MySQL索引笔记

1、 复合索引的建立以及最左前缀原则

假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，
因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，
或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
state, city, zip
state, city
state

MySQL不能利用这个索引来搜索~没有包含在最左前缀的内容~。例如，如果你按照city或zip来搜索，
就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），
该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。
如果是这样的，不用增加索引，因为已经有了(例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引)。
复制代码

2、order by的字段必须放在索引的最后面

假设表persons有如下信息段

CREATE TABLE `persons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(60) NOT NULL COMMENT 'uuid',
  `unitcode` varchar(30) NOT NULL COMMENT '单位编码',
  `code1` varchar(36) DEFAULT NULL COMMENT '员工号/编码',
  `name1` varchar(50) DEFAULT NULL COMMENT '姓名',
  /*
   * ...
   */
  `s_date` date DEFAULT NULL COMMENT '员工档案建档日期',
  `logout` smallint(6) DEFAULT NULL COMMENT '在职状态',
  /*
   * ...
   */
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `persons_uuid_unique` (`uuid`),
  KEY `persons_name1_unitcode_index` (`name1`,`unitcode`),
  /*
   * ...
   */
  KEY `persons_name1_unitcode_logout_deleted_at_index` (`name1`, `unitcode`,`logout`,`deleted_at`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=34199 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码

有如下查询语句

-- sql1
desc select * from `persons` where `unitcode` like '000%' and `logout` = '0' and `persons`.`deleted_at` is null order by `name1`  limit 15 offset 0

-- sql2
desc select `unitcode`, `name1` from `persons` where `unitcode` like '000%' and `logout` = '0' and `persons`.`deleted_at` is null order by `name1`  limit 15 offset 0
复制代码

1. 上面的sql1中不会用到索引， sql2中才用到索引
2. 要使sql1中用到索引必须将sql查询语句中where中没有出现，只是在order by中出现的列名放到索引的最后面，将 索引persons_name1_unitcode_logout_deleted_at_index改为以下即可。

/*
 * ...
 */
KEY `persons_name1_unitcode_logout_deleted_at_index` (`unitcode`,`logout`,`deleted_at`, `name1`) USING BTREE
复制代码