0203使用规则-索引-MySQL

1 联合索引

1.1 最左前缀法则

在Mysql数据库中创建了联合索引（或称复合索引，即包含多个列的索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引最左列开始，不跳过中间列。

• 如果跳过某一中间列，索引将部分失效（后面的字段索引失效）。

示例，以之前的tb_user1表为例,数据如下表所示：

id	name	phone	email	profession	age	gender	status	createtime
1	吕布	17799990000	[e-mail]	软件工程	23	1	6	2001-02-02 00:00:00
2	曹操	17799990001	[e-mail]	通讯工程	33	1	0	2001-03-05 00:00:00
3	赵云	17799990002	[e-mail]	英语	34	1	2	2002-03-02 00:00:00
4	孙悟空	17799990003	[e-mail]	工程造价	54	1	0	2001-07-02 00:00:00
5	花木兰	17799990004	[e-mail]	软件工程	23	2	1	2001-04-22 00:00:00
6	大乔	17799990005	[e-mail]	舞蹈	22	2	0	2001-02-07 00:00:00
7	露娜	17799990006	[e-mail]	应用数学	24	2	0	2001-02-08 00:00:00
8	程咬金	17799990007	[e-mail]	化工	38	1	5	2001-05-23 00:00:00
9	项羽	17799990008	[e-mail]	金属材料	43	1	0	2001-09-18 00:00:00
10	白起	17799990009	[e-mail]	机械工程及其自动化	27	1	2	2001-08-16 00:00:00
11	韩信	17799990010	[e-mail]	无机非金属材料工程	27	1	0	2001-06-12 00:00:00
12	荆轲	17799990011	[e-mail]	会计	29	1	0	2001-05-11 00:00:00
13	兰陵王	17799990012	[e-mail]	工程造价	44	1	1	2001-04-09 00:00:00
14	狂铁	17799990013	[e-mail]	应用数学	43	1	2	2001-04-10 00:00:00
15	貂蝉	17799990014	[e-mail]	软件工程	40	2	3	2001-02-12 00:00:00
16	妲己	17799990015	[e-mail]	软件工程	31	2	0	2001-01-30 00:00:00
17	芈月	17799990016	[e-mail]	工业经济	35	2	0	2000-05-03 00:00:00
18	嬴政	17799990017	[e-mail]	化工	38	1	1	2001-08-08 00:00:00
19	狄仁杰	17799990018	[e-mail]	国际贸易	30	1	0	2007-03-12 00:00:00
20	安琪拉	17799990019	[e-mail]	城市规划	51	2	0	2001-08-15 00:00:00
21	典韦	17799990020	[e-mail]	城市规划	52	1	2	2000-04-12 00:00:00
22	廉颇	17799990021	[e-mail]	土木工程	19	1	3	2002-07-18 00:00:00
23	后羿	17799990022	[e-mail]	城市园林	20	1	0	2002-03-10 00:00:00
24	姜子牙	17799990023	[e-mail]	工程造价	29	1	4	2003-05-26 00:00:00

tb_user1表中索引如下所示：

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
tb_user1	0	PRIMARY	1	id	A	24				BTREE			YES	
tb_user1	0	idx_tb_user1_phone	1	phone	A	24				BTREE			YES	
tb_user1	1	idx_tb_user1_name	1	name	A	24				BTREE			YES	
tb_user1	1	idx_tb_user1_pro_age_sta	1	profession	A	16			YES	BTREE			YES	
tb_user1	1	idx_tb_user1_pro_age_sta	2	age	A	22			YES	BTREE			YES	
tb_user1	1	idx_tb_user1_pro_age_sta	3	phone	A	24				BTREE			YES	

其中有我们之前创建的联合索引idx_tb_user1_pro_age_sta,字段及顺序profession,age,status

测试开始

• 执行如下sql语句

  explain select * from tb_user1 where profession='软件工程' and age=31 and `status`='0';
  -- 结果
  id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	54	const,const,const	1	100.00	
  

  • 字段过滤顺序profession,age,status,符合最左前缀法则，其中key_len为54

• 执行如下sql语句

  explain select * from tb_user1 where profession='软件工程' and age=31;
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	49	const,const	1	100.00	
  

  • 字段过滤顺序profession,age 符合最左前缀法则，其中key_len为49，status字段长度为5

• 执行如下sql语句

  explain select * from tb_user1 where profession='软件工程';
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47	const	4	100.00	
  

  • 字段过滤顺序profession符合最左前缀法则，其中key_len为47，profession字段长度47，age字段长度2

• 执行如下sql语句

  explain select * from tb_user1 where age=31 and `status`='0';
  -- 查询结果
  1	SIMPLE	tb_user1		ALL					24	4.17	Using where
  

  • 缺失索引最左侧列，符合最左前缀法则，索引失效，使用全表扫描。

• 执行如下sql语句

  explain select * from tb_user1 where profession='软件工程' and `status`='0';
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47	const	4	10.00	Using index condition
  

  • 缺失索引对应的age列，age列对应索引及之后部分索引失效

• 执行如下sql语句

  explain select * from tb_user1 where  `status`='0' and profession='软件工程' and age=31;
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	54	const,const,const	1	100.00	
  

  • 最左前缀法则和sql中放的位置无关，只是看是否按创建索引顺序有没有（包含）。

1.2 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

以上述表为例

• 执行sql语句

  explain select * from tb_user1 where profession='软件工程' and age>30 and `status`='0';
  -- 查询结果
  1	SIMPLE	tb_user1		range	idx_user1_profession_age_sta	idx_user1_profession_age_sta	49		2	10.00	Using index condition
  

  • age列索引右侧status列索引失效

• 执行sql语句

  explain select * from tb_user1 where profession='软件工程' and age>=30 and `status`='0';
  -- 查询结果
  1	SIMPLE	tb_user1		range	idx_user1_profession_age_sta	idx_user1_profession_age_sta	54		2	10.00	Using index condition
  

  • 业务条件允许的情况下范围查询尽量使用>=或者<=

2 索引失效

2.1 常见情况

• 索引列运算：不要在索引列上进行运算操作，否则索引将失效

  • 我们想查询手机号后两位为15的人员信息

    explain select * from tb_user1 where SUBSTR(phone,10,2)='15';
    -- 查询结果
    1	SIMPLE	tb_user1		ALL					24	100.00	Using where
    

• 字符串不加单引号：字符串类型字段使用时，不加引号，单列索引将失效，联合索引当前列及之后索引列失效。

  explain select * from tb_user1 where phone = 17799990002;
  -- 查询结果
  1	SIMPLE	tb_user1		ALL	idx_tb_user1_phone				24	10.00	Using where
  explain select * from tb_user1 where phone = '17799990002';
  -- 查询结果
  1	SIMPLE	tb_user1		const	idx_tb_user1_phone	idx_tb_user1_phone	46	const	1	100.00	
  explain select * from tb_user1 where profession='软件工程' and age=31 and `status`=0;
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	49	const,const	1	10.00	Using index condition
  -- 虽然使用了索引但是status 字段没加单引号该列索引失效
  

• 模糊匹配：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

  explain select * from tb_user1 where profession like '软件%';
  -- 查询结果
  1	SIMPLE	tb_user1		range	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47		4	100.00	Using index condition
  explain select * from tb_user1 where profession like '%工程';
  -- 查询结果
  1	SIMPLE	tb_user1		ALL					24	11.11	Using where
  

• or链接的条件：用or链接的条件，如果任一条件没有索引，那么所有索引都不会生效；当所有条件列都有索引，索引才会生效；

  explain select * from tb_user1 where age=20 or id=1;
  -- 查询结果
  1	SIMPLE	tb_user1		ALL	PRIMARY				24	13.75	Using where
  explain select * from tb_user1 where id=20 or phone='1111';
  -- 查询结果
  1	SIMPLE	tb_user1		index_merge	PRIMARY,idx_tb_user1_phone	PRIMARY,idx_tb_user1_phone	4,46		2	100.00	Using union(PRIMARY,idx_tb_user1_phone); Using where
  

  • age列没有单列索引，也没在联合索引的最左侧。

2.2 数据分布影响

• 如果MySQL评估使用索引比全表慢，则使用索引。

  explain select * from tb_user1 where phone >= '17799990012';
  -- 查询结果
  1	SIMPLE	tb_user1		range	idx_tb_user1_phone	idx_tb_user1_phone	[phone]	Using index condition
  explain select * from tb_user1 where phone >= '17799990011';
  -- 查询结果
  1	SIMPLE	tb_user1		ALL	idx_tb_user1_phone				24	54.17	Using where
  

  • 结果集记录大于等于一半时，全表扫描；小于一半时，使用索引；

• 数据分布对null和not null影响

  -- 表中profession 字段值全部非null
  explain select * from tb_user1 where profession is null;
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47	const	1	100.00	Using index condition
  -- 把professon 字段全部置为null，在此直线上述查询，结果
  1	SIMPLE	tb_user1		ALL	idx_user1_profession_age_sta				24	100.00	Using where

3 SQL提示

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

现在我们在tb_user1表的profession字段上有单列索引和联合索引，默认使用哪个索引呢？

explain select * from tb_user1 where profession='软件工程';
-- 查询结果
1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta,idx_user1_pro	idx_user1_profession_age_sta	47	const	4	100.00	

• 默认使用的联合索引

• use index：建议使用索引

  explain select * from tb_user1 use index(idx_user1_pro) where profession='软件工程';
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_pro	idx_user1_pro	47	const	4	100.00	
  

• ignore index：忽略索引

  explain select * from tb_user1 ignore index(idx_user1_pro) where profession='软件工程';
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47	const	4	100.00	
  

• force index：强制使用索引

  explain select * from tb_user1 FORCE index(idx_user1_pro) where profession='软件工程';
  -- 查询结果
  1	SIMPLE	tb_user1		ref	idx_user1_pro	idx_user1_pro	47	const	4	100.00	
  

4 覆盖索引和回表查询

覆盖索引是一种优化技术，可以提高查询性能，特别是在查询涉及到大量数据列时。当一个查询可以直接使用索引来满足查询需求，而无需访问实际的数据行时，就称之为覆盖索引。

覆盖索引的优势在于，它可以减少磁盘I/O和CPU开销，因为数据库不需要访问实际的数据行，而只需读取索引所包含的列数据即可完成查询。这对于大型数据表和复杂查询特别有用，可以显著提高查询性能。

要创建一个覆盖索引，需要确保索引包含了查询所需的所有列。通常，只需要在SELECT子句中列出需要的列，而不需要SELECT *（选择所有列）。然后，创建一个多列索引，包括查询所需的列。当查询时，MySQL将使用该索引来直接满足查询需求。

需要注意的是，覆盖索引在某些情况下可能不适用或效果有限。例如，如果查询涉及大量的列或复杂的计算，覆盖索引可能无法满足查询需求。此外，过多的索引可能会增加写操作的开销，因为每次修改数据时，都需要更新索引。因此，在创建索引时需要权衡索引的数量和覆盖的列，以及对读写性能的影响。

MySQL的回表查询是在使用非覆盖索引的情况下，当需要获取查询结果中的列不在索引中时，MySQL需要通过回表操作来获取缺失的列数据。

当执行一个查询时，MySQL会首先使用索引定位到满足查询条件的行，但索引中只包含了部分列的数据。如果查询结果需要包含其他列，MySQL就需要通过回表操作去主键索引或聚集索引中查找并获取这些列的数据。

回表查询的过程可以分为两个步骤：

1. 使用索引定位：MySQL首先使用非覆盖索引定位到满足查询条件的行，这一步的效率相对较高，因为索引的数据量通常比实际数据行少。
2. 回表获取数据：MySQL通过找到的索引中的主键或聚集索引的值，再去主键索引或聚集索引中查找并获取查询结果所需的其他列数据。这一步需要额外的I/O操作，因为需要读取实际的数据行。

示例如下：

explain select id, profession, age, status from tb_user1 where profession='软件工程' and age=31 and `status`='0';
-- 查询结果
1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	54	const,const,const	1	100.00	Using indexexplain select profession, email from tb_user1 where profession = '软件工程';
-- 查询结果
1	SIMPLE	tb_user1		ref	idx_user1_profession_age_sta	idx_user1_profession_age_sta	47	const	4	100.00	explain select profession from tb_user1 where status = '0';
-- 查询结果
1	SIMPLE	tb_user1		index	idx_user1_profession_age_sta	idx_user1_profession_age_sta	54		24	10.00	Using where; Using index

5 前缀索引

• 概述

MySQL的前缀索引是一种索引技术，它允许你为索引的列指定一个前缀长度，而不是使用完整的列值作为索引。通过指定较短的前缀长度，可以减小索引的大小，提高查询性能和减少存储空间的需求。

前缀索引的主要优势在于减小了索引的大小，因为只存储了列值的前缀而不是完整的列值。这样可以减少磁盘I/O和内存使用，特别是在具有大量数据和较长列的表中，对查询性能的提升较为显著。

• 前缀长度

  1. 查询条件必须符合索引的前缀。只有当查询条件也是使用相同前缀长度的列值进行匹配时，MySQL才能使用前缀索引来加速查询。否则，MySQL将无法有效地使用该索引。
  2. 前缀长度过短可能导致索引选择性下降。选择性指不重复的索引值和数据表总记录的比值，索引选择性越好，查询效率越高。
  3. 需要权衡前缀长度和查询性能。前缀长度越短，索引的大小越小，但查询性能可能会受到影响。需要根据具体的查询模式和数据分布来选择合适的前缀长度。

• 语法

  create index xxx on table_name(column(长度));
  

选择性查看：

select count(distinct SUBSTRING(email,1,10))/count(*) from tb_user1;
-- 查询结果
1.0000select count(distinct SUBSTRING(email,1,5))/count(*) from tb_user1;
-- 查询结果
0.9583select count(distinct SUBSTRING(email,1,4))/count(*) from tb_user1;
-- 查询结果
0.9167

• 如果要平衡选择性和性能，可以截取5位

create index idx_email_5 on tb_user1(email(5));
-- 索引结果
tb_user1	1	idx_email_5	1	email	A	23	5		YES	BTREE			YES	
-- 查询示例
explain select * from tb_user1 where email like '17799%';
1	SIMPLE	tb_user1		range	idx_email_5	idx_email_5	23		2	100.00	Using where

6 单列和联合索引

单列索引和联合索引是在MySQL中用于优化查询性能的两种索引类型。

1. 单列索引（Single-Column Index）： 单列索引是指只针对单个列创建的索引。它可以加速根据该列进行等值比较（例如"=“）或范围比较（例如”<“、”>"）的查询。当查询涉及到单个列时，单列索引是最常见和简单的索引类型。

2. 联合索引（Composite Index）： 联合索引是指基于多个列创建的索引，也称为复合索引或多列索引。它可以加速涉及到联合索引中的多个列的查询条件的查询。当查询条件涉及到多个列时，联合索引可以提供更好的查询性能。

• 示例

  -- 两个单列索引情况
  explain select id, phone, name from tb_user1 where name='项羽' and phone='17799990008';
  -- 查询结果
  1	SIMPLE	tb_user1		const	idx_tb_user1_phone,idx_tb_user1_name	idx_tb_user1_phone	46	const	1	100.00	
  

  • 使用phone字段建立的索引，并且回表查询

  -- 同一字段上既有单列索引又联合索引时，可以指定索引 见## 3 SQL提示
  

  联合索引的创建顺序非常重要。MySQL使用联合索引按照索引的列顺序进行排序和存储数据。因此，如果查询条件只涉及到联合索引的前缀列，那么MySQL可以有效地使用该索引。这就是最左前缀法则，可以帮助优化查询性能。

  需要注意的是，使用过多的索引可能会增加写操作的开销，因为每次修改数据时，都需要更新索引。因此，在创建索引时需要谨慎权衡索引的数量和覆盖的列，以及对读写性能的影响。

  综上所述，单列索引和联合索引都是优化查询性能的重要工具。选择何种索引类型，取决于查询的特点和需求。在设计和创建索引时，需要考虑查询模式、数据分布和性能需求等因素，以获得最佳的查询性能。

7 设计原则

在设计索引时，可以考虑以下原则来提高查询性能和减少存储空间的需求：

1. 针对表数据量大，且查询频繁的表建立索引；
2. 选择合适的索引列：选择那些经常用于查询条件、连接操作或排序/分组操作的列作为索引列。重点考虑经常被查询的列和高选择性的列，以获得更好的索引效果。
3. 注意索引的长度和类型：对于字符串列，可以考虑只选择前缀作为索引，以减小索引的大小。对于数值列，使用适当的数据类型，以减少存储空间和提高索引效率。
4. 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提供查询效率；
5. 避免过多索引：过多的索引会增加存储空间和维护开销，并可能降低写操作的性能。只创建那些真正需要的索引，权衡索引的数量和覆盖的列。
6. 定期维护索引：随着数据的增加和修改，索引可能需要进行重建或优化。定期检查和维护索引的统计信息，以保持索引的有效性和性能。
7. 注意查询的顺序：对于复杂的查询语句，考虑查询的顺序和使用子查询来利用索引。优化查询语句的结构和逻辑，以充分利用索引的优势。
8. 如果列不能存储NULL值，建表时使用NOT NULL 约束。

综上所述，索引设计是一个权衡和优化的过程。需要根据具体的应用需求、数据分布和查询模式来选择合适的索引策略。理解数据和查询的特点，并遵循上述原则，可以提高查询性能和优化数据库的使用。

结语

如果小伙伴什么问题或者指教，欢迎交流。

❓QQ:806797785

参考链接:

[1]MySQL数据库视频[CP/OL].2020-04-16.p79-88.