本文共 9377 字，大约阅读时间需要 31 分钟。

本文来自：http://blog.csdn.net/kevinlifeng/article/details/43233227

Sql语句优化和索引

1.Innerjoin和左连接，右连接，子查询

A.     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连接

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

  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 using(uuid) where b.uuid>=3000;

  这个语句执行测试不到一秒；

C．在使用ON 和 WHERE 的时候，记得它们的顺序，如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id WHERE B.NAME=’XXX’

执行过程会先执行ON 后面先过滤掉B表的一些行数。然而WHERE是后再过滤他们两个连接产生的记录。

不过在这里提醒一下大家：ON后面的条件只能过滤出B表的条数，但是连接返回的记录的行数还是A表的行数是一样。如：

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

返回的记录数是A表的条数，ON后面的条件只起到过滤B表的记录数，而

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

返回的条数，是笛卡尔积后，符合A.id = B.id这个条件的记录

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

Select * from A left join B ona.id=B.ref_id where B.ref_id>10;

可以优化为：select * from (select * from A wehre id >10) T1 left join B onT1.id=B.ref_id;

2.建立索引,加快查询性能.

A．在建立复合索引的时候，在where条件中用到的字段在复合索引中，则最好把这个字段放在复合索引的最左端，这样才能使用索引，才能提高查询。

B．保证连接的索引是相同的类型，意思就是A表和B表相关联的字段，必须是同类型的。这些类型都建立了索引，这样才能两个表都能使用索引，如果类型不一样，至少有一个表使用不了索引。

C．索引，不仅仅是主键和唯一键，也可以是其他的任何列。在使用like其中一个有索引的字段列的时候。

如： select *from A name like ‘xxx%’;

这个sql会使用name的索引（前提name建立了索引）；而下面的语句就使用不了索引

Select * from A name like ‘%xxx’;

因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。

D.复合索引

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

如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果area，age两列上创建复合索引的话将带来更高的效率。如果我们创建了（area,age,salary）的复合索引，那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。因此我们在创建复合索引的应该将最常用作限制条件的列放在最左边，依次递减。

E.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.

F.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在钱10个或者20字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

G．排序的索引问题

Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

3.limit千万级分页的时候优化。

A．在我们平时用limit,如：

Select * from A order by id limit 1,10;

这样在表数据很少的时候，看不出什么性能问题，倘若到达千万级，如：

Select * from A order by id limit10000000,10;

虽然都是只查询10记录，但是这个就性能就让人受不了了。所以为什么当表数据很大的时候，我们还继续用持久层框架如hibernate,ibatis就会有一些性能问题，除非持久层框架对这些大数据表做过优化。

B．在遇见上面的情况，我们可以用另外一种语句优化，如：

Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;

确实这样快了很多，不过前提是，id字段建立了索引。也许这个还不是最优的，其实还可以这样写：

Select * from A where id between 10000000and 10000010;

这样的效率更加高。

4.尽量避免Select * 命令

A.从表中读取越多的数据，查询会变得更慢。它会增加磁盘的操作时间，还是在数据库服务器与web服务器是独立分开的情况下，你将会经历非常漫长的网络延迟。仅仅是因为数据不必要的在服务器之间传输。

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

 A．如果您真需要随机显示你的结果，有很多更好的途径实现。而这个函数可能会为表中每一个独立的行执行BY RAND()命令—这个会消耗处理器的处理能力，然后给你仅仅返回一行。

 

6.利用limit 1取得唯一行

 A．有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1.来终止数据库引擎继续扫描整个表或者索引,如：

Select * from A  where namelike ‘%xxx’ limit 1;

这样只要查询符合like ‘%xxx’的记录，那么引擎就不会继续扫描表或者索引了。

 

7.尽量少排序

A.排序操作会消耗较多的CPU资源，所以减少排序可以在缓存命中率高等

 

8.尽量少OR

 A.当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较地下，很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。

 

9.尽量用union all 代替union

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

10.避免类型转换

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

 

11.不要在列上进行运算

A. 如下面:select * fromusers where YEAR(adddate)<2007;将在每个行进行运算，这些导致索引失效进行全表扫描，因此我们可以改成：

Select * from users where adddate<’2007-01-01’;

 

12.尽量不要使用NOT IN和<>操作

A. NOT IN和<>操作都不会使用索引，而是将会进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可以使用id>3 or id <3;如果NOT EXISTS是子查询，还可以尽量转化为外连接或者等值连接，要看具体sql的业务逻辑。

B．把NOT IN转化为LEFT JOIN如：

SELECT * FROM customerinfo WHERE CustomerIDNOT in (SELECT CustomerID FROM salesinfo );

优化：

SELECT * FROM customerinfo LEFT JOINsalesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHEREsalesinfo.CustomerID IS NULL;

 

13.使用批量插入节省交互（最好是使用存储过程）

A. 尽量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);

 

14. 锁定表

A. 尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很多的应用系统中.由于事务执行的过程中,数据库将会被锁定,因此其他的用户请求只能暂时等待直到该事务结算.如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟.其实有些情况下我们可以通过锁定表的方法来获得更好的性能.如:

LOCK TABLE inventory write

Select quanity from inventory whereitem=’book’;

…

Update inventory set quantity=11 whereitem=’book’;

UNLOCK TABLES;

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到列表中。包含有write关键字的LOCK TABLE语句可以保证在UNLOCK TABLES命令被执行之前，不会有其他的访问来对inventory进行插入，更新或者删除的操作。

 

15.对多表关联的查询，建立视图

A．对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

==============================以下来自另一个博客

MySQL是一个强大的开源数据库。随着MySQL上的应用越来越多，MySQL逐渐遇到了瓶颈。这里提供 101 条优化 MySQL 的建议。有些技巧适合特定的安装环境，但是思路是相通的。我已经将它们分成了几类以帮助你理解。

MySQL监控

MySQL服务器硬件和OS（操作系统）调优：

1、有足够的物理内存，能将整个InnoDB文件加载到内存里 —— 如果访问的文件在内存里，而不是在磁盘上，InnoDB会快很多。

2、全力避免 Swap 操作 — 交换（swapping）是从磁盘读取数据，所以会很慢。

3、使用电池供电的RAM（Battery-Backed RAM）。

4、使用一个高级磁盘阵列 — 最好是 RAID10 或者更高。

5、避免使用RAID5 — 和校验需要确保完整性，开销很高。

6、将你的操作系统和数据分开，不仅仅是逻辑上要分开，物理上也要分开 — 操作系统的读写开销会影响数据库的性能。

7、将临时文件和复制日志与数据文件分开 — 后台的写操作影响数据库从磁盘文件的读写操作。

8、更多的磁盘空间等于更高的速度。

9、磁盘速度越快越好。

10、SAS优于SATA。

11、小磁盘的速度比大磁盘的更快，尤其是在 RAID 中。

12、使用电池供电的缓存 RAID（Battery-Backed Cache RAID）控制器。

13、避免使用软磁盘阵列。

14. 考虑使用固态IO卡（不是磁盘）来作为数据分区 — 几乎对所有量级数据，这种卡能够支持 2 GBps 的写操作。

15、在 Linux 系统上，设置 swappiness 的值为0 — 没有理由在数据库服务器上缓存文件，这种方式在Web服务器或桌面应用中用的更多。

16、尽可能使用 noatime 和 nodirtime 来挂载文件系统 — 没有必要为每次访问来更新文件的修改时间。

17、使用 XFS 文件系统 — 一个比ext3更快的、更小的文件系统，拥有更多的日志选项，同时，MySQL在ext3上存在双缓冲区的问题。

18、优化你的 XFS 文件系统日志和缓冲区参数 – -为了获取最大的性能基准。

19、在Linux系统中，使用 NOOP 或 DEADLINE IO 调度器 — CFQ 和 ANTICIPATORY 调度器已经被证明比 NOOP 和 DEADLINE 慢。

20、使用 64 位操作系统 — 有更多的内存能用于寻址和 MySQL 使用。

21、将不用的包和后台程序从服务器上删除 — 减少资源占用。

22、将使用 MySQL 的 host 和 MySQL自身的 host 都配置在一个 host 文件中 — 这样没有 DNS 查找。

23、永远不要强制杀死一个MySQL进程 — 你将损坏数据库，并运行备份。

24、让你的服务器只服务于MySQL — 后台处理程序和其他服务会占用数据库的 CPU 时间。

 

MySQL 配置：

25、使用 innodb_flush_method=O_DIRECT 来避免写的时候出现双缓冲区。

26、避免使用 O_DIRECT 和 EXT3 文件系统 — 这会把所有写入的东西序列化。

27、分配足够 innodb_buffer_pool_size ，来将整个InnoDB 文件加载到内存 — 减少从磁盘上读。

28、不要让 innodb_log_file_size 太大，这样能够更快，也有更多的磁盘空间 — 经常刷新有利降低发生故障时的恢复时间。

29、不要同时使用 innodb_thread_concurrency 和 thread_concurrency 变量 — 这两个值不能兼容。

30、为 max_connections 指定一个小的值 — 太多的连接将耗尽你的RAM，导致整个MySQL服务器被锁定。

31、保持 thread_cache 在一个相对较高的数值，大约是 16 — 防止打开连接时候速度下降。

32、使用 skip-name-resolve — 移除 DNS 查找。

33、如果你的查询重复率比较高，并且你的数据不是经常改变，请使用查询缓存 — 但是，在经常改变的数据上使用查询缓存会对性能有负面影响。

34、增加 temp_table_size — 防止磁盘写。

35、增加 max_heap_table_size — 防止磁盘写。

36、不要将 sort_buffer_size 的值设置的太高 — 可能导致连接很快耗尽所有内存。

37、监控 key_read_requests 和 key_reads，以便确定 key_buffer 的值 — key 的读需求应该比 key_reads 的值更高，否则使用 key_buffer 就没有效率了。

38、设置 innodb_flush_log_at_trx_commit = 0 可以提高性能，但是保持默认值（1）的话，能保证数据的完整性，也能保证复制不会滞后。

39、有一个测试环境，便于测试你的配置，可以经常重启，不会影响生产环境。

MySQL Schema 优化：

40、保证你的数据库的整洁性。

41、归档老数据 — 删除查询中检索或返回的多余的行

42、在数据上加上索引。

43、不要过度使用索引，评估你的查询。

44、压缩 text 和 blob 数据类型 — 为了节省空间，减少从磁盘读数据。

45、UTF 8 和 UTF16 比 latin1 慢。

46、有节制的使用触发器。

47、保持数据最小量的冗余 — 不要复制没必要的数据.

48、使用链接表，而不是扩展行。

49、注意你的数据类型，尽可能的使用最小的。

50、如果其他数据需要经常需要查询，而 blob/text 不需要，则将 blob/text 数据域其他数据分离。

51、经常检查和优化表。

52、经常做重写 InnoDB 表的优化。

53、有时，增加列时，先删除索引，之后在加上索引会更快。

54、为不同的需求选择不同的存储引擎。

55、日志表或审计表使用ARCHIVE存储引擎 — 写的效率更高。

56、将 session 数据存储在 memcache 中，而不是 MySQL 中 — memcache 可以设置自动过期，防止MySQL对临时数据高成本的读写操作。

57、如果字符串的长度是可变的，则使用VARCHAR代替CHAR — 节约空间，因为CHAR是固定长度，而VARCHAR不是（utf8 不受这个影响）。

58、逐步对 schema 做修改 — 一个小的变化将产生的巨大的影响。

59、在开发环境测试所有 schema 变动，而不是在生产环境的镜像上去做。

60、不要随意改变你的配置文件，这可能产生非常大的影响。

61、有时候，少量的配置会更好。

62、质疑使用通用的MySQL配置文件。

查询优化：

63、使用慢查询日志，找出执行慢的查询。

64、使用 EXPLAIN 来决定查询功能是否合适。

65、经常测试你的查询，看是否需要做性能优化 — 性能可能会随着时间的变化而变化。

66、避免在整个表上使用count(*) ，它可能会将整个表锁住。

67、保持查询一致，这样后续类似的查询就能使用查询缓存了。

68、如果合适，用 GROUP BY 代替 DISTINCT。

69、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。

70、保证索引简单，不要在同一列上加多个索引。

71、有时，MySQL 会选择错误的索引，这种情况使用 USE INDEX。

72、使用 SQL_MODE=STRICT 来检查问题。

73、索引字段少于5个时，UNION 操作用 LIMIT，而不是 OR。

74、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 来代替 UPDATE，避免 UPDATE 前需要先 SELECT。

75、使用索引字段和 ORDER BY 来代替 MAX。

76、避免使用 ORDER BY RAND()。

77、LIMIT M,N 在特定场景下会降低查询效率，有节制使用。

78、使用 UNION 来代替 WHERE 子句中的子查询。

79、对 UPDATE 来说，使用 SHARE MODE 来防止排他锁。

80、重启 MySQL 时，记得预热数据库，确保将数据加载到内存，提高查询效率。

81、使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以删除表中所有数据。

82、最小化你要查询的数据，只获取你需要的数据，通常来说不要使用 *。

83、考虑持久连接，而不是多次建立连接，已减少资源的消耗。

84、基准查询，包括服务器的负载，有时一个简单的查询会影响其他的查询。

85、当服务器的负载增加时，使用SHOW PROCESSLIST来查看慢的/有问题的查询。

86、在存有生产环境数据副本的开发环境中，测试所有可疑的查询。

MySQL备份过程：

87、在二级复制服务器上进行备份。

88、备份过程中停止数据的复制，以防止出现数据依赖和外键约束的不一致。

89、彻底停止MySQL之后，再从数据文件进行备份。

90、如果使用MySQL dump进行备份，请同时备份二进制日志 — 确保复制过程不被中断。

91、不要信任 LVM 快照的备份 — 可能会创建不一致的数据，将来会因此产生问题。

92、为每个表做一个备份，这样更容易实现单表的恢复 — 如果数据与其他表是相互独立的。

93、使用 mysqldump 时，指定 -opt 参数。

94、备份前检测和优化表。

95、临时禁用外键约束，来提高导入的速度。

96、临时禁用唯一性检查，来提高导入的速度。

97、每次备份完后，计算数据库/表数据和索引的大小，监控其增长。

98、使用定时任务（cron）脚本，来监控从库复制的错误和延迟。

99、定期备份数据。

100、定期测试备份的数据。

转载地址：https://blog.csdn.net/heqinghua217/article/details/78600967 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！