本文源码：GitHub·点这里 || GitEE·点这里

一、高性能索引

1、查问性能问题

在 MySQL 应用的过程中，所谓的性能问题，在大部分的场景下都是指查问的性能，导致查问迟缓的根本原因是数据量的一直变大，解决查问性能的最常见伎俩是：针对查问的业务场景，设计正当的索引构造。

2、索引应用准则

索引的应用并不是越多越好，而是针对业务下的查问场景，一直的改良和优化，例如电商零碎中用户订单的场景，假如存在如下表构造：

CREATE TABLE `ds_user` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `user_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `ds_order` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `user_id` int(11) NOT NULL COMMENT '用户 ID',
  `order_no` varchar(60) NOT NULL COMMENT '订单号',
  `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称',
  `number` int(11) DEFAULT '1' COMMENT '个数',
  `unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '单价',
  `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '总价',
  `order_state` int(2) DEFAULT '1' COMMENT '1 待领取，2 已领取,3 已发货,4 已签收',
  `order_remark` varchar(50) DEFAULT NULL COMMENT '订单备注',
  `create_time` datetime DEFAULT NULL COMMENT '创立工夫',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

用户和订单治理表，在电商的业务中很常见，能够通过对该业务剖析，看看罕用的索引构造：

用户方：

• 基于用户的查问，少数是基于用户 ID(user_id)；
• 基于订单号(order_no)，查看物流的信息；

经营方：

• 基于时间段的流水明细 (create_time) 或排序;
• 基于订单状态的筛选 (order_state) 和统计；
• 基于产品 (product_name) 的数据统计分析；

这样一个流程剖析走下来，即能够在开发初期，确定哪些构造是查问必须用到的，事后做好索引构造，防止数据量宏大到影响性能时再去思考应用索引。

有些时候会思考放弃一些查问条件，例如基于产品名称的数据统计，走定时工作的形式，用来缓解表的查问压力，解决的形式是多样的。

优良的索引设计，都是建设在对业务数据的了解上，思考业务数据的查问形式，进步查问效率。

二、索引创立

1、单列索引

单列索引，即索引建设在表的一个字段上，一个表能够有多个单列索引，应用起来绝对比较简单：

CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;

主键索引，或者上述的 user_id_index 都是单列索引。

业务场景：基于用户本人对订单查问，和管理系统，订单和用户的关联查问，所以订单表的 user_id 须要一个索引。

2、组合索引

组合索引蕴含两个或两个以上的列，组合索引相比单列索引简单很多，如何建设组合索引，和业务关联度十分高，在应用组合索引时，还须要思考查问条件的程序。

CREATE INDEX state_create_time_index ON `ds_order`(`create_time`,`order_state`);

如上就是组合索引，理论蕴含的是 2 个索引 (create_time) (create_time,order_state)，这样查问就波及到最左前缀的准则，必须依照程序来查问，这里上面详说。

业务场景：首先单说这里组合索引，在业务开发中，常见订单状态的统计，基于统计后果做经营剖析，另外就是在经营零碎中，基于创立时间段的筛选条件是默认存在的，防止全副数据实时扫描；一些其余的常见查问也都是条件加时间段的查问模式。

3、前缀索引

如果须要加索引的列是很长的字符串，那么索引会变的宏大臃肿，起到的成果可能并不是很显著。这时候能够截取列的后面一部分，创立索引，节俭空间，这样可能会呈现索引的选择性降落，即基于前缀索引查问出的类似数据可能很多：

ALTER TABLE ds_order ADD KEY (order_no(30)) ;

这里因为订单号太长，所以抉择后面 30 位作为前缀索引，用作订单号的查问，当然这里波及到一个十分经典的业务场景，订单号机制。

业务场景：前缀索引一个典型的利用场景就是解决订单号，一个看似很长的订单号，其实蕴含的信息十分多：

• 工夫点：就是订单生成的工夫，年月日时分秒；
• 标识位：即一个惟一的 UID，保障订全单号惟一；
• 埋点一：在很多业务中，在订单号记录产品类目；
• 埋点二：通常会标识产品属性，例如色彩，口味等；
• 错位符：避免订单号被剖析，会随机一段错位符号；

如此一段剖析下来，理论订单号是十分长的，所以须要引入前缀索引机制，前缀索引冀望应用的索引长度能够筛选整个列的基数，例如下面的订单号：

• 大部分业务基于工夫节点筛选足够，即索引长度 14 位；
• 如果是并发业务，很多工夫节点雷同，则索引长度是工夫点 + 标识位；

留神：如果业务容许的状况下，个别要求前缀索引的长度有唯一性，例如下面的工夫和标示位。

4、其余索引

例如全文索引等，这些用到的场景不多，如果数据宏大，又须要检索等，通常会抉择弱小的搜寻中间件来解决。显式惟一索引，这种也会在程序上做躲避，防止不敌对的异样被抛出。

三、索引查问

如何创立最优的索引，是一件不容易的事件，同样在查问的时候，是否应用索引也是一件难度极大的事件，经验之谈：少数是性能问题裸露的时候，才会回头扫视查问的 SQL 语句，针对性能问题，做相应的查问优化。

1、单列查问

这里间接查问主键索引，MySQL 的主键个别抉择自增，所以速度十分快。

EXPLAIN SELECT * FROM ds_order WHERE id=2;
EXPLAIN SELECT * FROM ds_order WHERE id=1+1;
EXPLAIN SELECT * FROM ds_order WHERE id+1=1;

这里，id=2,id=1+1，MySQL 都能够主动解析，然而 id+ 1 是在索引列上执行运算，间接导致主键索引生效。这里有一个根本策略，如果非要在单列索引上做操作，能够将该逻辑放在程序中，到 MySQL 层面，SQL 语句越干净利落越好。

2、前缀索引查问

前缀索引的查问，能够基于 Like 对特定长度筛选，或者全订单号查问。

EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%';
EXPLAIN SELECT * FROM ds_order WHERE order_no='20200801131415872362873287162572367';

3、组合索引查问

查问最麻烦的就是组合索引，或者说查问条件组合起来，都应用了索引：

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_state='1';

上述基于组合索引中列的程序，应用了组合索引：state_create_time_index。

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00';

上述只应用 create_time 列，也同样应用了索引构造。

EXPLAIN SELECT * FROM ds_order WHERE order_state='1';

上述如果只应用 order_state 条件，则结果显示全表扫描。

EXPLAIN SELECT * FROM ds_order 
WHERE create_time>'2020-08-01 00:00:00' AND order_no LIKE '20200801%';

上述则基于组合索引的 create_time 列和单列索引 order_no 保障查问条件都应用了索引。

通过下面几个查问案例，索引组合索引应用的注意事项如下：

• 组合索引必须按索引最左列开始查问；
• 不能跳过组合字段查问, 这样无奈应用索引；

四、索引其余阐明

1、索引的长处

• 基于注解或惟一索引保障数据库表中数据的唯一性；
• 索引通过缩小扫描表的行数进步查问的效率；

2、索引的毛病

• 创立索引和保护索引，会消耗空间和理论；
• 查问以外的操作增删改等，都须要动静保护索引；

3、索引应用总结

索引机制在 MySQL 中真的非常复杂，非专业的 DBA(就是指开发人员)，根本要纯熟常见的索引构造，待过两年所谓的大厂，每个版本开发波及的外围表 SQL 都是有业余 DBA 验收，简单的查问都是提交需要，DBA 间接输入查问 SQL，当然在个别公司是没有 DBA，须要开发在开发的过程中一直的思考，逐渐优化，这须要对业务数据有肯定的敏感度，对外围接口有执行监控，当发现略微呈现耗时状况，就能够一直优化，这个积攒是个干燥和提高的过程。

五、源代码地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base

举荐浏览：MySQL 数据库系列