MYSQL优化之索引（index）-语法认知篇
一、索引概述
1.什么是索引
索引是一种单独的、存储在磁盘上的数据库结构，包含对数据表中所有记录的引用指针。它的作用就相当于书籍的目录，可以加快对数据的查询速度
2.索引的优点
-
可以大大加快数据的检索速度
-
可以保证数据库表中每行数据的唯一性（唯一索引）。
-
加速表和表之间的连接。
-
可以显著减少查询中分组和排序的时间。
3.索引的分类
- 普通索引：普通索引（INDEX）是MySQL中最基本的索引，它的唯一任务是加快对数据的访问速度。
- 组合索引：组合索引是相对单列索引而言，单列索引是指索引中只包含单个列，一个表可以有多个单列索引；组合索引是指在表的多个字段组合上创建的索引。
- 唯一索引：设置唯一索引（UNIQUE）的列的值必须唯一，但允许有空值。如果索引包含多个字段，则列值的组合必须唯一。创建唯一索引的主要目的不是提高查询速度，而是避免数据重复。
- 全文索引：全文索引（FULLTEXT）可以在CHAR，VARCHAR和TEXT类型的列上创建。在创建索引的列上支持值的全文查找，并且允许列值重复和为NULL。
- 空间索引：空间索引（SPATIAL）是对空间数据类型的字段创建的索引，MySQL中的空间数据类型主要有GEOMETRY、POINT、LINESTRING和POLYGON。创建空间索引的字段，必须将其声明为NOT NULL。
4.创建索引的原则
- 数据量很小的表最好不要使用索引。
- 对于经常执行修改操作的表不要创建过多索引，并且索引中的列应尽可能少。而对于经常执行查询操作的字段，应该创建索引。
- 索引并非越多越好。过多的索引会占用大量磁盘空间，并且会影响插入、修改和删除等语句的性能。
- 在条件表达式中经常会用到的不同值较多的列上创建索引，不同值较少的列不要创建索引。因为这样不仅不会提高查询效率，反而会严重降低更新速度。
- 在频繁进行排序或分组（即进行group by或order by操作）的列上创建索引。如果待排序的列有多个，可以在这些列上创建组合索引。
二、创建和查看索引
1.在创建表的同时创建索引
1.1.语法
CREATE TABLE 表名(
[字段 数据类型 约束条件 comment '字段描述',]
……
INDEX|KEY 索引名称 (索引字段 1 [(length)]) [ASC|DESC],
……
(索引字段 n [(length)]) [ASC|DESC])
);
1.2.示例
创建数据表tb_user，并在创建表的时候创建索引（加快对数据的访问速度）数据表要求如下:
| 字段 | 数据类型（长度） | 约束条件 | 字段描述 |
|---|---|---|---|
| user_id | int | 主键、自增长 | 编号 |
| user_name | varchar（40） | 非空 | 用户名 |
| user_cardNo | varchar(18) | 非空 | 身份证号 |
| user_gender | int | 性别 | |
| user_birthday | date | 出生年月 | |
| user_phone | varchar(11) | 非空 | 联系电话 |
-- 创建用户表，在创建表的时候 创建索引
drop table if exists tb_user;
create table tb_user(
user_id int not null primary key auto_increment comment '编号',
user_name varchar(40) not null comment '用户名',
user_cardNo varchar(18) not null comment '身份证号',
user_gender int comment '性别',
user_birthday date comment '出生日期',
user_phone varchar(11) not null comment '联系电话',
-- 在创建表的时候创建索引 index 索引名(字段名)
index i_index(user_name),
-- 创建组合索引 index 索引名(字段1，字段2，字段n)
index z_index(user_name,user_gender,user_birthday)
);
2.使用ALTER TABLE语句在已有的表上创建。
-- 语法：
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [索引名] (索引字段[(length)]) [ASC|DESC]
-- 示例：给tb_user 表的 user_CardNo 身份证号添加唯一索引
alter table tb_user add unique(user_cardNo);
3.使用CREATE INDEX语句在已有的表上创建。
-- 语法：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名 (col_name[(length)]) [ASC | DESC]
-- 示例：给tb_user 表的 user_phone 电话号码字段添加全文索引
create fulltext index index_full_phone on tb_user (user_phone);
4.查看索引
-- 语法： show index from 表名
-- 示例：查看tb_user 表的索引
show index from tb_user;
三、删除索引
1.使用ALTER TABLE语句删除索引
-- 语法：ALTER TABLE 表名 DROP INDEX 索引名;
-- 示例：删除user_phone上的全文索引
alter table tb_user drop index index_full_phone;
2.删除主键索引
ALTER TABLE 表名 drop PRIMARY KEY;
3.使用DROP INDEX语句删除索引
-- 语法：DROP INDEX 索引名 ON 表名;
-- 示例：删除user_CardNo上的唯一索引
DROP INDEX user_cardNo ON tb_user;