Mysql-索引

一、索引的作用

• 如果没有索引 => 全表扫描 => 从第一条记录开始找，直到找到为止或表结束

• 索引是一种特殊数据结构，像字典的目录，帮助MySQL高效地获取数据

优点

• 显示提升查询速度
• 降低磁盘i/o次数
• 优化排序与分组操作（索引有排序）

缺点

• 增加存储空间（索引数据占存储空间）
• 降低写入性能（写入数据之后，索引数据要更新）

什么情况下适合索引

• 表主要用来写入数据，几乎很少查询 => 不建议索引
• 经常要查询 =>建议加索引（索引要加到查询的条件字段上,否则索引失效）

二、MySQL索引底层结构

常用数据存储结构：哈希结构，B+树结构

二叉树查找
二叉树

• 每个节点都（最多）有两个子节点
• 任何节点的左子节点值都小于当前节点
• 任何节点的右子节点值都大于当前节点

平衡二叉树（AVL树）

• 每个节点都（最多）有两个子节点
• 任何节点的左子节点值都小于当前节点
• 任何节点的右子节点值都大于当前节点
• 每个节点的左右子树的高度不能超过1

每次insert数据时，再建立索引数据

B树和B+树
B树和B+树在AVL树的基础上，数据量大时，更进一步提升效率

• 每个节点都（最多）有N个子节点
• 任何节点的左子节点值都小于当前节点
• 任何节点的右子节点值都大于当前节点
• 每个节点的左右子树的高度不能超过1

B+树的数据存储在叶子节点上，数据形成链
B+一个节点存储1000个数
1000*1000*1000 = 10亿 => 三层

子节点存储内容：

B树：内部节点和叶子节点都存储数据
B+树：只有叶子节点存储数据，内部节点只存储键值

叶子节点结构：

B树：叶子节点没有直接链接
B+树：通过链表连接，支持快速遍历

插入/删除操作：

B树：影响多个层级节点
B+树：主要集中在叶子节点上（稳定性更好）

适用场景：

B树：适合精确查询，范围查询性能差
B+树：适合精确查询和范围查询，性能都很好

MySQL使用B+树

二叉树： 某些情况下，树不平衡(高)
|
平衡二叉树(Val)：确保树的平衡，只有两个子节点
|
B树：N个子节点
|
B+树：N个子节点

三、索引的分类

字段数量：单列索引和多列索引(复合索引/联合索引/组合索引)

单列索引分类：

• 普通索引：key, index
• 唯一索引：节点值不允许重复
• 主键索引：不允许重复、不允许有空值

多列索引注意：
查询条件中包含了多列索引的第一个字段时才能使用多列索引

user:id, name, age => 多列索引
select * from user where name=“cali” and age=18; => 不会使用索引
select * from user where name=“cali” and id=6; => 会使用索引

1. 普通索引

• 在建表时创建

create table test_index01(
name char(10),
age int,
index idx_name(name)
);

root@test 16: 02>show create table test_index01\G
*************************** 1. row ***************************
       Table: test_index01
Create Table: CREATE TABLE `test_index01` (
  `name` char(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

root@test 16: 03>desc test_index01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | MUL | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• 单独创建

create table test_index02(
	name char(10),
	age int
);

root@test 16: 04>desc test_index02;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

root@test 16: 05>create index idx_name on test_index02(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

root@test 16: 05>desc test_index02;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | MUL | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

注意：创建索引越早越好（数量越少时创建）
数据量大时，创建索引会非常耗时

2. 唯一索引

create table test_index03(
name char(10),
age int
);

alter table test_index03 add unique index idx_name(name);

create unique index idx_age on test_index03(age);

root@test 16: 11>desc test_index03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | UNI | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除索引
drop index idx_age on test_index03;

注意：如果表中已经有数据了，字段值有相同的，创建唯一索引会失败

三种添加索引的方式：创建表时，alter, create

3. 主键索引

• 创建表时

create table test_index04(
name char(10),
age int,
primary key idx_name(name)
);

• alter table

create table test_index05(
name char(10),
age int
);

alter table test_index05 add primary key idx_name(name);

查看表索引信息

root@test 16: 28>show index from test_index05\G
*************************** 1. row ***************************
        Table: test_index05               
   Non_unique: 0             # 0是，1不是
     Key_name: PRIMARY                
 Seq_in_index: 1             # 索引中顺序
  Column_name: name                   
    Collation: A             # A有顺序存储，NULL无序
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE                  
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

4. 联合索引

注意：最常查询的字段放在第一位

create index idx_name on table_name (column1, column2,....)

alter table table_name add index idx_name(column1, column2,....)

5. 全文索引

只能添加在文本类型的字段上
适合字段：text
存储引擎: MyISAM

create table test_index06(
name char(10),
age int,
content text
);

alter table test_index06 add fulltext index idx_content(content);

create fulltext index idx_name on test_index06(name);

添加存储引擎

create table test_index07(
name char(10),
age int,
content text
) engine=“MyISAM”;

root@test 16: 45>alter table test_index07 add fulltext index idx_content(content);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

总结

MySQL 索引是一种数据结构，用于快速定位和访问表中的特定数据，显著提高查询效率

通过索引，数据库无需全表扫描，直接定位到符合条件的数据