数据库优化

---

layout: post
title: "数据库优化"
date: 2016-05-23 12:57:08 +0800
comments: true
categories: [mysql]

---

在面试的时候被问到数据库优化问题，结果一下没反应过来理解为了PHP性能优化（懵逼脸）一顿BB，然后就GG了

SQL及索引优化

如何发现有问题的SQL？

使用MySQL慢查日志对有效率问题的SQL进行监控

show varilables like 'show_query_log' //查看是否开启慢查日志
set global slow_query_log=on;//开启慢查日志
set global slow_query_log_file='/home/mysql/sql_log/mysql-show.log' //设置保存位置
set global log_queries_not_using_indexes=on; //记录没有索引的SQL
set global long_query_time=1 //记录大于1s的SQL

慢查日志分析工具

• mysqldumoslow mysql自带的分析工具

• pt-query-digest 比上一种更详细

[图片上传失败...(image-d923f6-1532593875585)]

发现有问题的SQL

[图片上传失败...(image-85c887-1532593875585)]

如何分析SQL查询

使用explain查询SQL的执行计划

explain select * from test;

[图片上传失败...(image-e97398-1532593875585)]
[图片上传失败...(image-a05c73-1532593875585)]
Count()和Max()的优化

<pre class="prettyprint">
Max():添加索引
Count()：在使用时注意查询具体列和*，查询某一列的时候不会包含为NULL的项
</pre>

子查询优化

<pre class="prettyprint">
通常情况下需要把子查询优化为join查询，但在优化时要注意关联建是否有一对多的关系，要注意重复数据。
select * from t where t.id in (select t1.tid from t1);
select t.id from t join t1 on t.id=t1.tid;
当出现一对多的关系需要使用distinct去重
select distinct t.id from t join t1 on t.id=t1.tid;
</pre>

group by 优化

<pre class="prettyprint linenums">
explain select actor.first_name,actor.last_name,COUNT() FROM sakila.file_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.actor INNER JOIN (
SELECT actor_id,COUNT() AS cnt FROM sakila.film_actor GROUP BY actor_id
)AS c USING(actor_id);
</pre>

limit 优化

<pre class="prettyprint">
limit 常用于分页处理，时常会伴随order by使用，因此大多时候会使用Filesorts这样会造成大量IO问题
SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;
优化1:使用有索引的列或主键进行Order by
SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5;
优化2:记录上次返回的主键，在下次查询时使用主键过滤
SELECT film_id,description FROM sakila.film WHERE film_id>55 and film_id<60 ORDER BY film_id LIMIT 1,5;
</pre>

索引优化

• 在离散度较大的列上建立联合索引
• 过多的索引会减小查询效率。
• 删除重复和多于的索引
  [图片上传失败...(image-6b9864-1532593875585)]

查找重复和冗余索引

[图片上传失败...(image-f91610-1532593875585)]

需要在 information_schema库下运行

• 使用pt-duplicate-key-checker 工具检查重复及冗余索引
• pt-duplicate-key-checker -uroot -p 'xxxx' -h 127.0.0.1

数据库结构优化

选择合适的数据类型

• 使用可以存下你的数据的最小的数据类型
• 使用简单的数据类型，Int要比varchar类型在mysql处理上简单
• 尽可能使用not null 定义字段
• 尽量少使用text类型，非用不可以时最好考虑分表
• 使用bigint来存放ip

表的范式化和反范式化

范式化一般是指第三设计范式，也就是要求数据表中不存在飞关键字段对任意候选关键字的传递函数依赖则符合第三范式。