五、索引

MySQL 索引

创建索引
创建表时创建索引
CREATE 在已存在的表上创建索引
ALTER TABLE 在已存在的表上创建索引
查看并测试索引
删除索引

一、索引简介
索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查
询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序
表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
二、索引的分类
普通索引
唯一索引
全文索引
单列索引
多列索引
空间索引

三、准备实验环境

1. 准备表

image.png
2. 创建存储过程，实现批量插入记录

mysql> use school
mysql> delimiter $$
mysql> create procedure autoinsert1()
-> BEGIN
-> declare i int default 1;
-> while(i<200000)do
-> insert into school.t2 values(i,'ccc');
-> set i=i+1;
-> end while;
-> END$$
mysql> delimiter ;

查看存储过程的基本信息

image.png
查看存储过程的详细信息
image.png
3. 调用存储过程

mysql> call autoinsert1();

四、创建索引

＝＝＝创建表时
语法：
CREATE TABLE 表名 (
字段名 1 数据类型 [完整性约束条件…],
字段名 2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC]) 
);

创建普通索引示例：

CREATE TABLE department10 ( 
dept_id INT, 
dept_name VARCHAR(30) , 
comment VARCHAR(50), 
INDEX index_dept_name (dept_name) 
); 

创建唯一索引示例：

CREATE TABLE department11 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
UNIQUE INDEX (dept_name)
);

创建全文索引示例：

CREATE TABLE department12 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
log text,
FULLTEXT INDEX (log)
);

创建多列索引示例：

CREATE TABLE department13 (
dept_id INT,
dept_name VARCHAR(30) ,
comment VARCHAR(50),
INDEX (dept_name, comment)
);

CREATE 在已存在的表上创建索引

语法：
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
创建普通索引示例： 
CREATE INDEX index_dept_name ON department (dept_name); 
创建唯一索引示例：
CREATE UNIQUE INDEX index_dept_name ON department (dept_name);
创建全文索引示例：
CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);
创建多列索引示例：
CREATE INDEX index_dept_name_ comment ON department (dept_name, comment);

ALTER TABLE 在已存在的表上创建索引

语法：
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
创建普通索引示例：
ALTER TABLE department ADD INDEX index_dept_name (dept_name);
创建唯一索引示例：
ALTER TABLE department ADD UNIQUE INDEX index_dept_name (dept_name);
创建全文索引示例：
ALTER TABLE department ADD FULLTEXT INDEX index_dept_name (dept_name);
创建多列索引示例：
ALTER TABLE department ADD INDEX index_dept_name_comment (dept_name, comment);

四、管理索引

查看索引
SHOW CRETAE TABLE 表名\G
测试示例
EXPLAIN SELECT * FROM department WHERE dept_name=‘hr’;
删除索引 
show create table employee6; 
DROP INDEX 索引名 ON 表名; 

索引测试实验：

mysql> create table school.t2(id int,name varchar(30));
Query OK, 0 rows affected (1.33 sec)
mysql> desc school.t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure autoinsert1()
-> BEGIN
-> declare i int default 1;
-> while(i<100000)do
-> insert into school.t2 values(i,'ccc');
-> set i=i+1;
-> end while;
-> END$$
mysql> use school
Database changed
mysql> delimiter ;
mysql> call autoinsert1();

未创建索引

mysql> explain select * from school.t2 where id=20000;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1  | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 44848 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> create index index_id on school.t2(id);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings 作用: 0
mysql> explain select * from school.t2 where id=20000;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1  | SIMPLE      | t2    | ref  | index_id      | index_id | 5       | const | 1    | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
SHOW CREATE TABLE 表名\G
EXPLAIN： 命令的作用是查看查询优化器如何决定执行查询
花费时间比较：
创建索引前
mysql> select * from school.t2 where id=20000;
+-------+------+
| id    | name |
+-------+------+
| 20000 | ccc  |
+-------+------+
1 row in set (0.03 sec)

创建索引后

mysql> create index index_id on school.t2(id);
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from school.t2 where id=20000;
+-------+------+
| id    | name |
+-------+------+
| 20000 | ccc  |
+-------+------+
1 row in set (0.00 sec)