MySQL性能调优之监控方法：
1. set profiling=1 开启性能监控，此命令在某些版本的mysql中无法使用
2. 然后执行SQL
3. show profiless，查看系统执行SQL的时间
4. show profile cpu, block io for query 数字ID （此ID为show profiles中的性能输出日志序号）
MySQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。
在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory，CSV 等一些非事务性存储引擎，而使用行级锁定的主要是Innodb 存储引擎和NDB Cluster 存储引擎，页级锁定主要是BerkeleyDB 存储引擎的锁定方式。
MyISAM读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：
1. 除了READ_HIGH_PRIORITY 的读锁定之外，Pending write-lock queue 中的WRITE 写锁定能够阻塞所有其他的读锁定；
2. READ_HIGH_PRIORITY 读锁定的请求能够阻塞所有Pending write-lock queue 中的写锁定；
3. 除了WRITE 写锁定之外，Pending write-lock queue 中的其他任何写锁定都比读锁定的优先级低。
MyISAM写锁定出现在Current write-lock queue 之后，会阻塞除了以下情况下的所有其他锁定的请求：
1. 在某些存储引擎的允许下，可以允许一个WRITE_CONCURRENT_INSERT 写锁定请求
2. 写锁定为WRITE_ALLOW_WRITE 的时候，允许除了WRITE_ONLY 之外的所有读和写锁定请求
3. 写锁定为WRITE_ALLOW_READ 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
4. 写锁定为WRITE_DELAYED 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
5. 写锁定为WRITE_CONCURRENT_INSERT 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
Innodb 的行级锁定注意事项：
a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定；
b) 合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query 的执行；
c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e) 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL 因为实现事务隔离级别所带来的附加成本；
如何查看MyISAM中表级锁定信息：
答：show status like '%table_locks%'
table_locks_immediate:显示的数字就是锁定的次数。
table_locks_waited:显示的数字是出现表级锁定争用而发生等待的次数
如何查看Innodb中行级锁定信息：
答： show status like '%Innodb_rows%'
Innodb 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
● Innodb_row_lock_current_waits：当前正在等待锁定的数量；
● Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
● Innodb_row_lock_time_avg：每次等待所花平均时间；
● Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
● Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
mysqlslap是一个mysql官方提供的压力测试工具。以下是比较重要的参数：
–defaults-file，配置文件存放位置
–concurrency，并发数
–engines，引擎
–iterations，迭代的实验次数
–socket，socket文件位置
自动测试：
–auto-generate-sql，自动产生测试SQL
–auto-generate-sql-load-type，测试SQL的类型。类型有mixed，update，write，key，read。
–number-of-queries，执行的SQL总数量
–number-int-cols，表内int列的数量
–number-char-cols，表内char列的数量
例如：
shell>mysqlslap –defaults-file=/u01/mysql1/mysql/my.cnf –concurrency=50,100 –iterations=1 –number-int-cols=4 –auto-generate-sql –auto-generate-sql-load-type=write –engine=myisam –number-of-queries=200 -S/tmp/mysql1.sock
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.016 seconds
Minimum number of seconds to run all queries: 0.016 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.265 seconds
Minimum number of seconds to run all queries: 0.265 seconds
Maximum number of seconds to run all queries: 0.265 seconds
Number of clients running queries: 100
Average number of queries per client: 2
指定数据库的测试：
–create-schema，指定数据库名称
–query，指定SQL语句，可以定位到某个包含SQL的文件
例如：
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
Benchmark
Average number of seconds to run all queries: 0.018 seconds
Minimum number of seconds to run all queries: 0.018 seconds
Maximum number of seconds to run all queries: 0.018 seconds
Number of clients running queries: 25
Average number of queries per client: 1
Benchmark
Average number of seconds to run all queries: 0.011 seconds
Minimum number of seconds to run all queries: 0.011 seconds
Maximum number of seconds to run all queries: 0.011 seconds
Number of clients running queries: 50
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 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。
要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：
◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置；
◆ 在使用GROUP BY 的同时，只能使用MAX 和MIN 这两个聚合函数；
◆ 如果引用到了该索引中GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；
为什么松散索引扫描的效率会很高？
答：因为在没有WHERE 子句，也就是必须经过全索引扫描的时候， 松散索引扫描需要读取的键值数量与分组的组数量一样多，也就是说比实际存在的键值数目要少很多。而在WHERE 子句包含范围判断式或者等值表达式的时候， 松散索引扫描查找满足范围条件的每个组的第1 个关键字，并且再次读取尽可能最少数量的关键字。
什么是紧凑索引？
答：紧凑索引扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有满足条件的索引键，然后再根据读取的数据来完成GROUP BY 操作得到相应结果。