MySQL索引及性能优化分析

一、SQL性能下降的原因

• 查询语句问题，各种连接、子查询
• 索引失效（单值索引、复合索引）
• 服务器调优及各个参数设置（缓冲、线程池等）

二、索引 排好序的快速查找数据结构

1. 索引分类

• 单值索引 一个索引只包含单个列，一个表可以有多个
• 复合索引 一个索引包含多个列
• 唯一索引 索引的值必须唯一，但是允许空值

默认使用B+树索引，除B+树索引外，还有哈希索引（hash index）等

2. 索引结构

• B-Tree 索引
• Hash 索引
• Full-Text 索引
• R-Tree 索引

B-Tree 示意图

3. 索引的优势和劣势

• 优势 提高检索效率、降低排序成本
• 劣势 索引需要占用空间，降低表更新速度

4. 基本语法

创建

CREATE [UNIQUE] INDEX indexName ON mytable(column_list); 
ALTER mytable ADD [UNIQUE] INDEX indexName ON (column_list); 
ALTER mytable ADD PRIMARY KEY (column_list);
ALTER mytable ADD FULLTEXT indexName(column_list);

删除

DROP INDEX indexName ON mytable;

查看

SHOW INDEX FROM table_nameG;

 三、explain执行计划

id select 查询的序列号，表示查询中执行select子句或操作表的顺序

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高
• id有相同有不同，id如果相同，可以认为是一组，从上往下顺序执行；所有组中，id值越大，优先级越高，越先执行

select_type 查询的类型，主要是用于区别普通查询、联合查询、子查询等复杂查询

• SIMPLE 简单的select查询，查询中不包含子查询或union
• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
• SUBQUERY 在select或where列表中包含了子查询
• DERIVED 在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
• UNION 若第二个select出现在UNION之后，则被标记为UNION
• UNION RESULT 从UNION表获取结果的select

table 显示这一行数据是关于哪张表的

type 访问类型

• system 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计
• const 表示通过索引一次就找到了，用于比较primary key或unique索引
• eq_ref 唯一性索引扫描，对于每个索引，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
• ref 非唯一性索引扫描，返回所有匹配某个单独值的所有行
• range 只检索给定范围的行，使用一个索引来选择行。key列显示了使用哪个索引，一般是where语句中出现了between、<、>、in等
• index Full Index Scan,index与all的区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。
• ALL 全表扫描

possible_keys 可能用到的索引

key 实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在Key列中。

key_len 表示索引中使用的字节数，显示为索引字段的最大可能长度，并非实际使用长度。

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所要读取的行数。

extra 包含不适合在其它列中显示但是十分重要的额外信息。

• Using Filesoft MySQL无法利用索引完成的排序操作成为文件排序
• Using temporary 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。 
• Using Index 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据，如果同时出现了Using Where 表名索引被用来执行索引键值的查找，如果没有出现Using Where 表名索引用来读取数据而非执行查找动作。
• Using where 表明使用了where过滤
• Using join buffer 使用了连接缓存, 出现在两表连接时，驱动表没有索引的情况下，给驱动表建立索引可解决此问题，type将变成ref
• Impossible where where子句的值总是false，不能用来获取任何元组

 ※ 覆盖索引

select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖。

四、一般查询优化

• 尽量使用全值匹配
• 最左前缀法则，如果索引了多列，查询从索引的最左列开始并且不跳过索引中的列。
• 不要在索引列上做任何操作（计算、函数、类型转换）
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引
• 使用不等于无法使用索引
• is not null 无法使用索引，但是is null 可以的
• like以通配符开头索引失效，以通配符结尾索引type为range ，通配符开头和结尾用覆盖索引type为index
• 字符串不加单引号索引失效(底层转换、使用函数)
• 用or连接时,索引失效
• 多表查询有索引的情况下inner join效果是最好的，如果用in或exists注意小表驱动大表

※ Order By 排序使用索引情况

五、查询截取分析

1. 分析方案

• 开启慢查询日志，例如设置超过5秒就是慢SQL
• explain + 慢SQL分析
• show profile 查询SQL在MySQL服务器的执行细节和生命周期情况
• SQL服务器参数调优

2. 慢查询日志

使用方法

　　查看开启 SHOW VARIABLES LIKE '%slow_query_log%';

　　开启 SET GLOBAL slow_query_log=1;    #针对当前数据库有效

　　查看阙值时间 SHOW VARIABLES LIKE 'long_query_time%';

　　修改 SHOW VARIABLES LIKE 'long_query_time%'; #需要重新连接或新开一个会话才能看到修改值。

　　set session long_query_time=1 #改变当前session变量; 

　　查询当前系统中有多少条慢查询记录 SHOW GLOBAL STATUS LIKE '%Slow_queries%';

分析工具

　　mysqldumpslow　　

　　

　　得到返回记录集最多的10个SQL
　　mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

　　得到访问次数最多的10个SQL
　　mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

　　得到按照时间排序的前10条里面含有左连接的查询语句
　　mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

　　另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
　　mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

3. Show Profile

开启功能，默认是关闭，使用前需要开启

　　show variables like 'profiling';

　　set profiling=1;　　

运行SQL

　　select * from emp group by id%10 limit 150000;

　　select * from emp group by id%20  order by 5;

查看结果

　　show profiles;

诊断SQL

　　show profile cpu,block io for query  n

　　type:
　　| 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

5. 全局查询日志

配置启用

　　在mysql的my.cnf中，设置如下：

　　#开启

　　general_log=1   

　　# 记录日志文件的路径

　　general_log_file=/path/logfile

　　#输出格式

　　log_output=FILE

编码启用

　　set global log_output='TABLE';

　　select * from mysql.general_log;        

六、MySQL锁机制

1. MyISAM锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
锁类型 　　他人可读 　　他人可写
读锁　　　　 是 　　　　　　否
写锁 　　　　否 　　　　　　否

结论：
结合上表，所以对MyISAM表进行操作，会有以下情况：
　　1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
　　2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

2. Innodb锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁.

事务（Transaction）及其ACID属性

　　事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
　　l 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

　　l 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

　　l 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
　　l 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

1>更新丢失(Lost Update) 

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

 2>脏读(Dirty Reads)

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取
的数据无效，不符合一致性要求。

3>不可重复读(Non-Repeatable Reads)

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
一句话：一个事务范围内两个相同的查询却返回了不同数据。

4>幻读(Phantom Reads)

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
一句话：事务A 读取到了事务B提交的新增数据，不符合隔离性。

事务隔离级别

　　脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别：show variables like 'tx_isolation';

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
• 涉及相同表的事务，对于调用表的顺序尽量保持一致。
• 在业务环境允许的情况下,尽可能低级别事务隔离