Mysql索引

目录

一、索引底层实现原理

二、数据结构为B+树

三、索引分类

四、索引的设计原则

五、扩展知识点

---

一、索引底层实现原理

索引的作用：提高查询效率

Mysql数据存储：磁盘

索引数据存储：磁盘 

Mysql应用程序启动时将磁盘的索引数据加载到内存中，减少IO次数，减少IO量（大小）。

大文件加载到内存中时，采用分块读取，块大小默认为16k。

show variables like 'innodb_page_size';

值/1024=16k，可进行修改。

磁盘预读：内存在跟磁盘交互的时候，有一个最基本的逻辑单位，称之为页，也叫做datapage,一般情况下是4k，跟操作系统相关，我们进行数据读取的时候默认读取页的整数倍。

在innodb中，每次读取数据大小为16k。

每一个块的数据格式：K-V             K：索引列的值    V：行记录

二、数据结构为B+树

为什么数据结构没有使用哈希表和其他树？

哈希表：底层结构数组+链表/红黑树

                哈希碰撞和哈希冲突的概率高，需要设计性能优良的哈希算法才能避免

                哈希表是无序的，当需要进行范围查询的时候必须要逐个进行对比，效率较低

（memory存储引擎选择的是哈希索引，innodb存储引擎中支持自适应哈希，有可能会有哈希索引）

二叉树、BST、AVL、红黑树：至多只有两个分支，每个块中存储一个节点（innodb每块大小16k，存储一个节点浪费，且层级多时查询效率慢）

                                                        除二叉树，其他三棵树有序

                                                        AVL、红黑树是平衡二叉树

B树：树的每个节点存储多个值，每个值会记录相应的键值（表中记录的主键）、指针（存储子节点的地址信息）、数据（表数据中除主键外的数据）。

B+树：叶子节点存储全树的键值、数据且相互连接（双向链表），其他节点存储指针、键值，存储数据量远大于B树。

 一般情况下，3~4层的B+树足以支撑千万级的数据存储。

在选择索引列的时候，占用的存储空间越小越好。

在满足业务需要的情况下，尽可能使用主键自增方式，避免叶分裂、叶合并。

小问题：

1. Mysql的一张表中可以创建多少个索引？                                                                                         理论上来讲没有限制，跟列的个数相关，但索引并不是越多越好。
2. 当一张表有多个索引时，是一个索引对应一棵树，还是多个索引对应一棵树？                        一个索引对应一棵树。
3. 索引的叶子节点存储的时数据，当多个索引的时候，数据存储几份？                                          一份
4. 如果只存储一份的话，那么其他的索引叶子节点存储什么？                                                          primary key（聚簇索引的key值）

在innodb存储引擎中，数据在进行插入的时候必须与某一个索引列绑定在一起，如果表中有主键，则与主键绑定；如果没有主键，那么会与表中唯一键绑定；如果没有唯一键，那么就会生成一个6字节的rowid进行绑定存储。

三、索引分类

聚簇索引(聚集索引)：跟数据绑定存储的索引。

非聚簇索引（二级索引）：没有跟数据绑定存储的索引。

非聚簇索引的叶子节点存储的是聚簇索引的列值。

表名：student   字段：id,name,age,grade,school,sex  主键：id  普通索引：name  联合索引：age,grage,sex,school

回表：

select  * from student where name = ’11‘;

根据name查询name的B+树，取到primary key。再通过primary key查询primary key的B+树，拿到数据。整个过程就是回表，查询两次树，效率低。

覆盖索引：

select  id,name from student where name = ’11‘;

根据name查询name的B+树，取到id(primary key)。索引覆盖效率高。

前缀索引：

create index id_xxx on table_name(column(n))

字段类型（varchar、text等）为大文本字符串时，需要建立前缀索引来控制长度，避免浪费磁盘IO，影响查询效率。

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

例如：

 select  * from student where grade = 2;

        匹配不到(age,grade,sex,school)联合索引

select  * from student where sex = 1 and age = 16 and grade = 2 ;

        匹配到(age,grade,sex,school)联合索引,且优化器会自动调整顺序  

select  * from student where sex = 1 and age = 16 and grade > 2 ;    

        匹配到(age,grade,sex,school)联合索引,sex没有用到索引，grade范围查询之后会停止匹配

四、索引的设计原则

• 数据量大，且查询频繁；
• 经常被作为查询条件、排序、分组的字段，创建联合索引更好；
• 区分度较高的简历索引，尽量建立唯一索引，区分度越高，使用索引的效率越高；
• 字符串较长，建议建立前缀索引；
• 尽量使用联合索引，避免回表；
• 控制索引数量，索引越多，维护代价越高，影响增删改效率；
• 索引列是否为null，如明确，则创建索引时也明确限制，可以更好的确定哪个索引最有效的用于查询。

五、扩展知识点

OLAP(On-Line Analytical Processing 联机分析处理)：对历史数据的分析、产生决策性策略，数据仓库。例如：hive

OLTP(On-Line Transaction Processing 联机事务处理)：满足业务系统需要、效率要求高，数据库。例如：Mysql、Oracle、DB2