小谈mysql优化-CSDN博客

mysql性能的优化很重要，可以提高查询的速度。

那如果优化大致的步骤有那些哪？

1. 通过 show status和应用特点了解各种 SQL的执行频率

通过 SHOW STATUS 可以提供服务器状态信息，也可以使用 mysqladmin extende d-status 命令获得。 SHOW STATUS 可以根据需要显示 session 级别的统计结果和 global级别的统计结果。

如显示当前session： SHOW STATUS like "Com_%"; 全局级别：show global status;
以下几个参数对 Myisam 和 Innodb 存储引擎都计数：

1. Com_select 执行 select 操作的次数，一次查询只累加 1 ；

2. Com_insert 执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次；

3. Com_update 执行 update 操作的次数；

4. Com_delete 执行 delete 操作的次数；

以下几个参数是针对 Innodb 存储引擎计数的，累加的算法也略有不同：

1. Innodb_rows_read select 查询返回的行数；

2. Innodb_rows_inserted 执行 Insert 操作插入的行数；

3. Innodb_rows_updated 执行 update 操作更新的行数；

4. Innodb_rows_deleted 执行 delete 操作删除的行数；

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于我们了解数据库的基本情况：

1. Connections 试图连接 Mysql 服务器的次数
2. Uptime 服务器工作时间
3. Slow_queries 慢查询的次数

2. 定位执行效率较低的SQL语句

可以通过以下两种方式定位执行效率较低的 SQL 语句：
1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=file_name] 选项启动时， mysqld 写一个包含所有执行时间超过long_query_time 秒的 SQL 语句的日志文件。
2. 使用 show processlist查看当前MYSQL的线程， 命令慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看 SQL 执行情况，同时对一些锁表操作进行优化。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table
　正在检查数据表（这是自动的）。
Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
　复制从服务器正在连接主服务器。
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
　正在为GROUP BY做排序。
　Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

3. 通过EXPLAIN 分析低效 SQL的执行计划：

通过以上步骤查询到效率低的 SQL 后，我们可以通过 explain 或者 desc 获取MySQL 如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接的次序。

4. MySQL索引

怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在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<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 substring(name,1,3) = 'abc' 优化成：SELECT id FROM A WHERE LIKE 'abc%'

　　如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分页优化

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

　　　　SELECT id FROM A LIMIT 1000,10 很快

　　　　SELECT id FROM A LIMIT 90000,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) limit 10;

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

2、批量插入优化

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 取得一行

　　　　有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用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 add 替换 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)

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)

21. 尽量使用表变量来代替临时表。

如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

22.避免频繁创建和删除临时表，以减少系统表资源的消耗。

23.临时表并不是不可使用，

适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

24.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；

如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

25. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

COUNT优化：

26.count(*) 优于count(1)和count(primary_key)

　　很多人为了统计记录条数，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对 count(*) 计数操作做了一些特别的优化。

27.count(column) 和 count(*) 是不一样的

　　这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和 count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。

　　count(column) 是表示结果集中有多少个column字段不为空的记录

　　count(*) 是表示整个结果集有多少条记录

1)innodb引擎在统计方面和myisam是不同的，Myisam内置了一个计数器，

Count(*)在没有查询条件的情况下使用 select count(*) from table 的时候，Myisam直接可以从计数器中取出数据。而innodb必须全表扫描一次方能得到总的数量

2. 但是当有查询条件的时候，两者的查询效率一致。

4. 主键索引count(*)的时候之所以慢

InnoDB引擎:

[1] 数据文件和索引文件存储在一个文件中，主键索引默认直接指向数据存储位置。

[2] 二级索引存储指定字段的索引，实际的指向位置是主键索引。当我们通过二级索引统计数据的时候，无需扫描数据文件；而通过主键索引统计数据时，由于主键索引与数据文件存放在一起，所以每次都会扫描数据文件，所以主键索引统计没有二级索引效率高。

[3] 由于主键索引直接指向实际数据，所以当我们通过主键id查询数据时要比通过二级索引查询数据要快。

l MyAsm引擎

[1] 该引擎把每个表都分为几部分存储，比如用户表，包含user.frm，user.MYD和user.MYI。

[2] User.frm负责存储表结构

[3] User.MYD负责存储实际的数据记录，所有的用户记录都存储在这个文件中

[4] User.MYI负责存储用户表的所有索引，这里也包括主键索引。

优化GROUP BY

默认情况下， MySQL 排序所有 GROUP BY col1 ， col2 ， .... 。查询的方法如同在查询中指定 ORDER BY col1 ， col2 ， ... 。如果显式包括一个包含相同的列的 ORDER BY
子句， MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序。
例如：
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

转载于:https://my.oschina.net/u/3359933/blog/1607842