分享未来 ^Internet _Technology

今天遇到一个很诡异的事情，编译安装完mysql用mysql_install_db初始化数据库怎么也创建不了var目录，也没有任何报错信息。于是寻求google找到了答案，原来是系统中已经安装了mysql的rpm包，想想上午安装了perl，是不是因为关联的关系也附带安装了mysql的rpm包。导致后续安装mysql源码包后出现了问题。解决方案如下：

[code="plain"]
rpm -qa | grep mysql
[/code]

先查找是否有安装mysql，如果有提示那么卸载mysql

[code="plain"]
rpm -e --nodeps mysql
[/code]

继续进入mysql bin目录执行mysql_install_db ，注意用户权限的问题就行了

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