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)