MYSQL优化之索引（index）-语法认知篇

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;