MySQL day04 索引及执行计划

2020-05-17 16:13 457人阅读评论(0)

索引

索引介绍
索引的管理命令

什么时候创建索引？
建索引原则
管理命令

1.查询表的索引情况
2.建立索引
3.删除索引

压力测试体现索引功能

1.导入100w的测试表
2.压测命令：未建立索引
3.压测命令：建立索引

执行计划

1.type详解
2.其他字段解释
explain(desc)使用场景（面试题）

索引介绍

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)建索引,改语句

转载：https://blog.csdn.net/qq_39578545/article/details/106167714

查看评论

飞道的博客