目录
7.2.1、启动查询缓存：vim /etc/my.cnf添加：
1、调优思路
- 硬件优化
- 磁盘io优化
- 操作系统的优化
- 纵向拆解、横向拆解
- 数据库设计与规划
- my.cnf参数的优化
- mysql查询优化
- mysql存储引擎
2、硬件优化
cpu：采用64位、高主频、高缓存，高并行处理能力
内存：大内存、主频高，尽量不要用swap
硬盘：建议15000转或更大转数，使用raid10、raid5磁盘阵列或ssd固态硬盘
网络：服务器标配的千兆网卡，建议是10G网卡，使用网卡bond技术。mysql服务器尽可能和使用它的web服务器在同一局域网内，尽量避免诸如防火墙策略等不必要的开销。
注：网卡bond是通过把多张网卡绑定位一个逻辑网卡，实现本地网卡的冗余，带宽扩容和负载均衡
3、磁盘io规划，io相关的技术
1、raid技术：raid10或raid5
2、建议15000转或更大转数。有条件的可以使用SSD固态磁盘
3、swap分区：最好使用raid0或SSD
4、磁盘分区：将数据库目录放到一个磁盘或分区。存储数据的硬盘或分区和系统所在的硬盘分开。
5、由于binlog日志频繁记录操作，开销非常大，需要把binlog日志放到单独的硬盘分区上。
4、操作系统的优化--内核、tcp连接数量
4.1、tcp连接数量、系统打开文件的最大限制
1、设置tcp连接数量限制
1）修改内核对tcp连接的有关限制：
内核编译时默认设置的本地端口号范围可能太小，因此需要修改本地端口范围限制。
第一步：修改/etc/sysctl.conf文件，在文件中添加如下行：
net.ipv4.ip_local_port_range=1024 65000
这表明系统对本地端口范围限制为1024~65000之间。请注意，本地端口范围的最小值必须大于或等于1024；而端口范围的最大值则应为65535。修改完后保存此文件。
第二部：执行sysctl命令使修改生效：
sysctl -p
2）Linux最大并发允许的连接：
查看当前服务器的并发连接数命令：
查看使用了多少连接数：
cat /proc/sys/net/ipv4/netfilter/ip_conntrack_count
新版本centos7查看用这个：
#cat /proc/sys/net/netfilter/nf_conntrack_count
查看总数多少：
#cat /proc/sys/net/ipv4/ip_conntrack_max
新版本（centos7）用这个：
#cat /proc/sys/net/netfilter/nf_conntrack_max
注：确保模块nf_conntrack_max被加载
临时设置增加并发数：
echo 524288 > /proc/sys/net/ipv4/ip_conntrack_max
新版本centos用这个：
echo 524288 > /proc/sys/net/netfilter/nf_conntrack_max
永久设置增加并发数：
/etc/sysctl.conf中加入：net.ipv4.ip_conntract_max=102400
新版本centos7用这个：
/etc/sysctl.conf中加入：net.netfilter.nf_conntrack_max=102400
执行sysctl -p使修改生效：
sysctl -p
3）linux的并发过程中，time_wait的数量太大，引起连接的挂起等待，需要在服务器增加以下配置：
编辑文件/etc/sysctl.conf，加入以下内容：
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=30
然后执行/sbin/sysctl -p让参数生效。
net.ipv4.tcp_syncookies=1表示开启SYN cookies。当出现SYN等待队列溢出时，启用cookies来处理，可防范少量SYN攻击，默认为0，表示关闭；
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时间
2、系统打开的最大限制：
用户级别：
（1）查看Linux系统用户最大打开的文件限制
#ulimit -n
（2）修改打开文件限制
#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则指定了想要修改的新的限制值，即最大打开文件数（请注意软限制要小于或等于硬限制）。
（3）修改/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系统级别：
查看Linux系统对同时打开文件数的硬限制
sysctl -a | grep file-max
fs.file-max=655535
这表明linux系统最多允许同时打开65535个文件（即包含所有用户打开文件数总和），是linux系统级硬限制，所有用户级的打开文件数限制都不会超过这个数值。通常这个系统级硬限制是linux系统在启动时根据系统硬件资源状况计算出来的最佳的最大同时打开文件数限制。
（1）、修改file-max限制
vim /etc/sysctl.conf
fs.file-max=1000000
执行sysctl -p生效
sysctl -p
4.2、禁用不必要启动的服务
4.3、文件系统调优
给数据库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。
可以考虑在挂载分区时启用noatime选项。notime不记录访问时间。
vim /etc/fstab 在挂载项中添加noatime选项就可以了。
UUID=46cb104c-e4dc-4f84-8afc-552f21279c65 /data xfs defaults,noatime 0 0
使设置立即生效，可运行：
mount -o remount /data/
通过mount命令查看：mount
这样以后系统在读此分区下的文件时，将不会再修改atime属性。
4.4、最小化yuanze
1）安装系统最小化
2）开启程序服务最小化原则
3）登陆最小化yuanze
4）权限最小化
5、纵向拆解、横向拆解
1、纵向拆解：专机专用
例如：现在公司一台服务器同时负责web、ftp、数据库等多个角色。
纵向拆解后：数据库服务器专机专用，避免额外的服务可能导致的性能下降和不稳定性。
2、横向拆解：总从同步、负载均衡、高可用性集群，当大哥mysql数据库无法满足日益增加的需求时，可以考虑在数据库这个逻辑层面增加多个服务器，以达到稳定、高效的效果。
6、数据库设计与规划
1、应该尽量把字段设置为NOT NULL，字段避免null值出现，这样奖励啊执行查询的时候，数据库不用去比较null值。或者设置数字0代替null。
2、在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255)，显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们尽量使用INT或MEDIUMINT而不是BIGINT来定义整数字段。
3、使用枚举或整数代替字符串
例如，对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为枚举类型。因为在MYSQL种，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度比文本类型快得多。这样，我们又可以提高数据库的性能。
4、尽量使用TIMESTAMP而非DATETIME
5、单表不要有太多字段，建议20以内
7、my.cnf内参数的优化
7.1、优化总原则：
给mysql的资源太少，则mysql施展不开；给mysql的资源太多，可能会拖累整个OS。
%40资源给OS，60%-70%给mysql（内存和CPU）
7.2、对查询进行缓存
大多数LAMP应用都严重依赖于数据库查询，查询的大致过程如下：
PHP发出查询请求-》数据库收到指令对查询语句进行分析-》确定如何查询-》从磁盘中加载信息-》返回结果
如果反复查询，就会反复执行这些。MySQL有一个特性称为查询缓存，它可以将查询的结果保存在内存缓存区中，今后对于同样的select语句，将直接从缓存区中读取结果。这样极大的提高性能，不过，问题时查询缓存在默认情况下是禁用的。
注：一个SQL拆线呢如果以select开头，那么MySQL服务器将尝试对其使用缓存。两个SQL语句，只要相差哪怕一个字符（例如大小写不一样；多一个空格等），那么这两个sql将使用不同的一个CACHE。
7.2.1、启动查询缓存：
vim /etc/my.cnf添加：
systemctl restart mysqld
7.2.2 、查询缓存
show status like 'qcache%';
7.2.3、使用mysql查询缓存
create database aa;
use aa;
create table test3(id int,name varchar(255));
insert into test3 values(1,'aaaa'),(2,'aaaa');
select * from test3;
show status like "qcache%"; #没有命中
再查询：
select * from test3;
select * from test3;
show status like "qcache%" #可以看见缓存命中了2次
7.3、强制限制mysql资源限制
您可以在mysqld中强制一些限制来确保系统负载不会导致资源耗尽的情况出现
vim /etc/my.cnf
参数：
测试：
vim /etc/my.cnf #在配置文件中添加以下内容：
验证：
show status like 'max_used_connections'; //当前有1个连接
在另一个客户端打开一个mysql连接，执行以下查询，可以看到有两个：
show status like 'max_used_connections';
7.4、表高速缓存
数据库中的每个表都存储在一个文件中，要读取文件的内容，你必须先打开文件，然后再读取。为了加快从文件中读取数据的过程，mysqld对这些打开文件进行了缓存，其最大数目由/etc/my.cnf中的table_cache指定
可以执行show variables like 'take_open_cache';查看该参数的设置值。
vim /etc/my.cnf
systemctl restart mysqld
show global status like 'open%_tables';
Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中table_open_cache值可能太小。
table_cache的值在2G内存以下的机器中的值默认从256到512个。对于有1G内存的机器，推荐值是128-256。
注：通常在设置table_open_cache参数的时候，在业务的高峰时期，检查open_Tables的值，如果open_Tables的值与table_open_cache的值相等，并且opened_tables的值在不断的增加，这个时候就需要对table_open_cache的值增加了。
7.5、关键字（索引）缓存区
key_buffer_size指定索引缓存区的大小，它决定索引处理的速度，尤其是索引读的速度。
执行show variables like 'key_buffer_size';查看该参数的设置值
vim /etc/my.cnf #在表中添加以下内容
systemctl restart mysqld
查看：
show status like '%key_read%';
Key_reads代表命中磁盘的请求个数，key_read_requests是总数，命中磁盘的读请求数除以读请求总数就是不中比率
7.6、总结：
mysql有超过100个可以调节的设置，要记住那么多基本是不可能的，但是幸运的是你只需要记住很少一部分你就可以基本满足你的需求了，我们还可以通过“show status”命令来查看mysql是否按照我们的期望来运行。
8、查询优化
1、启用mysql的慢查询日志
慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
查看慢查询日志的定义：
其中：
show_query_log：off关闭状态，on开启状态
show_query_log_file：慢查询日志存放地点
long_query_time：选项来设置一个时间值，时间以秒为单位，可以精确到微妙。如果查询时间超过了这个时间值（默认为10秒） ，这个查询语句将被记录到慢查询日志中，设置为0的话表示记录所有的查询。
开启慢查询日志功能：
方法1：通过配置文件my.cnf开启慢查询日志，在配置文件my.cnf中添加如下面的配置项：
show_query_log=1 #开启慢查询日志
show_query_log_file=/data/mysql/log/slow.log #这个路径对mysql用户具有可写权限
long_query_time=1 #查询超过1秒钟的语句记录下来
long-queries-not-using-indexes=1 #没有使用索引的查询
注：如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log
重启mysqld服务，使修改的参数生效。
测试：
create table test(id int,name varchar(20));
insert into test values(1,'man');
select * from test;
cat /data/mysql/log/slow.log
方法2：通过登陆mysql服务器直接定义，方式如下：
set global slow_query_log=1; #开启慢查询日志
set global long_query_time=0.001; #更改时间
2、使用EXPLAIN执行计划
可以通过在select语句前使用explain，来获取该查询语句的执行计划，而不是真正的执行该语句。
3、当只要一行数据时使用limit 1
4、只取自己需要的column，避免使用select *
5、添加索引（主键索引/唯一索引/普通索引/复合索引）
6、不做列运算：select id from tablename where age+1=10,任何对列的操作都将导致表扫描，查询时要尽可能将操作移至等号右边
7、sql语句尽可能简单，一条sql只能在一个cpu计算；大语句拆小鱼具，减少锁时间；一条大sql可以堵死整个库。
8、or改写成in
9、避免%xxx式查询
10、尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
9、存储引擎
1、mysiam
mysiam存储引擎的表在数据库中，每一个表都被存储为三个以表名命名的物理文件。首先是任何存储引擎都不可缺少存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYN）和索引数据（.MYI）。
mysiam存储引擎的特点：
表级锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
数据库在读写过程中相互堵塞，在数据写入的过程中阻塞用户对数据的读取，在数据读取的过程中阻塞用户写入数据。
表级锁开销小，影响范围大，适合读多写少的表
不支持事务
不支持外键
不支持崩溃后的安全恢复
2、innodb
innodb存储引擎也和mysiam不太一样，虽然也有.frm文件来存放表结构定义相关的元数据，但是表结构和索引数据是存放在一起的。至于是每个表单独存放还是所有表存放在一起，完全由用户来决定。
innodb存储引擎的特点：
行级锁，锁定行的开销要比锁定全表要大。影响范围小，适合写操作比较频繁的数据表，但是全表扫描仍然是表级锁定
支持事务，支持四个事务隔离级别
支持外键
支持崩溃后的安全恢复
总体来讲，mysiam适合select密集型的表，而innodb适合insert和update密集型的表
查看默认的存储引擎：
show variables like '%storage_engine%';
查看支持的存储引擎：
show engines;
9.1、详解死锁
锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端不能相互影响），通过加锁来处理
操作方面：
读锁：读操作增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征时阻塞其他客户端的读，写操作。
锁定粒度（范围）：
行级：提升并发性，锁本身开销大
表级：不利于并发性，锁本身开销小
死锁：
死锁的本质是一种僵持状态，之所以出现死锁，是因为多个线程对资源的强占，你要我的，我也要你的，两人堵在路上谁都不让，所以死锁了。
开启死锁，在my.cnf配置为
innodb_print_all_deadlocks=1
9.2、查看数据库服务的状态，登陆mysql
show status;看系统的状态
show engine innodb status \G #显示innodb存储引擎的值
show variables;看变量，在my.cnf配置文件里定义的变量值
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
