mysql索引有哪些-线下业务排查摸清全类型使用边界

上周线上订单库连续三次慢查询告警，运维拉着我翻执行计划，才彻底捋清mysql索引有哪些，当时对着几十张业务表反复建删索引，踩的坑到现在都记的清楚。后台数据库单表千万级数据，日常下单、退款、统计语句混杂，一开始只晓得普通索引，随手建了一堆，结果联合查询依旧拖慢，甚至出现索引失效的情况。

最早上手只接触过主键索引，项目初期建表默认给自增id设主键，那时压根没意识到这也是一类独立索引。主键自带唯一非空属性，整张表只能存在一个，当初测试环境批量插入数据，忘记设置主键，直接导致全表扫描，报表查询耗时直接飙升到二十多秒。后来补上主键索引，查询速度瞬间回落，但当时完全没理清主键和普通唯一索引的差别，还傻傻给订单号再加了一层唯一索引，重复约束拖慢写入速度。

唯一索引是第二个上手的类型，当初想着订单编号不能重复，随手创建唯一索引，业务高峰期频繁新增订单，数据库写入卡顿明显。后来才反应过来，唯一索引写入时会全表校验重复数据，高并发场景下性能损耗远高于普通二级索引，那段时间每天凌晨都要清理冗余唯一索引，调整业务逻辑靠程序兜底唯一约束，才缓解写入压力。

普通索引也就是二级索引，是日常开发用的最多的索引类型，之前运营需要频繁按商品名称检索订单，直接给商品名字段建普通索引，短时间内查询效率提升明显。可没过半个月，运营新增多条件筛选语句，同时筛选商品名、下单时间、支付状态，单字段普通索引直接失效，数据库依旧走全表扫描，那段时间天天收到慢查询短信提醒。

联合索引就是在这个节点被逼着吃透的，多字段筛选的场景下，单独单字段索引根本起不到作用，只能搭建联合索引。当时按照商品名、下单时间、支付状态的顺序搭建复合索引，前期查询速度肉眼可见变好，可没过一周，运营调整筛选逻辑，把下单时间放到查询条件第一位，整条SQL直接不走新建的联合索引。折腾好久才搞明白，联合索引遵循最左前缀原则，查询条件的字段顺序必须匹配索引建立的顺序，不然索引直接作废。

全文索引很少在业务库用到，上次合作的第三方商家需要模糊检索商品详情文本，原本打算用like %%匹配内容，几十万条数据检索直接超时。临时搭建全文索引，匹配长文本的效率远高于模糊查询，只是这种索引仅适配字符大字段，短字段使用完全没必要，而且只能适配MyISAM和InnoDB引擎，老旧版本数据库还会出现检索结果错乱的问题。

空间索引接触的次数屈指可数，去年做同城配送业务，需要根据用户经纬度筛选附近门店，数据库自带的空间索引能快速匹配坐标范围。当时直接新建空间索引存储经纬度字段，门店点位检索速度比普通索引快几倍，可这个索引限制很多，只能存储几何类型数据，普通业务表基本不会用到，平时开发几乎不会主动创建。

前缀索引是我最容易忽略的索引类型，业务表里面手机号、收货地址这类长字符串字段，直接建普通索引会占用大量磁盘空间，索引文件持续膨胀拖慢磁盘读写。拆分字段前缀创建索引，截取手机号前11位建立前缀索引，磁盘占用直接下降一半，查询效率没有明显下滑。只是前缀索引没办法覆盖完整字段，无法满足精准匹配的查询需求，只能适配模糊前缀检索的场景。

那天下班后对着本地数据库反复测试七种索引的执行计划，才发现之前随意建索引的操作有多离谱，明明业务场景适配联合索引，却执着单字段普通索引，白白消耗数据库资源。电脑屏幕上挂着慢查询日志，指尖反复敲击建索引的语句，脑子里不断复盘白天线上出现的各类索引问题，直到深夜才关掉数据库客户端。