Mysql常见的优化原则
1、最左匹配原则
首先引用Mysql官方文档的一段话：
MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 8.3.5, “Column Indexes”).
MySQL可以创建联合索引(即, 多列的索引). 一个索引可以包含最多16列. 对于 某些数据类型, 你可以索引列的前缀(这里说的是对于Blob和Text类型, 索引列的前几位就可以, 如INDEX(blob_col(10))
先说结论
- 联合索引一方面可以帮助我们避免创建重复索引
- 当我们的查询条件不满足最左匹配原则时，就会出现索引失效的问题
- 创建联合索引时，尽量使得区分度较大的索引列放在最左侧。区分度计算公式：distinct(index_col)/total_num
下面以一个具体案例说明：
# 创建员工表
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #编号
ename VARCHAR(20) NOT NULL DEFAULT "", #名字
job VARCHAR(9) NOT NULL DEFAULT "", #上级编号
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
hiredate DATE NOT NULL, #入职时间
sal DECIMAL(7,2) NOT NULL,
comm DECIMAL(7,2) NOT NULL,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #部门编号
) DEFAULT CHARSET=utf8;
# 建立job_deptno联合索引
ALTER TABLE emp ADD INDEX idx_job_deptno (job,deptno)
先看下面三条sql的执行计划
EXPLAIN SELECT id FROM emp WHERE job = 'SALESMAN' AND deptno = '105';
# 执行计划
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ -------------- -------------- ------- ----------- ------ ---------
1 SIMPLE emp ref idx_job_deptno idx_job_deptno 32 const,const 99672 Using where
EXPLAIN SELECT id FROM emp WHERE job = 'SALESMAN';
# 执行计划
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ -------------- -------------- ------- ------ ------ -------------
1 SIMPLE emp ref idx_job_deptno idx_job_deptno 29 const 250124 Using where
EXPLAIN SELECT id FROM emp WHERE deptno = '105';
# 执行计划
id select_type table type possible_keys key key_len ref rows Extra
-- ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE emp ALL (NULL) (NULL) (NULL) (NULL) 500249 Using where
从上面的三条执行计划中我们可以得到什么信息？
- （1）和（2）都命中了索引，（3）未命中了索引。这是因为第三条sql的查询条件中未包含联合索引中的最左列(job)
- 通过（1）和（2）的索引长度（key_len）可以看出，（2）中的索引仅仅使用了（job）索引。这表明：idx_job_deptno该索引的效果相当于创建了两个索引：idx_job、idx_job_deptno
2、避免重复索引
还是以上述案例说明：假设我们要查询某个领导下的所有员工信息。
我们很自然地想到建立(job)字段的索引，但其实完全没有必要
因为我们上面已经解释过，创建下面的联合索引其实相当于创建了两个索引
这其实给我们一个启示，在平时业务中建立索引时尽量建立联合索引减少索引个数
INDEX idx_job_deptno (job,deptno)
==
INDEX(job)
INDEX(job,deptno)
3、禁止索引运算
如下面的sql会导致索引失效
首先添加相关索引部门id索引
ALTER TABLE emp ADD INDEX idx_deptno (deptno)
EXPLAIN SELECT id FROM emp WHERE deptno + 1 = 105;
id select_type table type possible_keys key key_len ref rows Extra
--- ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE emp ALL (NULL) (NULL) (NULL) (NULL) 500249 Using where
EXPLAIN SELECT id FROM emp WHERE deptno > 105;
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE emp ALL idx_deptno (NULL) (NULL) (NULL) 500249 Using where
EXPLAIN SELECT id FROM emp WHERE deptno != 105;
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE emp ALL idx_deptno (NULL) (NULL) (NULL) 500249 Using where
4、避免非前缀匹配的模糊查询
添加idx_name索引
ALTER TABLE emp ADD INDEX idx_name (ename)
前缀匹配查询（推荐）
EXPLAIN SELECT * FROM emp WHERE ename LIKE 'aAAa%';
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- -------- ------- ------ ------ -------------
1 SIMPLE emp range idx_name idx_name 62 (NULL) 38 Using where
索引失效场景
EXPLAIN SELECT * FROM emp WHERE ename LIKE '%aAAa%';
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE emp ALL (NULL) (NULL) (NULL) (NULL) 500249 Using where
5、避免子查询
目标：查询指定部门下的员工id
先看子查询
SELECT emp.id FROM emp WHERE deptno IN (SELECT deptno FROM dept);
# 执行时间：0.337sec
连接查询
SELECT emp.id FROM emp LEFT JOIN dept ON emp.deptno = dept.`deptno`;
# 执行时间：0.009sec
事实胜于雄辩！
为什么上述子查询效率会比连接查询慢？
我们再来看下第一个子查询，一般我们都会认为它会分为两步：首先执行内查询，继而执行外查询
SELECT deptno FROM dept；
SELECT emp.id FROM emp WHERE deptno IN (a,...,n);
然而mysql优化器会改写为这样：
SELECT emp.id FROM emp WHERE deptno exsit (SELECT deptno FROM dept);
这样会有个问题，如果外层表是一个非常大的表，对于外层查询的每一行，子查询都得执行一次，这个查询的性能会非常差。
6、延迟关联查询
先说核心思想：在分页查询这种场景下，通过覆盖索引查询获取所需的主键，减少回表次数，再根据主键查询真正所需要的数据。
目标：查询工号排序第200000个~200100个员工
EXPLAIN SELECT emp.ename FROM emp ORDER BY deptno LIMIT 200000, 100;
# 执行时间：0264sec
id select_type table type possible_keys key key_len ref rows Extra
-- ----------- ------ ------ ------------- ------ ------- ------ ------ ----------------
1 SIMPLE emp ALL (NULL) (NULL) (NULL) (NULL) 500249 Using filesort
遇到偏移量比较大的分页查询时，上述查询方式有什么弊端？
如limit 200000,20 取第200000个~200100条记录，mysql会取出200100条记录然后将前200000条记录丢弃，这无疑是一种巨大的性能浪费。
此时我们采取延迟关联的方式来优化，即先通过覆盖索引查询所需记录的主键索引，此时无需回表；然后通过主键一次性查出相应的记录
EXPLAIN SELECT emp.ename FROM emp INNER JOIN (SELECT id FROM emp ORDER BY deptno LIMIT 200000, 100) AS temp ON temp.id = emp.id;
#执行时间：0.071sec
id select_type table type possible_keys key key_len ref rows Extra
-- ----------- ---------- ------ ------------- ---------- ------- ------- ------ -------------
1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 100
1 PRIMARY emp eq_ref PRIMARY PRIMARY 4 temp.id 1
2 DERIVED emp index (NULL) idx_deptno 3 (NULL) 200100 Using index
我们可以从对应的执行计划中看出明显的差距。 如《高性能mysql》中所述。
