SQL硬核调优

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。

MySQL的优化方式有很多，大致我们可以从以下几点来优化SQL:

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

  一， Explain分析执行计划

  1. 查看SQL执行频率

  MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。

  --下面的命令显示了当前 session 中所有统计参数的值
  show session status like 'Com_______'; -- 查看当前会话统计结果
  show global status like 'Com_______'; -- 查看自数据库上次启动至今统计结果
  

  2.explain分析执行计划

  EXPLAIN作为MySQL的性能分析神器，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

  3. Explain分析执行计划-Explain 之 id

  id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种:

  1. id 相同表示加载表的顺序是从上到下。

  2. id 不同id值越大，优先级越高，越先被执行。

  3. id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

  4.Explain分析执行计划-Explain 之 select_type

  表示 SELECT 的类型，常见的取值，如下表所示：

  type 显示的是访问类型，是较为重要的一个指标，可取值为：

  结果值从最好到最坏以此是：system > const > eq_ref > ref > range > index > ALL
  

  5.Explain分析执行计划-其他指标字段

  1. Explain 之 table

     显示这一步所访问数据库中表名称有时不是真实的表名

     字，可能是简称，

  2. xplain 之 rows

     扫描行的数量。

  3. Explain 之 key

     possible_keys : 显示可能应用在这张表的索引， 一个或多个。

     key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

     key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，

     长度越短越好 。

  6.show profile分析SQL

  Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

  通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

  select @@have_profiling;
  set profiling=1; -- 开启profiling 开关；
  

  通过profile，我们能够更清楚地了解SQL执行的过程。首先，我们可以执行一系列的操作

  show databases;
  use test_optimize;
  show tables;
  select * from user where id < 2;
  select count(*) from user;
  

  执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

  show profiles;
  

  通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

  show profile for query 8;
  

  二、如何使用索引优化

  索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

  数据准备

  create table `tb_seller` (
  `sellerid` varchar (100),
  `name` varchar (100),
  `nickname` varchar (50),
  `password` varchar (60),
  `status` varchar (1),
  `address` varchar (100),
  `createtime` datetime,
  primary key(`sellerid`)
  );
  

  -- 创建组合索引
  create index idx_seller_name_sta_addr on tb_seller(name,status,address);
  

  1.避免索引失效应用-全值匹配

  该情况下，索引生效，执行效率高。

  explain select * from tb_seller where name='小米科技' and status='1' and
  address='北京市';
  

  2.避免索引失效应用-最左前缀法则

  该情况下，索引生效，执行效率高。

  -- 最左前缀法则
  -- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
  explain select * from tb_seller where name='小米科技'; -- 403
  explain select * from tb_seller where name='小米科技' and status='1'; -- 410
  explain select * from tb_seller where status='1' and name='小米科技'; -- 410
  -- 违反最左前缀法则 ， 索引失效：
  explain select * from tb_seller where status='1'; -- null
  -- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
  explain select * from tb_seller where name='小米科技' and address='北京市'; -- 403
  

  3.避免索引失效应用-其他匹配原则

  该情况下，索引生效，执行效率高

  -- 范围查询右边的列，不能使用索引 。
  explain select * from tb_seller where name='小米科技' and status >'1' and address='
  北京市';
  -- 不要在索引列上进行运算操作， 索引将失效。
  explain select * from tb_seller where substring(name,3,2)='科技'
  

  -- 4、尽量使用覆盖索引，避免select *
  -- 需要从原表及磁盘上读取数据
  explain select * from tb_seller where name='小米科技' and address='北京市'; -- 效率低
  -- 从索引树中就可以查询到所有数据
  explain select name from tb_seller where name='小米科技' and address='北京市'; -- 效率高
  explain select name,status,address from tb_seller where name='小米科技' and address='北京市'; -- 效率高
  -- 如果查询列，超出索引列，也会降低性能。
  explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市'; -- 效率低
  

  -- 用or分割开的条件， 那么涉及的索引都不会被用到。
  explain select * from tb_seller where name='程序员' or createtime = '2088-01-01 12:00:00';
  explain select * from tb_seller where name='程序员' or address = '西安市';
  explain select * from tb_seller where name='程序员' or status = '1';
  -- 以%开头的Like模糊查询，索引失效。
  explain select * from tb_seller where name like '科技%'; -- 用索引
  explain select * from tb_seller where name like '%科技'; -- 不用索引
  explain select * from tb_seller where name like '%科技%';-- 不用索引
  -- 弥补不足,不用*，使用索引列
  explain select name from tb_seller where name like '%科技%';
  

  -- 1、如果MySQL评估使用索引比全表更慢，则不使用索引。
  -- 这种情况是由数据本身的特点来决定的
  create index index_address on tb_seller(address);
  explain select * from tb_seller where address = '北京市'; -- 没有使用索引
  -- 2、is NULL ， is NOT NULL 有时有效，有时索引失效。
  create index index_address on tb_seller(nickname);
  explain select * from tb_seller where nickname is NULL; -- 索引有效
  explain select * from tb_seller where nickname is not NULL; -- 无效
  

  三、实用的SQL优化

  1.优化insert语句

  当进行数据的insert操作的时候，可以考虑采用以下几种优化方案

  -- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减
  客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
  -- 原始方式为：
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  -- 优化后的方案为 ：
  insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
  

  2.优化子查询

  使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起

  来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

  explain select * from user where uid in (select uid from user_role );
  

  explain select * from user u , user_role ur where u.uid = ur.uid;
  

  system>const>eq_ref>ref>range>index>ALL
  连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
  

  3.in是把双刃剑，到底怎么用？

  -  低效：SELECT * FROM t WHERE age = 10 OR age = 20 OR age = 30;
  -  高效：SELECT * FROM t WHERE age IN (10,20,30);
  

  一，先说结论：IN 比 OR 快

  假设检索的列为N，IN 的时间复杂度为 O(LogN)，而 OR 的时间复杂度为 O(N)

  在数据量少于100条时的情况下差别不大

  当要检索的列为主键索引时，IN 的执行速度和 OR 差别不大

  当要检索的列为普通索引时，IN 的执行速度和 OR 差别不大

  当要检索的列没有索引时，IN的执行速度要远大于 OR

  二、为什么 IN 比 OR 快？

  对于下面这条 SQL 语句：

  SELECT * FROM tbName WHERE KEY IN (a1, a2, … , an);

  SELECT * FROM tbName WHERE KEY = a1 OR KEY = a2 OR … OR KEY = an;

  用不上索引的情况下，遍历全表的 key，去匹配 a1, a2, …, an

  OR，就是从 a1 匹配，匹配失败，去匹配 a2，直到匹配成功或者一个都匹配不上，时间复杂度是 O(N)

  IN，先将 a1, a2, …, an 变成二叉搜索树，用过二叉树查找，时间复杂度为O(LogN)

  因此，IN 的效率要高于 OR，参数集合范围越大，IN 的性能不会太大下降，而 OR 会下降非常厉害。

  in和or的效率，

  如果该字段有索引或者主键，则性能没啥差别，

  如果该字段没有索引，则in的性能要远远优于or

  in是把双刃剑，到底怎么用？

  低效：SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='张三');
   高效：SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
  

  连接（JOIN）… 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

  谓词下推和列裁剪。

  优化前：
  select *
  from table1 a 
  join table2 b on a.id=b.id 
  where a.age>20 and b.cid=1
  

  谓词下推

  优化后：
  select *
  from
  (select * from table1 where age>20) a 
  join (select * from table2 where cid=1) b 
  on a.id=b.id
  

  列裁剪和谓词下推。

  优化前：
  select a.name, a.age, b.cid 
  from ( select * from table1 where a.age>20） a 
  join ( select * from table2 where b.cid=1) b 
  on a.id=b.id
  

  列裁剪

  优化后：
  select a.name, a.age, b.cid 
  from
  (select id,name,age from table1 where a.age>20） a 
  join
  ( select id,cid from table2 where b.cid=1) b
  on a.id=b.id
  

  推荐：

  1. MySQL之常用 SQL 查询语句优化方法大全
  2. 常用SQL技巧详解