什么是索引
MySQL查询必须从第一条记录开始读完整个表,直到找出相关的行。
表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,
MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
建立索引会占用磁盘空间的索引文件
索引的作用
- 加快搜索,快速找出在某个列中有一特定值的行
- mysql中的primary key,unique,联合唯一也都是索引,
这些索引除了加速查找以外,还有约束的功能
索引类型
normal,unique,fulltext,spatial 的区别是什么?
索引的类别由建立索引的字段内容特性来决定,通常normal最常见
normal:普通索引
unique:唯一索引,不允许重复的索引
如果该字段信息保证不会重复,例如身份证号用作索引时,可设置为 unique
主键索引:primary key :加速查找+约束(不为空且唯一)
唯一索引:unique:加速查找+约束 (唯一)
组合索引,即一个索引包含多个列。
primary key(id,name) 联合主键索引
unique(id,name) 联合唯一索引
index(id,name) 联合普通索引
fulltext:全文搜索的索引
用于搜索很长一篇文章的时候效果最好
搜索比较短的文本,如果就一两行字的,普通的 INDEX 也可以
spatial:空间索引
索引方法:
BTREE B树,可以是多叉树,主流使用
b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
BTree可用于 “>、 < 、=” 查询,例如 where id = 100
HASH(key,value) 这种方式对范围查询支持得不是很好
查询单条快,范围查询慢
Hash 不能做order by 排序,不能做 用like模糊查询
RTREE
不同引擎对索引方法的支持
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
慢查询的优化步骤
先运行看看是否真的很慢,注意设置SQL_NO_CACHE
1. where条件单表查,锁定最小返回记录表
* 把查询语句的 where都应用到表中返回的记录数最小的表开始查起,
* 单表每个字段分别查询,看哪个字段的区分度最高
2. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
3. order by limit 形式的sql语句让排序的表优先查
4. 了解业务方使用场景
5. 加索引时参照建索引的几大原则
6. 观察结果,不符合预期继续从0分析
在实际操作应该选取表中哪些字段作为索引?
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:
- 选择唯一性索引 UNIQUE
- 为经常需要排序、分组和联合操作的字段建立索引
- 为常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
- 不推荐在同一列建多个索引
经常更新修改的字段不要建立索引针对 mysql的,因为字段更改同时索引就要重新建立,排序,
而Orcale好像是有这样的机制字段值更改了,它不立刻建立索引,排序索引,而是根据更改个数,时间段去做平衡索引这件事的。