mysql 查询优化和索引使用心得

-- sql优化案例

-- 1.not in 用left join on 替换
-- 2.like '%XXX%' 用 like 'XXX%' 替换

-- 3.limit 优化 实用，在分页中   
    EXPLAIN SELECT * FROM CSP_POI_SEND c ORDER BY c.SEND_ID LIMIT 999999,10;  -- 3278348
   
    EXPLAIN SELECT * FROM CSP_POI_SEND c WHERE c.SEND_ID >= 1000000  ORDER BY c.SEND_ID LIMIT 10;
   
-- 4.count(*) 统计优化，注意在生产库中不要使用，会锁表
 EXPLAIN SELECT COUNT(*) FROM CSP_CTC_CONTACT ;  -- 7002616
 
 EXPLAIN SELECT COUNT(*) FROM CSP_CTC_CONTACT c WHERE c.CONTACT_ID > 0 ;  -- 增加辅助索引
 
 EXPLAIN SELECT COUNT(DISTINCT c.CALLER_ID) FROM CSP_CTC_CONTACT c ;
 
 EXPLAIN SELECT COUNT(*) FROM ( SELECT DISTINCT c.CALLER_ID FROM CSP_CTC_CONTACT c) temp ; -- 增加distinct

-- 5.or优化 用UNION ALL 替换
 EXPLAIN SELECT * FROM CSP_CTC_CONTACT c WHERE c.CID = '45822' OR c.CALLER_ID = '18676555302';
 
 EXPLAIN SELECT * FROM CSP_CTC_CONTACT c WHERE c.CID = '45822' UNION ALL SELECT * FROM CSP_CTC_CONTACT c WHERE c.CALLER_ID = '18676555302';
 
-- 6.使用 ON DUPLICATE KEY UPDATE 主键冲突判断，冲突则update，不冲突则insert，
 INSERT INTO tabless VALUES(3, 'd') ON DUPLICATE KEY UPDATE id=id+1;
 
-- 7.去掉不必要的排序 如：order by a.title desc

-- 8.不必要的嵌套select 查询

-- 9.不必要的表自身连接

-- 10.用where子句替换having子句（只会在检索出所有记录后才对结果集进行过滤，用于集合函数的比较，如count()）

-- 合理使用索引：
 1.每次写入表中时，如插入，更新，删除，都会去更新索引，加大开销；
 2.只有当某列被用于WHERE子句时，索引才会有用；  --- 这个地方要时刻记住
        3.合理使用联合索引，见下面说明；
       
        4.字段使用函数，将不能使用索引；
 5.致命的无引号导致全表扫描，无法用到索引； -- 注意字符类型 一定要加
 6.当取出的数据量超过表中数据量的20%，优化器就不会使用索引，而是全表扫描；
 7.考虑不为某些列建立索引；  -- 如性别，2个均匀分布的值，全表扫描更快；
 8.order BY 和 GROUP BY 优化： -- 重点：一条sql只能有一个索引，有多条索引，优化器会选择那个最优的，可以考虑建立联合索引；
 
 9.mysql 5.6支持EXPLAIN UPDATE/DELETE;
 10.mysql5.6 开启MBR后查询性能提升，支持Muti-RANGE READ 索引优化，SELECT VERSION();
 11.mysql5.6支持一条SQL用2个索引，支持INDEX CONDITION pushdown ICP索引合并。
       
       
       
    -- 联合索引又叫复合索引。要遵循最左侧原则。
   
    对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
    例如索引是KEY INDEX （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

    两个或更多个列上的索引被称作复合索引。

    利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。
    复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。
    如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

    所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；
    仅对后面的任意列执行搜索时，复合索引则没有用处。

    如：建立 姓名、年龄、性别的复合索引。

    CREATE TABLE test（

    a INT,

    b INT,

    c INT,

    KEY a（a,b,c）

    ）；

    优： SELECT * FROM test WHERE a=10 AND b>50

    差： SELECT * FROM test WHERE b>50

    优： SELECT * FROM test ORDER BY a

    差： SELECT * FROM test ORDER BY b

    差： SELECT * FROM test ORDER BY c

    优： SELECT * FROM test WHERE a=10 ORDER BY a

    优： SELECT * FROM test WHERE a=10 ORDER BY b

    差： SELECT * FROM test WHERE a=10 ORDER BY c

    优： SELECT * FROM test WHERE a>10 ORDER BY a

    差： SELECT * FROM test WHERE a>10 ORDER BY b

    差： SELECT * FROM test WHERE a>10 ORDER BY c

    优： SELECT * FROM test WHERE a=10 AND b=10 ORDER BY a

    优： SELECT * FROM test WHERE a=10 AND b=10 ORDER BY b

    优： SELECT * FROM test WHERE a=10 AND b=10 ORDER BY c

    优： SELECT * FROM test WHERE a=10 AND b=10 ORDER BY a

    优： SELECT * FROM test WHERE a=10 AND b>10 ORDER BY b

    差： SELECT * FROM test WHERE a=10 AND b>10 ORDER BY c

 

 

转载于:https://my.oschina.net/u/817550/blog/316076