MySQL学习（一）日志与索引 --- 2019年1月

1、MySQL的架构

　　1）、连接器

　　先根据Ip和端口号，用户名和密码，连接MySQL数据库，连接后如果没有下一步动作，连接就处于空闲状态，此时有一个连接超时时间的设置 wait_timeout默认8小时。

　　连接器会查询当前登录用户在权限表中所存储的权限。

　　连接分为长连接和短连接，长连接是指连接成功后不断开，下一次请求继续用该连接。短连接是连接成功后执行几次就断开连接。

　　连接的过程复杂，所以尽量用长连接，但是用长连接占用的内存资源在断开的时候才会释放，所以可能MySQL占用的内存很大。解决办法就是定时关闭长连接。

　　2）、查询缓存

　　建立连接后，执行逻辑就会先查询缓存。 之前的sql语句是以key = sql 语句，value = 查询结果存在缓存中，所以会查询缓存是否存在。查询缓存有速度快的作用。

　　但是实际情况中尽量少用查询缓存，并且MySQL8.0将缓存模块都去掉了。 因为只要对一个表更新，就会查询缓存失败，所以查询缓存的命中率可能很低。

　　3）、分析器

　　分析器先会做词法分析，输入的字符串和空格，关键字，表名，字段名等，看是否符合规则；

　　然后做词意分析，分析表，字段等是否存在连接的数据库里；

　　4）、优化器

　　经过分析器，MySQL知道要做什么操作，此时就会优化操作，例如用什么索引，怎么优化sql语句。

　　5）、执行器

　　下一步来到执行器，开始执行sql语句。此时会判断该连接用户是否有权限对数据库进行操作。

　　执行器是调用引擎接口（默认InnoDB），对表一行一行的查数据，看每一行是否符合sql语句的条件。循环遍历查询结束后将结果集返回给客户端。

2、日志系统

　　1）、重做日志（redo log）

　　redo log 记录的是"在某个数据页上做了什么修改"。修改很多数据的时候，在数据库底层的操作是，先把记录写到redo log 里面，并更新内存，等系统空闲了，把数据从内存中再慢慢写入磁盘中，这就是WAL技术(write ahead logging)。 redo log 保证了 crash-safe。 redo log 日志的大小是有限的，当 redo log 写满，就会先清空部分 redo log 的内存（写一部分数据到磁盘）。

　　innodb_flush_log_at_trx_commit 这个参数值设置为1的时候，表示每次事务的 redo log 都直接持久化到磁盘，建议设置为1，保证MySQL异常重启后数据不丢失。

　　2）、归档日志（bin log）

　　bin log 存在于 server层，记录的是"给某一行的A字段值加1"这种逻辑，有两种模式，statement 记录的是 sql 语句，row 记录的是行（2句，改变前和改变后）。它的主要作用是归档，方便宕机等数据库出现问题的时候，恢复正确的数据。 

　　sync_binlog 参数值设置为1的时候，表示每次事务的 binlog 都持久化到磁盘，也建议设置为1，这样可以保证MySQL异常重启之后 binlog 不丢失。

　　数据的修改是一个"二阶段提交"：

　　　　a.执行器用引擎接口查询内存或磁盘（内存没数据的时候）拿到数据页；

　　　　b.执行器修改数据，调用引擎接口引入新数据；

　　　　c.数据先写入内存中，再写入 redo log 中，此时 redo log 处于 prepare（准备）阶段，可以提交事务了；

　　　　d.执行器生成这个操作的 biglog，将 biglog 写入磁盘；

　　　　e.执行器调用引擎接口的事务管理，redo log 修改为 commit 状态。

　　其实可以分为三个小阶段：

　　　　写入 redo log 状态 prepare 阶段（第一阶段），生成 binlog 写入磁盘阶段（第二阶段），修改 redo log 为 commit 阶段（第三阶段）。

　　　　如果在第一阶段 crash，没有生成 binlog 日志，并且 redo log 处于 prepare 阶段。当恢复数据库后，数据回滚，保持事务一致性。

　　　　如果在第二阶段crash，没有修改 redo log 的状态为commit，但是已经生成 binlog 写入了磁盘。当恢复数据库后，第一阶段第二阶段完整，会将 redo log 修改为 commit 状态。也是保持事务的强一致性。（binlog 是有完整的格式，statment 最后有 COMMIT 和 row 最后有一个XID event，并且MySQL5.6.2还引入了 binlog - checksum 来保证 binlog 的完整性）

3、事务隔离

　　数据库事务：保证数据库的一组操作要么全部成功要么、全部失败。

　　四大特性：原子、一致、隔离、持久性。

　　当数据库多个事务执行的时候，可能会出现脏读（事务A读事务B数据，事务B回滚，事务A还是拿事务B之前的数据，脏数据）、幻读（事务A读取了N条数据，事务B对事务A读取的数据新增或者减少了M条数据，事务A再次读取就产生了幻读）、不可重复读（事务A读取数据，事务B修改，事务A再次读取发现数据不匹配）等问题。所以SQL用事务隔离来解决事务的问题。包括了：读未提交、读提交、可重复读、串行化。

　　读未提交：事务A和事务B，事务B无论运行到时候流程，事务A都可以拿到事务B的最新数据；

　　读提交：事务A和事务B，事务B要运行的流程跑完，事务A才可以读取事务B的流程结束的数据；

　　可重复读：事务A和事务B，事务A只关注自己的初始数据，事务B不影响事务A；

　　串行：事务A执行完毕才执行事务B；

　　这四种隔离线安全性越来越高，但是效率也越来越低。

　　在实现上，数据库里面会根据事务创建一个视图，访问的时候以视图的逻辑结果为准（适用于读提交、可重复读）。可重复读隔离别，视图是在事务启动时创建。读提交级别，视图是在每个SQL语句开始的时候创建的。InnoDB引擎默认隔离级别为 --- 可重复读。

　　事务隔离的实现：可重复读的隔离级别下，一个事务为一个事务视图read-view，数据有一个回日志，当数据回滚的时候，其他用数据最后值的时候也不冲突。当事务已经回滚完，不再需要回滚日志时，回滚日志会被删除。所以尽量不要使用长事务，因为长事务存在了很老的事务视图，在回滚结束之前都会访问数据库的数据，大量占用内存空间。

　　回滚日志（undo log）里不同的事务有不同的视图，同一条记录在系统中存在多个版本，这就是MVCC（数据库的多版本并发控制）。

　　事务的启动方式：显示启动begin/start transaction，提交语句commit，回滚语句rollback。或者用set autocommit = 1 自动提交事务。如果值为0，不会自动提交事务，必须手动提交，会造成长连接。

　　如何避免长事务对业务的影响：

　　　　是否set autocommit = 0，如果是0，将值修改为1；

　　　　确认是否有不必要的只读事务；

　　　　SET MAX_EXECUTION_TIME，设置每个语句执行的时间，避免单个语句执行的时间意外太长；

　　　　监控innodb_trx表，发现长事务报警；

4、索引

　　索引就是为了提高查询数据的速度，就像书的目录一样可以快速定位目标。

　　常见的三种数据结构

　　　　1）、哈希表：把值放一个数组里，key作为数组的位置，value作为数组的值。在多个key经过哈希函数运算会得到同一个value值的时候，再用链表。特点是：新增数据快（只需要往后追加），查询速度慢（遍历所有的值）。所以适用于等值查询的场景，例如Memcached或其他NoSQL引擎。

　　　　2）、有序数组：将数据按顺序排列好，查询数据或查范围数据的时候很快。 但是如果在数据中间插入新的数据，就会使该数据之后的数据往后逻辑，成本太高（用的二分查找）。所以有序数组适用于查询静态数据（数据的值不轻易增加）的场景。

　　　　3）、二叉树及N叉树：查询尽量少的读磁盘，就必须让查询过程访问尽量少的数据页，所以去掉二叉树要用N叉树。N叉树的N取决于数据快的大小。（一般树的层数3、4层，一层多的有1000多个节点）特点：读写速度快，适配磁盘的方式，所以被广泛应用于数据引擎中。

　　常用的InnoDB数据引擎的索引模型

　　　　在InnoDB引擎中，表都是根据主键顺序以索引的方式存放的。InnoDB引擎使用了BTREE（B+树）索引模型，所以数据都是存在B+树中的。每一个索引在InnoDB里面都对应了一棵B+树。索引类型有2种：

　　　　主键索引（聚簇索引）：存在树叶节点的是整行数据；

　　　　普通索引（二级索引）：存在树叶节点的是主键的值（所以主键不宜过大）；

　　　　基于主键索引查询只需要扫描1次索引树。基于普通索引查询，要先扫描普通索引树得到主键的值，再扫描主键索引树得到整条数据的值（回表）。所以我们尽量使用主键索引。

　　　　索引维护：新增数据的时候，如果某数据页数据满了，又插入新的数据，那就会需要申请一个新的数据页，将部分数据迁移过去，叫做页分裂，并且页的利用率也降低了，影响性能。如果数据删除，页分裂的过程也可逆，2页（块）合并为1页。

　　　　可以考虑主键自增，首先是数据都是自增，都是追加操作，所以插入数据都是按序在树节点插入，不会涉及到页分裂。然后主键长度越小，普通索引的叶子长度越小，普通索引占用的空间也就越小。但是也有普通业务字段做主键的：该表只有一个索引，该索引必须唯一，典型的KV场景。

　　　　重建索引：先删除普通索引，再重建普通索引是合理的，可以达到节省空间的作用（重建普通索引会去掉标记为删除的数据页位置和空白数据页可复用，所以索引树会变小，并且索引会写在内存中）。但是删除主键索引再重建主键索引操作是错误的，无论是删除还是重建，都会将表重建，但是可以用：alter table T engine=InnoDB 语句代替（如果表数据和索引太大，可以不用重新建表，直接用这句sql就可以释放之前的索引记录）。

　　　　重建索引适用于 表的大小远远小于索引的大小（数据的删改增操作过多，数据页上产生过多的空洞即被标记为删除的位置，或者有很多可以复用的空白数据页，造成索引的臃肿）

　　　　没有主键的表，InnoDB会默认创建一个RowId做为主键。

　　索引优化

　　　　索引覆盖：查询条件是普通索引，查询结果是联合索引的字段或者是主键，不用回表直接返回数据，减少树的搜索次数，显著提升查询性能；

　　　　最左前缀：B+树这种索引，可以用最左前缀原则。联合索引（几个索引放一起）的最左侧的N个字段，也可以是字符串索引的最左侧的M个字符，加速检索；

　　　　联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引。根据业务需求，将查找频繁的数据进行靠左创建索引，或字段过长字段靠左创建索引（考虑到存储空间的问题）；

　　　　索引下推：例如 name like "萧%" and age > 10 的查询条件，MySQL的5.6版本之前，会对匹配名字前缀符合 萧 的所有数据进行回表，再匹配age > 10。在MySQL的5.6版本后，在索引遍历过程中（遍历索引name的时候），会先过滤掉age <= 10的数据，再进行回表。

　　　知识点扩展

　　　　where查询条件和联合索引的顺序不一样，优化器会自动做出优化；

　　　　数据量很大的时候，使用覆盖索引，普通索引比主键查询更快；

　　　　每个索引都要维护数据段，新插入/修改等操作，每个索引都回去维护这个值，所以索引不是越多越多，索引越多，"维护成本"越大。

5、MySQL的锁

　　MySQL的锁分为全局锁、表级锁和行级锁

　　1）、全局锁：MySQL提供一个全局加读锁的方法，命令：Flush tables with read lock （FTWRL）。加了该锁之后所有线程都只能对数据库的数据读，而不能写。此锁适用于数据库逻辑备份的时候。

　　但是也可能存在缺点：备份加了读锁，只能读，业务就停了。还有主从备份的时候，从库不能立即执行主库同步过来的 binlog，备份延迟。

当官方自带备份工具 mysqldump 使用参数 single-transaction ，会启动一个事务，可以保证一致性视图。由于多版本并发控制（MVCC）整个过程数据是可以更新的。但是前提是引擎要支持事务隔离级别为 可重复读。 single-transaction 适用于所有的表使用事务引擎（例如InnoDB）的数据库。

　　还有全局只读不使用 set global readonly = true 方式，是因为 readonly 的值也可能拿来做其他逻辑，例如判断一个库是主库还是从库（主从分离原则）。

　　另外有个原因是执行 FTWRL 如果中途网络断开等原因，锁会自动释放，数据库回到初始状态。但是 readonly 会一直保持只读，导致数据库的长时间不可用。

　　2）、表级锁：有两种，一种是表锁，一种是元数据锁（meta data lock MDL）

　　表锁的加锁方法： lock tables ... read/write，可以用unlock释放锁。表锁不止限制了其他线程的读写，也限制了自己线程的读写情况。所以一般InnoDB支持行锁的引擎都不会使用表锁。

　　MDL不需要配置显示使用，在线程访问表的时候自动加上。它的作用是保证读写的正确性。

　　数据库CRUD时（DML）加读锁，读锁可以同时进行多个。对表的结构变动时（DDL），加写锁，写锁只能存在一个（读写锁互斥，写锁与写锁也互斥）。

　　如果对一个表，先有读锁和后有写锁，读锁未释放，写锁就会阻塞。 在写锁之后进来的锁也会阻塞。如果客户端有重试机制，可能线程就一下子爆满，内存升高。解决方法是杀死长事务。如果长事务不好找到，那就在 alter table 加一个等待时间，如果超过等待时间拿不到MDL写锁就放弃，不阻塞后面的业务。

　　3）、行锁：MySQL 的行锁是由各引擎实现，所有有些引擎不支持行锁，常用的InnoDB就支持行锁。行锁针对的数据就是一行数据。

　　两阶段锁：一个针对行数据的事务从对行数据有操作时持有锁，要等到事务提交才会释放锁。所以其他事务针对同一行数据，就会阻塞。

　　所以有个很重要的知识点，在代码里，如果一个事务同时有2个操作，一个操作1是插入数据，一个操作2是更新数据（可能会有多个线程同时修改这行数据），那么在代码里的代码顺序就可以1，2这样排序（在代码的一个事务里，更新操作放在最后面）。 因为2拿到锁和释放锁的时间就变小了。这样其他锁阻塞的时间就变短了。

　　但是可能出现死锁，因为两个或者多个锁出现了循环等待，变成了无限等待。

　　　　解决方法1：是设置锁的超时时间，InnoDB 默认 innodb_lock_wait_timeout = 50s，显然不适用。但是也不能把超时时间修改的太短，可能会误伤那些响应时间长一点的线程；

　　　　解决方法2：是发起死锁检测，默认 innodb_deadlock_detect = on 检测死锁。发现死锁后，主动回滚死锁里面的事务，让其他事务进行。但是死锁检测也会消耗大量的CPU。 所以可以临时关闭死锁检测（不推荐）、控制并发数量（不好控制）、将一行改为多行来减少冲突（其实很复杂）。

　　知识点扩展

　　　　online DDL操作：mysql5.6开始支持该操作，在对表字段做操作时不会阻塞。它的原理：拿MDL写锁，降级成MDL读锁，执行DDL操作，升级MDL写锁，释放MDL锁。如果1、2、4 、5 没有锁冲突，只有第3步耗时最长，这期间可以对表改结构和CRUD数据，所以叫DDL操作；

　　　　FTWRL的时候，如果之前有读写，则会读写完再做FTWRL操作（执行FTWRL是在所有事物执行完之后）；

　　　　readonly 在用户权限是超级用户的时候 readonly 是失效的；

　　　　MDL锁释放的情况就是 事务提交；

　　　　InnoDB行级锁是通过锁索引记录实现的，如果更新数据的列没有建立索引，则更新数据的时候会锁定整张表。

6、事务到底是隔离的还是不隔离的（重点）

　　这篇文章的理论知识点太多，就不一一总结，只总结了最精华的部分。要看的去极客时间的专栏看吧。

　　begin/start transaction 命令并不是一个事务的起点，在执行它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。也可以用命令：start transaction with consistent snapshot 直接启动事务。

　　InnoDB为每个事务都构造了一个数组，用来保存这个事务启动的瞬间，当前所有启动了但是还没有提交的所有事务的ID。数组里面事务ID的最小值记为低水位，当前系统已经创建过的事务ID的最大值 +1 为高水位。

　　这个视图数组和高水位，就组成了当前事务的一致性读视图（read - view）。

　　一致性读视图：保证了当前事务从启动到提交期间，读取到的数据是一致的。

　　InnoDB利用了"所有数据有多个版本"的这个特性，实现了"秒级创建快照"的能力。

　　一个数据版本，对于一个事务视图A来说，除了自己的更新自己总是可见（事务A中有更新 + 查询操作，查询可以看到更新后的数据），有三种情况：

　　　　1、该数据版本未提交，对事务视图A不可见；　

　　　　2、该数据版本已提交，但是是在视图创建后提交的，不可见；

　　　　3、该数据版本已提交，是在视图创建前提交的，可见。　

　　当前读：更新数据执行当前读 --- 更新数据都是先读后写，而这个读，必须要读最新版本（事务版本），只能读当前的值（事务已成功提交的值），成为当前"读"。（最新值是已经提交了事务后的当前该字段的最新值）

　　如果 select 语句加了锁，也会变为当前读（我理解就是读取当前最新数据，不遵循MVCC的排序）。

　　写锁未释放，当前读如果要读最新版本，必须加锁，所以会阻塞，那就得等写锁释放了锁，才能继续当前读。

　　这篇文章把：一致性读、当前读、行锁串联起来了。

　　可重复读（RR）的核心就是一致性读；而事务更新数据的时候，必须当前读；如果当前的记录的行锁被其他事务占用的话，就需要进入锁阻塞，等待。

　　读提交和可重复读的逻辑类似，区别是：

　　　　可重复读隔离级别下，事务开始的时候创建一致性视图，事务内的无论多少个sql语句都是共享这一个视图；查询只承认在事务启动前就已经提交完成的数据版本；

　　　　读提交隔离级别下，每一个语句执行前都会重新创建一个视图；查询只承认在语句启动前就已经提交完成的数据版本。

7、唯一索引和普通索引怎么选择

　　查询情况

　　InnoDB的数据是按数据页来读写的。当要查询一条数据，是以页为单位，将整页从磁盘中读入内存。InnoDB中每个数据页大小默认16K。

　　普通索引查询是命中一个数据后继续查找下一个，直到不满足条件。唯一索引是命中数据停止查找。但是对于普通索引来说，要多做的只需要几次指针寻找和一次计算。

　　更新情况

　　唯一索引不存在 change buffer，普通索引有 change buffer ，并且 change buffer是可以持久化的数据，不止在内存中有拷贝，也会被写入到磁盘上。（唯一索引涉及到读取数据是否有冲突）

　　当要更新一个数据页的时候，如果数据页在内存中就直接更新，如果数据页在磁盘（不在内存中），在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存到 change buffer 中，这样就不需要从磁盘读这个数据页并做相应修改。在访问这个数据页的时候再从磁盘读数据到内存中，执行 change buffer 中与这个页相关的操作。

　　merge 是将 change buffer 中的操作，应用到原数据页，得到最新的数据页，方式有三种：

　　　　访问这个数据页；

　　　　系统有后台线程定期 merge；

　　　　数据库正常关闭的过程也会执行 merge 操作。

　　如果不涉及到查询数据就可以使用 change buffer ，并且插入数据是在 change buffer 里操作，那么写多读少的操作就很适合普通索引。

　　将更新操作先记录再 change buffer 中，减少读磁盘的数据页到内存中，SQL语句的速度会有明显的提示。因为将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。

　　change buffer 的使用场景

　　change buffer 的作用是将记录的变更动作缓存下来，change buffer 记录的变更越多（这个页面上要更新的次数越多），收益就越大。并且将 change buffer 尽量开大一点。

　　如果所有的更新后面都涉及到马上对这个记录的查询，那就应该关闭 change buffer （更新就会去磁盘读数据页到内存中，执行merge）。

　　change buffer 和 redo log

　　如果插入多条数据，部分数据可以从内存中查到，部分数据从内存中查不到。1 --- 在内存中的就直接更新内存；2 --- 没有在内存中，就在 change buffer 区域，记录下"我在哪页插入一行"信息。 然后将上述两个动作依次记录在 redo log 中。然后事务就可以完成了。这个操作写了2处内存1处磁盘。

　　如果读上述操作的1数据，会直接从内存中拿取数据。（因为redo log 日志已经记录了操作，是肯定会把数据写入到磁盘，所以数据肯定是一致的）

　　如果读上述操作的2的数据，会需要将数据页从磁盘读入内存，然后应用change buffer 里面的操作日志，生成一个正确的版本（新的数据页）并返回结果。

　　redo log 和 change buffer 在更新和插入结合使用：

　　redo log 主要是节省随机写磁盘的IO消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗（如果能直接从内存查到数据页，就不需要从磁盘读到内存中）。

　　change buffer 有一部分在内存中，一部分在系统表空间中，merge 操作就会把内存中的持久化到系统表空间中。 如果断电，已经持久化到系统表空间数据已经 merge ，不用恢复。

　　主要涉及到在内存中的 change buffer，主要有以下几种情况：

　　　　change buffer 写入，redo log 没有commit，binlog 没有写入到磁盘，这部分数据丢失；

　　　　change buffer 写入，dedo log 没有commit，binlog 已经写入到磁盘，这部分数据会按强一致性让redo log commit，数据恢复；

　　　　change buffer 写入，redo log已经commit，binlog 肯定已经写入磁盘，这部分数据按强一致性，数据恢复。

　　　　change buffer 结合了日志的二阶段提交。

　　扩展知识点

　　　　锁是一个单独的数据结构，如果数据页上有锁，change buffer 在判断是否能用的时候就会认为否；

　　　　change buffer 在某行数据已经记录的情况下，再次修改是又增加一条记录（所以拿的数据是最新版）；

　　　　merge 其实是从磁盘读数据页到内存，然后 change buffer 把操作日志应用，更新数据页（内存中的），同时写redo log；

　　　　change buffer 在 merge 时是将内存中的数据更新的最新，redo log 是将磁盘的数据更新到最新；

　　　　change buffer 最有收益的时候，大量插入的表，表中有多个非唯一索引，就有收益。

8、MySQL选错索引

　　索引的选择是优化器做的。

　　强制让优化器使用某个索引，使用 forcr index()；

　　在数据库里，扫描行数是影响执行代价的因素之一，还有是否使用临时表、是否排序等因素。

　　MySQL在真正执行开始语句前，并不能精确知道满足这个sql语句条件的记录有多少，而只能根据统计信息来估算记录数。 这个统计信息就是索引的 "区分度" 。一个索引上不同的值越多，这个索引的区分度就越好。 一个索引上不同的值的个数，称之为 "基数" 。 这个基数越大，区分度越好。

　　MySQL是通过 "采样统计" 估算基数。因为整张表的所有数据一行一会扫描统计代价太高。

　　InnoDB默认旋选择N个数据页，统计这些数据页的不同值，得到一个平均值，乘以总页数，就得到索引的基数。并且在变更的数据行数超过1/M的时候，会自动触发重新一次的索引基数统计。

　　通过参数设置 innodb_stats_persistent ，值为on时统计信息持久化存储，默认N是20，M是10；值为off的时候，统计信息存储在内存中，默认N是8，M是16。 

　　优化器也会计算，该索引是不是一个普通索引，取数据是不是会回表。

　　analyze table 表名 命令，可以重新统计索引信息，扫描行变正常。

　　索引选择异常和处理

　　　　采用force index强行选择一个索引，缺点是代码不优美，如果索引改了名字，这个语句也要改；

　　　　可以考虑修改语句，引导MySQL使用我们期望的索引；

　　　　新建一个更适合的索引，或者删除误用的索引；

9、怎么给字符串字段加索引

　　如果查询条件是某字段例如name，如果该字段没有加索引，那么sql语句就会对表全盘扫描；

　　给字段创建索引，如果不指定前缀长度，索引就会包含整个字符串；但是也可以指定索引长度，即取字段值的长度的字符串去匹配。

　　指定了合理的长度的索引，只取了该长度的值来建立索引树，占用空间更小。缺点是如果索引长度不合理，可能扫描的次数会比较多。

　　我们建立索引，指定索引的长度，关注的是区分度，区分度越高越好。区分度越高，意味着重复的键越少。

　　使用前缀索引，还得考虑会不会影响覆盖索引对查询的影响。

　　如果某字段使用索引，但是该字段的值很长字段值都不相同。所以解决办法有以下：

　　　　第一种方式是使用倒序存储，存储值的时候倒过来存，查询可以使用索引，拿的时候再颠倒。

　　　　第二种方式是使用hash字段，再创建一个整数字段，来保存身份证的校验码，在这个字段上创建索引。

　　它们都不支持范围查询，hash字段的方式支持等值查询。

10、为什么MySQL会偶尔变"慢"一下

　　脏页：当内存数据页和磁盘数据页内容不一致，这个内存也就是脏页；

　　干净页：内存数据写入磁盘后，内存和磁盘上的数据页内容一致，就是干净页；

　　平时的sql语句执行的很快，一般来说就是写内存和 redo log，偶尔的"慢"一下，就是在刷脏页（flush）；

　　发生 flush 动作的原因：

　　　　1、InnoDB的 redo log(重做日志)写满了，内存就会刷一部分脏页到磁盘里；

　　　　2、系统内存不足，当需要新的内存页存数据时，内存不足就会淘汰一些数据页，如果是脏页，就会先将脏页写到磁盘再淘汰。刷脏页一定会写入磁盘，保证了数据页有两种状态，一是内存中存在数据页，内存中的结果就肯定是正确的，直接返回。二是内存中没数据，肯定数据文件上是正确结果，读入内存后返回（已经将数据页写入磁盘，所以内存和磁盘的数据页是一致的）；

　　　　3、MySQL认为系统"空闲"的时候；

　　　　4、MySQL正常关闭的情况，MySQL会把内存的脏页都 flush 到磁盘上，下次启动 MySQL 直接从磁盘拿数据，启动速度会更快；

　　关注性能的时候，我们只会关注上面的第1和第2种情况。

　　第1种情况的话要尽量避免，因为出现这种情况表明数据都不能写到 redo log，所有的更新都阻塞。从监控来看，更新数跌为0。第2种情况是常态，内存不足。

　　InnoDB用缓冲池(buffer pool) 管理内存，缓冲池的内存页有三种状态：

　　　　1、还没使用的；

　　　　2、使用了是干净页；

　　　　3、使用了是脏页。

　　InnoDB的策略是尽量使用内存，对于一个长时间运行的库来说，未被使用的页面很少。

　　要读入的数据页没有在内存的时候，就必须到缓冲池申请一个数据页，把最久不使用的数据页从内存中淘汰。如果淘汰的是干净页，直接复用，如果是脏页，先刷数据到磁盘再复用。

　　刷脏页虽然是常态，但是出现以下情况会影响性能：

　　　　1、一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

　　　　2、日志写满，更新全部阻塞，写性能为0；

　　所以需要控制刷脏页比例。

　　InnoDB 刷脏页的控制策略

　　要正确告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候可以刷多块。所以就要用到 innodb_io_capacity 参数，值设置为磁盘的IOPS，磁盘的IOPS可以用 fio 这个工具来测试。

　　如果设置的参数太小（磁盘能支持更大的值），脏页的生成速度大于刷脏页速度，就会造成脏页累积，影响查询和更新性能。

　　所以考虑刷脏页的速度，影响因素：一个是脏页比例，一个是 redo log 写盘速度。

　　参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认75%。

　　还有个因素是"连坐"机制，如果刷脏页A，会看邻居数据页B是不是脏页，是就一起刷新，以此类推...所以可以设置参数 innodb_flush_neighbors 的值为0，取消连坐，自己刷自己。默认为1，有连坐机制。（在机械硬盘IOPS只有几百的时候，相同的逻辑减少随机IO可以提升系统性能，连坐机制很重要。到了SSD硬盘的IOPS比较高的时候，可以取消连坐只刷自己，因为 IO 是足够支持的）

　　MySQL 8.0，innodb_flush_neifhbors的默认值已经是0了。

　　扩展知识点

　　　　一个高配的机器，redo log 设置的太小，每次事务都也要写redo log ，如果设置的太小，很快会被写满，write pos一直追着CP，系统就得停止更新，去推进checkpoint。可能会出现 磁盘压力很小，但是数据库出现间歇性的性能下跌。

11、表数据删除一半，表文件大小不变

　　表数据既可以存在表空间里，也可以是单独的文件。这个行为有参数 innofb_file_per_table 控制，值OFF放系统表空间，值ON 以一个.idb为后缀的文件中。　　

　　MySQL 5.6.6版本开始默认值为 ON。

　　建议存储在单独的文件中。当不需要该表的时候，直接 drop table就可以删除这个文件，如果放在表空间，即时表删除了，空间也不会回收。

　　delete 命令，只是把在数据页上的位置，或者把数据页标记成"可复用"，但磁盘文件的大小是不会变的。记录的复用是符合在该数据页的位置的记录才能用，数据页的复用是任意其他新数据都可以用。 所以删除数据会造成空洞，并且插入数据也会造成空洞（数据页分裂，前一页会移动部分数据到分裂后的数据页上，前一页就会产生空洞）。

　　经过大量增删改操作的都可能造成数据的大量空洞。如果把空洞去掉，就能达到收缩表空间的目的。

　　重建表就做空间收缩，把表中存在的空洞去掉（其实是索引上的数据页的空洞去掉），用 alter table A engine=InnoDB 命令来重建表。

　　在MySQL5.5版本之前，做重建表操作的时候有新数据写入表A的话，就会造成数据丢失。这个DDL不是Online的。

　　在MySQL5.6版本后，引入了Online DDL之后，重建表的流程做了优化：

　　　　1、建立一个临时文件，扫描A主键的所有数据页；

　　　　2、用数据页中表A的记录生成B+树，存储到临时文件中；

　　　　3、生成临时文件的过程，将所有对A的操作记录几率在一个日志文件(row log)中；

　　　　4、临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上的一致性；

　　　　5、用临时文件替换表A的数据文件。

　　上述的操作是很消耗IO和CPU资源的，线上服务推荐使用GitHub开源的gh-ost来做。

　　给 InnoDB 表的一个字段加全文索引，写法：alter table t add FULLTEXT(field_name); 整个过程是 inplace (在InnoDB内部创建，内部完成，占用临时空间) ，会阻塞增删改操作，是非Online的。

　　DDL：数据库定义语音，创建 表、视图、索引、同义词、聚簇等，隐性提交，不能rollback。

　　扩展知识点

　　　　varchar(10)和varchar(100)存储hello没啥区别，因为支持紧凑排序（小于256都差不多）。

　　　　在表本身就没有空洞的时候，使用alter table t engine = InnoDB会让一个表占用的空间更大，因为重建表的时候，InnoDB不会把整张表都占满，每个数据页会留1/16给后续的更新用（不会导致刚重建，更新插入数据就马上产生数据页分裂）。

12、count(*)这么慢怎么办

　　InnoDB引擎执行count(*)的时候，需要把数据一行一行的从引擎中读取出来然后累积计数。

　　MyISAM引擎虽然count(*)快，但是不支持事务；

　　show table status 虽然返回很快，但是不准确（采样统计）；

　　如果我们总是要查询总数，我们只能自己计数。思路：需要自己找一个地方，把操作记录的行数存起来。

　　　　1、用缓存：缓存系统可能会丢失更新，就算不更新，计数的逻辑也可能不精确（两个不同的存储构成系统（redis、MySQL），不支持分布式事务，无法拿到精确一致的视图）；

　　　　2、在数据库保存计数：把技术直接放到数据库单独的一张表中；

　　不同的count用法

　　count(*)是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累加值。所以count()，括号里的参数"字段"（例如count(id)、count(1)）不影响个数。

　　对性能的影响：

　　　　1、count(id)，InnoDB引擎会遍历整张表，把每一行的id值都取出来返回给server层，server层拿到id后判断不为null，就累计加1。

　　　　2、count(1)，InnoDB引擎遍历整张表，不取值。server层对于返回的每一行，放一个数字值为1，判断（）里不为null，累积加1；

　　　　对比1、2的话，count(1)的速度快于count(id)，因为引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作；

　　　　3、count(字段)，该字段定义not null ，一行行从记录里面读出这个字段，不可能为null，累计加1。字段定义可以为null，执行的时候，判断到有可能为null，还要把值取出来再判断，不是null才累积加1。

　　　　所以遵循原则，server层要什么字段，InnoDB就返回什么字段。

　　　　4、count（*）是个例外，并不会把全部字段都取出来，MySQL已经优化过count(*)了，不取值，直接累加。

　　综上，按效率排序：count(字段) < count(主键id) < count(1) 约= count(*)。

　　所以尽量使用count(*)。

 

索引与日志总体扩展知识点

　　1、正常运行的实例，数据写入后的最终落盘，是从 buffer pool 而不是 redo log，原因是 redo log 并没有记录数据页的完整数据，所以它没有能力自己去更新磁盘数据页。（修改插入数据都是去修改数据页里面的行数据）。

　　2、数据落盘：

　　　　a、如果是正常运行的实例，数据页被修改后，跟磁盘的数据页不一样，产生脏页。 最终数据的落盘，是把内存中的数据页写盘。（这个过程与 redo log 毫无关系）

　　　　b、崩溃恢复场景，InnoDB判断一个数据页在崩溃恢复的时候丢失了更新，会将它读到内存，然后让 redo log 更新内存内容。 更新完后，内存数据页和磁盘的数据页又不相同，变成了脏页。此时就回到了a的情况。

 　　3、MySQL的InnoDB引擎对于数据的查询以及存储都是基于整个数据页的，而不是单独的某一条数据。

　　4、给where条件的字段加索引。　

 

该文章只是笔记，记录的是我自己大概的总结。

如果想看完整的知识点，可以去 极客时间app 上找 MySQL实战45讲 --- 林晓斌 老师的课程。

链接：https://time.geekbang.org/column/139

转载于:https://www.cnblogs.com/AlmostWasteTime/p/10229837.html