mysql 索引总结

最新推荐文章于 2022-12-26 17:10:37 发布

ambity_lyf

最新推荐文章于 2022-12-26 17:10:37 发布

阅读量116

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/abc13663873130/article/details/103103274

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 简介

mysql 的查找语句执行时有两种搜索信息方式，一种是全表扫描,将数据取出，一一进行对比，然后将数据的结果集返回，会造成大量的io,并消耗数据库时间，第二种就是通过索引找到数据，然后将其返回。使用索引可以避免全表扫描，提高查找速度，快速查找数据。

2. 常见的索引操作

2.1 添加索引

CREATE TABLE user_a(
	name varchar(20),
	id bigint,
	PRIMARY KEY(id)
);

添加唯一索引
CREATE TABLE user_b(
	name varchar(20),
	id bigint UNIQUE
);


 添加普通索引
CREATE TABLE user_c(
	name varchar(20),
	id bigint,
	INDEX ordinary_index(id)
);
 创建全文索引

CREATE TABLE user_d(
	name varchar(20),
	id bigint,
	content text not null,
	FULLTEXT KEY text_full(content)
);

 创建复合索引
CREATE TABLE user_e(
	name varchar(20),
	id bigint,
	INDEX ordinary_index(id,name)
);

2.2 创建表后操作索引
创建表后添加普通索引
ALTER TABLE table_name ADD INDEX index_name(column_name);
创建表后添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
创建表后添加唯一索引
ALTER TABLE table_name ADD UNIQUE KEY index_name(column_name);
创建表后添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_name(column_name);
删除列上的索引
Alter table table_name drop INDEX column_name;

3.索引的数据结构

mysql 常用的两种引擎INNODB与MyIsam 默认使用的B+ 树作为索引的数据结构，下面对其做简单介绍。

3.1 B-Tree

-.根节点至少包含两个孩子
-.树中的每个节点最多含有m 个孩子
-除根节点与叶节点外，其他每个节点至少含有ceil(m/2)个孩子
-.所有的叶子节点都位于同一层
-. 假设每个非终端结点中包含有n 个关键字信息
a) Ki 为关键字，且关键字按顺序升序排序
b) 关键字的个数必须满足
[ceil(m/2)-1]<=n<m-1
c) 非叶子节点的指针“P[1],p[2],…p[M];其中P[1]指向关键字小于K【1】的子树P[M]指向关键字大于K[M-1]的子树，其他P[i]指向关键字属于(K【i-1】,K【i】）的子树

3.2 B+树：

B+树是B 树的变种，与经典的B 树结构相比，主要有以下不同
-.非叶子节点的子树指针与关键字个数相同
-.非叶子节点的子树指针p[i],指向关键字值[k(i),k[i+1]] （此处为闭区间）的子树
-.非叶子节点仅用来索引，数据都保存在叶子节点中

而数据库则常用叶子节点带指针的B+ -Tree 即所有的叶子节点均有一个链指针指向下一个叶子节点
结构大致如下：
在这里插入图片描述
相对于其他数据结构 B+ 树有着如下的优势

B+ 树的磁盘读写代价更低（单个存储块存储更多索引）
B+树的查询效率更加文档（每个查询都是从根节点到叶子节点）
B+树更有利于对数据库的扫描（每个叶子节点都保存有指向下一个节点的指针，有利于范围查找）

3.3其他索引

mysql 还支持其他数据结构形式的索引，这里只对为什么不用hash 结构做阐述。
hash 索引虽然看起来效率要高，但是有以下缺点

仅仅能满足 = IN 数据库操作，不能使用范围查询
无法被用来避免数据的排序操作
不能利用部分索引键查询
不能避免表扫描
遇到大量hash 值相等的情况后性能不一定会比B-Tree 索引高

4. 密集索引与稀疏索引

密集索引：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。;稀疏索引: 只为索引码的某些值建立索引项如索引信息只保存主键值。密集索引与稀疏索引的区别在于密集索引文件中的每个搜索码值都对应一个索引项，即行数据也保存在索引信息中。

MySIAM 存储引擎只有稀疏索引，InNODB 存储引擎有且只有一个密集索引。其筛选规则如下

主键 > 唯一非空键
如果表没有主键和唯一非空键，则InNODB 会建立一个隐藏主键作为密集索引。
非主键索引存储相关键位和其对应的主键值，使用非主键查询时，一般情况下先查找索引树拿到主键值，然后查找主键索引树，获取对应数据，这个过程也被称为回表。

5.最左匹配原则

mysql 会一直向右匹配直到遇到范围查询，如果 > < between like 就停止匹配。
如建立联合索引 (a,b,c),则：
where a=? and b=? and c=? 走索引
where a=? and c=? 不走索引
where a=? and b>? 走索引
where b>? and a=? 不走索引
where a>? and b>? 只有在查询a 值的时候走索引而查询b 的时候不走索引

6.其他易见问题

6.1 索引越多越好吗
不是，数据量小的表不需要建立索引建立索引以为着其他开销，数据库变更需要维护索引，更多索引意味着需要更多的空间。

6.2 应尽量在建立索引的列使用not null 修饰；
索引不会包含有NULL 值的列。只要列中包含有null 值都将不会被包含在索引中，复合索引只要有一列含有null 值，那么整个复合索引都是无效的。

6.3 使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

6.4 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6.5 like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6.6 不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
select * from users where adddate<‘2007-01-01’;

6.7 索引覆盖
我们假设存在一张user 表，其主键为id 列，并在name 列上创建了索引，对于查找语句 select id,name from user where name = a; 能够命中索引列name, 在索引树的叶子节点中存在主键值id,此时不需要再去主键索引树中查找，mysql 回直接返回，这种情况被称为索引覆盖。