MySQL学习随笔03

MySQL索引原理

索引的数据结构

B+树 ，b+树应运⽽⽣（B+树是通过⼆叉 查找树，再由平衡⼆叉树，B树演化⽽来）

b+树性质 1**.索引字段要尽量的⼩**：通过上⾯的分析，我们知道IO次数取决于b+数的⾼度h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N⼀定的情况下，m越⼤，h越⼩；⽽m = 磁 盘块的⼤⼩ / 数据项的⼤⼩，磁盘块的⼤⼩也就是⼀个数据⻚的⼤⼩，是固定的，如果数据项占的空间越⼩， 数据项的数量越多，树的⾼度越低。这就是为什么每个数据项，即索引字段要尽量的⼩，比如int占4字节，要 比bigint8字节少⼀半。这也是为什么b+树要求把真实的数据放到叶⼦节点⽽不是内层节点，⼀旦放到内层节 点，磁盘块的数据项会⼤幅度下降，导致树增⾼。当数据项等于1时将会退化成线性表。 2.索引的最左匹配特 性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索 树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下⼀步的所搜⽅向，如果 name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就 不知道下⼀步该查哪个节点，因为建立搜索树的时候name就是第⼀个比较因⼦，必须要先根据name来搜索才 能知道下⼀步去哪⾥查询。比如当(张三,F)这样的数据来检索时，b+树可以⽤name来指定搜索⽅向，但下⼀个 字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性 质，即索引的最左匹配特性

聚集索引与辅助索引

在数据库中，B+树的⾼度⼀般都在2~4层，这也就是说查找某⼀个键值的⾏记录时最多只需要2到4次IO，这倒 不错。因为当前⼀般的机械硬盘每秒⾄少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。 数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）， 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即⾼度是平衡的，叶 ⼦结点存放着所有的数据。 聚集索引与辅助索引不同的是：叶⼦结点存放的是否是⼀整⾏的信息

1、聚集索引

聚集索引的好处之⼀：它对主键的排序查找和范围查找速度非常快，叶⼦节点的数据就是⽤户所要查询的数 据。如⽤户需要查找⼀张表，查询最后的10位⽤户信息，由于B+树索引是双向链表，所以⽤户可以快速找到最 后⼀个数据⻚，并取出10条记录

聚集索引的好处之⼆：范围查询（range query），即如果要查找主键某⼀范围内的数据，通过叶⼦节点的上 层中间节点就可以得到⻚的范围，之后直接读取数据⻚即可

2、辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是： 辅助索引的叶⼦节点不包含⾏记录的全部数据。 叶⼦节点除了包含键值以外，每个叶⼦节点中的索引⾏中还包含⼀个书签（bookmark）。该书签⽤来告诉 InnoDB存储引擎去哪⾥可以找到与索引相对应的⾏数据。

聚集索引和非聚集索引的区别

聚集索引 1.纪录的索引顺序与⽆⼒顺序相同 因此更适合between and和order by操作

               2.叶⼦结点直接对应数据 从中间级的索引⻚的索引⾏直接对应数据⻚

               3.每张表只能创建⼀个聚集索引

非聚集索引 1.索引顺序和物理顺序⽆关

                   2.叶⼦结点不直接指向数据⻚

                   3.每张表可以有多个非聚集索引，需要更多磁盘和内容 多个索引会影响insert和update的速度

 

MySQL索引管理

⼀ 功能

#1. 索引的功能就是加速查找

#2. mysql中的primary key，unique，联合唯⼀也都是索引，这些索引除了加速查找以外，还有约 束的功能

⼆ MySQL常⽤的索引

普通索引INDEX：加速查找

唯⼀索引：

     -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

     -唯⼀索引UNIQUE:加速查找+约束（不能重复）

联合索引：

   -PRIMARY KEY(id,name):联合主键索引

   -UNIQUE(id,name):联合唯⼀索引

   -INDEX(id,name):联合普通索引

 

创建/删除索引的语法

#⽅法⼀：

创建表时 CREATE TABLE 表名 (

             字段名1 数据类型 [完整性约束条件…],

             字段名2 数据类型 [完整性约束条件…],

             [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY

             [索引名] (字段名[(⻓度)] [ASC |DESC])

);

 

#⽅法⼆：

     CREATE在已存在的表上创建索引

     CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(⻓度)] [ASC |DESC]) ;

 

#⽅法三：

     ALTER TABLE在已存在的表上创建索引

     ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(⻓度)] [ASC |DESC]) ;

 

#删除索引：

    DROP INDEX 索引名 ON 表名字;