索引的概念

  • 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（相似于c言语的链表经过指针指向数据记载的内存地址)。
  • 运用索引后能够不必扫描全表来定位某行的数据，而是先经过索引表找到该行数据对应的物理地址然后访问相应的数据，因而能加速数据库的查询速度。
  • 索引就好比是一本书的目录，能够依据目录中的页码快速找到所需的内容。
  • 索引是表中一列或许若干列值排序的办法。
  • 树立索引的目的是加速对表中记载的查找或排序。

索引的效果

  • 设置了合适的索引之后，数据库运用各种快速定位技术，能够大大加速查询速度，这是创立索引的最主要的原因。
  • 当表很大或查询涉及到多个表时，运用索引能够成千上万倍地进步查询速度。
  • 能够下降数据库的IO本钱，而且索引还能够下降数据库的排序本钱。
  • 经过创立仅有性索引，能够保证数据表中每一行数据的仅有性。
  • 能够加速表与表之间的衔接。
  • 在运用分组和排序时，可大大削减分组和排序的时刻。
  • 树立索引在搜索和恢复数据库中的数据时能明显进步性能

索引的副效果

  • 索引需求占用额外的磁盘空间。
    • 关于MyISAM引擎而言，索引文件和数据文件是别离的，索引文件用于保存数据记载的地址。
    • InnoDB 引擎的表数据文件本身就是索引文件，即索引文件和数据文件是同一个。
  • 更新一个包含索引的表需求比更新一个没有索引的表花费更多的时刻，这是由于索引本身也需求更新。因而，理想的做法是仅仅在常常被搜索的列(以及表)上面创立索引。

索引的排序功能

  1. 先创立一个表，在表内随机刺进记载（如：id不按次序）

    MySQL数据库——索引

  2. 创立id的索引，只检查id字段会排序好，但检查全部依然没有排序。

    MySQL数据库——索引
    MySQL数据库——索引
    MySQL数据库——索引

创立索引的准则依据

索引虽能够提高数据库查询的速度，但并不是任何情况下都适合创立索引。由于索引本身会耗费系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到详细的数据行，如果索引运用不当，反而会添加数据库的负担。

  • 表的主键、外键有必要有索引。由于主键具有仅有性，外键相关的是主表的主键，查询时能够快速定位
  • 记载数超越300行的表应该有索引。如果没有索引，每次查询都需求把表遍历一遍，会严重影响数据库的性能。
  • 常常与其他表进行衔接的表，在衔接字段上应该树立索引。
  • 仅有性太差的字段不适合树立索引。
  • 更新太频频的字段不适合创立索引。
  • 常常出现在 where子句中的字段，特别是大表的字段，应该树立索引。
  • 在常常进行 GROUP BY、ORDER BY的字段上树立索引
  • 索引应该建在选择性高的字段上。
  • 索引应该建在小字段上，关于大的文本字段甚至超长字段，不要建索引。

索引的分类和创立

create table member (id int(10),name varchar(10),cardid int(18),phone int(11), address varchar(50),remark text);

MySQL数据库——索引
MySQL数据库——索引

一般索引

最基本的索引类型，没有仅有性之类的约束。

  • 直接创立索引：CREATE INDEX 索引名 ON 表名 (列名[(length)]);
    • (列名(length))：length是可选项，下同。如果疏忽length的值，则运用整个列的值作为索引。如果指定，运用列的前length个字符来创立索引，这样有利于减小索引文件的大小。在不丢失精确性的情况下，长度越短越好。而且只有字符串类型才干设置length的大小。
    • 索引名主张以“_index”结尾。

MySQL数据库——索引
MySQL数据库——索引

  • 修正表办法创立：ALTER TABLE 表名 ADD INDEX 索引名 (列名);

    MySQL数据库——索引
    MySQL数据库——索引

  • 创立表的时分指定索引：CREATE TABLE表名（字段1 数据类型,字段2 数据类型[,...],INDEX 索引名(列名));

    MySQL数据库——索引

仅有索引

仅有索引：与一般索引相似，但区别是仅有索引列的每个值都仅有。仅有索引答应有空值（留意和主键不同)。如果是用组合索引创立，则列值的组合有必要仅有。添加仅有键将主动创立仅有索引。

  • 直接创立仅有索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名);

    MySQL数据库——索引

  • 修正表办法创立：ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

    MySQL数据库——索引

留意：如果表格中某字段中有两条记载的该字段内容相同，则无法为该字段添加仅有索引。同样，添加了仅有索引的字段无法添加内容相同的记载。

MySQL数据库——索引
MySQL数据库——索引

  • 创立表的时分指定：CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));
    MySQL数据库——索引

主键索引

主键索引：是一种特殊的仅有索引，有必要指定为”PRIMARY KEY”。一个表只能有一个主键，不答应有空值。添加主键将主动创立主键索引。

  • 创立表的时分指定：CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

    MySQL数据库——索引

  • 修正表办法创立：ALTER TABLE 表名 ADD PRIMARY KEY (列名);

    MySQL数据库——索引

组合索引（单列索引与多列索引）

组合索引（单列索引与多列索引）：能够是单列上创立的索引，也能够是在多列上创立的索引。需求满意最左准则，由于select句子的 where条件是顺次从左往右履行的，所以在运用select 句子查询时 where 条件运用的字段次序有必要和组合索引中的排序共同，不然索引将不会收效。

  • 直接创立索引：CREATE INDEX 索引名 on 表名(字段1,字段2，字段3);

    MySQL数据库——索引
    MySQL数据库——索引

  • 修正表办法创立：ALTER TABLE 表名 ADD INDEX 组合索引名 (字段1,字段2,字段3,...)

    MySQL数据库——索引

  • 创立表的时分指定：CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型, INDEX 索引名 (列名1,列名2,列名3));

    MySQL数据库——索引

留意：查询时，where指定的条件要按照组合索引的次序，索引才会收效。

MySQL数据库——索引

仅有组合索引

多个字段组合的值不能重复，其间某个单列是能够重复的

MySQL数据库——索引

这种情况下，单个或多个字段有多条记载重复并没有问题，只需设置的所有字段不完全一样即可。

MySQL数据库——索引
MySQL数据库——索引

全文索引

全文索引：适合在进行含糊查询的时分运用，可用于在一篇文章中检索文本信息。
在MySQL5.6版别以前，FULTEXT索引仅可用于MyISAM引擎，在5.6版别之后innodb引擎也支撑FULLTEXT索引。全文索引能够在CHAR、VARCHAR或许TEXT类型的列上创立

  • 直接创立索引：CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

    MySQL数据库——索引

  • 修正表办法创立：ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

    MySQL数据库——索引

  • 创立表的时分指定索引：CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));数据类型能够为CHAR、VARCHAR 或许TEXT

    MySQL数据库——索引

运用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST ('查询内容');

MySQL数据库——索引

查询结果内容只需包含该单词（字符串）即可。

MySQL数据库——索引

检查索引

show index from 表名;
show keys from 表名;

MySQL数据库——索引

各字段意义

MySQL数据库——索引

字段名 意义
Table 表的称号
Non_unique 如果索引不能包含重复词，则为0；如果能够，则为1
Key_name 索引的称号
Seq_in_index 索引中的列序号，从1开端
Column_name 列称号
Collation 列以什么办法存储在索引中。在 MysQL 中，有值’A’（升序）或 NULL（无分类）
Cardinality 索引中仅有值数目的估计值
Sub_part 如果列仅仅被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL
Packed 指示关键字怎么被紧缩。如果没有被紧缩，则为NULL
Null 如果列含有NULL，则含有 YES。如果没有，则该列含有NO
Index_type 用过的索引办法（BTREE，FULLTEXT，HASH，RTREE）
Comment 备注

删去索引

  • 办法一：drop index 索引名 on 表名;

    MySQL数据库——索引

  • 办法二：alter table 表名 drop index "索引名";

    MySQL数据库——索引

删去主键索引

alter table 表名 drop primary key;

MySQL数据库——索引

explain指令分析

在select指令检查前加上explain，能够检查运用的索引。

MySQL数据库——索引
MySQL数据库——索引

explain的相关参数

相关参数
possible_keys 显现或许应用在这张表中的索引。
key 实际运用的索引。如果为NULL，则没有运用索引。
key_len 运用的索引的长度。在不丢失精确性的情况下，长度越短越好。
ref 显现索引的哪一列被运用了，如果或许，则是一个常数。
Extra 关于MySQL怎么解析查询的额外信息。

总结

索引的效果

  • 加速对大表或多表查询速度，对字段排序
  • 下降IO本钱，排序本钱
  • 仅有性索引还能保证字段值的仅有性
  • 加速多表衔接、分组、排序、恢复数据库的速度

索引怎么作业的

  • 没有索引时，每次查询都需求先全表扫描来定位某行数据，查询速度慢，IO资源耗费较大
  • 有索引时，会先进行索引查询，然后经过索引值的数据对应的物理地址定位到详细的数据行，从而加速查询速

创立索引时的主张

  • 行数较多的情况下，比如主张在超越300行的表的字段上创立索引
  • 主张在主键、外键字段，表衔接字段，分组(group by)、排序(order by)、 where 常运用的字段，仅有性较好的字段上创立索引不主张在仅有性差的、会频频更新的、大文本的字段上创立索引