MySQL索引心得

场景分析

订单表

CREATE TABLE test_innodb.torder (
  `id` int(11) NOT NULL,
	order_no varchar(100) NOT NULL,
	order_status varchar(100) NOT NULL,
	order_create_time DATETIME NOT NULL,
	order_amount BIGINT NOT NULL，
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

查询需求

• 查询最近（30天）内某个订单状态的所有订单
• 统计处于某个订单状态的所有订单
• 根据交易单号查询订单的状态

索引定义 KEY order_status-order_no (order_status, order_no), KEY order_status-order_create_time (order_status, order_create_time), UNIQUE KEY order_no (order_no) USING BTREE

市民表

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

查询需求

1. 根据市民身份证号查询所有信息
2. 根据市民的身份证号查询他的姓名

索引定义 KEY id_card (id_card), KEY name_age (name,age)

优化原则：

1. 最左匹配原则

最左匹配原则的含义是，Mysql匹配索引时按照联合索引的最左 N 个字段，或者是字符串索引的最左 M 个字符。

如果能通过新增联合索引、调整联合索引的顺序达到减少索引的效果，那就可以优先考虑这个顺序。

2. 覆盖索引

覆盖索引和核心原理很简单，就是避免查询过程中再去通过主键索引搜索树，减少回表次数，也就是减少磁盘IO访问。

如果增加覆盖索引能匹配到高频需求，可以优先考虑建立这个索引，同时要注意的是索引增加后第一会增大空间的消耗，其次会降低插入数据的性能（一定程度上通过读写分离可解决），但一般业务场景下上面两点是可以忍受的。

3. 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

这个优化原则可以类比覆盖索引的原则来判断，根据具体SQL分析具体优化的表现。