avatar
文章
63
标签
44
分类
10
首页
时间轴
标签
分类
娱乐
友情链接
留言板
关于
WF's Blog
首页
时间轴
标签
分类
娱乐
友情链接
留言板
关于
4.索引及执行计划
|数据库
字数总计:2.8k|阅读时长: 11 分钟
|阅读量:|评论数:

索引及执行计划

第1章 索引作用

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

第2章 索引的种类(算法)

1
2
3
4
5
B树索引
Hash索引
R树
Full text
GIS

第3章 B树 基于不同的查找算法分类介绍

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

第4章 MySQL的各类索引是怎么构成BTREE结构的

4.1 聚簇索引(聚集索引,集群索引)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
介绍: 
如果有PK,MySQL默认使用PK作为聚簇索引
将来存储数据时,能够保证所有数据行按照主键列的顺序存储到磁盘页中.
自动生成聚集索引树
如果没有PK,会自动选择唯一键(UK)
什么都没有,会自动按照时间戳的hash值作为聚簇索引.
一张表中只能有一个聚簇索引.
建议是数字的自增列最佳.

构建过程: 
(1) 在建表时,设定了主键.MySQL InnoDB 自动将主键作为聚簇索引列
(2) 在存储数据时,会按照聚簇索引组织存储数据
(3) InnoDB引擎会将数据行所在的数据页作为叶子节点
(5) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(6) 提取枝节点的最小值和指针,最终生成根节点

4.2 辅助索引(二级索引)

1
2
3
4
5
6
7
8
9
10
11
12
按照业务的查询特点,一般经常需要where  group  order by 条件列
创建辅助索引.
构建过程:
(1) 将索引键值进行自动排序(默认从小到大排序)
(2) 将排好序的键值+PK,生成叶子节点(16KB)
(3) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(4) 提取枝节点的最小值和指针,最终生成根节点

查询过程:
一旦我们拿索引键值作为条件发起查询
(1) 按照Btree查找算法,找到辅助索引叶子节点,获取到PK值
(2) 按照PK的值回表,通过聚簇索引Btree进一步找到具体数据行

第5章 辅助索引细分

1
2
3
4
5
1.普通的单列辅助索引
2.联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3.唯一索引
索引列的值都是唯一的.

第6章 索引树的高度的影响因素

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

第7章 索引的基本管理

7.1 索引建立前

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)

7.2 索引的管理命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
(1) 索引的查询 
mysql> desc city;
mysql> show index from city;
(2) 索引创建 
mysql> alter table city add index idx_name(name);
(3) 删除索引
mysql> alter table city drop index idx_name;

(5) 联合索引创建 
mysql> alter table city add index idx_name_co(name,countrycode);

(6) 前缀索引 
mysql> alter table city add index idx_name(name(10));

(7) 唯一索引 
 alter table t1  add unique index idx_tel(telnum);

7.3 压力测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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

第8章 执行计划(explain)

8.1 介绍

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

8.2 获取优化器选择完的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> desc select * from world.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.10 sec)

mysql> desc select * from world.city where countrycode='CHN' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city          #查询的表
   partitions: NULL
         type: ref           #查询类型
possible_keys: CountryCode   #可能走的索引
          key: CountryCode   #走的索引名
      key_len: 3             #应用索引的长度
          ref: const
         rows: 363           #查询结果集的长度
     filtered: 100.00
        Extra: NULL          #额外信息
1 row in set, 1 warning (0.00 sec)

8.3 执行计划分析

8.3.1 重点关注的信息

1
2
3
4
5
6
7
8
9
select_type  #查询类型 
table        #查询的表名.
type         #索引应用级别
#全表扫描 : ALL 
#索引扫描 : index,range,ref,eq_ref,const(system),NULL
possible_keys #有可能会用的索引.
key           #真正使用的索引名
key_len       #索引覆盖长度.主要针对联合索引.
extra         #file_sort 文件排序

8.3.2 type详解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
(1) ALL 全表扫描
mysql> desc  select * from city where district='guangdong';
mysql> desc  select * from city ;
mysql> desc select * from city where countrycode like '%CH%';
mysql> desc select * from city where countrycode not in ('CHN','USA');

(2)index 全索引扫描
mysql> desc select  countrycode from city;

(3)range 索引范围扫描 (优化的最低级别)
 > < >= <=   like  in or
mysql> desc  select * from city where id<10;
mysql> desc  select * from city where countrycode like 'CH%';

mysql> desc  select * from city where countrycode in ('CHN','USA');
mysql> desc  select * from city where countrycode='CHN' or countrycode='USA';

desc  

union all 不去重复
select * from city where countrycode='CHN'
union all 
select * from city where countrycode='USA';

union去重复 
select * from city where countrycode='CHN'
union 
select * from city where countrycode='USA';

注意: 特殊情况
聚簇索引   <>  not in 
mysql> desc select * from city where id <> 10;

(4)ref 辅助索引等值查询
mysql> desc select * from city where countrycode = 'CHN';

(5)eq_ref
多表查询,右表中的连接列是主键或者唯一键索引.
mysql> desc select city.name,country.name  from city 
    -> join country
    -> on city.countrycode=country.code
    -> where city.population<100;

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

8.3.3 key_len 计算方式

8.3.4 联合索引应用细节

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
联合索引:
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. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

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

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

第9章 索引应用规范

1
2
3
4
5
业务
1.产品的功能
2.用户的行为
"热"查询语句 --->较慢--->slowlog
"热"数据

9.1 建索引原则

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
(1) 语句本身就是全表扫描
	select *  from city;
	select * from city where 1=1;
(2) 查询条件没建索引
mysql> desc select * from city where name='dalian';
(3) 查询条件不满足索引应用逻辑
mysql> desc select * from city where countrycode like '%CH%';
如果业务中有大量的需求,可以使用ES
<>,not in()
(5) 查询条件中出现计算
mysql> desc select * from city where id-1=9;

(6) 出现隐式转换
mysql> desc select * from t1 where telnum=110;
mysql> desc select * from t1 where telnum='110';

(7) 统计信息不真实,导致索引失效.
现象: 昨天查询功能 0.0.1秒,今天突然就慢了
原因1: 
解决: 重建索引,重新收集统计信息
mysql> optimize table city;
原因2: 
	缓存失效:缓存击穿和雪崩

(8) 查询结果集超过25%,有可能会出现全表扫描(read ahead)
解决方案: limit, between and.

9.3 SQL优化思路

1
2
3
4
5
6
7
8
9
10
11
3.1 应急性慢
mysql -uroot -p
show full processlist;
kill xxxx;
explain select ;

3.2 持续性
slowlog 工具日志
desc 
改语句
index
文章作者: Wu Fei
文章链接: http://linuxwf.com/2020/04/15/4-%E7%B4%A2%E5%BC%95%E5%8F%8A%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 WF's Blog！
mysql
打赏
  • 微信
    微信
  • 支付宝
    支付宝
相关推荐
2020-04-15
1.数据库介绍和MySQL安装
2020-04-15
10.读写分离架构-Atlas
2020-04-15
11.分布式架构-MyCAT
2020-04-15
2.MySQL体系结构及管理
2020-04-15
3.SQL基础
2020-04-15
5.存储引擎
评论
繁