"\u003cp\u003e作/译者：叶金荣（imysql#imysql.com\u0026gt;），来源： \u003ca href=\"http://imysql.com/\"\u003ehttp://imysql.com\u003c/a\u003e，欢迎转载。\u003c/p\u003e\n\u003cp\u003e起因：在innodb表上做count(*)统计实在是太慢了，因此想办法看能不能再快点。\u003c/p\u003e\n\u003cp\u003e现象：先来看几个测试案例，如下\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e一、 sbtest 表上的测试\u003c/strong\u003e\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003eshow create table sbtest\\G\n*************************** 1. row ***************************\nTable: sbtest\nCreate Table: CREATE TABLE `sbtest` (\n`aid` bigint(20) unsigned NOT NULL auto_increment,\n`id` int(10) unsigned NOT NULL default \u0026#39;0\u0026#39;,\n`k` int(10) unsigned NOT NULL default \u0026#39;0\u0026#39;,\n`c` char(120) NOT NULL default \u0026#39;\u0026#39;,\n`pad` char(60) NOT NULL …\u003c/code\u003e\u003c/pre\u003e"

"\u003cp\u003e在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题，所以最近打算写一个MySQL数据库性能优化方面的系列文章，希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助。\u003c/p\u003e\n\u003cp\u003e这是本系列的第一篇文章：MySQL 数据库性能优化之缓存参数优化\u003c/p\u003e\n\u003cp\u003e数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。\u003cstrong\u003e\u003cem\u003e本文先从 MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化\u003c/em\u003e\u003c/strong\u003e：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\u003cstrong\u003equery_cache_size/query_cache_type （global）\u003c/strong\u003e\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003eQuery cache 作用于整个 MySQL Instance，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache 功能，MySQL在接受到一 …\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003eMySQL程序剖析 （Profiling）\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e我们将要详细的讲到MySQL的剖析(Profiling),因为它很少依赖于你的应用。应用和服务器 级别的剖析有的时候都是有必要的。虽然应用级别的剖析可以给你整个应用性能的总揽。，但是对MySQL的剖析提供了信息是服务器级别所提供不了的。比如， 对PHP代码进行剖析不会显示MySQL有多少行语句执行了。\u003c/p\u003e\n\u003cp\u003e与应用剖析一样，目标是找出MySQL哪部分消耗过多的时间。我们不会剖析MySQL源码的，虽然有的 时候定制化MySQL安装很有用，但是这是另一本书的主题了。所替代的是，我们将教你一些可以技术来获取和分析不同种类的MySQL执行语句的信息。\u003c/p\u003e\n\u003cp\u003e你可以用在任意的颗粒级别以满足你的需求：你可能对整个服务器进行剖析或者单独检查一个语句或者一组语 句。下列信息你可以一点点的收集：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003eMySQL经常访问的那些数据\u003c/li\u003e\n\u003cli\u003eMySQL经常执行语句的类型\u003c/li\u003e\n\u003cli\u003eMySQL线程大部分时间的状态\u003c/li\u003e\n\u003cli\u003eMySQL经常执行语句的子系统\u003c/li\u003e\n\u003cli\u003eMySQL执行语句所访问的数据类型\u003c/li\u003e\n\u003cli\u003e不同活动的类型，比如扫描索引。\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003e我们先从范围最广的剖析开始，那就是服务器剖析，将教你更多细节。 …\u003c/p\u003e"

"\u003cp\u003e查看索引使用情况\u003c/p\u003e\n\u003cp\u003e如果索引正在工作， Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。\u003c/p\u003e\n\u003cp\u003eHandler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。\u003c/p\u003e\n\u003cp\u003e语法：\u003c/p\u003e\n\u003cblockquote\u003e\n\u003cp\u003emysql\u0026gt; show status like ‘Handler_read%’;\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e有关更多MySQL之Handler_read_*介绍参考：\u003c/p\u003e"

"\u003cp\u003e\u003ca href=\"https://dev.mysql.com/doc/refman/5.7/en/show-profile.html\"\u003ehttps://dev.mysql.com/doc/refman/5.7/en/show-profile.html\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003e此功能将在新版本中被移除，性能分析使用新方法来代替。(官方提供了此命令的使用方法, 对于 show profile for query ID / show profile  \u003cstrong\u003eCPU\u003c/strong\u003e for query ID 结果中每项的说明信息请参考： \u003ca href=\"https://www.cnblogs.com/itcomputer/articles/5056127.html\"\u003ehttps://www.cnblogs.com/itcomputer/articles/5056127.html\u003c/a\u003e）\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003eNote\u003c/strong\u003e\u003c/p\u003e\n\u003cblockquote\u003e\n\u003cp\u003eThese statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see \u003ca href=\"https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html\" title=\"Chapter 25 MySQL Performance Schema\"\u003eChapter 25, \u003cem\u003eMySQL Performance Schema\u003c/em\u003e\u003c/a\u003e.\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e对于新版本我们也可以直接查询  \u003ccode\u003eINFORMATION_SCHEMA\u003c/code\u003e \u003ca href=\"https://dev.mysql.com/doc/refman/8.0/en/profiling-table.html\" title=\"25.20 The INFORMATION_SCHEMA PROFILING Table\"\u003e\u003ccode\u003ePROFILING\u003c/code\u003e\u003c/a\u003e . See \u003ca href=\"https://dev.mysql.com/doc/refman/8.0/en/profiling-table.html\" title=\"25.20 The INFORMATION_SCHEMA PROFILING Table\"\u003eSection 25.20, “The INFORMATION_SCHEMA PROFILING …\u003c/a\u003e\u003c/p\u003e"

"\u003cp\u003e在MySQL里，我们一般使用 \u003ca href=\"http://dev.mysql.com/doc/refman/5.0/en/show-status.html\"\u003eSHOW STATUS\u003c/a\u003e 查询服务器状态，语法一般来说如下：\u003c/p\u003e\n\u003cp\u003eSHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]\u003c/p\u003e\n\u003cp\u003e执行命令后会看到很多内容，其中有一部分是Handler_read_*，它们显示了数据库处理SELECT查询语句的状态，对于调试SQL语句有很大意 义，可惜实际很多人并不理解它们的实际意义，本文简单介绍一下：\u003c/p\u003e\n\u003cp\u003e为了让介绍更易懂，先建立一个测试用的表：\u003c/p\u003e\n\u003cp\u003eCREATE TABLE IF NOT EXISTS \u003ccode\u003efoo\u003c/code\u003e (\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003eid\u003c/code\u003e int(10) unsigned NOT NULL auto_increment,\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003ecol1\u003c/code\u003e varchar(10) NOT NULL,\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003ecol2\u003c/code\u003e text NOT NULL,\u003c/p\u003e\n\u003cp\u003ePRIMARY KEY (\u003ccode\u003eid\u003c/code\u003e),\u003c/p\u003e\n\u003cp\u003eKEY \u003ccode\u003ecol1\u003c/code\u003e (\u003ccode\u003ecol1\u003c/code\u003e)\u003c/p\u003e\n\u003cp\u003e);\u003c/p\u003e\n\u003cp\u003eINSERT INTO `foo` (`id`, `col1`, `col2`) VALUES\n(1, ‘a’, ‘a’),\n(2, ‘b’, ‘b’),\n(3, ‘c’, ‘c’),\n(4, ‘d’, ‘d’), …\u003c/p\u003e"

"\u003cp\u003e对于SQL查询语句对于服务器系统资源的使用情况见：发现瓶颈 – Profiling(程序剖析) -MySQL Profiling\u003c/p\u003e\n\u003cp\u003e网上有很多的文章教怎么配置MySQL服务器，但考虑到服务器硬件配置的不同，具体应用的差别，那些文章的做法只能作为初步设置参考，我们需要根据自己的 情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后运行，根据服务器的”状态”进行优化。\u003c/p\u003e\n\u003cp\u003emysql\u0026gt; show global status;\u003c/p\u003e\n\u003cp\u003e可以列出MySQL服务器运行各种状态值，另外，查询MySQL服务器配置信息语句：\u003c/p\u003e\n\u003cp\u003emysql\u0026gt; show variables;\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e一、慢查询\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003emysql\u0026gt; show variables like ‘%slow%’;\n+——————+——-+\n| Variable_name    | Value |\n+——————+——-+\n| log_slow_queries | ON    |\n| slow_launch_time | 2     |\n+——————+——-+\u003c/p\u003e\n\u003cp\u003emysql\u0026gt; show global status like …\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003eExplain ID详解\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e含义：select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003eid的情况有三种，分别是：\u003c/strong\u003e\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003eid相同表示加载表的顺序是从上到下。\u003c/li\u003e\n\u003cli\u003eid不同id值越大，优先级越高，越先被执行。\u003c/li\u003e\n\u003cli\u003eid有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003e再看一个查询计划的例子：\u003c/p\u003e\n\u003cp\u003e\u003ca href=\"https://blog.haohtml.com/wp-content/uploads/2010/06/mysql_explain.png\"\u003e\u003cimg src=\"https://blogstatic.haohtml.com//uploads/2023/09/mysql_explain.png\" alt=\"\"\u003e\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e执行顺序依次为 4 -\u0026gt; 3 -\u0026gt; 2 \u0026gt; 1 \u0026gt; NULL\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e第一行：id列为1，表示第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。[select d1.name……]\u003c/p\u003e\n\u003cp\u003e第二行：id为3，表示该查询的执行次序为2（4→3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。[select id,name from t1 where other_column=”]\u003c/p\u003e\n\u003cp\u003e第三 …\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003e比较IN()里面的数据\u003c/strong\u003e\n许多数据库服务器都只把IN()看作多个OR的同义词，因为它们在逻辑上是相等的。MYSQL不是这样的，它会对IN()里面的数据进行排序，然后用二分法查找个是否在列表中，这个算法的效率是Ｏ（Ｌogn),而等同的OR子句的查找效率是Ｏ(n)。在列表很大的时候，OR子句就会变得慢得多。\u003c/p\u003e\n\u003cp\u003e这里的语句和Oracle数据库里是一样的。\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003e1.1优化SQL的一般步骤\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e11.1.1\u003c/strong\u003e \u003cstrong\u003e通过show status和应用特点了解各种SQL的执行频率\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e通过SHOW STATUS可以提供服务器状态信息，也可以使用mysqladminextended- status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global 级别的统计结果。\u003c/p\u003e\n\u003cp\u003e以下几个参数对Myisam和Innodb存储引擎都计数：\u003c/p\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003eCom_select 执行select操作的次数，一次查询只累加1；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eCom_insert执行insert操作的次数，对于批量插入的insert操作，只累加一次；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eCom_update执行update操作的次数；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eCom_delete 执行delete操作的次数；\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003cp\u003e以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：\u003c/p\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003eInnodb_rows_read select查询返回的行数；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eInnodb_rows_inserted执行Insert操作插入的行数；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eInnodb_rows_updated执行update操作更新的行数； …\u003c/p\u003e\u003c/li\u003e\u003c/ol\u003e"

"\u003cp\u003eMYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。\u003c/p\u003e\n\u003cp\u003e同样是取10条数据\u003c/p\u003e\n\u003cp\u003eselect * from yanxue8_visit limit 10000,10 和\u003c/p\u003e\n\u003cp\u003eselect * from yanxue8_visit limit 0,10\u003c/p\u003e\n\u003cp\u003e就不是一个数量级别的。\u003c/p\u003e\n\u003cp\u003e网上也很多关于limit的五条优化准则，都是翻译自mysql手册，虽然正确但不实用。今天发现一篇文章写了些关于limit优化的，很不错。\u003c/p\u003e\n\u003cp\u003e文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。（测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19）\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e1、offset比较小的时候。\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003eselect * from yanxue8_visit limit 10,10\u003c/p\u003e\n\u003cp\u003e多次运行，时间保持在0.0004-0.0005之间\u003c/p\u003e\n\u003cp\u003eSelect * From …\u003c/p\u003e"