深入浅出MySQL(十)关于mysql查询的一些优化技巧

EXPLAIN
先是最简单的一个分析：
EXPLAIN(SELECT admin_name FROM w_admin )

这里面显示了查询类型select_type为简单查询，type指的是字段，possible_keys是指使用了哪个索引来进行搜索，key是指mysql实际使用的索引，key_len是指响应索引的长度，ref是指链接的匹配条件（例如外键查询）：rows是指mysql根据统计信息以及索引选用的情况。
例如这样一条关于管理员角色分配查询的sql语句。

in查询里面不要包含太多的字段内容：
in实际上是用了数组来存储里面的字段信息的，如果这些数字都是连续的话，就建议不要用in了，用between会好些。

执行select语句的时候，尽量指明字段名称
减少网络的io耗损

当查询数据只有一条的时候，尽量使用limit 1
这是为了能够使得EXPLAIN中的type列达到const类型

如果说sql语句进行排序的时候，尽量使得排序的关键字是索引字段

如果限制条件中其他字段没有使用到索引，则尽量少用or
通常会用union all或者union来替代

union和union all的使用区别
前者在进行数据整合的时候会进行去重和重新排序，因此会消耗较多的cpu性能，所以使用的时候需要谨慎。(UNION ALL 查询的时候没有去重，所以可能会查询出来多余部分的数据)

随机查询的实现技巧
SELECT * from w_article ORDER BY RAND() limit 10
这种方式实现的随机查询一般性能消耗会比较大，不妨试试用limit来进行优化，limit的开始位置通过java或者php代码，传入相应的参数来进行设置。

常用的查询关键字 in和exists的区别？
两种关键字驱动的查询顺序有所不同，这也是性能变化的关键之处。
exists：外表驱动
in：内表驱动，先进行子查询
优化可以考虑：

原SQL语句：

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

合理高效的分页技巧：
通过一些limit来进行分页的话，当数据量增大的时候，查询会越来越慢。
不妨可以通过相应的id来进行起始点优化性能。不过这招很不靠谱。

尽量在where字句中用到null值，因为这样会放弃使用全表查询

不建议使用%前缀模糊查询
例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。
那如何查询%name%？
如下图所示，虽然给secret字段添加了索引，但在explain结果并没有使用：
那么如何解决这个问题呢，答案：使用全文索引。
在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。（但是全文索引只在MYISAM里能被支持）
创建全文索引的SQL语法是：
ALTER TABLE dynamic_201606 ADD FULLTEXT INDEX idx_user_name (user_name);
使用全文索引的SQL语句是：
select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);
注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

在where字句中尽量少些一些对于字段的表达式操作，容易造成数据库引擎放弃索引的操作
举个例子来说：

select user_id,user_project from user_base where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

select user_id,user_project from user_base where age=36/2;

对于联合索引来说，要遵守最左前缀法则
举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

当我们使用了范围查询的时候，联合索引可能会失效
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。