怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。

建立索引不是建的越多越好，原则是：

第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。

第二：建立索引的方法论为：

多数查询经常使用的列；

很少进行修改操作的列；

索引需要建立在数据差异化大的列上

利用以上的基础我们讨论一下如何优化sql.

1、sql语句模型结构优化指导

a. order by + limit组合的索引优化

如果一个sql语句形如：select [column1],[column2],…. from [table] order by [sort] limit [offset],[limit];

这个sql语句优化比较简单，在[sort]这个栏位上建立索引即可。

b. where + order by + limit组合的索引优化

如果一个sql语句形如：select [column1],[column2],…. from [table] where [columnx] = [value] order by [sort] limit [offset],[limit];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnx,sort)

c. where+order by多个栏位+limit

如果一个sql语句形如：select * from [table] where uid=1 order x,y limit 0,10;

对于这个语句，大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由mysql处理排序的机制造成的。

2、复合索引(形如(x,y,uid)索引的索引)

先看这样一条语句这样的：select* from users where area =’beijing’ and age=22;

如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果area，age两列上创建复合索引的话将带来更高的效率。

例如我们建立了一个这样的索引（area,age,salary），那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。

3、like语句优化

select id from a where name like ‘%abc%’

由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

select id from a where name like ‘abc%’

4、where子句使用 ！= 或 操作符优化

在where子句中使用 ！= 或 操作符，索引将被放弃使用，会进行全表查询。

如sql:select id from a where id != 5 优化成：select id from a where id>5 or id=‘2016-11-30’ and createdate"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"

18、尽可能使用更小的字段

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

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

19、inner join 和 left join、right join、子查询

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

select a.id,a.name,b.id,b.name from a left join b on a.id =b.id;

select a.id,a.name,b.id,b.name from a right join on b a.id= b.id;

select a.id,a.name,b.id,b.name from a inner join on a.id =b.id;

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

select a.id,a.name,b.id,b.name from a,b where a.id = b.id;

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

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

select* from a where exists (select * from b where id>=3000 and a.uuid=b.uuid);

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

select* from a inner join b on a.uuid=b.uuid using(uuid) where b.uuid>=3000; 这个语句执行测试不到一秒；

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

select * from a left join b a.id=b.ref_id where a.id>10;可以优化为：select * from (select * from a wehre id >10) t1 left join b on t1.id=b.ref_id;

20、exist 代替 in

select * from a where idin (select id from b)

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 操作的流程原理如同一下代码