Mysql之索引选择及优化 (3)

在使用普通索引查询时，会先加载普通索引，通过普通索引查询到实际行的主键，再使用主键通过聚集索引查询相应的行，以此循环查询所有的行。若直接全量搜索聚集索引，则不需要在普通索引和聚集索引中来回切换，相比两种操作的总开销可能扫描全表效率更高。

实际工作中，还是要看业务情况，如果数据分布不均衡，实际查询条件总是查询数据较少的部分，在索引选择较低的列上加索引，效果可能也很不错。

覆盖索引

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

-- 只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表 select ID from T where k between 3 and 5 -- 增加字段V，每次查询需要返回V，可考虑把k、v做成联合索引 select ID,V from T where k between 3 and 5 最左前缀原则+索引下推 -- id、name、age三列，name、age上创建联合索引 -- 满足最左前缀原则，name、age均走索引 select * from T where and age=12 -- Mysql自动优化，调整name、age顺序，，name、age均走索引 select * from T where age=12 and -- name满足最左前缀原则走索引，MySQL5.6引入索引下推优化（index condition pushdown)，即索引中先过滤掉不满足age=12的记录再回表 select * from T where name like 'xxx%' and age=12 -- 不满足最左前缀原则，均不走索引 select * from T where name like '%xxx%' and age=12 -- 满足最左前缀原则，name走索引 select * from T where -- 不满足最左前缀原则，不走索引 select * from T where age=12

联合索引建立原则：

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

空间：优先小字段单独建立索引，例如：name、age，可建立(name,age)联合索引和(age)单字段索引

前缀索引 mysql> create table SUser( ID bigint unsigned primary key, name varchar(64), email varchar(64), ... )engine=innodb; -- 以下查询场景 mysql> select name from SUser where email='xxx'; -- 方案1：全文本索引，回表次数由符合条件的数据量决定 mysql> alter table SUser add index index1(email); -- 方案2：前缀索引，回表次数由前缀匹配结果决定 mysql> alter table SUser add index index2(email(6));

前缀索引可以节省空间，但需要注意前缀长度的定义，在节省空间的同时，不能增加太多查询成本，即减少回表验证次数

如何设置合适的前缀长度？

-- 预设一个可以接受的区分度损失比，选择满足条件中最小的前缀长度 select count(distinct left(email,n))/count(distinct email) from SUser;

如果合适的前缀长度较长？

比如身份证号，如果满足区分度要求，可能需要12位以上的前缀索引，节约的空间有限，又增加了查询成本，就没有必要使用前缀索引。此时，我们可以考虑使用以下方式：

倒序存储

-- 查询时字符串反转查询 mysql> select field_list from t where id_card = reverse('input_id_card_string');

使用hash字段

-- 创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引 mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc); -- 查询时使用hash字段走索引查询，再使用原字段精度过滤 mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

以上两种方式的缺点：

不支持范围查询

使用hash字段需要额外占用空间，新增了一个字段

读写时需要额外的处理，reverse或者crc32等

前缀索引对覆盖索引的影响？

-- 使用前缀索引就用不上覆盖索引对查询性能的优化 select id,email from SUser where email='xxx'; 唯一索引

建议使用普通索引，唯一索引无法使用change buffer，内存命中率低

索引失效

不做列运算，包括函数的使用，可能破坏索引值的有序性

避免 %xxx 式查询使索引失效

or语句前后没有同时使用索引，当or左右查询字段只有一个是索引，该索引失效

组合索引ABC问题，最左前缀原则

隐式类型转化

隐式字符编码转换

优化器放弃索引，回表、排序成本等因素影响，改走其它索引或者全部扫描