数据库优化

2017-08-20 | 阅读：次

数据库优化目的：

1.避免出现页面访问错误

由于数据库连接timeout产生也页面5xx错误（服务器内部错误）
由于慢查询造成页面无法加载
由于阻塞造成数据无法提交（服务器内部锁）

2.增加数据库的稳定性

很多数据库问题都是由于低效的查询引起的

3.优化用户体验

流畅的页面的访问速度
良好的网站功能体验

从以下方面优化数据库：

硬件
系统配置
数据库表结构（数据库范式）
sql及索引（结构良好的sql和建立合适的索引）

mysql慢查询日志开启：

show variables like 'show_query_log' //查看是否看起慢查询日志

set global slow_query_log = on //开启慢查询日志

set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'

set global log_queries_not_using_index = on

set global long_query_time=1	//超过1秒之后的sql记录到日志中

show variables like 'slow%' //查看慢查询日志位置

慢查询日志包含的内容：

sql的执行信息
执行sql的主机内容
sql的内容
sql执行时间

分析工具mysqldumpslow

mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

pt-query-digest

pt-query-digest slow-log > slow_log.report  //输出到文件

pt-query-digest slow.log -review \  //输出到数据库表

如何通过慢查询日志发现有问题的sql：

查询此时多且每次查询占用时间长的sql

通常为pt-query-digest分析的前几个查询
IO大的sql

注意pt-query-digest分析中的Rows examine项
未命中索引的sql

注意pt-query-digest分析中Rows examine和Rows Send的对比

如何分析sql查询：

使用explain查询sql的执行计划

返回各列的含义：

table：显示这一列的数据是关于那张表的
type：显示连接使用了何种类型，从最好到最差的连接类型为const（常数查找，用于主键或唯一索引查找）、eq_reg（范围查找）、ref、range（索引的范围查找）、index（索引扫描）、all（表扫描）
possible_keys：显示可能用在这张表中的索引，如果为空，没有可能的索引
key：实际使用的索引，如果为null，则没有使用索引
key_len：使用的索引长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的那一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数
extra：
Using filesort：查询需要优化，mysql需要进行额外的步骤来发现如何对返回的行排序
Using temporary：查询需要优化，mysql需要创建一个临时表来存储结果，通常发生在对不同列集进行order by上而不是group by上

在某一列建立索引：

create index col on table()

子查询优化：

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据，使用distinct去重

group by优化：

//优化前：
select actor.first_name,actor.last_name,count(*)
from sakila.film_actor
inner join sakila.actor using(actor.id)
group by film_actor.actor_id;

//优化后：
select actor.first_name,actor.last_name,c,cnt
from sakila.film_actor inner join (
select actor_id,count(*) as cnt from sakila.film_actor group by actor_id
)as c using(actor_id);

limit查询

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候回使用filesorts 会造成大量的IO问题

select film_id,description from sakila.film order by title limit 50,5;

优化方法：

1.使用有索引的列或主键进行order by操作
2.记录上次返回的主键，在下次查询是使用主键过滤

select film_id,description from sakila.film where film _id > 55 and film_id <= 60 order by film_id limit 1,5;

如何建立合适的索引：

在where，group by，order by，on从句中出现的列
索引字段越小越好
离散度越大的列放在联合索引的前面

count(*)与count(id)区别：count(id)不包括为null的行

数据库表结构优化：

选择合适的数据类型
遵循范式

第一范式：

指数据库表的每一列都是不可分割的基本数据项

第二范式：

数据库表中不存在非关键字段对任一候选键的部分函数依赖，也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式：

在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式

好处：
1.减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
2.消除异常（插入异常，更新异常，删除异常）
3.让数据组织的更加和谐…
垂直拆分：把原来一个有很多列的表拆分成多个表，
把不常用的字段单独放在一个表中
把大字段独立放在一个表中
把经常一起使用的字段放在一起
水平拆分：解决表单数据量过大