1.多张表查问的索引剖析

2.在什么状况下适宜/不适宜建设索引

3.如何防止索引生效

4.查问优化

1.多张表查问的索引剖析

假如咱们当初有三张表：

CREATE TABLE `t_student` (  `id` int NOT NULL AUTO_INCREMENT,  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '学生名字',  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生表';
CREATE TABLE `t_class` (  `id` int NOT NULL AUTO_INCREMENT,  `class_name` varchar(255) COLLATE utf8_bin DEFAULT NULL,  `create_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='课程表';
CREATE TABLE `t_score` (  `id` int NOT NULL AUTO_INCREMENT,  `student_id` int DEFAULT NULL,  `class_id` int DEFAULT NULL,  `score` int DEFAULT NULL COMMENT '得分',  `create_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='分数表';

假如咱们要对这三张表进行联查，如何能力建设优良的索引？

1)两张表联查

假如咱们有这样一个查问，查问学生的分数

select stu.name,sc.score from t_student stu left join t_score sc on stu.id = sc.student_id

咱们应用执行打算剖析一下：

能够看出，type都是ALL，咱们在深刻了解Mysql——性能剖析explain里说过，ALL是最差的一种后果，根本就等于全表扫描了，所以在这里，咱们适度地给这个查问做一个优化，加上索引，那么，咱们应该如何减少索引？个别会有三种选项：

左表减少索引，右表减少索引，两张表都减少索引。

左表减少索引：
其实咱们从这个查问能够看出，student表的id字段是主键，人造自带索引，然而却没有用到，是因为，咱们用的是left join(右边全有)，而且没有在where前面对右边这张表进行筛选，于是没有成果，由此能够得出，左边表是咱们的关键点，肯定要建设索引。

右表减少索引：

ALTER TABLE `test`.`t_score` ADD INDEX `idx_student_id`(`student_id`) USING BTREE;

咱们来看一下执行打算：


能够看出，左边这张表，type曾经变成了ref(应用到了索引，然而索引不是惟一索引),就缩小了被驱动表的扫描数量。

两张表都减少索引：
从下面的两种索引减少形式来看，如果是left join / right join等形式，被连贯的表的关联字段，加上索引之后，就能够大大减少扫描的数量，如果是join或者where前面有对主表的筛选，那也能够在筛选条件上加上字段（筛选条件如何减少索引下文会说），这样就能够缩小两张表扫描的数量。

2）三张表联查

咱们来看一个三表查问：

select stu.name,sc.score from t_student stu  left join t_score sc on stu.id = sc.student_id left join t_class cla on cla.id  = sc.class_id

三表以及多表查问，其实建设准则也和二表查问差不多，如果是left / right join，被连贯的表的字段要加上索引，主表的索引得看where条件前面是如何筛选的，如果没有where条件，能够不增加，反正都是须要全表扫描。

2.在什么状况下适宜/不适宜建设索引

适宜：
2.1.1）主键主动建设惟一索引

2.12）离散型越高的字段类型越适宜建设索引

因为索引自身就是树形构造，离散型越高，就能越准确地查找出节点，就能更快地排除出不须要的数据，如果这个值所有值都靠近雷同，那么索引就没有意义。

2.13）频繁作为查问的条件字段应该创立索引

如果咱们有一个user表，咱们常常要用到phone来查问用户，那么在这个频繁应用的查问字段上建设索引就显得尤其重要。

2.1.4）查问中与其余表关联的字段，外键关系建设索引

2.1.5）单键/组合索引的抉择，在高并发的状况下偏向于组合索引

2.1.6）查问中的排序字段，排序字段如果通过索引去拜访将大大提高排序速度

2.1.7）查问中的统计或者分组的字段，能够建设索引

不适宜：
2.2.1）频繁更新的字段不适宜建设索引，因为每次更新不单单是更新了记录还会更新索引，减轻IO累赘
如果咱们频繁更新索引中的值，那么索引树中的值就会频繁挪动，可能会造成索引碎片，减少IO的复旦。

2.2.2）where条件里用不到的字段不创立索引

2.2.3）表记录太少，不适宜建设

2.2.4）数据离散性不高的字段，为它建设索引没有太大的成果

3.如何防止索引生效
3.1）全值匹配不会造成索引生效。

3.2）最佳左前缀法令

如果这个索引是多值索引，要遵循最佳左前缀法令，值得就是搜寻条件从索引的最右边开始，保障连续性，不能跳过索引中的列。
因为索引会从左到右通过这几个值进行排序，而后再找到索引树中适合的地位进行搜寻。

3.3）不在索引列表上做任何操作（计算，函数，类型转换等等），会导致索引生效

3.4）当一个搜寻条件是范畴搜寻，则不能应用这个范畴条件左边的列。
假如咱们对a,b,c三个字段建设的索引，筛选条件为where a = 1,b > 2 。则c的搜寻条件就无奈通过索引去匹配，索引就会生效。

3.5）尽量应用笼罩索引，查问和筛选只拜访索引的值，就能够间接从索引中返回数据。

假如咱们对user表中的a和b字段建设了索引
select a,b from user where a = 1
这样就会在索引中间接返回这两个字段，因为索引曾经依据这两个字段排序过了。

3.6）mysql在应用不等于（!=）的时候，无奈应用索引，会导致全表扫描。

3.7）is null,is not null无奈应用索引

3.8）like以通配符结尾，会造成全表扫描（like '%123..'）

3.9）字符串不加单引号索引生效

3.10）少用or,用它连贯时会索引生效

4.查问优化

咱们在晚期的博客深刻了解MySql——查问性能优化说过一些查问优化的类型，这里咱们再提三种，连表（join）优化，order by 优化、group by优化、in/exists优化

4.1）连表（join）优化
要记得一句话，应用join的时候，永远要用数据量小的表去关联数据量大的表。

假如A join B，mysql会主动帮咱们做一个优化，到底是A join B (通过A表的所有数据去查问B表)，还是B join A（通过B表的所有数据去查问A表），数据少的表会被当做主表，能够缩小大量的筛选。

4.2）order by 优化

order by尽可能地在索引列表上实现排序操作，请遵循索引的最佳左前缀准则。

如果不在索引列上，filesort有两种算法：单路和多路排序

双路排序：晚期mysql4.1之前就是应用双路排序，要通过两次扫描，第一次将要排序的全副值进行排序，第二次再通过这些值去原表查问数据。家喻户晓，IO是很耗时的，所以呈现了单路排序。

单路排序：将所有要用的字段和要排序的字段全副取出，一次性全排序，而后返回后果。它的效率更快一些，防止了二次读取数据，把随机IO变成了程序IO，然而这种形式很占空间，如果设置的排序缓冲空间不够，所以可能会呈现取出局部数据屡次排序，从而屡次IO。

在这里，咱们能够减少增大sort_buffer_size，max_length_for_sort_data参数的设置，来加大排序缓冲区。

4.3）group by优化

group by本质是先排序后进行分组，遵循索引建的最佳左前缀

4.4）in/exists优化

咱们查问一条数据存在不存在，往往有两种写法：

select a.* from A a where a.id in (select b.id from B b)

或者

select a.* from A a where exists (select 1 from B b where b.id = a.id)

下面两种办法有什么区别呢？

in 将查问进去的数据进行全值匹配

exists 将查问进去的数据，放到子查问做验证，依据验证后果（true or false），来决定主查问的数据结构是否得以保留。

in是子查问为驱动表，里面的表为被驱动表，故实用于子查问后果集小而里面的表后果集大的状况。

exists是里面的表位驱动表，子查问外面的表为被驱动表，故实用于里面的表后果集小而子查问后果集大的状况。

所以说，小表驱动大表
当A数据量>B数据量 用in
当A数据量<B数据量 用exists