前缀索引
因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。
流程是：
先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1
再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1
找到最优长度之后，创建前缀索引 :create index idx_front on table_1 (col_1(4))
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2021-07-23
MySQL索引数据结构原理
二叉树 - 链表结构 - 相当于全表扫描 大于自己的值，放在右边，比自己小的放在左边 红黑树 - 二叉平衡树 - 数据量大时，树的高度不可控 B-Tree树 非叶子节点也会存储数据 没有维护双向指针 树的高度 由非叶子节点 的叶叉树来决定的 16^n^ = 2Kw B+Tree - 多叉平衡树 B-Tree树的变种 树的高度可控 非叶子节点，作为一个冗余，不存储数据 1170 * 1170 * 16 = 2Kw 叶节点大小 16KB - 16384字节 Hash - hash运算 hashmap 桶 - 一次 I/O hash冲突 仅支持 = 等值，不支持范围查询 主键 必须有，否则MySQL会自行选择一列作为 聚簇索引，如果没有，MySQL 会做个 隐藏的 聚簇索引 且为整型 数值存储空间要小 数值比较效率要比字符串比较高 数值自增排序，避免页分裂 ASCII值 二级索引 - 非聚簇索引 回表&跳表 联合索引(复合索引) 最左前缀原则 ...
2017-03-20
索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针。 索引是一种数据结构。 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。 索引的实现通常使用B树及其变种B+树。 更通俗的说，索引就相当于目录。 为了方便查找书中的内容，通过对内容建立索引形成目录。 而且索引是一个文件，它是要占据物理空间的。 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等， 然后找到对应字典页码， 这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。
2018-03-20
索引创建的原则
建索引的原则 1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。 3、尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。...
2017-03-20
索引回表
回表 查询时一些字段值拿不到，需要到主键索引B+树再查一次。 根据索引进行条件查询，回到主键索引树进行搜索的过程 因为查询还要回表一次，再次查询主键索引树，所以实际中应该尽量避免回表的产生。 解决回表问题可以建立联合索引进行索引覆盖，如图所示根据name字段查询用户的name和sex属性出现了回表问题： 那么我们可以建立下面这个联合索引来解决： 123456create table user ( id int primary key, name varchar(20), sex varchar(5), index(name, sex)) engine = innodb; 建立了如上所示的index(name,sex)联合索引，在二级索引的叶子结点的位置就会同时也出现sex字段的值，因为能够获取到要查询的所有字段，因为就不用再回表查询一次。 强制索引 force index
2020-03-20
索引采用的算法
索引为什么采用B+树，而不用B-树，红黑树 提升查询速度，首先要减少磁盘I/O次数，也就是要降低树的高度。 平衡二叉树、红黑树，都属于二叉树。 时间复杂度为O(n)，当表的数据量上千万时，树的深度很深，mysql读取时消耗大量 IO。 另外，InnoDB引擎采用页为单位读取，每个节点一页， 但是二叉树每个节点储存一个关键词，导致空间浪费。 B-树，非叶子节点存储数据，占用较多空间， 导致每个节点的指针少很多，无形增加了树的深度。 B+树数据都存储在叶子节点，非叶子节点只存储健值+指针， 索引树更加扁平，三层深度可以支持千万级表存储。 同时叶子节点之间通过链表关联，范围查找更快。
2019-05-20
索引失效的 12 种原因
一句话 索引失效 = MySQL 优化器决定不用你建的索引去执行。 永远用 EXPLAIN 验证，不要靠记忆。 12 种失效场景1. WHERE 列上做函数 / 表达式1234567-- ❌ 索引失效SELECT * FROM users WHERE YEAR(created_at) = 2024;SELECT * FROM users WHERE id + 1 = 100;-- ✅ 改写：把函数挪到右边SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';SELECT * FROM users WHERE id = 99; MySQL 8.0+ 支持 函数索引：ALTER TABLE users ADD INDEX idx_year ((YEAR(created_at))) 2. 隐式类型转换123456-- 字段是 VARCHAR，传了 INT-- ❌ 走全表扫SELECT * FROM...
评论
