在学习SQL优化之前，我们先来学习一下存储引擎，因为不同的存储引擎在增删改查方面的性能有所不同。
存储引擎
存储引擎就是存储数据、建立索引、更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
Oracle、SqlServer等数据库只有一种存储引擎，Mysql提供了插件式的存储引擎架构，所以Mysql存在多种存储引擎，可以自由选择。
Mysql5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。
InnoDB
InnoDB存储引擎是Mysql5.5之后默认的存储引擎，InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB相比其他的存储引擎的特点：
1、支持事务；
2、支持行锁；
3、支持外键：Mysql中唯一支持外键的存储引擎；
4、存储方式：表结构保存在.frm文件中，表数据和索引保存在.ibd文件中。
MyISAM
MyISAM不支持事务，也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以select,insert为主的应用基本上都可以使用这个引擎来创建表。
MyISAM的表有以下特点：
1、不支持事务；
2、不支持外键；
3、支持表锁；
4、存储方式：表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保 存在.MYI文件中。
接下来我们来学习SQL优化
SQL优化
在对sql语句进行优化之前，我们需要使用explain来分析它的执行计划，找到需要优化的地方。
explain分析执行计划
explain select * from test where id = 1;
执行结果如下：
首先我们对这个执行结果中的字段进行解释说明，如下图：
其中
type和extra字段是最重要的，从中可以看出sql语句的执行效率
explain 之 type
type显示的是访问类型，是较为重要的一个指标，可取值为：
1、NULL：Mysql不访问任何表、索引，直接返回结果。例如：explain select now();
2、system：表只有一行记录，这是const类型的特例，一般不会出现。
3、const: 表示通过索引一次就找到了，const用于根据primary key主键 或者unique唯一索引查询。因为只匹配一行数据，所以很快。
4、eq_ref: 类似ref，区别在于使用的是唯一索引，使用主键的关联查询， 查询出的记录只有一条。常见于主键或唯一索引扫描。
5、ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也 是一种索引访问。
6、range: 只检索给定返回的行，使用一个索引来选择行。Where之后出 现between,<,>,in等操作。
7、index：索引，index与ALL的区别为，index类型只是遍历了索引树，通常比 ALL快，ALL是遍历数据文件。
8、ALL：将遍历全表以找到匹配的行。
结果值从最好到最坏依次是：
NULL--> system--> const --> eq_ref --> ref --> range --> index --> ALL
一般来说，我们需要保证查询至少达到range级别，最好达到ref。
explain 之extra
using filesort:说明Mysql会对数据使用一个外部的索引排序，而不是按照 表内的索引顺序进行读取，称为“文件排序”，效率低。
using temporary:使用了临时表保存中间结果，Mysql在对查询结果排序时 使用临时表，常见于order by 和 group by，效率低。
using index:表示相应的select操作使用了覆盖索引，避免访问表的数据行， 效率不错。
索引的使用
索引是数据库优化最常用也是最有效的手段之一，我们在写sql语句时尽量能使用索引，避免索引失效，这样就能有效的提高sql语句的执行效率，从而提高系统性能。
避免索引失效：
1、全值匹配，对复合索引中所有列都指定具体值，该情况下，索引生效， 执行效率高；
2、最左前缀法则：如果对多个列建立的复合索引，要遵守最左前缀法则， 指的是查询条件中必须带索引的最左列，并且不跳过索引中的列；
3、范围查询右边的列，不能使用索引：指在复合索引中，一个索引的列 使用的是between、>、<等范围查询条件，则该条件后的列索引失效；
4、如果在索引列上进行运算操作，索引将失效；
5、字符串不加单引号，造成索引失效；
6、尽量使用覆盖索引（查询结果只包含索引的列），避免select *；
7、用or分割开的条件，如果or之前的条件列有索引，之后的列没有索引，那么涉及的索引都不会被用到；
8、以%开头的like模糊查询，索引失效，如果是尾部模糊匹配，索引不会失效，如果是开头模糊匹配，索引失效，可以使用覆盖索引解决该问题；
9、如果Mysql评估使用索引比全表扫描慢，则不使用索引；
10、is null和is not null有时索引失效，是根据数据库中的数据量来判断 的，如果索引列NULL值比较多，占据了绝大多数，那么is not null就走索引，反之，如果NULL值比较少，那么is null就走索引；
11、in走索引，not in索引失效；
12、单列索引和复合索引，尽量使用复合索引，而少使用单列索引。单列索引在查询时，数据库会选择一个最优的索引（辨识度最高索引来使用，并不会使用全部索引。
网友评论