什么是索引
索引就像书里面的目录,可有快速帮我们定位我们想要的数据在什么位置。
换到数据库中,索引的定义就是帮助存储引擎快速获取数据的一种数据结构,形象的说就是索引是数据的目录。
索引类型
- 按[数据结构] 分类:B+tree索引、Hash索引、Full-text索引。
- 按[物理存储] 分类:聚簇索引(主键索引),二级索引(辅组索引)
- 按[字段特性] 分类:主键索引,唯一索引,普通索引,前缀索引。
- 按[字段个数] 分类:单列索引,联合索引。
按数据结构分类
innodb在mysql5.5之后成为了mysql默认的存储索引,B+tree索引类型也是mysql存储索引采取最多的索引类型。
在创建表时,innodb存储引擎根据不通的场景选择不同的列作为索引:
- 如果有主键,默认会使用主键作为聚簇索引的索引键(key);
- 如果没有主键,就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键;
- 如果上面两个都不存在的情况下,innodb将自动生成一个隐式自增id列作为聚簇索引的索引键;
什么是B+Tree
- B+tree是一种多叉树,叶子节点才存放数据,非叶子节点只存放索引,每个节点里面存放的是主键。
- 每一层父节点的索引值都会出现在下层子节点的索引值中,因此在叶子节点中,包括了所有的索引值信息。
- 每个叶子节点都有两个指针,分别指向上一个节点和下一个节点,形成了双向链表。
如何通过b+tree查询数据呢?
- 先在根节点中寻找,当前的id是在哪个范围里。
- 在根节点中找到了范围,进入第二层,在第二层里面寻找当前id是在第二层节点的哪个范围。
- 在第二次找到范围后,进入第三层叶子节点,通过叶子节点的id比较找到对应的数据。
二级索引
- 我们以某个字段创建二级索引,那么会通过该字段创建出一个b+tree,节点里面存放的是以该字段床架的索引(橙色部分)
- 子叶子节点里面存放的是索引字段和主键值。
主键索引的B+ tree和二级索引的B+Tree的区别:
- 主键索引的b+tree的叶子节点存放的是实际完整的数据。
- 二级索引的b+tree的叶子节点存放的是数据的主键,而不是所有的数据。
- 我们通过二级索引取查询数据的时候分两种情况,如果我们只获取主键这个字段,那么通过二级索引查询数据的时候是直接可以获取到id的,如果我们想获取到完整的数据,通过二级索引查询数据,我们获取到key以后,还是需要通过回表查询来获取到全部的数据。(也就是说我们需要查询两个b+tree)
b+tree和数据结构的区别
b+ tree
- b + tree 只在叶子节点存储数据,所有的叶子节点都通过指针相连,这个对范围查询非常有利,因为可以通过指针从一个叶子节点移动到下一个叶子节点。(有序的双向链表),所有的叶子节点在同一层
- b + tree 非叶子节点有多少个子节点,非叶子节点不存储数据,比高度相同的b-tree有更多的分支,减少了给定键的io操作。
- b + tree由于数据都在叶子节点上,而且叶子节点通过链表相连,所以数据的物理存储是稳定的,不会因为插入和删除操作而改变。
- b + tree广泛的用于数据库和磁盘读写,因为查询写入的时候效率高。
- b + tree 比b - tree 更矮更胖。
在innoDb中一页默认存储16k的数据,一个bigint是8字节,一个指针是6字节,所以一个非叶子节点能存储的单元数 (16k*1024 / 14 byte=1170个)假设一个数据大小为1k,那么底层叶子节点一页16k就能存16条记录,那么2层高度的b+tree 能记录的数据数约16 * 1170 = 18720, 那么3层高度的b+tree 能记录的数据数约 16 * 1170 * 1170=21902400 ,2190w条记录,那么得出3层高度的b+tree 可以存储 2000w条数据。
b- tree
- 自平衡:在删除和插入节点的时候,会通过旋转和重新平衡操作来保持树的平衡。
- 多节点:b-tree 不同于二叉树,每个节点可以有多于两个的子节点。
- 所有的叶子节点都在同一层,有助于高效的磁盘读写。
- 节点包含多个键和指针,键用于搜索和排序,指针指向存储的数据
- 高度较低,由于b树的每个节点可以有多个子节点,因此b树的高度较低,这有助于在磁盘上查找数据。
- 查询速度是O(logn)
二叉树
- 每个节点最多有两个子节点。
- 子节点有左右之分,key 小于根节点那么他是左子节点,key大于根那么他是右子节点。
- 二叉树的子树也是二叉树
- 当每次插入的元素都是最大元素的时候,二叉树会退化成一个链表查询速度从O(logN)退化成O(n)。
hash
- hash查询速度快,是通过hash函数将元素映射到一个大的空间中,使得我们可以快速定位到元素的位置,查询速度是o(1)。
- hash表的性能在很大程度上取决与hash函数的质量和解决hash冲突的方法。
- hash表不维护元素的有序性,因此不能做范围查询。
- hash表更适合做内存中的数据存储,因为可以提供快速的访问。
防止hash冲突的方法:
- 开放寻址:发现有hash冲突的时候,寻找下一个空的hash地址
- 链地址法:每个hash桶都维护一个链表,所有的hash值相同的元素都存储在这个链表中,当发生冲突的时候,只需要在链表尾部添加新的元素。
索引类型
- 主键索引:主键索引的b+tree的叶子节点里面存放的是实际数据
create table table_name (
PRIMARY KEY(id) USING BTREE
)
create primary key 索引名称 on table_name(key)
ALTER TABLE table_name
ADD PRIMARY KEY (ID);
- 二级索引: 二级索引的b+tree 里面存放的是主键(id),二级索引在查询的时候如果只获取主键,这不需要回表,如果要获取主键之外的数据则需要回表查询。
CREATE TABLE table_name (
ID INT NOT NULL,
Name VARCHAR(100),
PRIMARY KEY (ID),
INDEX (Name)
);
create index 索引名称 on table_name(key);
ALTER TABLE table_name ADD INDEX (Name);
- 唯一索引:一张表可以在多个字段上创建unique 索引,但是索引的列必须的唯一的,允许为空
create table table_name (
UNIQUE (name,name1...)
)
create unique index 索引名称 on table_name(key);
alert table table_name ADD UNIQUE(name);
- 普通索引:在普通字段上创建的索引,既没有主键索引一样需要设置主键,也没有唯一索引需要列数据唯一
CREATE TABLE table_name (
....
INDEX(index_column_1,index_column_2,...)
);
create index 索引名称 on table_name(name1, name2)
alert table table_name add INDEX(name1)
- 前缀索引:前缀索引是指对字段的前几个字符创建索引,而不是整个字段,前缀索引可以建立的字段类型有,char,varchar,binary,varbinary。
CREATE TABLE table_name(
ID INT NOT NULL,
Name VARCHAR(255),
PRIMARY KEY (ID),
INDEX (Name(10))
);
create index 索引名称 on table_name(Name(length));
ALTER TABLE table_name ADD INDEX (Name(10));
- 联合索引: 通过将多个字段组合成一个索引,我们称之为联合索引。联合索引的非叶子节点用两个字段作为索引,叶子节点里面是索引和主键,叶子节点和叶子节点之间使用指针相连。
create index index_product_on_name ON product(product_on, name);
使用联合索引的时候要遵循最左匹配原则,也就是按照最左边优先的方式进行索引匹配,如果最左边的不在索引字段里面,那么联合索引就会失效。
我们这里举联合索引的例子(a,b)a索引是(1,2,3,4,5,6,7,8,9),b索引是(12,7,8,2,3,8,10,5,2),如果我们跨过a索引去使用b索引查询,因为b索引是无序的所以联合索引失效了。
联合索引范围查询
select a,b from table_name where a > 1 and b = 2;
因为联合索引是先按照a字段进行排序的,所以a > 1的查询条件的索引记录是肯定相邻,所以进行了索引扫描的时候定位到了符合a>1的第一条记录,但是在a>1条件的二级索引记录的范围里,b = 2的字段是无序的,因此我们不能根据条件查询b = 2来减少扫描数据的数量,所以得出在上述sql 中a >1用到了联合索引,而b字段没有使用联合索引。
如果数据库里面有多余字段,那么使用*查询回导致联合索引失效。
select * from t_table where a >= 1 and b = 2
因为联合索引是先按照a字段的值排序的,所以a >= 1的查询条件的索引记录是肯定相邻,所以进行了索引扫描的时候定位到了符合a>=1的第一条记录,虽然在a >=1添加查询的范围里,b字段的值是无序的,但是对于符合a = 1的二级索引的记录里,b字段的值是有序的,所以对于联合索引,是先按照 a 字段的值排序,然后在 a 字段的值相同的情况下,再按照 b 字段的值进行排序。所以以上这条查询语句a和b字段都使用到了联合索引。
SELECT a,b from t1 where a BETWEEN 2 and 6 and b = 2;
因为在BETWEEN 查询里面,在mysql中相当于 >= and =<,同理上述sql a>= 2 AND a <= 6 的条件查询的索引记录是相邻的,所以在进行索引扫描的时候定位到了a>=2的第一条数据,虽然在 a>=2 AND a <= 6 的查询范围里,b字段是无序的,但是对于符合 a = 2 and a =6的时候b字段是有序的,所以上面的sql用到了a和b的联合索引
SELECT * FROM t_user WHERE name like 'j%' and age = 22
因为联合索引是先按照name字段的值排序的,所以前缀为j的name字段的二级索引记录都是相邻的,于是在进行索引扫描的时候,可以定位到符合前缀为j的name的第一条记录,在前缀为j的索引记录里面 age字段是有序的,那么可以判断在上述sql里面name和age的联合索引是有效的。
SELECT * FROM t_user WHERE name like '%j' and age = 22
因为联合索引是先按照name字段的值排序的,但是因为通配符在左边所以匹配的是j结尾的,所以在二级索引记录里面是不相邻的,又因为联合索引需要遵循最左匹配,所以name和age没有命中联合索引
总结
从上面的sql中我们得出联合索引在遇到 > 和 < 做范围查询的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询后的字段是无法使用联合索引的,但是对于 >= 和 <=,BETWEEN,like,前缀匹配的查询,并不会停止匹配,匹配后的字段因为在联合索引里是有序的,所以可以使用联合索引。
索引如何优化
- 前缀索引
- 使用前缀索引是为了减小索引字段的大小,可以增加一个索引页中存储的索引值,有效的提高索引的查询速度。
- 前缀索引无法使用order by
- 无法把前缀索引用作覆盖索引
- 覆盖索引:
- 覆盖索引是指 SQL 中 query 的所有字段,在索引 B+Tree 的叶子节点上都能找得到的那些索引,从二级索引中查询得到记录,而不需要通过聚簇索引查询获得,可以避免回表的操作。
- 主键索引
- 主键索引最好是自增,因为每次插入新的数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,当页面写满,就会自动开辟一个新页面。如果我们不使用自增作为主键,那么每次插入的时候就可能回插入到现有数据也中间的某个位置,甚至需要从一个页面复制数据到另一个页面,我们通常将这种情况称为页分裂,页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
- 索引最好不要设置为空
- 索引列在存在null就会导致优化器在做索引选择的时候更加复杂,因为null的列回使索引,索引统计和值比较都变的更加复杂。
- NULL 值是一个没意义的值,但是它会占用物理空间,所以会带来的存储空间的问题,因为 InnoDB 存储记录的时候,如果表中存在允许为 NULL 的字段,那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。
如何防止索引失效
- 当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效;
- 当我们在查询条件中对索引列做了计算,函数,类型转换的时候,就会导致索引失效
- 联合索引要能正确使用最左原则,也就是按照最左优先的方式进行索引匹配,负责就会失效。
- 在wehere 子句中,如果在or 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。