MySQL索引与锁的机制三 MYSQL 索引机制

程序员文章站 2024-03-23 12:23:10

...

MYSQL 索引机制

数据库的检索过程：

Mysql 的基本存储结构是页，各个数据页组成一个双向的链表，单个数据页中的记录又组成一个单向的链表

每个数据页都会为存储的记录生成一个页目录，在通过 主键（索引） 查找某条记录时，可在页目录中使用二分法快速定位对应的槽， 让后再遍历该槽对应分组中的记录即可快速找到指定的记录数据。以 非主键作为搜索条件，只能从最小记录开始依次遍历单链表中的每条记录即：

1. 遍历双向链表，找到所在的页

2. 从所在的页中查找相应的记录，由于不根据主键查询，只能遍历所在页的单链表， 当数据量过大时（比如上亿条）结果会很慢

select * from users where id              = 10010      主键（索引）

select * from users where username = 'tom'        非主键（无索引）

MySQL索引与锁的机制三 MYSQL 索引机制

索引与创建索引：

什么是索引：

索引是对数据库 表中的一列或多列的值进行排序的一种存储结构（B+树），可以加快数据库的检索速度

索引的作用相当于数的目录，可以根据目录中的页码快速找到所需的内容

没有使用索引需要遍历双向链表来定位对应的页，现在通过索引就可以很快定位到对应的页上， 索引底层结构就是 B+ 树（也有 hash 表）， B+ 树作为树的一种实现，能够让我们很快底查找出对应的记录

索引的创建：

CREATE TABLE users(
   userid INT NOT NULL auto_increment PRIMARY KEY,
   username VARCHAR(20) NOT NULL,
   pwd VARCHAR(16),
   INDEX idx_username(username)                 #创建索引

    #INDEX idx_username(username(10)) #前 10个字符作为索引
)

ALTER TABLE users ADD INDEX idx_username(username)          #可重复的索引
ALTER TABLE users ADD UNIQUE idx_username(username)             #不可重复的索引
ALTER TABLE users ADD PRIMARY KEY idx_username(username)   #不可重复的索引，表中数据的唯一标识

DROP INDEX idx_username ON users                                           #删除索引

ALTER TABLE users DROP INDEX idx_username                          #删除索引
ALTER TABLE users DROP PRIMARY KEY                                    #删除索引

SHOW INDEX FROM users
SHOW KEY FROM users

联合索引：

CREATE * FROM users(
   userid INT NOT NULL,
   username VARCHAR(20) NOT NULL,
   pwd VARCHAR(20) NOT NULL,
   phone VARCHAR(20),
   INDEX idx_username_pwd(username, pwd, phone)                        #指定联合索引
)

SELECT * FROM users WHERE username = 'tom' AND pwd = 'malocfree'

相当于创建了 (username, pwd, phone)，(username, pwd)，(username) 三个索引

最左匹配：遇到范围查询（>, <, between, like，左匹配等）就不能进行一步匹配，后续退化为线性查找；如为一个表创建联合索引 (a,b,c,d)，查询 select * from mytable where a = 1 and b = 2 and c > 3 and d = 4, 则会一次命中   a, b,c 却无法命中 d

联合索引 (a,b,c,d)   查询条件 c > 7 and b = 5 and a = 1 and d < 3 与 a = 1 and c > 7 and b =5 and d <3 等顺序都是可以的。 Mysql 会自动优化为 a = 1 and b =5 and c > 7 and d < 3, 依次命中 a b c

索引注意事项：

适合建立索引的情况：

表的主关键字，自动建立唯一索引

表的字段唯一约束

直接条件查询的字段

查询中与其他表关联的字段

查询中排序的字段

查询中统计或者分组的字段（max 和 group by 的字段）

不适合建立索引的情况：

表记录太少（一般索引表与数据表不在同一个数据库，需要先访问索引表在通过索引表访问数据表）

经常插入, 删除，修改的表字段（B+树平衡调整）

数经常重复且分布均匀的表字段（b比如性别, 星期几等等）

索引并不总是最好用的工具，总的来说只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时。 索引才是有效的。对于非常小的表，大部分情况下全表扫描更加高效。对于中到大型的表，索引就非常有效了。但对于特大型的表，建立和使用索引的代价将随之增加。这种情况下则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配，例如：可以使用分区技术

根据每天把数据表分区
CREATE TABLE IF NOT EXISTS mytable(
	id INT NULL auto_increment PRIMARY KEY,
	mname VARCHAR(20),
  pwd VARCHAR(20)	
) PARTITIONED BY (ymd date)

聚簇索引与非聚簇索引：

● 聚簇索引

以主键创建的索引，在叶子结点处 储存的记录包含了表中所有的列（key- value）

● 二级索引（非聚簇索引）

非主键创建的索引，在叶子结点处储存的记录内容是 主键，使用非聚簇索引查询数据时，拿到叶子结点的主键，再去聚簇索引的 B+树上查询想要的数据（这个过程叫做回表）

● 每增加一种索引，就会新建一颗对应的 B+ 树，彼此独立的树

●MyISAM 单独为表的主键常见一个 B+ 树的索引，在 B+ 树的叶子结点中存储的不是完整的用户记录，而是主键值 + 行号的 组合。 也就是先通过索引找到对应的行号，在通过行号去找对应的记录。两次

● InnoDB 存储引擎 只需要根据主键值对应聚簇索引进行一次查找即可找到对应的记录数据， MyISAM 中需要进行一次回表操作，意味着MyISQL 中建立的索引全部是二级的

Hash 索引：

● 哈希索引就是采用哈希算法，把键值换算成行的哈希值，检索时不需要类似 B+ 那样从根节点到叶子结点逐级查询，只需要一次哈希算法即可立刻定位到相应的位置，速度快

● 局限性

无法利用索引完成排序

不支持最左匹配原则

在有大量重复键值的情况下，存在 hash 冲突效率低

不支持匹配范围

● 主流数据库采用 B+ 树索引 InnoDB 使用的是自适应哈希索引，即会根据表的使用情况自动为表生成哈希索引，不能人为干预

相关标签： MySQL索引与锁的机制三 MYSQL 索引机制

上一篇： JAVA 攻城狮第三十二天

下一篇： Python学习（四）——函数

MySQL索引与锁的机制三 MYSQL 索引机制

MYSQL 索引机制