"\u003cp\u003e在MySQL8.0以前，索引使用规则有一项是索引左前缀，假如说有一个索引idx_abc(a,b,c)，能用到索引的情况只有查询条件为a、ab、abc、ac这四种，对于只有字段b的where条件是无法用到这个idx_abcf索引的。这里再强调一下，这里的顺序并不是在where中字段出现的顺序，where b=2 and 1=1 也是可以利用到索引的，只是用到了(a,b)这两个字段\u003c/p\u003e\n\u003cp\u003e针对这一点， 从MySQL 8.0.13开始引入了一种新的优化方案，叫做 \u003cstrong\u003eSkip Scan Range\u003c/strong\u003e，翻译过来的话是\u003cstrong\u003e跳跃范围扫描\u003c/strong\u003e。如何理解这个概念呢？我们可以拿官方的SQL示例具体讲一下（）\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003eCREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));\nINSERT INTO t1 VALUES\n (1,1), (1,2), (1,3), (1,4), (1,5),\n (2,1), (2,2), (2,3), (2,4), (2,5);\nINSERT INTO t1 SELECT f1, f2 + 5 FROM t1; …\u003c/code\u003e\u003c/pre\u003e"

"\u003ch1 id=\"index-condition-pushdownicp\"\u003eIndex Condition Pushdown(ICP)\u003c/h1\u003e\n\u003cp\u003eIndex Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式。\u003c/p\u003e\n\u003ch3 id=\"icp原理\"\u003eICP原理\u003c/h3\u003e\n\u003cp\u003e禁用ICP，存储引擎会通过遍历索引定位基表中的行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。\u003c/p\u003e\n\u003cp\u003e开启ICP，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003eICP的目标是减少从基表中全纪录读取操作的数量，从而降低IO操作\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e对于InnoDB表，ICP只适用于辅助索引。\u003c/p\u003e\n\u003ch3 id=\"icp标识\"\u003eICP标识\u003c/h3\u003e\n\u003cp\u003e当使用ICP优化时，执行计划的Extra列显示 \u003cstrong\u003eUsing index condition\u003c/strong\u003e提示\u003c/p\u003e\n\u003ch3 id=\"相关参数\"\u003e相关参数\u003c/h3\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003eoptimizer_switch=\u0026#34;index_condition_pushdown=on”;\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e可以通过 SET optimizer_switch = …\u003c/p\u003e"

"\u003cp\u003e建议先阅读这篇文章： \u003ca href=\"http://hidba.org/?p=404\"\u003ehttp://hidba.org/?p=404\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003e下面我们只对其中提到的做一个验证。\u003c/p\u003e\n\u003cblockquote\u003e\n\u003cp\u003e(1).索引字段的附加信息：可以分为变长和定长数据类型讨论，当索引字段为定长数据类型，比如\u003cstrong\u003echar\u003c/strong\u003e，\u003cstrong\u003eint\u003c/strong\u003e，\u003cstrong\u003edatetime\u003c/strong\u003e，需要有是否为空的标记，这个标记需要占用1个字节；对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节；\u003c/p\u003e\n\u003cp\u003e(备注：当字段定义为非空的时候，是否为空的标记将不占用字节)\u003c/p\u003e\n\u003cp\u003e(2).同时还需要考虑表所使用的字符集，不同的字符集，gbk编码的为一个字符2个字节，utf8编码的一个字符3个字节， utf8mb4 编码则是4个字节;\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e每种MySQL数据类型的定义参考：\u003c/p\u003e\n\u003cp\u003e下面我们以定长数据类型准，变长数据类型请自行测试。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e一、数据索引类型允许为null的情况：\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e表结构：\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003eCREATE TABLE `tb` (\n`id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n`sid` smallint(5) DEFAULT NULL,\n`gid` smallint(5) DEFAULT NULL, …\u003c/code\u003e\u003c/pre\u003e"

"\u003cp\u003eMySQL使用基于 \u003cstrong\u003e成本的优化器\u003c/strong\u003e，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的 \u003ccode\u003elast_query_cost\u003c/code\u003e 的值来得到其计算当前查询的成本。\u003c/p\u003e\n\u003cdiv class=\"highlight\"\u003e\u003cpre tabindex=\"0\" style=\"color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;\"\u003e\u003ccode class=\"language-sql\" data-lang=\"sql\"\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003emysql\u003cspan style=\"color:#f92672\"\u003e\u0026gt;\u003c/span\u003e \u003cspan style=\"color:#66d9ef\"\u003eselect\u003c/span\u003e \u003cspan style=\"color:#f92672\"\u003e*\u003c/span\u003e \u003cspan style=\"color:#66d9ef\"\u003efrom\u003c/span\u003e t_message \u003cspan style=\"color:#66d9ef\"\u003elimit\u003c/span\u003e \u003cspan style=\"color:#ae81ff\"\u003e10\u003c/span\u003e;\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e...\u003cspan style=\"color:#960050;background-color:#1e0010\"\u003e省略结果集\u003c/span\u003e\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003emysql\u003cspan style=\"color:#f92672\"\u003e\u0026gt;\u003c/span\u003e \u003cspan style=\"color:#66d9ef\"\u003eshow\u003c/span\u003e status \u003cspan style=\"color:#66d9ef\"\u003elike\u003c/span\u003e \u003cspan style=\"color:#e6db74\"\u003e\u0026#39;last_query_cost\u0026#39;\u003c/span\u003e\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\u003cspan style=\"color:#f92672\"\u003e+\u003c/span\u003e\u003cspan style=\"color:#75715e\"\u003e-----------------+-------------+\n\u003c/span\u003e\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\u003cspan style=\"color:#75715e\"\u003e\u003c/span\u003e\u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e Variable_name \u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e Value \u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\u003cspan style=\"color:#f92672\"\u003e+\u003c/span\u003e\u003cspan style=\"color:#75715e\"\u003e-----------------+-------------+\n\u003c/span\u003e\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\u003cspan style=\"color:#75715e\"\u003e\u003c/span\u003e\u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e Last_query_cost\u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e \u003cspan style=\"color:#ae81ff\"\u003e6391\u003c/span\u003e.\u003cspan style=\"color:#ae81ff\"\u003e799000\u003c/span\u003e \u003cspan style=\"color:#f92672\"\u003e|\u003c/span\u003e\n\u003c/span\u003e\u003c/span\u003e\u003cspan style=\"display:flex;\"\u003e\u003cspan\u003e\u003cspan style=\"color:#f92672\"\u003e+\u003c/span\u003e\u003cspan style=\"color:#75715e\"\u003e-----------------+-------------+\n\u003c/span\u003e\u003c/span\u003e\u003c/span\u003e\u003c/code\u003e\u003c/pre\u003e\u003c/div\u003e\u003cp\u003e示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括： \u003cstrong\u003e每张表或者索引的页面个数\u003c/strong\u003e、 \u003cstrong\u003e索引的基数\u003c/strong\u003e、 \u003cstrong\u003e索引\u003c/strong\u003e 和 \u003cstrong\u003e数据行的长度\u003c/strong\u003e、 \u003cstrong\u003e索引的分布\u003c/strong\u003e 情况等等。\u003c/p\u003e\n\u003cp\u003e有非常多 …\u003c/p\u003e"

"\u003ch2 id=\"导读\"\u003e导读\u003c/h2\u003e\n\u003cblockquote\u003e\n\u003cp\u003e在MySQL里，聚集索引和非聚集索引分别是什么意思，有什么区别？\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e在MySQL中，InnoDB引擎表是（聚集）索引组织表（clustered index organize table），而MyISAM引擎表则是堆组织表（heap organize table）。\u003c/p\u003e\n\u003cp\u003e也有人把聚集索引称为聚簇索引。\u003c/p\u003e\n\u003cp\u003e当然了，聚集索引的概念不是MySQL里特有的，其他数据库系统也同样有。\u003c/p\u003e\n\u003cp\u003e简言之，\u003cstrong\u003e聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序\u003c/strong\u003e，而非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。\u003c/p\u003e\n\u003cp\u003e我们先来看看两种存储形式的不同之处：\u003c/p\u003e\n\u003cp\u003e简单说，IOT表里数据物理存储顺序和主键索引的顺序一致，所以\u003cstrong\u003e如果新增数据是离散的，会导致数据块趋于离散\u003c/strong\u003e，而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。\nIOT表相比HOT表的\u003cstrong\u003e优势\u003c/strong\u003e是：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003e范围查询效率更高；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e数据频繁更新（聚集索引本身不更新）时，更不容易产生碎片；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e特别适合有一小部分热点数据频繁读写的场景；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e通过主键访问数据时快速可达；\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003eIOT表的\u003cstrong\u003e不足\u003c/strong\u003e则有：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e数据变化如果 …\u003c/li\u003e\u003c/ul\u003e"

"\u003cp\u003e其实对于非专业的数据库操作人员来讲，例如软件开发人员，在很大程度上都搞不清楚数据库索引的一些基本知识，有些是知其一不知其二，或者是知其然不知其所以然。造成这种情况的主要原因我觉的是行业原因，有很多公司都有自己的DBA团队，他们会帮助你优化SQL，开发人员即使不懂优化问题也不大，所以开发人员对这方面也就不会下太多功夫去了解SQL优化，但如果公司没有这样的DBA呢，就只能靠程序员自己了。 最近突然想起前一阵和一朋友的聊天，当时他问我的问题是一个非常普通的问题：说说SQL聚集索引和非聚集索引的区别。\u003c/p\u003e\n\u003cp\u003e大家可能认为这个问题难度不大，认为太熟悉了，也许不会感兴趣，但你真能说清楚吗？其实要想说明白这两者的差别也不是三两句就说的清的，那天我也是觉的这问题太泛了，就随便说了其中的两个区别：\u003c/p\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003e聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个，这个跟没问题没差别，一般人都知道。\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续，这个大家也都知道。\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003cp\u003e上面的两点从大的方面讲都是讲的通的，后面我们继续探讨，举一个实际点的例子，一个学生表student，里面是学生 …\u003c/p\u003e"

"\u003cp\u003e本次，我们通过对比，明明白白的知道索引、提交频率对InnoDB表写入速度的影响，了解有哪些需要注意的。\u003c/p\u003e\n\u003cp\u003e先直接说几个结论吧：\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003e1、关于索引对写入速度的影响：\na、如果有自增列做主键，相对完全没索引的情况，写入速度约提升 3.11%；\nb、如果有自增列做主键，并且二级索引，相对完全没索引的情况，写入速度约降低 27.37%；\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e因此，\u003cstrong\u003eInnoDB表最好总是有一个自增列做主键\u003c/strong\u003e。\u003c/p\u003e\n\u003cp\u003e2、关于提交频率对写入速度的影响（以表中只有自增列做主键的场景，一次写入数据30万行数据为例）：\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003ea、等待全部数据写入完成后，最后再执行commit提交的效率最高；\nb、每10万行提交一次，相对一次性提交，约慢了1.17%；\nc、每1万行提交一次，相对一次性提交，约慢了3.01%；\nd、每1千行提交一次，相对一次性提交，约慢了23.38%；\ne、每100行提交一次，相对一次性提交，约慢了24.44%；\nf、每10行提交一次，相对一次性提交，约慢了92.78%；\ng、每行提交一次，相对一次性提交，约慢了546.78%，也就是慢了5倍；\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e因此，\u003cstrong\u003e最好是等待所有事务结束后再批量提交，而不是每执行完一个SQL就提交一次\u003c/strong\u003e。\u003c/p\u003e\n\u003cp\u003e曾经 …\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003eMySQL在以下几种情况会创建临时表：\u003c/strong\u003e\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003e1、UNION查询；\n2、用到TEMPTABLE算法或者是UNION查询中的视图；\n3、ORDER BY和GROUP BY的子句不一样时；\n4、表连接中，ORDER BY的列不是驱动表中的；\n5、DISTINCT查询并且加上ORDER BY时；\n6、SQL中用到SQL_SMALL_RESULT选项时；\n7、FROM中的子查询；\n8、子查询或者semi-join时创建的表；\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003eEXPLAIN 查看执行计划结果的 Extra 列中，如果包含 ** \u003ca href=\"http://imysql.com/2015/06/14/mysql-faq-what-important-information-in-explain.shtml\"\u003eUsing Temporary\u003c/a\u003e** 就表示会用到临时表。\u003c/p\u003e\n\u003cp\u003e当然了，如果临时表中需要存储的数据量超过了上限（ \u003ca href=\"https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size\"\u003etmp-table-size\u003c/a\u003e 或 \u003ca href=\"https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_heap_table_size\"\u003emax-heap-table-size\u003c/a\u003e 中取其大者），这时候就需要生成基于磁盘的临时表了。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e在以下几种情况下，会创建磁盘临时表：\u003c/strong\u003e\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003e1、数据表中包含BLOB/TEXT列；\n2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；\n3、 …\u003c/code\u003e\u003c/pre\u003e"

"\u003cp\u003e我们先了解下InnoDB引擎表的一些关键特征：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003eInnoDB引擎表是基于B+树的索引组织表(IOT)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e每个表都需要有一个聚集索引(clustered index)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e所有的行记录都存储在B+树的叶子节点(leaf pages of the tree)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e基于聚集索引的增、删、改、查的效率相对是最高的；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003e综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003e使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e该表不指定自增列做主键，同时也没有可 …\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e"

"\u003cp\u003e话说有这么一个表：\u003c/p\u003e\n\u003cp\u003eCREATE TABLE \u003ccode\u003euser_group\u003c/code\u003e (\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003eid\u003c/code\u003e int(11) NOT NULL auto_increment,\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003euid\u003c/code\u003e int(11) NOT NULL,\u003c/p\u003e\n\u003cp\u003e\u003ccode\u003egroup_id\u003c/code\u003e int(11) NOT NULL,\u003c/p\u003e\n\u003cp\u003ePRIMARY KEY  (\u003ccode\u003eid\u003c/code\u003e),\u003c/p\u003e\n\u003cp\u003eKEY \u003ccode\u003euid\u003c/code\u003e (\u003ccode\u003euid\u003c/code\u003e),\u003c/p\u003e\n\u003cp\u003eKEY \u003ccode\u003egroup_id\u003c/code\u003e (\u003ccode\u003egroup_id\u003c/code\u003e),\u003c/p\u003e\n\u003cp\u003e) ENGINE=InnoDB AUTO_INCREMENT=750366 DEFAULT CHARSET=utf8\u003c/p\u003e\n\u003cp\u003e看AUTO_INCREMENT就知道数据并不多，75万条。然后是一条简单的查询：\u003c/p\u003e\n\u003cp\u003e　　SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;\u003c/p\u003e\n\u003cp\u003e很简单对不对?怪异的地方在于：\u003c/p\u003e\n\u003cp\u003e如果换成MyISAM做存储引擎的时候，查询耗时只需要0.01s，用InnoDB却会是0.15s左右。\u003c/p\u003e\n\u003cp\u003e如果只是就这么点差距其实不是什么大不了的事，但是真实的业务需求比这个复杂，造成的差距也很大：MyISAM只需要0.12s，InnoDB则需要2.2s.，最终定位到问题症结是在这 …\u003c/p\u003e"

"\u003cp\u003e由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。\u003c/p\u003e\n\u003cp\u003e**　　数据类型选择**\u003c/p\u003e\n\u003cp\u003e数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。\u003c/p\u003e\n\u003cp\u003e我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题：\u003c/p\u003e\n\u003cp\u003e1、数字类型：非万不得已不要使用DOUBLE，不仅仅只是存 …\u003c/p\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"

"\u003cpre tabindex=\"0\"\u003e\u003ccode\u003e生产环境下建议关闭此功能，因绝大部分场景下此选项会产生效率低下问题。\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e\u003cstrong\u003equery_cache_size = 64M\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：\u003c/p\u003e\n\u003cblockquote\u003e\n\u003cp\u003e# \u0026gt; SHOW VARIABLES LIKE ‘%query_cache%’;\n# \u0026gt; SHOW STATUS LIKE ‘Qcache%’;\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况;\n如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲;\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003eQcache_free_blocks\u003c/strong\u003e，如果该值非常大，则表明缓冲区中碎片很多。\u003c/p\u003e\n\u003cp\u003e“Qcache_free_blocks”：Query Cache 中目前还有多少剩余的blocks。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理。\n● “Qcache_free_memory”：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query …\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\u003eMySQL服务器端的参数有很多，但是对于大多数初学者来说，众多的参数往往使得我们不知所措，但是哪些参数是需要我们调整的，哪些对服务器的性能影响最大呢？对于使用Myisam存储引擎来说，主要有key_buffer_size和table_cache两个参数。对于InnoDB引擎来说主要还是以innodb_开始的参数，也很好辨认。\n查看MySQL参数，可以使用\u003cstrong\u003eshow variables\u003c/strong\u003e和\u003cstrong\u003eshow status\u003c/strong\u003e命令查看，前者查看服务器静态参数，即在数据库启动后不会动态更改的值，比如缓冲区、字符集等。后者查看服务器的动态运行状态信息，即数据库运行期间动态变化的信息，比如锁，当前连接数等。\u003c/p\u003e\n\u003cp\u003ekey_buffer_size这个参数是用来设置索引块（index blocks）缓存的大小，它被所有线程共享，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。那我们怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads，比例key_reads / key_read_requests 应该尽可能的低，比 …\u003c/p\u003e"

"\u003cp\u003e\u003cstrong\u003e12.1优化表的数据类型\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。\u003c/p\u003e\n\u003cp\u003e我们可以使用PROCEDUREANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。\u003c/p\u003e\n\u003cp\u003e语法：\u003c/p\u003e\n\u003cp\u003eSELECT * FROM tbl_name PROCEDURE ANALYSE();SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);\u003c/p\u003e\n\u003cp\u003e输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。\u003c/p\u003e\n\u003cp\u003e在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e12.2通过拆分，提高表的访问效率这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e1.  纵 …\u003c/strong\u003e\u003c/p\u003e"

"\u003cp\u003eMySQL还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。\u003c/p\u003e\n\u003cp\u003e我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。\u003c/p\u003e\n\u003cp\u003e下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的，对于Innodb存储引擎，语句的执行是由获得行锁的顺序决定的。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003eMySQL的默认的调度策略可用总结如下：\u003c/strong\u003e\u003c/p\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003e写入操作优先于读取操作。\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e对某张数据表的多个读取操作可以同时地进行。\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003cp\u003e\u003cstrong\u003eMySQL提供了几个语句调节符，允许你修改它的调度策略：\u003c/strong\u003e\u003c/p\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003eLOW_PRIORITY关键字应用于DELETE、INSERT、LOADDATA、REPLACE和UPDATE。\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eHIGH_PRIORITY关键字应用于SELECT和INSERT语句。\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eDELAYED关键字应用于INSERT和REPLACE语句。\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003cp\u003e如果写入操作是一 …\u003c/p\u003e"

"\u003cp\u003eMysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN).. 替代。\u003c/p\u003e\n\u003cp\u003e假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：\u003c/p\u003e\n\u003cp\u003eSELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID  FROM salesinfo)\u003c/p\u003e\n\u003cp\u003e如果使用连接(JOIN)..来完成这个查询工作，速度将会快很多。尤其是当salesinfo 表中对CustomerID建有索引的话，性能将会更好，查询如下：\u003c/p\u003e\n\u003cp\u003eSELECT * FROM customerinfo\nLEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID\nWHERE salesinfo.CustomerID IS NULL\u003c/p\u003e\n\u003cp\u003e连 …\u003c/p\u003e"

"\u003cp\u003e在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同， 并且order by的字段都是升序或者都是降序。\u003c/p\u003e\n\u003cp\u003e例如：下列sql可以使用索引。\u003c/p\u003e\n\u003cp\u003eSELECT * FROM t1 ORDER BY key_part1,key_part2,…;\u003c/p\u003e\n\u003cp\u003eSELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC;\u003c/p\u003e\n\u003cp\u003eSELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC;\u003c/p\u003e\n\u003cp\u003e**但是以下情况不使用索引： **\u003c/p\u003e\n\u003cp\u003eSELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC；\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e–orderby的字段混合ASC和DESC\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e**\n**\u003c/p\u003e\n\u003cp\u003eSELECT * FROM t1 WHERE key2=constant ORDER BY key1；\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e–用于查询行的关键字与ORDERBY中所使用的不相同\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e**\n** …\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\u003e修改my.cnf的mysqld部分：\nlong_query_time = 1              //定义慢查询的时间1表示1秒\n–log-slow-queries[=file_name]   //记录慢查询到日志文件\n–log-queries-not-using-indexes //将没使用索引的sql记录到日志文件\n实例：\n[mysqld]\nlong_query_time = 1\nlog-slow-queries = /usr/local/mysql5.0.40/var/slow_query.log\nlog-queries-not-using-indexes = true\u003c/p\u003e\n\u003cp\u003e“too many connections”找不到问题所在,后来发现打开mysql的慢查询会有很大的帮助就搞了一个.\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e打开MySQL慢查询\u003c/strong\u003e\nMySQL慢查询记录日志对于跟踪PHP+MySQL体系下的MySQL负载调优问题很有用处，比如安装了很多Discuz!插件的用户，这样可以大概排查出那些插件有代码问题。其实启用MySQL的慢查询日志很简单,只需要在MySQL的配置文件里添 …\u003c/p\u003e"

"\u003cp\u003e在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io 资源，连 PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL 查询返回的结果。由此可见，提高系统的负载能力，降低 MySQL 的资源消耗迫在眉睫。\n**1、页面缓存功能：\n** 页面缓存功能降低MySQL的资源消耗的（系统本身就已经考虑，采用生成HTML页面，大大降低了数据库的压力）。\n\u003cstrong\u003e2、mysql服务器的优化\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e2.1、修改全站搜索\n修改my.ini(my.cnf) ，在 [mysqld] 后面加入一行“ft_min_word_len=1”，然后重启Mysql，再登录网站后台（模块管理-\u0026gt;全站搜索）重建全文索引。\n2.2、记录慢查询sql语句，修改my.ini(my.cnf)，添加如下代码：\u003c/p\u003e\n\u003cp\u003e#log-slow-queries\nlong_query_time = 1 #是指执行超过多久的 sql 会被 log 下来\nlog-slow-queries = E:/wamp/logs/slow.log #设置把日志写在那里，可以 …\u003c/p\u003e"