sql优化

优化顺序

首先定位系统的瓶颈，判断引起数据库瓶颈的原因

---

1. 业务优化（定位瓶颈后，判断能否从业务上进行优化，减小对数据库的压力）
2. 设计、程序优化（根据应用访问特点，从设计和实现上优化对数据库的访问）
3. DB、SYSTEM优化（对数据库处理能力进行升级：io，cpu，mem）
4. SQL优化（调整索引，优化sql）

---

结果：从下往上优化，见效快；从上往下优化，更加彻底

拆分

1. 绝大多数的应用不需要分库分表
2. 保持简单应用架构
3. 先优化，在考虑拆分
4. 优先考虑垂直拆分，在水平拆分
5. DRDS帮助你解决水平拆分的难题

性能优化

分页优化

原SQl：
SELECT * FROM `original_orders` WHERE created_at >= '2020-09-08 15:32:20' AND created_at <= '2021-09-08 15:32:20' LIMIT 300000,5000;
共 5000 行受到影响
执行耗时   : 1.005 sec
传送时间   : 3.221 sec
总耗时      : 4.226 sec

新SQL：
SELECT b.* FROM (SELECT id FROM `original_orders` WHERE created_at >= '2020-09-08 15:32:20' AND created_at <= '2021-09-08 15:32:20' LIMIT 300000,5000) a,`original_orders` b WHERE 
a.id = b.id;
共 1000 行受到影响
执行耗时   : 0.302 sec
传送时间   : 0.314 sec
总耗时      : 0.617 sec

原理：普通limit M，N的翻页写法，往往在越往后翻页的过程中速度越慢，原因 mysql会读取表中的前M+N条数据，M越大，性能就越差；优化后的翻页写法，先查询翻页中需要的N条数据的主键id，在根据主键id 回表查询所需要的N条数据，此过程中查询N条数据的主键ID在索引中完成

Order By 优化

原SQL：
SELECT a.* FROM `original_orders` a WHERE a.created_at >= '2020-09-08 15:32:20' AND a.created_at <= '2021-09-08 15:32:20' ORDER BY a.`id` LIMIT 300000,5000;
共 5000 行受到影响
执行耗时   : 1.017 sec
传送时间   : 3.149 sec
总耗时      : 4.166 sec

新SQL：
SELECT a.* FROM `original_orders` a WHERE a.created_at >= '2020-09-08 15:32:20' AND a.created_at <= '2021-09-08 15:32:20' ORDER BY a.`id` LIMIT 300000,5000;
共 5000 行受到影响
执行耗时   : 0.522 sec
传送时间   : 3.160 sec
总耗时      : 3.683 sec

原理：如果mysql查询数据的顺序与order by的字段一致的， 也就是说在排序字段上有索引，mysql沿着索引的顺序读取下来，当满足了 查询中N条记录，则立刻返回查询。

根据过滤性创建索引

原SQL：
SELECT * FROM `original_orders` WHERE order_status IN (3,4) AND check_status = 2 AND order_from = 1 ORDER BY created_at,out_time;

创建联合索引：
ALTER TABLE `ferp`.`original_orders`
  ADD INDEX `union_1` (
    `order_from`,
    `order_status`,
    `check_status`
  );

原理：

1. 创建索引的时候，优先将过滤性高的查询字段放在首位；
2. mysql的索引有前导列的限制，优先将等值的条件的字段放在 索引顺序的前列；
3. 若有>,<,not in,between,!=的查询条件的字段加入索引列后， 后续加入的索引字段都不能起到过滤作用；

PS：暂时还有点疑惑，待以后业务场景验证

避免函数计算

原SQL：
SELECT * FROM `original_orders` WHERE DATE_FORMAT(created_at,'%Y-%m-%d %H:%i::%s') = '2020-09-08 15:32:20';
执行耗时   : 0.597 sec
传送时间   : 0 sec
总耗时      : 0.597 sec
结果：无法使用created_at索引，会遍历表中所有数据查询

新SQL：SELECT * FROM `original_orders` WHERE created_at = '2020-09-08 15:32:20';
执行耗时   : 0.041 sec
传送时间   : 0 sec
总耗时      : 0.041 sec
结果：使用created_at索引，只会根据索引查到表中符合条件的几条数据

原理：

1. mysql不支持函数索引，所以在查询条件中加入函数计算，则无法使用到索引；

避免隐士转换

原SQL：
SELECT * FROM `original_orders` WHERE receiver_mobile = 15067079608;
执行耗时   : 0.202 sec
传送时间   : 0.290 sec
总耗时      : 0.492 sec
结果：无法使用receiver_mobile索引，造成了全表扫描

新SQL：
SELECT * FROM `original_orders` WHERE receiver_mobile = '15067079608';
执行耗时   : 0.036 sec
传送时间   : 0 sec
总耗时      : 0.036 sec
结果：使用receiver_mobile索引，只会根据索引查到表中符合条件的几条数据

原理：

1. 我们知道数字的精度是比字符串高的，所以这里做了隐士转换；receiver_mobile本身为字符串类型，现在参数值为整数类型，则会默认to_number将字符型转为整型；
2. 发生隐士转换，会导致索引无效，其原理类似于在查询字段上加上了一个函数，所以应该在设计编码阶段注意；
3. 常见的隐士转换：字段定义为字符，而传入条件为数字

避免无索引

1. 所有上线的sql都要经过严格的审核，创建合适的索引，这是保障数据库运 行稳定的基本要求；
2. 可以通过explain查看sql的执行计划，判断是否使用到了索引；

避免子查询

普通的mysql子查询写法性能上是很差的，为mysql的子查询天然的弱点，需要将sql进行改写联表式查询,即直接将原子查询语句当做一个临时表用;

死锁问题

1.通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免;

2.发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务;

3.用户发现自己的网站经常由于出现死锁，而导致部分用户无法进行正常的业务逻辑，

查找问题：通过在show engine innodb status发现出现死锁的sql：

解决方法： 该系统先是查询出需要更新的记录，锁住该条记录（for update 其他session不能读取和更新），然后在对该条记录进行更新；建议用户将悲观锁的更新方式换为乐观锁的更新方式：（先查出记录，更新此记录的时候根据原值当条件查询，当然也可以使用版本号或者cas算法来解决乐观锁的并发操作问题）；乐观锁多用于多读少写场景，悲观锁多用于多写场景;

锁问题

导致结果：明明实例资源还很多，但是应用程序调数据库操作明显变慢

查找问题：出现问题的sql以及用户反馈为：(可以通过show processlist查看),查找出哪些sql明显很慢

解决方案一：首先排查表中是否有索引；

解决方案二：排查是否有事务未提交或者正在一个大事务，导致更新操作被锁住

索引基本设计原则

1,SQL语句尽可能简单

2,保持事务(连接)短小

3,尽可能避免使用SP/TRIG/FUNC

4,尽量不用 SELECT * •改写OR语句为in

5,避免负向查询和% 前缀模糊查询

6,减少COUNT(*) •尽量不用外键