SQL常见的可优化点

由于工作较忙,随性更新。admin@stgod.com
# ###########################################
# 索引相关
# ###########################################
– 查询(或更新,删除,可以转换为查询)没有用到索引
    这是最基础的步骤,需要对sql执行explain查看执行计划中是否用到了索引,需要重点关注type=ALL, key=NULL的字段。
– 在索引字段上施加函数
    to_char(gmt_created, ‘mmdd’) = ’0101′
    正确的写法
    gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”)
– 在索引字段上使用全模糊
    member_id like ‘%alibab%’
    B树无法解决此类问题,可以考虑搜索引擎。
    但是member_id like ‘alibab%’可以用到索引。
    其实,对任何一个字段使用 like ‘%xxxx%’都是一种不规范的做法,需要能检查到这种错误用法。
– 多列字段的索引,没有用到前导索引
    索引:(memeber_id, group_id)
    where group_id=9234
    实际上,这个条件是没有办法用到上面的索引的。
    这是一个很常见的误用。要理解为什么不能用到这个索引,需要理解mysql如何构造多列索引的。
    索引是一棵B树,问题是,对于多列索引,mysql将索引字段按照索引建立的顺序进行拼装,组成一个新的字符串,这个字符串被用来做为构建B树的键。所以,在查询条件里,如果没有用到前导列,就没办法访问多列索引的B树。
    应该建立索引:(group_id, member_id)
– 访问到了索引之外的字段
    索引(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执行次数很多,但是读取的字段没有被索引覆盖,那么,可能需要建立覆盖性索引。
– 计数count(id)有时比count(*)慢
    count(id) === count(1) where id is not null
    如果没有(id)索引,那么会用全表扫描,而count(*)会使用最优的索引
    进行用索引快速全扫描
    计数统一使用count(*)
– 正确使用stop机制
    判断member_id在offer表中是否存在记录:
    select count(*) from offer where member_id=234 limit 1
    优于
    select count(*) from offer where member_id=234
    原因是第一条sql会在得到第一条符合条件的记录后停止。
    
# ###########################################
# 高效分页
# ###########################################
– 高效的分页
    使用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
– order by没有用到索引
    有索引(a, b, )
    混合排序规则
    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 titile
    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
– 避免子查询
    子查询是一个影响性能的隐患。应该使用join改写sql。

# ###########################################
# 数据类型
# ###########################################
– 避免隐式转换
    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`,被使用到了。
    这是一个经常被误用的做法。
– 主键不是自增列
    自增列的主键有多个好处:
    插入性能高。
    减小page的碎片。
    提供二级索引的性能,降低二级索引的空间,因为二级索引存储的是主键的值,并不是page中的行id。

转载请注明出处:https://stgod.com/3442/

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: