第一篇MySQL索引优化系列:(一)索引的类型里面有说过复合索引是什么,也就是对表上的多个列进行索引。复合索引全用的意思就是对于建立的复合索引中包含了几个字段,查询的时候最好能全部用到,而且严格按照索引顺序,这样查询效率是最高的。当然实际使用中要按照具体情况来分析,以上只是说一个理想状况。
下面来实际操作一下:
SQL脚本:
CREATE TABLE people
(
Id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(10) DEFAULT NULL,
age
int(11) DEFAULT NULL,
sex
char(4) DEFAULT NULL,
class
varchar(10) DEFAULT NULL,
birthday
date DEFAULT NULL,
PRIMARY KEY (Id
),
KEY idx_name_age_sex_class
(name
,age
,sex
,class
) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1026 DEFAULT CHARSET=utf8;
然后插入了1024条
INSERT INTO test
.people
(name
, age
, sex
, class
, birthday
) VALUES (‘张三’, ‘17’, ‘男’, ‘1’, ‘2021-03-02’);
和1条
INSERT INTO test
.people
( name
, age
, sex
, class
, birthday
) VALUES (‘李四’, ‘25’, ‘女’, ‘2’, ‘2021-02-24’);
不同的查询条件对比:
EXPLAIN SELECT * from people where name=“李四”
=>查询时间:0.027s key_len :33
EXPLAIN SELECT * from people where name=“李四” and age=“25”
=>查询时间:0.026s key_len :38
EXPLAIN SELECT * from people where name=“李四” and age=“25” and sex=“女”
=>查询时间:0.024s key_len :51
EXPLAIN SELECT * from people where name=“李四” and age=“25” and sex=“女” and class=“2”
=>查询时间:0.023s key_len :84
=========================================================================
最左前缀也跟复合索引有关,索引的顺序要按照建立时的顺序来进行索引,不然就不会使用创立的复合索引。就是从左到右的顺序来写sqlu、语句。
还是以上面的例子来说:
EXPLAIN SELECT * from people where name=“李四” and age=“25” and sex=“女” and class=“2”
假如我们改成以下的方式来查询,来看下还会不会使用创造的复合索引?
EXPLAIN SELECT * from people where age=“25” and sex=“女” and class=“2”
EXPLAIN SELECT * from people where sex=“女”
EXPLAIN SELECT * from people where class=“2” and name=“张三” and class=“1”
EXPLAIN SELECT * from people where name=“张三” and sex=“女” and class=“2”
EXPLAIN SELECT * from people where class=“2” and name=“张三” and sex=“女”