1.limit分页优化

当偏移量特别时，limit效率会非常低

1SELECTidFROMA LIMIT1000,10--超级快2SELECTidFROMA LIMIT90000,10--特别慢

　　优化方法:

    方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK。 因为用了id主键做索引当然快        方法二：select id, title from A where id>=( select id from collect order by id limit 90000,1) limit10;

    方法三：select id from A order by id between 10000000 and 10000010;

  2.批量插入优化

    1、INSERT into person ( name, age) values('A',14) 

    2、INSERT into person( name, age)values('B',14) 

    3、INSERT into person(name, age)values('C',14)

　　可优化为：

       1、INSERT into person(name, age)values('A',14),('B',14),('C',14)

    3.利用limit 1 、top 1 取得一行

有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

SELECTidFROMALIKE'abc%'--优化之前SELECTidFROMALIKE'abc%'limit1--优化之后

    4.尽量不要使用 BY RAND()命令

BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。

1SELECT*FROMAorderbyrand() limit1023SELECT*FROMAWHEREid>=((SELECTMAX(id)FROMA)-(SELECTMIN(id)FROMA))*RAND()+(SELECTMIN(id)FROMA) LIMIT10 --优化之后

    5.排序的索引问题　

Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

　6.尽量用 union all 替换 union

union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

    7.避免类型转换

这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。

例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"

　   8.尽可能使用更小的字段　　　　　　 

MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。

修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。

　9.Inner join 和 left join、right join、子查询

第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

1SELECTA.id,A.name,B.id,B.nameFROMALEFTJOINBONA.id=B.id;23SELECTA.id,A.name,B.id,B.nameFROMARIGHTJOINONB A.id= B.id;45SELECTA.id,A.name,B.id,B.nameFROMAINNERJOINONA.id=B.id;

　　经过来之多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：

1 SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id; 

推荐：能用inner join连接尽量使用inner join连接

第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

1Select*fromAwhereexists(select*fromBwhereid>=3000andA.uuid=B.uuid);

A表的数据为十万级表，B表为百万级表，在本机执行差不多用2秒左右，我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。

一种简单的优化就是用innerjoin的方法来代替子查询，查询语句改为：

1Select*fromAinnerjoinBONA.uuid=B.uuid using(uuid)whereb.uuid>=3000;  这个语句执行测试不到一秒；

第三：使用JOIN时候，应该用小的结果驱动打的结果

（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

1Select*fromAleftjoinB A.id=B.ref_idwhereA.id>10;可以优化为：select*from(select*fromA wehre id>10) T1leftjoinBonT1.id=B.ref_id;

10.exist 代替 in　

1、SELECT * from A WHERE id in ( SELECT id from B) 

2、SELECT * from A WHERE id EXISTS (SELECT 1 from A.id=B.id)

in 是在内存中遍历比较

exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in.

in()只执行一次，把B表中的所有id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记录。

in()适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况。