黑马程序员--mysql高级 --sql优化相关笔记

1.5.sql的优化

1.5.1.查看sql的执行频率

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

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

1.5.2.执行效率比较低的sql的定位

可以通过以下两种方式：

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句。

• show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

慢查询日志

-- 查看慢日志配置信息 
show variables like \'%slow_query_log%’; 

-- 开启慢日志查询 
set global slow_query_log=1; 

-- 查看慢日志记录SQL的最低阈值时间 
show variables like \'long_query_time%’; 

-- 修改慢日志记录SQL的最低阈值时间 
set global long_query_time=4;

show processlist

show processlist;

各个参数的意义：

1.5.3.explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

其中各个 参数的含义为：

对id的解释

对select_type的解释

对type的解释

各个type的展示

• NULL

• system

• const(查询唯一索引时)
  

• eq_ref（左表有主键索引 且左表每一行和右表刚好匹配）

  举个例子：我们创建两个表 user2 和 user2_ex

结果所示：当user2 和 user_ex的行恰好一一对应的时候查找模式为all（因为有 * ）和eq_ref

而添加一个重复元素后，就不能使用eq_ref了，效率降低。

• ref （左表为普通索引时）

ref允许左表匹配右表多行。

• range（范围查询）

• index （打印索引列）

• all （普通的全表查询）

重点是这个比较的大于顺序，一定要记牢，优化时候尽量把它维持在index等级之上。

其他指标（key 等）

]

1.5.4.使用show profile分析sql

查找步骤 ： 使用查找语句> show profiles展示>找到你要分析语句的序号> show profile for 你要找的语句

可以如上图最后一条 查找语句的cpu占用。

1.5.5.mysql的具体优化策略

1.依靠索引优化

创建组合索引，提高查找速度。要时刻遵循最左匹配法则，不能跳过。 要注意，实际查询值越高，就使用了越多的索引，速度越高。

• 如果跳过一个索引，违反最左原则，将会失效

• 范围查询，运算操作和单引号失去会使索引失去效果

• 避免使用* 因为查询除索引以外的数据需要从磁盘读取，效率较低

对比

同样的道理password没加索引，效率下降。

extra各个指数的指代意义

• or会引起索引失效

• 模糊索引

• 即使有索引，不用索引的情况

当我们一个列重复的数据较多时，我们查找重复的数据时，比如在下图找地址在北京的数据。数据库会自动为我们全表查询，此时全表查询效率较高。

而查找西安市的时候，会使用索引查询。

​

not in和in跟上面 道理一样 多就不用索引。

• 尽量使用复合索引
  

• 当一个查询条件包括三个单列索引时，取最优的生效

• 查询时，联表查询优于嵌套子查询

2.优化order by

下面的显示来自extra

尽量顺序一致，排序方式相同

3.优化Filesort

当我们迫不得已使用Filesort 时

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

4.优化limit

这么写

或者

5.大批插入数据的优化

当我们要用.log文件导入大量数据，怎么做才更有效率呢？

插入方法

-- 1、首先，检查一个全局系统变量 \'local_infile\' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like \'local_infile\';
 
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
 
-- 3、加载数据 

load data local infile \'D:\\\\sql_data\\\\sql1.log\' into table tb_user fields terminated by \',\' lines terminated by \'\\n\';

• 按照主键列排好顺序

• ​ 关闭唯一检索

  mysql在添加数据时会检测唯一索引的数据是否重复。如果我们事先已经检查好，就可以关闭唯一检索，提高效率。

  -- 关闭唯一性校验
  SET UNIQUE_CHECKS=0;
   
  truncate table tb_user;
  load data local infile \'D:\\\\sql_data\\\\sql1.log\' into table tb_user fields terminated by \',\' lines terminated by \'\\n\';
   
  SET UNIQUE_CHECKS=1;
  
  

6.优化insert

三个方向

1. 有序插入

   -- 数据有序插入
   insert into tb_test values(4,\'Tim\');
   insert into tb_test values(1,\'Tom\');
   insert into tb_test values(3,\'Jerry\');
   insert into tb_test values(5,\'Rose\');
   insert into tb_test values(2,\'Cat\');
    
    
   -- 优化后
   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(4,\'Tim\');
   insert into tb_test values(5,\'Rose\');
   

2. 尽量集中插入

   -- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的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\');
   	
   

3. 用一个事务插入（记得提前关了自动提交）

-- 在事务中进行数据插入。
begin;
insert into tb_test values(1,\'Tom\');
insert into tb_test values(2,\'Cat\');
insert into tb_test values(3,\'Jerry\');
commit;