sql的索引;它的作用、使用场景和创建/删除方法

sql的索引概述

SQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍。

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL系统的性能，加快数据的查询速度与减少系统的响应时间 

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构;通俗来讲索引就好比书本的目录，加快数据库的查询速度。

综上所述:sql的索引最重要的作用就是用来加快查询速度的

sql索引的作用

(1))提高查询效率

(2)消除数据分组、排序

(3)避免“回表”查询（索引覆盖）

(4)优化聚合查询

(5)用于多表JOIN关联查询

(6)利用唯一性约束，保证数据唯一性

(7)InnDB行锁实现

当然,索引也存在一些缺点,

sql索引的副作用

1.带索引的表在数据库中需要更多的存储空间;增加I/O成本;增加磁盘空间

2.增、删、改命令需要更长的处理时间，因为它们需要对索引进行更新

索引的使用场景

注意：此例子的中索引结构与数据库中的索引结构差距很大

user表有3个字段（id、name、age），存储8条记录，当我们查找age为20的记录时(在第六条)，sql语句 select * from user where age=20；

1 在没有索引情况下需要依次遍历表里的记录，第6次时找到age=20的记录；

2 如果我们在age列添加索引，只需3次，就可以找到age=20的记录。

正如官方所说：索引是一个数据结构；

通过语句show index from tablename查看索引

可以根据Index_type看出该索引由BTREE数据结构实现；除了BTREE，还有HASH、FULLTEXT、RTREE；

创建索引的指导原则

适合创建索引的列

1、该列用于频繁搜索

2、该列用于对数据进行排序

3、在WHERE子句中出现的列，在join子句中出现的列。

请不要使用下面的列创建索引

1、列中仅包含几个不同的值。

2、表中仅包含几行。为小型表创建索引可能不太划算，因为MySQL在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长 

数据的存储方式

- 聚集索引：聚簇索引的顺序就是数据的物理存储顺序，索引与数据存放在同一个文件中。

- 非聚集索引：非聚簇索引的顺序与数据的物理存储顺序不同，索引与数据存放在不同的文件。

拓展阅读:sql索引之聚集索引和非聚集索引

索引的应用层次

-  单列：主键索引、唯一索引、普通索引

-  多列：复合索引

创建索引三种方法

1、主键索引：创建了主键就会自动的创建主键索引

2、唯一索引：创建唯一键就会创建唯一索引

-- 创建表的时候添加唯一索引
create table t5(
    id int primary key,
    name varchar(20),
    unique ix_name(name)	-- 添加唯一索引
);

-- 给表添加唯一索引
mysql> create table t5(
    -> name varchar(20),
    -> addr varchar(50)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create unique index ix_name on t5(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 通过更改表的方式创建唯一索引
mysql> alter table t5 add unique ix_addr (addr);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建唯一键的语法

--创建唯一键语法一
create unique [index] 索引名 on 表名（字段名）
--创建唯一键方法二
alter table 表名 add uniqe [index] 索引名(字段名)

3、普通索引

-- 普通索引语法一
create index 索引名 on 表名（字段名）
-- 普通索引语法二
alter table 表名 add index 索引名(字段名)

-- 创建表的时候添加普通索引
mysql> create table t6(
    ->        id int primary key,
    ->        name varchar(20),
    ->        index ix_name(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 给表添加普通索引
mysql> create table t7(
    -> name varchar(20),
    -> addr varchar(50)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create index ix_name on t7(name) ;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 通过更改表的方式创建索引
mysql> alter table t7 add index ix_addr(addr);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

索引创建后，数据库根据查询语句自动选择索引

删除索引

语法:

drop index 索引名 on 表名
如:drop index ix_name on t7;