sql优化

一、sql 查询主要耗时阶段

    1、where 子句筛选数据

    2、数据库 I/O

    3、返回数据

    注：返回的数据量与I/O其实是同一个问题

 

二、sql 优化之索引

   1、索引的种类

       1、B-tree 索引（实际就是二叉树）

            特点：1、指针定义了子节点页中值的上限和下限。

                       2、该索引按顺序组织存储，很适合范围查找。

            适合的查询：1、全键值、键值范围、键前缀查找。

                                  2、该索引除了满足按值查找操作。还可以用于查询中的order by子句。

            索引的限制：1、不是按照索引左列开始查找则无法使用索引。

                                 2、不能跳过索引中的列进行查找。

                                 3、查询中某个列是范围查询，则其右侧所有列无法使用索引。

                                 注：该限制主要针对复合索引的限制

       2、哈希索引

   2、索引的优点

       1、大大减少需要扫描的数据量

       2、可以避免排序和临时表

       3、可以将随机I/O变为顺序I/O

   3、高效使用索引

       1、无法使用索引的情况

         1、查询中的列必须是独立的列。即列不能是表达式的一部分，不能是函数的参数，不能含有隐式转换  

    # 列是表达式的一部分
    select * 
    from application as a 
    where a.id + 1 = 5000 
 
    # 列是函数的参数
    select * 
    from application as a 
    where date(a.created_at) = '20181010'
 
    # 含有隐式转换（user_id 为int类型）
    select *
    from application as a
    where a.user_id = '567'    

         2、like查询是以通配符开头的（like '%a%'）

     select *
     from application as a
     where a.name = '%xia%'
     注：以上不能使用索引针对单列索引。对于复合索引，索引中对应列前面的列依旧可以使用索引。

       2、多列复合索引的使用条件

         1、必须符合最左前缀要求    

    # 现有复合索引 cycle、sex、status
 
    select *
    from application as a
    where a.status = 2
      and a.sex = 'male'
    # 不符合最左前缀原则，无法使用复合索引

    注：针对此问题，如果字段对应值较少，可列出字段所有值，从而利用索引

    # 现有索引 sex、cycle、status
    select * 
    from application as a
    where a.sex in('male', 'female')
      and a.cycle = 2
      and a.status = 4 

      2、符合最左前缀查询，若遇到第一个范围查询则其后的列无法再使用索引

    # 现有复合索引sex、cycle、status
 
    select *
    from application as a
    where a.sex = 'male'
      and a.cycle > 2
      and a.status = 3
    # 复合索引中的第二列使用了范围查询，第三列status无法再使用索引
    注：多个等值查询无此限制，即in无次限制
      
       # 现有复合索引sex、cycle、status
    
    select *
    from application as a
    where a.sex = 'male'
      and a.cycle in(3, 4)
      and a.status = 3
    # 使用in代替范围查询 

         3、索引选择性需要尽量高，且前缀索引长度需要尽量合适

            注：尽量接近与完整列的”基数“

         4、覆盖索引

            定义：查询的列需要都在索引中，但不一定要使用索引

            作用：减少I/O的次数，提高查询速度

         5、利用索引进行排序

          规则：1、索引列顺序和order by子句序列完全一样，且所有列的排序方向相同。    

        # 现有索引cycle、status
            
        select *
        from application as a
        order by a.cycle, a.status desc
        # cycle 与 status 的排列顺序不同，无法使用索引排序

                    2、多表关联时，order by子句引用的字段全为第一个表时，才能使用索引排序。 

       # 现有索引application.cycle, auto_cal_list.status
          
       select *
       from application as a
       inner jion auto_call_list as l on a.id = l.application_id
       where a.status = 4
       order by a.cycle, l.status
       # order by子句有第二个

                    3、需要满足最左前缀规则，除非前代列为常量时。

    4、使用索引时的 I/O

         背景：现有表product，共有100万条数据，有一多列索引（actor、title、prod_id）        

         现有以下两条不同的sql进行查询：

   select *  from product where actor = 'SE' and title like '%AP%'

   select *  from product
   join(
     select pro_id  from product  
     where actor = 'SE' and title like '%AP%'
   )as t1 on t1.prod_id = product.prod_id

   结果集情况：                                      结果：                                            分析：

  1、SE有：30000  其中AP：20000    1、每秒5次          2、每秒5次         1、3万+2万*列（数据I/o） 2、2万+join+2万*列

  2、SE有：30000  其中AP：40          1、每秒7次          2、每秒35次       2、3万+40*列                     2、40+join+40*列

  3、SE有：50        其中AP：10          1、每秒2400次    2、每秒2000次   3、50+10*列                       2、10+join+10*列

      注：当索引覆盖的行过大时（超过表数据的3%），选择器将选择使用全表扫描。

  三、sql优化之减少数据量

     1、减少select返回的数据量，尽量使用 select 列名 代替 select *。

      select *
      from application as a
        
      select a.id, a.user_id
      from application as a

      2、使用 limit 限制返回的数据量（orm中有无limit的比较）。   

     Application.filter().limit(1).execute()

     Application.filter().first()    

     使用limit的好处：

       1、使用 limit 查询到足够的数据就会停止查询，直接返回数据；无limit会将条件查询完才会返回相应的数

            据。

       2、两条语句虽然都是取一条数据，但使用limit只查一条数据，然后返回一条数据；无limit会查出所有数据，

             然后返回所有数据，但只取第一条数据。

       注：偏移量过大时使用limit同样会消耗资源。