mysql数据库优化
1. Mysql优化介绍
1.1 sql优化
a. sql优化分析
b. 索引优化
c. 常用sql优化
d. 常用优化技巧
1.2 优化数据库对象
a. 优化表的数据类型
b. 表拆分
c. 逆规范式（不遵循三范式）
d. 使用中间表（一般用于大数据或者统计分析时）
1.3 优化Mysql Server
a. Mysql 内在管理优化
b. log机制及优化
c. 调整Mysql并发相关参数
1.4 应用优化
a. 数据库连接池
b. 使用缓存减少压力
c. 负载均衡建立集群
d. 主主同步 中从复制（读写分离）
2 Mysql优化问题分析定位
2.1分析SQL执行频率
mysql命令行：show status "Com_select";
查看Com_select,Com_select,Com_update,Com_delete,Innodb_rows-read,Innodb_rows_inserted,Innodb_rows_updated,Innodb_deleted状态
通过查看状态检测出该服务器是以读写哪个为主，就可以从不同方面
进行优化
2.2 定位执行效率低SQL
2.2.1 慢查询日志定位 查询结束记录：
慢查询日志文件路径
og-slow-queries
超过多少秒的查询就写入日志
long_query_time
打开my.cnf配置文件，加入以下代码：
log-slow-queries = /tmp/mysql-slow.log //log文件路径
long_query_time = 2 //代表语句超过两秒的就记录
保存退出，重启MySQL即可。
[root@lizhong tmp]# tail -f /tmp/mysql_slow.log
Time: 120815 23:22:11
User@Host: root[root] @ localhost []
Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
SET timestamp=1294388531;
select count(*) from blog;
第一行：执行时间
第二行：执行用户
第三行（重要）：
Query_time SQL执行的时间,越长则越慢
Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent 查询返回的行数
Rows_examined 查询检查的行数
2.2.2 show processlist
查看当前正在进行的线程，包括线程状态，是否锁表（status为Locked ）更多状态解析
3 索引优化
3.1 索引存储分类
B-TREE索引：最常见的索引类型，大部分都支持
HASH索引：只有Memory引擎支持，使用场景简单
R-TREE索引：空间索引是MykSAM的一个特殊索引类型，主要用于地理空间数据类型
Full-text索引：全文索引，MylSAM的一个特殊索引，Innodb从5.6开始支持
3.2 索引的创建与删除
- 添加PRIMARY KEY(主键索引)
mysql>alter table 'table_name' add primary key ('columm');- 添加UNIQUE(唯一索引)
mysql>alter table 'table_name' add unique('column');- 添加index(普通索引)
mysql>alter table 'table_name' add index index_name ('column');- 添加FULLTEXT(全文索引)
mysql>alter table 'table_name' add fulltext ('column');- 添加多列索引
mysql>alter table 'table_name' add index index_name ('column1'，'column2','column3');
3.3 索引查看
- show index from table_name;
- show keys from table_name;
3.4 Mysql中使用索引的情况
- 匹配全值（select name,age from test where age=15）
- 匹配值范围查询（select name,age from test where age>15）
- 匹配最左前缀
- 仅仅对索引进行查询（字段而不用*）
- 匹配列前缀(select name from test where name like "d%"
)- 部分精确+部分范围(select name,age from test where age>15 and id=1)
3.5 不能使用索引的情况
- 以‘%’开头的like查询
- 数据类型出现隐式转换（int型数据却加上引号转成字符串）
- 复合索引查询条件不包含最左侧部分
- 即使使用索引但比全表扫面还慢（数据中都是已A开头，却selec>t name from test like 'A%';）
- 用or分割开的条件
- 高秒杀的时候
3.5 查询索引的使用情况
show status like 'Handler_reader%';
其中Handler_read_rnd_next越大，说明查询中大部分都没使用索引
4 常用sql语句优化
4.1 定期优化表
使用Optimize table table_name（不宜经常使用，看操作次数，一周左右就可以）来合并表空间碎片（对MYISAM,DBD,INNODB有效）
默认情况下直接对innodb引擎的数据表使用，可能会显示[Table does not support optimize,doing recreate + annalyze install]的提示信息，这时候我们需要用mysql --skip-new或者mysql --safe-mode命令来重启MySQL,以便于其他引擎支持OPTMIZE TABLE
4.2 常用优化
- 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上简历索引
- 应该尽量避免在where子句中使用!=,<>不等于操作符，否则将引擎放弃使用索引而进行扫面。
- 尽量在where语句中别使用or连接。
- 乱用%导致全表扫描，如果想提高效率，可以换成全文索引
- 应尽量避免在where字句中对字段进行表达式操作，这将导致引擎将放弃使用索引而进行全盘扫描
- 应尽量避免在where子句中使用对字段的函数操作，这将导致引擎放弃索引。
5. 优化数据库对象
5.1 优化数据类型
使用 PROCEDURE ANALYSE()对当前应用的表进行分析，它会给出优化建议，用户可根据实际情况考虑是否优化,输入如下命令之后，看最后一列，不一定正确，可参考。
PROCEDURE ANALYSE(16,256)排出多余16个，大于256字节的ENUM建议。
例：select * from user procedure analyse();
5.2 表拆分
- 垂直拆分：字段比较多，而针对某些不常用字段
- 水平拆分：a。表很大；b。表中的数据本来就有独立性，能简单分类;c.需要把表存放在多中介质
5.3 逆规范式
逆规范式可以降低链接操作，加快查询速度，但会降低修改速度，影响数据完整性。所以在考虑使用逆规范式时一定要权衡利弊
常用的逆规范式有：增加冗余列，增加派生列，重新组表和分割表、
5.4 使用中间表
- 数据查询量大
- 数据统计，数据分析
像查找财务报表，需要很多join，这时只需要建一两个中间表，先整合，在用一到两个join完成
6. Mysql引擎比较
6.1 存储引擎
10.png
