mysql优化

纵向拆解、横向拆解
1、纵向拆解: 专机专用
2、横向拆解:
主从同步、负载均衡、高可用性集群，当单个mysql数据库无法满足日益增加的需求时，可以考虑在数据库这个逻辑层面增加多台服务器，以达到稳定、高效的效果。
my.cnf 参数的优化
优化总原则：
给 mysql 的资源太少，则 mysql 施展不开：给 mysql 的资源太多，可能会拖累整个 OS。
40%资源给OS， 60%-70% 给mysql （内存和CPU）
对查询进行缓存
大多数LAMP应用都严重依赖于数据库查询，查询的大致过程如下:
PHP发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果
启动查询缓存：
vim /etc/my.cnf 添加：
query_cache_size = 256M #设置缓存为256M
query_cache_type=1 #1是开启mysql查询缓存，0是不缓存
注：通常设置为32-512Mb。设置完之后最好跟踪一段时间，查看是否运行良好。
systemctl restart mysqld 重启
查看：查询缓存
show status like ‘qcache%’;
参数说明：

1. Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。
   如果数目比较大，可以执行：
   mysql> flush query cache;
   #对缓存中的碎片进行整理，从而得到一个空闲块。
2. Qcache_free_memory：缓存中的空闲内存大小，通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是减少。
3. Qcache_hits：表示有多少次命中缓存。每次查询在缓存中命中时就增大。数字越大，缓存效果越理想。
4. Qcache_inserts 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
5. Qcache_lowmem_prunes：因内存不足删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返个数字最好长时间来看；如果返个数字在不断增长，就表示可能碎片非常严重，或者缓存内存很少。
   如果Qcache_free_blocks比较大，说明碎片严重。 如果 free_memory 很小，说明缓存不够用了。
6. Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。
7. Qcache_queries_in_cache：在当前缓存的查询（和响应）的数量。
8. Qcache_total_blocks：缓存中块的数量。

使用mysql查询缓存
create database aa;
use aa;
create table test(id int,name varchar(255));
insert into test values(1,‘aaaa’),(2,‘aaaa’);
select * from test;
show status like “qcache%”; 没有命中
再查询：
select * from test3;
select * from test3;
show status like “qcache%”;可以看见缓存了2次
强制限制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 variables like ‘wait_timeout’;查看wait_timeout的值。
max_connect_errors：是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试连接失败的客户端，以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止该主机的连接请求。max_connect_errors的值与性能并无太大关系。
可执行mysql> show variables like ‘max_connect_errors’;查看该参数的设置值。
如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：mysql> FLUSH HOSTS;
测试：
show status like ‘max_used_connections’;
再另一个客户端打开一个mysql连接，执行一下查询，可以看到有两个：
show status like ‘max_used_connections’;
表高速缓存：
数据库中的每个表存储在一个文件中，要读取文件的内容，你必须先打开文件，然后再读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/my.cnf中的 table_cache 指定
可以执行mysql> show variables like ‘table_open_cache’;查看该参数的设置值。
vim /etc/my.cnf #在表中添加以下内容
table_open_cache=512 最多缓存512个表
重启，查看
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的值增加了。
关键字（索引）缓冲区
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
执行mysql> show variables like ‘key_buffer_size’;查看该参数的设置值。
vim /etc/my.cnf //在表中添加以下内容
key_buffer_size=512M #关键字缓冲区大小
重启，查看
show status like ‘%key_read%’;
Key_reads 代表命中磁盘的请求个数，Key_read_requests 是总数， 命中磁盘的读请求数除以读请求总数就是不中比率.
总结：
mysql有超过100个可以调节的设置，要记住那么多基本是不可能的，但是幸运的是你只需要记住很少一部分你就可以基本满足你的需求了，我们还可以通过“SHOW STATUS”命令来查看mysql是否按照我们的期望在运行。

查询优化
1、启用mysql的慢查询日志
2、查看慢查询日志的定义：
show global variables like ‘%slow_query_log%’;
slow_query_log： off关闭状态  on开启状态
slow_query_log_file：慢查询日志存放地点
开启慢查询日志功能：
方法1：通过配置文件my.cnf开启慢查询日志，在配置文件my.cnf中添加下面的配置项：
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服务，使修改的参数生效
测试：
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服务器直接定义，方式如下：
mysql>set global slow_query_log=1;  #开启慢查询日志
mysql>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子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描