MySQL开发技巧查询索引和完整性

上周帮同事排查一个慢查询，发技表里就几万行数据，巧查走索引却花了1.2秒。询索性EXPLAIN一看，引和type=ALL，完整key=NULL。发技他拍着脑袋说“我明明建了索引”。巧查翻到建表语句，询索性索引建在status列上，引和查询条件是完整WHERE status != 'deleted'。不等于操作让MySQL直接放弃了索引。发技这种事见得太多了，巧查索引不是询索性建了就管用，得看查询怎么写。引和

最左前缀原则是完整绕不过去的坎。联合索引(a, b, c)，查询条件只有b = 1，索引废掉。只有a = 1 AND c = 2，也只能用到a那一截。我见过有人建了五六个单列索引，以为MySQL会自动挑最优的，结果优化器选了个选择性极差的列，扫描行数比全表还多。联合索引的列顺序要按区分度从高到低排，等值查询的列放前面，范围查询的列放最后。范围查询后面的列，索引直接失效。

覆盖索引能省掉回表。查询SELECT id, name FROM users WHERE age = 25，如果索引是(age, name)，id是主键自动带上，Extra里会出现Using index。不需要回表拿数据，速度差好几倍。但别为了覆盖索引把SELECT *改成列一堆，索引维护成本也是成本。写多读少的表，索引越少越好。

索引下推是MySQL 5.6之后的事。以前WHERE a = 1 AND b LIKE '%x%'，存储引擎只能拿a=1去索引里捞，捞出来交给Server层过滤b。有了ICP，b的条件也推到引擎层过滤，减少回表次数。但LIKE以%开头，索引本身就用不上，ICP也救不了。模糊查询前置通配符，这是硬伤，没辙。

完整性约束这块，外键是争议最大的。互联网公司基本禁用外键，理由是高并发下锁竞争和级联操作不可控。但金融系统里，外键是数据一致性的最后一道防线。我待过一家做支付的公司，所有核心表都带外键，DBA说“宁可慢一点，也不能出现孤儿记录”。后来他们用分布式数据库，外键没了，靠应用层补偿。哪种更好？看场景。单机MySQL，数据量不大，外键省心。分库分表，外键就是累赘。

CHECK约束在MySQL 8.0.16之前是摆设，写了也不生效。8.0之后才真正支持。比如CHECK (age >= 0 AND age <= 150)，插入-1直接报错。但别高兴太早，CHECK约束不能跨表，也不能用子查询。想实现“订单金额不能超过用户余额”这种，还得靠触发器或者应用层。

触发器是个双刃剑。有人喜欢用触发器做审计日志，插入订单自动写一条记录。问题是触发器里的错误会回滚整个事务，而且调试困难。更麻烦的是，触发器对复制的影响。基于行的复制还好，基于语句的复制，触发器可能执行两次。我现在的习惯是，能用应用层逻辑解决的，绝不写触发器。

默认值和NOT NULL是基本盘。但NOT NULL DEFAULT ''和NULL是两码事。NULL占一个字节的标记位，索引统计也不一样。COUNT(*)不忽略NULL，COUNT(col)忽略。一个字段如果业务上不允许为空，就老老实实NOT NULL，别偷懒。NULL值在索引里排序靠前，范围查询时可能带来意外的结果。

真正要命的是隐式类型转换。手机号字段是VARCHAR(11)，查询写成WHERE phone = 13800138000，没有引号。MySQL把字符串转成数字比较，索引失效，全表扫描。反过来，整型字段用字符串查，一样废索引。这种坑，EXPLAIN里看type=ALL就要警觉。养成习惯，写SQL前先看字段类型。

索引和完整性不是背几条规则就能搞定的。同一个查询，数据分布不同，执行计划可能天差地别。我习惯在测试环境用EXPLAIN ANALYZE跑一遍，看实际行数和预估行数的偏差。偏差大，说明统计信息不准，ANALYZE TABLE一下。MySQL的优化器没那么智能，它只是个基于成本的估算器。你给它的信息越准，它选错索引的概率越小。