本文转载自微信公众号《不送花的程序员》，转载请联系公众号。作为应届毕业生，我们都认为使用MySQL很简单，我们遵循的格式是[selectfromwheregroupbyorderby]；我们从来没有关注过耗时的SQL，更不用说查询性能了。但是，当用户数增加时，表数据持续快速增长，导致我们之前写的SQL的查询时间越来越长。到头来，DBA和领导大发牢骚。那么，这个时候我们是否应该学习如何优化我们糟糕的SQL呢？接下来，我将从多方面深入讲解如何优化SQL。1.索引优化索引的数据结构是B+Tree，B+Tree的查询性能比较高，所以建立索引可以提高SQL的查询性能。1.创建普通索引为where关键字后面经常出现的表字段创建对应的索引。2、创建复合索引如果where关键字后面经常出现几个字段，可以创建相应的复合索引。需要注意的是，可以优化的一件事是：把出现次数最多的字段放在最前面。比如现在我们有两个字段a和b经常同时出现在where关键字后面：select*fromtwherea=1andb=2;\*Q1*\也有很多SQL单独使用字段a作为查询条件:select*fromtwherea=2;\*Q2*\此时，我们可以创建一个复合索引index(a,b)。因为不仅Q1可以使用复合索引，Q2也可以使用复合索引。3.最左前缀匹配原则如果我们使用复合索引，我们应该尽量遵循最左前缀匹配原则。MySQL会一直向右匹配，直到遇到范围查询（>、<、between、like）停止匹配。如果此时我们有一条SQL：select*fromtwherea=1andb=2andc>3andd=4;那么我们应该构建的复合索引是：index(a,b,d,c)而不是index(a,b,c,d)。因为字段c是一个范围查询，所以当MySQL遇到范围查询时，就停止索引匹配。大家也注意到了，SQL中a、b、d的位置是可以任意调整的，优化器会找到对应的复合索引。还需要注意的是，最左前缀匹配原则不仅仅是复合索引的最左N个字段；也可以是单列（字符串类型）索引最左边的M个字符。比如我们常说的like关键字，尽量不要使用全模糊查询，因为它不会用到索引；所以建议使用正确的模糊查询：select*fromtwherenamelike'Li%'（查询所有李姓学生的姓名信息）。4、索引下推很多时候，我们也可以通过复合索引的索引下推来优化SQL。比如此时我们有一个复合索引：index(name,age)，然后有一个SQL如下：select*fromuserwherenamelike'Zhang%'andage=10andsex='m';根据复合索引的最左前缀匹配原则，MySQL匹配到复合索引的名称index(name,age)时，会停止匹配；那么接下来的流程就是根据主键回表判断是否同时满足年龄和性别的条件，满足则返回给客户端。但由于索引下推的优化，匹配到name后，不会立即返回表；而是先判断复合索引index(name,age)中的年龄是否满足条件；只有满足条件才会返回表，然后判断性别是否满足，否则过滤掉。然后通过MySQL5.6引入的索引下推优化，可以减少回表次数。5.覆盖索引很多时候，我们也可以使用覆盖索引来优化SQL。情况一：SQL只查询主键作为返回值。主键索引（聚集索引）的叶子节点是整行数据，而普通索引（二级索引）的叶子节点是主键的值。所以当我们的SQL只查询主键值的时候，我们可以直接获取对应的叶子节点的内容，而无需返回表。情况2：SQL查询字段在索引中。复合索引：如果此时我们有一个复合索引index(name,age)，则有如下SQL：selectname,agefromtwherenamelike'Zhang%';因为字段名是对的模糊查询，可以使用复合索引，然后匹配name，不需要回表，因为sql只查询字段名和age，所以直接返回就可以了指数值。6.普通索引尽量使用普通索引，而不是唯一索引。首先，普通索引和唯一索引的查询性能不会相差太大；当然前提是要查询的记录都在同一个数据页中，否则普通索引的性能会慢很多。但是普通索引的更新操作性能要优于唯一索引；其实很简单，因为普通索引可以使用changebuffer来执行update操作；而唯一索引要判断更新后的值是否唯一，所以每次都需要将更新磁盘中的数据读入缓冲池。7、前缀索引我们要学会熟练使用前缀索引，避免索引值过大。比如有一个字段是addrvarchar(255)，但是如果构建整个索引[index(addr)]，会浪费磁盘空间，所以会选择构建前缀索引[index(addr(64)))]。构建前缀索引，必须注意字段的分化程度。比如身份证号等字段的区分度就很低。只要出生地一样，前面的很多字都是一样的；在这种情况下，最坏的情况下可能会扫描整个表。前缀索引无法避免回表，即无法使用覆盖索引的优化点，因为索引值只是字段的前n个字符，需要回表判断是否查询值与字段值一致。如何解决？1).逆向存储：和身份证一样，后面的数字非常好区分；我们可以这样查询：selectfield_listfromtwhereid_card=reverse('input_id_card_string');2).字段被索引。8、清理索引列索引列不能参与计算，所以要保持索引列“干净”。假设我们为表学生的生日字段创建一个普通索引。以下SQL语句不能使用索引来提高执行效率：select*fromstudentwhereDATE_FORMAT(birthday,'%Y-%m-%d')='2020-02-02';我们应该将其更改为以下内容：select*fromstudentwherebirthday=STR_TO_DATE('2020-02-02','%Y-%m-%d');9、扩展索引我们应该尽可能地扩展索引，而不是增加新的索引。一张表的索引最好不要超过5个；一张表的索引越多，更新操作的性能消耗就越大。2、SQL优化1、OrderBy优化orderby后面的字段尽量做索引，避免使用sort_buffer进行排序。如果有SQL查询根据生日查询所有学生信息：select*fromstudentorderbybirthdaydesc;那么为了提高SQL的查询性能，我们可以为birthday字段创建一个索引：CREATEINDEXindex_birthdayONstudent(birthday);不要带不必要的字段，因为如果单行长度过长，查询数据过多，MySQL会使用rowid排序，而不是全字段排序，这会导致更多的操作返回表。如果我们只是查询学生的姓名、年龄和生日，就不要写select*；但只查询必填字段：selectname,age,birthday。2.Join优化在使用join时，应该以小表作为驱动表。小表：总数据量最小的表使用join语句时，最好保证能使用被驱动表的索引，否则只能使用BNL（BlockNested-LoopJoin）算法，最好不要使用它。启用BKA（BatchedKeyAccess）算法，这样NLJ算法也可以使用join_buffer，驱动表可以批量查询满足条件的值，然后使用MMR的顺序读盘特性（Multi-RangeRead)提高回表效率。如果一定要用join，而被驱动的表没有索引可以用，那么我们可以用临时表（createtemporarytablexx(...)engine=innodb;）将BNL算法转换为BKA算法，从而提高查询性能。join_buffer是一个无序数组，所以每次判断都需要遍历整个join_buffer。我们可以在业务端实现hashjoin来提高SQL的执行速度。3.GroupBy优化如果groupby语句的结果没有排序要求，语句后要加上orderbynull。尽量让groupbyprocess使用上表的索引，不仅不需要临时表，也不需要额外的排序。如果groupby统计的数据量不大，尽量只使用临时内存表；您还可以通过适当增加tmp_table_size参数来避免使用临时磁盘表。如果数据量太大，使用SQL_BIG_RESULT提示告诉优化器直接使用排序算法根据结果进行分组。4.OR优化Innodb引擎中，or关键字不能使用复合索引。假设有一条关于订单表的SQL：selectid,product_namefromorderswheremobile='12345678900'oruser_id=6;通常，为了提高上述SQL的查询效率，我们会想到为mobile和user_id字段构建一个复合索引index(mobile,user_id)；但是我们可以通过explain发现执行计划并没有提示使用复合索引，所以or关键字无法命中mobile+user_id的复合索引。那么我们可以分别为这两个字段创建普通索引，然后使用union关键字，如下：(selectid,product_namefromorderswheremobile='12345678900')union(selectid,product_namefromorderswhereuser_id=6);这时候mobile和user_id字段都有索引，Query效率最高。5.IN优化in关键字适用于主表小，子表小，exist关键字适用于主表小，子表小。由于查询优化器的不断升级，两者在很多场景下的性能相差无几，可以尝试换成join查询。假设我们现在有一条查询VIP用户所有订单数据的SQL：selectidfromorderswhereuser_idin(selectidfromuserwherelevel='VIP');我们可以发现不会对索引进行优化，所以我们可以使用连接查询，如下所示：idfromordersojoinuseruono.user_id=u.idandu.level='VIP';这时候被驱动的表应该是user，所以可以使用user表的主键索引，也就是可以使用BKA算法来提高join查询的性能。6.likeoptimizationlikeforfuzzyquery，但是如果是fullfuzzyquery，会命中不到对应字段的索引。假设有一条关于student表的SQL语句：SELECTname,age,birthdayFROMstudentWHEREnamelike'%Zhang%';使用explain发现执行计划提示查询未命中索引。因为原来的需求是查询所有张姓同学的信息，所以不用全模糊查询，直接用右模糊查询即可。改为如下写法：SELECTname,age,birthdayFROMstudentWHEREnamelike'Zhang%';但是产品经理一定要前后模糊匹配？可以试试全文索引FULLTEXT，但是MySQL的全文索引不支持中文查询。所以Elasticsearch是终极武器！三、数据表设计优化1、数据类型：应选择较简单或占用空间较小的类型。整数选择：可以根据长度选择tinyint、smallint、medium_int，而不是直接使用int。字符串选择：如果可以确定字符串的长度，尽量使用char类型，不要使用变长的varchar类型。浮点型选择：如果精度要求比较高，就用decimal代替double；也可以考虑用BIGINT来保存，小数位乘以整百就可以保存了。日期选择：尽量使用时间戳而不是日期时间。2、避免空值：NULL值仍然占用空间，使索引更新更加复杂，更新NULL时容易出现索引分裂。可以用一个有意义的值来代替NULL值，比如“none”字符串等。3、超长字符串：一般超长字符串很难用varchar存储，所以我们一般使用text类型。但是，尽量不要将文本类型的字段放在主表中，而是将其提取到子表中，并与业务主键关联起来。
