索引的类型
从存储结构上来划分：
BTree索引（B-Tree或B+Tree索引）；
Hash索引；
full-index全文索引；
R-Tree索引
这里所描述的是索引存储时保存的形式
从应用层次来分：
主键索引；
普通索引；
唯一索引；
复合索引(联合索引)；
空间索引；
根据中数据的物理顺序与键值的逻辑（索引）顺序关系：
聚集索引(聚族索引)；
非聚集索引(非聚族索引)；
总结
| 索引类型 | 概念 |
|---|---|
| 普通索引 | 一个索引只包含一个列，一个表可以有多个单列索引 |
| 唯一索引 | 索引列的值必须唯一，但允许有空值 |
| 复合索引 | 多列值组成一个索引，专门用于组合搜索，其效率大于索引合并 |
| 聚簇索引 | 也称为主键索引，是一种数据存储方式。 B+Tree结构， 非叶子节点包含健值和指针，叶子节点包含索引列和行数据。 一张表只能有一个聚簇索引。 |
| 非聚簇索引 | 不是聚簇索引，就是非聚簇索引。 叶子节点只是存索引列和主键id。 如果sql还要返回除了索引列的其他字段信息，需要回表， 第一次索引一般是顺序IO，回表的操作属于随机IO。 回表的次数越多，性能越差 |
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2015-03-20
前缀索引
因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，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))
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条记录。...
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值 二级索引 - 非聚簇索引 回表&跳表 联合索引(复合索引) 最左前缀原则 ...
2019-03-20
索引下推
MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。 有了索引下推优化，可以在减少回表次数 在InnoDB中只针对二级索引有效 官方文档中给的例子和解释如下： 在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’; 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件 如果使用了索引下推技术，则MYSQL首先会返回符合zi...
2016-10-03
创建索引
在执行CREATE TABLE时创建索引 12345678910CREATE TABLE user_index2 ( id INT auto_increment PRIMARY KEY, first_name VARCHAR (16), last_name VARCHAR (16), id_card VARCHAR (18), information text, KEY name (first_name, last_name), FULLTEXT KEY (information), UNIQUE KEY (id_card)); 使用ALTER TABLE命令去增加索引。 1ALTER TABLE table_name ADD INDEX index_name (column_list); ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。 索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一...
评论