在MySQL数据库怎么大力推进查询速度，优化查询效率，主要原则就是应当尽量避免全表扫描，必须考虑在where及order by 涉及的罚以建立索引。

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

第一：一个表的索引不是越多越好，也没有一个具体内容的数字，根据以往的经验，一个表的索引最多无法少于6个，因为索引越多，对update和insert操作方式也可以有性能的影响，涉及至索引的新建和重建操作方式。

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

多数查询经常使用的列；
很少进行修正操作的列；
索引需要建立在数据差异化大的罚以
利用以上的基础我们讨论一下如何优化sql.

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

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<5

5、where子句中采用 IS NULL 或 IS NOT NULL 的优化

在where子句中采用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会展开全表查询。

如SQL:

SELECT id FROM A WHERE num IS NULL

 优化成num上设置默认值0，确保表num没null值，然后SQL为：SELECT id FROM A WHERE num=0

6、where子句采用or的优化

很多时候使用union all 或 nuin(必要的时候)的方式替代“or”可以得到更好的效果。where子句中使用了or,索引将被退出使用。

如SQL:

SELECT id FROM A WHERE num =10 or num = 20

 优化成：

SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

7、where子句采用IN 或 NOT IN的优化

in和not in 也必须禁用，否则也会导致全表读取。

方案一：between替换in

如SQL:

SELECT id FROM A WHERE num in(1,2,3) 优化成：SELECT id FROM A WHERE num between 1 and 3

方案二：exist替换in

如SQL:

SELECT id FROM A WHERE num in(select num from b )

 优化成：

SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

方案三：left join替换in

如SQL:

SELECT id FROM A WHERE num in(select num from B)

 优化成：

SELECT id FROM A LEFT JOIN B ON A.num = B.num

8、where子句中对字段进行表达式操作方式的优化

不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能将无法恰当使用索引。

如SQL:

SELECT id FROM A WHERE num/2 = 100

 优化成：

SELECT id FROM A WHERE num = 100*2

如SQL:

SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0

 优化成：

SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'

如SQL:

SELECT id FROM A WHERE year(addate) <2016

 优化成：

SELECT id FROM A where addate<'2016-01-01'

9、任何地方都不要用 select * from table ，用具体内容的字段列表替代"*"，不要回到用不到的字段

10、使用“临时表中”暂存中间结果

采用临时表暂存中间结果好处：

（1）防止程序中多次扫描主表，增加程序执行“共享锁”阻塞“更新锁”，增加了堵塞，提高了并发性能。

（2）尽量采用表中变量去替代临时表。如果表中变量涵盖大量数据，请注意索引非常有限（只有主键索引）。

（3）避免频繁建立和删掉临时表，以增加系统资源的浪费。

（4）尽量避免向客户端返回小数据量，若数据量过大，应当考虑相应市场需求是否合理。

11、limit分页优化

总数据有500万左右，以下例子

select * from wl_tagindex where byname='f' order by id limit 300000,10 继续执行时间是 3.21s

优化后：

select * from (
select id from wl_tagindex
where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id

执行时间为 0.11s 速度显著提升

这里需要表明的就是 我这里使用的字段就是 byname ,id 需要把这两个字段做无机索引，否则的话效果提升不显著

12、批量插入优化

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

可以优化为：

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

13、利用limit 1 、top 1 取得一行，但是须要注意的是，Top关键字就可以在SQL Server数据库中可以采用，而在MySQL数据库中就要采用具有同样功能的LIMIT函数

有时要查询一张表时，你知道只需要看看一条记录，你可能回去查询一条特定的记录。可以采用limit 1 或者 top 1 去中止数据库索引稳步读取整个表或索引。

如SQL：SELECT id FROM A LIKE 'abc%' 优化为：SELECT id FROM A LIKE 'abc%' limit 1

14、尽量不要采用 BY RAND()命令

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

如SQL：

SELECT * FROM A order by rand() limit 10

 优化为：

SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

15、排序的索引问题

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

尽量不要包含多个列于的排序，如果需要最出色给这些列创建无机索引。

16、尽量用 union all 替代 union

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

17、避免初始化

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

比如utime 是datetime类型，传入的参数就是“2016-07-23”，在比较小小时通常是 date（utime）>"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逐次继续执行子查询，如果外层集是一个很大的表，我们可以想象查询性能会整体表现比这个更加糟糕。

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

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

List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i
可以窥见，当B表中数据较大时不适宜采用in()，因为它会B表数据全部结点一次
例如：A展毛10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。
再例如：A展毛10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。
结论：in()适合B表比A表数据小的情况
exist()会执行A.length()次，执行过程代码如下
List resultSet={};
Array A=(select * from A);
for(int i=0;i
当B表比A表中数据小时适合采用exists()，因为它没那么多结点操作方式，只须要再继续执行一次查询就行。
例如：A表有10000条记录，B展毛1000000条记录，那么exists()会执行10000次去推论A表中的id是否与B表的id成正比。
如：A表有10000条记录，B展毛100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可知B表数据越多，越适宜exists()充分发挥效果。
再如：A表有10000条记录，B展毛100条记录，那么exists()还是继续执行10000次，还不如采用in()结点10000*100次，因为in()就是在内存里遍历比较，而exists()需要查询数据库，
我们都知道查询数据库所消耗的性能更高，而内存比较很快。
结论：exists()适宜B表比A表数据大的情况
当A表中数据与B表数据一样小时，in与exists效率差不多，可自由选择一个使用。

相关专题