常用SQL优化

1. count(*)：

   对InnoDB而言，它需要把数据从磁盘中读取出来然后累计计数；而MyISAM引擎把一个表的总行数存在了磁盘上，所以count()会直接返回这个数，如果有where条件则和InnoDB一样。那么如何优化count()，一个思路是使用缓存，但是要注意双写一致的问题，还可以专门设置一张表用以存储count()。

   对于count(id)来说，InnoDB会遍历整张表，把每一行的id值都取出来，返回给server层。server拿到id后，判断不为空的就按行累加。对于count(1)来说，InnoDB引擎遍历整张表，但不取值，server对于返回的每一行，放一个数字1进去，执行的要比count(id)快，因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。对于count(字段)来说，如果这个字段定义为not null的话，一行一行的从记录里面读出这个字段，判断不能为null按行累加；如果这个字段定义允许为null，那么执行的时候还要把值取出来再判断一下，不是null才累加。而对于count(*)来说，并不会把全部字段取出来，而是专门做了优化，不取值，按行累加。所以排序效率：

   count(*) = count(1) > count(id) > count(字段)

2. order by：

   MySQL会给每个线程分配一块内存用于做排序处理，成为sort_buffer，一个包含排序的SQL执行的过程为：申请排序内存，然后一条条查询出整行数据，然后将需要的数据放入到排序内存中，然后对排序内存中的数据做一个快速排序，然后返回到客户端。当数据量过大，排序内存放不下的时候，就是利用磁盘临时文件来辅助排序，当排序内存放不下数据的时候，MySQL会使用rowid排序来优化。rowid排序相对于全字段排序，不会把所有字段都放入sort_buffer，所以在sort_buffer排序后还要回表查询。在少数情况下，可以使用联合索引+索引覆盖的方式来优化 order by。

3. 驱动表：

   当两个表发生关联的时候，就会有驱动表于被驱动表之分，驱动表也叫外表(R表)，被驱动表也叫内表(S表)。一般将小表当做驱动表(制定了内联条件，满足查询条件记录少的表为驱动表，未指定连接条件时，行数少的表未驱动表；MySQL内部用户器也是这么做的)

4. join：

   假设有如下的SQL

   select * from table1 left join tablet2 on table1.xxx = table2.xxx
   

   这条语句执行的过程是先遍历table1，然后根据从表table1中取出的每行数据去表table2中查找满足条件的记录，并且能够用上被驱动表的索引，这种查询方式叫做NLJ。当xxx不是索引的时候，再使用NLJ的时候就会对table2做多次全表扫描(每从table1去一条数据的时候就会全表扫描一次table2)，扫描数暴涨，这时候MySQL会采用另外一种策略，MySQL会把table1中的数据读到join_buffer的内存空间中去，然后依次取出table2的每一行数据，跟join_buffer中的数据作对比，满足join条件的作为结果集的一部分返回。

5. 在使用join的时候要满足以下几点：

   1. 小表驱动大表
   2. 被驱动表走索引的情况下(走NLJ查询方式)的时候才考虑用join

6. SQL的优化

   1. 在MySQL中，如果对字段做了函数运算，就用不上索引了

      select * from tradelog where month(data)=1;
      

      优化器对这样的 sql 会放弃走搜索树，因为它无法知道 data 的区间。

   2. 隐式的类型转换会导致索引失效。

      select * from table where xxx = 110717;
      

      其中 xxx 为 varchar 型，在 mysql 中，字符串和数字做比较的话，将字符串转换成数字再进行比较，这里相当于使用了 CAST(xxx ASsigned) 导致无法走索引。

   3. 索引列参与了计算不会走索引

   4. like %xxx 不会走索引，like xxx% 会走索引

   5. 在 where 子句中使用 or ，在 innodb 中不会走索引，而 MyISAM 会。

7. 执行计划：在查询 sql 之前加上 explain 可查看该条 sql 的执行计划。如

   EXPLAIN SELECT * FROM table
   

8. 执行计划各字段的含义

   1. id：查询中执行 select 子句或操作表的顺序。
   2. select_type：查询中每个 select 子句的类型（简单 到复杂）包括：
      1. SIMPLE：查询中不包含子查询或者UNION
      2. PRIMARY：查询中包含复杂的子部分
      3. SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY
      4. DERIVED：衍生，在FROM列表中包含的子查询被标记为DERIVED
      5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
      6. UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT
   3. type：表示 MySQL 在表中找到所需行的方式，又称“访问类型”，包括
   4. ALL：Full Table Scan， MySQL 将遍历全表以找到匹配的行
   5. index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树
   6. range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between < > 等查询
   7. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
   8. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
   9. onst 和 system：当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量，system 是 const 类型的特例，当查询的表只有一行的情况下， 使用 system
   10. NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引
   11. possible_keys：指出 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
   12. key：显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL。
   13. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
   14. ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
   15. rows：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
   16. Extra：其他重要信息 包括：
       1. Using index：该值表示相应的 select 操作中使用了覆盖索引；
       2. Using where：MySQL 将用 where 子句来过滤结果集；
       3. Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询；
       4. Using filesort：MySQL 中无法利用索引完成的排序操作称为“文件排序”。