MySQL——索引和执行计划和慢查询

目录

常见索引模型

索引分类

聚簇索引

主键索引

非聚簇索引

普通索引

唯一索引

全文索引

联合索引

前缀索引

空间索引

覆盖索引

Explain

慢查询

Show Profiles

---

常见索引模型

索引分类

聚簇索引

主键索引

在Innodb中，Mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。

在Innodb中，聚簇索引默认就是主键索引。

假如表没有设定主键，则按照下列规则来创建聚簇索引

没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引。

如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

 
例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。
建表语句如下：

create table user(
id int primary key,
t int not null,
name varchar(16),
index (t))engine=InnoDB;

非聚簇索引

普通索引

如下user建表语句中的 t 就是一个普通索引，普通索引与主键索引的区别在于，普通索引的叶子节点存放的不是行数据，而是主键值。
 
例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。建表语句如下：

create table user(
id int primary key,
t int not null,
name varchar(16),
index (t))engine=InnoDB;

例如：

select * from user where t=100;

这个查询sql会通过 t 这个普通索引在自身的 B+ 树上找到对应主键：1，然后再使用1在主键索引所在的B+树上查询出真实表的行数据后返回结果，这个操作被称为回表。

唯一索引

与普通索引类似，不同点在于唯一索引的索引列的值必须唯一，但允许有空值，这点与主键不同（主键索引列的值唯一，但不能为空）。

如果是多个字段组成的联合索引，则列值的组合必须唯一，创建方法与普通索引类似。

全文索引

5.6版本之后InnoDB存储引擎开始支持全文索引，Mysql允许在char、varchar、text类型上建立全文索引。
 
Mysql支持三种模式的全文检索模式

1.自然语言模式：通过match against 传递某个特定的字符串进行检索
2.布尔模式：可以为检查的字符串增加操作符，布尔操作符可以通过以下sql语句查看：

show variables like '%ft_boolean_syntax%';

3.查询扩展模式：当查询的关键字太短，用户需要隐含知识时进行。

例如，对于单词operating system的查询，用户可能希望查询的结果除了包含operating system的文档，还应该包含linux，windows，unix的单词。这种查询会分2次执行检索，第1次是使用给定的operating system的短语进行检索，第2次结合第一次相关性比较高的进行检索。

联合索引

使用多个列字段建立的索引，称为联合索引，也叫组合索引。
联合索引为：(t,name)

其建表语句如下：

create table user(
id int primary key,
t int not null,
name varchar(16),
index(t),
index(t,name) )engine=innodb;

说到联合索引，一定要谈谈最左匹配原则,假设联合索引由列(a,b,c)组成，则以下顺序满足最左前缀规则：a、ab、abc；。

所谓最左匹配原则指的就是如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

（1）设定表T已建立联合索引(id, name)

where条件为：id = 1 或者id = 1 and name = 'tom'，满足联合索引的最左匹配原则，是可以匹配索引来执行sql的；where条件为：name = 'tom' and id = 1，也满足联合索引的最左匹配原则，因为Mysql优化器会自动调整id，name的顺序与索引顺序一致，这样就能用到联合索引了。

where条件为：name = 'tom'不满足联合索引的最左匹配原则，也就无法使用(id, name)的联合索引了。
（2）设定表T已建立联合索引(a, b, c, d)

where条件为：a = 10 and b = 20 and c >100 and d = 5
这个where条件，只有a, b, c能使用到联合索引，d无法使用索引，因为c>100属于范围查询，将后面d的索引匹配给中断了。

生效条件

(1) select * from table where a=3 and b=5 and c=4; ---- abc顺序

abc三个索引都在where条件里面用到了，而且都发挥了作用。

(2) select * from table where c=4 and b=6 and a=3;

where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样。

(3) select * from table where a=3 and c=7; ---- 没有b，中间断点，阻塞了c的索引

a用到索引，b没有用，所以c是没有用到索引效果的。

(4) select * from table where a=3 and b>7 and c=3; ---- b范围值，断点，阻塞了c的索引

a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引。

(5) select * from table where b=3 and c=4; --- 联合索引必须按照顺序使用，并且需要全部使用

因为a索引没有使用，所以这里 bc都没有用上索引效果。

(6) select * from table where a>4 and b=7 and c=9; ---- a范围值，断点，阻塞了b c的索引

a用到了 b没有使用，c没有使用。

(7) select * from table where a=3 order by b;

a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的

(8) select * from table where a=3 order by c; ---- 没有b，中间断点，阻塞了c的索引

a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了。

(9） select * from table where b=3 order by a; --- 需要全部使用

b没有用到索引，排序中a也没有发挥索引效果。

(10) select * from table where b=3 and c=2 order by a;

a b c都没有用到索引。

失效条件

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描；
• 存储引擎不能使用索引范围条件右边的列；
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *；
• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描；
• is null,is not null也无法使用索引；
• like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

问题：解决like‘%字符串%’时索引不被使用的方法？

• 字符串不加单引号索引失效

SELECT * from staffs where name='2000'; -- 因为mysql会在底层对其进行隐式的类型转换

SELECT * from staffs where name=2000; --- 未使用索引

前缀索引

当索引列的字符比较多时，索引很大且速度很慢，此时可以优化索引列，只索引列开始的部分字符串，以此节约索引空间，提高索引效率。

前缀索引的使用原则是：降低重复的索引值
例如有以下一张学生表，st_num为学号

从上表可以发现 st_num 字段前7位都是重复的，都是以0102021开头的。

如果使用前1-7位字符来做前缀索引就会出现大量索引值重复的情况。

此时索引值重复性高，查询效率低下，不符合前缀索引的原则，因此可以依据具体需求来决定使用前8-10位字符来做前缀索引。
前缀索引创建方式如下：

create table `student` (
`st_num` varchar(255) not null,
`sex` int(10) not null,
`name` varchar(255) not null,
index (st_num(8))
)engine=InnoDB;

空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。

CREATE TABLE tb_geo(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(128) NOT NULL,
pnt POINT NOT NULL,
SPATIAL INDEX `spatIdx` (`pnt`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

覆盖索引

还是以上述例子来讲解，现将下列查询语句：

select * from student where name='Candy';

修改为：

select s_id from student where name='Candy';

这时只需要查 s_id 的值，而 s_id 的值已经在普通索引 name上了，因此可以从非聚簇索引B+树上直接返回查询结果，不需要回表操作。

也就是说，在这个查询里面，索引name已经覆盖了我们的查询需求，因此称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

Explain

用法：explain SQL语句;

结果有十二列：

id	select查询序列号，从大到小执行，相同从上往下执行
select_type	select语句的类型 (1) SIMPLE(简单SELECT，不使用UNION或子查询等) (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY) (3) UNION(UNION中的第二个或后面的SELECT语句) (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询) (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select) (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询) (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询) (8) DERIVED(派生表的SELECT, FROM子句的子查询) (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table	查询的表，有时不是真实的表名字，可能是简称，也可能是第几步执行的结果的简称
partitions	显示匹配的分区
type	对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。 常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好） ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 index: Full Index Scan，index与ALL区别为index类型只遍历索引树 range:只检索给定范围的行，使用一个索引来选择行 ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys	指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null） 该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
key	key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中，如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的） 不损失精确性的情况下，长度越短越好
ref	列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows	估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
filtered	按表条件过滤的行百分比
extra	该列包含MySQL解决查询的详细信息,有以下几种情况： Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。 Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。 Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” -- 测试Extra的filesort explain select * from emp order by name; Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。 Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行 No tables used：Query语句中使用from dual 或不含任何from子句 -- explain select now() from dual;

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

慢查询

相关配置项：

slow_query_log:ON/OFF 默认是OFF

slow_query_log_file:/var/lib/mysql/hostname-slow.log日志存放路径

long_query_time:10时间阙值，默认10s

log_queries_not_using_indexes:未使用索引的查询是否记录到慢日志中，ON/OFF，默认关

log_output:日志存储方式，file/table，默认是file，表的话是写入到mysql.slow_log表

表字段：

time	查询时间
user@Host	用户账号和ip 线程id
query_time	执行话费时长 时间是秒
lock_time	执行获取锁的时间
rows_sent	活得结果的行数
rows_examined	扫描的行数
SET timestamp	执行的具体时间
SQL语句

日志分析工具：mysqldumpslow

-s	排序
al	执行时获取锁时长平均值
ar	返回行数的平均值
at	查询时长的平均值
c	查询次数
l	获取锁时长
r	获得返回行数
t	执行总时长
-t num	返回几行
-g pattern	后面跟正则 筛选

例子：

1.取出耗时最长的两条SQL：

格式：mysqldumpslow -s t -t 2 慢日志文件

mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

参数分析：

• Count:出现次数；
• Time:执行最长时间(累计总耗费时间)；
• Lock:等待锁的时间；
• Rows:发送给客户端的行总数(扫描的行总数)；
• 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示)。

2.取出查询次数最多，且使用了in关键字的1条SQL：

mysqldumpslow -s c -t 1 -g 'in' /var/lib/mysql/localhost-slow.log；

3.得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

4.得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

5.得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

慢查询优化建议：

1、日期大小的比较，传到xml中的日期格式要符合'yyyy-MM-dd'，这样才能走索引，如：'yyyy'改为'yyyy-MM-dd'，'yyyy-MM'改为'yyyy-MM-dd'【这样MYSQL会转换为日期类型】

2、条件语句中无论是等于、还是大于小于，WHERE左侧的条件查询字段不要使用函数或表达式或数学运算

3、WHERE条件语句尝试着调整字段的顺序提升查询速度，如把索引字段放在最前面、把查询命中率高的字段置前等

4、保证优化SQL前后其查询结果是一致的

5、在查询的时候通过将EXPLAIN命令写在查询语句前，测试语句是否有走索引【具体用法百度】

6、禁止使用SELECT * FROM操作，应只返回需要的字段，不需要的字段不要返回

7、可以尝试分解复杂的查询，在应用层面进行表关联，以此代替SQL层面的表关联

8、WHERE子句和ORDER BY子句涉及到的列建索引

9、避免在WHERE子句中对字段进行NULL判断【可以对表字段改造一下，字符串型字段默认值设置为空字符串，数字型字段默认值设置为0，日期型字段默认值设置为1990-01-01等】

10、避免在WHERE子句中使用!=或<>操作符

11、避免在WHERE子句中使用OR操作符

12、BETWEEN AND代替IN

13、LIKE '%abc%'不会走索引，而LIKE 'abc%'会走索引

14、避免对字段进行表达式操作

15、避免对字段进行函数操作

16、GROUP BY操作默认会对GROUP BY后面的字段进行排序，如果你的程序不需要排序，可在GROUP BY语句后面加上ORDER BY NULL去除排序

17、如果是数值型字段，则尽量设计为数值型字段，不要为了方便、为了偷懒而给后面维护的同事埋坑

18、表中所有字段设计为NOT NULL

19、返回条数固定时，用LIMIT语句限制返回记录的条数，如只需要一条记录，或肯定只有一条记录符合条件，那建议加上LIMIT 1

20、对于枚举类型的字段【即有固定罗列值的字段】，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等

21、对于存IP地址的字段设计为成UNSIGNED INT型

22、避免在SQL中使用NOW()、CURDATE()、RAND()函数【因为这种方式会导致MYSQL无法使用SQL缓存】，可以转化为通过传入参数的方式

23、对于统计类的查询【如查询连续几个月的数据总量，或查询同比、环比等】，可以通过定时查询并统计到统计表的方式提高查询速度

SQL优化是个很复杂的过程，有可能出现拆东墙补西墙的情况，比如给数据库表加入了索引之后，确实查询快了，可是存储空间加多了，插入删除操作耗时也增加了。如果在一个写多读少的系统中，执行这种优化可能会起到反效果。以优化完之后千万不能大意，要持续监控系统，防止出现引入新瓶颈的情况。

Show Profiles

分析当前会话中SQL语句执行的资源消耗情况的工具，可用于SQL调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

profiling：ON/OFF，默认关闭
profiling_history_size：默认保存15条查询记录

show profile [参数1，参数2...] for query Query_ID;查看这条SQL语句的执行情况：

 

show profile的常用查询参数 

①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接受开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息。

需要注意的结果值

①converting  HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。

②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！

④locked。

 show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。