MySQL 缓冲机制详解
mysql缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的sql，服务器直接从缓存中取到结果，而不需要再去解析和执行sql。如果表更改了，那么使用这个表的所有缓冲查询将不再有效，查询缓存值的相关条目被清空。更改指的是表中任何数据或是结构的改变，包括INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表的使用MERGE表的查询。显然，这对于频繁更新的表，查询缓存是不适合的，而对于一些不常改变数据且有大量相同sql查询的表，查询缓存会节约很大的性能。
查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。
MYSQL 锁机制
MYSQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定、页级锁定和表级锁定。
1，行级锁定 (row-level)
行级锁定的最大特点就是锁定对象的颗粒度很小，它是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。弊端是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要的操作就更多，带来的消耗自然就更大。此外，行级锁定也最容易发生死锁。
2，表级锁定 (table-level)
和行级锁相反，表级别的锁定是MYSQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统处理成本最小，所以获取锁和释放锁的速度很快，由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。但是，表级锁带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度的性能大打折扣。
3，页级锁定 (page-level)
页级锁定是MYSQL中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。页级锁定的特点是锁定颗粒度介于行级锁定和表级锁定之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也介于上面两种。另外，也会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据需要消耗的内存数量越来越多。不过随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提高。
在MYSQL数据库中，使用表级锁定的主要是MYISAM，MEMORY,CSV等一些非事务性存储引擎，而使用行级锁定的主要是INNODB存储引擎和NDB CLUSTER存储引擎，页级锁定主要是BERKELEYDB存储引擎。
MYSQL - MYISAM 的优化
1，缩短锁定时间
(1) 尽量减少大的复杂Query，将复杂的Query分拆成几个小的Query分步进行；
(2) 尽可能地建立足够高效的索引，让数据检索更讯速；
(3) 尽量让MYISAM存储引擎的表只存放必要的信息， 控制字段类型；
(4) 利用合适机会优化 MYISAM表数据文件；
2，分离能并行的操作
MYISAM的存储引擎还有一个非常有用的特性，那就是Concurrent Insert(并发插入)的特性。
MYISAM存储引擎有一个控制是否打开 Concurrent Insert 功能的参数选项：concurrent_insert ，可以设置为0 , 1, 或者2 。说明如下：
(1) concurrent_insert = 2 ，无论MYISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行 Concurrent Insert 操作。
(2) concurrent_insert = 1，当MYISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从尾部进行Concurrent Insert。
(3) concurrent_insert = 0，无论MYISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许Concurrent Insert。
3，合理利用读写优先级
MYISAM 默认情况下是写优先级要大于读，所以可以根据系统环境的差异决定读与写的优先级。如果系统是一个以读为主，而且优先保证查询性能的话，可以通过设置系统参数选项 low_priority_updates = 1 ，将写的优先级设置为比读低，及告诉MYSQL尽量先处理读请求。如果系统需要有限保证数据写入的性能的话，则不需要设置 low_priority_updates 的参数。建议 concurrent_insert 设置为 1。
MYSQL - INNODB 的优化
INNODB 存储引擎实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些，但是在整体并发处理能力方面是要远远优于MYISAM的表级锁定的。当系统并发量较高的时候，INNODB的整体性能和MYISAM相比就会有明显的优势了。但是当使用不当的时候，可能会让INNODB的整体性能表现不仅不比MYISAM高，甚至可能会更差。优化MYISAM要做好以下的工作。
(1) 尽可能让所有的数据检索都通过索引来完成，从而避免INNODB因为无法通过索引加锁而升级为表级锁定。
(2) 合理设计索引，让INNODB在索引键上面加锁尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其它QUERY的执行。
(3) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙带来的影响而锁定了不该锁定的记录。
(4) 尽量控制事务大小，减少锁定的资源量和锁定时间长度。
系统锁定争用情况查询
执行语句 SHOW STATUS LIKE 'table%'; 查询表级锁定争用状态变量，说明如下：
table_locks_immediate : 产生表级锁定的次数；
table_locks_waited : 出现表级锁定争用而发生等待的次数；
如果 table_locks_waited 状态值比较高，那么说明系统中表级锁定争用的现象比较严重了。
执行语句 SHOW STATUS LIKE 'innodb_row_lock%'; 查询行级锁定争用状态变量，说明如下：
innodb_row_lock_current_waits : 当前正在等待锁定的数量；
innodb_row_lock_time : 从系统启动到现在锁定总时间长度；
innodb_row_lock_time_avg : 每次等待所花平均时间；
innodb_row_lock_time_max: 锁定最长的一次所花的时间；
innodb_row_lock_waits : 从系统启动到现在总共等待次数；
Query 语句优化
MySQL Query Qotimizer ，其主要的功能是通过计算分析系统中收集的各种统计信息，为客户端请求的Query给出最优的执行计划，也就是最优化的数据检索方式。Query语句的优化思路和原则主要体现在下面几个方向：
（1） 优化更需要优化的 QUERY；
高并发低消耗的QUERY对整个系统的影 响远比低并发高消耗的大。
（2） 定位优化对象的性能瓶颈；
IO 还是 CPU，可以借用 PROFILING 的功能找出瓶颈。
（3） 明确优化目标；
根据需求分析。
（4） 从EXPLAIN入手；
可以参考 MYSQL QUERY QOTIMIZER 提出的方案。
（5） 多使用PROFILE;
（6） 永远用小结果集驱动大的结果集；
优化 JOIN QUERY 的时候，最基本的原则就是 小结果集驱动大结果集，通过这个原则来减少嵌套循环中的循环次数，以减少IO总量及CPU运算次数。
（7） 尽可能在索引中完成排序；
（8） 只取自已需要的COLUMNS；
（9） 仅仅使用最有效的过滤条件；
（10） 尽可能避免复杂的 JOIN 和子查询；
前四点可以理解为QUERY优化的一个基本思路，后面部分则为优化的基本原则。
EXPLAIN 的信息解释
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：进行全文索引
查看数据库状态：
show full processlist;
show table status\G; #查看所有表信息
show full processlist;
show table status\G; #查看所有表信息
show create table table_name #查看table_name的建表信息
show columns from table_name #查看table_name的字段信息
show innodb status;
show innodb status;
show engines; #显示存储引擎的状态信息
show variables; #显示MySQL系统变量的值
show index from table_name; #显示table_name的索引信息
show open tables; #列举在表缓存中当前被打开的非TEMPORARY表
show databases; #查看有多少数据库在服务器中
SHOW TABLES; 显示当前库的所有表
表操作命令：
检查表 CHECK TABLE table_name;
修复表 REPAIR TABLE table_name;
优化表 OPTIMIZE TABLE table_name;
分析表 ANALYZE TABLE table_name;
清空表 TRUNCATE table_name;
删除表 DROP table_name;
重命名表 ALTER TABLE table1 RENAME table2;
改变一个字段的属性，同时重命名字段名 ALTER TABLE table_name CHANGE old_name new_name VARCHAR(80) NOT NULL;
不改变字段名的字段属性修改 ALTER TABLE table_name MODIFY field_name VARCHAR(100) NOT NULL;
在表开头添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL FIRST;
在表结尾添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL;
在某个字段之后添加一个字段 ALTER TABLE table_name ADD field_name VARCHAR(10) NOT NULL AFTER field_x;
删除表中的一个字段 ALTER TABLE table_name DROP field_name;
创建索引 CREATE INDEX index_name table_name (column_name);
删除索引 ALTER TABLE table_name DROP INDEX index_name;
创建表分区：
create table kanzher_rss_article_(
aid bigint not null,
rid bigint not null
)
partition by range(aid) (
partition p0 values less than (2000000),
partition p1 values less than (4000000),
partition p2 values less than (6000000),
partition p3 values less than (8000000),
partition p4 values less than (10000000),
partition p5 values less than (12000000),
partition p6 values less than (14000000),
partition p7 values less than (16000000),
partition p8 values less than (18000000),
partition p9 values less than (20000000)
);
aid bigint not null,
rid bigint not null
)
partition by range(aid) (
partition p0 values less than (2000000),
partition p1 values less than (4000000),
partition p2 values less than (6000000),
partition p3 values less than (8000000),
partition p4 values less than (10000000),
partition p5 values less than (12000000),
partition p6 values less than (14000000),
partition p7 values less than (16000000),
partition p8 values less than (18000000),
partition p9 values less than (20000000)
);
数据库同步设置
设置MASTER表
1，设置从表复制权限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xjjgooweb1234' WITH GRANT OPTION;
或者：GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO slave@192.168.1.2 IDENTIFIED BY 'password';
2，设置my.ini(my.cnf)：
[mysqld]
server-id = 1 #不能和Slave端设置重名
log-bin = proxy-bin #设置日志名，从该处同步到Slave端。
binlog-do-db = proxy #设置同步的数据库名
3，重启mysql Master数据库
4，查看 Master 数据库状态 show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| proxy-bin.000001 | 106 | proxy | |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| proxy-bin.000001 | 106 | proxy | |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
5，Master 数据库已经正常启动。
show binlog events; 查看复制日志
设置 SLAVE 表
6，设置my.ini(my.cnf)：
[mysqld]
server-id = 2
master-host = Master 服务器IP
master-user = user
master-password = password
master-port = 3306
master-connect-retry = 30 --断线或者开机后重试间隔时间
replicate-do-db = proxy --slave 同步 master 的数据库
7，删除日志目录下的master.info|relay-log.info文件，重启 mysql Slave 数据库
8，查看 slave 数据库状态
SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，表明 Slave 的 I/O 和 SQL 线程都在正常运行。
到此主从库搭建成功。
到此主从库搭建成功。
1.主辅库同步主要是通过二进制日志来实现同步的。
2.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用。因为读取的还是master.info文件里的信息。
2.在启动辅库的时候必须先把数据同步，并删除日志目录下的：master.info文件。因为master.info记录了上次要连接主库的信息，如果不删除，即使my.cnf里进行了修改，也不起作用。因为读取的还是master.info文件里的信息。
EXPLAIN
SET PROFILING = 1; 打开profiling 功能。
执行语句
查看profiles信息， SHOW PROFILES\G;
SHOW PROFILE CPU,BLOCK IO FOR QUERY 1;
