1、索引优化
1.1 建表或加索引时，保证表里互相不存在冗余索引。
对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。
1.2 复合索引（组合索引）
建立索引时，多考虑建立复合索引，并把区分度最高的字段放在最前面。
有时候区分度高的字段可能不符合个人或者业务习惯，比如
select * from user where name = '' and sex ='' and age=''；age的区分度明显比sex要高，如果要建立符合索引，则 age应该是在 sex前面。
比如 select * from goods where goods_no = 'aaa' and state=1;
这种情况我们只需要建了一个复合索引就可以，这就相当于创建了(goods_no ,state)、(goods_no )两个索引，这就是最佳左前缀特性。
ALTER TABLE `goods` ADD INDEX `idx_goodsno_state` (`goods_no`,`state`) USING BTREE；
如果索引个数超过5个，可以考虑把多个索引字段拼接后通过md5加密，然后插入表中，这样可以大大提升索引效率。
同时复合索引（组合索引）中的字段尽量避免为null，有些场景下索引可能会失效。所以默认建表时，所有字段都应是 not null 同时给一个默认值，字符串类型默认可以为 ''
1.3 使用短索引
对串列进行MySql索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
1.4 单个表上的索引个数不能超过8个
索引不是越多越好，索引也要占据空间，同时维护索引也需要消耗时间。
1.5 在多表join的SQL里，保证被驱动表的连接列上有索引，这样join执行效率最高。
where条件里等号左右字段类型必须一致，否则无法利用索引
数据类型不一致会导致索引失效
1.6 不要在列上进行运算，否则导致索引失效而进行全表扫描
索引列不要使用函数或表达式，否则无法利用索引。如where length(name)='Admin'或where user_id+2=10023。
再比如我们会在create_tm添加索引，便于按照时间查询，这样情况下，就不要在列上进行格式化
SELECT IFNULL(count(1),0) as sfmSum FROM mg_order_new m1
where DATE_FORMAT(m1.create_tm, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
1.7 LIKE双百分号无法使用到索引
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
1.8 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
1.9 asc和desc混用
select * from _t where a=1 order by b desc, c asc
desc 和asc混用时会导致索引失效，所以跟产品经理沟通时，尽量不要存在这种排序
1.10 不等于、不包含不能用到索引的快速搜索
select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1
在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
1.11 范围查询阻断，后续字段不能走索引
KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10
范围查询还有“IN、between”
KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)
select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10
优化：可以(order_status, created_at)互换前后顺序
2、sql优化
2.1 读取适当的记录 limit
假如我们确定记录只有一条，那还是要习惯加上limit 1, 这样在找到一条数据后就直接返回了，不会继续扫描表；
2.2 分组统计可以禁止排序
默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序
//隐式排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg
where mg.state = 1 group by goods_no, serial_num
//添加ORDER BY NULL禁止排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg
where mg.state = 1 group by goods_no, point_price ORDER BY NULL
所以在需要分组并不需要对结果进行排序的情况下，我们可以禁止隐式排序
上面的例子在MySQL 5.7及更低版本生效，GROUP BY在某些条件下隐式排序。 在MySQL 8.0中，不再发生这种情况，因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序。
不过目前公司线上环境通用的还是MySQL 5.7
2.3 事务里更新语句尽量基于主键或unique key，如update … where id=XX;
否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。
具体原理见 mysql多线程update死锁问题
-
2.4 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
例：SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，故生产环境应避免使用子查询
2.5 Using temporary 优化
多表关联left join其他表的时候，如果以其他表的字段作为查询条件都会产生临时表Using temporary; 这会使得性能受到影响
把非直接关联的表改为直接关联，可以通过改为不作为查询条件的子查询（不要在where后面使用子查询），
//优化前，出现了文件排序和临时表问题。
EXPLAIN SELECT video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
member.nickname
from app_recommend_controller
left join video on app_recommend_controller.video_id= video.id
left join member on member.id= video.member_id
WHERE video.display= 1 AND video.game_id= '9930'
ORDER BY video.upload_time desc LIMIT 0,20
```
//优化后
EXPLAIN SELECT video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
(select nickname form member where id= video.id) as nickname #这部分代替原来的内连接查询出来的昵称
from app_recommend_controller
left join video on app_recommend_controller.video_id= video.id
WHERE video.display= 1 AND video.game_id= '9930'
ORDER BY app_recommend_controller.video_id desc LIMIT 0,20
```
通过把非直接关联表member 从join查询 改为 不作为查询条件的子查询，来优化 Using temporary
参考：https://www.cnblogs.com/jpfss/p/9156422.html
2.6 Using filesort
在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。
优化：1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。
2.7 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢
2.8 SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
因为union all不需要去重，节省数据库资源，提高性能。
2.9 线上环境，多表join不要超过5个表
2.10 在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表
2.11 程序端SELECT语句必须指定具体字段名称，禁止写成 *
2.12 事务里包含SQL不超过5个
因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
2.13 事务操作 不要和 http、rpc调用写到一起
假如 微服务A 和 微服务B同时操作一张表中同样数据， 然后呢，微服务A 把mysql 和 http调用 微服务B写在了一起，
这就会导致一个严重的问题，微服务A 在 update 锁表后，在调用微服务B之后才会提交事务，但是微服务B里同样对 同一张表的数据有update，也加锁，所以呢，高并发情况下，会直接锁表。
解决办法，就微服务高内聚 低耦合，把对同一块业务 同一张表的代码 聚合到一个微服务里。
还有就是，不要把 mysql 事务操作 和 http、rpc调用写到一起。
2.13 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整合在一起。
因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
2.14 不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
2.15 用IN来替换OR
低效查询
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查询
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
2.16 大分页
select * from _t where a = 1 and b = 2 order by id desc limit 10000, 10;
对于大分页，越往后性能越差。
优化：把上一次的最后一条数据，也即上面的id传过来，然后做“id < xxx”处理
2.17 count
- count(主键 id)
InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。 - count(1)
InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
只看这上面这两个用法，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作 - count(字段)
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。 - count()
count()是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count()肯定不是 null，按行累加。按照效率排序的话，count() = count(1) > count(主键 id) > count(字段)，所以建议尽量使用 count(*)。
