Query 语句优化基本思路和原则

Query 语句优化基本思路和原则

 

1. 优化更需要优化的Query；
2. 定位优化对象的性能瓶颈；
3. 明确的优化目标；

4. 从Explain 入手；

    小结果集驱动大结果集

    尽可能避免复杂的Join 和子查询（）

 

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

   仅使用最有效的where过虑条件：并不是where的过虑条件越多，执行效率越高， Query语句性能的优劣最关键的是：选择一条最佳的数据访问路径，如何做到通过访问最少的数据量完成自己的任务，事例如下在最下面

10. 尽可能避免复杂的Join 和子查询；

      越复杂的Join 语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多。

 

需求： 查找某个用户在所有group 中所发的讨论message 基本信息。
场景： 1、知道用户ID 和用户nick_name
2、信息所在表为group_message
3、group_message 中存在用户ID(user_id)和nick_name(author)两个索引
方案一：将用户ID 和用户nick_name 两者都作为过滤条件放在WHERE 子句中来查询，Query 的执行计
划如下：
sky@localhost : example 11:29:37> EXPLAIN SELECT * FROM group_message
-> WHERE user_id = 1 AND author='1111111111'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_author_ind,group_message_uid_ind
key: group_message_author_ind
key_len: 98
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
方案二：仅仅将用户ID 作为过滤条件放在WHERE 子句中来查询，Query 的执行计划如下：
sky@localhost : example 11:30:45> EXPLAIN SELECT * FROM group_message
-> WHERE user_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_uid_ind
key: group_message_uid_ind
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
方案二：仅将用户nick_name 作为过滤条件放在WHERE 子句中来查询，Query 的执行计划如下：
sky@localhost : example 11:38:45> EXPLAIN SELECT * FROM group_message
-> WHERE author = '1111111111'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_author_ind
key: group_message_author_ind
key_len: 98
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
初略一看三个执行计划好像都挺好的啊，每一个Query 的执行类型都利用到了索引，而且都是
“ref”类型。可是仔细一分析，就会发现，group_message_uid_ind 索引的索引键长度为4（key_len:
4），由于user_id 字段类型为int，所以我们可以判定出Query Optimizer 给出的这个索引键长度是
完全准确的。而group_message_author_ind 索引的索引键长度为98（key_len: 98），因为author 字
段定义为varchar(32) ，而所使用的字符集是utf8，32 * 3 + 2 = 98。而且，由于user_id 与
author（来源于nick_name）全部都是一一对应的，所以同一个user_id 有哪些记录，那么所对应的
author 也会有完全相同的记录。所以，同样的数据在group_message_author_ind 索引中所占用的存储
空间要远远大于group_message_uid_ind 索引所占用的空间。占用空间更大，代表我们访问该索引所需
要读取的数据量就会更多。所以，选择group_message_uid_ind 的执行计划才是最有的执行计划。也就
是说，上面的方案二才是最有方案，而使用了更多的WHERE 条件的方案一反而没有仅仅使用user_id
一个过滤条件的方案一优。