索引使用实例_Mysql索引详解及基本用法

MySQL索引的概念

索引是一种特殊的文件（数据表上的索引是表空间的组成部分），其中包含指向数据表中的所有记录的引用指针。用更通俗的话来说，数据库索引就像一本书前面的目录，可以加快数据库查询的速度。

索引有两种类型：聚簇索引和非聚簇索引。聚簇索引是按照数据的物理位置排序的，而非聚簇索引则不同。聚簇索引可以提高多行检索的速度，而非聚簇索引对于单行检索非常快。

需要注意的是，创建过多的索引会影响更新和插入的速度，因为需要更新每个索引文件。对于需要频繁更新和插入的表，没有必要为很少使用的 where 子句创建单独的索引。对于相对较小的表，排序成本不会很大，也没有必要创建另一个索引。

1. 总索引

普通索引（通过关键字KEY或INDEX定义的索引）的唯一任务就是加快数据的访问速度，因此只对查询条件（WHERE = ...）或排序条件（ORDER BY）中出现频率最高的数据列建立索引，尽可能选择数据最整齐、最紧凑的数据列（如整型数据列）建立索引。

[sql]

– 直接创建索引（使用名称第一个字符）)(()) – 修改表结构添加索引() – 创建表时同时创建索引 e(idint(11)T,(255),(id),(title))==; – 删除索引； 创建复合索引 table(,); 命名规范有没有注意？采用“表名 字段1 字段2”的方式

2.唯一索引

和普通索引类似，不同之处在于索引列值必须唯一，但是允许为空值（注意和主键不同）。如果是组合索引，列值的组合必须唯一，创建方法和普通索引类似。

如果你能确定某个数据列只会包含不同的值，那么在为这个数据列建立索引时，就应该使用关键字将其定义为唯一索引。这样做的好处是：第一，简化了MySQL对此索引的管理，索引的效率更高；第二，当向数据表中插入新的记录时，MySQL会自动检查新记录的这个字段的值是否已经出现在某条记录的这个字段中；如果是的话，MySQL将拒绝插入该条新记录。也就是说，唯一索引可以保证数据记录的唯一性。其实在很多情况下，人们建立唯一索引往往不是为了提高访问速度，而只是为了避免数据重复。

[sql]

--创建唯一索引() --修改表结构N() --创建表时直接指定e(idint(11)T,(255),(id),(title));

3. 主索引

之前已经反复强调过，必须为主键字段建立索引，这种索引就叫做“主索引”，主索引和唯一索引唯一的不同就在于前者定义时使用的关键字不一样。

4. 外键索引

如果为外键列定义了外键约束，MySQL 将定义内部索引来帮助自身以最有效的方式管理和使用外键约束。

5.全文索引（）

MySQL 从 3.23.23 版开始支持全文索引和全文搜索。索引只能用于表；它们可以作为 TABLE 语句的一部分从 CHAR 或 TEXT 列创建，也可以稍后使用 ALTER TABLE 或 INDEX 添加。对于较大的数据集，将数据输入未索引的表中然后创建索引比将数据输入现有索引更快。但是，请记住，对于大型数据表，生成全文索引是一种非常耗时且占用磁盘空间的做法。

文本字段上的普通索引只能加快对出现在字段内容开头的字符串（即字段内容开头的字符）的搜索。如果字段包含由几个甚至多个单词组成的大型文本段，普通索引就没什么用了。这种类型的搜索经常以 LIKE %word% 的形式出现，这对 MySQL 来说非常复杂。如果需要处理的数据量很大，响应时间会很长。

这时全文索引就派上用场了。创建这种类型的索引时，MySQL 会创建文本中出现的所有单词的列表，查询会使用此列表来检索相关记录。可以使用表创建全文索引，也可以在必要时使用以下命令添加全文索引：

修改表添加（，）

使用全文索引，您可以使用查询命令来检索包含一个或多个给定单词的数据记录。以下是此类查询命令的基本语法：

* 从

其中匹配（，）（'单词 1'，'单词 2'，'单词 3'）

上述命令将查询 sum 字段中包含word1、word2 和word3 的所有数据记录。

[sql]

– 创建适合添加全文索引的表 e(idint(11)T,,(id),()); – 修改表结构添加全文索引 e() – 直接创建索引 e()

6.单列索引、多列索引

多个单列索引的查询效果与单个多列索引的查询效果有所不同，因为MySQL在执行查询时只能使用一个索引，会从多个索引中选择限制性最强的索引。

5. 复合索引（最左前缀）

SQL查询语句通常有很多限制，所以为了进一步提高MySQL的效率，我们应该考虑创建复合索引。例如上表中，对title和time创建复合索引：ALTER TABLE ADD INDEX (title(50), time(10))。创建这样的复合索引，其实相当于创建下面两组复合索引：

–标题，时间

-标题

为什么没有像time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”是结果，简单来说就是只从最左边开始，并不是所有包含这两列的查询都会用到这个组合索引，如下SQL语句所示：

–使用上述索引

* 来自 WHREE 标题='测试' 和时间=；

* 来自 WHREE 标题='测试'；

– 不要使用上述索引

* 从 WHREE 时间=；

MySQL 索引优化

上面说了使用索引的好处，但是过度使用索引会导致滥用。因此索引也有其弊端：虽然索引大大提高了查询速度，但是也会降低更新表的速度，比如执行、和。因为在更新表的时候，MySQL不仅需要保存数据，还需要保存索引文件。创建索引会占用索引文件的磁盘空间。一般这个问题并不严重，但如果在一张大表上建立多个组合索引，索引文件就会迅速膨胀。索引只是提高效率的一个因素，如果你的MySQL有一个很大的数据表，那么你需要花时间研究建立最佳的索引或者优化查询语句。下面是一些MySQL索引注意事项和优化方法的总结和收集。

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. 不要对列执行操作

例如：* 来自 YEAR() 的用户

最后，MySQL 只对以下运算符使用索引：=、in，有时还有 like（不以通配符 % 或 _ 开头时）。理论上，每个表最多可以有 16 个索引，但除非数据量真的很大，否则使用太多索引并不好玩。例如，刚才我为文本字段创建索引时，系统差点卡死。

其他用法：

只有当数据库中有足够多的测试数据时，性能测试结果才有意义。如果测试数据库中只有几百条记录，它们往往在执行第一个查询命令后就被加载到内存中，这将使后续的查询命令执行得非常快——无论是否使用索引。只有当数据库中的记录超过 1,000 条，并且数据总量超过 MySQL 服务器上的内存总量时，数据库的性能测试结果才有意义。

当你不确定哪些列应该被索引时，你经常可以从命令中获得一些帮助。这只是一个普通命令前面的关键字。有了这个关键字，MySQL 不会执行命令，而是对其进行分析。MySQL 会以表格的形式列出查询执行过程和使用的索引（如果有）。

在命令的输出中，第一列是从数据库读取的数据表的名称，它们按照读取的顺序排列。类型列指定了此数据表与其他数据表的关联（JOIN）。在各种关联类型中，效率最高的是，其次是 const、ref、range、index 和 All（All 表示：对应上级数据表的每条记录，本数据表的所有记录都要读取一次——这种情况往往可以通过使用索引来避免）。

数据列显示 MySQL 可用于搜索记录的各种索引。键列是 MySQL 实际使用的索引。数据列中给出了此索引的长度（以字节为单位）。例如，对于数据列上的索引，此字节长度将为 4。如果使用复合索引，您还可以在数据列中看到 MySQL 使用了其中的哪些部分。一般来说，数据列中的值越小越好（意味着速度越快）。

ref 列给出了关系中另一个表中的列的名称。row 列是 MySQL 在执行此查询时期望从表中读取的行数。row 列中所有数字的乘积使我们大致了解此查询需要处理多少种组合。

——————————————————————————————————————–

8. key 和 index 的区别

MySQL的key和index有些让人困惑，其实这考验的是你对数据库架构的理解。

1).key是数据库的物理结构，有两层含义，一是约束（侧重于约束和规范数据库的结构完整性），二是索引（用于辅助查询）。包括key、key、key等。

key有两个作用，一个是约束函数（），用来规范一个存储主键和唯一性，但是也会在这个key上建立索引。

这个key同样有两个作用，一个是约束函数（），规定了数据的唯一性，但是也会在这个key上建立索引。

该键同样具备两个作用，一是约束作用()，规范数据的引用完整性，但同时也在这个键上建立了索引；

可以看出，MySQL 的 key 既有 index 的含义，又有 MySQL index 的含义，可能跟其他数据库不一样。（至少在创建外键时，不会自动创建索引。）因此，创建 key 的方式有以下几种：

（1）在字段级别创建key，比如table t(id int not null key);

（2）在表级别创建，例如table t(id int, key (id));

（3）在表级别创建key，例如table t(id int, key(id));

其他键的创建类似，但无论使用哪种方法，都会创建数据和索引，只是索引使用这个或键。

2).索引是数据库的物理结构，只是用来辅助查询的，创建后会以目录式的结构存放在另一个表空间（MySQL中为）中。如果要对索引进行分类的话，可以分为前缀索引、全文索引等。

因此，索引仅仅是一个索引，它并不约束索引字段的行为（这就是键的用途）。

例如，表 t(id int, index (id));

3). 最后澄清：

（1）我们在讲索引分类的时候，把它分为主键索引，唯一索引，普通索引（这个是纯粹的索引）等等，这也是基于该索引是否被当做键来看待的。

例如，table t(id int, index (id)); – 索引用作键

（2）最重要的是，不管怎么描述，我们要明白这个索引到底是纯粹的索引，还是作为键来用，当做键来用的时候，它有两种含义，或者说起到了两种作用。

如何在查询中使用索引

* FROM WHERE 名称=外部输入数据

一开始的时候，数据不多的时候，执行效果还不错。

随着数据量的增加，这个查询执行得越来越慢。

然后根据名称创建索引

索引为（名称）；

这样就可以加速之前的查询。

但是有一天，你执行了如下SQL，发现速度又慢了。

* 来自年龄 = 25

为什么？因为年龄字段没有索引。

索引仅基于名称

换句话说，WHERE 中的条件会自动判断是否有可用的索引，如果有，则是否应该使用它们。

多列索引是包含两个字段的索引。

例如：INDEX ON (name,age);then*FROM WHERE name LIKE'张%'

并且年龄 = 25

这样的查询将能够使用上述索引。

多列索引的另一个用例是，在某些情况下，仅通过访问索引进行查询就足够了，而无需再次访问表。

例如：( avg ) AS 平均年龄 FROM WHERE name LIKE '张%'

此时，name 和 age 都包含在索引中。查询不需要从表中检索数据。