优化SQL的一般步骤
1.1通过show status和应用特点了解各种SQL的执行频率。对于事务型的应用, 通过Com_commit 和 Com_rollback 可以了解事务提交和回滚 的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。此外,以下几个参数便于我们了解数据库的基本情况:
A. Connections 试图连接 Mysql 服务器的次数
B. Uptime 服务器工作时间
C. Slow_queries 慢查询的次数1.2定位执行效率较低的 SQL 语句,可以通过以下两种方式定位执行效率较低的 SQL 语句:
A.可以通过慢查询日志定位那些执行效率较低的 sql 语句,用–log-slow- queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
B.慢查询日志在查询结束以后才纪录, 所 以在应用反映执行效率出现问题的时候查询 慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在 进行的线程,包括线程的状态,是否锁表等等,可以实时的查看SQL 执行情况,同 时对一些锁表操作进行优化。1.3通过 EXPLAIN 分析低效 SQL 的执行计划:通过以上步骤查询到效率低的 SQL 后,我们可以通过 explain 或者 desc 获取 MySQL 如何执行 SELECT 语句的信息,包括select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录 的更快的 SELECT。EXPLAIN中常见的参数如下：
A. select_type:select类型
B. table:输出结果集的表
C. type:表示表的连接类型
当表中仅有一行是type的值为system是最佳的连接类型;
当select操作中使用索引进行表连接时type的值为ref;
当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表 进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。
D. possible_keys:表示查询时，可以使用的索引列
E. key:表示使用的索引
F. key_len:索引长度
G. rows:扫描范围
H. Extra:执行情况的说明和描述1.4确定问题,并采取相应的优化措施:经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率。
索引问题
2.1 索引的存储分类
myisam 表的数据文件和索引文件是自动分开的;innodb 的数据和索引是存储在 同一个表空间里面,但可以有多个文件组成。创建索引语法如下:
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/head hash,btree
mysql 目前不支持函数索引,只能对列的前一部分(length)进行索引,例:
create index ind_test on table_name(name(4)),对于 char 和 varchar 列,使用前缀索引将大大节省空间。
2.2MySQL 如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。
查询要使用索引最主要的条件是查询条件中需要使用索引关键字, 如果是多列索 引, 那 么只有查询条件使用了多列关键字最左边的前缀时, 才 可以使用索引, 否 则将 不能使用索引。
下列情况下,Mysql 不会使用已有的索引:- 如果 mysql 估计使用索引比全表扫描更慢,则不使用索引。例如:如果key_part1
均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 - 如果使用 heap 表并且 where 条件中不用=索引列,其他> 、<、 >=、 <=均不使用 索引;
- 如果不是索引列的第一部分;
- 如果 like 是以%开始;
对 where 后边条件为字符串的一定要加引号, 字 符串如果为数字mysql 会自动转为字符串,但是不使用索引。
2.3查看索引使用情况
如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索 引值读的次数, 很低的值表明增加索引得到的性能改善不高, 因为索引并不经常用。使
Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补 救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描, 该值较高。通常说明表索引不正确或写入的查询没有利用索引。语法:
mysql> show status like ‘Handler_read%’;常用 SQL 的优化
3.1大批量插入数据:- 对于 Myisam 类型的表,可以通过以下方式快速的导入大量的数据。 ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭 Myisam 表非唯一索引的更新。在导入大量的数据到一 个非空的 Myisam 表时,通过设置这两个命令,可以提高导入的效率。对于导入大量 数据到一个空的 Myisam 表,默认就是先导入数据然后才创建索引的,所以不用进行 设置。 - 而对于 Innodb 类型的表, 这 种方式并不能提高导入数据的效率。 对于Innodb 类型 的表,我们有以下几种方式可以提高导入的效率:
a. 因为 Innodb 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺 序排列,可以有效的提高导入数据的效率。如果 Innodb 表没有主键,那么系统会默认 创建一个内部列作为主键, 所 以如果可以给表创建一个主键, 将 可以利用这个优势提高 导入数据的效率。
b. 在导入数据前执行 SET UNIQUE_CHECKS=0, 关闭唯一性校验, 在导入结束后执SE行T UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。
c. 如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动 提交,导入结束后再执行SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。
3.2优化 insert 语句: - 如果你同时从同一客户插入很多行,使用多个值表的 INSERT 语句。这比使用分开INSERT 语句快(在一些情况中几倍)。 Insert into test values(1,2),(1,3),(1,4)…
- 如果你从不同客户插入很多行, 能 通过使用INSERT DELAYED 语句得到更高的速度。
Delayed 的含义是让 insert 语句马上执行,其实数据都被放在内存的队列中,并 没有真正写入磁盘;这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反, 在所有其他用户对表的读写完后才进行插入; - 将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项) ;
- 如果进行批量插入,可以增加 bulk_insert_buffer_siz e 变量值的方法来提高速
度,但是,这只能对 myisam 表使用; - 当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多
INSERT 语句快 20 倍; - 根据应用情况使用 replace 语句代替 insert;
- 根据应用情况使用 ignore 关键字忽略重复记录。
3.3优化 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;
3.4优化 order by 语句:
在某些情况中,MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排 序。where 条件和 order by 使用相同的索引, 并且order by 的顺序和索引顺序相同, 并且 order by 的字段都是升序或者都是降序。
例如:下列 sql 可以使用索引。
SELECT * FROM t1 ORDER BY key_part1,key_part2,… ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2
DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情况不使用索引:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
–order by 的字段混合 ASC 和 DESC:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
–用于查询行的关键字与 ORDER BY 中所使用的不相同:
SELECT * FROM t1 ORDER BY key1, key2;
–对不同的关键字使用 ORDER BY;
3.5mysql 如何优化 or 条件:
对于 or 子句,如果要利用索引,则or 之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。
3.6其他措施 - 使用持久的连接数据库以避免连接开销。
- 经常检查所有查询确实使用了必要的索引。
- 避免在频繁更新的表上执行复杂的 SELECT 查询,以避免与锁定表有关的由于读、写
冲突发生的问题。 - 对于没有删除的行操作的 MyISAM 表,插入操作和查询操作可以并行进行,因为没有
删除操作的表查询期间不会阻塞插入操作. 对 于确实需要执行删除操作的表, 尽 量在空
闲时间进行批量删除操作,避免阻塞其他操作。 - 充分利用列有默认值的事实。 只 有当插入的值不同于默认值时, 才 明确地插入值。 这减
少 MySQL 需要做的语法分析从而提高插入速度。 - 对经常访问的可以重构的数据使用内存表,可以显著提高访问的效率。
- 通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份期间对应用的影响,可以在复制服务器上执行备份操作。
表的字段尽量不使用自增长变量, 在 高并发情况下该字段的自增可能对效率有比较大的影响,推荐通过应用来实现字段的自增长。
2 . Mysql 日志管理
2.1 错误日志:
包含了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相 关信息文件位置和格式:可以用–log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。 如果 没有给定file_name值,mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。
2.2慢查询日志：
记录内容:
记录包含所有执行时间超过long_query_time秒的SQL语句的日志文件。获得初使表 锁定的时间不算作执行时间。
文件位置和格式
用–log-slow-queries[=file_name]选项启动它。如果没有给出file_name值, 默 认为主机名, 后缀为-slow.log。 如果给出了文件名, 但 不是绝对路径名, 文件则写 入数据目录。
快速查看:
使用mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志,例如: [zzx@bj37 data]$ mysqldumpslow bj37-slow.log
其他选项:
在MySQL 5.1中,通过–log-slow-admin-statements服务器选项, 你可以请求将慢管理语句,例如OPTIMIZE TABLE、ANALYZE TABLE和 ALTER TABLE写入慢查询日志。
3 .应急处理：
如果数据库在运行过程中出现任何异常,一般按照如下步骤解决: 查看错误日志。错误日志一般放在数据库的 data 目录下通过 perror 工具查看错误号,判断错误出现的原因 寻找解决方案。结合show processlist命令分析各个进程，并且通过top命令观察cpu占用。
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
