飞利浦·斯塔克|慢 SQL 治理分享( 二 )

以CTO-D为单位根据以上多维度指标统计汇总应用的加权平均，由低到高得出排名，突出头尾top3 ，每周播报。
三为什么由我来做猜测可能与我的背景有关，有C/C++背景，曾在上家公司负责过公司层面异地多活架构的设计和落地，对于MySQL比较了解一些。
另外可能是利益无关，我所在小团队业务刚起步，不存在慢SQL ，这样可以插入到各个业务线去。
四行动支撑 1 集团MySQL规约
索引规约摘录部分：
【强制】超过三个表禁止join 。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表join也要注意表索引、SQL性能。
【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【参考】创建索引时避免有如下极端误解：
1）索引宁滥勿缺
认为一个查询就需要建一个索引。
2）吝啬索引的创建
认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制唯一索引
认为唯一索引一律需要在应用层通过“先查后插”方式解决。
2 DB变更标准
DDL需要控制变更速度，注意灰度和并发控制，变更发布需要在规定的变更发布窗口内。
五分享一些我参与优化的例子 1 数据分布不均匀

1）分库分表不合理
该业务数据分了8个库，每个库分了16张表，通过查看表空间可以看到数据几乎都分布在各个库的某2张表中。分库分表的策略有问题，另外过高预估了业务增量，这个持保留意见。
2）索引不合理
单表创建了idx_logistics_corp_id_special_id的联合索引，但即便这样区分度依然太低，根据实验及业务反馈(logistics_corp_idtransport_type_id)字段组合区分度非常高，且业务存在transport_type_id的单查场景。

2 索引问题
SELECT COUNT(0) AS `tmp_count`FROM( SELECT `table_holder`.`user_id` `table_holder`.`sc_item_id` SUM( CASE `table_holder`.`inventory_type` WHEN 1 THEN `table_holder`.`quantity` ELSE 0 END ) AS `saleable_quantity` SUM( CASE `table_holder`.`inventory_type` WHEN 1 THEN `table_holder`.`lock_quantity` ELSE 0 END ) AS `saleable_lock_quantity` SUM( CASE `table_holder`.`inventory_type` WHEN 401 THEN `table_holder`.`quantity` ELSE 0 END ) AS `transfer_on_way_quantity` `table_holder`.`store_code` MAX(`table_holder`.`gmt_modified`) AS `gmt_modified` FROM `table_holder` WHERE(`table_holder`.`is_deleted` = 0) AND(`table_holder`.`quantity`0) AND `table_holder`.`user_id` IN(3405569954) AND `table_holder`.`store_code` IN('ZJJHBHYTJJ0001' '...1000多个') GROUP BY `table_holder`.`user_id` `table_holder`.`sc_item_id` ORDER BY `table_holder`.`user_id` ASC `table_holder`.`sc_item_id` ASC ) `a`; 这个case对应的表有store_code索引，因此认为没问题，没办法优化了。实则通过执行计划，我们发现MySQL选择了全表扫描。针对该case实践发现，当范围查询的个数超过200个时，索引优化器将不再使用该字段索引。
【飞利浦·斯塔克|慢 SQL 治理分享】最终经过拉取最近一段时间的相关查询SQL ，结合业务的数据分布，我们发现采用(is_deletedquantity)即可解决。