mysql 索引使用笔记_mysql 8.0.31 create unique index near 'name)' at l-CSDN博客

本文链接：https://blog.csdn.net/qq_41936805/article/details/102150975

文章目录

Mysql 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

首先先看一下我们的原始表

mysql> show columns from bs_user;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | varchar(40)  | NO   | PRI | NULL    |       |
| student_id         | varchar(40)  | NO   |     | NULL    |       |
| username           | varchar(255) | NO   |     | NULL    |       |
| password           | varchar(255) | NO   |     | NULL    |       |
| enable_state       | varchar(255) | NO   |     | NULL    |       |
| create_time        | varchar(255) | NO   |     | NULL    |       |
| department_id      | varchar(40)  | NO   |     | NULL    |       |
| time_of_entry      | varchar(255) | NO   |     | NULL    |       |
| form_of_employment | int(1)       | NO   |     | NULL    |       |
| work_number        | varchar(20)  | NO   |     | NULL    |       |
| form_of_management | varchar(255) | NO   |     | NULL    |       |
| working_city       | varchar(16)  | NO   |     | NULL    |       |
| correction_time    | varchar(255) | YES  |     | NULL    |       |
| in_service_status  | varchar(255) | YES  |     | NULL    |       |
| company_id         | varchar(40)  | NO   |     | NULL    |       |
| company_name       | varchar(255) | NO   |     | NULL    |       |
| department_name    | varchar(255) | YES  |     | NULL    |       |
| level              | varchar(255) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

mysql> create index indexName on bs_user (username(5)); 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from bs_user;  
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | varchar(40)  | NO   | PRI | NULL    |       |
| student_id         | varchar(40)  | NO   |     | NULL    |       |
| username           | varchar(255) | NO   | MUL | NULL    |       |
| password           | varchar(255) | NO   |     | NULL    |       |
| enable_state       | varchar(255) | NO   |     | NULL    |       |
| create_time        | varchar(255) | NO   |     | NULL    |       |
| department_id      | varchar(40)  | NO   |     | NULL    |       |
| time_of_entry      | varchar(255) | NO   |     | NULL    |       |
| form_of_employment | int(1)       | NO   |     | NULL    |       |
| work_number        | varchar(20)  | NO   |     | NULL    |       |
| form_of_management | varchar(255) | NO   |     | NULL    |       |
| working_city       | varchar(16)  | NO   |     | NULL    |       |
| correction_time    | varchar(255) | YES  |     | NULL    |       |
| in_service_status  | varchar(255) | YES  |     | NULL    |       |
| company_id         | varchar(40)  | NO   |     | NULL    |       |
| company_name       | varchar(255) | NO   |     | NULL    |       |
| department_name    | varchar(255) | YES  |     | NULL    |       |
| level              | varchar(255) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

添加外键索引名如下

mysql> alter table bs_user add index index_stu(student_id);       
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from bs_user;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | varchar(40)  | NO   | PRI | NULL    |       |
| student_id         | varchar(40)  | NO   | MUL | NULL    |       |
| username           | varchar(255) | NO   | MUL | NULL    |       |
| password           | varchar(255) | NO   |     | NULL    |       |
| enable_state       | varchar(255) | NO   |     | NULL    |       |
| create_time        | varchar(255) | NO   |     | NULL    |       |
| department_id      | varchar(40)  | NO   |     | NULL    |       |
| time_of_entry      | varchar(255) | NO   |     | NULL    |       |
| form_of_employment | int(1)       | NO   |     | NULL    |       |
| work_number        | varchar(20)  | NO   |     | NULL    |       |
| form_of_management | varchar(255) | NO   |     | NULL    |       |
| working_city       | varchar(16)  | NO   |     | NULL    |       |
| correction_time    | varchar(255) | YES  |     | NULL    |       |
| in_service_status  | varchar(255) | YES  |     | NULL    |       |
| company_id         | varchar(40)  | NO   |     | NULL    |       |
| company_name       | varchar(255) | NO   |     | NULL    |       |
| department_name    | varchar(255) | YES  |     | NULL    |       |
| level              | varchar(255) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
18 rows in set (0.00 sec)

创建表的时候直接指定

mysql> create table mytable( 
id int not null, 
username varchar(16) not null, 
index indexName (username(16)) 
);      
Query OK, 0 rows affected (0.02 sec)

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除索引的语法

mysql> drop index indexName on mytable;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;      
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

唯一索引

先展示一下使用的案例table

mysql> show columns from mytable;      
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

创建索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

mysql> create unique index indexName on mytable(username(16));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除唯一索引

mysql> alter table mytable drop index indexName;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

创建表的时候直接指定

mysql> CREATE TABLE mytable(  
    ->  
    -> ID INT NOT NULL,   
    ->  
    -> username VARCHAR(16) NOT NULL,  
    ->  
    -> UNIQUE indexName (username(16))
    -> 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show colums from mytable;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'colums from mytable' at line 1
mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

显示索引信息

mysql> show index from mytable;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mytable |          0 | indexName |            1 | username    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

使用alter创建/删除索引或主键

索引

索引的添加

普通索引

mysql> alter table mytable add index indexName(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   | MUL | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

唯一索引

mysql> alter table mytable add unique indexName (username(16));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

全文索引

mysql> alter table mytable add fulltext indexName(username);
Query OK, 0 rows affected, 1 warning (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

索引的删除

mysql> alter table mytable drop index indexName;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键

添加主键

mysql> alter table mytable add primary key(username);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除主键

mysql> alter table mytable drop primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from mytable;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   |     | NULL    |       |
| username | varchar(16) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)