–query,指定SQL语句,可以定位到某个包含SQL的文件.

例如:

1. shell>mysqlslap –defaults-file=/u01/mysql1/mysql/my.cnf –concurrency=25,50 –iterations=1 –create-schema=test –query=/u01/test.sql -S/tmp/mysql1.sock 
2. Benchmark 
3. Average number of seconds to run all queries: 0.018 seconds 
4. Minimum number of seconds to run all queries: 0.018 seconds 
5. Maximum number of seconds to run all queries: 0.018 seconds 
6. Number of clients running queries: 25 
7. Average number of queries per client: 1 
8. Benchmark 
9. Average number of seconds to run all queries: 0.011 seconds 
10. Minimum number of seconds to run all queries: 0.011 seconds 
11. Maximum number of seconds to run all queries: 0.011 seconds 
12. Number of clients running queries: 50 
13. Average number of queries per client: 1 

MySQL 中索引使用相关的限制:

1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；

2. BLOB 和TEXT 类型的列只能创建前缀索引；

3. MySQL 目前不支持函数索引；

4. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；

5. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；

6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；

7. 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；

8. 使用非等值查询的时候MySQL 无法使用Hash 索引；

MySQL 目前可以通过两种算法来实现数据的排序操作:

1.取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在SortBuffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；

2.根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集，再按照顺序返回给客户端。

MySQL Explain 功能中给我们展示的各种信息的解释:

◆ ID：Query Optimizer 所选定的执行计划中查询的序列号；

◆ Select_type：所使用的查询类型，主要有以下这几种查询类型

◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；

◇ DEPENDENT UNION：子查询中的UNION，且为UNION 中从第二个SELECT 开始的后面所有SELECT，同样依赖于外部查询的结果集；

◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；

◇ SIMPLE：除子查询或者UNION 之外的其他查询；

◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；

◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；

◇ UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY

◇ UNION RESULT：UNION 中的合并结果；

◆ Table：显示这一步所访问的数据库中的表的名称；

◆ Type：告诉我们对表所使用的访问方式，主要包含如下集中类型；

◇ all：全表扫描

◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；

◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；

◇ fulltext：

◇ index：全索引扫描；

◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；

◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；

◇ rang：索引范围扫描；

◇ ref：Join 语句中被驱动表索引引用查询；

◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；

◇ system：系统表，表中只有一行数据；

◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；

◆ Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一项内容对于优化时候索引的调整非常重要；

◆ Key：MySQL Query Optimizer 从possible_keys 中所选择使用的索引；

◆ Key_len：被选中使用索引的索引键长度；

◆ Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的；

◆ Rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数；

◆ Extra：查询中每一步实现的额外细节信息，主要可能会是以下内容：

◇ Distinct：查找distinct 值，所以当mysql 找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；

◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；

◇ Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；

◇ No tables：Query 语句中使用FROM DUAL 或者不包含任何FROM 子句；

◇ Not exists：在某些左连接中MySQL Query Optimizer 所通过改变原有Query 的组成而使用的优化方法，可以部分减少数据访问次数；

◇ Range checked for each record (index map: N)：通过MySQL 官方手册的描述，当MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用range 或index_merge 访问方法来索取行。

◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候，MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的时候；

◇ Using filesort：当我们的Query 中包含ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。

◇ Using index：所需要的数据只需要在Index 即可全部获得而不需要再到表中取数据；

◇ Using index for group-by：数据访问和Using index 一样，所需数据只需要读取索引即可，而当Query 中使用了GROUP BY 或者DISTINCT 子句的时候，如果分组字段也在索引中，Extra 中的信息就会是Using index for group-by；

◇ Using temporary：当MySQL 在某些操作中必须使用临时表的时候，在Extra 信息中就会出现Using temporary 。主要常见于GROUP BY 和ORDER BY 等操作中。

◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where 信息；

◇ Using where with pushed condition：这是一个仅仅在NDBCluster 存储引擎中才会出现的信息,而且还需要通过打开Condition Pushdown 优化功能才可能会被使用,控制参数为engine_condition_pushdown.

MySQL 处理GROUP BY 的方式,有两种如下优化思路:

1.尽可能让MySQL 可以利用索引来完成GROUP BY 操作，当然最好是松散索引扫描的方式最佳。在系统允许的情况下，我们可以通过调整索引或者调整Query 这两种方式来达到目的；

2.当无法使用索引完成GROUP BY 的时候，由于要使用到临时表且需要filesort，所以我们必须要有足够的sort_buffer_size 来供MySQL 排序的时候使用，而且尽量不要进行大结果集的GROUPBY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy 到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降；

DINSTINCT 其实和 GROUP BY 原理类似,同样可以使用松散索引.

MySQL Schema 设计优化小记:

1. 适度冗余

2. 大字段垂直分拆

3. 大表水平分拆

时间字段类型:timestamp 占用4个字节，datetime,date占用8个字节，但是timestamp只能用在1970年以后的记录，datetime,date可用在1001年开始。

MySQL binlog日志优化方案:

Binlog 相关参数及优化策略,我们首先看看Binlog 的相关参数，通过执行如下命令可以获得关于Binlog 的相关参数,当然,其中也显示出了" innodb_locks_unsafe_for_binlog"这个Innodb 存储引擎特有的与Binlog 相关的参数:

1. mysql> show variables like '%binlog%'; 
2. +--------------------------------+------------+ 
3. | Variable_name | Value | 
4. +--------------------------------+------------+ 
5. | binlog_cache_size | 1048576 | 
6. | innodb_locks_unsafe_for_binlog | OFF | 
7. | max_binlog_cache_size | 4294967295 | 
8. | max_binlog_size | 1073741824 | 
9. | sync_binlog | 0 | 
10. +--------------------------------+------------+ 

"binlog_cache_size"：在事务过程中容纳二进制日志SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存，注意，是每个Client 都可以分配设置大小的binlog cache 空间。

如果读者朋友的系统中经常会出现多语句事务的华，可以尝试增加该值的大小，以获得更好的性能。当然，我们可以通过MySQL 的以下两个状态变量来判断当前的binlog_cache_size 的状况：Binlog_cache_use 和Binlog_cache_disk_use。"max_binlog_cache_size"：和"binlog_cache_size"相对应，但是所代表的是binlog 能够使用的最大cache 内存大小。当我们执行多语句事务的时候，max_binlog_cache_size 如果不够大的话，系统可能会报出" Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage"的错误。

"max_binlog_size"：Binlog 日志最大值，一般来说设置为512M 或者1G，但不能超过1G。该大小并不能非常严格控制Binlog 大小，尤其是当到达Binlog 比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL 都记录进入当前日志，直到该事务结束。

这一点和Oracle 的Redo 日志有点不一样，因为Oracle 的Redo 日志所记录的是数据文件的物理位置的变化，而且里面同时记录了Redo 和Undo 相关的信息，所以同一个事务是否在一个日志中对Oracle 来说并不关键。而MySQL 在Binlog 中所记录的是数据库逻辑变化信息，MySQL 称之为Event，实际上就是带来数据库变化的DML 之类的Query 语句。"sync_binlog"：这个参数是对于MySQL 系统来说是至关重要的，他不仅影响到Binlog 对MySQL 所带来的性能损耗，而且还影响到MySQL 中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下：

● sync_binlog=0，当事务提交之后，MySQL 不做fsync 之类的磁盘同步指令刷新binlog_cache 中的信息到磁盘，而让Filesystem 自行决定什么时候来做同步，或者cache 满了之后才同步到磁盘。

● sync_binlog=n，当每进行n 次事务提交之后，MySQL 将进行一次fsync 之类的磁盘同步指令来将binlog_cache 中的数据强制写入磁盘。在MySQL 中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache 中的所有binlog 信息都会被丢失。而当设置为"1"的时候，是最安全但是性能损耗最大的设置。因为当设置为1 的时候，即使系统Crash，也最多丢失binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，"sync_binlog"设置为0 和设置为1 的系统写入性能差距可能高达5 倍甚至更多。

MySQL QueryCache 负面影响:

a) Query 语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache 之后，每条SELECT类型的Query 在到达MySQL 之后，都需要进行一个hash 运算然后查找是否存在该Query 的Cache，虽然这个hash 运算的算法可能已经非常高效了，hash 查找的过程也已经足够的优化了，对于一条Query 来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千条Query 的时候，我们就不能对产生的CPU 的消耗完全忽视了。

b) Query Cache 的失效问题。如果我们的表变更比较频繁，则会造成Query Cache 的失效率非常高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我们每次缓存到Query Cache 中的Cache 数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同Query 进来之后无法使用到之前的Cache。

c) Query Cache 中缓存的是Result Set ，而不是数据页，也就是说，存在同一条记录被Cache 多次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定QueryCache 的大小啊。是的，我们确实可以限定Query Cache 的大小，但是这样，Query Cache 就很容易造成因为内存不足而被换出，造成命中率的下降。

在短连接的应用系统中，thread_cache_size 的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求数。

通过系统设置和当前状态的分析，我们可以发现，thread_cache_size 的设置已经足够了，甚至还远大于系统的需要。所以我们可以适当减少thread_cache_size 的设置，比如设置为8 或者16。根据Connections 和Threads_created 这两个系统状态值，我们还可以计算出系统新建连接连接的ThreadCache 命中率，也就是通过Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如下：

Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%

一般来说，当系统稳定运行一段时间之后，我们的Thread Cache 命中率应该保持在90%左右甚至更高的比率才算正常。可以看出上面环境中的Thread Cache 命中比率基本还算是正常的。

如何查看MySQL打开Table的数量:

1. mysql> show status like 'open_tables'; 
2. +---------------+-------+ 
3. | Variable_name | Value | 
4. +---------------+-------+ 
5. | Open_tables | 6 | 
6. +---------------+-------+ 

MySQL buffer注意事项:

join_buffer_size 和 sort_buffer_size 是针对的每个线程的buffer大小而言的，而不是整个系统共享的Buffer.

假设是一台单独给MySQL 使用的主机,物理内存总大小为8G，MySQL 最大连接数为500，同时还使用了MyISAM 存储引擎,这时候我们的整体内存该如何分配呢？

内存分配为如下几大部分:

a)系统使用,假设预留800M；

b)线程独享,约2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB),组成大概如下:

1. sort_buffer_size：1MB 
2. join_buffer_size：1MB 
3. read_buffer_size：1MB 
4. read_rnd_buffer_size：512KB 
5. thread_statck：512KB 

c)MyISAM Key Cache,假设大概为1.5GB；

d)Innodb Buffer Pool 最大可用量:8GB - 800MB - 2GB - 1.5GB = 3.7GB；

Tags: MySQL入门教程 MySQL学习笔记

分享到：