mysql优化基础二(索引篇)

一.索引优化案例

• mysql5.5建表操作注意事项：以下代码中id和article字段的引号。

CREATE TABLE `article` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
....
)
CREATE TABLE 'article' (
'id' INT(10) UNSIGNED NOT NULL PRIMARY KEY 
....
);

1.单表优化

• 创建表并添加数据
  

• 要求查询category_id为1且comments大于1的情况下，views最多的article_id的执行计划。
  
  结论：显然type是all，即最坏的情况，且extra中还出现了using filesort，这也是最坏情况。

• 第一次索引优化：对where后的字段建立索引，并查看执行计划。
  
  
  
  结论：从第二张图可以看出，虽然在type上得到优化，但extra中的using filesort还在，故此次优化不彻底，需删除并重新建立新的优化索引。而且根据第三张图的对比，可以看出即使建立了索引表，如果其中包含的字段在被查询时使用的是范围查询，会消除其优化效果，即索引失效。

• 第二次索引优化：根据第一次的经验，可以通过避开需要范围查询的索引来建立索引。
  

• 第二次优化后查看执行计划。
  并查看执行计划。并查看执行计划。
  
  结论：type值为ref，extra中的using filesort也消失了，结果理想。

2.两表优化

• 创建表并添加数据(分别向两表中插入20条20以内的card)
  

• 要求左外连接查询，显示执行计划如下：
  
  结论：type有all，不理想。

• 第一次添加索引优化：添加book的card索引。
  

• 第二次添加索引优化：添加class的card索引。
  
  结论：综合两次索引优化可以看出，其优化的效果差异明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定要建立索引。即左连接建右索引，右连接建左索引。

3.三表优化

• 创建了一个phone表其内置数据与两表优化中的两表一样，都有card，且是二十以内随机数，进行这三张表查询得到执行计划。
  

• 添加索引优化。
  

• 查询执行计划。
  

• join语句优化总结：
  1.尽可能减少join语句中的NestedLoop的循环总次数，“永远用小结果集驱动大结果集”(例如书的种类相比于书的数量要少，就应该用种类表驱动数量表)。
  2.优先优化NestedLoop的内层循环。
  3.保证Join语句中被驱动表上Join条件字段已经被索引。（区分驱动表与被驱动表）
  4.无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer的设置。

二.避免索引失效

• 最佳左前缀法则：查询从索引的最左前列开始，且不跳过索引中的列。
  符合法则案例：从下图可知，建立了name，age，pos顺序的符合索引。
  1.第一次查询满足从索引的最左前列开始，在不添加另外两个索引列的情况下满足法则。
  2.第二次查询从索引的最左前列开始，且用到了第二个索引，虽然没有用到第三个索引列，但满足不跳过索引列。
  3.第三次查询是全索引查询，一般都是满足条件的。
  不符合法则案例：
  从下图可知，第一次查询用到的是第二和第三个索引列，没有从第一个索引列开始，故查询时索引失效，从而返回的type为all。
  第二次查询用到了第三个索引列，没有从第一个索引列开始，且跳过了第二个索引列，故索引失效，返回type为all。
  第三次查询用到了第一个和第三个索引列，典型的跳过了第二个索引列，但满足从最左前缀索引列开始，故第一个索引列没有失效，第三个索引列由于第二个索引列被跳过而索引失效。即部分索引失效。

• 不在索引列上做任何操作(计算，函数，(手动或自动)类型转换)。
  
  从上图可知，第二次相比于第一次使用了函数left()，故导致其索引失效。

• 存储引擎不能使用索引表中与范围查询条件右边的列对应的索引列。
  
  由上图可知，第二次查询相比于第一次查询的不同在于，它的age索引列使用了范围查询，从而导致它们的type不同。使用了范围查询并不意味着全部索引失效，针对第二次查询，它的name索引列是有效的；而age列也有用到，只不过主要用于排序而不是查询；第三个pos是索引失效的。

• 尽量使用覆盖索引(只访问索引的查询(索引列与查询列一致))，减少使用select *。
  
  
  1.通过以上两图对比，可以看出第二次查询使用的查询列name,age,pos与idx_staffs_nameAgePos的索引列对应一致，故type是ref，比第一次使用’ *'来查询得到的range类型更好，key_len也更小，而且extra使用到了using index，表示状况理想。
  
  2.通过上图的两次查询可知，像第二次那样使用的查询列并没有完全与索引列对应，只使用到部分索引列的情况和完全对应一致的情况效果相同。

• is null，is not null无法使用索引。
  

• like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。
  
  1.由上图可知，%只有加在右边才不会索引失效。
  
  2.由上图可知，虽然%加在右边可以避免索引失效，但是在用*查询时会出现空结果。
  
  3.由上图可知，在建立好name和age的复合索引后，第一次查询通过覆盖索引(查询列与索引列一致)使得在左右两边添加%也能避免索引失效。而第二次查询通过主键也达到了同样的效果。
  
  4.对于上图的这些查询，同3一样，查询列只使用到索引列或主键时，就可以避免在左右两边添加%时索引失效。

• 字符串不加单引号导致索引失效。
  
  由上图可知，本来是varchar类型的name字段用int类型的值也可以查出来，说明底层自动发生了由int类型向varchar类型的转换。
  
  这张图是对以上查询的执行计划查看，发现第二次用int类型值查询时发生了索引失效。它的底层自动类型转换验证了之前的‘不在索引列上做任何操作(计算，函数，(手动或自动)类型转换)’这条准则。

• 少用or，用它来连接时可能导致索引失效。
  

• 在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
  

三.索引相关题目分析

• 查询条件where中包括复合索引列的全部列，且值都为常量时。
  
  由上图可以看出，当全部索引列都被赋予常量值而用到时，顺序不会影响索引的有效性。这是由mysql的优化器optimizer起的作用，它会调优索引的顺序，但以防万一，最好还是怎样建的索引就怎样设置条件。

• 查询条件用到范围查询和顺序交换。
  
  由图可知，虽然c4与建立索引时的位置不同，但经过优化器会按照建立索引的位置排列，则c1，c2，c3都是索引有效的，最后的c4虽然是范围查询，但也会用到，只不过是起的排序作用而不是查询。

• 查询条件为不同like的情况(建立a，b，c的复合索引，Y指用到索引)
  
  由上图可知，只有当like后的条件把%加在最前面才会导致其本身查询的索引(图中的b)和其后(图中的c)的索引失效。