mysql查询优化索引篇

     其实在写这篇文章之前,也对查询优化做过一些设置,但这次则更为具体一点,之前做的无非就是增加查询字段的索引,让select里和where里的内容全部都包含在索引内(覆盖索引不走回表的基本概念),但这次这么做的时候发现了一些问题,这也是我接下来要提到的,而且之前使用的是sqlserver的数据库做的优化,虽然数据量比较大，有1000W多条.但其实创建索引的部分则是我们领导建的,自己则是在查询语句和后台程序这块下功夫。而这次则是自己亲历亲为,接下来就开始展示这次项目的需求以及遇到的问题

    我们都知道想要检测一条查询语句能通过EXPLAIN关键字(具体语法请自行查阅)来判断是否可以优化,很显然,在优化之前的语句type类型为ALL,走的是全表扫描,也就是最慢的一个级别,所以我这次优先从这个地方开始下手,先把需要查询的字段全部加上索引:

ALTER TABLE 表名称 ADD INDEX 索引名称(字段1,字段2,字段3......);

      然后执行就报错了,原来是索引也是有范围限制的,之前查询的字段都是按照255长度来设置的,所以我还必须先把这些字段的长度给修改一下,对于那种固定的格式,比如状态,类型如果是用数字的话可以固定给1个长度,还有一些比如手机号,运单号等相对固定的格式,也可以给刚刚足够的长度即可.当我把长度全部重新设置了一遍:

ALTER TABLE 表名称
MODIFY COLUMN 字段1 VARCHAR(20),
MODIFY COLUMN 字段2  VARCHAR(5),
MODIFY COLUMN 字段3 VARCHAR(1);
......

      但修改完了发现还是报错,Too many key parts specified; max 16 parts allowed 通过查阅发现mysql的单个索引最多只能添加16个字段，但如果拆分的话 索引是失效的.打个比方你设置了索引1里有A,B,C这3个字段,然后又添加了一个索引2,里面增加了D,E,F这3个字段 然后你的查询语句如果是 select A,B,C,D from table 这种情况也是走的ALL全表,要么是select A,B,C from table 要么是select D,E,F from table 然后就是where的条件也要和select保持一致,如果你查询的是A,B,C 条件有D,E,F的话 索引也会失效.所以当时我这个查询有3个页面都用到了 之前是通过类别来查询不同的数据,现在则要根据类型做不同的查询条件了 因为如果全部放入到一个索引里是有限制的 

      于是我就创建了3个类型的索引,分别对应3个页面的查询列 这样一系列修改完了之后,再次执行之后级别就从之前的ALL提升到了INDEX了  如下图

      因为是联表查询,A表就是我查询的主要信息表,而B表则做了关联,同样也需要给B表的关联字段增加索引,不然B表的类型也是ALL级别.这样调整了之后 页面的整体速度有了明显的提升 感觉页面都没有刷新数据就发生了改变。然后执行搜索,第一次无缓存和第二次有缓存执行的效率如下图:

      最开始的时候是全字段查询,某些字段是text类型的,内容很多.非常的影响效率. 在第一次优化的时候就修改成点击操作的时候再触发事件通过主键ID获取那些庞大的内容.而没有优化之前是一次性查询出来,然后操作的时候把这个字段带入.现在的话改为不去查询那些列表里不展示的字段(除主键),并且过于大而长的字段也最好是再用户需要的时候再获取,类似于懒加载的思想.当时第一版优化完之后本地能把查询的速度从2秒提升到1秒,这次增加了索引之后则再一次把本地的查询效率提升到了新的台阶.

      最后总结下这次的收获,首先就是字段的长度大小,很多时候没感觉到有什么区别,只是概念上知道需要设置到刚好满足的长度即可,当然前提是这种字段是相对固定的格式,而不是全部的字段都按照同一个思想来做限制,这样弄反而会有问题，导致添加或者修改的时候程序报错,数据库字段长度不够.另外一个就是如果字段设置的太长了 比如之前设置的每个字段都是255的长度,那么建立一个索引 最多就只能添加3个255长度的字段,再添加就会报错，提示长度大小超出了限制

      另外一个则是让我知道了,并不是你建立了索引就能生效,比如上面我说到的那种情况,创建了2套索引  索引1为ABC，索引2为DEF  然后查询的时候既查询了索引1的内容,又查询了所引2的部分内容 这样则不会走索引的 .因为没有建立的索引没有覆盖住查询的内容.

      还有就是之前一直对联合索引和覆盖索引的概念比较模糊,现在通过例子  比如现在我建立了一个索引1ABC字段  又建立了另一个索引2DEF字段 

ALTER TABLE table ADD INDEX index1(A,B,C);

ALTER TABLE table ADD INDEX index2(D,E,F);

查询语句	索引是否生效
select *  form table	否
select G from table	否
select A from table	是
select D from table	是
select A,B,C,D from table	否

查询的字段如果被某个索引全部命中则走索引,少于或者等于都行,大于则不行,比如D字段不在索引1里而ABC都在索引1里这样的也是不生效的