MySQL索引与优化

1. SQL 优化步骤

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

首先，我们可以使用 show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value，状态变量是只读的。如果使用 SQL 命令，可以使用 like 或者 where 条件来限制结果。like 可以对变量名做标准模式匹配。

下面还有很多变量，读者可以自己尝试一下。也可以在操作系统上使用 mysqladmin extended-status 命令来获取这些消息。
但是执行 mysqladmin extended-status 后，出现这个错误。
这里需要注意一下 show status 命令中可以添加统计结果的级别，这个级别有两个

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

如果不指定统计结果级别的话，默认使用 session 级别。
对于 show status 查询出来的统计结果，有两类参数需要注意下，一类是以 Com_ 为开头的参数，一类是以 Innodb_ 为开头的参数。
下面是 Com_ 为开头的参数，参数很多，我同样没有截全。

Com_xxx 表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数，即

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

以 Innodb_ 为开头的参数主要有：

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

通过上面这些参数执行结果的统计，能够大致了解到当前数据库是以更新（包括插入、删除）为主还是查询为主。

除此之外，还有一些其他参数用于了解数据库的基本情况。

• Connections：查询 MySQL 数据库的连接次数，这个次数是不管连接是否成功都算上。
• Uptime：服务器的工作时间。
• Slow_queries：满查询次数。
• Threads_connected：查看当前打开的连接的数量。
  博客汇总了几乎所有 show status 的参数：
  https://blog.csdn.net/ayay_870621/article/details/88633092

1.2 慢查询日志定位执行效率较低的 SQL

定位执行效率比较慢的 SQL 语句，一般有两种方式

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

MySQL 中提供了一个慢查询的日志记录功能，可以把查询 SQL 语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。用 --log-slow-queries 选项启动时，mysqld 会写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL 。
比如我们可以在 my.cnf 中添加如下代码，然后退出重启 MySQL。

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

通常我们设置最长的查询时间是 2 秒，表示查询时间超过 2 秒就记录了，通常情况下 2 秒就够了，然而对于很多 WEB 应用来说，2 秒时间还是比较长的。
也可以通过命令来开启：
1。先查询 MySQL 慢查询日志是否开启

show variables like "%slow%";

2。再启用慢查询日志

set global slow_query_log='ON';

3。然后再次查询慢查询是否开启

如图所示，表示已经开启了慢查询日志。

慢查询日志会在查询结束以后才记录，所以在应用反应执行效率出现问题的时候慢查询日志并不能定位问题，此时应该使用 show processlist 命令查看当前 MySQL 正在进行的线程。包括线程的状态、是否锁表等，可以实时的查看 SQL 执行情况。同样，使用mysqladmin processlist语句也能得到此信息。

下面就来解释一下各个字段对应的概念

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

State 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章
https://blog.csdn.net/weixin_34357436/article/details/91768402
这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。

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

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

比如我们使用下面这条 SQL 语句来分析一下执行计划

explain select * from test1;

上表中涉及内容如下

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

PRIMARY ，查询中最外层的 SELECT（如两表做 UNION 或者存在子查询的外层的表操作为
PRIMARY，内层的操作为 UNION），比如下面这段子查询。

UNION，在 UNION 操作中，查询中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系时）。

SUBQUERY：子查询中首个SELECT（如果有多个子查询存在），如我们上面的查询语句，子查询第一个是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。
1、table ，这个选项表示输出结果集的表。

2、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 ，性能最差。

上面就是 type 内容的大致解释，关于 type 我们经常会在 SQL 调优的环节使用 explain 分析其类型，然后改进查询方式，越靠近 system 其查询效率越高，越靠近 all 其查询效率越低。

3、possible_keys ：表示查询时，可能使用的索引。
4、key ：表示实际使用的索引。
5、key_len ：索引字段的长度。
6、rows ：扫描行的数量。
7、filtered ：通过查询条件查询出来的 SQL 数量占用总行数的比例。
8、extra ：执行情况的描述。
通过上面的分析，我们可以大致确定 SQL 效率低的原因，一种非常有效的提升 SQL 查询效率的方式就是使用索引，接下来我会讲解一下如何使用索引提高查询效率。

2. 索引介绍-结构-种类-优化

2.1 MySQL索引介绍

索引的目的就是用于快速查找某一列的数据，对相关数据列使用索引能够大大提高查询操作的性能。不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多。
如果表中查询的列有索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

2.2 索引结构、种类

一：索引结构

• 全局索引(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 的优势在于范围查找。

二：MySQL索引的类型
普通索引(INDEX)：仅加速查询（普通索引是最基础的索引类型，它没有任何限制 ）

create index normal_index on cxuan003(id);

唯一索引(UNIQUE)：加速查询 + 列值唯一（可以有null），如果是组合索引，则列值的组合必须唯一，创建方式如下

create unique index normal_index on cxuan003(id);

主键索引(PRIMARY KEY)：加速查询 + 列值唯一（不可以有null）+ 表中只有一个，是一种特殊的索引。一般是在建表的时候同时创建主键索引。

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

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则，下面我们就会创建组合索引。而专门用于组合搜索，其效率大于索引合并。

全文索引(fulltext index)：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有 char、varchar，text 列上可以创建全文索引，创建表的适合添加全文索引

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)

参考：https://mp.weixin.qq.com/s/KlewDY6NTKJv-lsSzpXESA

2.3 创建索引的技巧

1、维度高的列创建索引。

• 数据列中不重复值出现的个数，这个数量越高，维度就越高。
• 如数据表中存在8行数据a,b ,c,d,a,b,c,d这个表的维度为4。
• 要为维度高的列创建索引，如性别和年龄，那年龄的维度就高于性别。
• 性别这样的列不适合创建索引，因为维度过低。
  2、对 where,on,group by,order by 中出现的列使用索引。
  3、对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引键。
  4、为较长的字符串使用前缀索引。
  5、不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。
  6、使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引。

2.4 什么样的sql不走索引

一：要尽量避免这些不走索引的sql

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算，原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正则表达式不使用索引，这应该很好理解，所以为什么在SQL中很难看到regexp关键字的原因 
-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用or 关键字 

-- 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

二：多表关联时的索引效率
SELECT sname FROM stu WHERE LEFT(date,4) <1990; — 不会使用索引，因为使用了函数运算，原理与上面相同
SELECT * FROM houdunwang WHERE uname LIKE’后盾%’ — 走索引
SELECT * FROM houdunwang WHERE uname LIKE “%后盾%” — 不走索引

从上图可以看出，所有表的type为all，表示全表索引。也就是6 6 6，共遍历查询了216次。

除第一张表示全表索引（必须的，要以此关联其他表），其余的为range（索引区间获得），也就是6+1+1+1，共遍历查询9次即可。

所以我们建议在多表join的时候尽量少join几张表，因为一不小心就是一个笛卡尔乘积的恐怖扫描，另外，我们还建议尽量使用left join，以少关联多。因为使用join 的话，第一张表是必须的全扫描的，以少关联多就可以减少这个扫描次数。

2.5 索引的常用优化总结

1、有索引但未被用到的情况（不建议）

• Like的参数以通配符%开头时，以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。
• where条件不符合最左前缀原则时
• 使用！= 或 <> 操作符时（使用>或<会比较高效。）
• 使用or来连接条件（两端都有索引才行）
• 对字段进行null值判断（is null, is not null 也无法使用索引，在实际中尽量不要使用null。）
• 索引列参与计算

2、避免使用select * 语句，其在解析的过程中，会将 ‘*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

3、order by 语句优化

• 重写order by语句以使用索引；
• 为所使用的列建立另外一个索引
• 避免在order by子句中使用表达式

4、提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉
5、在Join关键字连表的时候使用相当类型的例，并将其索引
6、优化嵌套查询：子查询可以被更有效率的连接(Join)替代；
7、很多时候用 exists 代替 in 是一个好的选择

2.6 sql语句优化原则

1、使用连接（JOIN）来代替子查询(Sub-Queries)
2、避免函数索引
3、使用in来替换OR条件
4、LIKE前缀%号、双百分号、_下划线查询非索引列或者 *无法使用到索引，如果查询的是索引列则可以
5、读取适当的记录LIMIT M,N，而不是读取多余的记录
6、选取最适当的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2.7 索引的弊端

不要盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

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

3.1MySQL 分析表

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

analyze table cxuan005;

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

• Table：表示表的名称；
• Op：表示执行的操作，analyze 表示进行分析操作，check 表示进行检查查找，optimize 表示进行优化操作；
• Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；
• Msg_text：显示信息。

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

3.2MySQL检查表

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

check table cxuan005;

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

3.3MySQL优化表

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

optimize table cxuan005;

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