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