mysql 查询优化01

1. 查询某张表数据和索引的大小：

   -- 查询某张表数据和索引大小:truncate(num1,num2)：截取num1小数点后num2位；
   select table_schema,concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
   concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size -- 存储单位为b(字节)
   from information_schema.tables tab
   where tab.TABLE_NAME='order_tab' -- 查询某张表数据和索引大小
   group by table_schema
   order by sum(data_length) desc;
   
   desc information_schema.tables;

   
   
2. explain "查询":查看本次查询走到的索引；
3. explain extended “查询”+show warnings:查看重构的查询，即最终执行的sql，以后写SQL时可以仿照这种“重构风格”；
4. like左匹配模式可以走到索引，但同><和between一样，这是一种"range"类型的索引，符合索引走到这里就会停止匹配，示例如下：

   -- 存在索引（mobile和cert_no）时，下边两个SQL筛选的rows一样
   explain 
   select count(*) from user_tab_du oc
   where oc.mobile like '13521%';
   
   explain 
   select count(*) from user_tab_du oc
   where oc.mobile like '13521%'  and oc.cert_no='';

5. 筛选条件为IN（A，B，C）时不是“范围查询”，而是“多值相等查询”，符合索引匹配到这里不会停止，因此将范围查询转换成“多值相等”可以加快速度；但是不能过度依赖，比如三个字段使用IN操作，每种有10值时，排列组合达到1000种；？（排列组合，多值相等）

   -- 为 （username，sex） 添加索引;('女士','李娜','先生')
   explain 
   select * 
   from user_tab_du oc
   where oc.username in('女士','李娜','先生');
   
   explain 
   select *
   from user_tab_du oc
   where oc.username in('女士','李娜','先生') and sex in('FEMALE','MALE');

   第二条SQL筛选rows小于第一条，表明索引走到了sex字段；
6. 当索引可以覆盖到所有查询字段时，称之为“覆盖索引”，查询可以直接从索引中去的所需数据。而非“覆盖索引”，即使走到相同索引并且查询数据大小相同，但是因为需要回表取数据，查询速度相对比较慢，比如

   explain -- 快
   select oc.username,oc.sex
   from XXX oc
   where oc.username in('女士','李娜','先生') and sex in('FEMALE','MALE');
   
   -- 慢
   explain
   select oc.username,oc.sync_bbs
   from XXX oc
   where oc.username in('女士','李娜','先生') and sex in('FEMALE','MALE');

   
   
7. 如上索引不能覆盖所需全部制度按的情况，可以使用“延迟关联”进行优化：先查询条件（索引）查到数据集主键id，在根据主键id获取结果集，如下比‘6’中第二个SQL明显快很多：

   explain
   select oute.username,oute.sync_bbs 
   from XXX oute
   inner join
   ( 
   select id 
   from XXX inne
   where inne.username in('女士','李娜','先生') and sex in('FEMALE','MALE')
   )tem
   on oute.id=tem.id;

   问题：后两种取结果集过程？后者走到了主键？
8. 等值传播：把多表连接on条件作为where筛选条件时，会在两张表中做同样的筛选

   explain extended
   select *
   from xxxlows_rfnd fr
   inner join xxxetails od
   on fr.ord_id=od.ord_id 
   where  od.ord_id=2819460;
   
   show warnings;
   -- 重构的查询
   /* select#1 */
   ...
   where ((`od`.`ord_id` = 2819460) and (`ent_portal`.`fr`.`ord_id` = 2819460))

9. 将索引列放在比较符号的的一侧，当索引列是表达式的一部分时，无法走到索引；

   explain
   select * from user_tab ui
   where id+1=218;
   
   explain 
   select * from user_tab ui
   where id=217;-- 应该尽量将逻辑操作放在比较符号的一侧；

10. -- 例如我们要查找2017-08-05更新过个人信息或者注册的用户；
    -- 有索引(update_time)
    -- 第一种去掉时分秒的方法：慢
    explain
    select *
    from user_table ui
    where date(ui.update_time)='2017-08-05';
    
    -- 第二种：很快
    explain
    select *
    from user_table ui
    where ui.update_time>'2017-08-04' and ui.update_time<'2017-08-06';
    
    
    
    
    
    
    
    

11. 在建立索引是，有些字段虽然选择性很低，但是因为很多业务类型都要用到，因此将其放在联合字段中，比如qq查找好友的功能中，选择性很低的“在线”‘不在线’字段可能加入了索引，三种搜索状态对应sql如下：

    -- 查找在线好友
    select users.info
    from user_table t
    where t.sex='female' and t.region='china' and t.age in(18,19,20,21,22) and t.status='online';
    
    -- 不在线
    ...t.status='off_line';
    
    -- 忽略状态（即查找所有状态时）
    。。。t.status in('online','off_line');

12. drop table tab_name,delete from tab_name where con_XXX,truncate table tab_name的区别

    1.drop table tab_name:删除表，后两者为删除表数据；
    2.delete from tab_name where con_XXX:根据条件删除表中记录；auto-incrementing keys不会重置；执行后会计算影响的记录数，也因此比truncate table tab_name 慢；可以使用事务回滚操作；仅仅是行锁
    3.truncate table tab_name:清空表，重置auto-incrementing key字段为1，保留索引；

    详情参见：点击打开链接
13.