MySql优化经验分享
一条sql的具体执行过程
连接
我们怎么查看MySQL当前有多少个连接？
可以用show status命令，模糊匹配Thread，
Show global status like "Thread%"
show global variables like 'wait timeout';—非交互式超时时间，如JDBC程序
show global variables like 'interactive timeout';"交互式超时时间，如数据库工具默认都是28800秒，8小时。
既然连接消耗资源，MySQL服务允许的最大连接数（也就是并发数）默认是多少呢？在5.7版本中默认是151个，最大可以设置成100000
MySQL中的参数（变量）分为session和global级别，分别是在当前会话中生效和 全局生效,但是并不是每个参数都有两个级别，比如max_connections就只有全局级别。当没有带参数的时候，默认是session级别，包括查询和修改。比如修改了一个参数以后，在本窗口査询已经生效，但是其他窗口不生效：所以，如果只是临时修改，建议修改session级别。如果需要在其他会话中生效，必须显式地加上global参数。
缓存
缓存没有生效，为什么？MySQL的缓存默认是关闭的。show variables like “query_cache%”!;
主要是因为MySQL自带的缓存的应用场景有限，第一个是它要求SQL语句必须一 模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。第二个是表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对 于有大量数据更新的应用，也不适合。
词法解析与语法解析
词法分析
就是把一个完整的SQL语句打碎成一个个的单词。
select name from user where id = 1;
它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。
语法分析
语法分析会对SQL做一些语法检查，比如单引号有没有闭合,然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我 们把它叫做解析树
预处理器
实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检査生成的解析树，解决解析器无法解析的语义。
它会检査表和列名是否存在，检査名字和别名，保证没有歧义。预处理之后得到一个新的解析树。
査询优化器
査询优化器的模块(Optimizer)。
査询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划，MySQL里面使用的是基于开销(cost)的优化器，那种执行计划开销最小，就用哪种。可以使用这个命令査看査询的开销：
show status like 'Last_query_cost';
error 1040: Too many connections的错误。这个是超
过了服务端设置的最大并发连接数。
优化的思路
1、从服务端来说，我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以:
(1)增加可用连接数，修改max connections的大小:
show variables like max_connections; ・・修改最大连接数，当有多个应用连接的时候
(2)或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时
间都是28800秒，8小时，我们可以把这个值调小。
show global variables like ‘wait_timeout’
引入连接池，实现对连接重用
2 缓存层面
使用缓存 redis
集群
主从复制
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。
也就是说：
- 从库会生成两个线程,一个I/O线程,一个SQL线程;
- I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
- 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
- SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;
修改配置
修改配置的工作一般由专业的DBA完成。也有一些工具可以给出
推荐值。
慢日志查询
1 打开慢日志开关
因为开启慢查询日志是有代价的（跟binlog—样），所以它默认
是关闭的：
show variables like ‘slow_query’
除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默
认是10秒。如果改成0秒的话就是记录所有的SQL。
show variables like ‘%long_query%’;
set @@global.slow_query_log=l;・.1开启，0关闭，重启后失效
set @@global.long_query_time=3;-默认10秒，另开一个窗口后才会查到最新值 show variables like ,%long_queiy%1; show variables like '%slow_query%‘;
修改配置文件my.cnfo
以下配置定义了慢査询日志的开关、慢査询的时间、日志文件的存放路径。
slowquerylog = ON long_qu eiy_time=2 slowqueiylogfile =/var/lib/mysql/localhost-slow.log
模拟慢査询：
慢日志分析 1、日志内容
less /var/lib/mysql/localhost-slow.log
MySQL提供了 mysqldumpslow的工具，在MySQL的bin目录下。
Mysqldumpslow – help
show engine存储引擎运行信息
show engine用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息;
事务的锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息。
