分享未来 ^Internet _Technology

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 相关的参数：
mysql> show variables like '%binlog%';
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| binlog_cache_size | 1048576 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| sync_binlog | 0 |
+--------------------------------+------------+
“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的数量：

mysql> show status like 'open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 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)，组成大概如下:
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c) MyISAM Key Cache，假设大概为1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；

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：系统启动后到现在总共等待的次数；

慢速SQL：执行时间超过给定时间范围的查询就称为慢速查询。

在MySQL中如何记录慢速SQL？

答：可以在my.cnf中设置如下信息：

[code="plain"]
[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don't use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes
[/code]

这三个设置的意思是可以记录执行时间超过5 秒和没有使用索引的查询.

MySQL中日志分类：

1. error log mysql错误记录日志

2. bin log 记录修改数据时候产生的quer并用二进制的方式进行存储

3. mysql-bin.index 记录是记录所有Binary Log 的绝对路径，保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件。

4. slow query log 记录慢速SQL，是一个简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户。

5. innodb redo log 记录Innodb 所做的所有物理变更和事务信息，保证事务安全性。

SQL架构可分为：SQL 层 与 Storage Engine层

SQL Layer 中包含了多个子模块：
1、初始化模块
顾名思议，初始化模块就是在MySQL Server 启动的时候，对整个系统做各种各样的初始化操作，比如各种buffer，cache 结构的初始化和内存空间的申请，各种系统变量的初始化设定，各种存储引擎的初始化设置，等等。
2、核心API
核心API 模块主要是为了提供一些需要非常高效的底层操作功能的优化实现，包括各种底层数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输出，以及最重要的内存管理部分。核心API 模块的所有源代码都集中在mysys 和strings文件夹下面，有兴趣的读者可以研究研究。
3、网络交互模块
底层网络交互模块抽象出底层网络交互所使用的接口api，实现底层网络数据的接收与发送，以方便其他各个模块调用，以及对这一部分的维护。所有源码都在vio 文件夹下面。
4、Client & Server 交互协议模块
任何C/S 结构的软件系统，都肯定会有自己独有的信息交互协议，MySQL 也不例外。MySQL的Client & Server 交互协议模块部分，实现了客户端与MySQL 交互过程中的所有协议。当然这些协议都是建立在现有的OS 和网络协议之上的，如TCP/IP 以及Unix Socket。
5、用户模块
用户模块所实现的功能，主要包括用户的登录连接权限控制和用户的授权管理。他就像MySQL 的大门守卫一样，决定是否给来访者“开门”。
6、访问控制模块

造访客人进门了就可以想干嘛就干嘛么？为了安全考虑，肯定不能如此随意。这时候就需要访问控制模块实时监控客人的每一个动作，给不同的客人以不同的权限。访问控制模块实现的功能就是根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问。用户模块和访问控制模块两者结合起来，组成了MySQL 整个数据库系统的权限安全管理的功能。
7、连接管理、连接线程和线程管理
连接管理模块负责监听对MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责MySQL Server 与客户端的通信，接受客户端的命令请求，传递Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的cache 等。
8、Query 解析和转发模块
在MySQL 中我们习惯将所有Client 端发送给Server 端的命令都称为query，在MySQLServer 里面，连接线程接收到客户端的一个Query 后，会直接将该query 传递给专门负责将各种Query 进行分类然后转发给各个对应的处理模块，这个模块就是query 解析和转发模块。其主要工作就是将query 语句进行语义和语法的分析，然后按照不同的操作类型进行分类，然后做出针对性的转发。
9、Query Cache 模块
Query Cache 模块在MySQL 中是一个非常重要的模块，他的主要功能是将客户端提交给MySQL 的Select 类query 请求的返回结果集cache 到内存中，与该query 的一个hash 值做一个对应。该Query 所取数据的基表发生任何数据的变化之后，MySQL 会自动使该query 的Cache 失效。在读写比例非常高的应用系统中，Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
10、Query 优化器模块
Query 优化器，顾名思义，就是优化客户端请求的query，根据客户端请求的query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个query 语句的结果。
11、表变更管理模块
表变更管理模块主要是负责完成一些DML 和DDL 的query，如：update，delte，insert，create table，alter table 等语句的处理。
12、表维护模块
表的状态检查，错误修复，以及优化和分析等工作都是表维护模块需要做的事情。
13、系统状态管理模块

系统状态管理模块负责在客户端请求系统状态的时候，将各种状态数据返回给用户，像DBA 常用的各种show status 命令，show variables 命令等，所得到的结果都是由这个模块返回的。
14、表管理器
这个模块从名字上看来很容易和上面的表变更和表维护模块相混淆，但是其功能与变更及维护模块却完全不同。大家知道，每一个MySQL 的表都有一个表的定义文件，也就是*.frm文件。表管理器的工作主要就是维护这些文件，以及一个cache，该cache 中的主要内容是各个表的结构信息。此外它还维护table 级别的锁管理。
15、日志记录模块
日志记录模块主要负责整个系统级别的逻辑层的日志的记录，包括error log，binarylog，slow query log 等。
16、复制模块
复制模块又可分为Master 模块和Slave 模块两部分， Master 模块主要负责在Replication 环境中读取Master 端的binary 日志，以及与Slave 端的I/O 线程交互等工作。Slave 模块比Master 模块所要做的事情稍多一些，在系统中主要体现在两个线程上面。一个是负责从Master 请求和接受binary 日志，并写入本地relay log 中的I/O 线程。另外一个是负责从relay log 中读取相关日志事件，然后解析成可以在Slave 端正确执行并得到和Master 端完全相同的结果的命令并再交给Slave 执行的SQL 线程。
17、存储引擎接口模块
存储引擎接口模块可以说是MySQL 数据库中最有特色的一点了。目前各种数据库产品
中，基本上只有MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天MySQL 可插拔存储引擎的特色。

本文记述leeon在搭建MySQL Replication中的一些知识点和技巧。

1. 在Master机和Slave机中分别设置server-id为1和2，如果不设置server-id，那么slave会无法启动。因为默认my.cnf的server-id均为1。

2. 在Master机my.cnf中开启log-bin。

3. 为Master机中的MySQL开启一个用户权限，此用户为Slave机的MySQL从Master中获取数据用。

[code="sql"]
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
[/code]

4. 执行

[code="sql"]
FLUSH TABLES WITH READ LOCK；
[/code]

清空所有表和块写入语句。

5. 然后备份Master机中所有的数据，可以用mysqldump或者直接打包MySQL中的二进制文件。并将数据导入到Slave机的MySQL中。

6. 在Master机中执行：

[code="sql"]
SHOW MASTER STATUS;
[/code]

记录下File 和 Position。

7.  关闭Master机的MySQL防止复制的数据和Slave的不一致。

8. 在Slave机中执行如下SQL：

[code="sql"] CHANGE MASTER TO MASTER_HOST='master_host_name', MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position; [/code]

master_host_name 为Master的Host

replication_user_name 为第三步中设置的用户名

replication_password 为第三步中设置的密码

recoded_log_file_name 为第六步中记录的file

recorded_log_position 为第六步中记录的position

9. 在Slave的MySQL中执行“START SLAVE”来启动slave服务。利用“SHOW SLAVE STATUS”观察“Slave_IO_Running”和“Slave_SQL_Running”是否均为YES，如果不为Yes则说明启动失败，要观察error提示。

10. 执行“show processlist”来观察MySQL中的master-slave服务是否正常。

正常情况下提示：

Master：
|  3 | rep  | 192.168.127.132:43625 | NULL | Binlog Dump | 3135 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |

Slave：
|  1 | system user |           | NULL | Connect |   3108 | Waiting for master to send event                                      | NULL             |
|  2 | system user |           | NULL | Connect | -25725 | Has read all relay log; waiting for the slave I/O thread to update it | NULL       

11. unlock tables。解锁Master机的MySQL。

----------------------------------------------------------------------------------------

补充：

Master 主要负责在Replication 环境中读取Master 端的binary 日志，以及与Slave 端的I/O 线程交互等工作。Slave 比Master 所要做的事情稍多一些，在系统中主要体现在两个线程上面。一个是负责从Master 请求和接受binary 日志，并写入本地relay log 中的I/O 线程。另外一个是负责从relay log中读取相关日志事件，然后解析成可以在Slave 端正确执行并得到和Master 端完全相同的结果的命令并再交给Slave 执行的SQL 线程。