深入剖析MySQL索引原理3

索引的使用原则

误区：是不是为每个字段创建索引会更好呢？
1）列的离散度
离散度公式：count(distinct(column_name)):count(*)重复值越高离散度越低，索引建立在离散度低的字段上效率不大，浪费空间

2）联合索引最左匹配
创建的顺序与书写的顺序一致，只能从最左边开始检索，不能中断否则无法用到索引。

3）覆盖索引
MySQL存储引擎自动优化

回表：在辅助索引检索查询数据时，除了扫描辅助索引的B+树时，还需要多扫描主键索引的B+Tree，这个动作成为回表。

覆盖索引：若在语句select col_name中的字段名包含在了二级索引的时候，不需要去主键索引的叶子节点中查询。减少B+ Tree的扫描，减少I/O的次数，提高查询性能。

4）索引条件下推
参数：optimizer_switch
默认开启

Select * from A where last_name=’wang’ and first_name like ‘%zi’

1. 找出二级索引数据（3个索引），回表，然后查询所有符合条件的数据【index_condition_pushdown = off】
2. 找出二级索引数据，在二级索引中筛选以zi结尾的索引（一个索引），到主键索引中查询符合条件的数据【index_condition_pushdown = on,效率更高】

索引的创建与使用原则

创建索引

1. 在用于where判断order排序和join的on字段上创建索引
2. 索引的个数不要太多
3. 区分度低的字段，例如性别，不要建索引
4. 频繁更新的值，不要作为主键或者索引
5. 复合索引吧散列性高（区分度高）的值放在前面
6. 创建复合索引，而不是修改单列索引。
7. 过长的字段怎么建立索引？前缀索引，全文索引
8. 不建议用无序的值作为索引。

什么时候用不到索引？

1. 索引列上使用函数（replace\SUBSTR\CONCAT\sum count average）、表达式
2. 字符串不加引号，出现隐式转换
3. Like条件中前面带%
4. 负向查询能用到索引吗？< > != not in 不一定