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

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

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

"MySQL使用基于 成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。\nmysql\u0026gt; select * from t_message limit 10; ...省略结果集 mysql\u0026gt; show status like \u0026#39;last_query_cost\u0026#39; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost| 6391.799000 | +-----------------+-------------+ 示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括： 每张表或者索引的页面个数、 索引的基数、 索引 和 数据行的长度、 索引的分布 情况等等。\n有非常多的原因会导 …"

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

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

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

"MySQL在以下几种情况会创建临时表：\n1、UNION查询； 2、用到TEMPTABLE算法或者是UNION查询中的视图； 3、ORDER BY和GROUP BY的子句不一样时； 4、表连接中，ORDER BY的列不是驱动表中的； 5、DISTINCT查询并且加上ORDER BY时； 6、SQL中用到SQL_SMALL_RESULT选项时； 7、FROM中的子查询； 8、子查询或者semi-join时创建的表； EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 ** Using Temporary** 就表示会用到临时表。\n当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），这时候就需要生成基于磁盘的临时表了。\n在以下几种情况下，会创建磁盘临时表：\n1、数据表中包含BLOB/TEXT列； 2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）； 3、 …"

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

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

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

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

"生产环境下建议关闭此功能，因绝大部分场景下此选项会产生效率低下问题。 query_cache_size = 64M\n指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：\n# \u0026gt; SHOW VARIABLES LIKE ‘%query_cache%’; # \u0026gt; SHOW STATUS LIKE ‘Qcache%’;\n# 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况; 如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲;\nQcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。\n“Qcache_free_blocks”：Query Cache 中目前还有多少剩余的blocks。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，可能需要寻找合适的机会进行整理。 ● “Qcache_free_memory”：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query …"

"https://dev.mysql.com/doc/refman/5.7/en/show-profile.html\n此功能将在新版本中被移除，性能分析使用新方法来代替。(官方提供了此命令的使用方法, 对于 show profile for query ID / show profile CPU for query ID 结果中每项的说明信息请参考： https://www.cnblogs.com/itcomputer/articles/5056127.html）\nNote\nThese statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Chapter 25, MySQL Performance Schema.\n对于新版本我们也可以直接查询 INFORMATION_SCHEMA PROFILING . See Section 25.20, “The INFORMATION_SCHEMA PROFILING …"

"在MySQL里，我们一般使用 SHOW STATUS 查询服务器状态，语法一般来说如下：\nSHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]\n执行命令后会看到很多内容，其中有一部分是Handler_read_*，它们显示了数据库处理SELECT查询语句的状态，对于调试SQL语句有很大意 义，可惜实际很多人并不理解它们的实际意义，本文简单介绍一下：\n为了让介绍更易懂，先建立一个测试用的表：\nCREATE TABLE IF NOT EXISTS foo (\nid int(10) unsigned NOT NULL auto_increment,\ncol1 varchar(10) NOT NULL,\ncol2 text NOT NULL,\nPRIMARY KEY (id),\nKEY col1 (col1)\n);\nINSERT INTO `foo` (`id`, `col1`, `col2`) VALUES (1, ‘a’, ‘a’), (2, ‘b’, ‘b’), (3, ‘c’, ‘c’), (4, ‘d’, ‘d’), …"

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

"MySQL服务器端的参数有很多，但是对于大多数初学者来说，众多的参数往往使得我们不知所措，但是哪些参数是需要我们调整的，哪些对服务器的性能影响最大呢？对于使用Myisam存储引擎来说，主要有key_buffer_size和table_cache两个参数。对于InnoDB引擎来说主要还是以innodb_开始的参数，也很好辨认。 查看MySQL参数，可以使用show variables和show status命令查看，前者查看服务器静态参数，即在数据库启动后不会动态更改的值，比如缓冲区、字符集等。后者查看服务器的动态运行状态信息，即数据库运行期间动态变化的信息，比如锁，当前连接数等。\nkey_buffer_size这个参数是用来设置索引块（index blocks）缓存的大小，它被所有线程共享，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。那我们怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads，比例key_reads / key_read_requests 应该尽可能的低，比 …"

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

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

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

"在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同， 并且order by的字段都是升序或者都是降序。\n例如：下列sql可以使用索引。\nSELECT * FROM t1 ORDER BY key_part1,key_part2,…;\nSELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC;\nSELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC;\n**但是以下情况不使用索引： **\nSELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC；\n–orderby的字段混合ASC和DESC\n** **\nSELECT * FROM t1 WHERE key2=constant ORDER BY key1；\n–用于查询行的关键字与ORDERBY中所使用的不相同\n** ** …"

"1.1优化SQL的一般步骤\n11.1.1 通过show status和应用特点了解各种SQL的执行频率\n通过SHOW STATUS可以提供服务器状态信息，也可以使用mysqladminextended- status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global 级别的统计结果。\n以下几个参数对Myisam和Innodb存储引擎都计数：\nCom_select 执行select操作的次数，一次查询只累加1；\nCom_insert执行insert操作的次数，对于批量插入的insert操作，只累加一次；\nCom_update执行update操作的次数；\nCom_delete 执行delete操作的次数；\n以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：\nInnodb_rows_read select查询返回的行数；\nInnodb_rows_inserted执行Insert操作插入的行数；\nInnodb_rows_updated执行update操作更新的行数；\nInnodb_rows_deleted 执行delete操作删除的行 …"

"修改my.cnf的mysqld部分： long_query_time = 1 //定义慢查询的时间1表示1秒 –log-slow-queries[=file_name] //记录慢查询到日志文件 –log-queries-not-using-indexes //将没使用索引的sql记录到日志文件 实例： [mysqld] long_query_time = 1 log-slow-queries = /usr/local/mysql5.0.40/var/slow_query.log log-queries-not-using-indexes = true\n“too many connections”找不到问题所在,后来发现打开mysql的慢查询会有很大的帮助就搞了一个.\n打开MySQL慢查询 MySQL慢查询记录日志对于跟踪PHP+MySQL体系下的MySQL负载调优问题很有用处，比如安装了很多Discuz!插件的用户，这样可以大概排查出那些插件有代码问题。其实启用MySQL的慢查询日志很简单,只需要在MySQL的配置文件里添加log-slow-queries …"

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