mysql联合索引生效原则

mysql中 myisam,innodb默认使用的是 Btree索引,至于btree的数据结构是怎样的都不重要,
只需要知道结果,既然是索引那这个数据结构最后是排好序;就像新华字典他的目录就是按照a,b,c…这样排好序的;
所以你在找东西的时候才快,比如你找 “中” 这个字的解释,你肯定就会定位到目录的 z 开头部分;
组合索引可以这样理解,比如(a,b,c),abc都是排好序的,在任意一段a的下面b都是排好序的,任何一段b下面c都是排好序的;

组合索引的生效原则是 从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;
比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点,全部发挥作用;
where a=3 and c=5... 这种情况下b就是断点,a发挥了效果,c没有效果
where b=3 and c=4... 这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果;
where b=45 and a=3 and c=5 .... 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关

(a,b,c) 三个列上加了联合索引(是联合索引 不是在每个列上单独加索引)

还需注意, (a,b,c)多列索引和 (a,c,b)是不一样的,看上面的图也看得出来关系顺序是不一样的;
分析几个实际例子来加强理解;
分析句子中使用的索引情况

(0)    select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了,而且都发挥了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨,where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样
(2)    select * from mytable where a=3 and c=7;
a用到索引,b没有用,所以c是没有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因为a索引没有使用,所以这里 bc都没有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用,c没有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引,b在结果排序中也用到了索引的效果,前面说了,a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b没有用到索引,排序中a也没有发挥索引效果

补充一个:

快速生成1000W测试数据库;

创建测试表:

create table user (  
    id int(10) not null auto_increment,   
    uname  varchar(20) ,  
    regtime  char(30)  ,  
    age  int(11)   ,
    primary key (id)
)  
engine=myisam default charset=utf8 collate=utf8_general_ci  ,
auto_increment=1 ;

编写存储过程:

delimiter $$
SET AUTOCOMMIT = 0$$
 
create  procedure test()
begin
declare v_cnt decimal (10)  default 0 ;
dd:loop
          insert  into user values
    (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50);
      commit;
        set v_cnt = v_cnt+10 ;
           if  v_cnt = 10000000 then leave dd;
          end if;
         end loop dd ;
end;$$
 
delimiter ;

调用存储过程:

call test();
### 创建和使用 MySQL 聨合索引MySQL 中,联合索引是一种通过组合多个字段来提高查询效率的方式。当查询涉及多个条件时,合理设计联合索引能够显著减少 I/O 开销并提升性能。 #### 创建联合索引 可以通过 `CREATE INDEX` 或者 `ALTER TABLE` 命令创建联合索引。以下是两种方式的具体语法: 1. 使用 `CREATE INDEX` 创建联合索引: ```sql CREATE INDEX index_name ON table_name (column_a, column_b, column_c); ``` 2. 使用 `ALTER TABLE` 添加联合索引: ```sql ALTER TABLE table_name ADD INDEX index_name (column_a, column_b, column_c); ``` 上述语句中的 `(column_a, column_b, column_c)` 定义了联合索引的组成顺序。此顺序非常重要,因为 MySQL 遵循 **前缀原则**[^1],即只有满足从到右连续匹配的部分才能被利用。 #### 使用联合索引优化查询 为了充分利用联合索引,在编写 SQL 查询时需注意以下几点: - 查询条件应尽可能覆盖联合索引定义的侧部分字段。 - 如果联合索引为 `(column_a, column_b, column_c)`,那么查询中至少要包含 `column_a` 的过滤条件,否则该联合索引可能无法完全生效。 下面是一个具体的例子展示如何构建和应用联合索引: 假设有一张名为 `users` 的,其结构如下: | id | name | age | gender | |----|---------|-----|--------| | 1 | Alice | 25 | F | | 2 | Bob | 30 | M | 如果经常执行这样的查询: ```sql SELECT * FROM users WHERE age = 30 AND gender = 'M'; ``` 可以为此场景创建一个联合索引: ```sql CREATE INDEX idx_age_gender ON users(age, gender); ``` 这样做的好处在于,MySQL 可以直接定位符合条件的数据行而无需扫描整个。 #### 注意事项 尽管联合索引能带来性能上的优势,但也存在一些局限性和注意事项: - 索引维护成本较高:每当数据发生变化(如插入、更新或删除),都需要同步修改对应的索引条目。 - 存储空间占用增加:每新增一条记录,相应的索引也需要额外存储资源支持。 - 不恰当的设计可能导致浪费:例如对于很少使用的查询路径设置复杂的大范围联合索引并不明智。 ### 示例代码 这里提供一段完整的脚本用于演示联合索引的实际操作过程: ```sql -- 创建测试 CREATE TABLE test_table ( id INT PRIMARY KEY, col_a VARCHAR(50), col_b INT, col_c DATE ); -- 插入模拟数据 INSERT INTO test_table VALUES (1,'A',10,'2023-01-01'), (2,'B',20,'2023-02-01'); -- 创建联合索引 CREATE INDEX idx_ab ON test_table(col_a, col_b); -- 执行带有多条件筛选的查询 EXPLAIN SELECT * FROM test_table WHERE col_a='A' AND col_b=10; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值