MySQL优化看这篇就对了

最新推荐文章于 2023-06-06 07:45:00 发布

xxxjd

最新推荐文章于 2023-06-06 07:45:00 发布

阅读量224

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/u011356878/article/details/103729678

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

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

执行计划分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ovfT4zVI-1577418393242)(/upload/微信图片_20191224155303.png)]

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

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

SQL执行顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NHs6ktgd-1577418393247)(/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

常见的优化方式

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l7TEeYNU-1577418393250)(/upload/20191225111541.png)]

SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10000

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6bQ8K6Qv-1577418393251)(/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都不会使用索引。但是事实却不是。
第一条，不是使用索引
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E9jGNRUy-1577418393256)(/upload/20191225105202.png)]

第二条使用type 为index的索引
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2ysZ3thX-1577418393257)(/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本地缓存

这个比较简单省略

三、读写分离

数据库主从不一致

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

原文地址有图
 原文地址有图
 原文地址有图
 原文地址有图
 原文地址有图

xxxjd

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL优化看这篇就对了

我们在面试的时候经常被问到你如何对数据库优化？动不动就分库分表，但是实际上有几个有分库分表的经验呢？下面我们将介绍优化数据库的各个阶段。一、SQL语句优化sql语句的优化是我们优化数据库的第一个阶段，也是要最先考虑的方案，成本最低，见效最快的方案。1.通过慢查询日志，找到我们的慢sql2.通过EXPLAIN分析执行计划，使用索引。慢查询日志开启vim /etc/my.cnf加入如下三...
复制链接

扫一扫