MySQL 调优都不会？

通过 show status 命令了解 SQL 执行次数

• session 级：默认当前链接的统计结果
  
• global 级：自数据库上次启动到现在的统计结果
  

• Com_select：执行 select 操作的次数，一次查询会使结果 + 1。
  
• Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
  
• Com_update：执行 UPDATE 操作的次数。
  
• Com_delete：执行 DELETE 操作的次数。
  

• Innodb_rows_read：执行 select 查询返回的行数。
  
• Innodb_rows_inserted：执行 INSERT 操作插入的行数。
  
• Innodb_rows_updated：执行 UPDATE 操作更新的行数。
  
• Innodb_rows_deleted：执行 DELETE 操作删除的行数。
  

• Connections：查询 MySQL 数据库的连接次数，这个次数是不管连接是否成功都算上。
  
• Uptime：服务器的工作时间。
  
• Slow_queries：满查询次数。
  
• Threads_connected：查看当前打开的连接的数量。
  

定位执行效率较低的 SQL

• 可以通过慢查询日志来定位哪些执行效率较低的 SQL 语句。
  

   
   
   

 
    
    
    log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2


   
   
   

   
   
   

 
    
    
    show variables like "%slow%";


   
   
   

   
   
   

 
    
    
    set global slow_query_log='ON';


   
   
   

• Id ：Id 就是一个标示，在我们使用 kill 命令杀死进程的时候很有用，比如 kill 进程号。
  
• User：显示当前的用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句。
  
• Host：显示 IP ，用于追踪问题
  
• Db：显示这个进程目前连接的是哪个数据库，为 null 是还没有 select 数据库。
  
• Command：显示当前连接锁执行的命令，一般有三种：查询 query，休眠 sleep，连接 connect。
  
• Time：这个状态持续的时间，单位是秒
  
• State：显示当前 SQL 语句的状态，非常重要，下面会具体解释。
  
• Info：显示这个 SQL 语句。
  

通过 EXPLAIN 命令分析 SQL 的执行计划

   
   
   

 
    
    
    explain select * from test1;


   
   
   

• select_type：表示常见的 SELECT 类型，常见的有 SIMPLE，SIMPLE 表示的是简单的 SQL 语句，不包括 UNION 或者子查询操作，比如下面这段就是 SIMPLE 类型。
  

• table ，这个选项表示输出结果集的表。
  
• type，这个选项表示表的连接类型，这个选项很有深入研究的价值，因为很多 SQL 的调优都是围绕 type 来讲的，但是这篇文章我们主要围绕优化方式来展开的，type 这个字段我们暂时作为了解，这篇文章不过多深入。
  
  type 这个字段会牵扯到连接的性能，它的不同类型的性能由好到差分别是
  
  system ：表中仅有一条数据时，该表的查询就像查询常量表一样。
  
  const ：当表中只有一条记录匹配时，比如使用了表主键（primary key）或者表唯一索引（unique index）进行查询。
  
  eq-ref ：表示多表连接时使用表主键或者表唯一索引，比如
  

  
       
       
       
  
   
        
        
        select A.text, B.text where A.ID = B.ID
  
  
       
       
       

  这个查询语句，对于 A 表中的每一个 ID 行，B 表中都只能有唯一的 B.Id 来进行匹配时。
  
  ref ：这个类型不如上面的 eq-ref 快，因为它表示的是因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，C.ID 不是唯一的。
  
  ref_or_null ：与 ref 类似，只不过这个选项包含对 NULL 的查询。
  
  index_merge ：查询语句使用了两个以上的索引，比如经常在有 and 和 or 关键字出现的场景，但是在由于读取索引过多导致其性能有可能还不如 range（后面说）。
  
  unique_subquery ：这个选项经常用在 in 关键字后面，子查询带有 where 关键字的子查询中，用 sql 来表示就是这样
  

  
       
       
       
  
   
        
        
        value IN (SELECT primary_key FROM single_table WHERE some_expr)
  
  
       
       
       

  range ：索引范围查询，常见于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等运算符的查询中。
  
  index ：索引全表扫描，把索引从头到尾扫一遍。
  
  all ：这个我们接触的最多了，就是全表查询，select * from xxx ，性能最差。
  

• possible_keys ：表示查询时，可能使用的索引。
  
• key ：表示实际使用的索引。
  
• key_len ：索引字段的长度。
  
• rows ：扫描行的数量。
  
• filtered ：通过查询条件查询出来的 SQL 数量占用总行数的比例。
  
• extra ：执行情况的描述。
  

索引

索引介绍

索引分类

• 全局索引(FULLTEXT)
  ：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。
  
• 哈希索引(HASH)
  ：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。
  
• B-Tree 索引
  ：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。
  
• R-Tree 索引
  ：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。
  

   
   
   

 
    
    
    create index normal_index on cxuan003(id);


   
   
   

   
   
   

 
    
    
    drop index normal_index on cxuan003;


   
   
   

   
   
   

 
    
    
    create unique index normal_index on cxuan003(id);


   
   
   

主键索引：是一种特殊的索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE 
table
 ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) NOT NULL ,
       PRIMARY KEY (id)
)

CREATE TABLE 
table
 (id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER NOT NULL ,
content text CHARACTER NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (
id
),
  FULLTEXT (content)
);

   
   
   

 
    
    
    CREATE FULLTEXT INDEX index_content ON article(content)


   
   
   

索引使用

索引使用细则

   
   
   

 
    
    
    create index id_hash_index on cxuan005(id,hash);


   
   
   

   
   
   

 
    
    
    explain select * from cxuan005 where id = '333';


   
   
   

   
   
   

 
    
    
    explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';


   
   
   

   
   
   

 
    
    
    explain select * from cxuan005 where id like '%1';


   
   
   

   
   
   

 
    
    
    explain select * from cxuan005 where id like '1%';


   
   
   

   
   
   

 
    
    
    explain select * from cxuan005 where id is null;


   
   
   

• 最简单的，如果使用索引后比不使用索引的效率还差，那么 MySQL 就不会使用索引。
  
• 如果 SQL 中使用了 OR 条件，OR 前的条件列有索引，而后面的列没有索引的话，那么涉及到的索引都不会使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段没有索引，那么我们使用 or 进行查询。
  

  
       
       
       
  
   
        
        
        explain select * from cxuan005 where id = 111 and info = 'cxuan';
  
       
       
       

• 我们从 explain 的执行结果可以看到，虽然 possible_keys 选项上仍然有 id_hash_index 索引，但是从 key、key_len 可以得知，这条 SQL 语句并未使用索引。
  
• 在带有复合索引的列上查询不是第一列的数据，也不会使用索引。
  

  
       
       
       
  
   
        
        
        explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';
  
  
       
       
       

  
  
  
  
• 如果 where 条件的列参与了计算，那么也不会使用索引
  

  
       
       
       
  
   
        
        
        explain select * from cxuan005 where id + '111' = '666';
  
  
       
       
       

  
  
  
  
• 索引列使用函数，一样也不会使用索引
  

  
       
       
       
  
   
        
        
        explain select * from cxuan005 where concat(id,'111') = '666';
  
  
       
       
       

  
  
  
  
• 索引列使用了 like ，并且 
  %
   位于第一个字符，则不会使用索引。
  
• 在 order by 操作中，排序的列同时也在 where 语句中，将不会使用索引。
  
• 当数据类型出现隐式转换时，比如 varchar 不加单引号可能转换为 int 类型时，会使索引无效，触发全表扫描。比如下面这两个例子能够显而易见的说明这一点
  
  
  
  
  
• 在索引列上使用 IS NOT NULL 操作
  
  
  
  
  
• 在索引字段上使用 <>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
  
  
  
  
  

查看索引的使用情况

MySQL 分析表、检查表和优化表

MySQL 分析表

   
   
   

 
    
    
    analyze table cxuan005;


   
   
   

MySQL 检查表

   
   
   

 
    
    
    check table cxuan005;


   
   
   

MySQL 优化表

   
   
   

 
    
    
    optimize table cxuan005;