08.MYSQL索引及执行计划

B树功能分类

聚集索引
辅助索引
辅助索引(单列索引,联合索引,唯一索引,前缀索引)

分析执行计划

table : 表名
type : 查询的类型

查询类型又分为全表扫描和索引扫描

全表扫描 : ALL
索引扫描 : index,range,ref,eq_ref,const(system),NULL,从左到右,index性能最差,NULL性能最好

index: 全索引扫描

DESC SELECT countrycode FROM city;

range: 索引范围扫描(>,<,>=,<=,between and,or,in,like`)

DESC SELECT countrycode FROM city WHERE id>20000;
DESC SELECT countrycode FROM city WHERE Countrycode LIKE 'CH%';
#对于辅助索引来讲,!= 和not in等语句是不走索引的
#对于主键索引列来讲,!= 和not in等语句是走range
DESC SELECT * FROM city WHERE countrycode = 'CHN' OR countrycode = 'USA';
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

ref: 辅助索引等值查询

DESC SELECT * FROM city WHERE countrycode='CHN';
一般会把or语句修改为UNION ALL来提高性能
DESC SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件

DESC SELECT b.NAME, a.NAME, a.population FROM city AS a JOIN country AS b ON a.countrycode = b.CODE WHERE a.population < 100;

const(system) : 主键或者唯一键的等值查询

DESC SELECT countrycode FROM city WHERE id=100;

NULL

DESC SELECT countrycode FROM city WHERE id=20000;

possible_key : 可能会用到的索引
key : 真正选择了哪个索引
key_len : 索引覆盖长度,尽量让查询操作覆盖更多的索引长度

根据表的字符集不同,ken_len的长度也不同.字符集为utf8时,1个字符占3个字节.字符集为utf8mb4时,1个字符占4个字节.

varchar(20) : 当该字段可以为空时,key_len长度加1,varchar类型加2,代表首尾两个字符表示开始和结束.字符集为utf8时,1个字符占3个字节,此时key_len长度为63.字符集为utf8mb4时,1个字符占4个字节,此字段key_len长度为83.
char(20) : 当该字段可以为空时,key_len长度加1,字符集为utf8时,1个字符占3个字节,此时key_len长度为61.字符集为utf8mb4时,1个字符占4个字节,此字段key_len长度为81.
int : 当该字段可以为空时,key_len长度加1,该字段一共占32bit,所以占用4个key_len长度,此字段key_len长度为5.

varchar(20),字符集为utf8mb4,能存20个任意字符
字符集为utf8mb4,不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
对于中文,字符集为utf8mb4,1个占4个字节
对于数字和字母,1个实际占用大小是1个字节

Extra : 出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引order by,group by,distinct,union

联合索引应用细节

索引列等值查询

查询操作所有索引列都是<等值>查询条件下,与排列顺序无关,因为优化器会自动查询条件排列,为了查询效率,创建索引时,应该将唯一值较多的列,写在最左侧

mysql> DESC test;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| num   | int(11)   | YES  |     | NULL    |       |
| k1    | char(2)   | YES  | MUL | NULL    |       |
| k2    | char(4)   | YES  |     | NULL    |       |
| dt    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

mysql> ALTER TABLE test ADD INDEX m12(k1,k2,num);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC SELECT * FROM test WHERE k1='eF' AND num=16 AND k2='uvkl';
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 31      | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
1 row in set (0.07 sec)

mysql> DESC SELECT * FROM test WHERE k1='eF' AND k2='uvkl' AND num=16;
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 31      | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
1 row in set (0.08 sec)

不连续部分条件

创建一个联合索引,顺序为k1,k2,num,观察索引覆盖长度

k2,k1,num : 排序后为k1,k2,num,索引覆盖长度为9+17+5=31
k2,k1 : 排序后为k1,k2,索引覆盖长度为9+17=26
k1,num : 排序后为k1,索引覆盖长度为9
k1 : 排序后为k1索引覆盖长度为9

mysql> DESC test;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| num   | int(11)   | YES  |     | NULL    |       |
| k1    | char(2)   | YES  | MUL | NULL    |       |
| k2    | char(4)   | YES  |     | NULL    |       |
| dt    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

mysql> ALTER TABLE test ADD INDEX m12(k1,k2,num);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC SELECT * FROM test WHERE k1='eF' AND k2='uvkl' AND num=16;#9+17+5=31;
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 31      | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------------------+------+-----------------------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DESC SELECT * FROM test WHERE k1='eF' AND k2='uvkl';#9+17=26;
+----+-------------+-------+------+---------------+-----+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 26      | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------------+------+-----------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DESC SELECT * FROM test WHERE k1='eF' AND num=16;#9;
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 9       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DESC SELECT * FROM test WHERE k1='eF';#9;
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | m12           | m12 | 9       | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.03 sec)

在WHERE查询中如果出现范围查询

范围查询包括>,<,>=,<=,LIKE.创建一个联合索引,顺序为k1,k2,num,观察索引覆盖长度

如果有范围查询的需求,创建索引时,尽量将范围查询的字段放在最后一个来最大化查询效率

k2,k1,num : 排序后为k1,k2,num,num为范围查询时,索引覆盖长度为9+17+5=31
k2,k1,num : 排序后为k1,k2,num,k2为范围查询时,索引覆盖长度为9+17=26

mysql> DESC test;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| num   | int(11)   | YES  |     | NULL    |       |
| k1    | char(2)   | YES  | MUL | NULL    |       |
| k2    | char(4)   | YES  |     | NULL    |       |
| dt    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

mysql> ALTER TABLE test ADD INDEX m12(k1,k2,num);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC SELECT * FROM test WHERE k2='uvkl' AND k1='eF' AND num>16;#9+17+5=31;
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | m12           | m12 | 31      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DESC SELECT * FROM test WHERE k2<'uvkl' AND k1='eF' AND num=16;#9+17=26;
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | m12           | m12 | 26      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-----------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.04 sec)

多子句查询

mysql> DESC test;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| num   | int(11)   | YES  |     | NULL    |       |
| k1    | char(2)   | YES  | MUL | NULL    |       |
| k2    | char(4)   | YES  |     | NULL    |       |
| dt    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

mysql> ALTER TABLE test ADD INDEX k12(k1,k2);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC SELECT * FROM test WHERE  k1='eF' ORDER BY k2;
+----+-------------+-------+------+---------------+-----+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+---------------+-----+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test  | ref  | k12           | k12 | 9       | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-----+---------+-------+------+------------------------------------+
1 row in set (0.09 sec)

索引应用规范

建立索引的原则(DBA运维规范)

建表必须要有主键,一般是无关列,自增长
经常做为WHERE条件列ORDER BY,GROUP BY,JOIN ON,DISTINCT的条件
最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
列值长度较长的索引列,我们建议使用前缀索引.
降低索引条目,一方面不要创建没用索引,清理不常使用的索引(percona toolkit)
索引维护要避开业务繁忙期
小表不建索引

不走索引的情况(开发规范)

没有查询条件或者查询条件没有建立索引

#没有索引相关内容
select * from city;
#where条件和索引无关
select * from city where 1=1;

查询结果集是原表中的大部分数据,应该是25％以上
索引本身失效,统计数据不真实

面试题:同一个语句突然变慢?

统计信息过旧,导致的索引失效

查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)

#直接指定id为100
desc select * from city where id=100;
#虽然结果相同,但是这样的计算会让mysql进行全表扫描
desc select * from city where id-99=1;

隐式转换导致索引失效

#直接指定id为100
desc select * from city where id=100;
#虽然结果相同,但是这样的数字与字符串的转换会让mysql进行全表扫描
desc select * from city where id='100';

!=,<>,NOT IN不走索引(辅助索引)
LIKE "%aa"百分号在最左侧不走索引
联合索引

explain(desc)使用场景

你做过哪些优化?

你用过什么优化工具?

你对索引这块怎么优化的?

我们公司业务慢,请你从数据库的角度分析原因

应急性的慢：突然卡住

show processlist;获取到导致数据库卡顿的语句
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
建索引,改语句

一段时间慢(持续性的)

1.记录慢日志slowlog,分析slowlog 2.explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3.建索引,改语句

手机上阅读

最后一次更新于2020-05-30 13:16

神出鬼没的Enderman

long long ago,I was born.

B树功能分类

分析执行计划

联合索引应用细节

索引列等值查询

不连续部分条件

在WHERE查询中如果出现范围查询

多子句查询

索引应用规范

建立索引的原则(DBA运维规范)

不走索引的情况(开发规范)

explain(desc)使用场景

07.MYSQL索引及执行计划

09.MYSQL存储引擎