MySql之索引的创建、删除以及Hash树和B树（全家桶）!!

2022-09-18 20:03:12

一、索引介绍：（索引可以大大提高查询效率）

　　索引是一种数据结构，例如B-Tree，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。（无索引会全部扫描）

其中。当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引。PRIMARY索引是比较特殊的，这个索引本身与数据一起存储在同一个表中。另外除PRIMARY索引之外的其他索引称为二级索引或非聚簇索引。

索引创建的几种方式：

（1）建表的时候创建：

　CREATE TABLE t(
  　　 t1 INT PRIMARY KEY,
   　　t2 INT NOT NULL,
   　　t3 INT NOT NULL,
   　　t4 VARCHAR(10),
   　　INDEX (c2,c3) 
　　);

（2）为列或一组列添加索引：

　CREATE INDEX index_name ON table_name (column_list)

（3）为为列创建索引，要指定索引名称，索引所属的表以及列。

 CREATE INDEX idx_c4 ON t(c4);

想必很多小伙伴都好奇有无索引查询时会有多大区别？

比如查找职称是boss的人，没有索引的话，需要扫描23行（rows=23）
SELECT 
    empNum, 
    lastName, 
    firstName
FROM
    employee
WHERE
    jobName = ‘boss‘;

但是如果加入索引呢？ 这样的话，同样是查询Boss，但是索引下的扫描行数只有17行，咋一看效率提高不多，但是如果放在数以万计的情况下，就很明显了!

CREATE INDEX jobTitle ON employees(jobTitle);

 EXPLAIN SELECT 
    empNum, 
    lastName, 
    firstName
FROM
    employee
WHERE
    jobName = ‘Boss‘;

二、Mysql索引删除

（1）要从表中删除现有索引，可使用DROP INDEX*语句

DROP INDEX index_name ON table_name

（2）MySQL删除主键索引

DROP INDEX `PRIMARY` ON table_name;

三、查看索引（show）

（1）获取索引信息

SHOW INDEXES FROM table_name;#指定数据库的话，在后面加in database_name

例子：建一个名为contacts的表

CREATE TABLE contacts(
    contact_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone) INVISIBLE,
    INDEX name(first_name, last_name) comment ‘By first name and/or last name‘
);

SHOW INDEXES FROM contacts;　　#查看contacts表中所有索引信息

结果：（Non_unique唯一索引；Key_name 索引名称；Seq_in_index 索引中的列序列号；Column_name 是索引的列名；Cardinality表示索引中唯一值的数目的估计值）

四、索引之唯一索引

如果要强烈使一列或多列具有唯一性，通常使用PRIMARY KEY约束。但是，每个表只能有一个主键。因此，如果使多个列或多个组合列具有唯一性，则不能使用主键约束。

幸运的是，MySQL提供了另一种索引，叫做唯一索引，允许我们可以使一个或者多个列的值具有唯一性。另外，不会像主键索引一样，我们的每张表中可以有很多个唯一索引

为了创建一个唯一索引，我们可以来使用CREATE UNIQUE INDEX语法

如果想要在一个已存在表中添加一个唯一索引，我们可以使用下列的ALTER TABLE语句

ALTER  TABLE  table_name  ADD  INDEX  index_name( column_1,column_2 ) ;

例子：

假设我们要管理应用程序中的联系人，并且还希望联系人表的每个联系人的电子邮件必须是唯一的。那我们就可以使用CREATE TABLE语句创建唯一约束来满足我们的需求，如下:

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE Index unique_email (email)
);

MySql之索引的创建、删除以及Hash树和B树（全家桶）!!

首先向contacts表中插入一行数据来

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES(‘John‘,‘Doe‘,‘(408)-999-9765‘,‘john.doe@mysqltutorial.org‘);

下面我们尝试插入一行email列中有john.doe@mysqltutorial.org, 这时我们就会得到一个报错的信息。

INSERT INTO contacts(first_name,last_name,phone,email)
VALUES(‘Johny‘,‘Doe‘,‘(408)-999-4321‘,‘john.doe@mysqltutorial.org‘);

此时你会得到如下报错

Error Code: 1062. Duplicate entry ‘john.doe@mysqltutorial.org‘ for key ‘unique_email‘

五、索引数据结构

数据库索引，是数据库管理系统中一个排序的数据结构，主要有
B树索引、Hash索引两种！！！

注意：可能很多小伙伴把B - 树读成 B减树，然而实际上那个并不是减号，而是横线，而且B -实际上就是B树

https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw（B-树）

https://mp.weixin.qq.com/s/jRZMMONW3QP43dsDKIV9VQ（B+树）

（1）B树索引结构实例：

左边表示的是某个数据库的数据表，一共有两列七条记录，最左边的是数据记录的物理地址（就

是在硬盘的存储位置）。为了加快对Col2这一列的查找，可以创建一个如右边所示的二叉查找树，

每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找

在O(log2n)的复杂度内获取到相应数据。

（2）理解Hash树索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节

点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，这时疑问就来了，既然

Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？

任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于

其特殊性也带来了很多限制和弊端，主要有以下这些。

1）Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询
2）Hash 索引无法被用来避免数据的排序操作

3）Hash 索引不支持多列联合索引的最左匹配规则

4）Hash索引在任何时候都不能避免表扫描

六、索引使用场景

什么情况下会使用索引呢？

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中于其他表关联的字段，外键关系建立索引
频繁更新的字段不适合建立索引，因为每次更新不单单时更新了记录还会更新索引
where 条件里用不到的字段不创建索引
查询中排序的字段，排序的字段若通过索引去访问将会大大提高排序速度
查询中统计或者分组的字段

哪些情况不需要创建索引

表记录太少
经常增删改的表
如果某个数据列包含许多重复的内容，为它建立索引
就没有太多太大实际效果

MySql之索引的创建、删除以及Hash树和B树（全家桶）!!

码农公寓

一、索引介绍：（索引可以大大提高查询效率）

相关文章