关于mysql优化:深入理解Mysql索引优化查询

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

关于mysql优化:深入理解Mysql索引优化查询

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

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

3. 如何防止索引生效

4. 查问优化

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）