[MySQL] 一文弄懂MySQL索引创建原则

• 一、适合创建索引
  
  
  
  • 1、字段的数值有唯一性限制
    
  • 2、频繁作为Where查询条件的字段
    
  • 3、经常Groupby和Orderby的列
    
  • 4、Update、Delete的where条件列
    
  • 5、Distinct字段需要创建索引
    
  • 6、多表Join连接操作时，创建索引注意事项
    
  • 7、使用列的类型小的创建索引
    
  • 8、使用字符串前缀创建索引
    
  • 9、区分度高的列适合作为索引
    
  • 10、使用最频繁的列放到联合索引的左侧
    
  • 11、在多个字段都要创建索引的情况下，联合索引由于单值索引
    
  
  
• 二、不适合创建索引
  
  
  
  • 1、在where中使用不到的字段不要设置索引
    
  • 2、数据量小的表最好不要使用索引
    
  • 3、有大量重复数据的列上不要建立索引
    
  • 5、不建议用无序的值作为索引
    
  • 6、删除不在使用或很少使用的索引
    
  • 7、不要定义冗余或重复的索引
    
  • 8、删除不在使用或很少使用的索引
    
  • 9、不要定义冗余或重复的索引
    
  
  
• 总结
  

select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花费211ms

alter table student_info add index idx_sid(student_id);
select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花费3ms

select student_id,count(*) as num from student_info group by student_id limit 100;#花费2.466s

alter table student_info add index idx_sid(student_id);
select student_id,count(*) as num from student_info group by student_id limit 100;#花费6ms

对于既有group by又有order by的查询语句，建议最好建立联合索引，并且将group by中的字段放到order by字段的前边，满足‘最左前缀匹配原则’，这样索引的利用率就会高，自然查询的效率也就会高；同时8.0之后的版本支持降序索引，如果order by之后的字段时降序的，可以考虑直接创建降序索引，也会提高查询效率。

update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花费549ms

alter table student_info add index idx_name(name);
update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花费2ms

select distinct(student_id) from student_id;#花费2ms

alter table student_info add index idx_sid(student_id);
select distinct(student_id) from student_id;#花费0.1ms

select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花费176ms

alter table student_info add index idx_name(name);
select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花费2ms

• 数据类型越小，在查询时进行的比较操作越快
  
• 数据类型越小，索引占用的空间就越少，在一个数据页内就可以存下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以存储更多的数据在数据页中，提高读写效率。
  

上述对于主键来说很合适，因为在聚簇索引中既存储了数据，也存储了索引，可以很好的减少磁盘I/O；而对于二级索引来说，还需要一次回表操作才能查到完整的数据，也就能加了一次磁盘I/O。

create table product(id int, desc varchar(120) not null);
alter table product add index(desc(12));

• 频繁更新的字段不一定要创建索引，因为更新数据的时候，索引也要跟着更新，如果索引太多，更新的时候会造成服务器压力，从而影响效率。
  
• 避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时虽然提高了查询速度，同时也会降低更新表的速度。