数据库优化

首先来看一下数据库优化的方式以及优化后的效果和成本。

上面你也看到了sql及索引优化成本低而且效果高，那么如何优化sql呢？

优化sql,首先需要发现有问题的sql

那么如何发现有问题的sql呢?(这里我使用mysql举例)

你可以开启mysql的慢查询日志对有效率问题的sql进行监控

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

慢查询日志中包含sql,sql执行时间以及其他执行信息。

你也可以使用mysql慢查询日志分析工具pt-query-digest,找到
1.查询次数多且占用时间长的sql,通常为pt分析的前几个查询。
2.io大的sql(数据库中的数据是一页一页存储的，每查询一页就是一次io,io费时)
3.未命中索引的sql。

然后可以通过explain sql语句 分析sql的执行计划。

sql的简单优化

Count()和max()

-- 优化max()函数,像这类的函数需要遍历一遍对应的字段，可以在该字段上添加索引，快速查找
explain select max(payment_date) from payment;
create index idx_paydate on payment(payment_data);//给payment_date建立索引(覆盖索引)

平时我们使用都是使用count(*)计算所有行，count(字段)就是计算该字段上只要不为null就+1，为null就不+。下面我们看看，如果有一张电影表，记录所有电影和该电影上映的时间。

-- 在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数
select count(release_year='2006' or null) as '2006年电影数量'，count(release_year='2007' or null) as '2007年电影数量' from film;
-- 上面or null，如果=2006，就是1 or null true 就+1  如果！=2006 然后就是0 or null得到null,null是不会+1计数的
-- count()统计的是非空的数据，所以只有为null才不计算，所以就算false也会计算，所以上面or null,就是除了2006就是null不计算了

子查询优化

通常情况下，需要避免子查询，改为连接查询，比如使用in,因为外层没查询一次，子查询in内部就会遍历查询一遍。浪费时间。

//查询sandra出演的所有影片
explain select title,release_year,LENGTH from film
where film_id in (
select film_id from film_actor where actor_id in (
select actor_id from actor where first_name='sandra'));

Exist和In

在必须写子查询的情况就会有两种情况，谁在外层，谁在里层？
最好结果就是：最优化匹配原则：拿最小记录匹配大记录。

• 若是子查询 较小，优先使用in (IN优先查询子查询，然后匹配外层查询)
• 若是外层查询比子查询小，优先使用Exists （优先查询外层表，然后和内层表匹配）

group by的优化

explain 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;

因为上面代码首先连接查询，最后group by，group by 放在最外层，这里会创建临时表给分组用。

//改后 结合子查询 索引
explain 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);

这里使用子查询在内部就直接分组查询，然后与外层连接查询就不会涉及到创建临时表。
像如果需要添加where条件，最后在子查询内部来添加，尽量不要写在最外面

limit优化

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

//文件排序，IO大
explain select film_id,description from sakila.film order by title limit 50,5;

1. 1.优化：使用有索引的列或主键进行order by操作（order by film_id） 但是优化1在扫描的时候还是会全部扫描，io大，所以可以使用下面where条件的方式来避免扫描太多的数据
2. 2.记录上次返回的主键，在下次查询的时候用主键过滤，避免了数据量大时扫描过多的记录 select film_id,description from sakila.film where film_id>55 and film_id<=60 order by film_id
   limit 1,5; 页数越大，IO越大
   注意：上面where条件中的film_id必须是连续的，否则可能会错过数据，如果这个id是不连续的，可以在创建表时添加一个连续字段的id

既然说到创建连续字段id,你会想到mysql的自增的主键id，其实有一点需要注意，
当一个事务开启主键id+1,但是如果发生会滚，另外一个事务再执行id会跳过那个回滚设置的主键，它认为这个id用了!

索引优化

如何为合适的列创建索引？

1. 在where从句，order by从句，group by从句，on从句中出现的列加索引，就是为常用的列添加索引。
2. 创建的索引字段越小越好。索引字段小了，每页存储的数量就多了，io就少了。
   mysql中普遍选择B+bree做索引，如果不太理解2，可以简单了解一下B+tree原理。
3. 创建联合索引，两方面考虑，一方面根据数据考虑，另一方面根据表字段访问情况

对于下面的sql中where有两个条件，此时创建两个单个普通索引没有用，所以要创建联合索引。

select * from payment where staff_id=2 and customer_id=584;

• 3.1 从数据的角度出发：
  但是你可能有个疑问？index(staff_id,customer_id)好？还是index(customer_id,staff_id)好？
  答：离散度大（select count(distinct customer_id)比较大）的放前面

• 3.2 从字段访问情况出发：
  说到联合索引就不得不提到最左匹配原则：当我们创建联合索引，比如（key1,key2,key3）,相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则
  所以根据该原则，把where子句中使用最频繁的字段的一列放到联合主键的最左面，这样就可以满足大部分的索引。

表结构优化

选择合适的数据类型

• 使用可以存下你的数据的最小数据类型；
  • 使用int来存储日志时间，利用FROM_UNIXTINE()(得到日期),UNIX_TIMESTAMP()(得到时间戳)两个函数来进行转换
  • 使用bigint来存ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换
• 使用简单的数据类型。int型在mysql中比varchar处理更简单；
• 尽可能使用not null定义字段，null字段mysql需要专门处理。
  • Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引
    --------这也是《高性能mysql第二版》介绍的
• 尽量少用text类型。

表的范式化和反范式化

范式化是数据库设计的规范，尤其是若不满足第三范式的表会存在以下问题：

• 1.数据冗余：（分类，分类描述）对于每一个商品都会进行记录
• 2.数据的插入异常
• 3.数据的更新异常
• 4.数据的删除异常

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询的目的，反范式化是一种以空间来换取时间的操作。

表的拆分

1. 垂直拆分
   所谓的垂直拆分就是把一个拥有很多列的表拆分成多个表，解决宽度问题，通常按以下原则进行：
   • 把不经常使用字段单独放到一个表中；
   • 把大字段独立放到一个表中；
   • 把经常使用的字段放到一起；
2. 水平拆分
   表的水平拆分是解决表数据量过大的问题，水平拆分每个表结构是一样的。
   水平拆分就是如何把数据均匀的分到水平拆分的每个表中。
   比如 对id%表数量

系统配置优化

1. 操作系统配置优化
2. MySQL数据库优化

若涉及到这一步，就只能自己查阅资料来解决了，这里就不细说了。