mysql技术4--索引及执行计划

一.索引的介绍

1. 索引的作用

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

2. 索引的分类(算法)

B树    默认使用的索引类型
R树
Hash
FullText 
GIS 索引

3. BTREE索引算法演变

4. Btree索引功能上的分类

4.1 辅助索引

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

4.2 聚集索引

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

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

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

5. 辅助索引细分

5.1 单列辅助索引
5.2 联合索引(覆盖索引) *****
5.3 唯一索引

6. 索引树高度

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

6.1 数据行数较多

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

6.2 字段长度

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

6.3 数据类型

char 和 varchar 
enum 

7. 索引的命令操作

7.1 查询索引

desc city;
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 unique index uidx_dis(district);（验证列有没有重复值）

mysql> select count(district) from city;（统计没去重复的值的数量）
mysql> select count(distinct district) from city;（统计去完重复的值的数量）
通过两者的值的比较，验证district列是否有重复的值

前缀索引（只能针对字符串列）
mysql> alter table city add index idx_dis(district(5));#只取district的前5列

7.3 删除索引

查看索引
mysql>show index from city;
删除索引：
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. 压力测试准备:

上传一个表t100w.sql：
[root@web01 ~]# cd /tmp
[root@web01 tmp]# rz -E
上传到数据库：
mysql> use test
mysql> source /tmp/t100w.sql
查看：
mysql>select count(*) from t100w;
myslq>select * from t100w limit 10;  #查看前10行

8.1 未做优化之前测试

模拟100个用户连接数据库，总共做了2000次查询
查看键值：
mysql> select * from t100w limit 10;
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  56914 | Hd   | MN89 | 2019-07-09 16:01:41 |
选择当中的其中一个键值来做压力测试：
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 -p123456 -verbose（要等一会）
mysql> show processlist;   （可以查看是否当前正在查询）

查询完毕后显示查询所用的时间：
[root@db01 ~]# 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 -p123456 -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: 755.861 seconds
	Minimum number of seconds to run all queries: 755.861 seconds
	Maximum number of seconds to run all queries: 755.861 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20

8.2 索引优化后

索引优化后查询所需要的时间：
mysql>use test
mysql>alter table t100w add index idx_k2(k2);
mysql>desc t100w;创建辅助索引
[root@db01 ~]# 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 -p123456 -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.678 seconds#明显快了很多
	Minimum number of seconds to run all queries: 1.678 seconds
	Maximum number of seconds to run all queries: 1.678 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20
	

二. 执行计划分析

1.作用

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

2.获取执行

desc   SQL语句
explain SQL 语句
2个语句的执行效果是一样的

mysql> desc select * from test.t100w where k2='MN89';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 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 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

3.分析执行计划

1.table：  表名

2.type：   查询的类型:
1）全表扫描	 	: ALL 
2）索引扫描    	: index,range,ref,eq_ref,const(system),NULL（从左到右依次性能变好）

mysql>use world
mysql>desc city;
2.1）index: 全索引扫描
mysql> desc select countrycode  from city;

2.2）range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select  * from city where countrycode like 'CH%';
(> like性能比or and好，原因是B*树采用的是双向指针，所以对于连续的查询性能比较高，而or and是非连续查询）
对于辅助索引来讲,!= 和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';

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

2.4）eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件（驱动表类型是ALL，子表类型是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;

2.5）const(system) : 主键或者唯一键的等值查询
mysql> desc select * from  city where id=100;

3.possible_key:可能会用到的索引
desc 
select  * from city where countrycode!='CHN' 

4.key: 真正选择了哪个索引
mysql> alter table city add index idx_c_p(countrycode,population);
mysql>desc select  * from city where countrycode!='CHN' ;

5.key_len 索引覆盖长度
单列索引
varchar(20)  utf8mb4
1）能存20个任意字符
2）不管存储的是字符,数字,中文,都1个字符最大预留长度是4个字节
3）于中文,1个占4个字节 
4） 对于数字和字母,1个实际占用大小是1个字节
计算：
varchar(10) :  没有not null(1)+4*10+2=43
char(10)    :  没有not null(1)+4*10=41 
int         :  没有not null(1)+4=5
key_len用途:判断联合索引的覆盖长度,一般情况是越长越好

联合索引: 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）多子句查询时,应用联合索引  （按照子句的执行顺序建立联合索引）
mysql>desc select * from test where k1='aa' order by k2;
mysql>alter table test add index idx3(k1,k2);

6.Extra: Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
需要关注一下这些排序语句：
order by
group by
distinct 
union 
可以关注key_len应用的长度来查看联合索引是否合理

三.索引优化

1. explain(desc)使用场景

你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?

题目意思:  公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

四.索引应用规范

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

2.不走索引的情况
(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) 联合索引

本文地址：https://blog.csdn.net/weixin_44736359/article/details/107581589