正文：MYSQL 使用技巧总结

原创

wx64b6623705e68 2023-07-20 18:01:54 ©著作权

©著作权归作者所有：来自51CTO博客作者wx64b6623705e68的原创作品，如需转载，请与作者联系，否则将追究法律责任

本人是软件测试工程师，最近在做一些数据库基础学习，特意记录下学习过程的一些总结，随记：

索引使用小技巧 1、联合索引最左原则，例如（联合索引：a, b, c）： where a = 3, 使用a where a = 3, b = 5 使用a, b where a = 3, b = 4, c = 5 使用a, b, c where a = 3, c = 4 使用a where a = 3, b > 4, c = 5 使用a, b（c不能使用在范围条件之后）反例： KEY \idx_test\ (\user_id\,\batch_id\,\coupon_code\) USING BTREE SELECT * FROM coup_coupon WHERE user_id = 122121 and batch_id > 10017 and coupon_code = 'vmKnC61y1ns260Gc94o09Q36JV02O50t'; 结果：不管有没有第三个条件，扫描行都是169行再创建一个索引：KEY \idx_coup_code\ (\coupon_code\)，此时会走\idx_coup_code\，引擎会自动选择最优方式

2、数据量小的情况（< 2000条数据） InnoDB必定会有一个聚簇索引，且一个表的数据是一个独立文件所以：2000条数据是连续的，虽然是全表扫描。但实际上磁头只需要一个回合就能读出所有数据

1、单表查询走全表扫描反而比索引更快
2、如果是关联查询，左右条件字段还是要走索引快

3、前缀索引：索引创建不能过大，大字段或者text字段要指定索引长度根据实际情况获取占比，一般情况“选择性”在0.6到0.7都可以接受。值越高越好，要结合索引取长进行综合判断选择性：“索引列”取前几位、再去重后的总量占比

创建：ALTER TABLE shop_wechat_config ADD KEY idx_app_secret(app_secret(8));
占比测试：
    SELECT 1.0*count(distinct left(coupon_code, 4))/count(*) FROM coup_coupon; -> 0.46524
    SELECT 1.0*count(distinct left(coupon_code, 5))/count(*) FROM coup_coupon; -> 0.95477
    结果：取4位时“选择性”只有0.46，但是取5位达到了0.954，所以这个表的索引其实可以直接使用5位前缀索引
占比测试2：
    SELECT 1.0*count(distinct left(open_id, 9))/count(*) FROM crm_user_third_0;  -- 0.13915
    SELECT 1.0*count(distinct left(open_id, 10))/count(*) FROM crm_user_third_0; -- 0.82227
    SELECT 1.0*count(distinct left(open_id, 11))/count(*) FROM crm_user_third_0; -- 0.99124

缺点：MySQL不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)

4、使用“覆盖索引” 定义：如果查询的列恰好是索引列的一部分，那查询只需要找到索引文件后直接返回，不需要回行到磁盘再找数据比如：SELECT batch_id, batch_name, coupon_id, third_code, redeem_code FROM coup_coupon WHERE batch_id = 10086; 索引:batch_id,batch_name 此时，查询的字段为都包含在“索引文件”中，就不会“回表”把整条数据查出来再取name，而是直接返回“索引文件”

5、减少索引量： 1、使用联合索引，根据大多数使用场景，有些索引可以合并成联合索引 2、底频次的索引，可以考虑替代方案（代码中处理、全表扫描）

6、联合索引 - 前后顺序规则1：因为有最左原则，从单个的使用场景上考虑，有的单个使用频次比较高的，我们放最前端规则2：不考虑单个使用，把过滤数据越多的，越靠前放反例： KEY \idx_status_batch_id\ (\status\,\batch_id\) USING BTREE 基本永远也用不到，也不知道谁创建的说明：MySql发现命中率大于30%时，就会优化成全表扫描

7、索引自动优化索引默认命中率大于30%时、或者磁盘来回扫描多次，会认为性能比全表扫描还慢 PS：机械硬盘中，按索引搜索，索引的最小单位是页，而根据索引的长久使用（索引分裂、索引合并），会导致页的顺序分散在磁盘的不同位置。比如：走索引会对整个盘扫描1000次，而全表扫描反例： SELECT * FROM coup_coupon WHERE coupon_code != '4400A940h'; 命中率 > 99.99% SELECT * FROM coup_coupon_use WHERE status = 3(已使用); 命中率 > 80%

PS：索引类型在某些场景下，也会自动优化。比如：使用B-Tree，但是数据重复率非常底，Mysql可能会选择重建索引，自动改为Hash索引

8、避免冗余索引会增加索引负担比如：KEY1 \idx_batch_id_user_id_coup_code\，KEY2 \idx_batch_id\，KEY2完全是冗余的

9、Order By - 使用问题： Order By子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查找的限制是一样的：需要满足索引的最左前缀的要求，否则无法利用索引排序。

10、Select * 问题性能：从性能上来讲，并无区别。主要区别： 1、磁盘IO：如果只需要其中的部分数据，则指定字段查询是最快的，减少磁盘读取量（虽然不多） 2、网络IO：如果有无用数据，增加内部系统的网络（真实发生过的问题，大表的一条数据就有1K，结果查询几万条数据，传输量达到了几十上百兆） 3、回表查询：如果需要的数据刚好都有索引，则可以直接返回索引文件内容，磁盘不会再根据索引地扯进行扫描 4、扩展性：如果使用Select *，增加、删除列时，会影响到代码

函数失效 1、查询字段中使用，不会失效，但是消耗CPU和内存 SELECT coupon_id, IFNULL(update_time, 0), (batch_id * 10) bid2, CONCAT(user_id, "_", "a") FROM coup_coupon WHERE batch_id = 142; 2、查询条件中使用函数索引失效反例1 - 函数： SELECT * FROM coup_coupon WHERE batch_id + 1 = 22 反例2 - 运算符号： SELECT * FROM coup_coupon WHERE batch_id + 1 = 22 SELECT * FROM coup_coupon WHERE batch_id * 2 = 22 SELECT * FROM coup_coupon WHERE batch_id = (plat + 132);
查询条件中使用关键词 1：OR 条件条件里只要有一个OR的条件没加索引，索引就会失效 2：LIKE 条件只能从左往右模糊匹配，不支持左边模糊反例：LIKE '%ABC' 正例：LIKE 'ABC%' 3：IN 条件， 4：NOT IN 条件， 5：!= 条件普通索引：SELECT * FROM app WHERE name != "duan"; -- 不会走索引主键索引：SELECT * FROM app WHERE app_id != 18; -- 会走索引，前提命中率 < 30% 6：>或< 条件如果是聚集索引或普通索引是整数类型，是会走索引的。反之则会失效
创建聚集索引 - InnoDB表必定有聚簇索引： 1、手动定义（主键索引） 2、自动：找出所有的唯一索引，根据创建顺序往下取。然后此索引列为非null。否则找下一个，如果一直找不到，则会隐式定义一个主键来作为聚簇索引 PS：聚集索引尽量使用有顺序的生成方案，避免索引页的分裂
设置非空Null - 索引 1、Count函数会漏掉null值例如： # 总共：41242行 SELECT count(update_time) FROM coup_coupon; # 41160行 SELECT count(update_time) FROM coup_coupon WHERE update_time is null; # 0行 SELECT * FROM coup_coupon WHERE update_time is null; # 82行 2、对null做算术运算的结果都是null 3、不要使用is not null，索引失效 4、null 比空字符串需要更多的存储空间 5、排序（排序无法走到索引） 6、5.6之后，null值也可以走索引（不过应该是mysql做了特殊处理，这样消耗CPU和内存）

为什么索引列无法存储Null值？索引是有序的。NULL值进入索引时，无法确定其应该放在哪里（）数据“页”：有上一页、下一页，Null值是不确定的一个数据，根本不知道放到哪个位置（页->叶子节点）合适。 PS：新版的5.7引擎好像是做了特殊处理，null值搜索可以走索引，不过是有代价的

一定要定义主键，且使用自增ID 在InnoDB中，使用自增ID可以避免页分裂问题如果没手动指定聚簇索引，自动找了一个UUID的唯一索引来当聚簇索引，那很容易就引起页分裂

页分裂： 1、表字段大小是已知的，所以一页能放多少条数据，也是固定的 2、比如，页5、6、7全部都存满了，此时插入新数据，根据主键索引分配到页6，然后6满了，顺序放入页7，7也满了，此时会进行分裂。生成一个新的页，再把5、7页的数据重新安排代价： 1、相邻两页的数据列重新记录位置 2、数据列分散（严重）解决办法： 1、如果有删除的数据，则有可能在页合并的时候挪回去（数据库动处理） 2、用OPTIMIZE重新整理表（极慢），比如：OPTIMIZE TABLE bs_brand;

总结：多看多操作，多余比较难得要多多琢磨，慢慢就上手了。