MySQL索引操作实战：查询、创建与删除索引

04913
来自AI助手的总结
介绍MySQL索引查询、参数解读及创建方法
MySQL索引操作实战：查询、创建与删除索引

一、索引基本操作说明

环境准备：


-- 将world.sql数据库文件上传到数据库服务器中，根据存储路径进行加载恢复数据库数据

[root@master ~]# unzip world.zip

-- 导入sql语句方式一：进入数据库中执行source语句

mysql> source /root/world.sql

-- 导入sql语句方式二：数据库外使用<符号进行导入

[root@master ~]# mysql < /root/world.sql

-- 查看表

mysql> use world;

mysql> show tables;

+-----------------+

| Tables_in_world |

+-----------------+

| city            |
| country         |
| countrylanguage |

+-----------------+

3 rows in set (0.00 sec)

二、查询索引信息

-- 查询表结构信息，获取索引配置
mysql> use world;
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Day006-数据库服务索引执行计划-图6

索引信息的展示形式：

序号 索引标识 解释说明
01 PK（PRI） 表示为聚簇索引，也可以理解为主键索引
02 K（MUL） 表示为辅助索引，也可以理解为一般索引
03 UK 表示唯一键索引 
-- 查询表索引信息，获取索引详细信息
mysql> use world;
mysql> show index from city\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4188
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)
-- 算下city表行数
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

Day006-数据库服务索引执行计划-图77

回显重要参数解读：

  • Non_unique：索引信息是否是唯一键值索引，其中0表示唯一，1 表示可以重复

  • Collation：显示创建索引排序方式，其中A代表升序，D代表降序

  • Cardinality：显示索引列的索引选择度，数据库会自动计算估值，索引列数据重复情况。先通过select count(*) from city;计算city表总行数4079，这里4188/4079约等于1，表示重复值越少；这里232/4079约等于0，表示重复值越多。

  • Sub_part：当设置前缀索引时，会显示截取的前缀字符数量

  • Visible：是否临时禁用索引功能，可以影响日常批量操作效率

三、创建索引信息

环境准备


-- 创建数据库db18

mysql> create database db18;

3.1 创建单列索引

创建单列索引语法格式


-- 创建主键索引，主键索引是一种特殊的唯一索引，一个表中只能有一个主键索引，不允许有空值

mysql> CREATE TABLE `table_name` ( ...,PRIMARY KEY ( `column` ))

mysql> ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

-- 创建辅助索引，辅助索引又称为普通索引,是MySOL中的基本索引类型,允许在定义索引的列中插入重复值和空值

mysql> CREATE TABLE `table_name` ( ...,index index_name(column(length))

mysql> CREATE index `index_name` on table(column(length))

mysql> alter table `table_name` add index `index_name` (column(length));

mysql> alter table `table_name` add index `index_name` on (column(length));

-- 创建唯一键索引，唯一索引指索引列的值必须唯一,但允许有空值;如果是组合素引,则列值的组合必须唯一;

mysql> CREATE TABLE `table_name` ( ...,unique index index_name(column(length))

mysql> CREATE unique index `index_name` on table(column(length))

mysql> ALTER TABLE `table_name` ADD UNIQUE `index_name` on (column(length));

Day006-数据库服务索引执行计划-图8

3.1 创建主键索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建主键索引
mysql> create table db18.test01(id int,name char(10),primary key(id));
-- 结果验证
mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引


-- 先创建表test02

mysql> create table db18.test02(id int,name char(10));

-- 在已有表的基础创建主键索引

mysql> alter table db18.test02 ADD primary key(id);

-- 结果验证，查看索引

mysql> desc db18.test02;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show index from db18.test02\G;

*************************** 1. row ***************************

        Table: test02

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  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)

ERROR:

No query specified

3.1 创建辅助索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建辅助索引,不带前缀
mysql> create table db18.test03(id int,name char(10),index index_name(name));
-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 创建表的时候顺带创建辅助索引,设置排序方式为降序
mysql> create table db18.test05(id int,name char(10),age char(5),index zq(name desc));
-- 结果验证，观察Collation的值为D，代表为降序
mysql> show index from db18.test05\G;
*************************** 1. row ***************************
        Table: test05
   Non_unique: 1
     Key_name: zq
 Seq_in_index: 1
  Column_name: name
    Collation: D
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

情况二：创建表之后再创建索引


-- 先创建表test08

mysql> create table db18.test08(id int,name char(10));

-- 在已有表的基础添加辅助索引

mysql> alter table db18.test08 ADD index index_name(name);

-- 结果验证，查看索引

mysql> desc db18.test08;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

3.1 创建唯一键索引

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建唯一索引
mysql> create table db18.test06(id int,name char(10),unique index index_name(name));
-- 结果验证，观察到Key字段为UNI
mysql> desc db18.test06;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引


-- 先创建表test09

mysql> create table db18.test09(id int,name char(10));

-- 在已有表的基础添加唯一索引

mysql> alter table db18.test09 ADD unique index index_name(name);

-- 结果验证，查看索引

mysql> desc db18.test09;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | UNI | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

3.2 创建联合索引(多列索引)

作用：减少查询数据信息时回表次数

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建联合索引
mysql> create table db18.test07(id int,name char(10),age char(5),index index_name(name,age));
-- 结果验证，观察到Seq_in_index字段为1和2
mysql> SHOW INDEX FROM db18.test07\G;
*************************** 1. row ***************************
        Table: test07
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test07
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test10
mysql> create table db18.test10(id int,name char(10),age char(5));
-- 在已有表的基础添加唯一索引
mysql> alter table db18.test10 ADD index index_name(name,age);
-- 结果验证，查看索引
-- 结果验证，观察到Seq_in_index字段为1和2
mysql> SHOW INDEX FROM db18.test10\G;
*************************** 1. row ***************************
        Table: test10
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: test10
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

3.3 创建前缀索引


mysql> alter table city add index ix_n(name(10));

Day006-数据库服务索引执行计划-图9

情况一：创建表的时候将索引创建

-- 创建表的时候顺带创建辅助索引,带前缀
mysql> create table db18.test04(id int,name char(10),index index_name(name(5)));
-- 结果验证,观察到Sub_part这个字段显示值为5
mysql> show index from db18.test04\G;
*************************** 1. row ***************************
        Table: test04
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 5
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

情况二：创建表之后再创建索引

-- 先创建表test11
mysql> create table db18.test11(id int,name char(10));
-- 在已有表的基础添加辅助索引
mysql> alter table db18.test11 ADD index index_name(name(5));
-- 结果验证,观察到Sub_part这个字段显示值为5
mysql> show index from db18.test11\G;
*************************** 1. row ***************************
        Table: test11
   Non_unique: 1
     Key_name: index_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 5
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

四、删除索引信息

# 删除索引信息（一般索引）
alter table 表名 drop index 索引名;
mysql> alter table t100w drop index idx_name;
-- 删除辅助索引
mysql> alter table t100w drop index oldboy;
-- 删除联合索引
mysql> alter table t100w drop index id;
-- 删除唯一索引
# 删除索引信息（聚簇索引）
alter table 表名 drop primary key;
mysql> alter table t100w drop primary key;
-- 删除聚簇索引

Day006-数据库服务索引执行计划-图10

4.1 删除聚簇索引

环境准备

-- 创建表的时候顺带创建主键索引
mysql> create table db18.test01(id int,name char(10),primary key(id));
-- 结果验证
mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除聚簇索引


mysql> alter table db18.test01 drop primary key;

结果验证

mysql> desc db18.test01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4.2 删除辅助索引

环境准备

-- 创建表的时候顺带创建辅助索引,不带前缀
mysql> create table db18.test03(id int,name char(10),index index_name(name));
-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 创建表的时候顺带创建辅助索引,设置排序方式为降序
mysql> create table db18.test05(id int,name char(10),age char(5),index zq(name desc));
-- 结果验证，观察Collation的值为D，代表为降序
mysql> show index from db18.test05\G;
*************************** 1. row ***************************
        Table: test05
   Non_unique: 1
     Key_name: zq
 Seq_in_index: 1
  Column_name: name
    Collation: D
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

删除辅助索引


mysql> alter table db18.test03 drop index index_name;

结果验证

-- 结果验证
mysql> desc db18.test03;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
MySQL
# 数据库运维# 关系型数据库# MySQL# 索引优化# 查询性能
喜欢就支持一下吧
点赞13 分享
相关推荐
MongoDB 副本集成员-AI运维探索者
MongoDB 副本集成员
MongoDB 副本集成员
2年前 53
MongoDB 的生产上线及版本升级-AI运维探索者
MongoDB 的生产上线及版本升级
MongoDB 的生产上线及版本升级
2年前 51
PostgreSQL 流复制原理详解：WAL 传输、同步级别与配置要点-AI运维探索者
PostgreSQL 流复制原理详解：WAL 传输、同步级别与配置要点
PostgreSQL 流复制原理详解：WAL 传输、同步级别与配置要点
2年前 51
Postgres-XC 分布式数据库入门：架构、安装与使用限制-AI运维探索者
Postgres-XC 分布式数据库入门：架构、安装与使用限制
Postgres-XC 分布式数据库入门：架构、安装与使用限制
2年前 49
数据库基础入门：概念、分类、版本与MySQL优势-AI运维探索者
数据库基础入门：概念、分类、版本与MySQL优势
数据库基础入门：概念、分类、版本与MySQL优势
2年前 49
MySQL索引操作实战：查询、创建与删除索引-AI运维探索者
MySQL索引操作实战：查询、创建与删除索引
MySQL索引操作实战：查询、创建与删除索引
2年前 49
评论 抢沙发

请登录后发表评论

    暂无评论内容

Jenkins 自动化接口测试：JMeter、Ant 与测试报告展示

污点Taint是什么、种类与使用场景

Prometheus 集群监控：接入 scheduler、kube-state-metrics 与 CoreDNS

Kubernetes ResourceQuota实战：按环境和项目限制资源

RAG 入门：它是什么，解决了什么问题，核心原理是什么

MongoDB 副本集成员