MySQL 基础优化

执行计划

1 分析
1.1 什么是执行计划？

select * from t1 where name='zs';
分析的是优化器按照内置的cost计算算法，最终选择后的执行计划。
cost？
代价，成本。
对于计算机来讲 ，代价是什么？
IO ，CPU ， MEM

1.2 查看执行计划

mysql> explain select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)

mysql> desc select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

1.3 执行计划显示结果的认识

table    : 此次查询涉及到的表
type     : 查询类型：全表扫描，索引扫描    
possible_keys  ： 可能用到的索引 
key     ： 最后选择的索引
key_len ： 索引覆盖长度
rows    ： 此次查询需要扫描的行数
Extra   ： 额外的信息

1.4 输出信息介绍
1.4.1 table

此次查询涉及到的表，针对一个查询中多个表时，精确到问题表
desc select country.name,city.name 
from world.city 
join country 
on city.countrycode=country.code 
where city.population='CHN';

mysql> desc select country.name,city.name from world.city join country on city.countrycode=country.code where city.population='CHN';
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4046 |    10.00 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.05 sec)

mysql> alter table city add index idx(population);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc select country.name,city.name from world.city join country on city.countrycode=country.code where city.population='CHN';
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys   | key     | key_len | ref                    | rows | filtered | Extra |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
|  1 | SIMPLE      | city    | NULL       | ref    | CountryCode,idx | idx     | 4       | const                  |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY         | PRIMARY | 12      | world.city.CountryCode |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

1.4.2 type 查询类型

（1）ALL： 全表扫描：不用任何的索引。

例如：
desc select * from city;
desc select * from city where 1=1;
desc select * from city where countrycode like '%ch%';
desc select * from city where countrycode not in ('CHN','USA');
desc select * from city where countrycode != 'CH%';

索引扫描：index < range < ref < eq_ref < const(system) 性能

mysql> desc select * from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from city where 1=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

mysql> desc select * from city where countrycode like '%ch%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from city where countrycode not in ('CHN','USA');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4046 |    85.07 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

mysql> desc select * from city where countrycode != 'CH%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4046 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（2）index： 全索引扫描

mysql> desc select countrycode from world.city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 12      | NULL | 4046 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（3）range： 索引范围查询：> < >= <= like in or between and

desc select * from city where id<10;
desc select * from city where countrycode like 'CH%';

desc select * from city where countrycode in ('CH%','USA');
------> 改写为 union all
select * from city where countrycode='CH%'
union all
select * from city where countrycode='USA'; # 怎么写？？？？

mysql> desc select * from city where id<10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 12      | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> desc select * from city where countrycode in ('CH%','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 12      | NULL |  275 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

特殊情况：查询条件为主键时
desc select * from city where id != 10;
desc select * from city where id not in (10,20);

mysql> desc select * from city where id != 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2032 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from city where id not in (10,20);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 2041 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（4）ref： 辅助索引的等值查询

desc select * from city where countrycode='CHN';

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       | ref  | CountryCode   | CountryCode | 12      | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

（5）eq_ref： 多表链接中，非驱动表链接条件是主键或唯一键。

A join B on A.xx=B.yy

desc select country.name,city.name 
from world.city 
join country on city.countrycode=country.code 
where city.population='CHN';

mysql> desc select country.name,city.name from world.city join country on city.countrycode=country.code where city.population='CHN';
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys   | key     | key_len | ref                    | rows | filtered | Extra |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
|  1 | SIMPLE      | city    | NULL       | ref    | CountryCode,idx | idx     | 4       | const                  |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY         | PRIMARY | 12      | world.city.CountryCode |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | decimal(10,2)                                                                         | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       |
| Population     | int(11)                                                                               | NO   |     | 0       |       |
| LifeExpectancy | decimal(3,1)                                                                          | YES  |     | NULL    |       |
| GNP            | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| GNPOld         | decimal(10,2)                                                                         | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int(11)                                                                               | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

（6）const(system): 聚簇索引等值查询

desc select * from city where id=10;

mysql> desc select * from city where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1.4.3 possible_keys，key

possible_keys    ：可能会走的索引，所有和此次查询有关的索引。
key    　　　　　　：此次查询选择的索引。

1.4.4 key_len 联合索引覆盖长度
（1）介绍：

对于联合索引index(a,b,c), 我们希望将来的查询语句，对于联合索引应用越充分越好。
key_len， 可以帮助我们判断，此次查询，走了联合索引的几部分。
例如：    idx(a,b,c) ----> a ab abc
全部覆盖：
select * from t1 where a= and b= and c= 
select * from t1 where a in and b in and c in 
select * from t1 where b= and c= and a= 
select * from t1 where a and b order by c
部分覆盖：
select * from t1 where a= and b= 
select * from t1 where a=
select * from t1 where a= and c= 
select * from t1 where a= and b> < >= like and c= 
select xxx from t1 where a order by c
不覆盖：
bc
b
c

（2）key_len的计算：index(a,b,c)

假设，某条查询可以完全覆盖三列联合索引。
例如：
select * from t1 where a= and b= and c= 
key_len= a长度？ + b长度？ + c长度？
长度指的是什么？
长度受到： 数据类型，字符集 影响
长度指的是，列的最大储值字节长度

数字：    
not null    没有not null
tinyint       1    1+1
int           4    4+1
bigint        8    8+1
key_len :    
a    int    not null -----> 4
a    int             -----> 5

字符：utf8            ----->    一个字符最大占3个字节
not null       没有       not null
char(10)       3*10      3*10+1
varchar(10)    3*10+2    3*10+2+1

key_len :
b char(10)       not null    30
b char(11)                   31
c varchar(10)    not null    32
c varchar(10)                33


create table t1 (
a int not null,         4
b int ,                 5
c char(10) not null,    40
d varchar(10)           43
)charset = utf8mb4;

index(a,b,c,d)

问：查询中完全覆盖到4列索引，key_len是多少？------》92

mysql> use test;
mysql> create table t1 (
-> a int not null,
-> b int ,
-> c char(10) not null,
-> d varchar(10)
-> )charset = utf8mb4;
mysql> desc t1;
mysql> alter table t1 add index idx(a,b,c,d);
mysql> desc select * from t1 where a=1 and b=1 and c='a' and d='a';
mysql> desc select * from t1 where a=1 and b=1 and c='a' ;
mysql> desc select * from t1 where a=1 and b=1 ;
mysql> desc select * from t1 where a=1 ;

1.4.5 extra

using filesort: 表示此次查询使用到了文件排序，说明在查询中的排序操作：order by group by distinct ..

desc select * from city where countrycode='CHN' order by population;
show index from city;
alter table city add index idx_c_p(countrycode,population);
show index from city;
desc select * from city where countrycode='CHN' order by population;

mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 12      | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4046 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx         |            1 | Population  | A         |        3897 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> alter table city add index idx_c_p(countrycode,population);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4046 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx         |            1 | Population  | A         |        3897 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_c_p     |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_c_p     |            2 | Population  | A         |        4046 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_c_p | idx_c_p | 12      | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2. 索引应用规范

2.1 建立索引的原则（DBA运维规范）
2.1.0 说明

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的？

2.1.1 （必须的）建表时一定要有主键，一般是个无关列

略，回顾一下，聚簇索引结构。

2.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案：
（1）如果非得使用重复值较多的列作为查询条件（例如：男女），可以将表逻辑拆分
（2）可以将此列和其他的查询类，做联合索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

mysql> select count(distinct countrycode) from world.city;
+-----------------------------+
| count(distinct countrycode) |
+-----------------------------+
|                         232 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct countrycode,population ) from world.city;
+-----------------------------------------+
| count(distinct countrycode,population ) |
+-----------------------------------------+
|                                    4052 |
+-----------------------------------------+
1 row in set (0.01 sec)

2.1.3 （必须的）为经常需要where, order by , group by , join on 等操作的字段，排序操作会浪费很多时间。

where A B C ----》 A B C
in 
where A group by B order by C
A,B，C
如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

2.1.4 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

2.1.5 限制索引的数目

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

2.1.6 删除不再使用或者很少使用的索引(percona toolkit)

pt-duplicate-key-checker

5.7以前：
mysql> use mysql
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| innodb_index_stats        |

5.7+：
mysql> use sys
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys                                 |
+-----------------------------------------------+
| schema_unused_indexes                         |

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。
数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.1.7 大表加索引,要在业务不繁忙期间操作
2.1.8 尽量少在经常更新值的列上建索引
2.1.9 建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

2.2 不走索引的情况（开发规范）
2.2.1 没有查询条件，或者查询条件没有建立索引

select * from tab; 全表扫描。
select * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select * from tab order by price limit 10 ; 需要在price列上建立索引
（2）
select * from tab where name='zhangsan' name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

2.2.2 查询结果集是原表中的大部分数据，应该是15-30％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
与数据库的预读能力有关，以及一些参数有关。彩蛋。。。。。

假如：tab表 id，name id:1-100w ，id列有(辅助)索引
select * from tab where id>500000;
如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

 

2.2.3 索引本身失效，统计数据不真实

索引和表有自我维护的能力。
对于表内容变化比较频繁的情况下，统计信息不准确，过旧。有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? --->统计数据不真实，导致索引失效。
DML   ? --->锁冲突
mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| innodb_index_stats |
| innodb_table_stats |
这两个表存放索引和表的状态信息，不是实时更新的
mysql> select * from innodb_index_stats;
mysql> select * from innodb_table_stats;
mysql> optimize table world.city;（实时更新）
mysql> alter table world.city engine=innodb;

[root@db01 ~]$ cd /data/3306/mysql
innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd

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

 

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

mysql> desc select * from world.city where id=3000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from world.city where id-1=2999;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

2.2.6 <> ，not in 不走索引（辅助索引）

EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit

or或in 可以改成union，使用不同的条件，分别测试。

EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

2.2.7 like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

3. 扩展：优化器针对索引的算法

自优化能力

3.1 MySQL索引的自优化-AHI（自适应HASH索引）

 

MySQL的InnoDB引擎，能够创建只有Btree。

AHI作用：自动评估“热”的内存索引page，生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的所读。
相当于索引的索引。

 

 

 

3.2 MySQL索引的自优化-Change buffer

比如insert，update，delete 一行数据。
对于聚簇索引会立即更新。
对于辅助索引，不是实时更新的。

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
change buffer 功能是临时缓存辅助索引需要的数据更新。
当我们需要查询新insert的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

优化器算法介绍；

 

show variables like '%switch%';
select @@optimizer_switch;

 

 

mysql> show variables like '%switch%'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.01 sec)

如何修改？
1. my.cnf
2. set global optimizer_switch='batched_key_access=on';
3. hints
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

3.3 ICP 索引下推

作用；
解决了，联合索引只能部分应用情况。
为了使减少没必要的数据页被扫描。
将不走所有的条件，在engine层取数据之前先做c二次过滤。
一些无关数据
https://mariadb.com/kb/en/multi-range-read-optimization/

 

index(a,b,c)
select * from t1 where a= and c=

在 server 先做a列过滤条件的索引优化，1-10
在 将c列的过滤下推到enigen层先做过滤，加载数据页 6-8 。

3.4 MRR muti range read 

mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';

辅助索引 --- 回表----》 聚簇索引
转换为
辅助索引 ---》sort id -- 回表--》聚簇索引

3.5 SNLJ

例子：
A join B on A.xx = b.yy where ....a.zz=XXXX
伪代码：
for each row in A matching range {
block
for each row in B {
A.xx = B.yy , send to client
}

}

以上例子，可以通过left join 强制驱动表。

 

3.6 BNLJ

在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配，一次性返回

主要优化了，CPU消耗，减少了IO次数

 

3.7 BKA

主要作用：使用来优化非驱动表的关联列有辅助索引。
BNL+MRR的功能
开启方式
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登录生效。