MYSQL查询性能优化及案例(一)

优化数据访问

查询性能底下的最主要原因就是访问的数据太多，主要包含两个方面

• 查询检索大量超过需要的数据，通常是访问太多的行，有时候时访问太多的列。
• MySQL服务层是否在分析大量超过的数据行。

请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的查询会被应用程序丢弃。这会给Mysql服务器带来额外的负担，并增加网络开销，并且会消耗服务器的CPU和内存资源。

查询所有在电影Academy Dinosaur中出现的演员

-- 错误做法
select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title = 'Academy Dinosaur';
-- 正确做法，也就是需要什么列查什么列
select actor.* from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title = 'Academy Dinosaur';

SELECT * 的优缺点

• 取出所有列意味着优化器无法完成索引覆盖的这类优化，给服务器带来额外的I/O、内存和CPU的消耗
• 可以提高相同代码的复用性，简化开发，如果程序使用了某种缓存机制，获取并缓存所有列的查询，相比多个独立只获取部分列的查询可能更有好处。但是这个场景非常少。

扫描额外的数据

查询为了返回结果是否扫描过多的数据，对于MYSQL来说很亮查询开销的指标如下。

• 响应时间：服务时间和排队时间之和，服务时间是指数据库处理这个查询真正花了多少时间，排队时间指的是等待行锁，等I/O操作完成等等。
• 扫描行数：理想情况下，扫描的行数和返回的行数是相同的，但是实际很少发生尤其是在关联查询时。
• 返回的行数

扫描的行数和访问类型

在explain语句中type反映了访问类型，访问类型从全表扫描、索引扫描、范围扫描、唯一索引扫奥妙、常量，速度由慢到快，扫描行数由多到少。

mysql> explain select * from film_actor where film_id=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: idx_fk_film_id
          key: idx_fk_film_id
      key_len: 2
          ref: const
         rows: 10
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> select * from film_actor where film_id=1;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|        1 |       1 | 2006-02-15 05:05:03 |
|       10 |       1 | 2006-02-15 05:05:03 |
|       20 |       1 | 2006-02-15 05:05:03 |
|       30 |       1 | 2006-02-15 05:05:03 |
|       40 |       1 | 2006-02-15 05:05:03 |
|       53 |       1 | 2006-02-15 05:05:03 |
|      108 |       1 | 2006-02-15 05:05:03 |
|      162 |       1 | 2006-02-15 05:05:03 |
|      188 |       1 | 2006-02-15 05:05:03 |
|      198 |       1 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
10 rows in set (0.01 sec)

这个查询使用ref类型访问数据的方式，访问的行数和返回的行数时1：1，当删除这个索引时再来运行查询。

mysql> explain select * from film_actor where film_id=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5462
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

此时访问类型变成ALL,需要扫描5426行数数据。这里的using where是从数据表中返回数据，这是在服务层完成层的，MYSQL需要从数据表中读出记录然后过滤。

但是不是说增加索引就i可以是的扫描的行数等于返回的行数

200 rows in set (0.00 sec)

mysql> explain select actor_id,count(*) from film_actor group by actor_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5462
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

读取5426行返回200行。
如果发现扫描大量行数据，但是只返回少量行，可通过以下方式进行优化

• 使用覆盖索引，把所有需要用的列放到索引中，这样存储引擎无需回表获取对应行就可以返回了。
• 使用单独的汇总表，单表冗余字段
• 重写复杂查询，让MYSQL优化器能够更优化的执行这个查询

重构查询的方式

目的是找到更有的实际需要的结果为不是需要从MYSQL获取一摸一样的结果集，有时候是转换一种查询方法，有时候是修改应用代码。

一个复杂查询还是多个简单查询

在实际工作中总是去强调在数据库层去完成可能多的工作，譬如使用存储过程、函数、复杂的查询等等，这样的逻辑是在网络通信，查询解析优化是一件代价很高的基础上，但是现在网络速度比以前快很多。现在mysql从设计上每秒支持几千次查询。所以运行多个小查询不成问题。

切分查询

删除数据就是很好的例子，如果一个大的语句一次完成的话就会锁住多行数据，阻塞很多小的重要的查询。将大的delete语句切分较小的查询可以尽可能小的影响mysql性能。

-- 删除所有 "created" 列的值早于当前日期的三个月之前的记录
delete from messaages where created < DATE_SUB(NOW(),INTERVAL 3 MONTH);
-- 改成
rows_affected = 0
do{
	rows_affected = do_query(
		"delete from messaages where created < DATE_SUB(NOW(),INTERVAL 3 MONTH) limit 10000"
	)
}while rows_affected > 0;

分解关联查询

可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联

select * from tag join tag_post on tag_post.tag_id=tag.id join post on tag_post.post_id = post.id where tag.tag = 'mysql'
-- 拆分成
select * from tag where tag = 'mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123,134);

分解关联查询的好处：

• 单个查询减少锁的竞争
• 可以减少冗余记录的查询，在应用层做关联查询，意味着对于某条应用只需要查询一次，数据库做关联查询可能重复访问一部分数据。
• 在应用层做关联可以更容易对数据库进行拆分，更容易做到高性能高扩展。