MySQL——3、索引优化

1.1 *数据库优化之索引*

1.1.1 *定位低效率的SQL语句*

1.1.1.1 *慢日志*

启动log-slow-queries[=file_name]选项，会在所有sql语句执行完成之后，会生成一个包含所有sql语句执行时间，超过long_query_time秒的日志文件

要找出项目中的慢Sql时

1、关闭数据库服务器(关闭服务)

2、把慢查询记录到日志中

3、设置慢查询时间

4、找出日志中的慢查询SQL

1.1.1.2 *show processlist命令*

慢日志只能在sql语句执行完成之后生成，而show processlist命令可以查看当前MySQL在进行的线程，包括：线程的状态、是否锁表

可以实时的查看sql的执行状态

1.1.2 *explain分析低效率sql语句*

可以通过explain或desc命令，获取mysql如何执行select语句的信息，包括select语句执行过程中，表如何连接，连接的顺序等

1.1.3 *索引优化*

可以对a表建立索引，

再次分析该条sql语句：

可以查看a表的执行结果如下：

常见的慢查询优化

1.1.3.1 *索引没起作用的情况*

1、使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

2、使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

1.1.3.2 *优化数据库结构*

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1、将字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询，改为对中间表的查询，以此来提高查询效率。

3、分解关联查询

将一个大的查询分解为多个小查询是很有必要的。

4、优化LIMIT分页

尽可能的使用索引覆盖扫描，而不是查询所有的列。然后，根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升

5、分析具体的SQL语句

两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。

联合查询尽可能使用inner join查询

2 *优化LIMIT分页*

对于下面的查询：

​ select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件的前M条记录，之后舍弃这M条记录并从第M+1条记录开始，再依次找到N条满足条件的记录。

如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

方法一：虑筛选字段（title）上加索引

​ title字段加索引 （此效率如何未加验证）

方法二：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三：“关延迟联”

如果这个表非常大，那么这个查询可以改写成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

方法四：建立复合索引 acct_id和create_time

select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

注意sql查询慢的原因都是:引起filesort

3 *联合查询尽可能使用inner* *join*

5、分析具体的SQL语句

两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。

例如： select * from a where id in (select id from b );

对于这条sql语句它的执行计划其实并不是先查询出b表的所有id，然后再与a表的id进行比较。

mysql会把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：select * from a where exists(select * from b where b.id=a.id );

而exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较，比较的条件是a.id=b.id . 看a表的每条记录的id是否在b表存在，如果存在就行返回a表的这条记录。

由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。

如何优化？

建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。

这样优化够了吗？还差一些。

由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。

但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的。

为什么要反过来？

​ 因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问：在双方两个表的id字段上都建有索引时，到底是a表查b表的效率高，还是b表查a表的效率高？

该如何进一步优化？

​ 把查询修改成inner join连接查询：select * from a inner join b on a.id=b.id; （但是仅此还不够，接着往下看）

为什么不用left join 和 right join？

​ 这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。

为什么使用inner join就可以？

​ inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。

那我们又怎么能知道a和b什么样的执行顺序效率更高？

​ 你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。

​ 在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。

利用explain字段查看执行时运用到的key（索引）

​ 而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。

3.0.1 *使用索引*

如果是用到的是左边的列，即便不是使用的索引的组合条件，仍然会用到索引

如果使用的是右边的列，则不会被索引

3.0.2 *存在索引但不使用索引*

创建复合索引，单独查询其中一列，如果查询的是复合索引中的第一列，则会使用索引，如果查询的不是第一列，即便该列属于复合索引的一部分，也不会使用索引