我们在面试的时候经常被问到你如何对数据库优化？动不动就分库分表，但是实际上有几个有分库分表的经验呢？下面我们将介绍优化数据库的各个阶段。
一、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+树
优化器选择不使用索引
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
网友评论