mysql复习笔记四：索引-操作

1、查看数据库所有表的索引

        1.1示例sql:去掉where子句可看见该链接下所有数据库所有表索引，通常不用

SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    NON_UNIQUE,
    INDEX_SCHEMA,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY,
    INDEX_TYPE,
    COMMENT
FROM 
    INFORMATION_SCHEMA.STATISTICS
WHERE 
    TABLE_SCHEMA = 'your_database';

        1.1.1字段介绍：

    TABLE_SCHEMA：数据库名称
    TABLE_NAME, --表名
    NON_UNIQUE,--是否唯一
    INDEX_SCHEMA,--索引所属数据库（即数据库名称）
    INDEX_NAME,--索引名称
    SEQ_IN_INDEX,--索引顺序（联合/复合索引中）
    COLUMN_NAME,--字段列名
    CARDINALITY,--索引中不同值的数量
    INDEX_TYPE,--索引的类型（BTREE、HASH）
    COMMENT --默认是空的或包含一些系统默认信息，而不是由数据库管理员或开发者手动输入的注释

对于某些类型的索引，COMMENT字段可能会包含有用的信息。例如：

• 当你创建一个全文索引（FULLTEXT index）时，COMMENT字段可能包含有关该索引使用的分析器的信息。
• 对于空间索引（SPATIAL index），COMMENT字段可能包含有关该索引的空间参考系统（SRS）的信息。

但在大多数情况下，COMMENT字段并不经常被使用，特别是在创建和管理常规的B-tree索引时。如果你想为某个索引添加自定义注释，你需要在创建索引时或之后通过修改元数据的方式手动设置COMMENT字段的值，但这通常不是标准的数据库操作流程的一部分。

        1.2操作：

        1.2.1执行sql:

SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    NON_UNIQUE,
    INDEX_SCHEMA,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY,
    INDEX_TYPE,
    COMMENT
FROM 
    INFORMATION_SCHEMA.STATISTICS
WHERE 
    TABLE_SCHEMA = 'wjs_test_db';

        1.2.2执行结果：

2、查询表索引信息

        2.1示例sql

show index from test_index;        

        2.2执行sql 

show index from test_index;		

        2.2.1执行结果：

        2.2.2执行结果字段简介：

1. Table：显示索引所属的表名。
2. Non_unique：表示索引是否唯一。如果值为0，则索引是唯一的；如果值为1，则索引可以包含重复的值。
3. Key_name：索引的名字。
4. Seq_in_index：索引列的顺序位置，从1开始计数。
5. Column_name：构成该索引的列名。
6. Collation：列在索引中的排序方式。可能的值有A（升序）和D（降序），也可能为空，表示默认排序。
7. Cardinality：索引中不同值的数量估计，用于优化器决定是否使用此索引来优化查询。
8. Sub_part：如果索引列被部分索引，则显示被索引的部分长度，否则为NULL。
9. Packed：对于某些类型的索引，可能有压缩信息。
10. Null：列是否允许NULL值。
11. Index_type：索引类型，例如BTREE、HASH等。
12. Comment：额外的注释或信息。
13. Index_comment：索引的注释。

3、添加索引

        3.1创建表时添加索引：

CREATE TABLE test_index(
	id BIGINT auto_increment  COMMENT '主键',
	PRIMARY key(id) COMMENT '主键',
	user_name VARCHAR(64) not null COMMENT '用户名',
	nick_name VARCHAR(64) not null COMMENT '姓名',
	INDEX name(user_name,nick_name) COMMENT '用户名称',
	age INT(3) not null COMMENT '年龄',
	`code` VARCHAR(21) not null COMMENT '唯一代码',
	UNIQUE(`code`)  COMMENT '唯一代码',
	phone VARCHAR(11) COMMENT '联系电话',
	create_by BIGINT(64) not null COMMENT '创建者',
	create_time datetime not null COMMENT '创建时间'
)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,COMMENT = '测试索引表',ENGINE = InnoDB;

 示例中总共有一个一级索引（主键），一个二级索引（code），一个联合索引（name->{user_name,nick_name}）

3.2建表后添加索引：

在MySQL中，如果你想要在已经创建的表上添加索引，你可以使用两种方法：`ALTER TABLE` 和 `CREATE INDEX` 语句。

使用 ALTER TABLE 命令来向现有表中添加各种类型的索引，如普通索引、唯一索引或全文索引。语法如下：

ALTER TABLE table_name ADD [INDEX | FULLTEXT | SPATIAL] [index_name] (column_name[, ...])
[ASC | DESC]

或者，对于唯一索引：

ALTER TABLE table_name ADD UNIQUE [index_name] (column_name[, ...])
[ASC | DESC]

其中：

• `table_name` 是你想要添加索引的表的名称。
• `index_name` 是你为索引选择的名称（可选，但建议提供）。
• `column_name` 是你想要索引的列名。
• `ASC` 或 `DESC` 指定索引的排序方式，如果不指定，默认为 `ASC`。

使用 CREATE INDEX 添加索引

使用 `CREATE INDEX` 语句来创建索引，这种方法更直观且直接。语法如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (column_name[, ...])
[ASC | DESC]

同样地：

• - `UNIQUE`, `FULLTEXT`, `SPATIAL` 分别用于创建唯一索引、全文索引和空间索引，若不需要这些特性则省略。
• `index_name` 是索引的名称。
• `table_name` 是表的名称。
• `column_name` 是你希望索引的列。
• `ASC` 或 `DESC` 是排序方式。

    3.2.1示例

假设你有一个名为 `users` 的表，并且你想为 `email` 列创建一个唯一索引，你可以使用以下命令之一：

使用 ALTER TABLE

ALTER TABLE users ADD UNIQUE email_idx (email);

使用 CREATE INDEX

CREATE UNIQUE INDEX email_idx ON users (email);

以上任一命令都会在 `users` 表的 `email` 列上创建一个唯一索引，命名为 `email_idx`。

确保在创建索引前评估其对性能的影响，尤其是对于大型表。索引可以加速查询速度，但同时也会占用更多存储空间并且可能影响写入性能。

4、修改索引：修改现有的数据库索引通常涉及到几个方面，包括更改索引的名称、改变索引的类型、重新定义索引的列或列的顺序，以及更新索引的存储参数等。不同的数据库系统提供了不同的命令来实现这些修改。以下是针对一些主流数据库系统的常见修改索引的方法：

4.1在MySQL中可以使用以下几种方法来修改索引：

        4.1.1重命名索引:
           使用 `ALTER TABLE` 或 `RENAME INDEX` 语句。

ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;

执行结果： 

        4.1.2改变索引类型或列:
           你需要先删除旧的索引，然后再创建一个新的索引。

DROP INDEX index_name ON table_name;
CREATE [INDEX | UNIQUE INDEX | FULLTEXT INDEX] new_index_name ON table_name (column_name);

       4.1.3更改索引列的顺序或添加/删除索引列:同样，这通常意味着删除旧索引并创建新索引。

DROP INDEX index_name ON table_name;
CREATE INDEX new_index_name ON table_name (new_column_order);

请注意，修改索引可能会导致数据库锁定，影响到正在进行的读写操作，因此在生产环境中进行此类操作时应谨慎，并尽量安排在低峰时段进行。在修改索引之前，最好备份相关数据或索引，以防万一操作出错时可以恢复。

5、删除索引：

DROP INDEX index_name ON table_name;