MySQL索引优化详解(9大优化方式原则策略)

选择区分度高的列作为索引

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

比如：姓名，身份证号等都是高区分度的列，而性别，年龄，民族等字段则是低区分度字段，这类字段建立索引是有害无利。

 

不要在索引列上运算

在列上进行运算或使用函数会使索引失效，从而进行全表扫描。

如下所示：

-- 全表扫描
select * from article where year(publish_time) < 2019

-- 走索引
select * from article where publish_time < '2019-01-01'

-- 全表扫描
select * from article where id + 1 = 5

-- 走索引
select * from article where id = 4

 

最佳左前缀法则

如果是复合索引，就要遵守最左前缀法则，意思是：查询从最左前列开始，并且不跳过索引中的列。

比如：索引列是name,age,pos。

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; （会用索引NAME ）
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND AGE = 25;（会用索引NAME 和AGE）
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND POS= 'dev'（只会用到NAME，因为跳过了AGE）
EXPLAIN SELECT * FROM staffs WHERE AGE = 25 AND POS='dev';（不会用索引，因为最左前列NAME没有使用）

原因是：

因为当 B+ 树的数据项是复合的数据结构，比如（name、age、sex）的时候，B+ 数是按照从左到右的顺序来建立搜索树的。

比如：当 (张三,20,F) 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据。

 

索引不包含有NULL的值

设计多列复合索引时一定要注意，所有列必须不能为null，因为含有null的列是不会被加入索引的，直接无效。

所以，我们在数据库设计时，不要让字段的默认值为NULL。

 

前导模糊查询不会使用索引

SELECT * FROM user WHERE name LIKE '%陈%'

会导致数据库引擎放弃索引进行全表扫描，查询效率非常低。

比如：like “%aaa%”不会使用索引而like “aaa%”可以使用索引。

 

不等于要慎用

EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

 

字符类型加引号

假设id为varchar类型:

-- 全表扫描
select * from article where id = 100

-- 走索引
select * from article where id = '100'

第一种索引会失效，第二种索引字符类型需要加上引号，才可以走索引。

 

善用limit 1

当查询结果只可能为1条数据的时候，加上LIMIT 1可以增加性能，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

 

使用Explain优化索引

使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

explain的作用:

1、表的读取顺序;

2、数据读取操作的操作类型;

3、哪些索引可以使用;

4、哪些索引被实际使用;

5、表之间的引用;

6、每张表有多少行被优化器查询;

mikechen

mikechen睿哥，10年+大厂架构经验，资深技术专家，就职于阿里巴巴、淘宝、百度等一线互联网大厂。

关注「mikechen」公众号，获取更多技术干货！

后台回复【架构】即可获取《阿里架构师进阶专题全部合集》，后台回复【面试】即可获取《史上最全阿里Java面试题总结》