希望长大对我而言，是可以做更多想做的事，而不是被迫做更多不想做的事...... 首页

MySQL优化看这篇就对了

丁D
学无止境
2019-12-27 09:20
63820已阅读

mysql

摘要你知道为什么你写的SQL不使用索引吗？你知道一张表最多存多少数据吗？你知道为什么要使用B+树做索引结构吗？你知道数据库和缓存不一致的处理方案吗？不知道，点我、点我、点我...

我们在面试的时候经常被问到你如何对数据库优化？动不动就分库分表，但是实际上有几个有分库分表的经验呢？下面我们将介绍优化数据库的各个阶段。

### 一、SQL语句优化
sql语句的优化是我们优化数据库的第一个阶段，也是要最先考虑的方案，成本最低，见效最快的方案。
1.通过慢查询日志，找到我们的慢sql
2.通过EXPLAIN分析执行计划，使用索引。

#### 慢查询日志开启
```
vim /etc/my.cnf
加入如下三行：

slow_query_log=ON //开启慢查询
slow_query_log_file=/var/lib/mysql/slow.log //慢查询日志位置
long_query_time=3 //达到多少秒的sql就记录日志,这里是3s

//重启
systemctl restart mysqld;

```
#### 执行计划分析
![](/upload/微信图片_20191224155303.png)

id：值越大越先执行，id相同，从上到下执行
key：使用的索引，为空就是不使用
type：
© all：全表扫描

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息，常用取值如下：

#### SQL执行顺序

![](/upload/20191225100558.jpg)

不是绝对的有时候，优化器也会执行where过滤些数据在join

#### 优化争议无数的count()

count(1)、count(**)、count(列)在innodb引擎中
© count(1)和count(*)直接就是统计主键，他们两个的效率是一样的。如果删除主键，他们都走全表扫描。
© 如果count(列)中的字段是索引的话，count(列)和count(*)一样快，否则count(列)走全表扫描。

MyiSAM引擎的count(*)，因为MyiSAM有记录当前的总行数，所以直接取该值就行，快得一逼，但是这个要在没有where条件的情况下，当统计带有where条件的查询，那么mysql的count()和其他存储引擎就没有什么不同了

#### 优化filesort
当我们使用order by进行排序的时候可能会出现Using filesort，这个时候我们就要将这个优化掉
mysql排序方式有2种
© 直接通过有序索引返回数据，这种方式的extra显示为Using Index,不需要额外的排序，操作效率较高。
© 对返回的数据进行排序，也就是通常看到的Using filesort，filesort是通过相应的排序算法，将数据放在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。

```
SELECT * FROM DB.TB WHERE ID=2222 AND FID IN (9,8,3,13,38,40) ORDER BY INVERSE_DATE LIMIT 0, 5

建立一个索引 IDX（ID,FID ,INVERSE_DATE）这个时候就会出现Using where; Using filesort。

因为建立索引的时候是id排序后，id相同再排FID，当FID有序后，当FID相同在排INVERSE_DATE。

这里id是固定，所以我们重新建立一个索引（ID,INVERSE_DATE），这样就不会出现Using filesort。
```

#### 优化limit 分页
```
select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒
select * from product limit 400000, 20   3.229秒
可以看到随着条数的增加，时间增长
```
一般优化这个有两种
```
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20  0.2秒
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

```
```
加一个参数来辅助，标记分页的开始位置：可以是上一次分页最大时间等，这里用id
SELECT * FROM product WHERE id > 800000 LIMIT 20
```
带有where的语句
```
select id from collect where vtype=1 limit 1000,10;
索引应该这样建立（vtype，id），不要建成（id，vtype）
```
https://mp.weixin.qq.com/s/RVgBc5dOVZbxbnMqftrrOg

#### 优化子查询
大部分的子查询都可以优化成join方式，这样效率会更高。
https://mp.weixin.qq.com/s/KV1elpMKM48tbF6DAlHuwQ

#### 常见的优化方式
© join的时候使用小表作为主表，驱动表。
```
select * from a join b on a.id=b.aid where a.create_time>xxx and b.create_time>xxxx
```
当a根据创建时间过滤后的条数和b根据过滤时间的条数，做比较。。不是直接a，b表做比较

#### 一张表最多只存多少数据，为什么使用B+树，不使用B树
https://zhuanlan.zhihu.com/p/81273236

#### 深入理解mysql B+树
http://blog.objectspace.cn/2019/12/10/%E6%B8%85%E7%A9%BA%E8%AE%A4%E7%9F%A5%EF%BC%8C%E7%84%B6%E5%90%8E%E9%87%8D%E6%96%B0%E7%90%86%E8%A7%A3MySQL%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84/

#### 优化器选择不使用索引
```
SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10

```
![](/upload/20191225111541.png)
```
SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10000
```
![](/upload/20191225111534.png)
第一条使用了索引，第二条没有使用索引。为什么呢？
这第二条是因为我们这个是非聚集索引，扫描完索引之后还需要，根据id去随机读取磁盘（10000次）
而随机读取的性能是很差的。所以sql优化器判断之后使用全表扫描（顺序读取磁盘性能还是高的）

第一条虽然也是这样，但是只需要查询10条随机读取磁盘的次数（10次），相对比较少，所以sql优化器判断之后使用了索引

优化：我们可以使用覆盖索引，让我们b+树的索引存储了索引key，这样我们就不用在回表去查询了
建立（creator_name，run_time）的聯合索引

>满足了使用索引的原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大。

#### join原理 NLJ、BNL、MRR、BKA
https://www.jianshu.com/p/a8ec97f4fde4

#### 颠覆最左原则
t_article表 索引 idnex001(creator_id,updator_id,upator)
```
select * from t_article where updator_id = 1
select updator_id from t_article where updator_id = 1
```
这两条sql会使用索引吗？根据我们理解的mysql最左原则，两条sql都不会使用索引。但是事实却不是。
第一条，不是使用索引
![](/upload/20191225105202.png)

第二条使用type 为index的索引
![](/upload/20191225105313.png)

index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

所以上面两条都满足使用index的原则。

第一条没有使用索引是因为我们查询select * 的话，辅助索引还需要到主键索引进行随机查询。。优化器认为顺序扫描更优，所以没有使用索引

第二条就不需要在要主键索引进行随机查询，所以使用了index类型的索引。

https://www.zhihu.com/question/36996520/answer/93256153

#### mysql 聚簇与非聚簇索引
https://blog.51cto.com/2839840/2057806?utm_source=oschina-app

### 二、引入缓存
在sql优化搞不定的时候，我们才需要考虑引入缓存，但我们要知道当引入缓存的时候系统的复杂性增加了，同时也会引入很多问题，比如数据库和缓存一致性问题等等。

这里很多问题都写过了。。参照下面各个链接

#### mybatis的二级缓存、ehcache本地缓存
这个比较简单省略
#### redis的分布式缓存

Redis安装及持久化

https://xujd.top/article/view.do?VEMnF0tjG1NJE15ORsK9
高可用哨兵
https://xujd.top/article/view.do?QUMnF0tjG1NJE15GBjbCvVpO
redis cluster集群、
https://xujd.top/article/view.do?QEMnF0tjG1NJE15OBsK9
https://xujd.top/article/view.do?UEMnF0tjG1NJE15OJsK9
Redis+Twemproxy+HAProxy+Keepalived
https://xujd.top/article/view.do?QUMnF0tjG1NJE15GBlbCvUdH

#### 数据库和缓存不一致的方案
https://xujd.top/article/view.do?SEMnF0tjG1NJE152FsK9

#### 删除缓存还是更新缓存
一般是删除，更新缓存的代价比较高
https://xujd.top/article/view.do?UEMnF0tjG1NJE152VsK9

#### 先操作缓存（删除缓存）还是数据库

https://xujd.top/article/view.do?UEMnF0tjG1NJE152VsK9

#### 缓存穿透、击穿、雪崩 
https://xujd.top/article/view.do?TEMnF0tjG1NJE152ZsK9

#### 缓存重建冲突（分布式锁）、使用双层nginx提高缓存命中
https://xujd.top/article/view.do?TEMnF0tjG1NJE15OVsK9

### 三、读写分离
#### 数据库主从不一致
https://xujd.top/article/view.do?VEMnF0tjG1NJE152FsK9
#### 从库和缓存不一致（双淘汰方案）
https://mp.weixin.qq.com/s/gQAA2-YuvTHrL2IP8Bco6w

### 四、分区表
https://blog.csdn.net/qq_28289405/article/details/80576614
### 五、垂直拆分
https://blog.csdn.net/qq_28289405/article/details/80576614
### 六、水平拆分
https://blog.csdn.net/qq_28289405/article/details/80576614

### 隔离级别
1.未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
2.提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
3.可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
4.串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

MYSQL默认是RepeatedRead级别

### cpu100%排查
当 cpu 飙升到 100%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的。

如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)， 等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升， 这种情况就需要跟应用一起来分析为何连接数会激增， 再做出相应的调整，比如说限制连接数等。

上一篇：Elasticsearch倒排索引比mysql快

下一篇：spring cloud网关之zuul