mysql索引

原创已于 2022-08-03 21:36:05 修改 · 166 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #sql

于 2020-09-07 17:12:46 首次发布

mysql 专栏收录该内容

27 篇文章

订阅专栏

索引类型

普通索引
CREATE INDEX index_mytable_name ON mytable(name);
唯一索引
索引列的值必须唯一，但允许有空值
主键索引
组合索引
全文索引（fulltext）
只有char、varchar，text 列上可以创建全文索引

覆盖索引

使用联合索引，查询的字段都有索引，由覆盖索引可以减少树的搜索次数，不用回表，显著提升查询性能

联合索引最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
索引列类型为字符串，where查询条件没有加引号，索引失效
查询条件字段和联合索引是完全匹配的话，顺序不对也可以走联合索引，mysql优化器会优化
查询条件字段和联合索引不是完全匹配的话，根据最左匹配原则

唯一索引和普通索引的选择（业务代码保证该字段唯一的情况下）

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。

唯一索引不能使用change buffer，只有普通索引可以使用change buffer
唯一索引的更新操作，要先判断这个更新是否违反唯一性约束性，要将数据页读入内存中才能判断。若数据页都在内存中，则更新内存会很快，无需使用change buffer。
而在其他情况下，change buffer 都能提升更新性能。在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新
而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下：1、 InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了；2、在下次查询需要访问这个数据页的时候，将数据页读入内存；3、然后执行 change buffer 中与这个页有关的操作。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。
除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

change buffer 的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，change buffer适合写多读少的业务。比如说日志，账单等场景

反之，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

前缀索引

MySQL是支持前缀索引的，可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息。