深入浅出mysql（三）：优化篇

迷南。 2022-05-18 05:42 283阅读 0赞

# **SQL 优化** #

## 1.优化SQL语句的一般步骤 ##

### 1.通过show status 命令了解各种SQL的执行频率 ###

MySQL客户端连接成功之后，通过show \[session|globa\]status命令可以提供服务器状态信息，也可以在操作系统上使用mysqladmin extended-status命令获取这些消息。

参数：

session显示当前连接的统计结果；

global显示自从数据库上次启动至今的统计结果；

如果不写：默认使用的参数是session；

### 2.定位执行效率较低的SQL语句 ###

可以通过以下两种方式定位执行效率较低的SQL语句；

1.  通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries\[=file\_name\]选项启动的时候，mysqld写一个包含所有执行时间超过long\_query\_time秒的SQL语句的日志文件。
2.  慢查询日志在查询结束之后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用**show processlist****命令**查看当前MySQL在进行的线程，包括线程的状态、是否表锁等，可以实时地查看SQL的执行情况，同时对一些表锁操作进行优化；

### 3.通过EXPLANIN分析低效SQL的执行计划 ###

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

通过EXPLAIN语句获取到MySQL如何执行SELECT语句的时候返回的参数说明：

1.  select\_type：SIMPLE（简单表，既不是用表连接或则子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）
2.  table：输出结果集的表
3.  type：表示MySQL在表中找到所需要的行的方式，或者叫访问类型：

<table style="margin-left:21pt;"> 
 <tbody> 
 <tr> 
 <td colspan="7" style="vertical-align:top;width:405.1pt;"> 从左到右，性能由最差到最好 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:34.05pt;"> ALL </td> 
 <td style="vertical-align:top;width:42.5pt;"> index </td> 
 <td style="vertical-align:top;width:42.55pt;"> range </td> 
 <td style="vertical-align:top;width:63.75pt;"> ref </td> 
 <td style="vertical-align:top;width:63.8pt;"> eq_ref </td> 
 <td style="vertical-align:top;width:100.55pt;"> const/system </td> 
 <td style="vertical-align:top;width:57.9pt;"> NULL </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:34.05pt;"> 全表扫描，MySQL遍历全表来找到匹配的行 </td> 
 <td style="vertical-align:top;width:42.5pt;"> 索引全扫描，MySQL遍历整个索引来查询匹配的行 </td> 
 <td style="vertical-align:top;width:42.55pt;"> 索引范围扫描。常见于&lt;,&lt;=,&gt;,&gt;= between等操作符 </td> 
 <td style="vertical-align:top;width:63.75pt;"> 使用非唯一索引或者唯一索引的前缀扫描 </td> 
 <td style="vertical-align:top;width:63.8pt;"> 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用primary key 或者unique index作为关联条件 </td> 
 <td style="vertical-align:top;width:100.55pt;"> 单表中最多有个一匹配行，查询起来非常的迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理 </td> 
 <td style="vertical-align:top;width:57.9pt;"> MySQL不用访问表或者索引，直接就能够得到结果 </td> 
 </tr> 
 </tbody> 
</table>

有时候仅仅通过explain分析执行并不能很快的定位SQL的问题，这个时候我们还可以选择profile联合分析；

### 4.通过show profile分析SQL ###

show profiles;

show pifile for query; 可以看到执行过程中线程的每个状态和消耗的时间

例如：因为MyISAM表有表元数据的缓存（例如行数，即COUNT(\*)值），那么对于一个MyISAM表的COUNT(\*)是不需要消耗太多的资源的，而对于InnoDB来说，就没有这种元数据缓存，COUNT(\*)执行的及较慢。

从profile的结果看出来，InnoDB引擎的表在COUNT(\*)的时候经历了Sending data状态，存在访问数据的过程，而MyISAM引擎的表在executing之后直接就结束查询，完全不需要访问数据；

show profile命令可以在做SQL优化的时候帮助我们了解时间都耗费哪里去了，而MySQL 5.6则通过trace文件进一步向我们展示了优化器是如何选择执行计划的；

### 5.通过trace分析优化器如何选择执行计划 ###

Mysql 5.6提供了对SQL的跟踪trace，通过trace文件能够进一步的了解为什么优化器选择A执行计划，而不选择B执行计划，帮助我们更好的理解优化器的行为；

提示：建立索引可以避免全表扫描，大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更加的明显；

### 6.确定问题并采取相应的优化措施 ###

## 2.索引问题 ##

索引是数据库优化中最常用也是最重要的手段之一；

## 1.索引的存储分类 ##

索引在MySQL中的存储引擎中实现的，而不是在服务器层实现的。

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:133pt;"> 索引 </td> 
 <td style="vertical-align:top;width:293.1pt;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> B-Tree索引 </td> 
 <td style="vertical-align:top;width:293.1pt;"> 最常见的索引类型，大部分存储引擎都支持B树索引 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> HASH索引 </td> 
 <td style="vertical-align:top;width:293.1pt;"> 只有Memory引擎支持，使用场景简单 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> R-Tree索引（空间索引） </td> 
 <td style="vertical-align:top;width:293.1pt;"> 空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> Full-text索引（全文索引） </td> 
 <td style="vertical-align:top;width:293.1pt;"> 全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引 </td> 
 </tr> 
 </tbody> 
</table>

mysql目前不支持索引函数，但是能够对列的前面一部分进行索引，即只对一个字段的前几个字符进行索引，这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点：在排序Order by 和分组 Group by操作的时候无法使用。

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:106.5pt;"> 索引 </td> 
 <td style="background-color:#002060;width:106.5pt;"> MyISAM索引 </td> 
 <td style="background-color:#002060;width:106.55pt;"> InnoDB索引 </td> 
 <td style="background-color:#002060;width:106.55pt;"> Memory索引 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> B-Tree索引 </td> 
 <td style="vertical-align:top;width:106.5pt;"> 支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> HASH索引 </td> 
 <td style="vertical-align:top;width:106.5pt;"> 不支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 不支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> R-Tree索引 </td> 
 <td style="vertical-align:top;width:106.5pt;"> 支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 不支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:106.5pt;"> Full-text索引 </td> 
 <td style="vertical-align:top;width:106.5pt;"> 支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> MySQL5.6开始支持 </td> 
 <td style="vertical-align:top;width:106.55pt;"> 不支持 </td> 
 </tr> 
 </tbody> 
</table>

比较常用的是B-Tree索引和Hash索引，只有Memory/Heap引擎支持Hash索引。Hash索引适用于Key-Value查询，通过Hash索引要比通过B-Tree索引查询更加的迅速；Hash索引不适用范围查询，例如<,>,<=,>=这类操作。

如果使用Memory/Heap引擎并且where条件中不使用=进行索引列，那么不会用到索引。Memory/Heap引擎只有在=的条件下才会使用索引；

## 2.MySQL如何使用索引 ##

### 1.MySQL中能够使用索引的典型场景 ###

B-Tree索引是最常见的索引，构造类似于二叉树，能够根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行；

B-Tree索引中的B不代表二叉树（binary），而是代表平衡树（balanced）

**能够使用索引的典型场景**

1.  匹配全值（Match the full value）。对索引中的所有列都指定具体的值，即是对索引中的所有列都有等值匹配的条件。
2.  匹配值的范围查询（Match a range of values）。对索引的值能够进行范围查找。
3.  匹配最左前缀（Match a leftmost prefix）：仅仅使用索引中的最左边列进行查找；

最左匹配原则可以算的上是MySQL中B-Tree索引使用的首要原则；

1.  仅仅对索引进行查询（Index only query）：当查询的列都在索引中的字段中的时候，查询的效率更高；

直接访问索引所在的列，就可以获取到所有的需要的数据，此时就不需要通过索引回表，Using index也就平常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提升效率；

1.  匹配列前缀（Match a column prefix）：

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

1.  能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）；
2.  如果列名是索引，那么使用column\_name is null 就会使用索引（区别于Oracle）
3.  MySQL 5.6引入了Index Condition Pushdown（ICP）的特性，进一步优化了查询。

Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎；

优先使用索引列中查询条件较好的条件进行过滤，然后再使用索引中其他的条件提高查询性能；

### 2.存在索引但是不能使用索引的典型场景 ###

有些时候虽然有索引，但是并不被优化器选择使用；

**存在索引但是不能使用索引的典型场景**

1.  以%开头的LIKE查询不能够利用B-tree索引，执行计划中key的值为NULL表示没有使用索引：

因为B-Tree索引的结构，所以以%开头的查询很自然的就没法利用索引了，一般都推荐使用全文索引（Fulltext）来解决类似的全文索引问题。或者考虑使用InnoDB的表都是聚簇表的特点，采取一种轻量级别的解决方式：InnoDB表上二级索引获取到主键之后再回表去检索记录，这样也可以避免全表扫描表产生的大量IO请求；

1.  数据类型出现隐式转换的时候也不能使用索引，特别是当列的类型为字符串，那么一定记得在where条件中把字符串常量值使用引号引起来，否则即便这个列上有索引，

MySQL也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索。

例如：一个条件last\_name=1 此时就不会使用索引，但是这样last\_name=‘1’就会使用索引；

1.  复合索引的情况下，假如查询条件不包含索引列最左边的部分，即不满足最左原则Leftmost，是不会使用复合索引的；
2.  如果MySQL估计使用索引比全表扫描更慢，则不使用索引；

也就是在查询的时候，条件的筛选性越高就越容易使用索引，筛选性越低就越不容易会使用索引；

1.  用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会使用到；

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加I/O访问，一次全表扫描过滤条件就够了；

### 3.查看索引使用情况 ###

如果索引正在工作，Handler\_read\_key的值将会很高，这个值代表了一个行被索引值的次数，很低的值表明增加索引得到的性能改善不高，因为索引不经常使用；

mysql > show status like ‘Handler\_read%’;

## 3.两个简单实用的优化方法 ##

## 1.定期分析表和检查表 ##

【定期分析表】

ANALYZE\[LOCAL|NO\_WRITE\_TO\_BINLOG\] TABLE tb\_name

该语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

【定期检查表】

CHECK TABLE tb\_name

检查表的作用是检查一个或者多个表是否有错误。

该语句也可以检查视图是否有错误；

### 2.定期优化表 ###

OPTIMIZE \[LOCAL|NO\_WRITE\_TO\_BINLOG\] TABLE tb\_name

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（例如VARCHAR、BLOB或者TEXT列的表）进行了很多的修改，则应该使用OPTIMIZE TABLE命令来进行表的优化。

这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但是该语句只对MyISAM、BDB和InnoDB表起作用

注意：

ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的情况下执行相关的操作；

## 4.常用SQL的优化 ##

### 1.大批量插入数据 ###

【对于MyISAM引擎的表】

向MyISAM引擎**非空的表**（如果是空表则默认会先导入数据，再建立索引）中导入大量的数据的时候，先把唯一索引关闭，导入数据之后再打开索引，可以提高导入效率；

ALTER TABLE tb\_name DISABLE KEYS;

loading the data

ALTER TABLE tb\_name ENABLE KEYS;

【对于InnoDB类型的表导入数据优化】

（1）因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排序，可以有效地提高导入数据的效率；

当导入的文件数据是按照表的主键顺序存储的时候比不按主键顺序存储的时候导入快；

（2）在导入数据之前执行SET UNIQUE\_CHECK=0，关闭唯一性校验，在导入结束之后执行SET UNIQUE\_CHECK=1，恢复唯一性校验，可以提高导入的效率；

（3）如果应用使用自动提交的方式，建议再导入之前执行SET AUTOCOMMIT=0，关闭自动提交，导入之后在执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率；

### 2.优化INSERT语句 ###

1.  尽量使用insert into set values(1,2), values(1,3), values(1,4), values(1,5)…的方式一次性导入多条数据，缩短客户端与数据库之间的连接、关闭等消耗；
2.  如果从不同的客户端插入很多行，通过使用INSERT DELAYED语句得到更高的速度，DELAYED的含义是让INSERT语句马上执行，其数据都被放在内存的队列中，并没有真正的写入磁盘，这条语句比每条语句分别插入要快很多；

LOW\_PRIORITY则刚好相反，在所有其他用户表的读写完成之后，才进行插入；

1.  将索引文件和数据文件分在不同的磁盘上存放，（利用建表中的选项）
2.  如果进行批量插入，可以通过增加bulk\_insert\_buffer\_size变量值的方法来提高速度，但是这只能对MyISAM表有用；
3.  当从一个文本文件转载一个表的时候，使用LOAD DATA INFILE。这通常比使用很多的INSERT语句快20倍；

### 3.优化ORDER BY 语句 ###

MySQL中有两种排序方式：

1.  通过有序索引顺序扫描直接返回有序数据，不需要额外的排序，操作效率较高；
2.  通过对返回数据进行排序，也就是Filesort排序；

了解了MySQL的排序方式之后，优化：尽量减少额外的排序，通过索引直接返回有序的数据；WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者降序。否则肯定需要额外的排序操作，这样就会出现Filesort；

总结：

下列SQL可以使用索引：

SELECT \* FORM table\_name ORDER by key\_part1,key\_part2,…;

SELECT \* FORM table\_name WHERE key\_part1=1 ORDER BY key\_part1 DESC,key\_part2 DESC;

SELECT \* FORM table\_name ORDER BY key\_part1 DESC,key\_part2 DESC;

下列几种情况则不适用索引：

（1）order by的字段混合使用ASC和DESC；

SELECT \* FORM table\_name ORDER by key\_part1 DESC,key\_part2 ASC;

（2）用于查询行的关键字与ORDERBY中所使用的不相同；

SELECT \* FORM table\_name WHERE key2=constant ORDER BY key1;

（3）对不同的关键字使用ORDER BY；

SELECT \* FORM table\_name ORDER BY key1,key2;

【Filesort的优化】

某些情况下如果不能让Filesort消失，则需要加快Filesort的操作；

对于Filesort，MySQL有两种排序算法；

1.  两次扫描算法：
2.  一次扫描算法：

MySQL是根据比较系统变量max\_length\_for\_sort\_data的大小和Query语句取出的字段总大小来判断使用哪一种排序算法。

适当的增加sort\_buffer\_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；

尽量只是用必要的字段，SELECT 具体的字段名称，而不是SELECT \* 选择所有字段，这样可以减少排序区的使用，提高SQL性能；

### 4.优化 GROUP BY语句 ###

默认情况下，MySQL对所有GROUP BY col1,col2的字段进行排序，这与在查询中指定ORDER BY col1，col2类似，如果查询包括GROUP BY但是用户想要避免排序结构的消耗，则可以指定ORDER BY NULL禁止排序；

### 5.优化嵌套查询 ###

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接JOIN替代；

连接JOIN之所以更高效一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作；

### 6.MySQL如何优化OR条件 ###

对于含有OR的查询语句，如果想要利用索引，则OR之间的每个条件都必须使用到索引；如果没有索引，则应该考虑增加索引；

### 7.优化分页查询 ###

一般分页查询的时候，通过创建覆盖索引能够比较好的提高性能。

【第一种优化思路】

先在索引上完成排序分页的操作，最后根据主键关联回原表所需要的其他列内容。

【第二种优化思路】

把LIMILT查询转换为某个位置的查询，即把LIMIT m,n转换成LIMIT n的查询，只适合在排序字段不会出现重复记录的特定环境，如果排序字段中有大量的重复记录，则分页结果可能会丢失部分数据。

### 8.使用SQL提示 ###

1.  USE INDEX

在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引；

1.  INGNORE INDEX

忽略一个或者多个索引

1.  FORCE INDEX

强制MySQL使用一个特定的索引

## 5.常用SQL技巧 ##

### 1.正则表达式的使用 ###

MySQL利用REGEXP命令提供给用户扩展的正则表达式功能

### 2.巧用RAND()提取随机行 ###

ORDER BY RAND()

可以将返回结果随机排序

### 3.利用GROUP BY的WITH ROLLUP子句 ###

使用GROUP BY的WITH ROLLUP子句可以检索出更多的分组聚合信息，它不仅仅能够像一般的GROUP BY语句那样检索出各种的聚合信息，还能检索出本组类的整体聚合信息。

其实，WITH ROLLUP反应的是一种OLAP思想，也就是说这一个GROUP UP语句执行完之后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值；

注意：当使用ROLLUP的时候，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY之间是互相排斥的，此外，LIMIT用在ROLLUP后面；

### 4.用BIT GROUP FUNCTIONS 做统计 ###

可以结合GROUP BY语句和BIT\_AND、BIT\_OR函数完成统计工作；

### 5.数据库名、表名大小写问题 ###

UNIX环境中，表名和数据库名是大小写敏感的，但在windows环境中，MySQL数据库名和表名是大小写不敏感的；

列、索引、存储子程序和触发器在任何平台上对大小写不敏感。

### 6.使用外键需要注意的问题 ###

在mysql中InnoDB存储引擎支持对外部关键字约束条件的检查。而其他的类型存储引擎则可能不能起到外键的作用；

## 6.小结 ##

很多数据库性能都是由不适合的SQL语句造成的；

--------------------

# **优化数据库对象** #

## 1.优化表的数据类型 ##

MySQL中通过PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中的列的数据类型提出优化建议；

mysql > SELECT \* FROM table\_name PROCEDURE ANALYSE()\\G;

## 2.通过拆分提高表的访问效率 ##

### 1. 垂直拆分 ###

如果一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分，另外，垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询的时候就可以减少I/O的次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作；

### 2.水平拆分 ###

水平拆分：即根据一列或者多列的值把数据行放到两个独立的表中；

使用水平拆分的场景：

1.  表很大，分隔之后，可以降低在查询的时候需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度；
2.  表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而有些数据不常用；
3.  需要把数据存放在多个介质上；

例如：根据月份的账单表，超过一年的历史账单可以存储到单独的存储介质上；

说明：水平拆分会给应用增加复杂度，它通常在查询的时候需要多个表名，查询所有数据需要UNIION操作。所以，水平拆分要考虑数据量的增长速度，根据实际情况决定是否对表进行水平拆分；

## 3.逆规范化 ##

反规范的好处就是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定做反规范的时候，一定需要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法；

【常用的反规范技术】

1.  增加冗余列：

是指在多个表中具有相同的列，它常用来在查询的时候避免连接操作；

1.  增加派生列：

是指增加的列来自其他表中的数据，由其他的表中的数据经过计算产生。增加的派生列其作用是在查询的时候减少连接操作，避免使用集函数；

1.  重新组表：

如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能；

1.  分割表：

另外，逆规范化技术需要维护数据的完整性

【维护数据的完整性常用的方法】

1.  批处理维护：

批处理维护是指对复制列或者是派生列的修改积累到一定的程度之后运行一些批处理作业或者存储过程对复制或者派生列进行修改，这只能是对实时性不高的情况下使用；

1.  应用逻辑：

数据的完整性也可以在应用逻辑中实现，这要求必须在同一个事务中对所有涉及到的表进行增、删、该操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是需求变化的时候，不容易维护；

1.  触发器：

使用触发器，对数据的任何修改立即触发对复制列或者派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，容易维护，是解决这类问题的比较好的方法；

## 4.使用中间表提高统计查询速度 ##

对于数据量比较大的表，在其上面进行统计查询通常会效率很低，并且还需要考虑查询是否会影响到在线应用产生的负面影响。通常在这种情况下，使用中间表可以提高统计查询的效率——》需要把数据转移到中间表，然后在中间表上进行统计的操作，得到想要的结果；

注意：中间表和源表的结构相同，并且可以在中间表上建立适当的索引来方便查询；

【使用中间表作为统计查询的优点】

*  中间表复制源表部分数据，并且与源表相隔离，在中间表上做统计查询不会对在线应用产生负面影响；
 *  中间表上可以灵活的添加索引或者增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用；

--------------------

# 锁问题 #

锁是计算机协调多个进程或者线程并发访问某一个资源的机制。

## 1.MySQL 锁概述 ##

MySQL不同存储引擎支持不同的锁机制；

**MySQL3****中锁特性表**

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:142pt;"> 表级锁 </td> 
 <td style="background-color:#002060;width:142.05pt;"> 行级锁 </td> 
 <td style="background-color:#002060;width:142.05pt;"> 页面锁 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:142pt;"> 
 <ol> 
 <li>开销小，加锁快；</li> 
 <li>不会出现死锁；</li> 
 <li>锁定粒度大，发生冲突的概率最大，并发度最低；</li> 
 </ol></td> 
 <td style="vertical-align:top;width:142.05pt;"> 
 <ol> 
 <li>开销大，加锁慢；</li> 
 <li>会出现死锁；</li> 
 <li>锁定粒度最小，发生冲突的概率最低，并发度也高；</li> 
 </ol></td> 
 <td style="vertical-align:top;width:142.05pt;"> 
 <ol> 
 <li>开销和加锁时间界于表锁和行锁之间；</li> 
 <li>会出现死锁；</li> 
 <li>锁定粒度界于表锁和行锁之间，并发度一般；</li> 
 </ol></td> 
 </tr> 
 </tbody> 
</table>

很难说明哪种锁更好，只能就具体应用的特点来说哪种锁更加的合适！

仅仅从锁的角度来讲：

1.  表级锁更适合于以查询为主，只有少量按照索引条件更新数据的应用，如Web应用；
2.  行级锁则更适合用于有大量的按照索引条件并发更新少量不同的数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统；

*  MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
 *  BDB存储引擎采用的是页面锁（page-level locking），但是也支持表级锁；
 *  InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但是默认情况下采用的是行级锁；

最为常用的就是MySQL表锁和InnoDB行锁问题，由于BDB已经被InnoDB取代，即将成为历史，在此就不做进一步的讨论了；

## 2.MyISAM表锁 ##

### 1.查询表级锁争用的情况 ###

mysql > show status like ‘table%’;

如果查询出来的值Table\_locks\_waited的值比较高，则说明存在较严重的表级锁争用情况；

### 2.MySQL表级锁的锁模式 ###

MySQL的表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）；

对MyISAM表的读操作，不会阻塞其他用户对该表的读请求，但会阻塞对同一张表的写请求；

对MyISAM表的写操作，则会阻塞其他用户对同一张表的读和写操作；

MyISAM表的读操作和写操作之间，以及写操作之间是串行化的！

当一个线程获得到对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写都会等待，直到锁被释放为止；

### 3.如何加表锁 ###

MyISAM在执行查询语句SELECT之前，会自动的给设计的所有表加读锁，在执行更新操作（UPDATE 、DELETE 、INSERT）前，会自动的给涉及的表加写锁，这个过程不需要用户干预，不需要使用LOCK TABLE和UNLOCK TABLES 显式的加锁和解锁；

注意：

（1）LOCK TABLES 在后面加上local选项，其作用就是在满足MyISAM表并发插入的条件的情况下，允许用户在表尾并发的插入记录；

（2）如果一个session已经对某些表加锁，则该会话session只能访问显示加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。在自动加锁的情况下也是如此，MyISAM总是一次获取SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁的原因；

（3）当使用LOCK TABLES的时候，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会报错！

例如：如果对表student锁定：

mysql > lock table student read;

此时通过别名访问就会报错：

mysql > select s1.name,s2.age from student s1,student s2;

此时需要对别名分别进行锁定：

mysql > lock table student as s1 read ,student as s2 read;

### 4.并发插入（Concurrent Inserts） ###

MyISAM存储引擎中有一个系统变量concurrent\_insert，专门用来控制其并发插入的行为，其值可以分别为0、1或者2；

concurrent\_insert：

值为0：

不允许并发插入

值为1：

如果MyISAM表中没有空洞（即表中的中间没有删除的行），MyISAM允许在一个进行读表的同时，另一个进程从表尾部进行插入记录（但是不允许在该表中进行删除或者更新操作）。这也是MySQL的默认设置

值为2：

无论MyISAM表中有没有空洞，都允许在表尾部进行插入数据；

总结：

可以利用MyISAM存储引擎的并发插入特性来解决应用中对同一表查询和插入的锁争用。例如，可以将concurrent\_insert系统变量设置为2，总是允许并发插入；同时通过定期的在系统中空闲时候执行OPTIMIZE TABLE 语句来整理空间碎片，收回因为删除记录而产生的中间空洞。

### 5.MyISAM的锁调度 ###

MySQL认为写的请求比读的请求更重要，即使读请求先到达锁等待队列，写请求后到达，写锁也会插入到读锁之前！！！

这也是MyISAM表不太适合用于有大量更新操作和查询操作的原因，因为，大量的更新操作会照成查询操作很难获得读锁，从而可能永远阻塞！

可以通过一些设置来调节MyISAM的调度行为；

1.  t通过指定启动参数 low-priority-updates，使得MyISAM引擎默认给予读请求以优先的权利；
2.  通过执行命令SET LOW\_PRIORITY\_UPDATES=1，使该连接发出的更新请求优先级降低；

1.  通过指定INSERT、UPDATE、DELETE语句的LOW\_PRIORITY属性，降低该语句的优先级；

另外MySQL也提供了一种折中的方法来调节读写冲突，即给系统参数max\_write\_lock\_count设置一个合适的值，当一个表的读锁到达这个值得时候，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

一些需要长时间运行的查询操作，也会是写进程饿死，因此，应用中应该避免尽量出现长时间的查询操作，可以通过使用中间表等措施对复杂的SQL做一定的分解，使得每一步的查询都能在较短的时间内完成，从而减少锁的冲突。如果查询不可避免，应该尽量的在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行；

## 3.InnoDB锁问题 ##

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

[http://blog.csdn.net/xifeijian/article/details/20316775][http_blog.csdn.net_xifeijian_article_details_20316775]

### 1.背景知识 ###

### 1.事务（Transaction）及其ACID属性； ###

1.  原子性：
2.  一致性：
3.  隔离性：
4.  持久性

### 2.并发事务处理带来的问题 ###

1.  更新丢失（Lost Update）：

当两个或者多个事务选择同一行，然后基于最初选定的值更新该行的时候，最后的没更新覆盖掉了由其他事务所做的更新。

1.  脏读（Dirty Reads）：

一个用户读取到了另一个用户未提交的事务；

1.  不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据之后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或者某些记录已经被删除了，这种就是不可重复读；

1.  幻读（Phantom Reads）：

一个事务按相同的查询条件重新读取之前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”；

脏读、不可重复读和幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可以分为以下两种：

*  在读取数据之前，对其进行加锁，阻止其他事务对数据进行修改；
 *  不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或者是事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做多版本并发控制简称（MVCC或者MCC），也经常称为多版本数据库；

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#ffffff;width:86.6pt;"> 隔离级别 </td> 
 <td style="background-color:#ffffff;width:27.55pt;"> 脏读 </td> 
 <td style="background-color:#ffffff;width:63.8pt;"> 不可重复读 </td> 
 <td style="background-color:#ffffff;width:42.55pt;"> 幻像读 </td> 
 <td style="background-color:#ffffff;width:196.3pt;"> 读数据一致性 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#ffffff;width:86.6pt;"> read uncommitted </td> 
 <td style="background-color:#ffffff;width:27.55pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:63.8pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:42.55pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:196.3pt;"> 最低级别，只能保证不读取物理上损坏的数据； </td> 
 </tr> 
 <tr> 
 <td style="background-color:#ffffff;width:86.6pt;"> read committed </td> 
 <td style="background-color:#ffffff;width:27.55pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:63.8pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:42.55pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:196.3pt;"> 语句级别 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#ffffff;width:86.6pt;"> repeatable read </td> 
 <td style="background-color:#ffffff;width:27.55pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:63.8pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:42.55pt;"> 允许 </td> 
 <td style="background-color:#ffffff;width:196.3pt;"> 事务级别； </td> 
 </tr> 
 <tr> 
 <td style="background-color:#ffffff;width:86.6pt;"> serializable </td> 
 <td style="background-color:#ffffff;width:27.55pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:63.8pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:42.55pt;"> &nbsp; </td> 
 <td style="background-color:#ffffff;width:196.3pt;"> 最高级别，事务级 </td> 
 </tr> 
 </tbody> 
</table>

### 2.获取InnoDB行锁争用情况 ###

通过检查InnoDB\_row\_lock状态变量来分析系统上的行锁的争夺情况；

mysql > show status like ‘innodb\_row\_lock%’;

如果InnoDB\_row\_lock\_waits和InnoDB\_row\_lock\_time\_avg的值比较高，就会发现锁的争用情况比较严重，可以通过查询information\_schema数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因；

### 3.InnoDB的行锁模式以及加锁的方法 ###

InnoDB实现了以下两种类型的行锁：

1.  共享锁（S）：

允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；

1.  排他锁（X）：

允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁；

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有内部使用的意向锁（Intention Locks），下面两种意向锁都是表锁：

1.  意向共享锁（IS）：

事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须首先取得该表的IS锁；

1.  意向排他锁（IX）：

事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁；

**InnoDB****行锁模式兼容性列表**

<table style="margin-left:21pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> &nbsp;&nbsp;&nbsp;&nbsp; 请求锁模式 是否兼容 &nbsp; &nbsp; 当前锁模式 </td> 
 <td style="vertical-align:top;width:85.2pt;"> X </td> 
 <td style="vertical-align:top;width:85.2pt;"> IX </td> 
 <td style="vertical-align:top;width:85.25pt;"> S </td> 
 <td style="vertical-align:top;width:85.25pt;"> IS </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> X </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 冲突 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> IX </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.2pt;"> 兼容 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 兼容 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> S </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 兼容 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 兼容 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> IS </td> 
 <td style="vertical-align:top;width:85.2pt;"> 冲突 </td> 
 <td style="vertical-align:top;width:85.2pt;"> 兼容 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 兼容 </td> 
 <td style="vertical-align:top;width:85.25pt;"> 兼容 </td> 
 </tr> 
 </tbody> 
</table>

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之如果两者不兼容，该事物就需要等待锁释放；

意向锁是InnoDB自动加上的，不需要用户干预。

对于UPDATE DELETE INSERT 语句，InnoDB会自动给涉及到的数据集添加排他锁（X）；对于普通的SELECT语句，InnoDB不会加任何锁；

可以通过以下方式显式的给记录集加上共享锁或者排他锁：

共享锁（S）：

SELECT \* FROM table\_name WHERE … LOCK IN SHARE MODE

排他锁（X）：

SELECT \* FROM table\_name WHERE … FOR UPDATE

### 4.InnoDB行锁实现方式 ###

InnoDB 行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

InnoDB的行锁分为3种情景：

1.  Record lock：对索引项加锁
2.  Gap lock：对索引项之间的间隙、第一条记录前的间隙或者最后一条记录后的间隙加锁；
3.  Next-key lock：前两种组合，对记录或者其前面的间隙加锁；

InnoDB这种行锁的实现特点意味着，如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际的效果和表锁是一样的！！！

【特别注意InnoDB行锁的这一个特性】

否则可能导致大量的锁冲突；

1.  在不通过索引条件查询的时候，InnoDB会锁定表中所有的记录；
2.  由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
3.  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或者普通索引，InnoDB都会使用行锁对数据加锁；
4.  即便是在条件中使用了索引字段，但是否使用索引来检索数据是MySQL通过判断不同的执行计划的代价来决定的，如果MySQL任务全表扫描效率最高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有的记录加锁。

### 5.Next-Key锁 ###

当我们使用范围条件而不是相等条件检索数据，并请求共享或者排他锁的时候，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内单并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个间隙加锁，这种锁机制就是所谓的Next-Key锁；

需要注意：InnoDB除了通过范围条件加锁的时候使用Next-Key锁之外，如果还是用相等的条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！！！

### 6.恢复和复制的需要对InnoDB锁机制的影响 ###

MySQL 5.6支持3种日志格式：

1.  基于语句的日志格式SBL：
2.  基于行的日志格式RBL：
3.  混合格式

支持4中复制模式：

1.  基于SQL语句的复制SBR:
2.  基于行数据的复制RBR：
3.  混合复制模式：

对于安全的SQL采用基于SQL语句的复制模式，对于非安全的SQL语句采用基于行的复制模式；

1.  使用全局事务ID（GTIDs）的复制：

主要是解决主从自动同步的问题；

注意：对于INSERT … SELECT 和 CREATE TABLE … SELECT ... 语句，可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的性能问题，应该在程序中尽量避免使用；

实际上，MySQL将这种SQL称为不确定（non-determinstic）的SQL，属于不安全（Unsafe SQL），不推荐使用；

解决方案：

如果应用中一定需要这种SQL来实现业务逻辑，又不希望对源表的并发更新产生影响，可以采取以下方式：

1.  方式一：

将系统变量innodb\_locks\_unsafe\_for\_binlog的值设置为on，强制MySQL使用多版本数据一致性读，但是付出的代价是可能无法使用BINLOG正确的恢复或者复制数据，因此，不推荐使用这种方式；

1.  方式二：

通过使用select \* from table\_name1 … Into outfile 和 load data infile …语句组合来间接实现，采用这种方式不会对table\_name1表加锁；

1.  方式三：

使用基于行的BINLOG格式和基于行数据的复制；

### 7.InnoDB在不同隔离级别下的一致性读以及锁的差异 ###

小结：

对于大部分应用使用Read Committed隔离级别就够了，以减少锁争用的机率，对于一些确实是需要更高隔离级别的事务，可以通过在程序中执行SET  SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或者SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求；

### 8.什么时候使用表锁 ###

对于InnoDB表，绝大部分情况下都应该使用行级锁，因为事务和行锁往往是选择InnoDB的原因。但是在一些特殊的情况下，也可以考虑使用表级锁；

1.  事务需要更新大部分数据或者全部数据，表又比较大，如果使用默认的行锁，执行效率会变低；

1.  事务涉及到多个表，比较复杂，容易引起死锁，造成大量的事务回滚。

使用表锁需要注意：

1.  表锁不是由InnoDB存储引擎层管理的，而是由其上一层MySQL Server负责的，仅当autocommit=0、innodb\_table\_lock=1（默认设置）的时候，InnoDB层才能知道MySQL加的表锁，以及MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别出涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
2.  在使用LOCK  TABLES 对InnoDB表加锁是应该注意：需要将AUTOCOMMIT 设置为0，否则MySQL不会给表加锁；事务结束之前，不要使用UNLOCK TABLES 释放表锁，因为UNLOCK TABLES会隐含的提交事务；

COMMIT 或者ROLLBACK并不能释放用LOCK TABLES加的表锁，必须使用UNLOCK TABLES释放表锁；

### 9.关于死锁 ###

一般发生死锁之后，InnoDB会自动的检测到，并使得一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

但是在涉及到外部锁或者涉及到表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数**innodb\_lock\_wait\_timeout**来解决；

### 【程序中避免死锁的建议】 ###

1.  在应用中，如果不同的程序会并发存取多个表，应该尽量约定以相同的顺序来访问表这样可以大大降低产生死锁的机会；
2.  在程序以批量处理数据的时候，如果实现对数据排序，保证每个线程按照固定的顺序来处理记录，也可以大大降低出现死锁的可能；
3.  在事务中，如果需要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新的时候再申请排他锁，因为当用户申请排他锁的时候，其他事务可能已经获得了相同记录的锁，从而造成锁冲突，甚至死锁；
4.  前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT … FOR UPDATE加排他锁的时候，在没有复合记录的情况下，两个线程都会加锁成功，程序发现记录不存在，就试图插入一条记录，如果，两个线程都这么做，就会出现死锁；
5.  当隔离级别为READ COMMIT的时候，如果两个线程都先执行SELETCT FOR UPDATE，判断是否存在符合条件的记录，如果没有就插入记录。此时有一个线程能够插入成功，另一个线程会出现锁等待，当第一个线程提交之后，第二个线程会因为主键重复而发生错误，虽然这个线程出错了，但是却获取了一个排他锁，这时候，如果第三个线程又来申请排他锁，也会出现死锁；

--------------------

[http_blog.csdn.net_xifeijian_article_details_20316775]: http://blog.csdn.net/xifeijian/article/details/20316775