四，索引及执行计划
2021-05-27 本文已影响0人
会倒立的香飘飘
1，索引作用
类似于一本书中的目录,起到优化查询的作用
2，索引的分类(算法)
B树 （BTree） 默认使用的索引类型
R树 （RTree）
Hash
FullText 全文索引（实现与es差不多）
GIS 索引
3，BTree索引算法演变
image.png
4，BTree索引功能上的分类
辅助索引
1,提取索引列的所有值,进行排序
2,将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
3,在叶子节点中的值,都会对应存储主键ID
聚集索引
1,MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
2,MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
3,聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根
辅助索引和聚集索引的区别
1，表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可,
2， 在一张表中,聚集索引只能有一个,一般是主键.
3， 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.,
4， 聚集索引,叶子节点存储的是有序的整行数据.
5， MySQL 的表数据存储是聚集索引组织表
辅助索引细分
单列辅助索引
联合索引(覆盖索引) *****
唯一索引(建索引的值是唯一的)
5，索引的操作
查询索引
desc city;
PRI ==> 主键索引
MUL ==> 辅助索引
UNI ==> 唯一索引
show index from city;
或
show index from city\G
创建索引
单列辅助索引
mysql> alter table city add index idx_name(name);
多列联合索引
mysql> alter table city add index idx_c_p(population,countrycode);
唯一索引
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));
删除索引
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;
压力测试准备
create database test charset utf8mb4 collate utf8mb4_bin;
use test;
create table t100w (
id int,
num int,
k1 char(2),
k2 char(4),
dt timestamp
)charset utf8mb4 collate utf8mb4_bin;
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 ;
call rand_data(1000000);
commit;
数据跑完后：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t100w |
+----------------+
1 row in set (0.00 sec)
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 1030345 |
+----------+
1 row in set (0.28 sec)
未做优化前的测试
从表中查询数据：
mysql> select * from t100w limit 10;
+------+--------+------+------+---------------------+
| id | num | k1 | k2 | dt |
+------+--------+------+------+---------------------+
| 1 | 56914 | Hd | MN89 | 2019-07-09 16:01:41 |
| 2 | 542219 | tx | bc45 | 2019-07-09 16:01:41 |
| 3 | 944336 | 6i | XYPQ | 2019-07-09 16:01:41 |
| 4 | 137325 | PK | 34VW | 2019-07-09 16:01:41 |
| 5 | 81318 | wz | 67KL | 2019-07-09 16:01:41 |
| 6 | 574174 | LZ | wxuv | 2019-07-09 16:01:41 |
| 7 | 146558 | ZG | fg23 | 2019-07-09 16:01:41 |
| 8 | 649971 | e4 | ABxy | 2019-07-09 16:01:41 |
| 9 | 94891 | ey | YZ34 | 2019-07-09 16:01:41 |
| 10 | 22211 | Sv | HITU | 2019-07-09 16:01:41 |
+------+--------+------+------+---------------------+
10 rows in set (0.00 sec)
随便选择一个k2的值填写到k2="MN89"
[root@mister_f ~]# 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=2000 -uroot -p2693499317@aA -h172.21.0.15 -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: 700.413 seconds
Minimum number of seconds to run all queries: 700.413 seconds
Maximum number of seconds to run all queries: 700.413 seconds
Number of clients running queries: 100
Average number of queries per client: 20
模拟100个用户进行数据查询可以使用命令查看 show processlist；
未做优化前耗时了700s
索引优化后
mysql> use test
mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (3.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | MUL | NULL | |
| dt | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
然后在测试
[root@mister_f ~]# 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=2000 -uroot -p2693499317@aA -h172.21.0.15 -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: 1.713 seconds
Minimum number of seconds to run all queries: 1.713 seconds
Maximum number of seconds to run all queries: 1.713 seconds
Number of clients running queries: 100
Average number of queries per client: 20
性能提高了近700倍
6，执行计划分析
作用
将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率
获取执行
desc sql语句
explain sql语句
以上两种方法都可以获取执行计划，以上语句只是获取执行计划不真正执行，对性能没有什么影响；
mysql> desc select * from t100w limit 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 1027638 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.02 sec)
mysql> explain select * from t100w limit 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 1027638 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
分析执行计划
1，table
表名
2，type
查询的类型：
全表扫描： 会显示all
索引扫描： 会显示index,range,ref,eq_ref,const(system),NULL
index:全索引扫描
查询的数据结果集是需要通过遍历整个索引树来达到最终的效果就是index
性能是要比全表扫描要好一些
例子：
mysql> desc select id 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 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
range：索引范围扫描
像(> < >= <= , between and ,or,in,like )这些类型的查询都是索引范围扫描
例子：
mysql> desc select * from city where id>2000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2094 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
mysql> desc select * from city where countrycode='CHN' or countrycode='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.04 sec)
对于辅助索引来讲,!= 和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';
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 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
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 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
eq_ref：多表连接时子表使用主键列或者唯一键列作为连接条件
A JOIN B B就是子表
ON A.x=B.y 说的是B.y这个列是主键或者唯一键的时候会走eq_ref
一般情况下多表连接查询小表都是放在左边，因为驱动表是不走索引的
例子：
mysql> 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;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | CountryCode | NULL | NULL | NULL | 4188 | 33.33 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.a.CountryCode | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
const(system):主键或唯一键的等值查询
例子：
mysql> desc select * from city where id=100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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.01 sec)
3，possible_keys：可能会用到的索引
4，key：真正走了那个索引
5，key_len：索引覆盖长度
varchar(20) utf8mb4
1. 能存20个任意字符
2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节
4. 对于数字和字母,1个实际占用大小是1个字节
select length(行) from test;
6，Extra:
重点关注：Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有和里的应用索引
需要关注一下sql子句
order by
group by
distinct
union
关注key_len覆盖的长度，
7，explain（desc）使用场景
你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句
8，索引的应用规范
建立索引的原则
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引
不走索引的情况（开发规范）
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) 联合索引
