MySQL进阶（7）—— 索引的使用以及SQL优化

最新推荐文章于 2025-03-12 08:30:00 发布

waitself

最新推荐文章于 2025-03-12 08:30:00 发布

阅读量320

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_42137701/article/details/108921973

mysql 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了MySQL数据库中索引的创建与使用，包括如何创建联合索引，以及避免索引失效的各种场景。同时，讨论了SQL查询优化，如大批量插入数据的技巧，优化INSERT语句，优化ORDER BY和GROUP BY语句，以及如何优化嵌套查询和OR条件。此外，还提出了分页查询的优化方法，并介绍了SQL提示如USE INDEX、IGNORE INDEX和FORCE INDEX的使用。通过对索引的合理使用和SQL语句的优化，可以显著提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、索引的使用

1.1、准备环境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

// 创建联合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

在这里插入图片描述

1.2、避免索引失效

1）全值匹配，对索引中所有列都指定具体值。
该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

在这里插入图片描述
2）最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：
在这里插入图片描述
违背最左前缀法则，索引失效：

在这里插入图片描述
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3）范围查询右边的列，不能使用索引

在这里插入图片描述
根据前面的两个字段 name、status 查询是走索引的，adress 没有用到索引。

4）不要在索引列上进行运算操作，索引将失效。
在这里插入图片描述
5）字段串不加单引号，造成索引失效。

在查询时，没有对字符串加单引号，MySQL 的查询优化器，会自动的进行类型转换，造成索引失效。

6）尽量使用覆盖索引，避免 select *
尽量使用覆盖索引（只访问索引的查询），减少 select *
在这里插入图片描述
如果查询列，超出索引，也会降低性能。

TIP : 
	
    using index ：使用覆盖索引的时候就会出现

    using where：在查找使用索引的情况下，需要回原表去查询所需的数据

    using index condition：查找使用了索引，但是需要回原表查询数据

    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7）用or分割开的条件，如果or前的条件中有索引，而后面的列中没有索引，那么设计的索引不会被用到。
在这里插入图片描述
8）以 %开头的 Like 模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：
通过覆盖索引来解决

9）如果 MySQL 评估使用索引比全表更慢，则不使用索引。
在这里插入图片描述
10）is NULL ，is NOT NULL 有时索引失效

11）in和 not in都走索引

12）单列索引和符合索引。
尽量使用复合索引，而少使用单列索引。
创建符合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

1.3、查看索引使用情况

//当前session的索引
show status like 'Handler_read%';	
// 全局索引情况
show global status like 'Handler_read%';

在这里插入图片描述

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

2、SQL优化

2.1、大批量插入数据

环境准备：

CREATE TABLE `tb_user_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '用户状态',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

当使用load命令导入数据的时候，适当的设置可以提高导入的效率。
对于InnoDB类型的表，有以下几种可以提高导入的效率。
1）主键顺序插入
因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

脚本文件介绍 :
	sql1.log  ----> 主键有序
	sql2.log  ----> 主键无序

脚本文件下载
链接: https://pan.baidu.com/s/1J_MmTbFkkutCzg4KecFCXg 密码: 6r8i

插入ID顺序排列数据：

在这里插入图片描述

插入ID无序排列数据：

在这里插入图片描述
2）关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

在这里插入图片描述
3）手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

在这里插入图片描述

2.2、优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

示例，原始方式为：

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');

优化后的方案为：

  insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

在事务中进行数据插入。

  start transaction;
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  commit;

数据有序插入

  insert into tb_test values(4,'Tim');
  insert into tb_test values(1,'Tom');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(5,'Rose');
  insert into tb_test values(2,'Cat');

优化后

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(4,'Tim');
  insert into tb_test values(5,'Rose');

2.3、优化 order by 语句

2.3.1、环境准备

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

2.3.2、两种排序方式

1）第一种是通过对返回数据进行排序，也就是常说的 filesort排序，所有不是通过索引直接返回排序结果的排序都叫做 FileSort 排序
在这里插入图片描述
2）第二种是通过有序索引扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

多字段排序

了解了 MySQL 的排序方式，优化目标就清晰了：
尽量减少额外的排序，通过索引直接返回有序数据。where 条件和 Order by 使用相同的索引，并且 Order by 的顺序和索引顺序相同，并且 Order by 的字段都是升序。或者都是降序。否则肯定需要额外的操作，这样就会出现 FileSort。

2.3.3、FileSort的优化

通过创建合适的索引，能够减少 FIleSort 的出现，但是在某种情况下，条件限制不能让 FileSort 消失，那就需要加快 FileSort 的排序操作。对于 FileSort ，MySQL 有两种排序算法：

1）两次扫描算法：MySQL4.1 之前，使用该排序方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果 sort buffer 不够，则在临时表 temporary tbale中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机 I/O 操作。
2）一次扫描算法：一次取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，单排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data的大小和 Query 语句取出的字段总大小，来判定使用哪种排序算法，如果 max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort buffer_size 和 max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。
在这里插入图片描述

5.4、优化 group by 语句

由于 group by 实际上也会进行排序操作，而且与 order by 相比，group by 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在group by 的实现过程中，与 order by 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行 order by bull禁止排序。如下：

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

在这里插入图片描述

优化后

explain select age,count(*) from emp group by age order by null;

在这里插入图片描述
从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 “filesort”，而上文提过Filesort往往非常耗费时间。

5.5、优化嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

示例，查找有角色的所有的用户信息 :

 explain select * from t_user where id in (select user_id from user_role );

在这里插入图片描述
优化后：

explain select * from t_user u , user_role ur where u.id = ur.user_id;

在这里插入图片描述
连接(Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

5.6、优化OR条件

对于包含 OR 的查询字句，如果要利用索引，则 OR 之间的每个条件都必须用到索引，而且不能使用到复合索引。如果没有索引，则应该考虑增加索引。
获取 emp 表中的所有索引：
在这里插入图片描述
示例：

explain select * from emp where id = 1 or age = 30;

在这里插入图片描述
建议使用 union 替换 or：

这里比较下重要指标，主要是 type 和 ref：
type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR 。

5.7、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价的非常大。
在这里插入图片描述

5.7.1 优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

在这里插入图片描述

5.7.2 优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

在这里插入图片描述

5.8 使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

5.8.1 USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

create index idx_seller_name on tb_seller(name);

在这里插入图片描述

5.8.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

 explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

在这里插入图片描述

5.8.3 FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

create index idx_seller_address on tb_seller(address);

在这里插入图片描述