数据库学习day04：SQL基础优化-索引及执行计划

基础 
1. 介绍安装
2.体系结构管理
3. SQL基础及元数据获取
4. SQL基础优化-索引及执行计划 

核心技术
1. 存储引擎
2. 日志管理
3. 备份恢复
4. 主从复制

高级-架构篇
1. 高可用及读写分离
2. 分布式数据库架构 
3. MySQL全面优化 

NoSQL部分 
云数据库部分

1. 什么是索引?索引的作用?

相当与一本书中的目录,用来加速查询.

2. 索引算法的演变

二叉树 ---> 红黑树---> BTREE ----> B+TREE(B*TREE)
目的是快速的确认范围 

B-Tree 
B+Tree(B*Tree)   在范围查询方面提供了更好的性能(> < >= <= like)
区别:  leaf节点和no-leaf节点,有相邻的指针.

B+Tree查找算法

image.png

3. MySQL支持的索引类型

B+TREE 
HASH
RTREE
FullTEXT
GIS索引

4. MySQL中的BTREE如何构建

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

4.1 聚簇索引

生成条件:
(1).自动选择主键列(PK),没有主键会自动选择UK,如果都没有自动生成隐藏列
(2).InnoDB 才有聚簇索引.
(3)聚簇索引必须在建表时才有意义,一般是表的无关列(ID)
功能: 
1.数据存储时,按照聚簇索引列顺序在磁盘上有序的存储在连续数据页上(16K)---->索引组织表(IOT)
如何构建:
1. 将有序的整表数据行所在数据页,作为叶子节点
2. 按照聚簇索引列值,向上生成枝节点和根节点

查询: 
    1.按照聚簇索引列作为查询条件时,等值查询,发生3次IO即可获得数据行
    2.如果是范围查询,利用叶子节点双向指针继续优化查询.

4.2 辅助索引

构建: 
    1. 提取name列值+ID列值,按照name列值的升序排序
    2. 将排好序的数据,均匀的,有序的存储到叶子节点中
    3. 通过name的值向上生成枝节点和根节点.

查询: 
    1. 按照name作为查询条件时,遍历辅助索引树,得到PK(主键)
    2. 拿着PK的进行回表查询
    
总结: 
    1. 减少查询行数
    2. 减少IO的次数
    3. 等值,缩小范围 
    4. 尽量使用聚簇索引查询
    5. 减少回表次数
       联合索引使用:减少ID的个数（减少回表的次数）
       覆盖索引: 辅助索引中有所有要查询的值

5. 索引管理

5.1 索引的查询

(1).desc city;
(2).show index from 表;

5.2 创建索引（辅助索引）

5.2.1 辅助索引的细分

（1）.单列索引

-- 创建:
mysql> alter table city add index idx_na(name);
-- 删除:
mysql> alter table city drop index idx_na;

（2）唯一索引,索引列的值都是唯一的.

alter table xxxx add unique index xxx(列);
alter table xxxx drop index xxx;

（3）前缀索引

mysql> alter table city add index idx_na(name(10));
mysql> alter table city drop index idx_na;

（4）联合索引:多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询

mysql> alter table city add index idx_na_co(name,countrycode);

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

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

5.4 联合索引

1. 构建时,取出id+a+b+c,依次a-->b--->c 的顺序,进行数据行的排序
2. 枝节点和根节点,只会保存最左列的索引值.
联合索引查询时,遵循最左原则.最左列尽量使用重复值少的列.

把控一个原则: 建立了联合索引,尽量应用完整

如何确认一个查询,对于联合索引应用长度?*

a ab   abc 
(a,b,c)              会不会走索引?                   走哪些部分?

where a=?                                         会                    a
where b=?                                        不会             
where c=?                                       不会                                  
where a=? and b=?  and c=?         会                   abc        
where  b=?  and c=? and a=?        会                   abc             
where a=? and  b>?  and c=?        会                   ab 
where a=? and  b  like ?  and c=?  会                   ab
where a=?  and c=?                 会                   a
where a=?  and b=?                 会                    ab
where b=? and c=?                  不会

多子句:
where a and c    group by b    ----->   acb

6. 执行计划获取及分析

在SQL执行之前,将优化器选择后的执行计划获取出来进行分析

1. 命令 (desc) 或（explain）
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)

6.1 执行计划之table

主要针对多表比较有意义.

damao[world]>desc select city.name,country.code from city join country on city.countrycode=country.code where city.countrycode='CHN';
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | const | PRIMARY       | PRIMARY     | 3       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | city    | NULL       | ref   | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL        |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

6.2 执行计划之type(查询类型) ******

全表扫描   : ALL 
索引扫描   : index,range,ref,eq_ref,const(system)
获取不到数据 : NULL

6.3 执行计划之possible_keys

可能会用到的索引. CBO(代价),RBO(规则)

6.4 执行计划之key

真正用到的索引

6.5 rows

查询结果集行数.

6.6 Extra *****

额外信息

7. 执行计划重要信息详解

7.1 type类型

7.1.1 ALL ：全表扫描

原因:  
(1) 查询条件没有索引
where group by  order by  select后的列  
select * from city;
(2) 查询条件不满足索引应用规则
查询条件是不确认值时,不走索引
mysql> desc  select * from city where countrycode !=  'CHN';
mysql> desc  select * from city where countrycode  like  '%CH%' ;
(3)其他原因
放一放.

7.1.2 index ：全索引扫描

原因：select 后跟的查询的列为索引列（可以理解为范围索引扫描）
damao[world]>desc select countrycode from world.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)

7.1.3 range: 索引的范围扫描

>  <   >= <= ,like, between and ,in ,or

1. desc select * from city where id<100;
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';

7.1.4 ref : 辅助索引等值查询

damao[world]>desc select countrycode 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 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

7.1.5 eq_ref : 多表连接时,右表中的on的连接条件是主键或唯一键

damao[world]>desc select * from city join country on city.countrycode=country.code where city.population<100;
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city    | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | country | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.city.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

7.1.6 const(system): 聚簇索引或者唯一索引的等值查询

damao[world]>desc select * from city where city.id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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.00 sec)

7.2 key_len 计算规则

反应的是,在联合索引应用长度 (字节)
    
          not null      null 
tinint    1bytes        1+1
int       4bytes        4+1     

create table t1(
n1 int not null,               4
n2 int ,                       5 
n3 tinyint not null            1 
)
 
utf8           not null      null
char(10)       30            31
varchar(10)    30+2          33

create table t1(
n1 char(20) not null,           20*3      60
n2 int ,                        4+1       5
n3 varchar(30)                  30*3+2+1  93
)

utf8mb4        not null      null
char(10)       4*10            40+1
varchar(10)    4*10+2          40+2+1

create table t1(
n1 char(20) not null,           20*4       80
n2 int ,                        4+1        5
n3 varchar(30)                  30*4+2+1   123
)

联合索引应用规则:

1. 把唯一值多的放在最前面
2. 理论上要将不等值的列放在最后面
3. key_len 覆盖长度越长越好.
5. 查询条件结果能够全部从辅助索引覆盖最好,减少回表次数.
6. where配合groupby 或者order by ,按照子句执行顺序联合索引.
    where b   group by a 
    where b   order by a

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

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

9. 索引应用规范

9.1 建立索引的原则

9.1.0 说明

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

9.1.1(必须的) 建表时一定要有主键,一般是个无关列

9.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

9.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段

排序操作会浪费很多时间。
where  A B C      ----》 A  B  C
in 
where A   group by B  order by C
A,B，C

如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

9.1.4 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

9.1.5 限制索引的数目

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

9.1.6 删除不再使用或者很少使用的索引（percona-toolkit）

pt-duplicate-key-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

9.1.7 大表加索引,要在业务不繁忙期间操作

9.1.8 尽量少在经常更新值的列上建索引

9.1.9 建索引原则

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

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

9.2.1 没有查询条件，或者查询条件没有建立索引

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列建立索引

9.2.2 查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

9.2.3 索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,，统计数据不真实
DML ?   --->锁冲突

9.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

9.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

**注意数据类型**

mysql> select * from t where telnum=12306;
mysql> select * from t where telnum='12306';

9.2.6 <> ，not in 不走索引（辅助索引）

特殊情况: 对于主键列,也是可以走range范围查询

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

9.2.7 like "%_" 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

最后编辑于：2019.11.18 19:55:08

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,277评论 6赞 503
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,689评论 3赞 393
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 163,624评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,356评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,402评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,292评论 1赞 301
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,135评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,992评论 0赞 275
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,429评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,636评论 3赞 334
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,785评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,492评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,092评论 3赞 328
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,723评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,858评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,891评论 2赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,713评论 2赞 354

数据库学习day04：SQL基础优化-索引及执行计划

1. 什么是索引?索引的作用?

2. 索引算法的演变

3. MySQL支持的索引类型

4. MySQL中的BTREE如何构建

4.1 聚簇索引

4.2 辅助索引

5. 索引管理

5.1 索引的查询

5.2 创建索引（辅助索引）

5.2.1 辅助索引的细分

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

5.4 联合索引

6. 执行计划获取及分析

6.1 执行计划之table

6.2 执行计划之type(查询类型) ******

6.3 执行计划之possible_keys

6.4 执行计划之key

6.5 rows

6.6 Extra *****

7. 执行计划重要信息详解

7.1 type类型

7.1.1 ALL ：全表扫描

7.1.2 index ：全索引扫描

7.1.3 range: 索引的范围扫描

7.1.4 ref : 辅助索引等值查询

7.1.5 eq_ref : 多表连接时,右表中的on的连接条件是主键或唯一键

7.1.6 const(system): 聚簇索引或者唯一索引的等值查询

7.2 key_len 计算规则

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

9. 索引应用规范

9.1 建立索引的原则

9.1.0 说明

9.1.1(必须的) 建表时一定要有主键,一般是个无关列

9.1.2 选择唯一性索引

9.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段

9.1.4 尽量使用前缀来索引

9.1.5 限制索引的数目

9.1.6 删除不再使用或者很少使用的索引（percona-toolkit）

9.1.7 大表加索引,要在业务不繁忙期间操作

9.1.8 尽量少在经常更新值的列上建索引

9.1.9 建索引原则

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

9.2.1 没有查询条件，或者查询条件没有建立索引

9.2.2 查询结果集是原表中的大部分数据，应该是25％以上。

9.2.3 索引本身失效，统计数据不真实

9.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

9.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

9.2.6 <> ，not in 不走索引（辅助索引）

9.2.7 like "%_" 百分号在最前面不走

推荐阅读更多精彩内容