MySQL性能优化[实践篇]-索引合并与复合索引

最新推荐文章于 2024-06-29 10:25:20 发布

Holmofy

最新推荐文章于 2024-06-29 10:25:20 发布

阅读量2.9k

点赞数 3

分类专栏： [数据库] |--MySQL MySQL数据库优化文章标签： MySQL 索引优化

本文链接：https://blog.csdn.net/holmofy/article/details/80384637

版权

本文介绍了MySQL性能优化中的索引合并策略和复合索引的使用，通过实例展示了如何创建和选择合适的索引顺序，以提高查询效率。文章探讨了范围查询、排序和分组对索引顺序的影响，并提供了调整索引顺序的建议。

摘要由CSDN通过智能技术生成

系列文章：

从上一篇创建索引的实践中，我们看到了索引给我们带来的性能提升是非常可观的。

我们上次创建的表结构非常简单，只有两三个字段，where子句查询条件只有一个字段。

实际应用场景中我们的表结构会更复杂，查询条件也会非常多。在多条件查询的情况下又如何才能用到索引呢，我们可以测试一下。

准备测试数据

创建表结构

create table tb_test(id int primary key auto_increment,
                     c1 char(1),
                     c2 char(1),
                     c3 char(1),
                     c4 char(1));

生成随机字符的函数

delimiter $$
# 生成随机字符串
create function rand_char() returns char(1)
begin
  declare CHARS char(52) default 'abcdefghijklmnopqrstuvwxyz';
  return substr(CHARS,floor(1+RAND()*52), 1);
end
$$

生成测试数据的存储过程

create procedure insert_tb_test(c int)
begin
  declare i int default 0;
  set autocommit = 0;
  repeat
    set i = i + 1;
    insert into tb_test(c1,c2,c3,c4) values(rand_char(),rand_char(),rand_char(),rand_char());
  until i = c end repeat;
  set autocommit = 1;
end
$$

delimiter ;

调用存储过程生成一百万条测试数据：

call insert_tb_test(1000000);

现在的查询场景是select * from tb_test where c1=? and c2=? and c3=? and c4=?

没有索引的情况下我们试着查询一次：

mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.61 sec)  # 0.61秒，很慢

我们可以为c1、c2、c3、c4分别创建一个索引或者为他们创建一个复合索引。

我们先来试试第一种方案。

多个单列索引

创建多个索引

alter table tb_test add index idx_tb_test_c1(c1);
alter table tb_test add index idx_tb_test_c2(c2);
alter table tb_test add index idx_tb_test_c3(c3);
alter table tb_test add index idx_tb_test_c4(c4);

创建索引后查询

mysql> select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D';
Empty set (0.05 sec)  # 花了 0.05秒

mysql> explain select * from tb_test where c1='A' and c2='B' and c3='C' and c4='D'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_test
   partitions: NULL
         type: index_