MySQL平常索引和唯一索引的深入讲解
发布时间：2022-03-09 05:42:30 所属栏目：编程 来源：互联网
导读：场景 1、维护一个市民系统，有一个字段为身份证号 2、业务代码能保证不会写入两个重复的身份证号（如果业务无法保证，可以依赖数据库的唯一索引来进行约束） 3、常用SQL查询语句：SELECT name FROM CUser WHERE id_card = XXX 4、建立索引 身份证号比较大，
|
场景 1、维护一个市民系统，有一个字段为身份证号 2、业务代码能保证不会写入两个重复的身份证号（如果业务无法保证，可以依赖数据库的唯一索引来进行约束） 3、常用SQL查询语句：SELECT name FROM CUser WHERE id_card = 'XXX' 4、建立索引 身份证号比较大，不建议设置为主键 从性能角度出发，选择普通索引还是唯一索引？ 假设字段k上的值都不重复 查询过程 1、查询语句：SELECT id FROM T WHERE k=5 2、查询过程 通过B+树从树根开始，按层搜索到叶子节点，即上图中右下角的数据页 在数据页内部通过二分法来定位具体的记录 3、针对普通索引 查找满足条件的第一个记录(5,500)，然后查找下一个记录，直到找到第一个不满足k=5的记录 4、针对唯一索引 由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续查找 性能差异 1、性能差异：微乎其微 2、InnoDB的数据是按照数据页为单位进行读写的，默认为16KB 3、当需要读取一条记录时，并不是将这个记录本身从磁盘读出来，而是以数据页为单位进行读取的 4、当找到k=5的记录时，它所在的数据页都已经在内存里了 5、对于普通索引而言，只需要多一次指针寻找和多一次计算 – CPU消耗很低 如果k=5这个记录恰好是所在数据页的最后一个记录，那么如果要取下一个记录，就需要读取下一个数据页 概率很低：对于整型字段索引，一个数据页（16KB，compact格式）可以存放大概745个值 change buffer 1、当需要更新一个数据页时，如果数据页在内存中就直接更新 2、如果这个数据页不在内存中，在不影响数据一致性的前提下 InnoDB会将这些更新操作缓存在change buffer 不需要从磁盘读入这个数据页（随机读） 在下次查询需要访问这个数据页的时候，将数据页读入内存 然后执行change buffer中与这个数据页有关的操作（merge） 3、change buffer是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上 4、将更新操作先记录在channge buffer，减少随机读磁盘，提升语句的执行速度 5、另外数据页读入内存需要占用buffer pool，使用channge buffer能避免占用内存，提高内存利用率 6、change buffer用到是buffer pool里的内存，不能无限增大，控制参数innodb_change_buffer_max_size # 默认25，最大50 mysql> SHOW VARIABLES LIKE '%innodb_change_buffer_max_size%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_change_buffer_max_size | 25 | +-------------------------------+-------+ merge 1、merge：将change buffer中的操作应用到原数据页 2、merge的执行过程 从磁盘读入数据页到内存（老版本的数据页） 从change buffer里找出这个数据页的change buffer记录（可能多个） 然后依次执行，得到新版本的数据页 写入redolog，包含内容：数据页的表更+change buffer的变更 3、merge执行完后，内存中的数据页和change buffer所对应的磁盘页都还没修改，属于脏页 通过其他机制，脏页会被刷新到对应的物理磁盘页 4、触发时机 访问这个数据页 系统后台线程定期merge 数据库正常关闭 使用条件 1、对于唯一索引来说，所有的更新操作需要先判断这个操作是否违反唯一性约束 2、唯一索引的更新无法使用change buffer，只有普通索引可以使用change buffer 主键也是无法使用change buffer的 例如要插入(4,400)，必须先判断表中是否存在k=4的记录，这个判断的前提是将数据页读入内存 既然数据页已经读入到了内存，直接更新内存中的数据页就好，无需再写change buffer 使用场景 1、一个数据页在merge之前，change buffer记录关于这个数据页的变更越多，收益越大 2、对于写多读少的业务，页面在写完后马上被访问的概率极低，此时change buffer的使用效果最好 例如账单类、日志类的系统 3、如果一个业务的更新模式为：写入之后马上会做查询 虽然更新操作被记录到change buffer，但之后马上查询，又会从磁盘读取数据页，触发merge过程 没有减少随机读，反而增加了维护change buffer的代价 更新过程 插入(4,400) 目标页在内存中 对于唯一索引来说，找到3~5之间的位置，判断没有冲突，插入这个值 对于普通索引来说，找到3~5之间的位置，插入这个值 性能差异：微乎其微 目标页不在内存中 1、对于唯一索引来说，需要将数据页读入内存，判断没有冲突，插入这个值 磁盘随机读，成本很高 对于普通索引来说，将更新操作记录在change buffer即可 减少了磁盘随机读，性能提升明显 索引选择 1、普通索引与唯一索引，在查询性能上并没有太大差异，主要考虑的是更新性能，推荐选择普通索引 2、建议关闭change buffer的场景 如果所有的更新后面，都伴随着对这个记录的查询 控制参数innodb_change_buffering mysql> SHOW VARIABLES LIKE '%innodb_change_buffering%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_change_buffering | all | +-------------------------+-------+ # Valid Values (>= 5.5.4) none / inserts / deletes / changes / purges / all # Valid Values (<= 5.5.3) none / inserts # change buffer的前身是insert buffer，只能对insert操作进行优化 change buffer + redolog 更新过程 当前k树的状态：找到对应的位置后，k1所在的数据页Page 1在内存中，k2所在的数据页Page 2不在内存中 INSERT INTO t(id,k) VALUES (id1,k1),(id2,k2); # 内存：buffer pool # redolog：ib_logfileX # 数据表空间：t.ibd # 系统表空间：ibdata1 1、Page 1在内存中，直接更新内存 2、Page 2不在内存中，在changer buffer中记录：add (id2,k2) to Page 2 3、上述两个动作计入redolog（磁盘顺序写） 4、至此事务完成，执行更新语句的成本很低 写两次内存+一次磁盘 5、由于在事务提交时，会把change buffer的操作记录也记录到redolog 因此可以在崩溃恢复时，恢复change buffer 虚线为后台操作，不影响更新操作的响应时间 读过程 假设：读语句发生在更新语句后不久，内存中的数据都还在，与系统表空间（ibdata1）和redolog（ib_logfileX）无关 SELECT * FROM t WHERE k IN (k1,k2); 1、读Page 1，直接从内存返回（此时Page 1有可能还是脏页，并未真正落盘） 2、读Page 2，通过磁盘随机读将数据页读入内存，然后应用change buffer里面的操作日志（merge） 生成一个正确的版本并返回 提升更新性能 1、redolog：节省随机写磁盘的IO消耗（顺序写） 2、change buffer：节省随机读磁盘的IO消耗 总结 以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值。 （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
