SQL常见的可优化点

   # ###################################################

   # 索引相关

   # ###################################################

    1. 查询(或更新，删除，可以转换为查询)没有用到索引

   这是最基础的步骤，需要对sql执行explain查看执行计划中是否用到了索引，需要重点关注type=ALL, key=NULL的字段。

    2.  在索引字段上施加函数

   to_char(gmt_created, ‘mmdd’) = ’0101′

   正确的写法

   gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”)

   

    3. 在索引字段上使用全模糊

   member_id like ‘%alibab%’

   B树无法解决此类问题，可以考虑搜索引擎。

   但是member_id like ‘alibab%’可以用到索引。

   其实，对任何一个字段使用 like ‘%xxxx%’都是一种不规范的做法，需要能检查到这种错误用法。

   

    4.  多列字段的索引，没有用到前导索引

   索引：(memeber_id, group_id)

   where group_id=9234，实际上，这个条件是没有办法用到上面的索引的。这是一个很常见的误用。要理解为什么不能用到这个索引，需要理解mysql如何构造多列索引的。

   索引是一棵B树，问题是，对于多列索引，mysql将索引字段按照索引建立的顺序进行拼装，组成一个新的字符串，这个字符串被用来做为构建B树的键。所以，在查询条件里，如果没有用到前导列，就没办法访问多列索引的B树。

   应该建立索引：(group_id, member_id)

   

    5. 访问到了索引之外的字段

   索引(member_id, subject)

   select subject from offer where member_id=234

   在member_id=234记录数很多的情况下，会优于

   select subject, gmt_created from offer where member_id=234

   原因是第二条sql会根据索引查找到的rowid访问表里的记录。第一条sql使用索引范围扫描就可以得到结果。

   如果某个sql执行次数很多，但是读取的字段没有被索引覆盖，那么，可能需要建立覆盖性索引。

   

    6.  计数count(id)有时比count(*)慢

   count(id) === count(1) where id is not null

   如果没有(id)索引，那么会用全表扫描，而count(*)会使用最优的索引进行用索引快速全扫描

   计数统一使用count(*)

   

    7.  正确使用stop机制

   判断member_id在offer表中是否存在记录：

   select count(*) from offer where member_id=234 limit 1

   优于

   select count(*) from offer where member_id=234

   原因是第一条sql会在得到第一条符合条件的记录后停止。

   # ###################################################

   # 高效分页

   # ###################################################

    1.  高效的分页

   使用join技术，利用索引查找到符合条件的id，构造成临时表，用这个小的临时表与原表做join

   select *

   from

   (

   select t.*, rownum AS rn

   from

   (select * from blog.blog_article

   where domain_id=1

   and draft=0

   order by domain_id, draft, gmt_created desc) t

   where rownum >= 2

   ) a

   where a.rn <= 3

   应该改写成

   select blog_article.*

   from

   (

   select rid, rownum as rn

   from

   (

   select rowid as id from blog.blog_article

   where domain_id=1

   and draft=0

   order by domain_id, draft, gmt_created desc

   ) t

   where rownum >= 2

   ) a, blog_article

   where a.rn >= 3

   and a.rid = blog_article.rowid

    2. order by没有用到索引

   有索引(a, b,c )

   混合排序规则

   ORDER BY a ASC, b DESC, c DESC /* mixed sort direction */

   缺失了前导列

   WHERE g = const ORDER BY b, c /* a prefix is missing */

   缺失了中间列

   WHERE a = const ORDER BY c /* b is missing */

   使用了不在索引中的列进行排序

   WHERE a = const ORDER BY a, d /* d is not part of index */

   # ###################################################

   # 高效地利用primary key

   # ###################################################

    随机查询

   一个错误的做法：

   select * from title where kind_id=1 order by rand() limit 1;

   create index k on title(kind_id);

   这个sql执行过程中需要全表扫描，并且将数据保存到临时表，这是一个非常耗时的操作。

   改进的做法，利用偏移量。

   select round(rand() * count(*)) from title where kind_id=1;

   select * from title where kind_id=1 limit 1 offset $random;

   create index k on title(kind_id);

   相比上面的做法，这种写法能够利用到kind_id上的索引，减少了需要扫描的数据块。但是，如果offset非常大，那么需要扫描的数据块也非常大，极端情况是扫描索引k的所有数据块。

   最优的做法，利用主键进行范围查找

   select round(rand() * count(*)) from title where kind_id=1;

   select * from title where kind_id = and id > $random limit 1;

   这个sql利用primary key进行范围查询，完全走索引，并且只读取一条记录，速度非常快。但是，这种用法的限制是primary key必须是int型，并且是连续自增长的。

   # ###################################################

   # 高效join

   # ###################################################

   子查询是一个影响性能的隐患。应该使用join改写sql。

   # ###################################################

   # 数据类型

   # ###################################################

    1.  避免隐式转换

   CREATE TABLE `user` (

   `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

   `account` char(11) NOT NULL COMMENT ”,

   `email` varchar(128),

   PRIMARY KEY (`id`),

   UNIQUE KEY `username` (`account`)

   ) ENGINE=InnoDB CHARSET=utf8;

   mysql> explain select * from user where account=123 \G

   *************************** 1. row ***************************

   id: 1

   select_type: SIMPLE

   table: user

   type: ALL

   possible_keys: username

   key: NULL

   key_len: NULL

   ref: NULL

   rows: 2

   Extra: Using where

   1 row in set (0.00 sec)

   可以看到，account=123的条件并没有用到唯一索引`username`。mysql的server从storage engine中读取所有的记录，使用to_number()函数，将记录中的account转换成数字，被转换后的数字用来和参数比较。我们的测试表里有2条记录，而执行计划中rows的值也是2，并且type的值为ALL，这也说明索引`username`并没有被用到。

   mysql> explain select * from user where account=’123′ \G

   *************************** 1. row ***************************

   id: 1

   select_type: SIMPLE

   table: user

   type: const

   possible_keys: username

   key: username

   key_len: 33

   ref: const

   rows: 1

   Extra:

   1 row in set (0.00 sec)

   参数为字符串类型，我们可以看到索引`username`，被使用到了。

   这是一个经常被误用的做法。

   

    2. 主键不是自增列

   自增列的主键有多个好处：

   插入性能高。

   减小page的碎片。

   提供二级索引的性能，降低二级索引的空间，因为二级索引存储的是主键的值，并不是page中的行id。

猜您喜欢

• 2013 年 9 月 10 日 -- MySQL源码：索引相关的数据结构(后篇)
• 2013 年 9 月 10 日 -- MySQL源码：索引相关的数据结构(前篇)
• 2013 年 9 月 5 日 -- Unique索引优化实践
• 2013 年 6 月 2 日 -- 从MySQL Bug#67718浅谈B+树索引的分裂优化
• 2013 年 5 月 16 日 -- 案例：MySQL优化器如何选择索引和JOIN顺序
• 2013 年 3 月 5 日 -- MySQL源码:JOIN顺序选择的复杂度
• 2012 年 12 月 25 日 -- MySQL源码:JOIN顺序选择的复杂度
• 2012 年 12 月 16 日 -- mysql中如何找出未使用或使用次数很少的索引
• 2009 年 11 月 8 日 -- MySQL中LEFT JOIN的主表
• 2009 年 4 月 14 日 -- MySql 随机读取数据