数据库优化（二）SQL优化原则

SQL优化作为数据库优化的一个重要环节，因为SQL优化不仅仅会提升查询数据的效率，而且会提升修改数据的效率，因为修改数据的时候就会先把数据查询数据出来再进行操作。

在SQL做优化，其主要还是通过索引来进行优化，主要思路就是先保证我们的查询都使用到了索引，然后在使用到索引的情况下再根据执行计划对type、key_len、row、 extra进行分析，因为SQL的优化会根据业务情况分不同的场景，优化方式也不同，所以我们这里主要介绍SQL中的一些优化原则，和这些原则背后的逻辑是什么。

最左匹配原则也是我们SQL中需要重点关注的，因为它会直接影响到你的like语句，组合索引是否命中。

最左匹配原则是指，索引在进行模糊匹配时，必须最左边开始匹配，讲起来有点绕口，我们看两个案例你应该就能理解。

tb_user表phone字段为索引

like '%186' ，这种情况是无法用到索引的，因为左边的值不确定。

like '186%' ，遵循最左匹配原则，可以用到索引。

tb_user表建立一个name,phone,age为顺序的组合索引

不符合最左匹配规则：根据最左匹配原则，我们必须先匹配name 才能匹配到phone,再继续匹配age，所以只有phone和age的查询条件都不会命中索引的。

符合最左匹配规则：当使用了name条件则可以使用到组合索引

在我们使用命中组合索引的时候如何判断组合索引是否全部命中，还是只命中了一部分? 这里我们执行计划里的key_len来判断。

我们上面表中的name定义的字段长度为 10，字符编码为UTF-8(UTF-8编码每个长度占用3个字节)，类型为varchar(需要2个字节存值的实际长度) ，然后允许为空(需要额外1个字节存储null)；那么计算得到name 的索引长度=10*3 +2+1=33，我们再看执行计划的key_len

再看一下命中name 和phone 之后key_len 就等于 name和phone字段索引的总长度了。

凡是经过了隐式转换的列是无法用到索引的，这个问题也是我们开发人员非常容易犯的错误。 隐式转换发生在匹配的条件和列的类型不一致，导致要对列的值进行隐式转换，才能与条件进行匹配。如下面几个例子都会导致索引失效。

tb_user 表的phone列为索引，phone的字段类型为varcchar

正确的方式：

正确的方式：

把条件转换为同列一致的类型即可避免对列的类型进行转换。

因为索引的类型和匹配的条件类型不匹配，所以无法使用条件来与索引进行匹配，必须先把数据查询出来之后，再进行类型转换，才能与条件进行条件匹配。

简单来说就是我们的列数据只需要通过索引就可以获得数据，不需要从数据表中去遍历数据，这种索引就已经覆盖了需要查询的列数据情况称为覆盖索引。

根据上一篇的“如何定位和慢SQL和对SQL进行性能分析”中有说到，当我们使用执行计划分析SQL语句时，当Extra字段说明里有Using index 的字样时，就表示使用到了覆盖索引，我们看一个案例。

tb_user表结构

phone为索引字段

当我们列只查询了phone时就会使用到覆盖索引

因为覆盖索引的重要性，所以有必要给大家复习下相关知识。在mysql里分为 聚集索引和辅助索引。聚集索引既是索引又是表数据，而辅助索引里保存的则是聚集索引的键，从下面的图我们可以理解使用mysql辅助索引查询数据时都是先从辅助索引获取到聚集索引的索引键，然后用索引键从聚集索引中找到对应的表数据。

如上图所示:

所有通过辅助索引查询数据其实都是先从辅助索引查询到聚集索引的索引key，然后用聚集索引的key从聚集索引里面查找数据，通常也称这个过程为回表，按我们上面理解的覆盖索引，当我们查询的字段已经包含在索引里面时，那么我们就不需要从聚集索引里面去查询数据了，因为你所查询的列本身就是索引的key，那么直接返回当前索引的Key就行了，这个过程就减少了一次从聚集索引查询表数据的过程，当我们查询的数据越多那么这个效率显而易见会得到巨大的提升，所以这也是覆盖索引的好处。

1、如果join 的字段使用了索引，就会使用Index Nested-Loop Join 算法，这种算法是最高效的。

2、sort、group by 里都涉及到要对数据排序，如果使用了索引，因为Mysql的索引是B+树天然具备顺序的特性，所以可以避免把数据放到sort buffer排序的过程,提升SQL效率。

控制事务的粒度核心思想就是减少对数据加锁的时间。 这样可以大大提升对数据修改的并发性能，这方面我们可以从两个方向来入手从而减少锁的影响。

innodb引擎锁的最小粒度为行锁，所以我们在修改数据的时候尽量保证只锁定相关的行，而我们知道只有条件命中了索引才会使用行锁，否则就是使用表锁，那么我们在修改数据的时候就要尽量保证条件是使用的索引字段。

比如我们对t_user表的信息进行变更，如果id是索引那么update语句我们尽量写成第二种方式。

 update t_user set age=18 where user_name="186885868953"  //锁整个表。
 ?
 update t_user set age=18 where id=1;                    //锁id=1的一行。

当我们在进行事务操作时，加锁是从语句执行开始，但是语句执行完后并不会马上释放对应的锁，而是等整个事务提交之后才会释放所有的锁。

比如下面的事务分别做了三个操作， 修改 table_A（执行2秒）、查询table_B（执行1秒）、往table_C 插入数据（执行2秒）；

那么从事务开启，从开始执行update 直到 事务最后commit,（总共6秒） 表table_A 里id=1的数据都是被锁的状态。

 begin
 ?
 update table_A  set name="张三"  where id=1;          //执行3秒
 ?
 select * from table_B where id=2;                   //执行1秒
 ?
 insert table_C   values(1,2);                       //执行2秒
 ?
 commit;

那么我们其实只要调换一下顺序，把update 语句放到最后就能减少数据锁定的时间，甚至我们都可以把查询table_B的语句放到事务之外去执行。

 select * from table_B where id=2;                   //执行1秒
 ?
 begin
 ?
 insert table_C   values(1,2);                       //执行2秒
 ?
 update table_A  set name="张三"  where id=1;        //执行3秒
 ?
 commit;

禁止使用select * 已经是老生常谈的问题了，每个开发人员谈到SQL优化都会说上个一二，但实际开发中往往又很容易忽略这个问题，一方面的确select * 一劳永逸，一个个字段填写还真有点麻烦，另外一个方面还是由于我们对select * 所带来的问题所知甚少，如果遇到SQL性能问题，那么第一个优化的就是select * 。

使用select * from 查询大量非必要的数据会导致如下情况：

1、数据库IO次数增加，数据库每次IO只会读取固定大小的数据，查询的数据越大，那么IO的次数也就越多。

2、消耗内存，数据读取数据是在内存里面做匹配筛选。

3、数据越大网络的传输速度就变慢，查询的数据要经过网络传输给应用程序。

4、无法合理的使用到覆盖索引。

5、影响sort、group by、join语句的性能，在sort、group by、join会用到sort buffer 、 临时表、join buffer、这些都是一块有限的内存空间，查询的字段越多内存装不下了，就只能转而在磁盘中去做对应操作，而磁盘操作的性相比内存性能相差上百倍。

6、字段越多，在应用层面（JVM）产生的对象体积就越大，对象越大就导致垃圾收集得越频繁、垃圾收集越频繁就会降低整个应用的性能。

其实很多情况下Mysql都已经对此进行了优化，虽然我们实际过程中基本上不需要再操心了，但这个思路原则还是值得我们学习和遵守的。

用小结果驱动大结果在Join中的体现:

Join的过程就是先查询出两个表数据，然后通过一个双层循环来遍历外层表 与内层表匹配的过程，如果关联的表有索引，那么Join的过程就类似于下图，左边的就是驱动表、右边的为被驱动表。

假如左边的表数据为5000条，右边的表数据为10000条，以左表为驱动表的话，那么驱动的扫描次数为5000次，数据匹配次数为5000 x 索引的高度。那么 如果换成右表为驱动表，驱动表被扫描的次数就会变成10000次，而数据匹配次数会变成10000 x 索引高度。

通过上面的逻辑，我们可以在进行SQL优化的时候，在保证业务的情况下尽量使用数据量小的那张表作为驱动表可以减少很多CPU计算次数，提升SQL的性能。