MySQL_索引的使用与设计

最左前缀法则

1. 最左前缀法则适用于联合索引；查询从索引的最左列开始，不跳过其中的列，如果跳过其中的列将会导致索引失效（后面字段的索引失效）。

2. 验证最左前缀法则

   • 三个列的联合索引都同时使用

     explain select * from user where profession = '通讯工程' and age = 17 and gender = 1;
     

     

   • 只使用专业和年龄查询

     explain select * from user where profession = '通讯工程' and age = 17;
     

     

   • 只使用专业查询

     explain select * from user where profession = '通讯工程';
     

     

3. 最左原则使用索引的情况（A+B+C）

   • A
   • AB
   • ABC
   • BC(不可以，需要查询A字段才可以)
   • AC(使用A索引)
   • CBA （也是可以的）

索引失效

1. 字符串不加引号

   explain select *from user where phone = 13708822169;
   

   

2. or 连接（or 前后没有建立索引的时候不会被用到）

   explain select *from user where phone = 13708822169 or status = 0;
   

   

3. 模糊查询

   explain select * from user where phone like '%0' ;
   

   

4. 对于索引使用函数运算

   explain select * from user where substring(phone,1,2) = '19' ;
   

   

5. 数据分布影响

   explain select * from user where phone > '13508822168';
   

   

   explain select * from user where gender = 1;
   

   

索引的使用和设计原则

使用原则

1. 避免使用 select * from table_name;

   • 可能避免了回表查询（减少一次查询，提高了效率，前提是查询的结果已经再二级索引的叶子节点出现）

2. 使用前缀索引代替全索引

   • 计算前缀长度（唯一索引的性能是最高的，唯一索引的前缀长度是1）

   • 不重复的索引基数/数据表的总记录数

     select count(*) from user;
     
     select count(distinct email) from user;
     
     select count(distinct substring(email ,1,8)) from user;
     
     select count(distinct substring(email ,1,2))/count(*) from user;# 判断前缀的长度为2
     

3. 单列索引和联合索引

   • 使用多个单列索引查询时，并不会全用索引，会根据mysql优化器的指引进行选择

     

设计原则

1. 数据量大的时候，一定要建索引
2. 针对于经常查询的多列，去建联合索引
3. 如果是字符串类型，而且数据很大，尽量去使用前缀索引，前缀度我们尽量去靠近1
4. 索引还需要控制数量，索引不是越多越好，一定要合适就行，越多增删改越慢，影响用户体验
5. 要去选择区分度高的列做索引，尽量去建立唯一索引
6. 设计列尽量不要去存空数据