记一次SQL的优化记录

Part1：生成测试数据

• 这里提供一个SQL脚本，包含了建表和插入数据的内容，建立测试表来演示SQL的优化

  -- 创建表结构
  DROP TABLE IF EXISTS index_test;
  CREATE TABLE index_test (
  	id BIGINT ( 20 ) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  	USER VARCHAR ( 16 ) DEFAULT NULL,
  	psd VARCHAR ( 64 ) DEFAULT NULL 
  
  ) ENGINE = MyISAM DEFAULT CHARSET = utf8;
  -- 创建存储过程
  DELIMITER $$
  DROP PROCEDURE IF EXISTS `insert_data`$$
  CREATE PROCEDURE `insert_data`(IN tableName varchar(500),IN num INT)
  BEGIN
    DECLARE n INT DEFAULT 1;/*定义一个变量，存储当前执行的次数*/
      WHILE n <= num DO
          SET @sqlStr = CONCAT("INSERT INTO ",tableName,"(USER,psd) VALUES(CONCAT(",n,",'用户'),password(",n,"))");
          PREPARE stmt FROM @sqlStr;
          EXECUTE stmt;
          set n=n+1;
    end while;
  END $$
  -- 执行插入数据
  call insert_data('index_test',2000000);
             

• 测试环境准备完毕，已经有了一个含有两百万条数据的表了。现在开始通过一个普通的SQL语句，来分析sql性能问题并优化

  • 

• 生成完数据后，记得把id主键去掉，因为主键默认存在索引，我们后面使用id做测试

  • 

Part2：识别性能问题

2.1 寻找运行缓慢的SQL语句

• 这里是根据自己编写的SQL来分析一下性能问题，所以就默认当前SQL为待优化SQL

  SELECT
  	t.id,
  	t.USER,
  	t.psd 
  FROM
  	index_test t 
  WHERE
  	t.id = 457365;
             

• 先运行一下，看一下在当前库中查询耗时有多少

  • 

  • 从上述结果中看出，在系统中执行SQL花费了0.256秒左右的时间

2.2 确认低效查询

• 发现一个可能是低效的查询问题后，应该首先要确认这个查询是否每次重复执行都很慢，因为要排除这个所谓的低效查询不是因为被锁定或者系统瓶颈等其他因素导致产生的个别现象。

2.2.1 运行SQL语句，记录执行时间

• 使用MySQL命令行模式或者一些客户端工具比如Navicat Premium等重复运行SQL来进行验证
  • 我在本地运行了多次，时间差不多都是0.25秒（250毫秒）。并且对于执行时间大于10毫秒的查询来说，MySQL命令行客户端返回的查询结果已经足够了。
• PS：重复运行的方法只能用于select语句，因为它不会修改现有的数据，如果低效查询语句时UPDATE或者DELETE语句，那么可以简单地将它们重写为select语句完成验证。比如，如果SQL查询时delete from inventory where item_id = 1，可以将该删除语句修改为select * from inventory where item_id = 1来模拟原有语句的性能。

2.2.2 生成一个查询执行计划（Query Execution Plan，QEP）

• 当MySQL要执行一个SQL查询的时候，它首先会对该SQL语句进行语法检查，然后构造一个QEP，QEP决定了MySQL从底层存储引擎中获取信息的方式。如果想要查看MySQL查询优化器为SQL语句构造的QEP，只需要在select语句前加上EXPLAIN关键字前缀执行即可。

  • 

• 说明：在大多数情况下，EXPLAIN并不运行实际的SQL语句；然而当优化器需要执行这条SQL语句的一部分来决定如何构造QEP时也会有例外情况，比如在FROM子句中使用派生表，这时在select_type列中显示的单词DERIVED。
• 对QEP了解不多的话，可以主要看索引和受影响的行数这两列信息。
  • EXPLAIN的结果中key列可以显示查询语句使用的索引，任何没有使用索引的查询语句都可以认为是没有被足够调优的SQL查询。
  • 受影响的行数显示在rows列中，这个数值可以用来估计查询需要读取的数据量，这和查询所需要的执行时间是直接相关的。
  • 结果中type列显示的ALL值也是潜在性能问题的一个标志。根据使用的不同的存储引擎，受影响的行数这个指标可能是一个估计值也有可能是一个精确值。即使受影响的行数是一个估计值（当使用InnoDB存储引擎管理表存储的时候），通常情况下，我们也可以把这个估计值作为优化器判别的一个依据。

  • 

• 从我上面给出的EXPLAIN例子中，key列未显示索引值。这个SQL是一个单表select语句，这个语句可以被理解成对整个表进行扫描并从中寻找那些符合WHERE子句判定的行，然后再对结果进行排序。为了实现这个目的，rows列的值可以看做是一个近似值。

Part3：优化查询

• 识别一个运行缓慢的SQL查询时任何优化过程的先决条件

3.1 不应该做的事情

• 在毫无头绪或者找不到什么方法进行优化的时候，可能会想到在where语句的条件上添加一个索引，比如

  # 已有表结构创建索引语法
  ALTER table 表名称 ADD INDEX 索引名称(列名称)
  
  ALTER TABLE index_test ADD INDEX idIndex(id);
             

  

  • Warning：在未经过验证的情况下，不要直接在生产环境中使用！！因为决定是否添加一个新索引并部署它需要考虑很多因素。（这里我执行了这条添加索引的语句）完成这条数据定义语言（DDL）花费了6.8秒的时间。在此期间，由于Alter语句时阻塞操作，因此所有为表添加和修改数据的额外请求都被阻塞了。根据其他数据操作语言（DML）的执行顺序，此时select语句也会被阻塞因而无法完成。如果表更大一些，一个alter语句可能需要几个小时甚至几天才能执行完成！！另一个需要考虑的因素是一个表有多个索引的情况下DML语句的性能开销。

3.2 确认优化

• 在上述添加索引之后，重新执行查询SQL，看效果

  • 

  • 时间只有19毫秒了，速度提升了接近13倍
  • 再使用EXPLAIN查看一下SQL执行

    • 

• 这里回头说一下添加完测试数据为什么要去掉主键id，其一是因为想使用一个纯净的表结构，无索引情况下添加索引对比前后效果，其二是，如果保留主键索引的情况下，假设将SQL的where查询调整到其他字段上，然后给其他字段添加索引比较效果，可能产生的效率不一定降低，也有可能上升。这也是之前提到的新增索引不能随便添加，要经过验证才可以应用到生产环境（小编这里测试过，保留id主键，给psd添加索引，然后查询后效率变低了）
• 对比一下使用id索引前后的EXPLAIN的执行效果，MySQL优化器选择了key列值指定的索引。预计执行SQL受影响的数从2000000变成了1，两百万倍的减少。

3.3 正确的方式

• 为表添加索引有很多优点，包括性能优化。在决定添加索引之前，一般都要检查两个地方，一个是验证现有表结构，然后是确认表的大小。
• 快捷方式：执行如下sql获取上述信息

  • 

  • 

• 从以上命令的返回结果来看，当前表结构包括一个id列的索引。也可以通过第二个命令知道Data_length和Rows信息来获得表大小的一个近似值。

3.4 备选方案

• 优化SQL的正确方法包括理解和验证此SQL语句以及与表相关的SQL语句的目的。通过执行这种分析，将会发现执行该SQL语句的应用程序代码已经含有改进查询的补充信息。

ows信息来获得表大小的一个近似值。

3.4 备选方案

• 优化SQL的正确方法包括理解和验证此SQL语句以及与表相关的SQL语句的目的。通过执行这种分析，将会发现执行该SQL语句的应用程序代码已经含有改进查询的补充信息。