MYSQL索引的使用详解

远昔技术教程 2023-11-16 271 0

MYSQL数据库中关于索引的使用，以及各索引的区别。在开发时经常遇到大数据查询缓慢等情况，这时候需要给相对应的字段添加索引来增加sql的执行速度。

一．索引概述

索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。由于引擎的关系，MyISAM 表是将数据行存放在数据文件里，索引值存放在索引文件里。索引就是一个排好序的键值对数组，查询时通过这种键值对快速找到对应的数据。对于 InnoDB 表来说，它的索引也是排好序的数组，但它的数据行与索引值存放在同一个文件里。

二．索引种类

从索引的定义方式和用途中来看，所以一共有四种索引：

1.普通索引(Index)，用于提升查询效率；

2.唯一索引(Unique)，除了提升查询效率还要求字段值不得重复；

3.主键索引(PrimaryKey)，唯一性且不得为空的索引；

4.全文索引(Fulltext)，用于在大量文本搜索中建立的索引。

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

相比单表查询，如果没有建立索引，最多就是全表扫描一遍。但对于多表查询时，索引的重要度就要大很多。比如三张表的联合查询时，如果没有建立索引，将是三张表数据行的乘积。比如：

//假设每个表有 1000 条数据，那么需要扫描 1000*1000*1000=10 亿次 WHEREt1.c1=t2.c2ANDt1.c1=t3.c3;
如果给 c2，c3 分别建立了索引，那么充其量只扫描 t1 一个全表扫描，这样另外两张表的 1000*1000=100 万次的乘积扫描量就减少了，大大的提高了查询速度。

虽然索引看上去那么美好，但凡事都有两面性，在提高效率和速度的同时却增加了某些负担：

1.索引需要使用更多的磁盘空间，索引越多，占用磁盘越多，有时甚至比表占用的多；

2.索引在提升查询速度的同时，降低了插入、更新和删除的操作速度，因为它们多了一项工作，就是在增删改的过程中更新索引。索引越多，速度越慢。所以，索引优化是一门比较大的学问，需要根据业务、环境、配置等一些列问题进行最右设计，需要较长时间的学习和实践才能有所体会。

三．索引操作

1.创建主键索引这个索引比较简单，我们一直在使用，只要创建一个无符号整型且自动增长的列，然后设置成主键即可。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE id=1;
2.创建普通或唯一索引直接进入 navicat 设计表的第二栏，选择一个字段(比如 user 字段)，添加一个 Nomral(普通索引)或 Unique(唯一索引)。

//通过 EXPLAIN 语句查看索引状态 EXPLAIN SELECT * FROM think_user WHERE user='蜡笔老新';
//查看表所有索引情况 SHOW INDEX FROM think_user;
PS：在添加主键、唯一、普通索引的时候，我们会需要选择一种数据结构，默认为 B-Tree(二叉树)结构，还有一种为 HASH(哈希)结构。从理论上来看，HASH 索引的效率比 B-Tree 高，但常用的还是 B-Tree，因为两种在各自查询的方式和范围有所不同。

1.Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

2.Hash 索引无法被用来避免数据的排序操作。

3.Hash 索引不能利用部分索引键查询。

4.Hash 索引在任何时候都不能避免表扫描。

5.Hash 索引遇到大量 Hash 值相等的情况后性能并不一定就会比 B-Tree 索引高。

所以，要使用 HASH，就必须结合具体业务和大量经验。

3.全文索引这个索引，一般来说对于我们用处不大。属于在大文章里进行对某些关键字进行索引，但由于中文分词识别问题，基本无法使用。那么对于英文状态下，建立全文索引后，可以依次 SQL 查询。

//全文索引搜索 SELECT * FROM think_user WHERE MATCH (intro) AGAINST ('teacher');
PS：如果搜索 is，则无效，因为这是最常见的却无意义的词，被 MySQL 设置成停止词。

四．索引原则

１.不要过度索引。索引越多，占用空间越大，反而性能变慢；

２.只对 WHERE 子句中频繁使用的建立索引；

３.尽可能使用唯一索引，重复值越少，索引效果越强；