MySQL索引

索引概述

MySQL官方对索引的定义为：

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

如下面的示意图所示 :

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。 所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

BTREE 索引：最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引：只有Memory引擎支持，使用场景简单。
R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	`支持`	`支持`	`支持`
HASH 索引	不支持	不支持	`支持`
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

常见索引

1）主键索引 :是数据库中一种特殊的索引，用于唯一标识表中的每一条记录。具有唯一性、非空性、自动索引（定义主键自动添加索引）、优化查询、数据完整性等特性

2）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

3）唯一索引：索引列的值必须唯一，但允许有空值

4）复合索引：即一个索引包含多个列，与单值索引（只在一个列上创建）不同，复合索引能够提高基于多个列的查询效率

索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

准备环境:

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

1.创建索引

语法：

CREATE  [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

CREATE INDEX index_name ON table_name (column_name);

示例：为city表中的city_name字段创建单值索引;

create index idx_city_name on city(city_name);

下面是 MySQL 中常见的索引类型及其创建方法：

1.普通索引（INDEX）普通索引是最基本的索引类型，用于加速数据的检索。

创建语法：

CREATE INDEX index_name ON table_name (column_name);
示例：

CREATE INDEX idx_name ON employees (last_name);

2.唯一索引（UNIQUE INDEX）唯一索引确保索引列中的所有值都唯一。它不仅加速查询，还可以保证数据的唯一性。

创建语法：

CREATE UNIQUE INDEX index_name ON table_name (column_name);
示例：

CREATE UNIQUE INDEX idx_email ON users (email);

3.主键索引（PRIMARY KEY）主键索引是唯一索引的一种，它不仅确保唯一性，还作为表的主键标识每一行数据。每个表只能有一个主键索引。

创建语法：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);
示例：

ALTER TABLE employees ADD PRIMARY KEY (employee_id);

4.全局唯一索引（UNIQUE KEY）类似于唯一索引，但在某些情况下可以在不同的表或分区中进行分布式唯一性检查。

创建语法：

CREATE UNIQUE INDEX index_name ON table_name (column_name);
示例：

CREATE UNIQUE INDEX idx_username ON users (username);

5.复合索引（COMPOSITE INDEX）复合索引是在多个列上创建的索引，用于加速基于多个列的查询。

创建语法：

CREATE INDEX index_name ON table_name (column1, column2, ...);
示例：

CREATE INDEX idx_name_age ON employees (last_name, age);

6.全文索引（FULLTEXT INDEX）全文索引用于加速对文本数据的全文搜索（如在 TEXT 或 VARCHAR 列上进行搜索）。仅支持 MyISAM 和 InnoDB(5.7之后版本) 存储引擎。

创建语法：

CREATE FULLTEXT INDEX index_name ON table_name (column_name);
示例：

CREATE FULLTEXT INDEX idx_description ON articles (description);

7.空间索引（SPATIAL INDEX）空间索引用于加速对空间数据的查询，如地理位置数据。仅支持 MyISAM 存储引擎。

创建语法：

CREATE SPATIAL INDEX index_name ON table_name (geometry_column);
示例：

CREATE SPATIAL INDEX idx_location ON places (location);

8.哈希索引（HASH INDEX）哈希索引是 MEMORY 存储引擎专用的索引类型，使用哈希算法进行索引。适用于等值查询，但不支持范围查询。

创建语法：

CREATE INDEX index_name USING HASH ON table_name (column_name);
示例：

CREATE INDEX idx_name_hash USING HASH ON employees (last_name);

9.位图索引（BITMAP INDEX）位图索引不是 MySQL 的内置索引类型，但在某些数据库系统中（如 Oracle）有类似的实现，主要用于处理低基数（少量唯一值）的列。

创建索引的其他注意事项：索引选择：根据查询的特点选择合适的索引类型。例如，范围查询使用 B 树索引效果更好，而全文搜索则使用全文索引。索引维护：创建索引会增加数据插入、更新和删除的开销。应根据实际需求平衡查询性能和写入性能。覆盖索引：创建覆盖索引（即索引中包含所有查询列）可以显著提高查询性能。索引管理查看索引：可以使用 SHOW INDEX 或 SHOW KEYS 语句查看表中的索引。

SHOW INDEX FROM table_name;

删除索引：可以使用 DROP INDEX 语句删除不再需要的索引。

DROP INDEX index_name ON table_name;

优化索引：定期使用 ANALYZE TABLE 和 OPTIMIZE TABLE 命令优化表的索引。

2.查看索引

语法：

show index  from  table_name;

示例：查看city表中的索引信息；

show index from city\G;

3.删除索引

语法：

DROP INDEX index_name ON tbl_name;

示例：想要删除city表上的索引idx_city_name，可以操作如下：

drop index idx_city_name on city;

4.修改索引使用ALTER命令

# 添加一个主键，索引值必须是唯一的，且不能为NULL
alter  table  tb_name  add  primary  key(column_list);

# 创建唯一索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter  table  tb_name  add  unique index_name(column_list);

# 添加普通索引， 索引值可以重复。
alter  table  tb_name  add  index index_name(column_list);

# 该语句指定了索引为FULLTEXT， 用于全文索引
alter  table  tb_name  add  fulltext  index_name(column_list);

索引设计原则

1.什么时候建立

1.针对于数据量较大且查询比较频繁的表，建立索引。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

2.建立什么索引

1.尽量选择区分度高的列作为索引，尽量建立唯一索引（区分度越高，使用索引的效率越高）

2.字符串类型的字段的长度较长，建立前缀索引。

3.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

3.注意

1.如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。（当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询，比如受数据分布影响的索引失效）

2.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

Z笔记

左老师的课堂笔记

MySQL索引

索引概述

索引结构

常见索引

索引语法

1.创建索引

2.查看索引

3.删除索引

4.修改索引使用ALTER命令

索引设计原则

1.什么时候建立

2.建立什么索引

3.注意