MySQL day04 索引及执行计划

最新推荐文章于 2020-09-09 10:19:19 发布

果子哥丶

最新推荐文章于 2020-09-09 10:19:19 发布

阅读量375

点赞数

分类专栏： # MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_39578545/article/details/106167714

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

索引

索引介绍
索引的管理命令
压力测试体现索引功能
执行计划

索引介绍

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

2.索引的种类(算法)

B树索引
Hash索引
R树
Full text
GIS

3.MySQL B+TREE 索引构建过程
理论上三次IO

3.1 聚簇索引BTREE结构（InnoDB独有）
区 ===》簇

构建前提

建表时，指定了主键列，MySQL InnoDB会将主键作为聚簇索引列，比如
如果没有主键,会自动选择唯一键unique作为聚集索引.
以上都没用，生成隐藏聚簇索引

作用
有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照ID值的顺序，有序在磁盘存储数据
在这里插入图片描述

3.2 辅助索引BTREE结构
说明：使用普通列作为条件构建的索引。
作用：优化非聚簇索引列之外的查询

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

(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

辅助索引查找过程
在这里插入图片描述

辅助索引只能查出name=s 对应 id=8和id=25
在这里插入图片描述如果查找的值在辅助索引都有就不用回到聚集索引找，select id,name

辅助索引得到的id值回到聚集索引找
在这里插入图片描述

聚集索引和辅助索引构成区别

聚集索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

辅助索引细分

1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表
查询
说明：使用多列组合一个索引
联合索引，注意最左原则。 idx(a,b,c)
（1）查询条件中，必须要包含最左列，上面例子就是a列
（2）建立联合索引时，一定要选择重复值少的列，作为最左列
例如：idx(a,b,c)
全部覆盖：
select * from t1 where a= and b= and c=
select * from t1 where a in


3.唯一索引
索引列的值都是唯一的.

关于索引树的高度受什么影响

              解决方法
1. 数据行过多：分表,分库,分布式
2. 索引列值过长 ：前缀索引
3. 数据类型: 选择合适的数据类型
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
                                         1      2      3

索引的管理命令

什么时候创建索引？

按照业务语句的需求创建合适的索引。
并不是将所有列都建立索引，不是索引越多越好
将索引建立在，经常 where group by order by join on …的条件

为什么不能乱建索引？
1.如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新。会阻塞很多正常的业务更新的请求。
2.索引过多，会导致优化器选择出现偏差

建索引原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期



❤没有查询条件，或者查询条件没有建立索引
select * from tab;       全表扫描。
select  * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

管理命令

1.查询表的索引情况

mysql> desc city;
key：PRI主键索引（聚簇索引） MUL辅助索引 UNI唯一索引
mysql> show index from city;

2.建立索引

分析业务语句

语法：alter table 表名 add index 索引名(列名);
mysql> alter table city add index idx_na(name);
联合索引：mysql> alter table city add index idx_n_c(name,countrycode);
前缀索引，比如字段district的前5个字符构建索引  mysql> alter table city add index idx_d(district(5));

联合索引创建
在这里插入图片描述

3.删除索引

mysql> alter table city drop index idx_na;
mysql> alter table city drop index idx_d;
mysql> alter table city drop index idx_n_c;

压力测试体现索引功能

100万数据

1.导入100w的测试表

mysql> source t100w.sql
mysql> show index from t100w;

在这里插入图片描述

2.压测命令：未建立索引

通过slap模仿100个用户连接，同时查询，一共执行200次。 每个人运行2次
#mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='780p'" engine=innodb \
--number-of-queries=200 -uroot -p123456-verbose

在这里插入图片描述

3.压测命令：建立索引

可以发现where k2

mysql> use mysql
mysql> alter table t100w add index idx_k2(k2);

在这里插入图片描述

执行计划

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

全表扫描(应当尽量避免,因为性能低)
索引扫描
获取不到数据

执行计划显示结果的认识
在这里插入图片描述
全盘扫描：不用任何的索引。 ALL
where 1=1;
where countrycode like ‘%ch%’;
where countrycode not in (‘CHN’,‘USA’);
where countrycode !=‘CHN’;

如果是主键 <> NOT IN 是可以走range索引的

索引扫描（越右越优先）：index < range < ref < eq_ref < const(system)

mysql> desc sql语句; 可以查看到是什么类型

1.type详解

从左到右性能依次变好.
ALL  :  
全表扫描,不走索引  ALL
例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';  
2. 查询条件出现以下语句(辅助索引列)
USE world 
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
注意:对于聚集索引列,使用以上语句,依然会走索引
DESC SELECT * FROM city WHERE id <> 10;

————————————————————————————————————————————————————————————————————————————

INDEX  :
全索引扫描
1. 查询需要获取整个索引树种的值时:
DESC  SELECT countrycode  FROM city;

2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c)  ---> a  ab  abc

SELECT * FROM t1 WHERE b 
SELECT * FROM t1 WHERE c    


RANGE :
索引范围扫描 
辅助索引> < >= <= LIKE IN OR 
如果是主键 <>  NOT IN  是可以走range索引的

例子:
1. DESC SELECT * FROM city WHERE id<5;
2. DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
3. DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

注意: 
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:

DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。


ref: 
非唯一性索引,辅助等值查询
DESC SELECT * FROM city WHERE countrycode='CHN';


eq_ref: 
在多表连接时,索引是主键或唯一非 NULL 索引时, 将使用该值
DESC SELECT b.name,a.name FROM city AS a 
JOIN country AS b 
ON a.countrycode=b.code 
WHERE a.population <100;
DESC country


const(system):
select * from city where id=10;
id是主键
DESC SELECT * FROM city WHERE id=10;

2.其他字段解释

extra: 
filesort ,文件排序.
SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

结论: 
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

索引优化效果测试:
优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" 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: 701.743 seconds
    Minimum number of seconds to run all queries: 701.743 seconds
    Maximum number of seconds to run all queries: 701.743 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

优化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" 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: 0.190 seconds
    Minimum number of seconds to run all queries: 0.190 seconds
    Maximum number of seconds to run all queries: 0.190 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

联合索引:
1. SELECT * FROM t1  WHERE a=    b=   
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);  
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);  
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.

2.  如果出现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 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

explain(desc)使用场景（面试题）

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