面试必问:谈一下sql优化

平时工作中数据库操作都是以mysql为主，所以主要谈一下对mysql的优化。mysql优化主要包含三点。1.操作系统级别的；2.应用程序相关的；3.mysql自身相关的。

一、针对操作系统我们能优化的不会太多，而且收益也不会很明显。一般都是针对cpu和io性能进行调整；

二·、应用程序这块，首先在设计表时一定要考虑周到，这是一切sql优化的基础，另外也许考虑到业务的数据量，是否需要进行分库分表，是否需要进行冷数据存储，又或者是否需要加入缓存。

 三、最后一点sql优化，这也是我们需要考虑最多的，也是收益比较明显的，注意的点也比较多：

     1.调整buffer pool的大小，mysql都会先将数据加载到内存中进行操作， 一般调整至系统内存的60%左右
     2.表id用自增或者有序id，保证对磁盘的顺序写，避免页的分裂和合并，提高插入性能
      3.尽量对多个查询条件的sql建立联合索引，保证最左匹配原则，对范围查找的字段，将其放到联合索引的尾部，降低索引字段失效的风险
      4.尽可能的使用到覆盖索引，因此要避免写select * 查询，尽量避免回表操作(查询聚簇索引)
      5.不要再索引字段上做计算操作，索引会失效， 尽可能在order by或者group by相关得字段加上索引 避免走文件排序和临时表
      6.如果需要对较长的字段加索引，尽量加前缀索引，提高效率
      7.如果必须走全表扫描，也尽量往索引树上全表扫描using index，因为mysql是按照页为单位加载到buffer pool中，要加载的数据越少，IO次数就越少，内存利用越高    
         具体走那个索引还是要和mysql自己的成本计算有关
      8.若是联合查询 要让小表(驱动表)驱动大表(被驱动表)，小表只会查一次，大表会根据小表的数据的数量查询多次大表
         连接查询主要是两种策略 ：
        一种是NLJ(嵌套循环连接算法)，先查询驱动表再根据驱动表的数据一行行检索被驱动表，这种是在被驱动表的关联字段加了索引的情况
        另一种是BNL(基于块的嵌套循环连接算法)，会先将驱动表的数据加入join_buffer中，再扫描被驱动表，一次提出一条数据与join_buffer中的数据比较
        这是因为由于被驱动表的关联字段未加索引，每一次被驱动表的全表扫描都会影响性能更别说多次了
      9.在被驱动表的关联字段加上索引
      10.in和exists都要让小表驱动大表
          in: select * from a where id in（select id from b）; 当b的数据集小于a时  in由于exists
          等价于:
           for（select id from b）{
               select * from a where a.id = b.id
         }
       exists: select * from a where exists (select 1 from b where a.id =b.id)  ;  当a 表的数据集小于b时   exists由于in
         等价于
          for(select * from a){
                select * from b   where a.id = b.id
          }
       11.看业务情况可降低mysql的隔离级别,mysql默认是readRepeatable 可重复读，这个隔离级别下为避免写数据时的幻读，会增加间隙锁，可将隔离界别调至read commited  读已提交，但是在mvcc下的select会出现不可重复读的情况，若你的项目是用得spring+mybatis，mybatis默认得开启一级缓存，一级缓存是基于sqlsession级别得，spring整合mybatis时，默认是在同一个事务下sqlsession才会复用，所以即使即将数据库得隔离级别设置为read_commited，也不会出现不可重复读得现象，除非在本事务将一级缓存摧毁。
       12.开启慢查询日志，对慢sql做分析优化

       13.尽量对update或者delete语句后面的where条件加索引，因为update或者delete会加锁，这个锁主要是会对索引加锁，若wehere条件的字段没有加索 就会默认加表锁；

   14  尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。