【MySQL】MySQL的索引（上）

2023-07-25 58

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群系列 2核4GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： 【MySQL】MySQL的索引

介绍

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索

引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的

时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而

不必查看所有数据，那么将会节省很大一部分时间。索引类似一本书的目录，比如要查

找’student’这个单词，可以先找到s开头的页然后向后查找，这个就类似索引。

索引的分类

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有Hash索引和

B+Tree索引

按照功能划分，索引划为以下分类:

索引的操作-创建索引-单列索引-普通索引

介绍

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，

纯粹为了查询数据更快一点。

格式

1. create database mydb5;
2. use mydb5;
3. 
4. -- 方式1-创建表的时候直接指定
5. create  table student(
6.     sid int primary key,
7.     card_id varchar(20),
8.     name varchar(20),
9.     gender varchar(20),
10.     age int,
11.     birth date, 
12.     phone_num varchar(20),
13.     score double,
14.     index index_name(name) -- 给name列创建索引
15. );

1. -- 方式2-直接创建
2. -- create index indexname on tablename(columnname); 
3. create index index_gender on student(gender); 
4. 
5. -- 方式3-修改表结构(添加索引)
6. -- alter table tablename add index indexname(columnname)
7. alter table student add index index_age(age);

操作

1. -- 1、查看数据库所有索引 
2. -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’; 
3. select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
4.

1. -- 2、查看表中所有索引 
2. -- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’; 
3. select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
4.

1. -- 3、查看表中所有索引 
2. -- show index from table_name; 
3. show index from student;

索引的操作-删除索引

格式

1. drop index 索引名 on 表名 
2. -- 或 
3. alter table 表名 drop index 索引名 
4.

操作

1. drop index index_gender on student 
2. -- 或 
3. alter table student drop index index_name

索引的操作-创建索引-单列索引-唯一索引

介绍

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合

索引，则列值的组合必须唯一。它有以下几种创建方式：

操作-创建索引

1. -- 方式1-创建表的时候直接指定
2. create  table student2(
3.     sid int primary key,
4.     card_id varchar(20),
5.     name varchar(20),
6.     gender varchar(20),
7.     age int,
8.     birth date, 
9.     phone_num varchar(20),
10.     score double,
11. unique index_card_id(card_id) -- 给card_id列创建索引
12. );
13. -- 方式2-直接创建
14. -- create unique index 索引名 on 表名(列名) 
15. create unique index index_card_id on student2(card_id);
16. 
17. -- 方式3-修改表结构(添加索引)
18. -- alter table 表名 add unique [索引名] (列名)
19. alter table student2 add unique index_phone_num(phone_num)

操作-删除索引

1. drop index index_card_id on student2 
2. -- 或 
3. alter table student2 drop index index_phone_num

索引的操作-创建索引-单列索引-主键索引

介绍

每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这

就是主键索引。主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引.

索引的操作-创建索引-组合索引

介绍

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机

号建立索引，同样的可以建立为普通索引或者是唯一索引。复合索引的使用复合最左原则。

格式

1. -- 创建索引的基本语法 
2. create index indexname on table_name(column1(length),column2(length));

操作

1. -- 组合索引
2. use mydb5;
3. -- 创建索引的基本语法-- 普通索引
4. -- create index indexname on table_name(column1(length),column2(length)); 
5. create index index_phone_name on student(phone_num,name);
6. -- 操作-删除索引
7. drop index index_phone_name on student; 
8. -- 创建索引的基本语法-- 唯一索引
9. create  unique index index_phone_name on student(phone_num,name);

1. select * from student where name = '张三'; 
2. select * from student where phone_num = '15100046637'; 
3. select * from student where phone_num = '15100046637' and name = '张三'; 
4. select * from student where name = '张三' and phone_num = '15100046637'; 
5. /* 
6.   三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段  才能够进行匹配。
7.   而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。 
8. 
9. */
10.