最左前缀原则:
简述:
查询条件中从索引的最左前列开始,并且不跳过索引中的某个列或多个列。
建表:
CREATE TABLE`index_test` (
`id`int(11) NOT NULLAUTO_INCREMENT,
`title`char(255) NOT NULL DEFAULT '' COMMENT 'title',
`content`text COMMENT 'text',
`c_time`bigint(20) NOT NULL DEFAULT '0' COMMENT 'create time',PRIMARY KEY(`id`),KEY `joint_index` (`title`,`content`(30),`c_time`) # 联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)
上表中,联合索引:
KEY `joint_index` (`title`,`content`(30),`c_time`)
原则解释:
最左列:查询条件中,必须包含 title(在查询条件中的位置,无要求),该联合索引才可以生效:包含 title,生效:
mysql> select title from index_test where title='123' and c_time=123456789;
包含title,但未在查询条件的首位, 生效:
mysql> select title from index_test where c_time=123456789 and title='123';
不包含title, 该联合索引,未生效:
mysql> select title from index_test where id=2 and c_time=123456789;
不跳过中间的某个列:生效:
mysql> select title, content, c_time from index_test where title='123' and content='wer';
生效:
mysql> select title, content, c_time from index_test where title='123' and c_time=123456789 and content='wer';
失效(跳过content列):
mysql> select title, content, c_time from index_test where title='123' and c_time=123456789;
联合索引的优势:
一个顶三个:
建了一个(a,b,c)的复合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,因为每多一个索引,都会增加写操作的开销和磁盘空间的开销。
对于大量数据的表,这可是不小的开销!
覆盖索引:关键(避免了回表操作,减少IO操作)同样的有复合索引(a,b,c),如果有如下的sql:select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一
索引列越多,筛选出的数据约少(也就越精准)
mysql> explain select title from index_test where title='123' and c_time=123456789;+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | index_test | NULL | ref | joint_index | joint_index | 1020 | const | 1 | 100.00 | Using where; Using index |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.01 sec)