day05-01-如何计算执行计划ken_len
- 上节回顾
1.1 information_schema.tables
1.2 B树查找算法
1.3 B树功能分类
聚集索引
辅助索引
1.4 辅助索引分类
单列
联合
唯一
前缀
执行计划分析
2.1 table
2.2 type: ALL index range ref eq_ref const NULL
ALL: 全表扫描
select * from t1
select * from t1 where xxx where 条件无索 引
select * from t1 where != not in like '%xaa%'
index: 索引扫描
select id from city;
range: 索引范围扫描
< >= <= like , in or
ref: 辅助索引等值查询
select * from city where countrycode='CHN';
eq_ref : 多表连接，子表
const :主键和唯一键的等值
2.3 possible_key:可能会用到的索引
mysql> desc select * from city where countrycode != 'CHN';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 12 | NULL | 3716 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
TYPE=ALL 全表扫描
possible_keys = countrycode 有可能走这个索引，但不一定走
key=NULL
最终需要关注key字段，key字段的值，是确认SQL真正选择了哪个索引
2.4 key: 真正选择了哪个索引 --- 这个非常关键
mysql> alter table city add index idx_c_p(countrycode,population);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc select * from city where countrycode != 'CHN';
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode,idx_c_p | CountryCode | 12 | NULL | 3716 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
2.5 key_len 索引覆盖长度
key_len 是此字符集，此数据类型的最大预留长度
key_len 和字符集有关系，每个字符集规定的数据类型长度不同
varchar(10)
10个中文 实际预留40个字节
10个英文
10个数字
例：
mysql> create table t1 (id int,k1 char(2),k2 varchar(2),k3 char(4),k4 varchar(4));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| k1 | char(2) | YES | | NULL | |
| k2 | varchar(2) | YES | | NULL | |
| k3 | char(4) | YES | | NULL | |
| k4 | varchar(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> insert t1 values('1','aa','中国','aaaa','中国你好');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+------+--------+------+--------------+
| id | k1 | k2 | k3 | k4 |
+------+------+--------+------+--------------+
| 1 | aa | 中国 | aaaa | 中国你好 |
+------+------+--------+------+--------------+
1 row in set (0.00 sec)
mysql>
mysql> insert t1 values('1','bb','中国','vbbb','中国你好');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> alter table t1 add index id(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> alter table t1 add index k1(k1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add index k2(k2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add index k3(k3);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add index k4(k4);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| k1 | char(2) | YES | MUL | NULL | |
| k2 | varchar(2) | YES | MUL | NULL | |
| k3 | char(4) | YES | MUL | NULL | |
| k4 | varchar(4) | YES | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
先看id列，int类型
mysql> desc select * from t1 where id=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | id | id | 5 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
可以看到id 的 key_len = 5
id 字段类型为 int ，最大保留长度是4个字节，5-4 ，多一个字节，这个字节就是用来表示，字段是否允许为null
再看char类型
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| k1 | char(2) | YES | MUL | NULL | |
| k2 | varchar(2) | YES | MUL | NULL | |
| k3 | char(4) | YES | MUL | NULL | |
| k4 | varchar(4) | YES | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> desc select * from t1 where k1='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | k1 | k1 | 9 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
key_len = 9
char(2), 2* 最大预留长度4 ,然后 +1个字节，表示该字段是否为 null
2*4+1 =9
再看varchar
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| k1 | char(2) | YES | MUL | NULL | |
| k2 | varchar(2) | YES | MUL | NULL | |
| k3 | char(4) | YES | MUL | NULL | |
| k4 | varchar(4) | YES | MUL | NULL | |
+-------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> desc select * from t1 where k2='中国你好';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | k2 | k2 | 11 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
key_len = 11
varchar 和 char一样，最大保留字节长度是 4
k2的varchar(2) 长度是2
2 * 4 =8
8 + 1(非空标识) =9
11 - 9 = 2 ???
varchar数据类型，会用这2个字节，分别标识数据的起始位和结束位
第一个字节标识 是否为NULL
第二个字节标识 数据起始位
...
...
最后一个字节标识 数据结束位
所以这里得到的key_len 是11
那么k4 | varchar(4) ，key_len长度应该是（
1字节标识NULL + 1字节数据起始位 +4个字符 * 4（每字符最大保留长度4字节） + 1字节数据结束位
1+1+4*4+1 = 19
看一下
mysql> desc select * from t1 where k4='中国你好';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | k4 | k4 | 19 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
key_len = 19
联合索引
联合索引 的 key_len 值 = 建立联合索引列字段最大长度总和
mysql> alter table t1 drop index id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop index k1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop index k2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop index k3;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop index k4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
建立一个联合索引
mysql> alter table t1 add index idx_k1tok4(k1,k2,k3,k4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> show index from t1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | idx_k1tok4 | 1 | k1 | A | 2 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_k1tok4 | 2 | k2 | A | 2 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_k1tok4 | 3 | k3 | A | 2 | NULL | NULL | YES | BTREE | | |
| t1 | 1 | idx_k1tok4 | 4 | k4 | A | 2 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql>
| k1 | char(2) ken_len: 1+2*4 = 9
| k2 | varchar(2) ken_len: 1+1+2*4+1 = 11
| k3 | char(4) ken_len: 1+4*4 = 17
| k4 | varchar(4) ken_len: 1+1+4*4+1 =19
在这里，刚才建立的联合索引key_len值，应该等于 9+11+17+19 =56
查看一下，验证结果：
mysql> desc select * from t1 where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
+-------+------------+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_k1tok4 | idx_k1tok4 | 56 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql>
key_len: 56
也就是说，在使用联合索引中，where 后的列顺序，以及列key_len值的总和
越接近 联合索引的Key_len值，说明使用联合索引覆盖率 越好，SQL语句的查询性能越好。
验证一下：
mysql> desc select * from t1 where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_k1tok4 | idx_k1tok4 | 56 | const,const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql>
where 后的 k1，k2，k3，k4 全部按照建立联合索引顺序使用，字段key_len总长度是56，可以看到性能开销全部都是const，说明性能非常好
只用k1,k2,k3
mysql> desc select * from t1 where k1='aa' and k2='中国' and k3='aaaa';
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_k1tok4 | idx_k1tok4 | 37 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql>
mysql>
mysql>
只用k1,k2
mysql> desc select * from t1 where k1='aa' and k2='中国';
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_k1tok4 | idx_k1tok4 | 20 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql>
mysql>
只用k1
mysql> desc select * from t1 where k1='aa';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_k1tok4 | idx_k1tok4 | 9 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>
把握一个原则
在有联合索引的执行计划查看时，ken_len覆盖长度，一定是越长越好！
单列索引，key_len长度应该是越短越好，这样索引占用空间就很小
varchar(20) utf8mb4
- 能存20个任意字符
- 不管存储的是字符,数字,中文,都是1个字符最大预留长度是4个字节
- 对于中文,1个字占4个字节
- 对于数字和字母,1个字符实际占用大小是1个字节
select length() from test;
===============================
=====================================================
- 联合索引应用细节
联合索引 add index idx(a,b,c,d)
建立联合第一原则：唯一值多的列放在最左侧
3.1. 只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
abcd
acbd
adbc
acbd
等等
mysql> desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
mysql> desc select * from test where k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';
原因: 优化器,自动做查询条件的排列
3.2. 不连续部分条件
cda ----> acd ---> a -----> idx(c,d,a)
dba ----> abd ---> ab ----> idx(d,b,a)
3.3. 在where查询中如果出现> < >= <= like
在where条件中出现<不等值>查询时，联合索引应用，会止步于<不等值>条件列，即使后面有其他等值条件，也不会应用。
(1)
mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';
(2)
mysql> alter table test add index idx1(k1,k3,k4,k2);
此类语句优化，
1.可以把不等值条件列放在语句最后面，让语句优先匹配等值查询
2.重建索引
3.4. 多子句 查询,应用联合索引
mysql 对于单SQL的索引应用，默认只应用一个索引
mysql> desc select * from test where k1='aa' order by k2;
mysql> alter table test add index idx3(k1,k2);
多子句查询，建立联合索引，必须按照where后子句的执行顺序建立，
select * from t1 where c4='xx group by c2 having c1 order by c3
那么这个查询语句的联合索引，就必须按照子句执行顺序
alter table t1 add index idx_name(c4,c2,c1,c3);
=====================================================
Extra: Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
order by
group by
distinct
union
关注key_len应用的长度，可以判断是否有效的应用全联合索引
- explain(desc)使用场景（面试题）
你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库hang的语句
-
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
-
建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句 -
索引应用规范
5.1 建立索引的原则（DBA运维规范）
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期（8.0，索引新特性，可以设置隐藏/可用状态）
(7) 小表不建索引
5.2 不走索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上（辅助索引有此类限制）。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
大量的索引信息进行更新，统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
mysql> create table t3(id int,name varchar(64),telnum char(11));
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into t3 values(1,'aa','123456'),(2,'bb',654321),(3,'cc','998877');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> alter table t3 add index idx_telnum(telnum);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc select * from t3 where telnum='123456';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ref | idx_telnum | idx_telnum | 45 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> desc select * from t3 where telnum=123456;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t3 | NULL | ALL | idx_telnum | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql>
telnum char(11)
where telnum=123456
where telnum='123456'
这两个条件得到结果一样，但性能完全不同，很多人会忽略。
字段本身的数据类型就是char字符串，不带引号的话，mysql需要用内置函数把数字
抓换成字符串，因为在索引列上使用函数或者运算符操作，就会导致索引时效，从而进行全表扫描
带引号就不不会这样，直接走辅助索引等值扫描
这就是隐式转换导致索引失效
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走、
如果业务场景确实有大量这种搜索操作，那么建议使用ES，mysql不擅长做这种事情
(8) 联合索引 与 where 后字段排序不一致
浙公网安备 33010602011771号