01、MySQL-------性能优化

一、影响性能的相关因素

存储过程：

存储过程：类似可以在数据库里面写代码的一种脚本，在Mysql里面可以封装一个函数，在这个函数里面可以写很多的逻辑。可以把所有的业务放在存储过程里面去实现。

缺点：数据库很耗资源。数据库的连接是有上限的，一个请求调用一个存储过程去执行业务逻辑，意味着单个线程响应的时间变长，在并发高的情况下，每个线程返回的时间都变长的话，意味着数据库的连接很快就被用完了，那么其他现在就会进入等待，那么在应用层面就会积压很多线程，意味着应用中的线程会被耗尽，那么外面的请求就进不来了，就会导致服务器的雪崩，宕机。

在互联网应用里面并不是多么好，可以不在数据库中作的事情，就不要在数据库做

如果是传统行业，并发比较小，那么使用存储过程，在sql里面调用函数就也还行。

存储过程中调用的函数有哪些？

case when

表的设计也是影响性能的因素

账号密码修改频率低的，可以设计在一个实体类里面（一张表里面），可以放在缓存里面。

修改频率高的放在一个实体类里面，不放缓存。

应用层面的设计也是影响性能的因素

如果应用层面（代码）没设计好，所有的请求都落到数据库，那么sql优化的再好也顶不住。

代码规范、mysql规范、字段规范

二、sql优化

1>、Mysql系统架构

2>、引擎

区别：

数据库默认是Innodb引擎。

3>、索引

1、什么是索引？

能帮我们快速的定位数据，它是一种新的数据结构。

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。

联合主键索引理解：

一张表永远只有一个主键索引。

除了主键索引外的其他索引都可以叫做辅助索引。

创建了索引的字段，最好设计成不为null，最好把【不是null】选项勾选上

这种情况叫做联合主键，这三个加起来才是主键，才是唯一主键，相当于 【组合索引】 一样

索引长度理解：

什么是慢查询？

mysql可以主动开一个慢查询，慢查询就是

超过指定时间的sql就会记录到日志里面

1）、索引理解

Navicat里面添加索引

BTREE 底层就是B+TREE

如图，给这个字段加上索引（就是把这个字段作为一个索引），然后数据库底层就会把这个字段的数据另外弄成一个二叉树的数据结构， 那么在查询的时候，如果把这个字段作为查询条件，数据库就会直接去这个二叉树数据结构找该数据。

比如表有10000条数据，有十条数据有该字段，那么没给该字段加索引前，数据库需要遍历全表，遍历这10000条数据，但是如果把该字段弄成索引，那么就只需要遍历这个二叉树而已，就是只需要遍历10次就行，效率就大大提升了。

2）、树高

为什么千万级别的数据，只需要3层树高就可以？

树高是4的话，有3次IO。

如图，下面的图的树高是4，IO次数就是3.（因为根节点已经被缓存了，所以IO次数就是4-1=3）

因为非叶子节点只存储指针，而每个节点属于一次io，一次io有16k，3次io就可以存有千万级别的数据对应的指针

这个点不是很理解？

**磁盘IO次数=树的高度 ** 还是 磁盘IO次数=树的高度-1 ？

读取每次节点，都相当于一次的磁盘IO，搜索整棵树，路径长度为树高，磁盘IO次数=树的高度，树高越矮，磁盘IO次数越少，性能就越高。

​ 比如树高=4，经历了3次磁盘IO，因为mysql会把根节点（最上面的非叶子节点叫根节点）缓存起来，所以树高为4的话，磁盘IO次数为3。所以根节点被缓存起来后，树高就变成3，io次数也是3，所以磁盘IO次数=树的高度

为什么树高=4，只经历了3次IO？

因为根节点被缓存起来了，所以树高就变成3了，IO次数也就变成3了。

问题：那第一次查询的时候，树的根节点应该还没被缓存起来，所以树高=4，IO次数也是4吗？

3）、在线二叉树

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

磁盘IO次数=树高-1

总结

1、索引数据结构都是B+Tree

2、为什么不用二叉树或者红黑树呢？

​ 二叉树特定情况下会变成单向链表，性能低，可以用更好的数据结构 B+Tree。

3、有BTree和B+Tree，有什么区别，为什么选择B+Tree？

​ BTree 的非叶子节点是存实际数据的，会导致树的高度更高，那么磁盘IO次数就会越多，效率就会减低

​ B+Tree 中 非叶子节点是不存储实际的数据，存储的是值和指针（因为体积小，那么树高就更低，磁盘IO次数就更少，查询效率就更快）

​ MySql默认一次的磁盘IO是16k，16k固定的情况下，非叶子节点体积越小，能容纳数据就越多（树的阶越大，树高越矮，这个【阶】就是非叶子节点里面存的数据多少）。

​ 读取每次节点，都相当于一次的磁盘IO，搜索整棵树，路径长度为树高，磁盘IO次数=树的高度-1，树高越矮，磁盘IO次数越少，性能就越高。

​ 比如树高=4，经历了3次磁盘IO，因为mysql会把根节点（最上面的非叶子节点叫根节点）缓存起来，所以树高为4的话，磁盘IO次数为3。

4）什么是索引覆盖？

索引覆盖：辅助索引里面存的值，就已经包括了我需要的字段了（如图就是该字段是设置为辅助索引，而sql要查的字段就刚好是它），所以就不用再通过索引里面的值去回表找主键索引来查询数据，

因为辅助索引（字段）里面的值就已经是我们想要的数据了，再回表去查更多的数据也没有什么意义。

也可以简单的理解为：索引覆盖就是不用再进行回表查询，就是不用通过辅助索引的值去主键索引那里查完整的行数据。通过辅助索引就能找到对应字段的列的数据（前提：查询的该字段也是一个索引）

分析图：

覆盖索引的解释：

5）什么是回表查询？

一个表只有一个主键索引，其他的我们自己添加的索引都可以叫做辅助索引（比如复合索引这些都能叫做辅助索引）

回表查询就是 通过辅助索引的叶子节点里面存的主键值，到主键索引哪里找到对应的数据。

主键索引里面存的值就是完整的一行数据

2、Mysql索引实现

主键索引就是表的id

辅助索引就是我们自己添加的索引

1）、MyISAM索引实现

MyISAM的主键索引和辅助索引找数据的分析图

这里的主键找到数据的索引，还得根据索引的物理位置在另外的文件（.myd文件）去找具体的数据，比Innodb慢

2）、Innodb索引实现

Innodb的主键索引和辅助索引

3）、聚簇索引

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。

跟上面分析的索引实现一样

通俗讲:
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应

3、索引的利弊

索引是独立于我们数据之外的另外一种数据结构，需要存储空间的。

新增、修改等操作都要重新生成索引树，需要额外消耗资源（弊），但是相比我们查询所带来的性能的提升（利），这点消耗可以忽略不计。

索引的好处：
1，提高表数据的检索效率；
2，如果排序的列是索引列，大大降低排序成本；
3，在分组操作中如果分组条件是索引列，也会提高效率；

**索引的问题：**索引需要额外的维护成本；

字段修改的频率比查询的频率高的话，就不用建立索引。

4、创建索引情况

1，较频繁的作为查询条件的字段应该创建索引；

2，唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

​ 比如：查询 男和女 ，筛选完还是有海量的数据

3，更新非常频繁的字段不适合创建索引；

​ 添加索引的话，频繁修改就需要频繁重新生成索引树，消耗的资源 大于 查询的性能带来的收益

4，不会出现在WHERE 子句中的字段不该创建索引；

​ 都不作为查询条件，更没有必要添加索引

5、单值索引和组合索引

单值索引

只是把一个字段作为索引，这个就是单值索引

分析图：

组合索引

也叫复合索引

这个索引就是组合索引，组合索引用的比较多。

分析图：

组合索引比单值索引查询要快，但是要记得组合索引得符合【最左原则】

最左原则：where后面查询的第一个字段（不用按where顺序）要和组合索引的第一个字段相同。

注意这个情况，也是符合最左原则的，最左原则是要条件符合，不是说写的顺序要符合。

比如：组合索引（A、B、C），那么where条件 （ABC ACB BAC CBA 等）都是符合最左原则的。

组合索引（A、B、C），那么where条件 （BC CB BC 等）里面都没有A，查询的时候无法命中组合索引中的A索引字段，那么这个才叫做不符合最左原则

最佳创建组合索引

应该按照最高频的字段来创建组合索引，最大程度去覆盖到sql的查询条件，这样查询性能也能提升，生成的索引树成本也能降低。

4>、Explain命令

示例数据库：

数据下载:https://github.com/datacharmer/test_db

MySQL官方文档中提供的示例数据库之一：employees

使用命令导入数据:
mysql -uroot -p123456 -t < employees.sql
1
2
3
4
5
6

Explain命令的作用：通过这个Explain命令来查看sql的执行计划，看能怎么对sql进行优化。

Explain命令主要来查看SQL语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描等。它可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理用户的SQL语句

1)select_type:查询类型

1、union

union是产生的两个记录集(字段要一样的)并在一起，成为一个新的记录集

union是将表内容拼接成一列（上下拼接），也是根据字段共同属性进行将表与表之间数据进行上下拼接

2、subquery

子查询中的第一个select，其select_type为SUBQUERY

3、dependent subquery 和 dependent union

子查询只有一个select ，第一个select 就是 subquery

子查询有多个select ， 第一个 select 就是 dependent subquery，

第二个select 使用 union 连接的话，那就是 dependent union。

dependent 表示依赖的意思。表示 最外面的select 依赖于 子查询中的结果。

4、derived

当这个语句查询出来的数据作为别人的表的时候（查询的数据跟在from后面），相当于派生表的类型

2）type：联接类型

联接类型

1、system 和 const

system：查询速度最快，直接从内存中查询获取到数据

const：通过主键索引查询到数据

2、eq_ref

条件是：唯一索引

两张表关联的情况下（比如join之类的），查询条件的这个字段刚好是主键字段的情况下，或者是唯一字段的情况下，那么type的类型就是 eq_ref

eq_ref：表示查询的那个条件属于该表的【主键字段】或者是【唯一字段】，

因为该字段属于唯一主键，匹配这个主键成功就能直接找到对应的数据，相当于 一对一 的情况。

3、ref

条件：非唯一索引

eq_ref 的性能比 ref 要高

如图：两张表关联查询时，第二张表（titles）的匹配字段（emp_no）属于该表的非唯一性主键索引时，那么这张表的 type 联接类型就属于 ref。

解答：

把那三个字段弄成一个索引，相当于弄成一个【非唯一性主键索引】，那么在查询的时候，类型就是 ref

ref 性能比 eq_ref 低的原因就在于 ref 出现的情况是 第二张表的查询条件是 非唯一性主键索引，因为是非唯一性的，所以在索引树查询的时候，需要匹配如图的三个字段的条件才行，不像唯一性主键索引，只需要匹配一个就行，所以 ref 的性能比 re_ref 低一点。

唯一索引和非唯一索引的区别：

一张表永远只有一个主键索引。

这种叫做唯一索引，

这张表只有这个字段是主键，是主键索引，是唯一索引

这种叫非唯一索引

这种情况叫做联合主键，这三个加起来才是主键，三个加起来才能算是一个【唯一主键】，相当于 【组合索引】 一样

4、ref_or_null

出现的场景：

查询的条件字段有索引，但是设计字段的时候是可以为null，所以如果用该字段作为查询条件的话，就会出现ref_or_null的联接类型

5、index_merge

出现的场景：一张表中有多个查询条件且都有索引时，会出现 index_merge 索引合并类型。

针对or导致的索引失效的分析图

OR 前后只要存在非索引的列，都会导致索引失效

测试：

6、range

按指定的范围进行检索，就会显示未 range类型

7、index

ALL 是全表扫描数据，index 是全表扫描索引

性能上面，index 仅仅比 ALL 好上一点而已

8、ALL

all 类型，就是一个全表扫描，跟用没用索引一个样。

3）Extra详细信息

1、Not exists

查询的条件是冲突的，一个是主键，是非空的（employees），一个要查这个字段为null的，主键不可能为空，就不再继续扫描该表（dept_emp）了。

mysql根据语义发现这种查法是查不出数据的，就显示为 【Not exists】

2、Range checked for each record

范围查询覆盖了整张表，mysql表示没有好的索引可以用，就显示这个【Range checked for each record】

3、Using filesort

这里的sql是根据字段进行分组，所以这里显示的 【Using filesort】 表示按照字段进行排序

（这个解释比较笼统，后续可以研究）

4、Using index

这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时就会显示【Using index】

5、Using temporary

查询的时候，Mysql需要创建一张临时表来处理该查询时会显示【Using temporary】

临时表是比较消耗资源的

6、Using where

当sql有where子句时，extra都会有说明。

但是这两个where字句，一个有显示 Using where ，一个没有，不太理解

7、Using union

一张表中有多个查询条件且都有索引时，会出现type类型为 index_merge ，进行索引合并类型，

Using union出现的场景 ：

这里也一样，会使用union函数进行索引合并，跟type联接类型为 index_merge 一样

8、Using index for group-by

Using index for group-by 这个也可以理解为【索引覆盖】

索引覆盖：索引已经包括了我需要的字段了，就不用再通过索引去回表找主键索引来查询数据

分析图：

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

覆盖索引的理解：

4)命令汇总：

按顺序：从上到下，性能最好的是 system ， 性能最差的是 ALL

三、Join理解及优化

1、Inner join

谁是小表谁是大表，MySQL Optimizer会自动判断，和我们写表的顺序是无关的

2、join

join 和 inner join 一样的结果，谁是小表谁是大表，MySQL Optimizer会自动判断，和我们写表的顺序是无关的

3、where

where、join 和 inner join 一样的结果，谁是小表谁是大表，MySQL Optimizer会自动判断，和我们写表的顺序是无关的

4、left join

left join 就不用解释了，就是左表驱动右表，以左边的表为基准。

MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL

Optimizer采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表.

5、straight_join

straight_join 是能优化sql性能的

STRAIGHT_JOIN 功能同 inner join 、 join 是一样的，区别只是能让左边的表来驱动右边的表，能改变 表优化器 对于 联表，其他的作用和 inner join 、 join 的效果都是一样的

这个是 dept_emp 为驱动表

因为 无论是 Inner join 还是 straight_join , 用 Explain 命令看的话，都是以 dept_emp 为驱动表，所以两者的耗时其实是差不多的。

因为这条sql语句 无论是用 inner join 还是 straight_join ， 都是以 dept_emp 为驱动表，所以看不出使用 straight_join后的性能提升。

所以我用 straight_join 把employees 表弄成驱动表，那么结果应该就是 驱动表为 employees 的sql语句性能比较低。如图也能看出

注意： straight_join 是用来让左边的表作为驱动表的，跟left join 一样的作用，不过似乎使用 straight_join的性能会比使用 left join 的性能要高。

6、场景优化情况

优化情况：添加索引，或者是更改驱动表

优化情况：根据情况设置缓冲区大小

7、Show profiles：比较性能

Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况,可以用于SQL的调优测量

四、一些索引测试

1、模糊查询like

用like做查询时，通配符% 放在字段值后面（样子为—>xxx%），进行前缀查询，索引就能使用，

前缀查询的样子abc%，就可以拿a、b、c先去索引树进行匹配，所以索引就可以使用。

如果把通配符%放在字段值最前面来进行后缀查询（样子为—>%xxx），那么索引就会失效。

比如后缀查询的样子是 【%abc】，我们根本不知道%是什么值，也就没办法在索引树进行比对，所以索引就会失效

2、范围查询

>、<

进行范围查询后，后续的查询条件的字段的索引就会失效 ，就不会再走索引

大于和小于 会导致后面的索引失效

between

between 不会导致后面索引失效

3、函数

使用函数会导致该字段的索引失效，如图

4、or & and

**使用or不会导致索引失效的情况：**就是or左右的查询条件的字段都有加索引（单值索引 或者 复合索引（复合索引要符合最左原则才行））

使用or导致索引失效的情况： or左右的查询条件的字段有一个以上没有加索引或者不满足索引规则，那么使用or就会导致索引失效

or 会导致索引失效的情况

如上图，如果想让 这个 or 不导致索引失效，我们可以单独给这个title 加上一个所以就可以了。

如图：

5、算数表达式

在该字段进行算数表达式也会导致索引失效

如图：

五、索引使用策略及优化

1、索引选择性

索引的选择性是指索引列中不同值的数目与表中记录数的比。

如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。

比如有2000条数据，值记录的是男和女，那么这个表索引列就是有2个不同的值，索引选择性就是 2/2000，就是一千分之一，表明索引的效率很低。

如果是20000条数据，那么就是 2/20000 等于一万分之一

一个索引的选择性越接近于1，这个索引的效率就越高。

既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？

答案是否定的。

因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

情况1：如果表的记录比较少，少于2000条数据，那么就没必要建立索引。

情况2：超过2000条数据的表，也要看索引的选择性，索引的选择性太低也没必要建立索引。比如男和女这种。

除了选择性需要考量，这个索引的长度也要考量，因为索引的长度越长，那么需要消耗的空间就越多。

索引长度理解：

2、前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

前缀索引分析：

需求： first_name 和 last_name 字段这些字段来查询数据。

1、根据两个字段查询数据，没有添加索引的情况下，耗时是0.068s

2、根据情况看是否要给字段建立单值索引，可通过计算出索引选择性的大小来看。

查看 first_name 和 last_name 字段的索引选择性，发现都太小，不适合建立单值索引。

3、复合索引

复合索引符合要求，索引选择性达到了0.9313，可以建立，但是可以看到两个字段的长度加起来是30，弄成符合索引之后，因为长度越长越消耗空间。

有没有兼顾长度和选择性的办法？

可以考虑用fifirst_name和last_name的前几个字符建立索引，例如，看看其选择性

4、优化复合索引，减少复合索引的长度

5、再对比下查询性能，速度提升了 278倍

6、注意点

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于

Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）

3、自增id作主键的原因

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

为什么要用一个自增的id作为主键？

解释：

自增id：
涉及到磁盘的位置，因为如果输入的位置是连续的话，可以把一个位置用完之后，才会再去申请下一块位置，因为位置是连续的。

uuid：
因为不是连续的，所以存入的位置不会按顺序插入，可能这个位置插一个数据，另一个位置插入一个数据，可能导致的结果就是 一个位置还没用完，数据就会插到另一个位置。
这样会产生很多空间的碎片，就是一个区域还没用完，就会去重新申请新的一块区域，导致空间浪费。

比如行李箱，一件一件摆好的话，存的衣服会更多（自增id），如果随便丢进行李箱，那么能塞进去的衣服就会比较少（uuid），一些空间缝隙没利用到

为什么要用自增id作为主键的分析图

六、其他优化

1、order by 优化

order by 作用：对指定列进行排序

ORDER BY 实现原理：
1，通过有序索引而直接取得有序的数据；
2，通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回；

双路排序：

顺序读性能比乱序读高

内存排完序之后，需要回表进行乱序读，查询需要的字段。

单路排序：

单路排序没有 row_id这个磁盘地址

因为 sortBuffer 是把表的整行数据都读过去的，所以在内存排完序的结果就是最终的结果，不用跟双路排序一样还得回表查。

单路排序速度比双路排序要快，不过比较占内存。

区别：

order by 怎么选择 单路排序还是双路排序？

MySQL根据设置的【max_length_for_sort_data】变量来确定走【单路排序】还是【双路排序】，默认值是1024字节，

如果需要返回的【列的总长度】（就是元组长度） 大于 【max_length_for_sort_data】，走【双路排序】，

否则走【单路排序】。

查看：show variables like ‘max_sort_length’;

单路排序算法：sort buffer 缓冲区缓存整个行的数据，在根据排序字段进行排序，返回数据给客户端，

缺点：占用内存

优点：避免回表查询

双路排序算法：sort buffer 缓冲区只缓存主键id和排序字段在内存中，在根据排序字段进行排序，在做一次回表查询，根据主键id查询该行数据返回给客户端。

区别1：

查询返回的字段如果比较多，那么 order by 就是走【双路排序】，

查询返回的字段如果比较少，那么 order by 就是走 【单路排序】。

区别2：

双路排序有值的磁盘物理地址，单路排序没有。

**元组：**双路排序的元组是 row_id（磁盘物理地址），单路排序的元组就是那些字段，所以单路排序的元组比双路排序的元组长

解释：

单路因为元组比较长，所以需要占的空间就比双路排序的多，在同样的内存大小的 Sortbuffer内存缓冲区 中，单路存的元组就比双路的少，所以产生的临时文件就会比双路的多。

但是双路排序有顺序读和乱序读，所以它的磁盘io次数是比单路排序的要多。

所以总的来说，单路排序的性能会比双路排序的性能会好些，但是还是得看情况而定

1）双路排序会读取两次表， 且第二次是随机读取的
2）单路排序的元组比双路排序要长，导致它需要多次向临时文件写入内容，增加IO操作，当需要返回的列的总长度很长时尤其明显。

提问：

问题：如果一次排序的顺序高达1万条，10万条的数据，能一次性加载到内存中吗?

答：

如果要排序的数据量太多的话，不可能一次性把数据都读取到内存，这样会把mysql的服务器的内存一下子就占满了。

MySQL会进行分批次的读取，比如有1万的数据，在内存可能会一次读1千，然后执行完得到最终结果后，会把排好序存储在一个临时文件中（1千条排序后的结果）。

然后再重复上面的操作，1万条数据，一次1千的分批读，那么就会产生10个临时文件，然后再针对这10个临时文件重新读取，然后在内存中进行归并排序，这个时候的性能肯定比直接一万条数据直接排序的性能更高。

数据量多的情况下，单路排序和双路排序都是这么分批次处理的。

​

优化：

ORDER BY 实现原理：
1，通过有序索引而直接取得有序的数据；
2，通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回；

优化方案：
1，加大max_length_for_sort_data 参数

（加大这个参数，那么就提高比元组长度大的概率，所以就提高走单路排序的概率）

2，去掉不必要的返回字段

（不必要的返回字段越多，元组长度就越长，占的空间就越多，导致排序缓冲区每次读取的数据量变小）

3，增大sort_buffer_size （排序缓冲区）参数，

（增大的话，排序缓冲区一次性能读取的数据就会变多）

增大 sort_buffer_size 和 max_length_for_sort_data 参数，尽量走单路排序，减少临时文件产生，减少IO，尽量避免走双路排序。

2、using temporary

http://mysql.taobao.org/monthly/2015/03/04/

3、group by 优化

分组

GROUP BY的前提是排序,所以优化手段和ORDER BY是一样的。