MySQL-索引及执行计划管理

1.1 索引的作用

类似于一本书的目录，起到优化查询的功能

1.2 索引的类型（笔试）

BTREE索引（最常用）
RTREE索引
HASH索引
全文索引

1.3 BTREE索引的细分类（算法）

B-TREE
B+TREE
B*TREE（默认）

1.4 BTREE索引的功能分类

聚集索引（集群索引）
辅助索引（二级索引） ※※※※

1.5 Btree是如何构建的？

1.5.1 辅助索引

建立索引：
alter table t1 add index idx_name(name);
1>将name列的所有值取出来,进行自动排序
2>将排完序的值均匀的落到16KB叶子节点数据页中,并将索引键值所对应的数据行的聚集索引列值
3>向上生成枝节点和根节点

1.5.2 聚集索引

1>默认是按照主键生成聚集索引.没有主键,存储引擎会使用唯一键；如果都没有,会自动生成隐藏的聚集索引.
2>数据在存储是,就会按照聚集索引的顺序存储到磁盘的数据页.
3>由于本身数据就是有序的,所以聚集索引构建时,不需要进行排序.
4>聚集索引直接将磁盘的数据页,作为叶子节点.
5>枝节点和根节点只会调取下层节点主键的最小值

1.5.3 辅助索引和聚集索引的区别？（重点）

1.6 辅助索引细分

单列辅助索引
联合索引
唯一索引

1.7 索引树的高度（越低越好）

1.7.1 表的数据量级大

（1）分区表
（2）分库分表（分布式架构）----目前最流行的

1.7.2 索引键值的长度

（1）尽可能的选择列值短的列创建索引
（2）采用前缀索引

1.7.3 数据类型的选择（选择合适）

1.8 索引的管理

1.8.1 准备压力测试的数据

create database test charset utf8mb4;
use test;
create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);
delimiter //
create  procedure rand_data(in num int)
begin
declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
declare str2 char(2);
declare str4 char(4);
declare i int default 0;
while i<num do
set str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));
set str4=concat(substring(str,1+floor(rand()*61),2),substring(str,1+floor(rand()*61),2));
set i=i+1;
insert into t100w values (i,floor(rand()*num),str2,str4,now());
end while;
end;
//
delimiter ;

插入100w条数据：

call rand_data(1000000);

commit;

1.8.2 进行压力测试

1>没有添加索引之前的测试

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 683.581 seconds
    Minimum number of seconds to run all queries: 683.581 seconds
    Maximum number of seconds to run all queries: 683.581 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

[root@db01 ~]#

2>添加索引之后的测试

alter table t100w add index idx_k2(k2);

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='LMhi'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 4.542 seconds
    Minimum number of seconds to run all queries: 4.542 seconds
    Maximum number of seconds to run all queries: 4.542 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

[root@db01 ~]#

1.8.2 索引命令操作

1>查询索引

desc student;
show index from student\G

Key：
PRI（主键）
UNI（唯一索引）
MUL（辅助索引）

2>创建索引
（1）创建单列索引：

alter table student add index idx_name(sname);

（2）创建联合索引：

alter table student add index idx_sname_sage_ssex(sname,sage,ssex);

（3）创建前缀索引：

alter table student add index idx_sname(sname(5));

（4）创建唯一索引：

alter table student add unique index idx_tel(telnum);

3>删除索引

alter table student drop index idx_name;

1.9 explain（desc）-----重点

1.9.1 作用：

抓取优化器优化过的执行计划

1.9.2 执行计划的分析

wenjuan[test]>wenjuan[test]>desc select * from t100w where k2 != 'asdf';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx_k2        | NULL | NULL    | NULL | 997381 |    86.34 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[test]>

说明：
table：以上SQL语句涉及到的表   ***
type：查询的类型（全表扫描---ALL、索引扫描、查不到数据---NULL）  *****
possible_keys：可能会用到的索引 ***
key：使用到的索引 ****
key_len：索引的覆盖长度  *****
Extra：额外的信息 ****

1.9.3 type详细说明（重点）

1> ALL：全表扫描 , 不会走任何索引
（1）查询条件,没建索引

oldguo[test]>explain  select * from test.t100w where k2='VWtu'
oldguo[test]>desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  |     | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

（2）有索引不走
以下这几种情况：

desc select * from t100w where k2 != 'asdf';
desc select * from t100w where k2 like '%aa%';
desc select * from t100w where k2 not in ('asda','asas');
desc select * from t100w;
desc select * from t100w where 1=1;

注意: !=和not in 如果是主键列,是走range.
2> index 全索引扫描

wenjuan[test]>desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx_k2 | 17      | NULL | 997381 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[test]>

===========从range开始,索引才有价值的============
3> range 索引范围查询
（1）所有索引：> ,<, >=, <= ,like , between and

wenjuan[world]>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)

wenjuan[world]>


wenjuan[world]>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 | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[world]>

in , or

wenjuan[world]>desc select * from city where countrycode in ('CHN','USA');
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[world]>

（2）聚集索引：!= 、not in

wenjuan[test]>desc select * from world.city where id != 10;
wenjuan[test]>desc select * from world.city where id not in (10,20);

说明：

B+tree 索引能额外优化到：> ,<, >=, <= ,like , between and

in 和 or 享受不到b+tree额外的优化效果的,所以我一般情况会将in , or 进行改写：

desc select * from city where countrycode='CHN' 
union all 
select * from city where countrycode='USA';

wenjuan[world]>desc select * from city where countrycode='CHN' 
    -> union all 
    -> select * from city where countrycode='USA'; 
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

wenjuan[world]>

4> ref（辅助索引等值查询）:

wenjuan[world]>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 | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

wenjuan[world]>

5> eq_ref：多表连接查询中,非驱动表on的条件列是主键或者唯一键

wenjuan[world]>desc select a.name,b.name from city as a join country as b on a.countrycode=b.code where a.population<100;

6> const(system)：主键或唯一键的等值

wenjuan[world]>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)

wenjuan[world]>

7> NULL：获取不到数据

wenjuan[world]>desc select * from city where id=100000000000000;

1.9.4 possible_keys（可能会用到的索引）

NULL：没有和查询条件匹配的索引条目
有值：有查询条件匹配的索引条目，但是没走，大部分原因是语句查询方式不符合索引应用条件

1.9.5 key（使用到的索引）

最终使用的索引，可以帮助我们判断是否走了合适的索引

1.9.6 key_len（索引的覆盖长度）：在联合索引应用的判断时，会经常看

字符集	一个字符最大存储长度占几个字节
utf8	3
utfmb4	4

不同类型索引	有not nulll时（非空）	没有指定not null时（为空）
int	最大4个字节	最大4+1个字节
tinyint	最大1个字节	最大1+1个字节
char(10)	最大*410**个字节	最大*410+1**个字节
varchar(10)	最大*410+2**个字节	最大*410+2+1**个字节

说明：

有非空约束时,key_length就是最大字节长度

在没有非空约束时: 字符最大长度+1

varchar类型,需要额外在最大字符长度+2(存储字符长度的最长度占位)

联合索引准备：

-- 创建测试表
create table t1(a int not null ,b char(10) not null ,c varchar(10) not null )charset utf8mb4;
wenjuan[test]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | NO   |     | NULL    |       |
| b     | char(10)    | NO   |     | NULL    |       |
| c     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

wenjuan[test]>

-- 创建索引
wenjuan[test]>alter table t1 add index idx(a,b,c);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

wenjuan[test]>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      |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | idx      |            2 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | idx      |            3 | c           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

wenjuan[test]>

（1）最完美的查询情况

desc select * from t1 where a=1 and b='a' and c='a';
desc select * from t1 where b='1' and a=1 and c='a';
desc select * from t1 where c='1' and a=1 and b='a';
desc select * from t1 where c='1' and b='a' and a=1;
desc select * from t1 where a=1 and c='a' and b='a';
desc select * from t1 where b='1' and c='a' and a=1;

结论：
当查询条件中,包含了索引列中所有的列条件,并且都是等值的查询,那么无关排列顺序,都可以走全联合索引优化;
原因：优化器会自动调整顺序,达到最佳的优化效果。所以，我们重点需要关注的是联合索引建立的顺序,从左到右,唯一值越多的列放在最左边

（2）部分索引（查询条件中,哪些因素会key_len长度）

--------1>按照索引的建立顺序,在查询条件中,少了任意一个中间列,后续列都无法走索引
wenjuan[test]>desc select * from t1 where a=1 and c='a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[test]>

wenjuan[test]>desc select * from t1 where a=1 and b like 'a%' and c='a';
-------2>在条件查询中间,出现不等值查询时,从不等值列开始,所有列都无法使用联合索引 (暂存)
优化方法：将不等值列放在最后.

-------3>如果有多子句的条件查询(必须是联合索引)，按照子句的执行顺序,建立联合索引.

1.9.7 Extra：额外的信息

using filesort ===> 排序不走索引,走的额外排序

wenjuan[(none)]>use world;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
wenjuan[world]>desc select * from world.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 | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[world]>

原因：在 group  by ,order by,distinct等.
一般优化的方法是,和where条件的列建立联合索引

扩展：

以json的方式显示执行计划，可以通过used_key_parts查看使用的索引部分

wenjuan[test]>desc format=json select * from t1 where b='1' and c='a' and a=1;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.20"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "idx"
      ],
      "key": "idx",
      "used_key_parts": [
        "a",
        "b",
        "c"
      ],
      "key_length": "86",
      "ref": [
        "const",
        "const",
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.20",
        "prefix_cost": "1.20",
        "data_read_per_join": "88"
      },
      "used_columns": [
        "a",
        "b",
        "c"
      ]
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

wenjuan[test]>

未完……