开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL索引（介绍 & 练习） -> 正文阅读

[大数据]MySQL索引（介绍 & 练习）

表结构如下：

1、索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

1）索引简介

模式（schema）中的一个数据库对象；
在数据库中用来加快表的查询速度；
通过使用快速路径访问方法快速定位数据，减少磁盘的I/O；
像表一样独立存放，但必须属于某个表（不能单独存在）；
由数据库自动维护，表被删除时，索引会和表一起被删除；
索引的作用类似于书的目录。

2）索引优点 & 缺点

优点：

提高检索数据的速度；
提高查询速度；
节省查询中分组和排序的时间。

缺点：

创建和维护索引需要耗费时间（耗费的时间随数据量递增）；
索引需要占用物理空间；
增、删、改（数据）时，需要动态维护索引，一定程度降低了数据的维护速度。

3）索引分类

普通索引
惟一性索引
全文索引
单列索引
多列索引
空间索引

4）索引设计原则

建议选择惟一性索引；
为经常需要排序、分组或联合操作的字段建立索引；
为常作为查询条件的字段建立索引；
限制索引的数目；
尽量使用数据量少的索引；
尽量使用前缀来索引；
定期删除不再使用或使用很少的索引；

5）创建索引

1> 创建索引（表仍未创建）

CREATE TABLE 表名(属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
···
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);

①创建普通索引

Create table index1(
Id int,
Name varchar(20),
Sex boolean,
Index (id)
);

Show create table index1 \G
Explain select * from index1 where id=1 \G

②创建唯一性索引

Create table index2(
Id int unique,
Name varchar(20),
Unique index index2_id(id asc)
);

③创建全文索引

只能创建在char,varchar或text类型的字段上。
Create table index3(
Id int,
Info varchar(20),
Fulltext index index3_info(info)
);

explain select * from table where id=1;

④创建单列索引

Create table index4(
Id int,
Subject varchar(30),
Index index4_st(subject(10))
);

⑤创建多列索引

Create table index5(
Id int,
Name varchar(20),
Sex char(4),
Index index5_ns(name,sex)
);

#使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。

⑥创建空间索引

Create table index6(
Id int,
Space geometry not null,
Spatial index index6_sp(space)
)engine=myisam;

#创建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。

2> 创建索引（表已创建）【修改索引】

①用create index语句正常创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index
CREATE  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
 ON  表名  (属性名  [ (长度) ]  [  ASC | DESC] );

1．创建普通索引
CREATE INDEX index_name ON table(column(length));

2．创建惟一性索引
CREATE UNIQUE INDEX indexName ON table(column(length));

3．创建全文索引
CREATE FULLTEXT INDEX index_content ON article(content);

4．创建单列索引
CREATE INDEX index3_name on index3 (name(10));

5. 创建多列索引
#不做演示

6. 创建空间索引
#不做演示

②用ALTER TABLE语句来创建索引

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER  TABLE  表名  ADD   [ UNIQUE | FULLTEXT | SPATIAL ]   INDEX  
索引名（属性名  [ (长度) ]  [ ASC | DESC]）;

1．创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column(length))

2．创建惟一性索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3．创建全文索引
ALTER TABLE index3 add fulltext index index3_name(name);

4．创建单列索引
ALTER TABLE index3 add index index3_name(name(10));

5．创建多列索引
#不做演示

6．创建空间索引
#不做演示

6）删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。
对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：

DROP  INDEX  索引名  ON  表名 ;
ALTER TABLE 表名 DROP INDEX 索引名;

7）简单测试索引是否创建成功

explain select * from 表名 where 字段名（需要查看的索引） = 字段值;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  ? | ?           | ?       | ?          | ?    | ?             | ?    | ?       | ?    |    ? |        ? | ?           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
#表格中的“?”的具体值到时候自己对照下方的解释自己理解

EXPLAIN分析结果的含义:
	table：这是表的名字。 
	type：连接操作的类型
	possible_keys：可能可以利用的索引的名字
	Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
	key_len：索引中被使用部分的长度，以字节计。
	ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
	rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
	Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

2、素材

writers表结构:

字段名		数据类型			主键		外键		非空		唯一		自增
w_id		SMALLINT(11)	是		否		是		是		是
w_name		VARCHAR(255)	否		否		是		否		否
w_address	VARCHAR(255)	否		否		否		否		否
w_age		CHAR(2)			否		否		是		否		否
w_note		VARCHAR(255)	否		否		否		否		否

3、练习要求

1）在数据库里创建表writers,存储引擎为MyISAM,创建表的同时在w_id字段上添加名称为UniqIdx的唯一索引

mysql> create database homework;

mysql> use homework;
mysql> create table writers
    -> (
    -> w_id SMALLINT(11) primary key unique auto_increment,	#没有写非空是因为主键包含非空
    -> w_name VARCHAR(255) not null,
    -> w_address VARCHAR(255),
    -> w_age CHAR(2) not null,
    -> w_note VARCHAR(255),
    -> unique index index_id(w_id)
    -> )engine=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_homework |
+--------------------+
| sign               |
| stu                |
| stu_mark           |
| writers            |
+--------------------+
4 rows in set (0.00 sec)

mysql> desc writers;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| w_id      | smallint(11) | NO   | PRI | NULL    | auto_increment |
| w_name    | varchar(255) | NO   |     | NULL    |                |
| w_address | varchar(255) | YES  |     | NULL    |                |
| w_age     | char(2)      | NO   |     | NULL    |                |
| w_note    | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> explain select * from writers where w_id=1;
+----+-------------+---------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys         | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | writers | NULL       | const | PRIMARY,w_id,index_id | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+-----------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#可以看到使用了“index_id”

2）使用alter table语句在w_name字段上建立nameIdx的普通索引

mysql> alter table writers add index nameIdx (w_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from writers where w_name="patton";
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | writers | NULL       | ref  | nameIdx       | nameIdx | 767     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
#可以看到使用了“nameIdx”

3）使用CREATE INDEX 语句在w_address和w_age字段上面建立名称为MultiIdx的组合索引

mysql> create index Multildx on writers(w_address,w_age);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from writers where w_address="guangzhou" and w_age="18";
+----+-------------+---------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | writers | NULL       | ref  | Multildx      | Multildx | 774     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#可以看到使用了“Multildx”

4）使用create index语句在w_note字段上建立名称为FTIdex的全文索引

mysql> create fulltext index FTIdex on writers(w_note);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from writers where w_note="no";
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | writers | NULL       | ALL  | FTIdex        | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#可以看到使用了“FTIdex”

5）删除名为FTIdx的全文索引

mysql> drop index FTIdex on writers;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#或者

mysql> alter table writers drop index FTIdex;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0