MySQL优化

调优思路

• 硬件优化
• 磁盘io优化
• 操作系统的优化
• 纵向拆解、横向拆解
• 数据库设计与规划
• my.cnf参数的优化
• mysql查询优化
• Mysql存储引擎

硬件优化

CPU：64 位、高主频、高缓存，高并行处理能力

内存：大内存、主频高，尽量不要用SWAP

硬盘：建议是15000转或更大转数，使用RAID10、raid5磁盘阵列或SSD固态磁盘

网络：服务器标配的千兆网卡，建议是10G网卡，使用网卡bond技术。msyql服务器尽可能和使用它的web服务器在同一局域网内，尽量避免诸如防火墙策略等不必要的开销。

注：网卡bond是通过把多张网卡绑定为一个逻辑网卡，实现本地网卡的冗余，带宽扩容和负载均衡。

磁盘io规划，io相关的技术

raid 技术：raid10或raid5

建议是15000转或更大转数。有条件的可以使用SSD固态磁盘

swap 分区：最好使用raid0或SSD
磁盘分区：将数据库目录放到一个磁盘或分区。存储数据的硬盘或分区和系统所在的硬盘分开。

由于binlog日志频繁记录操作，开销非常大，需要把binlog日志放到单独的硬盘分区上。

操作系统的优化-内核、tcp、连接数量

设置tcp连接数量限制：

修改内核对tcp连接的有关限制

vim /etc/sysctl.conf
net.ipv4.ip_local_port_range=1024 65000
##这表明将系统对本地端口范围限制设置为1024~65000之间。请注意，本地端口范围的最小值必须大于或等于1024;而端口范围的最大值则应小于或等于65535.修改完后保存此文件
sysctl -p
##执行sysctl命令使修改生效

linux的最大并发允许的连接

modprobe nf_conntrack
 sysctl -p
 lsmod | grep nf_conntrack

查看当前服务器的并发连接数命令

cat /proc/sys/net/netfilter/nf_conntrack_count
##查看使用了多少连接数

cat /proc/sys/net/netfilter/nf_conntrack_max
##查看总数多少(需要确保nf_conntrack被加载)

echo 524288 > /proc/sys/net/netfilter/nf_conntrack_max
##临时设置增加并发数

/etc/sysctl.conf中加入：net.netfilter.nf_conntrack_max = 102400
sysctl -p
##永久设置增加并发数

linux的并发过程中，time_wait的数量太大，引起连接的挂起等待，需要在服务器增加以下配置
在文件/etc/sysctl.conf中添加配置
net.ipv4.tcp_syncookies=1
##防止少量的syn攻击,默认为0
net.ipv4.tcp_tw_reuse = 1 
net.ipv4.tcp_tw_recycle = 1 
net.ipv4.tcp_fin_timeout = 30 
执行 /sbin/sysctl -p 让参数生效

net.ipv4.tcp_tw_reuse = 1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭； 
net.ipv4.tcp_tw_recycle = 1 表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。 
net.ipv4.tcp_fin_timeout 修改系默认的 TIMEOUT 时间

系统打开文件的最大限制：

查看Linux系统用户最大打开文件的限制
ulimit -n

修改打开文件限制

[root@haha ~]# vim /etc/security/limits.conf
mysql soft nofile 102400
mysql hard nofile 102400
mysql soft nproc  102400
mysql hard nproc  102400
##其中mysql指定了要修改那个用户的打开文件数限制，可用“*”号表示修改所有用户的限制；soft或hard指定要修改软限制还是硬限制，102400则指定了想要修改的新的限制值，即最大打开文件数（请注意软限制要小于或等于硬限制）,（设置软限制后，是可以超过软限制的，但是不能超过硬限制。nofile指的是限制打开的文件数量。nproc是操作系统级别对每个用户创建的进程数的限制）

修改/etc/pam.d/login
vim /etc/pam.d/login
session    required     /usr/lib64/security/pam_limits.so
##这是告诉linux在用户完成系统登录后，应该调用pam_limits.so模块来设置系统对该用户可使用的各种资源数量的最大限制（包括用户可打开的最大文件数限制），而pam_limits.so模块就会从/etc/security/limits.conf文件中读取配置来设置这些限制值

查看linux系统对同时打开文件数的硬限制
sysctl -a | grep file-max
fs.file-max = 65535
##这表明这台linux系统最多允许同时打开（即包含所有用户打开文件数总和）65535个文件，是linux系统级硬限制，所有用户级的打开文件数限制都不会超过这个数值。通常这个系统级硬限制是linux系统在启动时根据系统硬件资源状况计算出来的最佳的最大同时打开文件数限制

修改file-max限制
vim /etc/sysctl.conf
fs.file-max = 1000000
执行sysctl -p生效
sysctl -p

禁用不必要启动的服务

文件系统调优

给数据库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。

可以考虑在挂载分区时启用 noatime 选项。
##noatime#不记录访问时间

vim /etc/fstab ##在挂载项中添加noatime选项
/dev/sdb /data/mysql/data xfs defaults,noatime 0 0

mount -p remount /data/mysql/data ##立即生效

最小化原则

安装系统最小化
开启程序服务最小化原则
登录最小化原则
权限最小化

纵向拆解、横向拆解

纵向拆解: 专机专用
例：现在公司一台服务器同时负责 web、ftp、数据库等多个角色。
纵向拆解后：数据库服务器专机专用，避免额外的服务可能导致的性能下降和不稳定性。

横向拆解:
主从同步、负载均衡、高可用性集群，当单个mysql数据库无法满足日益增加的需求时，可以考虑在数据库这个逻辑层面增加多台服务器，以达到稳定、高效的效果。

数据库设计于规划

应该尽量把字段设置为NOT NULL,字段避免null值出现，这样在将来执行查询的时候，数据库不用去比较NULL值。或者设置默认数字0代替null。

在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该尽量使用INT或MEDIUMINT而不是BIGIN来定义整型字段。

使用枚举或整数代替字符串类型
例如，对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

尽量使用TIMESTAMP而非DATETIME

单表不要有太多字段，建议在20以内

my.cnf参数的优化

优化总原则
给 mysql 的资源太少，则 mysql 施展不开：给 mysql 的资源太多，可能会拖累整个 OS。 40%资源给OS， 60%-70% 给mysql （内存和CPU）
mysql有超过100个可以调节的设置，要记住那么多基本是不可能的，只需要记住很少一部分就可以基本满足需求，还可以通过“SHOW STATUS”命令来查看mysql是否按照期望在运行

对查询进行缓存

查询的大致过程如下:
PHP发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果

启动查询缓存
vim /etc/my.cnf
query_cache_size=256M ##设置缓存为256M
query_cache_type=1 ##1表示开启，0表示关闭
systemctl restart mysqld ##修改后，重启服务

查看查询缓存

msql>show status like ‘qcache%’;

qcache%参数说明：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。
如果数目比较大，可以执行：
mysql> flush query cache;
#对缓存中的碎片进行整理，从而得到一个空闲块。

Qcache_free_memory：缓存中的空闲内存大小，通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是减少。

Qcache_hits：表示有多少次命中缓存。每次查询在缓存中命中时就增大。数字越大，缓存效果越理想。

Qcache_inserts 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

Qcache_lowmem_prunes：因内存不足删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返个数字最好长时间来看；如果返个数字在不断增长，就表示可能碎片非常严重，或者缓存内存很少。
如果Qcache_free_blocks比较大，说明碎片严重。 如果 free_memory 很小，说明缓存不够用了。

Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。

Qcache_queries_in_cache：在当前缓存的查询（和响应）的数量。

Qcache_total_blocks：缓存中块的数量。

使用mysql查询缓存

开启查询缓存后
mysql> select * from test;
mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | zz   |
|    2 | cc   |
|   11 | mm   |
|   12 | xx   |
+------+------+
4 rows in set (0.01 sec)

mysql> show status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 134198848 |
| Qcache_hits             | 1         |
| Qcache_inserts          | 1         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 2         |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

强制限制mysql资源配置
在mysqld中强制一些限制来确保系统负载不会导致资源耗尽的情况出现

vim /etc/my.cnf
max_connections=500
wait_timeout=10
max_connect_errors=100

max_connections：MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这是建立在服务器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
可执行mysql> show variables like ‘max_connections’;查看mysql的最大连接数设置。
注：数值过小会经常出现ERROR 1040: Too many connections错误

wait_timeout：指的是MySQL在关闭一个非交互的连接之前所要等待的秒数（空闲时间）。
可执行mysql> show global variables like ‘wait_timeout’;查看wait_timeout的值。

max_connect_errors：是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试连接失败的客户端，以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止该主机的连接请求。max_connect_errors的值与性能并无太大关系。
可执行mysql> show variables like ‘max_connect_errors’;查看该参数的设置值。
如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：mysql> FLUSH HOSTS;

mysql> show status like ‘max_used_connections’;
±---------------------±------+
| Variable_name | Value |
±---------------------±------+
| Max_used_connections | 1 |
±---------------------±------+
1 row in set (0.01 sec)

表高速缓存

数据库中的每个表存储在一个文件中，要读取文件的内容，你必须先打开文件，然后再读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/my.cnf中的 table_cache 指定

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+
1 row in set (0.01 sec)

vim /etc/my.cnf
table_open_cache=512  ##最多缓存512个表

systemctl restart msyqld

mysql> show global status like 'open%_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 33    |
| Opened_tables | 111   |
+---------------+-------+
2 rows in set (0.01 sec)

关键字（索引）缓冲区

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

vim /etc/my.cnf
key_buffer_size=512M

systemctl restart mysqld

mysql> show status like '%key_read%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 6     |
| Key_reads         | 3     |
+-------------------+-------+
2 rows in set (0.01 sec)

Key_reads 代表命中磁盘的请求个数，Key_read_requests 是总数， 命中磁盘的读请求数除以读请求总数就是不中比率

查询优化

启用慢查询日志

mysql> show global variables like '%low_query_log%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /data/mysql/data/localhost-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.01 sec)

slow_query_log： off关闭状态  on开启状态

slow_query_log_file：慢查询日志存放地点

long_query_time：选项来设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询

开启慢查询日志功能
方法	1：配置文件修改，永久生效
slow_query_log = 1  #开启慢查询日志
slow-query-log-file=/data/mysql/log/slow.log  #这个路径对 mysql 用户具有可写权限 
long_query_time=1  #查询超过1秒钟的语句记录下来
log-queries-not-using-indexes =1  #没有使用索引的查询
注：如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log
重启mysqld服务，使修改的参数生效

vim /etc/my.cnf
slow_query_log=1
slow-query-log-file=/data/mysql/log/slow.log
long_query_time=1
log-queries-not-using-indexes=1

systemctl restart msyqld


方法2：直接定义，重启失效
mysql>set global slow_query_log=1;  #开启慢查询日志
mysql>set global long_query_time=0.001; #更改时间

使用explain模拟执行

当只要一行数据时使用LIMIT 1

只取自己需要的column，避免使用SELECT *

添加索引（主键索引/唯一索引/普通索引/复合索引）

不做列运算：SELECT id from tablename where age + 1 = 10，任何对列的操作都将导致表扫描，查询时要尽可能将操作移至等号右边

sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。
OR改写成IN 

避免%xxx式查询

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

存储引擎

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

查看默认的存储引擎：
mysql> show variables like '%storage_engine%';
查看支持的存储引擎： 
mysql> show engines;

myisam

MyISAM存储引擎的表在数据库中，每一个表都被存储为三个以表名命名的物理文件。首先是任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI 文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。

Myisam存储引擎的特点：
表级锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁

数据库在读写过程中相互堵塞，在数据写入的过程中阻塞用户对数据的读取，在数据读取的过程中阻塞用户写入数据。

表级锁开销小，影响范围大，适合读多写少的表

不支持事务

不支持外键

不支持崩溃后的安全恢复

innodb

Innodb 存储引擎也和MyISAM 不太一样，虽然也有.frm文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。至于是每个表单独存放还是所有表存放在一起，完全由用户来决定。

Innodb存储引擎的特点：
行级锁，锁定行的开销要比锁定全表要大。影响范围小，适合写操作比较频繁的数据表。但是全表扫描仍然会是表级锁定

支持事物，支持四个事务隔离级别

支持外键

支持崩溃后的安全恢复

死锁

锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理

操作方面：
读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：
行级：提升并发性，锁本身开销大
表级：不利于并发性，锁本身开销小

死锁：
死锁的本质是一种僵持状态，之所以出现死锁，是因为多个线程对资源的强占，你要我的，我也要你的，两人堵在路上谁都不让，所以死锁了

开启死锁

[root@haha ~]# vim /etc/my.cnf
log-error=/data/mysql/log/mysqld.log
innodb_print_all_deadlocks=1