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

最新推荐文章于 2024-07-25 13:34:36 发布

nicepainkiller

最新推荐文章于 2024-07-25 13:34:36 发布

阅读量525

点赞数

分类专栏： Mysql MySQL索引与锁的机制数据库文章标签： MySQL索引与锁的机制三 MYSQL 索引机制

本文链接：https://blog.csdn.net/nicepainkiller/article/details/102915627

版权

数据库同时被 3 个专栏收录

33 篇文章 1 订阅

订阅专栏

Mysql

28 篇文章 0 订阅

订阅专栏

MySQL索引与锁的机制

7 篇文章 0 订阅

订阅专栏

MYSQL 索引机制

数据库的检索过程：

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

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

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

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

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

索引与创建索引：

什么是索引：

索引是对数据库 表中的一列或多列的值进行排序的一种存储结构（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)