深入浅出数据库索引原理

本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容，下面一一展开。

一：MySQL索引基础

首先，我们将从索引基础开始介绍一下什么是索引，分析索引的几种类型，并探讨一下如何创建索引以及索引设计的基本原则。

创建一张用于测试的user表结构如下:

建表语句如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;

1：什么是索引

“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”

                                                                                         ——《高性能MySQL》

我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

 

2：索引的类型

可以使用SHOW INDEX FROM table_name;查看索引详情：

 

主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。如上图就是一个主键索引。

唯一索引 UNIQUE KEY

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引;

可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引;

普通索引 INDEX

这是最基本的索引，它没有任何限制。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引：

组合索引 INDEX

即一个索引包含多个列，多用于避免回表查询。

可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引：

全文索引 FULLTEXT(基本不用)

也称全文检索，是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引： 这种类型基本不用，因为要使用的话就是针对大文本创建，目前大文本一般都采用专业的搜索引擎检索查找效率更高。

索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。

注意: 创建索引的SQL语法可以不用记忆，一般平时我们用工具创建的居多，比如navicat，如下图，当然，如果特殊环境没有安装navicat类似的工具，记不住也没关系，临时上网查询也可以。

 

3：索引的设计原则

• 适合索引的列是出现在where子句中的列，或者连接子句中指定的列，还有排序的字段；
• 基数较小的类，索引效果较差，没有必要在此列建立索引，比如性别；
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间(这种也极少使用，一般都是用搜索引擎替代)
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

 

二：MySQL索引优化实战

上面我们介绍了索引的基本内容，这部分我们介绍索引优化实战。在介绍索引优化实战之前，首先要介绍两个与索引相关的重要概念，这两个概念对于索引优化至关重要。

1、索引相关的重要概念

基数

单个列唯一键（distict_keys）的数量叫做基数。

可以看到user表中一共有一百万条数据，其中name字段不同的有624473，gender有两个，phone有997117个，说明性别字段gender大量重复phone重复的很少跟主键差不多了。该表的索引情况如下图所示：

那么问题来了，name,gender，phone列都有索引， 那么SELECT * FROM user WHERE gender = 0; SELECT * FROM user WHERE name = 'swj';都能命中索引吗？