索引

MySQL 中的索引简介

4.1索引的优点 为什么要创建索引？

        这是因为，创建索引可以大大提高系统的查询性能。 第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。 第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的 时间。 第五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

4.2索引的缺点 也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索 引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点， 但是，为 表中的每一个列都增加索引，是非常不明智的。 这是因为，增加索引也有许多不利的一个 方面: 第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物 理空间。如果要建立聚簇索引，那么需要的空间就会更大。 第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降 低了数据的维护速度。

4.3什么样的字段适合创建索引 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑 在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在具备下述特性的 列上创建索引： 第一、在经常需要搜索的列上，可以加快搜索的速度； 第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范 围是连续的； 第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的 排序，加快排序查询时间； 第六、在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。 建立索引，一般按照 select 的 where 条件来建立，比如： select 的条件是 where f1 and f2，那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的，只有在字段 f1 和 f2 上同时 建立索引才有用等。

4.4什么样的字段不适合创建索引: 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下述特点： 第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些 列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反 而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值 很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例， 即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这 些列的数据量要么相当大，要么取值很少。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索 性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时， 会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5 MySQL 中的索引种类

5.1B-Tree 索引 B-Tree 索引，顾名思义，就是所有的索引节点都按照 balance tree 的数据结构来存储。 B-tree 结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。 B-tree 中，每个结点包含： 1、本结点所含关键字的个数； 2、指向父结点的指针； 3、关键字； 4、指向子结点的指针； 对于一棵 m 阶 B-tree，每个结点至多可以拥有 m 个子结点。各结点的关键字和可以拥 有的子结点数都有限制，规定 m 阶 B-tree 中，根结点至少有 2 个子结点，除非根结点为叶 子节点，相应的，根结点中关键字的个数为 1~m-1；非根结点至少有[m/2]（[]，向上取整） 个子结点，相应的，关键字个数为[m/2]-1~m-1。 B-tree 有以下特性： 1、关键字集合分布在整棵树中； 2、任何一个关键字出现且只出现在一个结点中； 3、搜索有可能在非叶子结点结束； 4、其搜索性能等价于在关键字全集内做一次二分查找； 5、自动层次控制； 由于限制了除根结点以外的非叶子结点，至少含有 M/2 个儿子，确保了结点的至少利 用率，其最低搜索性能为： 其中，M 为设定的非叶子结点最多子树个数，N 为关键字总数； 所以 B-树的性能总是等价于二分查找（与 M 值无关），也就没有 B 树平衡的问题； 由于 M/2 的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占 M/2 的 结点；删除结点时，需将两个不足 M/2 的兄弟结点合并。

5.2Full-text 索引 Full-text 索引就是我们常说的全文索引，他的存储结构也是 b-tree。主要是为了解决在 我们需要用 like 查询的低效问题。只能解决’xxx%’的 like 查询。如：字段数据为 ABCDE，索 引建立为- A、AB、ABC、ABCD、ABCDE 五个。

6 MySQL 中的索引管理 在 MySQL 中，对索引的查看和删除操作是所有索引类型通用的。

6.1普通索引 这是最基本的索引，它没有任何限制 MyIASM 中默认的 BTREE 类型的索引，也是我们大 多数情况下用到的索引。 6.1.1 创建索引 CREATE INDEX index_name ON table_name (column(length)) ALTER TABLE table_name ADD INDEX index_name (column(length)) CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))

6.1.2 查看索引 SHOW INDEX FROM [table_name] SHOW KEYS FROM [table_name] # 只在 MySQL 中可以使用 keys 关键字。

6.1.3 删除索引 DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY

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

6.2.1 创建索引 CREATE UNIQUE INDEX index_name ON table_name (column(length)) ALTER TABLE table_name ADD UNIQUE index_name (column(length)) CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))

6.3全文索引（FULLTEXT） MySQL 从 3.23.23 版开始支持全文索引和全文检索，FULLTEXT 索引仅可用于 MyISAM 表；他们可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或 是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。 对于较大的数据集，将你的资料输入一个没有 FULLTEXT 索引的表中，然后创建索引， 其速度比把资料输入现有 FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表，生 成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

6.3.1 创建索引 CREATE FULLTEXT INDEX index_name ON table_name(column(length)) ALTER TABLE table_name ADD FULLTEXT index_name( column) CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))

6.4组合索引（最左前缀） CREATE TABLE article(id int not null, title varchar(255), time date); 平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效 率，就要考虑建立组合索引。例如上表中针对 title 和 time 建立一个组合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分 别建立了下面两组组合索引： –title,time –title 为什么没有 time 这样的组合索引呢？这是因为 MySQL 组合索引“最左前缀”的结果。简 单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引， 如下面的几个 SQL 所示： 1，使用到上面的索引 SELECT * FROM article WHERE title='测试' AND time=1234567890; SELECT * FROM article WHERE title='测试'; 2，不使用上面的索引 SELECT * FROM article WHERE time=1234567890; 参考：https://segmentfault.com/a/1190000008131735#articleHeader5

6.4.1 创建索引 CREATE INDEX index_name ON table_name (column_list)