关于mysql:Mysql-实战笔记-二-实践1

一、一般索引和惟一索引

查问过程

对于一般索引来说，查找到满足条件的第一个记录后，须要查找下一个记录，直到碰到第一个不满足 条件的记录。
对于惟一索引来说，因为索引定义了唯一性，查找到第一个满足条件的记录后，就会进行持续检索。
InnoDB 的数据是按数据页为单位来读写的。在 InnoDB 中，每个数据页的 大小默认是 16KB。对于整型字段，一个数据页能够放近千个 key。

更新过程

当须要更新一个数据页时，如果 数据页在内存中就间接更新， 而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 changebuffer 中， 这样就不须要从磁盘中读入这个数据页了。在下次查问须要拜访这个数据页的时候，将数据页读入内存，而后执行 change buffer 中与这个页无关的操作。
将 change buffer 中的操作利用到原数据页，失去最新后果的过程称为 merge。除了拜访这个数据页会触发 merge 外，零碎有后盾线程会定期 merge。在数据库失常敞开（shutdown）的过程中，也会执行 merge 操作。
惟一索引的更新操作都要先判断这个操作是否违反唯一性束缚。而这必须要将数据页读入内存能力判断。所以惟一索引的更新不能应用 change buffer，只有一般索引能够应用。
change buffer 用的是 buffer pool 里的内存，因而不能有限增大。change buffer 的大小，能够通过参数 innodb_change_buffer_max_size 来动静设置。这个参数设置为 50 的时候，示意 change buffer 的大小最多只能占用 buffer pool 的 50%。
如果插入一个新记录 (4,400) 的话：

1. 这个记录要更新的指标页在内存中

对于惟一索引来说，找到 3 和 5 之间的地位，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，找到 3 和 5 之间的地位，插入这个值，语句执行完结。

1. 这个记录要更新的指标页不在内存中

对于惟一索引来说，须要将数据页读入内存，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，则是将更新记录在 change buffer，语句执行就完结了。

change buffer 的应用场景

对于 写多读少 的业务来说，页面在写完当前马上被拜访到的概率比拟小，此时 change buffer 的应用成果最好。这种业务模型常见的就是账单类、日志类的零碎。
反过来，假如一个业务的更新模式是写入之后马上会做查问，那么即便满足了条件，将更新先记录在 change buffer，但之后因为马上要拜访这个数据页，会立刻触发 merge 过程。这样随机拜访 IO 的次数不会缩小，反而减少了 change buffer 的保护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引抉择和实际

一般索引和惟一索引在查问能力上是没差异的，次要思考的是对更新性能的影响。
如果所有的更新前面，都马上随同着对这个记录的查问，那么你应该敞开 change buffer。而在其余状况下，change buffer 都能晋升更新性能。

change buffer 和 redo log

insert into t(id,k) values(id1,k1),(id2,k2)
k1 所在的数据页在内存 (InnoDBbuffer pool) 中，k2 所在的数据页不在内存中。下图是是带 change buffer 的更新状态图。

剖析这条更新语句，你会发现它波及了四个局部：内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、零碎表空间（ibdata1）
(零碎表空间就是用来放零碎信息的，比方数据字典什么的，对应的磁盘文件是 ibdata1, 数据表空间就是一个个的表数据文件，对应的磁盘文件就是 表名.ibd)
这条更新语句做了如下的操作（依照图中的数字程序）：

1. Page 1 在内存中，间接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）。做完下面这些，事务就能够实现了。所以，你会看到，执行这条更新语句的老本很低，就是写了两处内存，而后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是程序写的。同时，图中的两个虚线箭头，是后盾操作，不影响更新的响应工夫。

redo log 次要节俭的是随机写磁盘的 IO 耗费（转成程序写），而 change buffer 次要节俭的则是随机读磁盘的 IO 耗费
如果某次写入应用了 change buffer 机制，之后主机异样重启，是否会失落 change buffer 和数据?
不会失落，尽管是只更新内存，然而在事务提交的时候，咱们把 change buffer 的操作也记录到 redo log 里了，所以解体复原的时候，change buffer 也能找回来。
merge 的过程是否会把数据间接写回磁盘?
merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），顺次利用，失去新版数据页；
3. 写 redo log。这个 redo log 蕴含了数据的变更和 change buffer 的变更。

到这里 merge 过程就完结了。这时候，数据页和内存中 change buffer 对应的磁盘地位都还没有批改，属于脏页，之后各自刷回本人的物理数据。

二、MySQL 为什么有时候会选错索引？

优化器的逻辑

化器抉择索引的目标，是找到一个最优的执行计划，并用最小的代价去执行语句。在数据库外面，扫描行数是影响执行代价的因素之一。当然，扫描行数并不是惟一的判断规范，优化器还会联合 是否应用长期表、是否排序等因素进行综合判断。
扫描行数是怎么判断的?
MySQL 在真正开始执行语句之前，并不能准确地晓得满足这个条件的记录有多少条，而只能依据统计信息来估算记录数。这个统计信息就是 索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。 而一个索引上不同的值的个数，咱们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
咱们能够应用 show index 办法，看到一个索引的基数。尽管这个表的每一行的三个字段值都是一样的，然而在统计信息中，这三个索引的基数值并不同，而且其实都不精确。

MySQL 是怎么失去索引的基数的呢？
MySQL 采样统计，如果把整张表取出来一行行统计，尽管能够失去准确的后果，然而代价太高了，所以只能抉择“采样统计”。InnoDB 默认会抉择 N 个数据页，统计这些页面上的不同值，失去一个平均值，而后乘以这个索引的页面数，就失去了这个索引的基数。

其实索引统计只是一个输出，对于一个具体的语句来说，优化器还要判断，执行这个语句自身要扫描多少行。接下来，咱们再一起看看优化器预估的，这两个语句的扫描行数是多少。

优化器为什么放着扫描 37000 行的执行打算不必，却抉择了扫描行数是 100000 的执行打算呢？这是因为，如果应用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
MySQL 选错索引，是没能精确地判断出扫描行数。既然是统计信息不对，那就修改。analyze table t 命令，能够用来从新统计索引信息。咱们来看一下执行成果。

索引抉择异样和解决

一种办法是，采纳 force index 强行抉择一个索引。
第二种办法就是，能够思考批改语句 (语义的逻辑是雷同的)，疏导 MySQL 应用咱们冀望的索引。
第三种办法是，在有些场景下，咱们能够新建一个更适合的索引，来提供给优化器做抉择，或删掉误用的索引。

三、怎么给字符串字段加索引？

应用前缀索引后，可能会导致查问语句读数据的次数变多。
应用前缀索引，定义好长度，就能够做到既节俭空间，又不必额定减少太多的查问老本。
当要给字符串创立前缀索引时，有什么办法可能确定我应该应用多长的前缀呢？实际上，咱们在建设索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着反复的键值越少。 因而，咱们能够通过统计索引上有多少个不同的值来判断要应用多长的前缀。
首先，应用上面这个语句，算出这个列上有多少个不同的值:
select count(distinct email) as L from SUser;
而后，顺次选取不同长度的前缀来看这个值，比方咱们要看一下 4~7 个字节的前缀索引，能够用这个语句:
select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7,from SUser;

前缀索引对笼罩索引的影响

应用前缀索引就用不上笼罩索引对查问性能的优化了

区分度不好的字符串怎么建索引？

如身份证号码
第一种形式是应用倒序存储。存储身份证号的时候把它倒过去存。
第二种形式是应用 hash 字段。能够在表上再创立一个整数字段，来保留身份证的校验码，同时在这个字段上创立索引。
它们的相同点是，都不反对范畴查问。

四、为什么 MySQL 会“抖”一下？

一条 SQL 语句，失常执行的时候特地快，然而有时也不晓得怎么回事，它就会变得特地慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。
InnoDB 在解决更新语句的时候，只做了写日志 (redo log) 这一个磁盘操作。而后把内存里的数据写入磁盘的过程，术语就是 flush。在这个 flush 操作执行之前，内存和硬盘数据是不统一的。
当内存数据页跟磁盘数据页内容不统一的时候，咱们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就统一了，称为“洁净页”。
平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶然“抖”一下的那个霎时，可能就是在刷脏页（flush）。

什么状况会引发数据库的 flush 过程呢？

1. InnoDB 的 redo log 写满了。这时候零碎会进行所有更新操作。 如果你从监控上看，这时候更新数会跌为 0。
2. 零碎内存不足。当须要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页应用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
   InnoDB 用缓冲池（buffer pool）治理内存， 缓冲池中的内存页有三种状态：还没有应用的；应用了并且是洁净页；应用了并且是脏页。。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不应用的数据页从内存中淘汰掉：如果要淘汰的是一个洁净页，就间接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成洁净页后能力复用。
   一个查问要淘汰的脏页个数太多，会导致查问的响应工夫显著变长。
3. MySQL 认为零碎“闲暇”的时候。
4. MySQL 失常敞开的状况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就能够间接从磁盘上读数据，启动速度会很快。

InnoDB 刷脏页的控制策略

首先，你要正确地通知 InnoDB 所在主机的 IO 能力，这样 InnoDB 能力晓得须要全力刷脏页的时候，能够刷多快。这就要用到 innodb_io_capacity 这个参数了，它会通知 InnoDB 你的磁盘能力。这个值我倡议你设置成磁盘的 IOPS。磁盘的 IOPS 能够通过 fio 这个工具来测试。
想要尽量避免“一下”这种状况，就要正当地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它常常靠近 75%。其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 失去的。

补充：mysql 在筹备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“街坊”也带着一起刷掉。
在 InnoDB 中，innodb_flush_neighbors 参数就是用来管制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时示意不找街坊，本人刷本人的。
找“街坊”这个优化在机械硬盘时代是很有意义的，能够缩小很多随机 IO。而如果应用的是 SSD 这类 IOPS 比拟高的设施的话，因为这时候 IOPS 往往不是瓶颈，所以能够设置为 0。