可以看到，rows列的值是3说“SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` =‘nihao‘” 语句扫描了表中的3条记录

 

然后在emp表加上索引

CREATE INDEX ix_emp_name ON emp(name)

 

现在再分析上面的查询语句，执行的EXPLAIN语句结果如下

结果显示，rows列的值为1。这表示这个查询语句只扫描了表中的一条记录，其他查询速度自然比扫描3条记录快。

而且possible_keys 和key的值都是ix_emp_name ，这说明查询时使用了ix_emp_name 索引

如果表中记录有100条、1000条、10000条优势就显现出来了

 

 

 （3）使用索引查询

 索引可以提高查询速度，但并不是使用带有索引的字段查询时，索引都会起作用。

下面的几种情况跟跟SQLSERVER一样，有可能用不到索引

（1）使用like关键字的查询语句

使用like关键字进行查询的时候，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引

才会起作用

 

使用like关键字，并且匹配字符串中含有“%”字符，EXPLAIN语句如下

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE ‘%x‘;

 

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE ‘x%‘;

name上有索引ix_emp_name

第一个查询type为ALL，表示要全表扫描

第二个查询TYPE为index，表示会扫描索引

 

like 关键字是否能利用上索引跟SQLSERVER是一样的

我之前写过一篇文章：

 

（2）使用多列索引的查询语句

mysql可以为多个字段创建索引。一个索引可以包括16个字段（跟SQLSERVER一样）对于多列索引，只有查询条件中使用了

这些字段中的第一个字段时，索引才会被使用，这个字段叫：

 

在表person中name，age字段创建多列索引，验证多列索引的情况

CREATE INDEX ix_person_name_age ON `person` (name,age)

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `Name` =‘suse‘

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

 

从第一条查询看出，WHERE `Name` =‘suse‘的记录有一条，扫描了一条记录并且使用了ix_person_name_age 索引

从第二条记录可以看出，rows列的值为4，说明共扫描了4条记录，并且key列值为NULL，说明EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

语句并没有使用索引。因为age字段是多列索引的第二个字段，只有查询条件中使用了name字段才会使用ix_person_name_age 索引

 

这个跟SQLSERVER是一样的，详细请看：

 

（3）使用OR关键字的查询语句

查询语句的查询条件中只有OR关键字，而且OR前后的两个条件中的列都是索引时，查询中才使用索引，否则，查询不使用索引

 

查询语句使用OR关键字的情况

我们再创建一个索引

CREATE INDEX ix_person_age ON `person` (age)

 

EXPLAIN SELECT Name,Age FROM `person`  WHERE `Name` =‘SUSE‘ OR `job`=‘SPORTMAN‘

EXPLAIN SELECT Name,Age FROM `person`  WHERE   `AGE` =2 OR `Name` =‘SUSE‘ 

大家要注意，这里跟刚才不一样，这次我们select的字段只有name和age，而不是select出全部字段

因为并没有在job这个字段上建立索引，所以第一个查询使用的是全表扫描

第二个查询因为name字段和age字段都有索引，那么mysql可以利用这两个索引的其中之一，这里是ix_person_name_age索引来查找记录

利用索引来查找记录会快很多

 

（4）优化子查询

mysql从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件

子查询可以一次性完成很多逻辑需要多个步骤才能完成的SQL操作。子查询虽然使查询语句灵活，但是执行效率不高。

执行子查询时，mysql需要为内层查询语句结果建立一个临时表。然后外层查询语句从临时表中查询记录

查询完毕后，再撤销临时表。因此，子查询的速度会受到一定影响，如果查询的数据量特别大，这种影响就会更大。

 

在mysql中，可以使用连接（join）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询快，如果查询中使用索引的话，性能会更好。

所以很多网上的文章都说尽量使用join来代替子查询，虽然网上也说mysql5.7对于子查询有很大的改进，但是如果不是使用mysql5.7还是需要注意的

 

如果系统中join语句特别多还需要注意修改my.ini或my.cnf文件中的join_buffer_size大小，预防性能问题

---

优化数据库结构

 

一个好的数据库设计方案对于数据库的性能常常起到事半功倍的效果。

数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面

 

（1）将字段很多的表拆分成多个表

有时候有些字段使用频率很低或者字段的数据类型比较大，那么可以考虑垂直拆分的方法，把不常用的字段和大字段拆分出去

 

（2）增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，

然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

 

（3）增加冗余字段

设计数据库表时应尽量遵循范式理论，尽可能减少冗余字段，但是现今存储硬件越来越便宜，有时候查询数据的时候需要join多个表

这样在高峰期间会影响查询的效率，我们需要反范式而为之，增加一些必要的冗余字段，以空间换时间

需要这样做会增加开发的工作量和维护量，但是如果能换来可观的性能提升，这样做也是值得的

 

（4）优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。

根据实际情况，可以分别进行优化

 

对于myisam表，常见优化方法如下：

1、禁用索引

对于非空表，插入记录时，mysql会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。

为了解决这个问题，可以在插入记录之前禁用索引，数据插入完毕后再开启索引

禁用索引语句如下：

ALTER TABLE table_name DISABLE KEYS ;

其中table_name是禁用索引的表的表名

重新开启索引语句如下：

ALTER TABLE table_name ENABLE KEYS ;

对于空表批量导入数据，则不需要进行此操作，因为myisam表是在导入数据之后才建立索引！

 

2、禁用唯一性检查

插入数据时，mysql会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。

为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启

禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS=0;

开启唯一性检查的语句如下：

SET UNIQUE_CHECKS=1;

 

3、使用批量插入

插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。

第一种情况

INSERT INTO emp(id,name) VALUES (1,‘suse‘);
INSERT INTO emp(id,name) VALUES (2,‘lily‘);
INSERT INTO emp(id,name) VALUES (3,‘tom‘);

 

第二种情况

INSERT INTO emp(id,name) VALUES (1,‘suse‘),(2,‘lily‘),(3,‘tom‘)

第二种情况要比第一种情况要快

 

4、使用LOAD DATA INFILE批量导入

当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快很多

 

 

对于INNODB引擎的表，常见的优化方法如下：

1、禁用唯一性检查

插入数据时，mysql会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。

为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启

禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS=0;

开启唯一性检查的语句如下：

SET UNIQUE_CHECKS=1;

 

2、禁用外键约束

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下：

SET FOREIGN_KEY_CHECKS=0;

恢复对外键的检查语句如下

SET FOREIGN_KEY_CHECKS=1;

 

3、禁止自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作

或显式指定事务

USE test;

START TRANSACTION;
INSERT INTO emp(name) VALUES(‘ming‘);
INSERT INTO emp(name) VALUES(‘lily‘);
commit;

 

（5）分析表、检查表、优化表、修复表和CHECKSUM表

mysql提供了分析表、检查表和优化表的语句

分析表主要是分析关键字的分布；

检查表主要是检查表是否存在错误；

优化表主要是消除删除或者更新造成的空间浪费

修复表主要对myisam表文件进行修复

CHECKSUM表主要对表数据传输前和传输后进行比较

 

1、分析表

mysql中提供了ANALYZE TABLE 语句分析表，ANALYZE TABLE 语句的基本语法如下

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，tbl_name为分析的表的表名

可以有一个或多个

使用ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分享期间，只能读取表的记录，不能更新和插入记录

ANALYZE TABLE 语句能分析INNODB、BDB和MYISAM类型的表

 

使用ANALYZE TABLE 来分析emp表，执行语句如下：

ANALYZE TABLE emp;

上面结果显示说明

table：表示分析的表名

op：表示执行的操作，analyze表示进行分析操作

msg_type：表示信息类型其值通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一

msg_text：显示信息

实际上分析表跟SQLSERVER里的更新统计信息是差不多的

主要就是为了索引的基数更加准确，从而使查询优化器能够更加准确的预估行数

 

emp表的记录行数是18

分析表之后，Cardinality 基数更加准确了

 

2、检查表

mysql中使用check table语句来检查表。check table语句能够检查innodb和myisam类型的表是否存在错误。

对于myisam类型的表，check table语句还会更新关键字统计数据。而且，check table也可以检查视图是否有错误，

比如在视图定义中被引用的表已不存在。

该语句基本语法如下：

CHECK TABLE TBL_NAME [,tbl_name]...[option]...
option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

其中，tbl_name是表名；option参数有5个取值分别是QUICK、FAST、MEDIUM、EXTENDED、CHANGED

各个选项的意思分别是

QUICK：不扫描行，不检查错误的连接

FAST：只检查没有被正确关闭的表

MEDIUM：扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点

EXTENDED：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是花的时间较长

CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表

 

option只对myisam表有效，对innodb表无效。check table语句在执行过程中也会给表加上只读锁。

 

3、优化表

mysql中使用OPTIMIZE TABLE语句来优化表。该语句对INNODB和MYISAM表都有效。但是，OPTIMIZE TABLE语句只能优化表中的

VARCHAR、BLOB、TEXT类型的字段

OPTIMIZE TABLE语句的基本语法如下：

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL和NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志

tbl_name是表名

通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。

OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

提示：一个表使用了TEXT或者BLOB这样的数据类型，如果已经删除了表的一大部分，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）

进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。在多数设置中，根本不需要运行OPTIMIZE TABLE。

即使对可变长度的行进行了大量更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定表进行OPTIMIZE TABLE

 

OPTIMIZE TABLE语句类似于SQLSERVER的重建索引和收缩数据文件的功能

 

4、修复表

mysql中使用Repair Table来修复myisam表，只对MyISAM和ARCHIVE类型的表有效。

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,tbl_name]...[option]...
option={QUICK|EXTENDED|USE_FRM}

选项的意思分别是：

QUICK：最快的选项，只修复索引树。
EXTENDED：最慢的选项，需要逐行重建索引。
USE_FRM：只有当MYI文件丢失时才使用这个选项，全面重建整个索引。

与Analyze Table一样，Repair Table也可以使用local来取消写入binlog。

 

 

5、Checksum 表

数据在传输时，可能会发生变化，也有可能因为其它原因损坏，为了保证数据的一致，我们可以计算checksum（校验值）。

使用MyISAM引擎的表会把checksum存储起来，称为live checksum，当数据发生变化时，checksum会相应变化。

语法如下：

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

quick：表示返回存储的checksum值

extended：表示重新计算checksum

如果没有指定选项，则默认使用extended。

 

Checksum 表主要用来对比在传输表数据之前和表数据之后，表的数据是否发生了变化，例如插入了数据或者删除了数据，或者有数据损坏

CHECKSUM值都会改变。

---

优化MYSQL服务器

水电费优化mysql服务器主要从两个方面入手，一方面是对硬件进行优化；另一方面是对mysql服务器的参数进行优化

 

1、优化服务器硬件

服务器的硬件性能直接决定着MYSQL数据库的性能。硬件的性能瓶颈直接决定MYSQL数据库的运行速度和效率。

优化服务器硬件的几种方法

（1）配置较大的内存。足够大的内存，是提高mysql数据库性能之一。内存速度比磁盘I/O快得多，可以通过增加系统缓冲区容量，使数据库

在内存停留时间更长，以减少磁盘I/O

（2）配置高速磁盘系统，以减少读盘等待时间，提高响应速度

（3）合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力

（4）配置多处理器，mysql是多线程的数据库，多处理器可同时执行多个线程

 

 

2、优化MYSQL的参数

通过优化MYSQL的参数可以提高资源利用率，从而达到提高MYSQL服务器的性能的目的。

MYSQL服务器的配置参数都在my.cnf或者my.ini文件的[mysqld]组中。

下面对几个对性能影响较大的参数进行介绍

我们先看一下与网络连接的性能配置项及对性能的影响。
● max_conecctions：整个 MySQL 允许的最大连接数；
这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量大于
max_conecctions 的情况下，由于 MySQL 的设置限制，那么应用中必然会产生连接请求的等待，
从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数设置的尽
可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值
● max_user_connections：每个用户允许的最大连接数；
上面的参数是限制了整个 MySQL 的连接数，而 max_user_connections 则是针对于单个用户的连
接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服
务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和
max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来
说，完全没有做太多的限制，可以尽量放开一些。
● net_buffer_length：网络包传输中，传输消息之前的 net buffer 初始化大小；
这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大
小，所以造成的影响主要是当我们的每次消息都很大的时候 MySQL 总是需要多次申请扩展该缓
冲区大小。系统默认大小为 16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非
常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到
8KB。
● max_allowed_packet：在网络传输中，一次传消息输量的最大值；
这个参数与 net_buffer_length 相对应，只不过是 net buffer 的最大值。当我们的消息传输量
大于 net_buffer_length 的设置时，MySQL 会自动增大 net buffer 的大小，直到缓冲区大小达
到 max_allowed_packet 所设置的值。系统默认值为 1MB，最大值是 1GB，必须设定为 1024 的倍
数，单位为字节。
● back_log：在 MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没
办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的
所有请求将存放在一个等待队列中，这个队列就是 MySQL 的连接请求队列。当我们的系统存在
瞬时的大量连接请求的时候，则应该注意 back_log 参数的设置。系统默认值为 50，最大可以设
置为 65535。当我们增大 back_log 的设置的时候，同时还需要主义 OS 级别对网络监听队列的限
制，因为如果 OS 的网络监听设置小于 MySQL 的 back_log 设置的时候，我们加大“back_log”设
置是没有意义的。
上面介绍了网络连接交互相关的主要优化设置，下面我们再来看看与每一个客户端连接想对应的连
接线程。
在 MySQL 中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个 Thread Cache 池，将
空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL 首先会
检查 Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，
才创建新的连接线程。在 MySQL 中与连接线程相关的系统参数及状态变量说明如下：
● thread_cache_size：Thread Cache 池中应该存放的连接线程数。
当系统最初启动的时候，并不会马上就创建 thread_cache_size 所设置数目的连接线程存放在
Thread Cache 池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当
存放的连接线程达到 thread_cache_size 值之后，MySQL 就不会再续保存用完的连接线程了。
如果我们的应用程序使用的短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据
库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让 MySQL 新建和销毁相应
的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了 Thread Cache 之后，由于
连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用
完 之 后 销 毁 ， 所 以 可 以 节 省 下 大 量 的 系 统 资 源 。 所 以 在 短 连 接 的 应 用 系 统 中 ，
thread_cache_size 的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求
数。
而如果我们使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，但
也并不是完全没有价值。因为应用程序即使是使用了长连接，也很难保证他们所管理的所有连
接都能处于很稳定的状态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应
用服务器数量较大的系统中，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应
用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作将
会更多。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大有
帮助的。只不过在长连接的环境中我们不需要将 thread_cache_size 参数设置太大，一般来说
可能 50 到 100 之间应该就可以了。
● thread_stack：每个连接线程被创建的时候，MySQL 给他分配的内存大小。
当 MySQL 创建一个新的连接线程的时候，是需要给他分配一定大小的内存堆栈空间，以便存放
客户端的请求 Query 以及自身的各种状态和处理信息。不过一般来说如果不是对 MySQL 的连接线
程处理机制十分熟悉的话，不应该轻易调整该参数的大小，使用系统的默认值（192KB）基本上
可以所有的普通应用环境。如果该值设置太小，会影响 MySQL 连接线程能够处理客户端请求的
Query 内容的大小，以及用户创建的 Procedures 和 Functions 等


计算出系统新建连接连接的 Thread
Cache 命中率，也就是通过 Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如
下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%
我们可以通过上面的这个运算公式计算一下上面环境中的 Thread Cache 命中率：Thread_Cache_Hit
= (127 - 12) / 127 * 100% = 90.55%
一般来说，当系统稳定运行一段时间之后，我们的 Thread Cache 命中率应该保持在 90%左右甚至更
高的比率才算正常。可以看出上面环境中的 Thread Cache 命中比率基本还算是正常的。
Table Cache 相关的优化
我们先来看一下 MySQL 打开表的相关机制。由于多线程的实现机制，为了尽可能的提高性能，在
MySQL 中每个线程都是独立的打开自己需要的表的文件描述符，而不是通过共享已经打开的表的文件描述
符的机制来实现。当然，针对于不同的存储引擎可能有不同的处理方式。如 MyISAM 表，每一个客户端线
程打开任何一个 MyISAM 表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程
共享同一个索引文件的描述符。对于 Innodb 的存储引擎，如果我们使用的是共享表空间来存储数据，那
么我们需要打开的文件描述符就比较少，而如果我们使用的是独享表空间方式来存储数据，则同样，由
于存储表数据的数据文件较多，则同样会打开很多的表文件描述符。除了数据库的实际表或者索引打开
以外，临时文件同样也需要使用文件描述符，同样会占用系统中 open_files_limit 的设置限额。
为了解决打开表文件描述符太过频繁的问题，MySQL 在系统中实现了一个 Table Cache 的机制，和前
面介绍的 Thread Cache 机制有点类似，主要就是 Cache 打开的所有表文件的描述符，当有新的请求的时
候不需要再重新打开，使用结束的时候也不用立即关闭。通过这样的方式来减少因为频繁打开关闭文件
描述符所带来的资源消耗。我们先看一看 Table Cache 相关的系统参数及状态变量。
在 MySQL 中我们通过 table_cache（从 MySQL5.1.3 开始改为 table_open_cache），来设置系统中为
我们 Cache 的打开表文件描述符的数量。通过 MySQL 官方手册中的介绍，我们设置 table_cache 大小的时
候应该通过 max_connections 参数计算得来，公式如下：
table_cache = max_connections * N；
其中 N 代表单个 Query 语句中所包含的最多 Table 的数量。但是我个人理解这样的计算其实并不是太
准确，分析如下：
首先，max_connections 是系统同时可以接受的最大连接数，但是这些连接并不一定都是 active 状
态的，也就是说可能里面有不少连接都是处于 Sleep 状态。而处于 Sleep 状态的连接是不可能打开任何
Table 的。
其次，这个 N 为执行 Query 中包含最多的 Table 的 Query 所包含的 Table 的个数也并不是太合适，因
为我们不能忽略索引文件的打开。虽然索引文件在各个连接线程之间是可以共享打开的连接描述符的，
但总还是需要的。而且，如果我 Query 中的每个表的访问都是通过现通过索引定位检索的，甚至可能还
是通过多个索引，那么该 Query 的执行所需要打开的文件描述符就更多了，可能是 N 的两倍甚至三倍。
最后，这个计算的公式只能计算出我们同一时刻需要打开的描述符的最大数量，而 table_cache 的
设置也不一定非得根据这个极限值来设定，因为 table_cache 所设定的只是 Cache 打开的描述符的数量的
大小，而不是最多能够打开的量的大小。


join_buffer_size ：当我们的 Join 是 ALL ， index ， rang 或者 index_merge 的时候使用的
Buffer；
实际上这种 Join 被称为 Full Join。实际上参与 Join 的每一个表都需要一个 Join Buffer，所以在
Join 出现的时候，至少是两个。Join Buffer 的设置在 MySQL 5.1.23 版本之前最大为 4GB，但是从
5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系统默认是 128KB。
● sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；
Sort Buffer 同样是针对单个 Thread 的，所以当多个 Thread 同时进行排序的时候，系统中就会出现
多个 Sort Buffer。一般我们可以通过增大 Sort Buffer 的大小来提高 ORDER BY 或者是 GROUP BY
的处理性能。系统默认大小为 2MB，最大限制和 Join Buffer 一样，在 MySQL 5.1.23 版本之前最大
为 4GB，从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4GB 的限制。
如果应用系统中很少有 Join 语句出现，则可以不用太在乎 join_buffer_size 参数的大小设置，但是
如果 Join 语句不是很少的话，个人建议可以适当增大 join_buffer_size 的设置到 1MB 左右，如果内存充
足甚至可以设置为 2MB。对于 sort_buffer_size 参数来说，一般设置为 2MB 到 4MB 之间可以满足大多数
应用的需求。当然，如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以
继续增大 sort_buffer_size 的设置。在这两个 Buffer 设置的时候，最需要注意的就是不要忘记是每个
Thread 都会创建自己独立的 Buffer，而不是整个系统共享的 Buffer，不要因为设置过大而造成系统内存
不足。

配置完参数之后，需要重启MYSQL服务才能生效

---

如何使用查询缓冲区

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句多、更新较少的情况。默认情况下查询缓冲区的大小为0，也就是不可用

可以修改query_cache_size以调整查询缓冲区大小；修改 query_cache_type以调整查询缓冲区的类型。

在my.ini中修改query_cache_size和query_cache_type的值如下所示

[mysqld]
query_cache_size=512M
query_cache_type=1

query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。

可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片

 

注意：开启查询缓冲区是有风险的，如果命中率不高，或者更新修改语句较多，都会使查询缓冲区失效，从而使命中率更加低

建议使用memcached等软件来做二级缓存，除非系统中修改语句较少，命中率较高，这样才会看到明显的性能提升

 

---

总结

本文阐述了MYSQL的性能优化面的内容，虽然网上对于MYSQL优化的资料很多

但是，MYSQL优化方面需要长期的进行研究探索才能找到适合于自己公司的业务系统最佳参数，否则只是使用网上的介绍只会人云亦云

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o 

我的MYSQL学习心得（十六）,布布扣,bubuko.com