数据库中所有的表都建索引_可以为表中每一列都创建索引

admin

作者

概述

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据数据库中所有的表都建索引，在关联表时我们要遵循一些原则，这样会使我们编写的SQL 语句在效率上快很多。

一、需要优化的查询

使用explain常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL 其扫描速度由快到慢数据库中所有的表都建索引；

如果Extra出现数据库中所有的表都建索引了

Using temporary；有分页时出现了Using filesort则表示使用不了索引rows过多，或者几乎是全表的记录数；key 是 (NULL)；possible_keys 出现过多（待选）索引

都需要做优化。

二、驱动表的含义

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果：

EXPLAIN 结果中，第一行出现的表就是驱动表对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!），即using temporary;[驱动表] 的定义为：1）满足查询条件的记录行数少的表为[驱动表]；2）未指定查询条件时，行数少的表为[驱动表]（Important!）。优化的目标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：永远用小结果集驱动大结果集（Important!）！：A JOIN B，A为驱动，A中每一行和B进行循环JOIN，看是否满足条件，所以当A为小结果集时，越快。NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要，驱动表的数据小可以显著降低扫描的行数。

MySQL的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量也不可能小，而且每次循环都不能避免消耗CPU，所以 CPU 运算量也会跟着增加。

当有order by条件时，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解释语句；

如果第一行的驱动表为a，则效率会非常高，无需优化；否则，因为只能对驱动表字段直接排序的缘故，会出现using temporary，所以此时需要使用STRAIGHT_JOIN明确a为驱动表，来达到使用a.col上index的优化目的；或者使用left join，而STRAIGHT_JOIN为inner join且使用a作为驱动表。三、Mysql的STRAIGHT_JOIN

STRAIGHT_JOIN，在数据量大的联表查询中灵活运用的话，能大大缩短查询时间。

首先来解释下STRAIGHT_JOIN到底是用做什么的：

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

意思就是说STRAIGHT_JOIN功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

需要注意的是：

STRAIGHT_JOIN只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）

尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。而且你可能不能确定未来表的大小变化。使用STRAIGHT_JOIN一定要慎重。

四、in、exists、join与驱动表的选择关系

以下两张表的dept_id字段应建立索引。

1、in

SELECT * FROM t_emp WHERE dept_id IN (SELECT dept_id FROM t_dept);// 这条SQL 语句相当于：for SELECT dept_id FROM t_dept for SELECT * FROM t_emp WHERE t_emp.dept_id = t_dept.dept_id

虽然SQL 语句是主查询员工信息，子查询部门id ，但是MySql 的执行顺序会先执行子查询，再执行主查询，然后获得我们要查询的数据。

通过上面IN 操作的执行顺序，可以看到是先查询部门表再根据部门表查出来的id 信息查询员工信息。一般情况下员工表肯定会有很多的员工信息，但是部门表只会有很少的数据信息，如果先通过查询部门表信息查询员工信息，以小表(t_dept)的查询结果，去驱动大表(t_emp)，这种查询方式是效率很高的

2、exists

SELECT * FROM t_emp WHERE EXISTS (SELECT 1 FROM t_dept WHERE t_dept.dept_id = t_emp.dept_id);// 这条SQL 语句相当于： for SELECT * FROM t_emp for SELECT * FROM t_dept WHERE t_dept.dept_id = t_emp.dept_id

将主查询的数据放在子查询中做条件验证，根据结果TRUE 和 FALSE 来决定主查询中的数据是否需要保留。EXISTS 子查询只返回TRUE 或 FALSE ，因此子查询中的SELECT * 可以是SELECT 1 或者其他，MySql 的官方说在实际执行时会忽略SELECT 清单，因此是没有什么区别的。EXISTS 子查询其实在执行时，MySql 已经对它做了一些优化并不是对每条数据进行对比。

可以看出使用EXISTS 查询时，首先查询员工表，然后根据部门表的查询条件返回的TRUE 或者 FALSE ，再决定员工表中的信息是否需要保留。这不就是用大的数据表(t_emp) 去驱动小的数据表小的数据表(t_dept)了吗？虽然这种方式也可以查出想要的数据，但是这种查询方式是不值得提倡的。

3、join

3.1、join选择驱动表

为什么一般情况下join的效率要高于left join很多？一般情况下参与联合查询的两张表都会一大一小，如果是join，在没有其他过滤条件的情况下MySQL会选择小表作为驱动表，但是left join一般用作大表去join小表，而left join本身的特性决定了MySQL会用大表去做驱动表，这样下来效率就差了不少，

总之：

1).当使用left join时，左表是驱动表，右表是被驱动表

2).当使用right join时，右表时驱动表，左表是驱动表

3).当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

考虑到查询效率，能用join就不要用left\right join 使用外连接非常影响查询效率，就算要用也要用数据量最小的表作为驱动表来驱动大表。

3.2、join查询在有索引条件下

驱动表有索引不会使用到索引被驱动表建立索引会使用到索引

在以小表驱动大表的情况下，再给大表建立索引会大大提高执行速度

3.3、left join 的通用法则

凡是不符合 WHERE 子句中条件的记录都不会参与连接。只要在搜索条件中指定关于被驱动表相关列的值不为 NULL，那么外连接中在被驱动表中找不到符合 ON 子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了

即：如果where条件中含有右表的非空条件（除开is null），则left join语句等同于join语句，可直接改写成join语句。

3.4、如何优化left join：