MySQL数据库优化（七）

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略 它采用一种机制将最经常访问的表保存在内存区块中

对索引区块来说 它维护着一个叫索引缓存(索引缓冲)的结构体 这个结构体中放著许多那些最常使用的索引区块的缓冲区块 对数据区块来说 MySQL没有使用特定的缓存 它依靠 *** 作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本 *** 作 然后讨论在MySQL 中所做的改进 它提高了索引缓存性能 同时能更好地控制缓存 *** 作

线程之间不再是串行地访问索引缓存 多个线程可以并行地访问索引缓存 可以设置多个索引缓存 同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用 不过 这种有效性正在减弱 自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小 如果这个值大小为 那么就不使用缓存 当这个值小得于不足以分配区块缓冲的最小数量( )时 也不会使用缓存

当索引缓存无法 *** 作时 索引文件就只通过 *** 作系统提供的本地文件系统缓冲来访问(换言之 表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元 通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的 这个树的底部时叶子节点 叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的 这个值可能等于 大于 或小于表的索引区块大小 通常这两个值是不一样的

当必须访问来自任何表的索引区块时 服务器首先检查在索引缓存中是否有可用的缓冲区块 如果有 服务器就访问缓存中的数据 而非磁盘 就是说 它直接存取缓存 而不是存取磁盘 否则 服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块 将它的内容替换成请求表的索引区块的拷贝 一旦新的索引区块在缓存中了 索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时 这个区块就被认为 脏 了 那么 在替换之前 它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略 当要选择替换的区块时 它选择最近最少使用的索引区块 为了想要让选择变得更容易 索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了 就把它放到队列的最后位置 当区块要被替换时 在队列开始位置的区块就是最近最少使用的 它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前 访问索引缓存是串行的 两个线程不能并行地访问索引缓存缓冲 服务器处理一个访问索引区块的请求只能等它之前的请求处理完 结果 新的请求所需的索引区块就不在任何索引缓存环冲区块中 因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始 服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时 需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求 只要它们不干扰别的线程(也就是 它们请求不同的索引区块 因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能 却不能完全消除线程间的冲突 它们仍然争抢控制管理存取索引缓存缓冲的结构 为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性 这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存 服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个 默认地 所有的 MyISAM 表索引都缓存在默认的索引缓存中 想要指定到特定的缓存中 可以使用 CACHE INDEX 语句

如下语句所示 指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

mysql> CACHE INDEX t  t  t  IN hot_cache+ + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | + + + + +

注意 如果服务器编译支持存 ISAM 储引擎了 那么 ISAM 表也使用索引缓存机制 不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的 如下 mysql>SET GLOBAL keycache key_buffer_size= * 想要删除索引缓存 只需设置它的大小为 mysql>SET GLOBAL keycache key_buffer_size= 索引缓存变量是一个结构体变量 由名字和组件构成 例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件 默认地 表索引在服务器启动时指定到主(默认的)索引缓存中 当一个索引缓存被删掉后 指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去 对一个繁忙的系统来说 我们建议以下三条策略来使用索引缓存 热缓存占用 %的总缓存空间 用于繁重搜索但很少更新的表 冷缓存占用 %的总缓存空间 用于中等强度更新的表 如临时表 冷缓存占用 %的总缓存空间 作为默认的缓存 用于所有其他表 使用三个缓存的一个原因是好处在于 存取一个缓存结构时不会阻止对其他缓存的访问 访问一个表索引的查询不会跟指定到其他缓存的查询竞争 性能提高还表现在以下几点原因 热缓存只用于检索记录 因此它的内容总是不需要变化 所以 无论什么时候一个索引区块需要从磁盘中引入 被选中要替换的缓存区块的内容总是要先被刷新 索引被指向热缓存中后 如果没有需要扫描全部索引的查询 那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中 在临时表里必须频繁执行一个更新 *** 作是相当快的 如果要被更新的节点已经在缓存中了 它无需先从磁盘中读取出来 当临时表的索引大小和冷缓存大小一样时 那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块 不过它也支持更完善的方法 叫做 中点插入策略

使用中点插入策略时 LRU链就被分割成两半 一个热子链 一个温子链 两半分割的点不是固定的 不过缓存管理系统会注意不让温子链部分 太短 总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分 因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时 它首先放在温子链的末尾 当达到一定的点击率(访问这个区块)后 它就提升到热子链中去 目前 要提升一个区块的点击率( )对每个区块来说都是一样的 将来 我们会让点击率依靠B树中对应的索引区块节点的级别 包含非叶子节点的索引区块所要求的提升点击率就低一点 包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾 这个区块在热子链内一直循环 如果这个区块在该子链开头位置停留时间足够长了 它就会被降级回温子链 这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的 一个索引缓存包含了 N 个区块 热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置 它又首先成为被删除的候选对象 因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块 如果更喜欢采用LRU策略 只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引 它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能 为了避免这样 就必须设定 key_cache_division_limit 远远低于 以采用中点插入策略 则在扫描索引 *** 作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引 或者至少足够保存全部非叶子节点 那么在使用前就载入索引缓存就很有意义了 将索引区块以十分有效的方法预载入索引缓存缓冲 从磁盘中顺序地读取索引区块

没有预载入 查询所需的索引区块仍然需要被放到缓存中去 虽然索引区块要保留在缓存中 因为有足够的缓冲 它们可以从磁盘中随机读取到 而非顺序地

想要预载入缓存 可以使用 LOAD INDEX INTO CACHE 语句 如下语句预载入了表 t 和 t 的索引节点(区块)

mysql> LOAD INDEX INTO CACHE t  t  IGNORE LEAVES+ + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | preload_keys | status | OK | | test t  | preload_keys | status | OK | + + + + +

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块 因此 上述语句加载了 t 的全部索引区块 但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存 将索引区块预先放到那个缓存中去 否则 索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小 用它就可以来调整索引文件I/O *** 作的性能

当读缓冲的大小和本地 *** 作系统的I/O缓冲大小一样时 就达到了I/O *** 作的最高性能了 但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能 读比较大的叶子节点时 服务器会读进来很多不必要的数据 这大大阻碍了读其他叶子节点

目前 还不能控制数据表的索引区块大小 这个大小在服务器创建索引文件 ` MYI 时已经设定好了 它根据数据表的索引大小的定义而定 在很多时候 它设置成和I/O缓冲大小一样 在将来 可以改变它的值 并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它 例如

mysql> SET GLOBAL cold_cache key_buffer_size= * *

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存

lishixinzhi/Article/program/Oracle/201311/16615

mysql 开启查询缓存可以有两种方法来开启一种是使用set命令来进行开启，另一种是直接修改my.ini文件来直接设置都是非常的简单的哦。

开启缓存，设置缓存大小，具体实施如下：

windows下是my.ini，linux下是my.cnf

在配置文件的最后追加上：

需要重启mysql生效；

b) 开启缓存,两种方式：

a)使用mysql命令：

如果报错：

Query cache is disabledrestart the server with query_cache_type=1 to enable it，还是老老实实的该配置文件，然后重启吧，原因如下：

查看是否设置成功

show variables like "%query_cache%" 查看是否设置成功：

当然如果你的数据表有更新怎么办，没关系mysql默认会和这个表有关系的缓存删掉，下次查询的时候会直接读表然后再缓存

下面是一个简单的例子：

以上的相关内容就是对mysql缓存查询和设置的介绍，望你能有所收获。

一般，我们会把 query_cache_type 设置为 ON,默认情况下应该是ON

query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存，如：

这样 当我们执行 select id,name from tableName这样就会用到查询缓存。

①在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明

select sql_no_cache id,name from tableName

②当sql中用到mysql函数，也不会缓存

当然也可以禁用查询缓存： mysql>set session query_cache_type=off

上面的显示，表示设置查询缓存是可用的。

表示查询缓存大小，也就是分配内存大小给查询缓存，如果你分配大小为0，

那么 第一步 和 第二步 起不到作用，还是没有任何效果。

上面是 mysql6.0设置默认的，之前的版本好像默认是0的，那么就要自己设置下。

设置

这里是设置1M左右，900多K。

再次查看下:

显示我们设置新的大小，表示设置成功。

例如： 如果查询结果很大， 也缓存？？？？这个明显是不可能的。

MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会

进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。

这个是默认的数值，如果需要修改，就像设置缓存大小一样设置，使用set

重新指定大小。

好了，通过4个步骤就可以 打开了查询缓存，具体值的大小和查询的方式 这个因不同

的情况来指定了。

mysql查询缓存相关变量

MySQL 提供了一系列的 Global Status 来记录 Query Cache 的当前状态，具体如下：

Qcache_free_blocks：目前还处于空闲状态的 Query Cache 中内存 Block 数目

Qcache_free_memory：目前还处于空闲状态的 Query Cache 内存总量

Qcache_hits：Query Cache 命中次数

Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数

Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL

Qcache_queries_in_cache：目前在 Query Cache 中的 SQL 数量

Qcache_total_blocks：Query Cache 中总的 Block 数量

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率

当服务器收到SELECT 语句的时候，Qcache_hits 和Com_select 这两个变量会根据查询缓存

的情况进行递增

查询缓存命中率的计算公式是：Qcache_hits/(Qcache_hits + Com_select)。

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据 查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小查询缓存利用率在80%以上而且 Qcache_lowmem_prunes >50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 = 1.94%，命中率很差，可能写 *** 作比较频繁吧，而且可能有些碎片。

查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果，可以通过下面的SQL来查看缓存命中次数（是个累加值）：

另外即使完全相同的SQL，如果使用不同的字符集、不同的协议等也会被认为是不同的查询而分别进行缓存。

在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

可以使用下面三个SQL来清理查询缓存：

1、FLUSH QUERY CACHE// 清理查询缓存内存碎片。

2、RESET QUERY CACHE// 从查询缓存中移出所有查询。

3、FLUSH TABLES//关闭所有打开的表，同时该 *** 作将会清空查询缓存中的内容。

Query Cache是MySQL Server层的一个非常好的特性，对于小数据集或访问量非常集中的应用场景，有非常好的性能提升，但是Query Cache引入了一些新的问题，而且大部分场景下比较鸡肋，官方打算弃用了

参考：

https://www.cnblogs.com/wangzhuxing/p/5223881.html

https://www.cnblogs.com/lixiuran/archive/2014/03/08/3588654.html

解决方案：先删除唯一索引，再在当前字段创建普通索引，参考下列说明以及SQL：

普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

唯一索引

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

---