mysql查询优化非索引_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