42 张图带你撸完 MySQL 优化 中

索引使用

索引可以在创建表的时候进行创建，也可以单独创建，下面我们采用单独创建的方式，我们在 cxuan004 上创建前缀索引

我们使用 explain 进行分析，可以看到 cxuan004 使用索引的情况

如果不想使用索引，可以删除索引，索引的删除语法是

索引使用细则

我们在 cxuan005 上根据 id 和 hash 创建一个复合索引，如下所示

create index id_hash_index on cxuan005(id,hash);

然后根据 id 进行执行计划的分析

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

可以发现，即使 where 条件中使用的不是复合索引（Id 、hash），索引仍然能够使用，这就是索引的前缀特性。但是如果只按照 hash 进行查询的话，索引就不会用到。

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

如果 where 条件使用了 like 查询，并且 % 不在第一个字符，索引才可能被使用。

对于复合索引来说，只能使用 id 进行 like 查询，因为 hash 列不管怎么查询都不会走索引。

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

可以看到，如果第一个字符是 % ，则没有使用索引。

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

如果使用了 % 号，就会触发索引。

如果列名是索引的话，那么对列名进行 NULL 查询，将会触发索引。

explain select * from cxuan005 where id is null;

还有一些情况是存在索引但是 MySQL 并不会使用的情况。

最简单的，如果使用索引后比不使用索引的效率还差，那么 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 索引的使用过程中，有一个 Handler_read_key 值，这个值表示了某一行被索引值读的次数。Handler_read_key 的值比较低的话，则表明增加索引得到的性能改善不是很理想，可能索引使用的频率不高。

还有一个值是 Handler_read_rnd_next，这个值高则意味着查询运行效率不高，应该建立索引来进行抢救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值比较高，就说明表索引不正确或写入的查询没有利用索引。

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

对于大多数开发者来说，他们更倾向于解决简单 SQL的优化，而复杂 SQL 的优化交给了公司的 DBA 来做。

下面就从普通程序员的角度和你聊几个简单的优化方式。

MySQL 分析表

分析表用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息，使得 SQL 生成正确的执行计划。如果用于感觉实际执行计划与预期不符，可以执行分析表来解决问题，分析表语法如下

analyze table cxuan005;

分析结果涉及到的字段属性如下

Table：表示表的名称；

Op：表示执行的操作，analyze 表示进行分析操作，check 表示进行检查查找，optimize 表示进行优化操作；

Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；

Msg_text：显示信息。

对表的定期分析可以改善性能，应该成为日常工作的一部分。因为通过更新表的索引信息对表进行分析，可改善数据库性能。

MySQL 检查表

数据库经常可能遇到错误，比如数据写入磁盘时发生错误，或是索引没有同步更新，或是数据库未关闭 MySQL 就停止了。遇到这些情况，数据就可能发生错误：Incorrect key file for table: ' '. Try to repair it. 此时，我们可以使用 Check Table 语句来检查表及其对应的索引。

check table cxuan005;

检查表的主要目的就是检查一个或者多个表是否有错误。Check Table 对 MyISAM 和 InnoDB 表有作用。Check Table 也可以检查视图的错误。

MySQL 优化表

MySQL 优化表适用于删除了大量的表数据，或者对包含 VARCHAR、BLOB 或则 TEXT 命令进行大量修改的情况。MySQL 优化表可以将大量的空间碎片进行合并，消除由于删除或者更新造成的空间浪费情况。它的命令如下

optimize table cxuan005;

我的存储引擎是 InnoDB 引擎，但是从图可以知道，InnoDB 不支持使用 optimize 优化，建议使用 recreate + analyze 进行优化。optimize 命令只对 MyISAM 、BDB 表起作用。