Day14：关于MySQL的索引——创、查、删

 前言：先创建一个练习的数据库和数据

1.创建数据库并创建数据表的基本结构

-- 创建练习数据库
CREATE DATABASE index_practice;
USE index_practice;-- 创建基础表（包含CREATE TABLE时创建索引）
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,          -- 主键索引（自动创建）product_code VARCHAR(20) UNIQUE,            -- 唯一索引product_name VARCHAR(50) NOT NULL,price DECIMAL(10,2),description TEXT,INDEX name_index (product_name(10))        -- 前缀索引
) ENGINE=InnoDB;

 2.导入实例数据 

-- 插入最少量的测试数据
INSERT INTO products (product_code, product_name, price, description) VALUES
('P1001', '无线鼠标', 99.00, '2.4G无线连接'),
('P1002', '机械键盘', 299.00, 'RGB背光键盘'),
('P1003', '蓝牙耳机', 199.00, '主动降噪功能'),
('P1004', '智能手表', 599.00, '心率监测功能');

索引的优点

• 可以提高查询速度。
• 可以确保数据的唯一性。
• 提高ORDER BY和GROUP BY的执行速度。

索引设计规则

• 为频繁查询的字段创建索引。
• 数据量较小的表最好不要创建索引。
• 尽量在不同值较多的字段上创建索引。
• 一个表中的索引不是越多越好，需要限制索引的数量。
• 对于频繁进行插入、删除、修改操作的表，创建的索引越多，则更新表所耗费的时间就越长。

一、索引的分类 

1.普通索引

最基本的索引类型，它没有唯一性的限制，可以有重复值和空值。创建普通索引的关键字是INDEX。

2.唯一索引

这种索引与普通索引基本相同，区别在于唯一索引的索引字段的值必须是唯一的，不允许重复，但允许有空值。创建唯一索引的关键字是UNIQUE。

3.主键索引

主键索引是一种特殊的唯一索引，不同之处在于每张表只能有一个主键索引，且不允许有空值。创建主键索引的关键字是PRIMARY KEY，即主键。一般在创建表时指定主键，也可以通过修改表的方式添加主键。

4.全文索引

全文索引只能在CHAR、VARCHAR或者TEXT类型的字段上创建，并且只能在存储引擎为MyISAM和InnoDB的表中创建。创建全文索引的关键字是FULLTEXT。当查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。 

注意：

创建在一个字段上的索引称为单索引

创建在多个字段上的索引称为组合索引、复合索引或多列索引

如果唯一索引是组合索引，则多个字段的组合必须是唯一的。

二、创建索引 

CREATE  [ UNIQUE | FULLTEXT ]  INDEX 索引名
ON 表名 (字段名称1 [ (长度1) ]  [ ASC  DESC ] 
[, 字段名称2 [ (长度2) ]  [ ASC  DESC ]] );

说明：

• 索引名：指定创建的索引名称，在一个表中可以创建多个索引，但是每个索引名必须是唯一的。
• UNIQUE | FULLTEXT：可选项，UNIQUE表示创建的是唯一索引；FULLTEXT表示创建的是全文索引。
• 长度：表示使用字段前多少个字符创建索引，这样可以减少索引文件的大小。只能对字符串类型的字段指定长度。
• ASC | DESC：指定索引按照升序ASC或者降序DESC排序。默认值为ASC。 

 1.使用CREATE INDEX语句创建索引

CREATE TABLE demo_table (id INT PRIMARY KEY,                     -- 主键索引col1 VARCHAR(20) UNIQUE,                -- 唯一索引col2 VARCHAR(30),INDEX idx_col2 (col2)                   -- 普通索引
);mysql> DESC demo_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| col1  | varchar(20) | YES  | UNI | NULL    |       |
| col2  | varchar(30) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.使用ALTER TABLE语句添加索引 

CREATE INDEX idx_name ON products(product_name);mysql> CREATE INDEX idx_name ON products(product_name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> DESC products;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | int           | NO   | PRI | NULL    | auto_increment |
| product_code | varchar(20)   | YES  | UNI | NULL    |                |
| product_name | varchar(50)   | NO   | MUL | NULL    |                |
| price        | decimal(10,2) | YES  |     | NULL    |                |
| description  | text          | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

3.使用CREATE TABLE语句创建索引

ALTER TABLE products ADD INDEX idx_price (price);mysql> ALTER TABLE products ADD INDEX idx_price (price);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> DESC products;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| id           | int           | NO   | PRI | NULL    | auto_increment |
| product_code | varchar(20)   | YES  | UNI | NULL    |                |
| product_name | varchar(50)   | NO   | MUL | NULL    |                |
| price        | decimal(10,2) | YES  | MUL | NULL    |                |
| description  | text          | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

三、查看索引

SHOW { INDEX | INDEXES | KEYS }{ FROM | IN } 表名 [{ FROM | IN } 数据库名];

• SHOW INDEX语句以二维表的形式显示指定表中的所有索引信息
• 由于显示的信息较多不易查看，使用\G参数可以将每一行垂直显示，查看效果更好。 

SHOW INDEX FROM products;mysql> SHOW INDEX FROM products;
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY      |            1 | id           | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          0 | product_code |            1 | product_code | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| products |          1 | name_index   |            1 | product_name | A         |           4 |       10 |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_name     |            1 | product_name | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_price    |            1 | price        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.03 sec)

四、删除索引

1.使用DROP INDEX语句删除索引

DROP INDEX 索引名 ON 表名;

DROP INDEX idx_name ON products;

2.使用ALTER TABLE语句删除索引 

ALTER TABLE 表名
DROP INDEX 索引名
DROP PRIMARY KEY ;

ALTER TABLE products
DROP INDEX idx_price;