希望长大对我而言,是可以做更多想做的事,而不是被迫做更多不想做的事...... 首页 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; ``` #### 执行计划分析  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执行顺序  不是绝对的有时候,优化器也会执行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 ```  ``` SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10000 ```  第一条使用了索引,第二条没有使用索引。为什么呢? 这第二条是因为我们这个是非聚集索引,扫描完索引之后还需要,根据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都不会使用索引。但是事实却不是。 第一条,不是使用索引  第二条使用type 为index的索引  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级别 很赞哦! (33) 上一篇:Elasticsearch倒排索引比mysql快 下一篇:spring cloud网关之zuul 目录 点击排行 redis哨兵 Redis+Twemproxy+HAProxy+Keepalived 2019-07-12 17:20 GC优化策略和相关实践案例 2019-10-10 10:54 JVM垃圾回收器 2019-10-10 10:23 Elasticsearch6.3.2之x-pack 2019-09-24 11:12 标签云 Java Spring MVC Mybatis Ansible Elasticsearch Redis Hive Docker Kubernetes RocketMQ Jenkins Nginx 友情链接 谭林超个人博客 郑晓博客 龙哥 佛布朗斯基 凉风有信 MarkHoo's Blog 冰洛博客 文轩设计之路 Rui | 丁D Java研发工程师 生活可以用「没办法」三个字概括。但别人的没办法是「腿长,没办法」、「长得好看,没办法」、「有才华,没办法」。而你的没办法,是真的没办法。 请作者喝咖啡