数据库bitcount走索引吗 数据库索引normal

MySql索引

创建一个索引

alter table sys_user add index idx_user_name(name)

定义
数据库索引，是数据库管理系统 (DBMS) 中一个排序的数据结构，以协助快速查询、更新数据库表中数据

哪怕没有唯一字段，也会有个默认行号，这个就成了索引了

索引文件 和 表数据有个对应关系

索引类型

• Normal：普通索引，非唯一索引，这个是默认选项（name,gender两个联合索引，也是普通索引）
• Unique：唯一索引，主键索引是特殊的唯一索引【没有Null值】
• Fulltext：全文索引，文本信息的查询 像char varchar text
  MyIsAM

Hash索引和B+Tree索引的对比

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引

但是hash有它的局限

• Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询
  由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样
• Hash 索引无法被用来避免数据的排序操作
  由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
• Hash 索引不能利用部分索引键查询
  对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用
• Hash 索引在任何时候都不能避免表扫描
  前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
• 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

索引数据的存储

要考虑的是怎么减少从索引文件中匹配到满足条件的索引的次数

• 线性结构

• （数组，集合），需要顺序检索

• 二分查找

• 二叉查找树，在极端情况下会变成一颗斜树（可以说是链表）
• 平衡二叉树（红黑树），特点是左边的节点深度和右边节点深度相同，这里用到左旋和右旋来实现平衡
  假设有500W条记录，那么平衡二叉树子节点深度最少也会有10W，那么检索索引带来的IO次数也不少
• 平衡多叉树，存储引擎中操作索引的最小的单位是page【页】16KB（116384B）一个索引中记录的信息（index， 数据的物理地址， Left node Right node）100B 此时会出现资源浪费（举例子说一个人住了一个20层楼），解决方案是在一个节点中保存多个索引信息

• B树【多路平衡查找树】

• 叶子节点具有相同的深度，叶子结点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• 节点拥有的子树数量称为度，关键字数：N ，则度是N+1
• 不推荐频繁的添加和删除数据操作，因为会变动整个树结构，浪费性能

查询MySQL中树索引的页大小(结果是16KB)

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

B+树

这个就是MySQL里用到的BTREE，是加强版的B树在B+树里，度=关键字数

所以两层的树就能存2000W条数据了

• 节点中数据的个数和节点的路数相同
• B+树的叶子节点存储的就是表结构的数据
• B+树中的叶子节点都是连着的
• 不管怎么样都会检索到B+树的叶子节点
• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

数据存储文件

.frm 是数据库表结构文件，在8.0之前的版本是有的，8.0之后优化没有了

• InnoDB

• .ibd存储的就是索引文件和数据文件
• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶子节点包含了完整的数据记录
• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

• 其实没有主键mysql也会创建一个类似rowid的字段来做索引
• 比较的效率快，其次整型占用空间小
• 如果不是自增的话，叶子节点为了满足递增的话，会导致叶子分裂，重新平衡，浪费性能

• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）

• MyIsAM

• .MYD 存储的是数据
• .MYI 存储的是索引
• 是非聚集索引

主键索引 普通索引

每建一个索引都是一个B+树

索引的使用原则

• 列的离散度（count(distinct(column_name)):count(*)）离散度越高越推荐使用索引
• 联合索引



• 最左匹配原则

MySQL查询优化器

回表
就是说按照索引查询出来后，然后再通过查询出来的主键再去查询一下对应的*（所有数据）

覆盖索引
就是命中了，比如说索引是 （name, phone），然后查询的select里只有name和phone，此时就是命中了。