一、索引简介

1.1 索引的优点

为什么要创建索引?这是因为,创建索引可以大大提高系统的查询性能。

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

1.2 索引的缺点

也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。 这是因为,增加索引也有许多不利的一个方面:

  1. 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  2. 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物 理空间。如果要建立聚簇索引,那么需要的空间就会更大。
  3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降 低了数据的维护速度。

1.3 什么样的字段适合创建索引

索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。 一般来说,应该在具备下述特性的列上创建索引:

  1. 在经常需要搜索的列上,可以加快搜索的速度;
  2. 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
  3. 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
  4. 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
  5. 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的 排序,加快排序查询时间;
  6. 在经常使用在 WHERE 子句中的列上面创建索引,加快条件的判断速度。

建立索引,一般按照 select 的 where 条件来建立,比如: select 的条件是 where f1 and f2,那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的,只有在字段 f1 和 f2 上同时建立索引才有用等。

1.4 什么样的字段不适合创建索引:

同样,对于有些列不应该创建索引。一般来说,不应该创建索引的这些列具有下述特点:

  1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反 而降低了系统的维护速度和增大了空间需求。
  2. 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例, 即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
  3. 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。
  4. 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

二、索引管理

2.1 MySQL 中的索引管理

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

2.1.1 普通索引

这是最基本的索引,它没有任何限制 MyIASM 中默认的 BTREE 类型的索引,也是我们大多数情况下用到的索引。

1.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)));

1.1.2 查看索引

SHOW INDEX FROM [table_name];

只在 MySQL 中可以使用 keys 关键字。

SHOW KEYS FROM [table_name];

1.1.3 删除索引

DROP INDEXindex_nameON talbe_name
ALTER TABLEtable_nameDROP INDEX index_name
ALTER TABLEtable_nameDROP PRIMARY KEY

2.1.2 唯一索引

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

1.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)));

2.1.3 全文索引(FULLTEXT)

MySQL 从 3.23.23 版开始支持全文索引和全文检索,FULLTEXT 索引仅可用于 MyISAM ;他们可以从 CHAR**、**VARCHAR 或****TEXT 中作为 CREATE TABLE 语句的一部分被创建,或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。

对于较大的数据集,将你的资料输入一个没有 FULLTEXT 索引的表中,然后创建索引, 其速度比把资料输入现有 FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

1.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));

2.1.4 组合索引(最左前缀)

CREATE TABLE article(id int not null, title varchar(255), time date);

平时用的 SQL 查询语句一般都有比较多的限制条件,所以为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。例如上表中针对 title 和 time 建立一个组合索引:ALTERTABLE 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;

1.4.1 创建索引

CREATE INDEX index_name ON table_name (column_list);

三、索引5种优化

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

1.1 索引不会包含有NULL 值的列

只要列中包含有 NULL 值都将不会被包含在索引中,组合索引中只要有一列含有 NULL 值,那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为 NULL。create table table_name(c1 varchar(32) default ‘0’);

1.2 使用短索引

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

CREATE INDEX index_name ON table_name (column(length));

1.3 索引列排序

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

1.4 like 语句操作

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

不会使用索引,而 like “aaa%”可以使用索引。

1.5 不要在列上进行运算

例如:select * from users where YEAR(adddate)<2007;,将在每个行上进行运算,这将导致 索 引 失 效 而 进 行 全 表 扫 描 , 因 此 我 们 可 以 改 成 : select * from users where adddate<’2007-01-01′;

1.6 索引总结

最后总结一下,MySQL 只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的 like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建 16 个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的。

建议:一个表的索引数最好不要超过 6 个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。


标题:(12)MySQL索引简介、索引管理、索引优化——MySQL学习笔记
作者:AlgerFan
地址:https://www.algerfan.cn/articles/2019/09/09/1568033865440.html
版权声明:本文为博主原创文章,转载请附上博文链接!

添加新评论