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

MySQL优化看这篇就对了

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

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级别

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

下一篇：spring cloud网关之zuul