SQL 优化

SQL优化的一般步骤

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

通过show status可以提供服务器状态信息,show status可以根据需要显式session级别的统计结果和global 级别的统计结果.

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

可以通过以下两种方式定位执行效率地的sql语句,

  • 通过慢查询日志定位那些优化效率较低的sql语句…但是慢查询日志在查询结束后才记录,所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题.
  • 使用show processlist 命令查看当前MYSQL在运行的线程,包括线程的状态,是否锁表等等,可以实时查看SQL执行情况
通过explain 分析低效SQL的执行计划:

通过以上步骤查询到效率低的 SQL 后,我们可以通过 explain 或者 desc 获取MySQL 如何执行 SELECT 语句的信息,包括 select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的 SELECT。

确定问题,采取相应的优化措施

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

索引问题

索引的存储分类

myisam 表的数据文件和索引文件是自动分开的,innoDB的数据和索引是存储在同一个表空间里面的,但是可以有多个文件组成.

索引的语法:

创建索引: 

1
2
3
CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] 
ON tbl_name (index_col_name,...)
index_col_name的格式:  col_name [(length)][ASC | DESC]

索引的存储类型目前只有两种(btree和hash) ,具体和表的模式有关.

myisam ->btree

innoDB ->btree

memory/heap -> hash btree

mysql 如何使用索引

索引用于快速找出在某个列中有某一特定值的行,对相关列使用索引是提高select操作性能的最佳途径.

下列情况中,MySQL不会使用已有的索引

1 如果MySQL估计使用索引比全表扫描更慢,则不会使用索引.

2 如果使用 heap 表并且 where 条件中不用=索引列,其他> 、<、 >=、 <=均不使用索引

3 如果不是索引列的第一部分

4 如果like 是以% 开始

5 对 where 后边条件为字符串的一定要加引号,字符串如果为数字 mysql 会自动转为字符串,但是不使用 索引。

查看索引使用情况

如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用 。
Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引。

两个简单实用优化方法

定期分析表

ANALYZE TABLE

语法:

1
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

本语句用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。这对于 MyISAM, BDB 和 InnoDB 表有作用。对于 MyISAM 表,本语句与使用 myisamchk -a 相当。

CHECK TABLE

语法:

1
2
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于MyISAM 表,关键字统计数据被更新。CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。

CHECKSUM TABLE

语法:

1
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

报告一个表校验和。

优化表

OPTIMIZE TABLE

语法:

1
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的 INSERT 操作会重新使用旧的记录位置。您可以使用 OPTIMIZE TABLE 来重新利用未使用的空间,并整理数据文件的碎片。OPTIMIZE TABLE 只对 MyISAM, BDB 和 InnoDB 表起作用。

常用SQL优化

大批量插入数据

1因为 Innodb 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果 Innodb 表没有主键,那么系统会默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高导入数据的效率。

2在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。

3 如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。

insert优化

1如果你同时从同一客户插入很多行,使用多个值表的 INSERT 语句。这比使用分开INSERT 语句快。

2如果你从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。Delayed 的含义是让 insert 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;这比每条语句分别插入要快的多;

3将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);

4 如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 myisam 表使用;

5 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多INSERT 语句快 20 倍;

6 根据应用情况使用 replace 语句代替 insert;

7 根据应用情况使用 ignore 关键字忽略重复记录。

group by 优化

默认情况下group by 会先进行排序, 如果查询包括 GROUP BY 但你想要避免排序结果的消耗,你可以指定 ORDER BY NULL 禁止排序。

order by 优化

在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序 。where 条件和 order by 使用相同的索引,并且 order by 的顺序和索引顺序相同 ,并且 order by 的字段都是升序或者都是降序。

join 优化

子查询可以被更有效率的连接(JOIN)替代。

连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

or 优化

对于 or 子句,如果要利用索引,则 or 之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。

查询优先还是更新优先

MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。

其他优化措施

1 使用持久的连接数据库以避免连接开销。

2 经常检查所有查询确实使用了必要的索引。

3 避免在频繁更新的表上执行复杂的 SELECT 查询,以避免与锁定表有关的由于读、写冲突发生的问题。

4 对于没有删除的行操作的 MyISAM 表,插入操作和查询操作可以并行进行,因为没有删除操作的表查询期间不会阻塞插入操作.对于确实需要执行删除操作的表,尽量在空闲时间进行批量删除操作,避免阻塞其他操作。

5.充分利用列有默认值的事实。只有当插入的值不同于默认值时,才明确地插入值。这减少 MySQL 需要做的语法分析从而提高插入速度。

6.对经常访问的可以重构的数据使用内存表,可以显著提高访问的效率。

7.通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份期间对应用的影响,可以在复制服务器上执行备份操作.

8,表的字段尽量不使用自增长变量,在高并发情况下该字段的自增可能对效率有比较大的影响,推荐通过应用来实现字段的自增长。