mysql优化方法

标签: mysql 优化 方法 | 发表时间:2014-08-23 14:52 | 作者:zhq_zhq
出处:http://www.iteye.com
通过show status和应用特点了解各种SQL的执行频率

通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果。
以下几个参数对Myisam和Innodb存储引擎都计数:
Com_select  执行select操作的次数,一次查询只累加1;
Com_insert 执行insert操作的次数,对于批量插入的insert操作,只累加一次;
Com_update 执行update操作的次数;
Com_delete 执行delete操作的次数。

以下几个参数是针对Innodb存储引擎计数的,累加的算法也略有不同:
Innodb_rows_read select查询返回的行数;
Innodb_rows_inserted执行Insert操作插入的行数;
Innodb_rows_updated 执行update操作更新的行数;
Innodb_rows_deleted 执行delete操作删除的行数。

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

对于事务型的应用,通过Com_commit和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。

此外,以下几个参数便于我们了解数据库的基本情况:
Connections 试图连接Mysql服务器的次数
Uptime    服务器工作时间
Slow_queries 慢查询的次数
定位执行效率较低的SQL语句

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

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

explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type   | table | type  | possible_keys| key            | key_len   | rows  | Extra     |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE    | b     | index | PRIMARY   | PRIMARY   | 4     |    1  | Using index   |
| SIMPLE    | a     | ALL   | NULL      | NULL      | NULL  |   12  | Using where   |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
2 rows in set (0.02 sec)

说明:
select_type:select 类型
table:输出结果集的表
type:表示表的连接类型
①当表中仅有一行是type的值为system是最佳的连接类型;
②当select操作中使用索引进行表连接时type的值为ref;
③当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。
possible_keys:表示查询时,可以使用的索引列.
key:表示使用的索引
key_len:索引长度
rows:扫描范围
Extra:执行情况的说明和描述
确定问题,并采取相应的优化措施

经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率。

例如上面的例子,我们确认是对a表的全表扫描导致效率的不理想,我们对a表的year字段创建了索引,查询需要扫描的行数明显较少。

mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| select_type   | table | type  | possible_keys| key            | key_len   | rows  | Extra     |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
| SIMPLE    | b     | index | PRIMARY   | PRIMARY   | 4     |    1  | Using index   |
| SIMPLE    | a     | ref       | year      | year      | 4     |    3  | Using where   |
+----------------+----------+-----------+----------------+----------------+----------+-----------+----------------+
2 rows in set (0.02 sec)

已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql 优化 方法] 推荐:

mysql优化方法

- - 数据库 - ITeye博客
通过show status和应用特点了解各种SQL的执行频率. 通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 以下几个参数对Myisam和Innodb存储引擎都计数:.

[MySQL优化案例]系列 — slave延迟很大优化方法

- - MySQL中文网
备注:插图来自网络搜索,如果觉得不当还请及时告知 :). 一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发. 简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MYSQL查询SQL语句性能优化方法

- - 数据库 - ITeye博客
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,. Sql 代码 : select id from t where num is null;.

MYSQL分页limit速度太慢优化方法

- - OurMySQL
   在mysql中limit可以实现快速分页,但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了,否则可能卡死你的服务器哦.    当一个表数据有几百万的数据的时候成了问题.    如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢,可以按照一下方法解决.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.