当前位置: 首页>后端>正文

MySQL优化看这篇就对了

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

一、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;

执行计划分析

[图片上传失败...(image-5181eb-1586497621890)]

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

? index:索引全扫描,MySQL遍历挣个索引来查询匹配的行,跟all相比就差了个排序,因为索引本来就是有序的

? range:索引范围扫描,常见于<、<=、>、>=、between等操作符

? ref:使用非唯一索引或唯一索引的前缀扫描,返回匹配的单行数据,这个就是我们平时理解的索引查询方式B+树二分法查询

? eq_ref:似ref,区别就在于使用的索引是唯一索引,简单来说,就是多表连接中使用primary key或者unique index作为关联条件。

? const/system:单表中最多有一个匹配行,查询起来非常迅速,常见于根据primary key或者唯一索引unique index进行的单表查询

? null:mysql不用访问表或者索引,直接就能够得到查询的结果,例如select 1+2 as result。

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

? Using index:直接访问索引就取到了数据,高性能的表现。

? Using where:直接在主键索引上过滤数据,必带where子句,而且用不上索引

? Using index condition:先条件过滤索引,再查数据,

? Using filesort:使用了外部文件排序 只要见到这个 就要优化掉

? Using temporary:创建了临时表来处理查询 只要见到这个 也要尽量优化掉

SQL执行顺序

[图片上传失败...(image-eec99-1586497621891)]

不是绝对的有时候,优化器也会执行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表做比较

? 不要在列上做运算where a-10 = 20 这样不使用索引,换成 where a=20+10

? 类型要一样 where a=123 如果a是varchar类型,这样就不会使用索引 换成 where a=‘123’

? IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况,这样效率会高的

? 能够用BETWEEN的就不要用IN

? 能够用DISTINCT的就不用GROUP BY

? 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符.

? select 列,使用覆盖索引,减少回表查询.

一张表最多只存多少数据,为什么使用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

[图片上传失败...(image-292c4a-1586497621891)]

SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10000

[图片上传失败...(image-7e0c4-1586497621891)]
第一条使用了索引,第二条没有使用索引。为什么呢?
这第二条是因为我们这个是非聚集索引,扫描完索引之后还需要,根据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都不会使用索引。但是事实却不是。
第一条,不是使用索引
[图片上传失败...(image-3242e0-1586497621891)]

第二条使用type 为index的索引
[图片上传失败...(image-5d10b6-1586497621891)]

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


https://www.xamrdz.com/backend/3zt1923355.html

相关文章: