MySQL索引创建及使用

MySQL索引的建立和使用。

出发地：https://blog.csdn.net/dengchenrong/article/details/88425762.

背景

最近加深了对数据库相关知识的学习，借此机会总结记录。我本来想自己总结一下。但是看到一个博客写的简洁明了，为了节省时间就直接移动了，有些地方还加了自己的见解。

https://blog.csdn.net/xluren/article/details/32746183

至于MySQL索引的好处，如果MySQL设计正确并且使用了索引就是一个兰博基尼，那么MySQL在没有设计和使用索引的情况下就是一个人力三轮车。对于没有索引的表，单表查询可能是瓶颈，但通常大型网站一天可能会产生几十万甚至几百万的数据，没有索引的查询会变得非常慢。在WordPress中，索引被添加到几个数据表中经常被查询的字段中。例如，BTREE索引是为wp_comments表中的五个字段设计的。

一、简单的对比测试

以我去年测试的数据为简单例子，20多个数据源随机生成200万条数据，每个数据源平均重复约10万次。表结构比较简单，只包含一个自加ID，一个char类型，一个text类型，一个int类型，单个2G大小的表，使用MyIASM引擎。测试开始时没有添加任何索引。

从文章中选择MySQL id，from _ UNIX time (time)，其中a.title=' test title '。

查询所需的时间很可怕。如果加入联邦查询等约束，数据库会疯狂消耗内存，影响前端程序的执行。然后向标题字段添加一个BTREE索引：

mysql ALTER TABLE文章ADD INDEX INDEX _ article _ title ON title(200)；

索引标题字段后，查询速度会很快。

二、MySQL索引的概念

索引是特殊文件(InnoDB数据表上的索引是表空间的一部分)，它包含指向数据表中所有记录的引用指针。更通俗地说，数据库索引好比是一本书前面的目录,能加快数据库的查询速度.在上面的SQL语句中，如果没有索引，数据库将遍历所有200条数据，然后选择合格的一条；拥有相应的索引后，数据库将直接在索引中查找符合条件的选项。如果我们将SQL语句更改为“select * from article，其中id=200000”，您希望数据库按顺序读取200万行数据并给出结果还是直接在索引中定位它们(注意：默认情况下，通用数据库将为主键生成一个索引)。

索引有两种类型：聚集索引和非聚集索引。聚集索引基于数据存储的物理位置，但非聚集索引不同。聚集索引可以提高多行检索的速度，而非聚集索引对于单行检索来说速度很快。

三、索引的类型

1、普通索引

这是最基本的指标，没有限制。例如，为上面的标题字段创建的索引是一个普通的索引，MyIASM中BTREE类型的默认索引也是我们在大多数情况下使用的索引。

创建者：

直接创建索引

在表上创建索引索引名(列(长度))

修改表结构的方式添加索引

ALTER TABLE TABLE _ name ADD INDEX INDEX _ name ON(列(长度))

创建表的时候同时创建索引

创建表格` TABLE `(

` id` int(11)不为空自动增量，

` title` char(255)字符集utf8 COLLATE utf8_general_ci不为空，

“content”文本字符集utf8 COLLATE utf8_general_ci NULL，

` time` int(10) NULL DEFAULT NULL，

主键(` id `),

INDEX index_name(标题(长度))

)

• 删除索引

DROP INDEX index_name ON table

2、唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

• 创建唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length))

• 修改表结构

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

• 创建表时同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	UNIQUE indexName (title(length))
);

3、组合索引
平时用的SQL查询
语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time
–title

为什么没有time这样的组合索引呢这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

• 使用到上面的索引

SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';

• 未使用到上面的索引

SELECT * FROM article WHREE time=1234567890;

以上就是常用的三种索引类型及其创建使用的方法，当然还有其他的类型，这里我就列举我们平时最常用的几种，更详细和全面的索引相关资料请咨询度娘。

总结：
常用的索引类型
1、普通索引
2、唯一索引
3、组合索引
普通索引和唯一索引的创建方式有三种，分别是直接创建、修改表结构创建、创建表时同时创建，注意组合索引的组合规则是最左前缀索引

四、索引的优化

上面说了使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

1. 何时使用聚集索引或非聚集索引

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6. 不要在列上进行运算

例如：select
* from users where
YEAR(adddate)2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from
users where adddate’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

最后总结一下，MySQL只对以下操作符才使用索引：,=,=,,=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。
而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引会引起反作用，索引虽好用，可不要太迷恋噢。