mysql的查询优化的一点技巧

1. 慢速分页

当使用limit x, y，x过大时，会发现查询时间也渐增。可以做个试验，550w的数据，limit 20,100查询时间为0.5s，改为150000,100时，查询时间骤增为178s

select id from test where name is null order by id limit 20, 100

查询时间： Time: 522ms

select id from test where name is null order by id limit 150000, 100

查询时间：Time: 178169ms

如果id有索引，直接使用下面的方式，速度可以得到极大的提升，其原理就是将上次分页的最后一条记录拿出来当做一个标志，因为主键做了索引，因此这里可以用比较运算符。上面的limit一定要一个一个数，直到数到150000，但是比较运算符可以在索引上先查找该id，于是利用B+树的查找性能，查询时间是O(logn)。

select id from test where id > '90491CFAE79F945153F36FB1922DF2861A5401BB' order by id limit 100

查询时间： Time: 79ms

 一般情况不推荐深度分页，因为速度会越来越慢，最主要是用户行为也不太可能对大数据做深度分页（比如google实际上只提供1000页的内容），但是实际上有时需要遍历数据库中的数据，还是需要分页来降低内存的占用的。这种情况下，这种方法做分页就比较有用了。

2. 没用使用批量处理

这条基本上属于常识了，稍微处理过数据的人都会发现交给数据库批量更新或者插入的速度要大大优于自己循环一条一条插入的速度。

3. join查询时以大表为基础查询

如下的表中，bigtable存有500w数据，smalltable中只有10条数据，并且彼此id互相关联，其中大表id已建索引，如使用如下语句，则查询时间惊人。

select * from bigtable b left join (select * from smalltable) as s on b.id = s.id

查询时间：Time: 28318ms

但如果换个思路，以小表为基础，则是瞬间。两者数据量差异越大，则时间对比越发明显。

select * from bigtable b right join (select * from smalltable) as s on b.id = s.id
或
select * from bigtable b join (select * from smalltable) as s on b.id = s.id

查询时间：Time: 571ms

4. 数据类型失配

这样的错误很可能是书写sql时的手误，但是往往造成极大的性能损失。

比如bigtable中id是varchar类型，但是查询时使用了如下语句

select * from bigtable where id = 12345678

查询时间：Time: 11646ms
如果将语句改为

select * from bigtable where id = '12345678'

查询时间：Time: 125ms

这样类似的错误往往会出现在复杂的查询语句当中，不容易被发现。

4. 索引误用

索引误用的例子很多，这里就举几个常见的。

建立索引时未使用选择性最高的方式，比如下面的语句

select * from bigtable where name = '12345678' and create_time between '2013-05-06' and '2013-07-04'

假设单查询create_time这个条件的总数，结果是201.2w条数据，而单查询name这个条件的总数，结果是18032条数据，显然，name这个条件比create_time这个条件更有区分度，因而选择性更高。单就这条语句而言，如果建索引，在name上会比在create_time上有效

索引的字段上不要使用额外计算，否则索引失效，事实上，这个是针对一类问题，比如

select * from bigtable where day(create_time)=1992 and month(create_time)=6

或是

select count(t.id) from bigtable t where t.grants & 1 != 0

都使用不上索引，这种情况往往会拖慢速度。

解决办法就是规避这些额外计算，比如

select * from bigtable where create_time between '1992-6-1' and '1992-6-1'

或者新建字段单独存储grants经常需要判断的权限位
复合索引未使用最左前缀原理
比如建立了一个复合索引<id, name, create_time>，

select count(t.id) from bigtable t where t.create_time = '2013-11-11'

查询时间：Time: 278406ms

这时未用到该复合索引，因而查询时间较慢，若要改进，可以在查询条件前面指定id, name，实在不能满足要求，则只能在create_time上单独建索引

5. like的全表扫描

like经常被用来做全文查询，但是在数据量稍大一点的查询中，like的速度绝对叫你想撞墙（传说以前12306就是用like做模糊查询），比如如下查询：

select * from bigtable where id like '%2346%'

查询时间：Time: 260699ms
explain的结果如下，用了全表扫描：

但是如果改用前缀查询，则能使用到索引，速度大幅提升：

select * from bigtable where id like '2346%'

查询时间：Time: 1ms

如果实在需要对关键字模糊匹配，可以考虑使用myISAM引擎的fulltext索引，或者使用全文索引的工具如lucene

mysql常用命令

登录

mysql -u root -p

授权

grant select, insert, update, delete, create, drop, REFERENCES on nfchina3.* to nfchina@'%';

暂且写到这里，稍后继续整理……

Mysql profile使用 

http://rickyliyi.blog.51cto.com/1308612/564944 

https://www.digitalocean.com/community/tutorials/how-to-use-mysql-query-profiling

sql的执行顺序 http://www.jellythink.com/archives/924 

MySQL性能优化的最佳20+条经验 http://coolshell.cn/articles/1846.html

mysql性能优化方案 http://001pp.com/chengxuyouhua/mysql%20xingnengyouhua2183.html

Mysql优化详细介绍 http://www.cnblogs.com/zhangbj8719/archive/2013/03/15/2961681.html

mysql加锁分析 http://hedengcheng.com/?p=771

解决mySQL占用内存超大问题 http://blog.csdn.net/dhping/article/details/5532828

索引的in和exists http://blog.csdn.net/hfred/article/details/6703775

浅谈mysql索引背后的数据结构及算法 http://blog.linezing.com/2011/07/%E6%B5%85%E8%B0%88mysql%E7%B4%A2%E5%BC%95%E8%83%8C%E5%90%8E%E7%9A%84%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%8F%8A%E7%AE%97%E6%B3%95

B树，B-树，B+树 http://blog.csdn.net/v_JULY_v/article/details/6530142

MySQL索引 使用笔记 http://www.perfgeeks.com/?p=460

sql的执行顺序等 http://blog.jobbole.com/55086/

http://www.cnblogs.com/yuanermen/archive/2010/07/22/1782833.html

http://www.111cn.net/database/mysql/50921.htm

http://blog.csdn.net/zqtsx/article/details/8929625

http://www.cnblogs.com/zhangbj8719/archive/2013/03/15/2961681.html

一个临时表优化案例 http://tech.uc.cn/?p=2218 

一些mysql相关知识的文章

存储过程 http://blog.chinaunix.net/uid-23302288-id-3785111.html

13个mysql数据库的实用SQL小技巧 http://www.gbtags.com/gb/share/2351.htm

mysql数据库迁移方案 http://my.oschina.net/u/204050/blog/87069

http://www.fovweb.com/web/mysql-database-migration.html