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

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

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

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

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

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

 

不要在索引列上运算

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

如下所示:

-- 全表扫描
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+ 数是按照从左到右的顺序来建立搜索树的。

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

比如:当 (张三,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语句,分析查询语句或者表结构的性能瓶颈。

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

explain的作用:

1、表的读取顺序;

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

3、哪些索引可以使用;

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

5、表之间的引用;

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

以上

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

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

评论交流
    说说你的看法