[MySQL优化案例]系列 — 分页优化

通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL:

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者像下面这个不带任何条件的分页SQL:

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

一般而言,分页SQL的耗时随着 start 值的增加而急剧增加,我们来看下面这2个不同起始值的分页SQL执行耗时: Continue reading

mysql中innodb表的count优化

作/译者:叶金荣(imysql#imysql.com>),来源:http://imysql.com,欢迎转载。

起因:在innodb表上做count(*)统计实在是太慢了,因此想办法看能不能再快点。
现象:先来看几个测试案例,如下
一、 sbtest 表上的测试

show create table sbtest\G
*************************** 1. row ***************************
Table: sbtest
Create Table: CREATE TABLE `sbtest` (
`aid` bigint(20) unsigned NOT NULL auto_increment,
`id` int(10) unsigned NOT NULL default '0',
`k` int(10) unsigned NOT NULL default '0',
`c` char(120) NOT NULL default '',
`pad` char(60) NOT NULL default '',
PRIMARY KEY  (`aid`),
KEY `k` (`k`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1

show index from sbtest;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| sbtest |          0 | PRIMARY  |            1 | aid         | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
| sbtest |          1 | k        |            1 | k           | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| sbtest |          1 | id       |            1 | id          | A         |     1000099 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

填充了 100万条 记录。

show index from 表名介绍:http://blog.haohtml.com/archives/1889 Continue reading

MySQL 数据库性能优化之缓存参数优化[转载]

在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助。

这是本系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化

数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化

  • query_cache_size/query_cache_type (global)

Query cache 作用于整个 MySQL Instance,主要用来缓存 MySQL 中的 ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache 功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache 中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。 Continue reading

发现瓶颈 – Profiling(程序剖析) -MySQL Profiling

MySQL程序剖析 (Profiling)

我们将要详细的讲到MySQL的剖析(Profiling),因为它很少依赖于你的应用。应用和服务器 级别的剖析有的时候都是有必要的。虽然应用级别的剖析可以给你整个应用性能的总揽。,但是对MySQL的剖析提供了信息是服务器级别所提供不了的。比如, 对PHP代码进行剖析不会显示MySQL有多少行语句执行了。

与应用剖析一样,目标是找出MySQL哪部分消耗过多的时间。我们不会剖析MySQL源码的,虽然有的 时候定制化MySQL安装很有用,但是这是另一本书的主题了。所替代的是,我们将教你一些可以技术来获取和分析不同种类的MySQL执行语句的信息。 Continue reading

查看mysql索引使用情况

查看索引使用情况

如果索引正在工作, Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引。

语法:

mysql> show status like ‘Handler_read%’;

有关更多MySQL之Handler_read_*介绍参考:http://blog.haohtml.com/archives/4262

show profiles 详解

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

此功能将在新版本中被移除,性能分析使用新方法来代替。(官方提供了此命令的使用方法, 对于 show profile for query ID / show profile  CPU  for query ID 结果中每项的说明信息请参考:https://www.cnblogs.com/itcomputer/articles/5056127.html

 

Note

These statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 25, MySQL Performance Schema.

对于新版本我们也可以直接查询  INFORMATION_SCHEMA PROFILING . See Section 25.20, “The INFORMATION_SCHEMA PROFILING Table”.

例如:

SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2 ORDER BY SEQ;

MySQL Query Cache 小结

最近经常有人问我 MySQL Query Cache 相关的问题,就整理一点 MySQL Query Cache 的内容,以供参考。

顾名思义,MySQL Query Cache 就是用来缓存和 Query 相关的数据的。具体来说,Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集。大概来讲,就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中。 Continue reading

MySQL之Handler_read_*

在MySQL里,我们一般使用SHOW STATUS查询服务器状态,语法一般来说如下:

SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]

执行命令后会看到很多内容,其中有一部分是Handler_read_*,它们显示了数据库处理SELECT查询语句的状态,对于调试SQL语句有很大意 义,可惜实际很多人并不理解它们的实际意义,本文简单介绍一下: Continue reading

根据status信息对MySQL服务器进行优化[精典]

对于SQL查询语句对于服务器系统资源的使用情况见:发现瓶颈 – Profiling(程序剖析) -MySQL Profilinghttp://blog.haohtml.com/archives/4624

网上有很多的文章教怎么配置MySQL服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的 情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。

mysql> show global status;

可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句:

mysql> show variables;

一、慢查询

mysql> show variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+

mysql> show global status like ‘%slow%’;
+———————+——-+
| Variable_name       | Value |
+———————+——-+
| Slow_launch_threads | 0     |
| Slow_queries        | 4148 |
+———————+——-+

配置中打开了记录慢查询,执行时间超过2秒的即为慢查询,系统显示有4148个 慢查询,你可以分析慢查询日志,找出有问题的SQL语句,慢查询时间不宜设置过长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑 给MySQL打补丁:http://www.percona.com/docs/wiki/release:start,记得找对应的版本。

打开慢查询日志可能会对系统性能有一点点影响,如果你的MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查 询,对系统性能影响又小。 Continue reading

MySQL性能优化的最佳20+条经验

今天,数据库的操作越来越成 为整个应用的性能瓶颈了,这点对于 Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事 情。当我们去设计数据库表结构,对 操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的 性能。这里,我们不会 讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
1. 为查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时 候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。 Continue reading