MySQL 索引优化实践（单表）

目录

• • 一、前言
  • 二、表数据准备
  • 三、常见业务无索引查询耗时测试
  • • 3.1、通过订单ID / 订单编号 查询指定订单
    • 3.2、查询订单列表
  • 四、订单常见业务索引优化实践
  • • 4.1、通过唯一索引和普通索引优化通过订单编号查询订单信息
    • 4.2、通过普通联合索引优化订单列表查询
    • • 4.2.1、分析查询字段的查询场景
      • 4.2.2、优化各场景查询和原因分析
      • • 4.2.2.1、需要根据订单编号查询
        • 4.2.2.2、需要根据客户编号查询
        • 4.2.2.3、需要根据创建时间查询 和 需要根据订单状态查询
  • 五、索引优化实践
  • • 4.1 联合索引第一个字段用范围查询可能不会走索引
    • 4.2 强制走索引（联合索引第一个字段用范围查询不会走索引这里强制使用索引）
    • 4.3 使用覆盖索引优化查询
    • 4.4 in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
    • 4.5 like KK% 一般情况也是可以走索引的
    • 4.6 分页查询索引使用和优化
    • 4.7 order by查询索引使用和优化
    • • 4.7 总结
    • 4.8 count()查询优化
    • • 4.8.1 MySQL5.7 count()查询优化
      • 4.8.2 MySQL8.0 count()查询优化
  • 六、索引设计原则

一、前言

      索引是为了高效查询排好序的数据结构，当表数据量到达一个量级没有对应索引帮助查询耗时会很长，MySQL资源开销也会非常大，当然索引也不能随意创建，要做到尽量少的索引解决尽量多的问题，这里会对一些业务场景做索引优化演示，这篇文中只介绍单表索引优化，如果单表问题能解决多表关联查询优化就简单多了。

如果对MySQL索引原理还有explain SQL分析工具不是很熟悉的可以看看几篇文章：

• MySQL 索引底层 B+Tree 原理解析
• MySQL explain SQL分析工具详解与最佳实践
• MySQL 索引介绍和最佳实践

二、表数据准备

这里要准备100w数据左右的表，表数据尽量多一些或者列多一些，如果数据太少，测试的时候可能看不到效果。

创建订单信息表

DROP TABLE IF EXISTS `order_info`;CREATE TABLE `order_info` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',  `order_no` varchar(100)  NOT NULL COMMENT '订单编号',  `customer_id` bigint(20) NOT NULL COMMENT '客户编号',  `goods_id` bigint(20) NOT NULL COMMENT '商品ID',  `goods_title` varchar(100) DEFAULT NULL COMMENT '商品标题',  `order_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '订单状态 1：待支付 2：已支付 3：已发货 4、已收货',  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  PRIMARY KEY (`id`)) ENGINE=InnoDB COMMENT='订单信息表';

使用存储过程插入100w条数据

## 创建一个插入数据的存储过程DROP PROCEDURE IF EXISTS insert_procedure;delimiter;;CREATE PROCEDURE insert_procedure () BEGIN  DECLARE i INT DEFAULT 1;  DECLARE goods_id BIGINT DEFAULT CEIL(RAND() * 100);  DECLARE t_error INTEGER DEFAULT 0;  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  START TRANSACTION;  WHILE ( i <= 1000000 ) DO    INSERT INTO `order_info`(`order_no`,`customer_id`, `goods_id`, `goods_title`, `order_status`, `create_time`) VALUES (CONCAT('ON00000',i), CEIL(RAND() * 100000), goods_id, CONCAT('笔记本电脑',goods_id), MOD(i, 4)+1, NOW());    SET i = i + 1;    SET goods_id = CEIL(RAND() * 100);  END WHILE;  IF t_error=1 THEN    ROLLBACK;  ELSE    COMMIT;  END IF;END;;delimiter;# 调用存储过程插入数据 我本地插入100w条数据耗时200sCALL insert_procedure ();

三、常见业务无索引查询耗时测试

我电脑的配置32G内存500G固态，MySQL配置全用默认，自己测试先看看自己MySQL配置的innodb_buffer_pool_size设置的是多少，默认是128MB，查看命令SHOW VARIABLES LIKE 'innodb_buffer_pool_size';配置里的单位是字节，InnoDB使用一个缓冲池来保存索引和原始数据，innodb_buffer_pool_size就是控制这个缓冲池的大小，这个缓冲池在一些情况下对查询性能影响非常大，线上建议设置成MySQL能使用内存的80%，这里不深入。

3.1、通过订单ID / 订单编号 查询指定订单

• 通过订单ID查询订单

SELECT * FROM order_info WHERE id = 955;

• 通过订单编号查询订单

SELECT * FROM order_info WHERE order_no = 'ON000009999';

这里已经可以看到查询耗时明显的差距，我们这里的ID是主键，MySQL InnoDB存储引擎会自动将表中的主键设置为主键索引，同时也是一个聚簇索引叶子节点携带数据，而订单编号是没有索引的会进行全表扫描，会将ON000009999和这个表中每行数据的订单编号都进行比对然后取出满足条件的数据行，100w数据查询一个订单信息耗时已经到了0.6秒左右。

3.2、查询订单列表

查询订单列表一般查询条件比较多，如订单编号、客户编号、订单状态、创建时间、创建时间倒序、是否分页做几个演示，查询条件的内容自己看看存储过程插入的数据长什么样。

• 1、查询客户编号为111的订单列表不分页，不根据创建时间倒序

SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46';

• 2、查询客户编号为111订单列表分页，不根据创建时间倒序

SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46' LIMIT 3;

• 3、查询客户编号为111订单列表分页，根据创建时间倒序

SELECT * FROM order_info WHERE customer_id = 111 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46' ORDER BY create_time DESC LIMIT 3;

这里三个查询只有第2个查询相对较快一点，耗时110毫秒，其它两个查询耗时基本上都接近500毫秒，从我们给定的条件来看区别就在分页或者不分页，排序或者不排序，一般认为分页肯定比不分页查询要快，但是我们看1和2查询分页比不分页耗时相差接近5倍相差可以说是巨大，然后在查询3中分页但是根据创建时间倒序，这里耗时和查询1相近和查询2耗时相差接近5倍，这其中原理挺有趣的会在下面索引优化实践中举例说明这一些问题。

四、订单常见业务索引优化实践

这里会对一些业务场景举例说明，也会对索引的一些特性做讲解。

4.1、通过唯一索引和普通索引优化通过订单编号查询订单信息

类似通过订单编号查询订单信息的业务有很多，都是通过一个编号信息如客户编号配送员编号查询一个一对一的详情数据，这一类查询都有一个特性编号唯一，并且编号类的数据大多都是字符串类型，这里优化可以考虑唯一索引和普通索引，一般我们会给这一类编号数据设置一个唯一索引，既保证了数据的唯一性也保证了通过编号查询的性能问题。

• 1、添加唯一索引

ALTER TABLE `order_info` ADD UNIQUE INDEX `idx_orderNo`(`order_no`);

• 2、添加索引后查询

SELECT * FROM order_info WHERE order_no = 'ON000009999';

无索引测试的时候耗时0.538s，添加索引后查询性能提升十几倍，数据量越大提升比例越高。

4.2、通过普通联合索引优化订单列表查询

      在上面无索引查询我们列举了查询订单列表的三个例子，查询耗时除了第2个都差不多耗时0.5s，不算太慢但是对MySQL性能开销其实是很大的，如果数据量在大一些到500w 1000w，查询时间也会增加到接受不了数值，所以必须要优化。

4.2.1、分析查询字段的查询场景

      优化前第一要考虑的就是需要一些什么字段，如我们例子中会使用订单编号、客户编号、订单状态、创建时间。

分析通过这四个字段查询的场景：

• 1、需要根据订单编号查询
• 2、需要根据客户编号查询
• 3、需要根据创建时间查询
• 4、需要根据订单状态查询

这里我给这四个字段分了四个查询场景，为什么这么分我在下面会详细说明。

4.2.2、优化各场景查询和原因分析

对于这种列表查询使用索引一定要知道索引的一个特性就是最左前缀原则，索引的匹配一定是从最左边第一个字段开始匹配的，不能跳过中间字段匹配，在索引优化实践中会详细说明。

4.2.2.1、需要根据订单编号查询

      在一个订单列表如果需要根据订单编号查询，那么一定是要查询一个唯一的订单，如果我们有索引那么我们可以通过一个订单编号快速定位到一条数据不用进行全表扫描，竟然能快速定位到一条数据那么就算还携带别的条件那直接回表取出行数据再去判断即可。
      所以这里只需要创建一个订单编号的索引来适配所有带订单编号的查询，我们在本文的4.1的优化通过订单编号查询订单信息中创建过一个订单编号的唯一索引，我们这里就用这个唯一索引就行。

• 1、需要根据订单编号查询测试

SELECT * FROM order_info WHERE order_no = 'ON000009999' AND order_status=4;

执行计划 EXPLAIN

这里可以看到查询耗时为0.037s，执行计划中使用到了订单编号的索引，扫描估计行数为1。

4.2.2.2、需要根据客户编号查询

      在需要根据客户编号查询的业务中一定是以客户编号为主要条件的，还有可能会携带订单状态，创建时间等，一个客户可能会下很多单，想想自己这些年网购和点外面应该也有个100单以上了把，那么这里就不能像订单编号只用一个单字段索引了，我们需要把订单状态和创建时间也加上，其实就算不加性能也不会差太多因为一个客户订单本来也不会太多，单表几十条数据和几百条数据查询差距不会很大。

• 1、创建以客户编号起头的普通联合索引

ALTER TABLE `order_info` ADD INDEX `idx_customerId_orderStatus_createTime`(`customer_id`, `order_status`, `create_time`);

我这里会把订单状态和创建时间也带上，某购物APP查询自己订单是不是都有状态选择，时间字段可以用作排序和检索。

• 2、需要根据客户编号查询测试

SELECT * FROM order_info WHERE customer_id = 111 AND order_status=4 AND create_time >= '2023-10-02 09:57:24' AND create_time <= '2023-10-02 10:00:46';

执行计划 EXPLAIN

这里可以看到使用到了我们创建的联合索引，并且三个字段全用到了，客户编号bigint类型不能为空占用8字节，订单状态tinyint不能为空占用1字节，创建时间datetime类型占用5个字节因为创建时间可为空所以多加一个字节，创建时间占用6个字节，合集15个字节和key_len相等。

4.2.2.3、需要根据创建时间查询 和 需要根据订单状态查询

      在查询订单列表时经常会查询某个状态某个时间有多少订单，状态值只有4个如果要通过状态值建立索引的话显然是不可行的，通过状态索引查找某个类型数据可能得到的是几十万行数据，然后还需要回表获取聚簇索引数据，所以对于这种状态值创建单独索引时还需要带上时间字段，在单独查询某个时间内全部订单时也可以使用这个索引，通过in查询将状态值全部包含满足最左前缀原则就能使用该索引查询指定时间段的全部订单。

• 1、添加索引前查询某个时间段内全部订单

SELECT * FROM order_info WHERE order_status IN (1,2,3,4) AND create_time >= '2023-10-02 09:57:30' AND create_time <= '2023-10-02 09:57:31';

因为我们是批量插入的时间间隔比较相近1秒有好几千条数据，自己查询测试的时候最好控制区间在2s的样子。

• 2、创建订单状态和创建时间的普通索引

ALTER TABLE `order_info` ADD INDEX `idx_orderStatus_createTime`(`order_status`,`create_time`);

• 3、查询某个时间段内全部状态订单

SELECT * FROM order_info WHERE order_status IN (1,2,3,4) AND create_time >= '2023-10-02 09:57:30' AND create_time <= '2023-10-02 09:57:40' ;

执行计划 EXPLAIN

我们这里数据是批量插入的，每秒会插入几千条数据，查询时间间隔不能太大了最好在2s的样子，不然索引可能是会失效的，要查询某个状态的订单只有把订单状态的的IN查询换成=查询效果是一样的，满足最左前缀原则即可。

五、索引优化实践

在订单常见业务索引优化实践中简单的介绍了一下在一些场景下创建一些什么索引能提升查询效率，但是还有很多可变因素会影响到索引的使用，也有很多场景可以使用更好的索引，以及索引中很重要的左前缀原则，这里会对一些场景做举例说明。

删除之前创建的索引，创建新的测试联合索引

# 删除上面创建的三个索引，避免测试时被其它索引干扰，要是没有创建则不用删除ALTER TABLE `order_info` DROP INDEX `idx_orderNo`;ALTER TABLE `order_info` DROP INDEX `idx_customerId_orderStatus_createTime`;ALTER TABLE `order_info` DROP INDEX `idx_orderStatus_createTime`;# 创建新的测试联合索引ALTER TABLE `order_info` ADD INDEX `idx_goodsTitle_customerId_orderStatus`(`goods_title`, `customer_id`, `order_status`);

4.1 联合索引第一个字段用范围查询可能不会走索引

EXPLAIN SELECT * FROM order_info WHERE goods_title > '笔记本电脑9' AND customer_id = 9 AND order_status = 4;

• 给一个区间值

EXPLAIN SELECT * FROM order_info  WHERE goods_title > '笔记本电脑9' AND goods_title < '笔记本电脑99' AND customer_id = 9 AND order_status = 4;

MySQL5.7

MySQL8.0

联合索引第一个字段就用范围查找可能不会走索引，对于MySQL5.7来说只要第一个字段用范围查找不会走索引，但是对于MySQL8.0来说给个查询区间还是可能会走索引的，前提是区间也不能太大不然也不会走索引，MySQL内部可能觉得第一个字段用范围，结果集应该很大，回表效率不高，还不如就全表扫描，创建联合索引时千万别把时间这类型字段放第一个了。

4.2 强制走索引（联合索引第一个字段用范围查询不会走索引这里强制使用索引）

EXPLAIN SELECT * FROM order_info FORCE INDEX(idx_goodsTitle_customerId_orderStatus) WHERE goods_title > '笔记本电脑9' AND customer_id = 9 AND order_status = 4;

这里确认是使用了我们指定的索引，然后再来看看查询性能怎么样

• 不指定强制走索引
  

• 指定强制走索引
  

经过对比发现强制走索引查询时间能缩短近10倍，所以有时候MySQL自身并不一定能选择到性能最高的索引使用方式，需要自己不断的尝试对比出最好的方式。

4.3 使用覆盖索引优化查询

EXPLAIN SELECT goods_title,customer_id,order_status FROM order_info WHERE goods_title > '笔记本电脑9' AND customer_id = 9 AND order_status = 4;

查询结果字段和条件字段都在同一个索引中，查询可以完全使用索引中字段不用回表则称为覆盖索引，覆盖索引因为能避免回表就算联合索引第一个字段范围查询也能走索引。

4.4 in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

• 100w数据表测试

EXPLAIN SELECT * FROM order_info  WHERE goods_title IN('笔记本电脑8','笔记本电脑9') AND customer_id = 9 AND order_status = 4;

EXPLAIN SELECT * FROM order_info  WHERE (goods_title = '笔记本电脑8' OR goods_title = '笔记本电脑9') AND customer_id = 9 AND order_status = 4;

• 根据order_info创建一个只有3条数据order_info_copy表进行测试
  MySQL5.7
  
  
  MySQL8.0
  
  

这里可以看到MySQL5.7中会进行全表扫描，但是MySQL8.0还是会走索引，现在MySQL8.0市场上用的已经比较多了，它执意要走索引肯定有它的道理，而且要通过索引优化查询肯定是要测试比较查询效率的，在实际业务中多测试再看看执行计划，只要实践才知道加的索引是否好用。

4.5 like KK% 一般情况也是可以走索引的

• like 笔记本电脑999

EXPLAIN SELECT * FROM order_info WHERE goods_title LIKE '笔记本电脑999%' AND customer_id = 9 AND order_status = 4;

• like 笔记本电脑9

EXPLAIN SELECT * FROM order_info WHERE goods_title LIKE '笔记本电脑9%' AND customer_id = 9 AND order_status = 4;

使用like前缀查询在结果集较少的时候是会走索引的，如果MySQL认为结果集较大还是不会走索引，结合以上几个例子可以看出，如果通过索引查询响应结果集过大并且没有满足覆盖索引也很有可能不会走索引，这点MySQL5.7 8.0都是一样的。

4.6 分页查询索引使用和优化

• 例1：在没有索引且不分页的请求下查询指定客户订单列表数据

EXPLAIN SELECT * FROM order_info WHERE customer_id = 9 ORDER BY goods_title;

• 例2：在没有索引分页查询指定客户订单列表数据

SELECT * FROM order_info WHERE customer_id = 9 LIMIT 0,3;

SELECT * FROM order_info WHERE customer_id = 9 LIMIT 80,3;

通过这两个分页查询可以看到查询从第0条开始往后查3条数据执行耗时很短，查询从第80条开始往后查3条数据耗时和不分页差不多，因为MySQL分页查询的时候会根据我们的分页条件找出对应结果集的数据，比如我们分页条件时customer_id = 9 LIMIT 0,3，MySQL会先用customer_id = 9去一条条对比数据，因为我们的分页参数时0,3也就是说找出3条数据即可，只要找到了3条数据就不会往后找了，同理LIMIT 80,3的时候需要找到83条数据就不会往后找了，所以这里我们LIMIT 80,3的时候和不分页耗时差不了多少，不加分页会扫描全表拿出全表的数据。

• 例3：在没有索引分页查询指定客户订单列表数据并且根据创建时间排序

SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time LIMIT 0,3;

这里加了一个排序条件分页查询后耗时也和不分页差不多，因为如果加了排序字段是需要先扫全表获取全部符合结果的数据才能进行分页。

• 例4：创建索引分页查询指定客户订单列表数据并且根据创建时间排序（正序）

# 创建索引ALTER TABLE `order_info` ADD INDEX `idx_customerId_createTime`(`customer_id`, `create_time`);# 删除索引ALTER TABLE `order_info` DROP INDEX `idx_customerId_createTime`;

SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time LIMIT 0,3;

执行计划 EXPLAIN

创建索引后查询快了很多，因为这里是正序的索引默认也是正序的根本不用在进行排序，下面再试试倒序。

• 例4：创建索引分页查询指定客户订单列表数据并且根据创建时间排序（倒序）

SELECT * FROM order_info WHERE customer_id = 9 ORDER BY create_time DESC LIMIT 0,3;

MySQL 5.7

MySQL8.0

查询时间和正序差不多，但是这里MySQL5.7和MySQL8.0的Extra有点区别，MySQL8.0可以反向索引扫描，MySQL5.7应该是将获取到的数据放入内存中排序，也可以创建倒序索引这里不深入。

4.7 order by查询索引使用和优化

• 例1：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' AND order_status = 4 ORDER BY customer_id;

利用最左前缀原则：中间字段不能断，因此查询用到了goods_title索引，从key_len=403也能看出，customer_id索引列用在排序过程中，因为Extra字段里没有using filesort，而且索引本来就是正序的无需在排。

• 列2：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' ORDER BY order_status;

从explain的执行结果来看：key_len=403，查询使用了goods_title 索引，由于用了order_status进行排序，跳过了
customer_id，出现了Using filesort。

• 列3：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' ORDER BY customer_id,order_status;

查找只用到索引goods_title 字段，customer_id和order_status用于排序，无Using filesort。

• 列4：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' ORDER BY order_status,customer_id;

和例3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为goods_title,customer_id,order_status，但是排序的时候customer_id和order_status颠倒位置了。

• 列5：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' AND customer_id = 9 ORDER BY order_status,customer_id;

与例4对比，在Extra中并未出现Using filesort，因为customer_id 为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

• 列6：

EXPLAIN SELECT * FROM order_info WHERE goods_title = '笔记本电脑9' ORDER BY customer_id ASC,order_status DESC;

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

• 列7：

EXPLAIN SELECT * FROM order_info WHERE goods_title IN ('笔记本电脑9','笔记本电脑10') ORDER BY customer_id,order_status;

对于排序来说，IN查询也是范围查询，如果排序查询中使用范围查询则索引中只能用到goods_title，后面的字段无法使用。

• 列8：

EXPLAIN SELECT * FROM order_info WHERE goods_title > '笔记本电脑9' ORDER BY goods_title;

MySQL5.7

MySQL8.0

MySQL5.7只要是联合索引第一个字段要走范围查询，那么索引就没法使用，除非覆盖索引，但是在MySQL8.0时却是会去使用索引的，还可以对比一下查询性能，我这里MySQL5.7全表扫描耗时1.3s，MySQL8.0走索引耗时0.8s，两个版本MySQL第一次执行可能时间都需要2s的样子，会将磁盘中的聚簇索引叶子节点数据加载到内存中，多次执行后回表可以直接查内存不用再次读取磁盘。

4.7 总结

• 1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
  效率高，filesort效率低。
• 2、order by满足两种情况会使用Using index。
  • order by语句使用索引最左前列。
  • 使用where子句与order by子句条件列组合满足索引最左前列。
• 3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
• 4、如果order by的条件不在索引列上，就会产生Using filesort。
• 5、能用覆盖索引尽量用覆盖索引
• 6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
  by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
  的限定条件就不要去having限定了。

Using filesort文件排序原理详解

• filesort文件排序方式
  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可
    以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,
    packed_additional_fields >
  • 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行
    数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具
    可以看到sort_mode信息里显示< sort_key, rowid >
• MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来
  判断使用哪种排序模式。
  • 如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
  • 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。

4.8 count()查询优化

count查询常见有4种写法count(1)、count(字段)、count(主键 id)、count(*)，这里会对4种写法原理和性能做讲解，这里MySQL5.7和MySQL8.0区别有点大，这里分开讲解，还需要将之前创建的索引全删除避免测试被影响。

# 删除之前创建的所有索引，已经删除的不用重复执行ALTER TABLE `order_info` DROP INDEX `idx_orderNo`;ALTER TABLE `order_info` DROP INDEX `idx_customerId_orderStatus_createTime`;ALTER TABLE `order_info` DROP INDEX `idx_orderStatus_createTime`;ALTER TABLE `order_info` DROP INDEX `idx_goodsTitle_customerId_orderStatus`;ALTER TABLE `order_info` DROP INDEX `idx_customerId_createTime`;

4.8.1 MySQL5.7 count()查询优化

先比对查询时间后面做总结

• MySQL5.7（goods_id字段无索引）
  
  这里我执行了很多次得到的执行效率结果都是 count(*) ≈ count(1) > count(id) > count(goods_id)

• MySQL5.7（goods_id字段建立索引）

# 新增idx_goodsId索引ALTER TABLE order_info ADD INDEX idx_goodsId(goods_id);# 删除idx_goodsId索引ALTER TABLE `order_info` DROP INDEX `idx_goodsId`;

这里给goods_id字段建立索引后，我执行了很多次得到的执行效率结果也还是 count(*) ≈ count(1) > count(id) > count(goods_id)，反而加上索引后count(goods_id)更加慢了，而且有一点蛋疼的事这几条SQL的执行计划都是下图这样的，理论上应该会展示索引的使用。

• MySQL5.7 count()查询总结
  • 通过上面两个查询效率比对可以确认count(*) ≈ count(1) > count(id) > count(goods_id)，在count查询的时候直接使用count(*)或者count(1)是最好的，MySQL使用count(*)并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，count(1)不需要取出字段统计，就用常量1做统计，效率也很高。
  • count(id)可以走主键索引，如果有辅助索引会选择走辅助索引相对主键索引存储数据更少，检索性能应该更高。
  • 理论上count(goods_id)，goods_id没有索引时会全表扫描拿出goods_id字段统计，建立索引后只用扫描索引统计，效率应该更高的，这里测试好像并没有，而且执行计划中也没有显示其它信息，我这可能是个例。
  • 我们目的是找到效率最高的查询方法，对于不同业务可能需要不同的方式，要找到最合适的方法必须靠实践。

4.8.2 MySQL8.0 count()查询优化

• MySQL8.0（goods_id字段无索引）
  
  其中count(1)、count(id)、count(*) 都是使用了主键索引执行计划如下
  
  count(goods_id)会进行全表扫描
  

• MySQL8.0（goods_id字段有索引）

# 新增idx_goodsId索引ALTER TABLE order_info ADD INDEX idx_goodsId(goods_id);# 删除idx_goodsId索引ALTER TABLE `order_info` DROP INDEX `idx_goodsId`;

添加上索引后四个查询都会走idx_goodsId索引

• MySQL8.0 count()查询总结
  • 我这里100w数据测试，我执行了很多次得到的查询效率都差不多，估计要加大数据测试才能行，不过和MySQL5.7理论应该是一致的count(*) 和 count(1)一定是最稳定效率最高的
  • 在count查询的时候直接使用count(*)或者count(1)是最好的，MySQL使用count(*)并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，count(1)不需要取出字段统计，就用常量1做统计，效率也很高。
  • count(id)可以走主键索引，如果有辅助索引会选择走辅助索引相对主键索引存储数据更少，检索性能应该更高。
  • 理论上count(goods_id)，goods_id没有索引时会全表扫描拿出goods_id字段统计，建立索引后只用扫描索引统计，效率应该更高的。

六、索引设计原则

• 1、代码先行，索引后上
  不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
  这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立
  索引。
• 2、联合索引尽量覆盖条件
  比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
  where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原
  则。
• 3、不要在小基数字段上建立索引
  索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，
  其值不是男就是女，那么该字段的基数就是2。
  如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没
  法进行快速的二分查找，那用索引就没有太大的意义了。
  一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查
  找的优势来。
• 4、长字符串我们可以采用前缀索引
  尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会
  比较小，此时你在搜索的时候性能也会比较好一点。
  当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立
  索引，哪怕多占用一些磁盘空间也是有必要的。
  对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个
  字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY
  index(goods_title(20), customer_id, order_status)。
  此时你在where条件里搜索的时候，如果是根据goods_title字段来搜索，那么此时就会先到索引树里根据goods_title
  字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来
  完整的goods_title字段值进行比对。
  但是假如你要是order by goods_title，那么此时你的goods_title因为在索引树里仅仅包含了前20个字符，所以这个排
  序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
• 5、where与order by冲突时优先where
  在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到
  底是让where去用上索引，还是让order by用上索引?
  一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
  因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可
  能会小很多。
• 6、基于慢sql查询做优化
  可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

来源地址：https://blog.csdn.net/weixin_44606481/article/details/133426832