MySQL-索引管理及执行计划

一、索引介绍

1.1、索引作用

提供了类似于书中目录的作用,目的是为了优化查询

1.2、索引算法上分类

大的分类：

B树索引 
Hash索引 
R树 
Full text 
GIS  #地图类索引
-------------------------------

#B树基于不同的查找算法分类：
B-tree 
以下两种类型在范围查询方面提供了更好的性能(> < >= <=)
B+Tree   
B*Tree

1.3、索引功能上的分类

1.3.1、辅助索引

1）辅助索引(S)怎么构建B树结构的?

• (1)索引是基于表中列(索引键)的值生成的B树结构
• (2)首先提取此列所有的值,进行自动排序
• (3)将排好序的值,均匀的分布到索引树的叶子节点中(16K)
• (4)然后生成此索引键值所对应得后端数据页的指针
• (5)生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

2）辅助索引细分

• 1）普通的单列辅助索引（普通索引）
• 2）覆盖索引(联合索引)：多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
• 3）唯一索引:索引列的值都是唯一的.

1.3.2、聚集索引

1）前提

• (1)表中设置了主键,主键列就会自动被作为聚集索引.
• (2)如果没有主键,会选择唯一键作为聚集索引.
• (3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

2）聚集索引(C)怎么构建B树结构的?

• (1) 在建表时,设置了主键列(ID)
• (2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
• (3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点

1.3.3、索引高度影响因数

1）数据量级, 解决方法:分表,分库,分布式

2）索引列值过长 , 解决方法:前缀索引

3）数据类型:

变长长度字符串,使用了char,解决方案:变长字符串使用varchar

enum类型的使用enum (‘山东‘,‘河北‘,‘黑龙江‘,‘吉林‘,‘辽宁‘,‘陕西‘......)

1.3.4、B树查找算法

二、索引基本管理

2.1、查看索引

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
Field :列名字
key   :有没有索引,索引类型
	  PRI: 主键索引
	  UNI: 唯一索引 
	  MUL: 辅助索引(单列,联和,前缀)

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         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2.2、索引创建修改删除

1）创建索引

alter table city add index idx_name(name);		#方法一
create index idx_name1 on city(name);			#方法二
show index from city;

2）删除索引

alter table city drop index idx_name1;		#直接删除索引名称即可

3）覆盖索引(联合索引)

alter table city add key idx_co_po(countrycode,population);	#多个字段上建立索引
alter table city add index idx_co_po(countrycode,population);	#多个字段上建立索引

4）前缀索引

alter table city add index idx_di(district(5));	#在前5个字符上建立索引

5）唯一索引

alter table city add unique index idx_uni1(name);

ERROR 1062 (23000): Duplicate entry ‘San Jose‘ for key ‘idx_uni1‘	#唯一键冲突

三、执行计划

3.1、执行计划介绍

获取到的是优化器选择完成的,他认为代价最小的执行计划.

作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.

3.2、select 获取数据的方法

1. 全表扫描(应当尽量避免,因为性能低)

2. 索引扫描

3. 获取不到数据

3.3、执行计划获取及分析

获取优化器选择后的执行计划：explain或者desc

mysql> desc select * from city where countrycode=‘CHN‘\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 3
          ref: const
         rows: 363
     filtered: 100.00
        Extra: NULL
---------------------------------------------------------------

#执行计划相关信息分析：
table: city                               #查询操作的表  
possible_keys: CountryCode,idx_co_po      #可能会走的索引
key: CountryCode						  #真正走的索引  
type: ref								  #索引类型      
Extra: Using index condition              #额外信息 

四、索引类型详解 *****

如下为索引类型，从左到右性能依次变好.

ALL			#全表扫描
index			#全索引扫描
range			#索引范围查询
ref				#辅助索引的等值查询
eq_ref			#多表连接的表,On的条件是主键或唯一键
system(const)	#主键或唯一键的等值查询
NULL			#索引中扫描不到这个数据

4.1、all: 全表扫描

desc select * from city;
desc select * from city where name like ‘%C%‘;
desc select * from city where name != ‘CHN‘;	#或者<>
desc select * from city where countrycode not in (‘CHN‘,‘USA‘);
#注意：生产中几乎是没有这种需求的。尽量避免

4.2、index: 全索引扫描

需要扫描整个索引树,获取到想要数据,比ALL性能好,顺序IO,可以减少回表查询

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

mysql> desc select CountryCode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | CountryCode | 3       | NULL | 4188 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

4.3、 range : 索引范围查询

4.3.1、情况种类

>  <  >= <= 
in 
or 
like ‘CH%‘
between and
---------------------------------------------------
注意：
B+树额外优化了
> < >= <= 
between and 
like ‘CH%‘
in or无法享受B+树的额外优化,可以用union all来替代

4.3.2、示例

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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> 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 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

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 | 3       | NULL |  397 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

4.3.3、优化案例：in=>union all

mysql> 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 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+

mysql> 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  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

4.4、 ref: 辅助索引的等值查询

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 | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

4.5、eq_ref

多表连接的表,On的条件是主键或唯一键

4.6、system 或 const

主键或唯一键的等值查询

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  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

4.7、NULL

索引中扫描不到这个数据

mysql> desc select * from city where id=5000;  #id=5000不存在
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

MySQL-索引管理及执行计划

原文：https://www.cnblogs.com/hujinzhong/p/11634743.html