SQL知识：SQL索引的类型、用法、创建以及使用场景

SQL索引在数据库中扮演着至关重要的角色，它们可以极大地提升数据库的性能，降低查询的耗时。今天我们将深入介绍SQL索引的相关知识，包括SQL索引的类型、用法以及使用场景，也包括创建索引的语法、索引的区别、以及索引的创建原则等内容。

索引的用法

索引类似于书籍的目录，要想找到一本数的某个特定主题，需要先查找书的目录，定位对应的页码。

存储引擎使用类似的方式进行数据查询，先去索引当中找到对应的值，然后根据匹配的索引找到对应的数据行。

创建索引的语法

首先创建一个表

create table t1 (

id int primary key,

username varchar(20),

password varchar(20)

);

创建单个索引的语法

CREATE INDEX 索引名 on 表名（字段名）

索引名一般是：表名_字段名

给id创建索引：CREATE INDEX t1_id on t1(id);

创建联合索引的语法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）

给username和password创建联合索引：CREATE index t1_username_password ON t1(username,password)

其中index还可以替换成unique，primary key，分别代表唯一索引和主键索引

删除索引：DROP INDEX t1_username_password ON t1

索引对性能的影响

大大减少服务器需要扫描的数据量。

帮助服务器避免排序和临时表。

将随机I/O变顺序I/O。

大大提高查询速度。

降低写的速度（不良影响）。

磁盘占用（不良影响）。

索引的使用场景

• 对于非常小的表，大部分情况下全表扫描效率更高。
• 中到大型表，索引非常有效。
• 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

索引的类型

索引很多种类型，是在MySQL的存储引擎实现的。

• 普通索引：最基本的索引，没有任何约束限制。
• 唯一索引：和普通索引类似，但是具有唯一性约束。
• 主键索引：特殊的唯一索引，不允许有空值。

索引的区别

• 一个表只能有一个主键索引，但是可以有多个唯一索引。
• 主键索引一定是唯一索引，唯一索引不是主键索引。
• 主键可以与外键构成参照完整性约束，防止数据不一致。
• 联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）
• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。
• 全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索 （基本不用）

MySQL索引的创建原则

• 最适合创建索引的列是出现在WHERE或ON子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列。
• 索引列的基数越大，数据区分度越高，索引的效果越好。
• 对于字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间。
• 根据情况创建联合索引，联合索引可以提高查询效率。
• 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

MySQL索引的注意事项

1、联合索引遵循前缀原则

KEY(a,b,c)

#以下SQL语句可以用到索引

WHERE a = 1 AND b = 2 AND c = 3

WHERE a = 1 AND b = 2

WHERE a = 1

#以下SQL语句用不到索引

WHERE b = 2 AND c = 3

WHERE a = 1 AND c = 3

2、LIKE查询，%不能在前

WHERE name LIKE "%wang%"

#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

3、列值为空（NULL）时是可以使用索引的

MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。

4、如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引

例如：表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。

5、如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

6、列类型是字符串，查询时一定要给值加引号，否则索引失效。

例如：列name varchar(16)，存储了字符串"100" WHERE name = 100; 以上SQL语句能搜到，但无法用到索引。

重要声明：本文来自SQL数据库开发，经授权转载，版权归原作者所有，不代表锐成观点，转载的目的在于传递更多知识和信息。