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.id8、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配置文件优化