mysql最左匹配原则与索引选择原则

mysql索引选择原则

• 一、explain语句分析
• 二、联合索引结构与最左匹配原则
• • 1. 联合索引结构
  • 2. 最左匹配原则
  • 3. 验证
• 三、mysql对于索引优先考虑对象
• 四、mysql索引的挑选原则

一、explain语句分析

1. 作用

	1.1 通常用于sql语句的性能分析
	1.2 打印出一条sql语句优化器的执行计划
	1.3 索引使用情况
	1.4 性能好不好

2. 语法

	 explain + 要操作的sql语句;

explain select * from article where status = 0;

3. 详细字段说明

explain分析sql语句：

id – select识别符。

	这是select的查询***，id的值越大优先级别越高，越先被执行，如果id相同，执行顺序由上至下

select_type – 表示select语句的类型。

	类型：
		SIMPLE：表示最简单的查询，不包括连接查询与子查询
		  explain select * from user where id = (select id from where name="asi");
		PRIMARY：最外层的查询 -- select * from user where id =();
		SUBQUERY：子查询 -- select id from where name="asi"
		  explain select id from where name="asi" union all select id from where name="ayu";
		UNION：合并 联合查询，union 后面的那张表就会表示成它
		UNION RESULT：联合结果
		DERIVED： 衍生查询-在select出一批自定义列的数据，概念上相当于一张表，但是该表只在语句执行过程出现和

table – 与查询语句相关的表。

partitions – 表分区，参数的使用

type – 表示的是表的连接类型。

	类型：
		①all：遍历全表数据查询

		②index：只遍历索引树上的数据查询

		③range：只检索给定范围的行，使用一个索引来选择行，条件过滤，索引定位

		④ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

		⑤null ：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

		⑥eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，
			      简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

		⑦const：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

		⑧system: system是const类型的特例，当查询的表只有一行的情况下，使用system

	效率排行榜：
	8 > 7 > 6 > 4 > 5 > 3 > 2 > 1

possible_keys – 可能使用的索引

key – 使用的索引

key_len表示mysql使用的索引字段按字节计算的长度，如果键是null，则长度为null

	注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段

ref – 表示使用哪个列或常数与索引一起来查询记录

		①all 全表查询没有使用索引
		
		②count 主键索引查询

rows – 显示mysql表中进行查询时扫描的数据量，并不是很精确（约等于）

filtered

Extra – 显示mysql在处理查询时的详细信息（额外的信息）。

	类型：
		① Using filesort: 如果根据索引列进行排序（order by 索引列）是可以用到索引的，SQL查询引擎会先根据索引列进行排序，
				    然后获取对应记录的主键id执行回表操作，如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作，
			            MySQL把这种在内存或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），
				    如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort

		② Using temporary: 许多查询的执行过程中，MySQL会借助临时表来完成一些功能，
					比如去重、排序、合并、分组之类的，比如我们在执行许多包含distinct、group by、union等子句的查询过程中，
					如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。
					如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示.

		③ USING index: 表示相应的select操作中使用了覆盖索引（Covering Index）,避免回表操作，效率不错！
				如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查找动作。

		④ Using where: 使用了where过滤

		⑤ using join buffer: 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，
					MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度

		⑥ impossible where: where子句的值总是false，不能用来获取任何元组

		⑦ select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，
					  	     不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

		⑧ distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

		⑨ Using index condition：查找使用了索引，但是需要回表查询数据

二、联合索引结构与最左匹配原则

1. 联合索引结构

1.1 简介

多个字段组合起来的索引(两个以上) idx_name_age(name,age)

2. 最左匹配原则

2.1 简介

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

2.2 建立联合索引

alter table 'user' add index idx_name_age_sex(name,age,sex);

	建立了这个联合索引相当于生成了3个索引
		idx_name_age_sex =》（name）、(name,age) 、(name,age,sex)

3. 验证

3.1 验证最左匹配原则是否与条件顺序有关？

		验证语句：
			explain select * from user where name='asi' and age=21 and sex=1; 
			explain select * from user where name='asi' and sex=1 and age=21 ; 
			explain select * from user where sex=1 and name='asi' and age=21 ; 
			explain select * from user where age=21 and name='asi' and sex=1; 
		结果：
			都可以使用到联合索引
		结论：
			只要条件当中有索引列，那么sql语句就可以命中索引，最左匹配原则与条件的先后顺序无关。

3.2 验证最左匹配原则匹配最左边的列？

		验证语句：
			explain select * from user where name='asi'; 
			explain select * from user where name='asi' and age=21; 
			explain select * from user where name='asi' and sex=1; 
			explain select * from user where age=21 and sex=1; 
		结果：
			前面两个可以使用联合索引并且是覆盖索引
			第三个使用了条件过滤
			最后一个回表了
		结论：	
			只要是按照了索引创建顺序来编写where条件，就可以使用到这个联合索引，并且大几率是覆盖索引

3.3 最左原则总结

最左匹配原则与条件中的字段顺序无关，只需要按照索引的创建顺序，最左字段存在即可。

三、mysql对于索引优先考虑对象

1. 简介

使用场景：

	要创建索引的时候要考虑到的因素（分组、排序）

sql语句：

select * from user where sex =1 and name='asi' group by id order by id;

MySQL优化器在选择索引时的策略

	mysql对于索引使用的优先级：
		where > group by > order by
		
		1. 优先过滤条件where上的字段
		2. 分组 =》排序
		3. 返回的结果默认不会作为考虑的对象，最后考虑只有覆盖索引的时候才会考虑

2. 条件与分组、排序共存的情况下

2.1 原因

当sql中where条件，分组，排序同时存在时，MySQL的优化器会优先选择条件来确定使用的索引，因为where可以减少更多的sql扫描，而排序和分组往往进行的是全表扫描。

3. 条件与排序共存

3.1 原因

所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。

4. 分组排序共存

4.1 原因

对于分组和排序共存的情况下，mysql会优先根据分组去选择索引，那是因为sql需要先将要查询的数据进行分组，随后才会进行数据的排序

5. 优化方案

将条件字段以及分组字段和排序字段，都建立在索引中

四、mysql索引的挑选原则

1.索引字段的选择

字段一般是推荐重复比较少的字段影响到数据的检索，如果是项目需求（可建立联合索引）

2. 挑选原则

1. 唯一字段可以单独建立单索引，非唯一考虑联合索引,推荐尽量使用唯一字段建立索引

2. 索引的个数，联合索引的个数 最佳 6个 以内，如果索引因为项目需求：最多 10个

3. 索引的使用遵循最左匹配原则其次覆盖索引

4. 尽量选择小的字段建立索引 int ，varchar(10)， char(5)

5. 避免<，<= ，> ,>= ,like（%），between 之间的条件（in不算在其中）。选择索引的字段的范围和模糊之前，
因为范围与模糊会引起索引失效，针对于联合索引，就是联合索引的中间尽量不要有范围查询的字段
	like中的前缀匹配还好，别的就不行了

6. 尽量多使用explain分析

7. 避免更新频繁的字段 （二叉树会一直变化，导致性能变慢）

8. 建立的索引- 优先考虑 建立 联合索引

9. 索引字段不要有 null， 不是 ''

                              六星教育--2008期mysql优化--李建宇