MySQL索引篇

索引的分类
索引类型有两种, 一种是BTree索引, 一种是Hash索引。本篇只介绍BTree索引。

BTree索引

BTree索引的使用范围

• 精确匹配
• 范围匹配
• Like前缀查询

Like关键字, 只有前缀查询才会使用到索引, 其余Like都不会使用到索引。

尽管使用字符串前缀匹配可以使用索引, 但效率不会很高, 所以要慎用Like关键字。只要使用了Like, 这个优化难度就比较大。
like前缀使用索引的速度是由匹配字符串的精确程度，匹配字符串精度越高，速度越快，否则反之，匹配范围过大时也会有不使用索引的情况。

复合索引

复合索引和普通索引差别不大。重点在于复合索引是以左侧为基准，左侧字段的选择性一定要高。
字段值的重复性越少，重复性就越高。比如性别字段，男和女，选择性就特别低。

在查询过程中使用复合索引时, 必须包含左侧列, 不然索引会失效。

索引的优化策略

索引失效的情况

1. 索引的选择性太差
2. 使用了not关键字/使用了<>
3. where字句跳过左侧索引列，直接查询右侧索引字段(复合索引)
4. 对索引列进行计算或使用函数(索引列不独立)
5. 使用or 查询部分字段没有使用索引
6. 字符串条件没有使用’’
7. 索引字段没有添加 not null约束
8. 隐式转换导致索引失效

关于or为什么会导致索引失效, 拿BTree索引来讲, BTree索引是有序的, 正因为是有序的, 所以支持范围查找, 当where中的两个字段都有各自的索引, 此时使用or关键字, 因为不同的两个字段的索引的顺序并不相同,这时就无法同时用两个索引判断一条记录是否满足条件，所以为了做这样的过滤，只能放弃其中一个索引，甚至做全表扫描。

关联表的两个字段类型不一样时会进行隐式转换, 此时会导致索引失效。

使用索引优化排序

Order By 字段与索引字段顺序相同时, 索引还可以进一步优化排序速度(前提是列的排序必须一致, 如果列是乱序, 就无法保证)。

• 当排序中出现了索引左侧列, 则允许使用索引排序。
• 左侧字段单字段排序时, 索引支持升降序。
• 多字段排序, 左侧字段必须是升序, 且顺序不能打乱

//创建联合索引
CREATE INDEX index_name ON test (uid, cid);
//索引生效
SELECT * FROM test WHERE uid <100000 ORDER BY uid,cid;
//索引生效
SELECT * FROM test WHERE uid <100000 ORDER BY uid,cid DESC;
//索引失效
SELECT * FROM test WHERE uid <100000 ORDER BY uid DESC,cid;
//索引失效
SELECT * FROM test WHERE uid <100000 ORDER BY cid, uid;

Explain执行计划