当前位置：首页 > news >正文

MySQL索引从基础到原理，看这一篇就够了

news 2025/7/12 15:05:37

https://developer.aliyun.com/article/841106
https://zhuanlan.zhihu.com/p/29118331

索引创建使用总结

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

1. 索引的创建

• 1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引
• 2、索引的个数不要过多。——浪费空间，更新变慢。
• 3、区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
• 4、频繁更新的值，不要作为主键或者索引。 ——页分裂
• 5、组合索引把散列性高(区分度高)的值放在前面。——最左前缀匹配原则
• 6、创建复合索引，而不是修改单列索引。——组合索引代替多个单列索引（由于MySQL中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
• 7、过长的字段，怎么建立索引?——使用短索引。
当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
create table shop(address varchar(120) not null);
alter table shop add key (address(12));
• 8、不建议用无序的值(例如身份证、UUID )作为索引——当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

2. 什么时候会用不到索引

• 1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式、计算(+ – * /):
explain SELECT * FROM ‘t2’ where id+1 = 4;
• 2、字符串不加引号，出现隐式转换
explain SELECT * FROM ‘user’ where name = 136;
explain SELECT * FROM ‘user’ where name = ‘136’;
• 3、like 条件中前面带%
where 条件中 like abc%，like %2673%，like %888 都用不到索引吗?为什么?
explain select * from user where name like ‘wang%’;
explain select * from user where name like ‘%wang’;
过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。
• 4、负向查询
NOT LIKE 不能:
explain select *from employees where last_name not like ‘wang’
!= (<>)和 NOT IN 在某些情况下可以:
explain select * from user where id not in (1)
explain select * from user where id <> 1
• 5.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
• 6，排序的索引问题
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
其实，用不用索引，最终都是优化器说了算。
优化器是基于什么的优化器?
基于 cost 开销(Cost Base Optimizer)，它不是基于规则(Rule-Based Optimizer)，也不是基于语义。怎么样开销小就怎么来。