Mysql索引及执行计划

weiguoxin

2020-05-28

1.索引的作用

类似于一本书中的目录,起到优化查询的作用

2.索引的分类(算法)

B树索引 默认使用的索引类型(差不多95%以上)
R树索引
Hash索引在innodb中也有Hash索引，只是它是自适应的，不需要人管理，存储引擎帮我们自动去做的一个在索引层上的优化。
FullText
GIS 索引

Mysql索引及执行计划

3.Btree索引算法演变(了解)

B-Tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree

4.Btree索引功能上的分类

4.1 辅助索引

(1) 提取索引列的所有值,进行排序
(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
(3) 在叶子节点中的值,都会对应存储主键ID

Mysql索引及执行计划

select * from t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

4.2 聚集索引

(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的(聚集索引).
(2)MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

4.3 聚集索引和辅助索引的区别

(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
(2) 在一张表中,聚集索引只能有一个,一般是主键.
(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
(4) 聚集索引,叶子节点存储的是有序的整行数据.
(5) MySQL 的表数据存储是聚集索引组织表

5.辅助索引细分

单列辅助索引

联合索引(覆盖索引) *

唯一索引

前缀索引

6.索引树高度

索引树高度应当越低越好,一般维持在3-4最佳.

6.1 数据行数较多

分表 : parttion 用的比较少了.
分片：分布式架构.

6.2 字段长度

业务允许,尽量选择字符长度短的列作为索引列
业务不允许,采用前缀索引.

6.3 数据类型

char 和 varchar
enum

7.索引的命令操作

7.1 查询索引 (key)

db01 [world]>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 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Field ==> 列名字
key ==> 有没有索引,索引类型
PRI ==> 主键索引
MUL ==> 辅助索引(单列,联和,前缀)
UNI ==> 唯一索引

mysql> show index from city\G #查看索引的信息信息

7.2 创建索引

这个过程也会锁表。注意在业务不繁忙的时候操作。

单列的辅助索引:
mysql> alter table city add index idx_name(name);

注意：我们不建议在一个列上建多个索引，同一个表中，索引名不能同名。

多列的联合索引(覆盖索引):
mysql> alter table city add index idx_c_p(countrycode,population);
mysql> alter table city add index idx_c_p(population,countrycode);
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.

唯一索引:
mysql> alter table city add unique index uidx_dis(district);
#判断同一个列的值是否有重复的。
mysql> select count(district) from city;
mysql> select count(distinct district) from city;

前缀索引：
mysql> alter table city add index idx_dis(district(5));
注意：数字列不能用作前缀索引。

如果出现where 条件中出现不等值查询条件
DESC SELECT * FROM t_100w WHERE num <1000 AND k2=‘DEEF‘;
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC SELECT * FROM t_100w WHERE k2=‘DEEF‘ AND num <1000 ;

如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

7.3 删除索引

mysql> alter table city drop index idx_name;
mysql> alter table city drop index idx_c_p;
mysql> alter table city drop index idx_dis;

8.压力测试准备:

mysql> use test
mysql> source /tmp/t100w.sql
mysql> select * from t100w limit 10;

8.1 未做优化之前测试

#模拟100个用户连接我们的数据库，并跑200次查询
[[email protected] ~]#mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema=‘test‘ \
--query="select * from test.t100w where k2=‘MN89‘" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose
mysql>show processlist; #查看现在已经有多少连接上来。

Mysql索引及执行计划

8.2 索引优化后

#做相关优化

Mysql索引及执行计划

9.执行计划分析

9.1 介绍

(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2)select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据

9.2 作用

将优化器选择后的执行计划截取出来.便于管理员判断语句的执行效率.

9.3 获取执行计划

desc SQL 语句
explain SQL 语句

Mysql索引及执行计划

9.4 分析执行计划

1. table
表名
2. type:ALL index range ref eq_ref const NULL

查询的类型:
全表扫描 : ALL
索引扫描 : index,range,ref,eq_ref,const(system),NULL

ALL:
????select * from t1;
????select * from t1 where xxx where 条件无索引
????select * from t1 where != not in like ‘%xaa%‘

index: 全索引扫描
mysql> desc select countrycode from city;

range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select * from city where countrycode like ‘CH%‘;
对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range
mysql> desc select * from city where countrycode=‘CHN‘ or countrycode=‘USA‘;
mysql> desc select * from city where countrycode in (‘CHN‘,‘USA‘);
一般改写为 union all
desc
select * from city where countrycode=‘CHN‘
union all
select * from city where countrycode=‘USA‘;

ref: 辅助索引等值查询
desc
select * from city where countrycode=‘CHN‘
union all
select * from city where countrycode=‘USA‘;

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B
on a.x = B.y
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) : 主键或者唯一键的等值查询
mysql> desc select * from city where id=100;

NULL：不需要回表查数据行,理论上性能是最好。

possible_key:可能会用到的索引

key:真正选择了哪个索引

key_len:索引覆盖长度
varchar(20) utf8mb4
1. 能存20个任意字符
2. 不管存储的是字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节
4. 对于数字和字母,1个实际占用大小是1个字节

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

10.联合索引应用细节

关注key_len应用的长度:
联合索引 add index idx(a,b,c,d)
k1:aa k2:中国 k3:aaaa k4:中国你好
1. 只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
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‘;
原因：优化器,自动做查询条件的排列
2. 不连续部分条件
cda ----> acd ---> a -----> idx(c,d,a)
mysql> desc select * from test where k3=‘aaaa‘ and k4=‘中国你好‘ and k1=‘aa‘;
dba ----> abd ---> ab ----> idx(d,b,a)
mysql> desc select * from test where k2=‘中国‘ and k4=‘中国你好‘ and k1=‘aa‘;
总结：唯一值多的列放在最左侧
3. 在where查询中如果出现> < >= <= like
mysql> desc select * from test where k1=‘aa‘ and k3=‘aaaa‘ and k4=‘中国你好‘ and k2>‘中国‘;
mysql> alter table test add index idx1(k1,k3,k4,k2);
4. 多子句查询,应用联合索引
mysql> alter table test add index idx3(k1,k2);
mysql> desc select * from test where k1=‘aa‘ order by k2;

联合索引相应总结：
t1(id,k1,k2,k3) idx(k1,k2,k3)
1、在where 条件中都是等值的 where k1=xx k2=xx k3=xxx
无关where条件的顺序,只要把控建索引时,需要把唯一值较多的放在最左侧.
2、在条件查询中没有最左列条件时,没有K1列的查询,都是不走索引的
意外情况: 将表中所有列建立联合,没有个列做查询条件都会走索引(了解)
3、如果查询中出现(> < >= <= like)
????a = and b<xxx and c=xxx
????建索引时,将等值查询条件往前放,不等值的放在最后
4、多子句时
????where c xxx order by a,b
????idx(c,a,b)

11.索引应用规范

11.1 相关说明

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

1、(必须的) 建表时一定要有主键,一般是个无关列
2、选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
3、(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段

11.2 建立索引的原则（DBA运维规范）

(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona-toolkit(工具)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引

11.3 不走索引的情况（开发规范）

(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上，优化器觉得就没有必要走索引了。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走索引
(8) 联合索引

12.explain使用场景

key_len
utf8mb4
varchar(10) : 没有not null(1)+4*10+2=43
char(10) : 没有not null(1)+4*10=41
int : 没有not null(1)+4=5
用途:判断联合索引的覆盖长度,一般情况是越长越好

索引 mysql

Mysql索引及执行计划

1.索引的作用

2.索引的分类(算法)

3.Btree索引算法演变(了解)

4.Btree索引功能上的分类

4.1 辅助索引

4.2 聚集索引

4.3 聚集索引和辅助索引的区别

5.辅助索引细分

6.索引树高度

6.1 数据行数较多

6.2 字段长度

6.3 数据类型

7.索引的命令操作

7.1 查询索引 (key)

7.2 创建索引

7.3 删除索引

8.压力测试准备:

8.1 未做优化之前测试

8.2 索引优化后

9.执行计划分析

9.1 介绍

9.2 作用

9.3 获取执行计划

9.4 分析执行计划

10.联合索引应用细节

11.索引应用规范

11.1 相关说明

11.2 建立索引的原则（DBA运维规范）

11.3 不走索引的情况（开发规范）

12.explain使用场景

相关推荐