<<MySQL性能调优与架构设计>>

一,基础篇
MySQL的基本架构

SQL Layer中的子模块

MySQL中实现访问控制的简单流程图

以客户端通过abc@localhost连接后请求如下Query我为例：
SELECT id,name FROM test.t4 where status = 'deleted';

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO    

   
profile

mysql> SHOW profile CPU,BLOCK IO  FOR query 4;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000036 | 0.000000 |   0.000000 |            0 |             0 | 
| checking query cache for query | 0.000066 | 0.000000 |   0.000000 |            0 |             0 | 
| Opening tables                 | 0.000022 | 0.000000 |   0.000000 |            0 |             0 | 
| System lock                    | 0.000011 | 0.000000 |   0.000000 |            0 |             0 | 
| Table lock                     | 0.000017 | 0.000000 |   0.000000 |            0 |             0 | 
| init                           | 0.000071 | 0.000000 |   0.000000 |            0 |             0 | 
| optimizing                     | 0.000012 | 0.000000 |   0.000000 |            0 |             0 | 
| statistics                     | 0.000019 | 0.000000 |   0.000000 |            0 |             0 | 
| preparing                      | 0.000015 | 0.000000 |   0.000000 |            0 |             0 | 
| executing                      | 0.000008 | 0.000000 |   0.000000 |            0 |             0 | 
| Sending data                   | 0.000691 | 0.000000 |   0.000000 |            8 |             0 | 
| end                            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 | 
| query end                      | 0.000009 | 0.000000 |   0.000000 |            0 |             0 | 
| freeing items                  | 0.000039 | 0.000000 |   0.000000 |            0 |             0 | 
| logging slow query             | 0.000007 | 0.000000 |   0.000000 |            0 |             0 | 
| cleaning up                    | 0.000008 | 0.000000 |   0.000000 |            0 |             0 | 
+--------------------------------+----------+----------+------------+--------------+---------------+

关于优化:
可以通过下面三句话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化.


   要想合理利用Innodb的行级锁定，做到扬长避短，我们必须做好以下工作：
a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
b) 合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
e) 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；
由于Innodb的行级锁定和事务性，所以肯定会产生死锁，下面是一些比较常用的减少死锁产生概率的的小建议，读者朋友可以根据各自的业务特点针对性的尝试：
a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
系统锁定争用情况查询
对于两种锁定级别，MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况，我们先看看MySQL实现的表级锁定的争用状态变量：
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 100   |
| Table_locks_waited    | 0     |
+-----------------------+-------+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；
两个状态值都是从系统启动后开始记录，没出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

对于Innodb所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 490578 |
| Innodb_row_lock_time_avg      | 37736  |
| Innodb_row_lock_time_max      | 121411 |
| Innodb_row_lock_waits         | 13     |
+-------------------------------+--------+
Innodb的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下：
Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是Innodb_row_lock_time_avg（等待平均时长），Innodb_row_lock_waits（等待总次数）以及Innodb_row_lock_time（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

在分析如何优化 MySQL Query 之前，我们需要先了解一下 Query 语句优化的基本思路和原则。一般来说，Query 语句的优化思路和原则主要提现在以下几个方面：
1. 优化更需要优化的Query；
2. 定位优化对象的性能瓶颈；
3. 明确的优化目标；
4. 从 Explain 入手；
5. 多使用profile
6. 永远用小结果集驱动大的结果集；
7. 尽可能在索引中完成排序；
8. 只取出自己需要的Columns；
9. 仅仅使用最有效的过滤条件；
10. 尽可能避免复杂的Join和子查询；

上面所列的几点信息，前面4点可以理解为 Query 优化的一个基本思路，后面部分则是我们优化中的基本原则。