Mysql之慢查询日志、SQL、索引、配置、分库、分表优化

1、数据库优化的目的

2、可以从哪几个方面进行数据库优化

3、SQL及索引优化

如何发现有问题的SQL?
使用MySQL慢查询日志对有效问题的SQL进行监控
（1）show variables like ‘slow_query_log’ -----查看是否开启慢查询日志
（2）set global show_query_log_file -’/home/mysql/sql_log/mysql-slow.log’ ------慢查询日志存储的位置
（3）set global log_query_not_using_indexes=on ----j将没使用索引的项目插入慢查询日志
（4）set global long_query_time=1-------超过1秒的设置与漫长寻日志

慢查询日志的存储格式

慢查询日志的所有包含的内容

4、慢查询日志的分析工具

mysqldumpslow
查看前三条数据

包含了Sql语句执行的次数、执行时间、锁定时间、发送的行数、哪个服务器发送的、具体执行时间

pt-query-digest

包含了执行时间（最大 最小时间）、锁定时间、查询的内容扫描的范围

包含表查询时间的统计

阿里云Postgresql

如何通过Mysql慢查询日志发现问题

5、使用explain 查询SQL的执行计划

6、Count()和Max()的优化

explain select max(payment_data) from payment \G**

影响行数达到了一万以上我们需要进行优化创建索引
create index idx_paydate on payment(pay_date)

7、子查询优化

注意当存在一对多关系时 需用distinct函数进行去重复
select distinct t.id from t join t1 on t.id = t1.id

8、Group By优化

避免文件排序和临时表操作用子查询进行优化

9、Limit 优化

使用索引的列或者主键进行Order by操作（就不会采用文件进行排序了采用主键）

记录上次返回的主键，在下次查询的时候使用主键进行过滤

10、索引优化
如何选择合适的列建立索引
（1）在where 从句，group by从句，order by从句，on从句中出现的列
（2）索引字段儿越小越好
（3）离散度大的放到联合索引的前面

索引的维护及优化---------------重复及融合索引
主键和唯一索引重复

找重复索引（pt-duplicate-key-checher)

删除不用的索引（pt-index-usage)
通过慢查询日志定位哪些索引是不是使用的

11、数据库表结构优化
选择合适的数据类型
（1）使用可以存下你的和数据的最小数据类型
（2）使用简单的数据类型。Int要比varchar类型在Mysql处理上简单
（3) 尽可能的使用not null定义字段
（4）尽量少用text类型，非用不可是最好考虑分表
比如： 使用int类型存储时间、或者用bigintlailai 存储IP地址

12、表的范式优化及反反范式化
建表的时候遵从 三大范式

13、表的垂直拆分
拆分原则
（1）把不常用的字段单独放到一个表中
（2）把大字段放到一个表中‘
（3）把经常一起使用的字段放到一起
14、表的水平拆分
表的水平拆分主要是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完成一致的
水平拆分的方法（hash运算取模）

面临的挑战（前台用拆分表、后台用汇总表）
（1）跨分区表进行数据查询
（2）统计及后台报表操作

13、系统配置优化
系统的CPU、Disk IO
14、Mysql配置文件优化