MySQL数据库——索引的设计与使用

一、索引的创建于删除

索引是数据库中用来提高性能最常用的工具,本文将简单介绍索引的设计原则。

所有MySQL列类型都可以被索引,对相关列使用索引是提高select查询语句的性能的最佳途径。不同的存储引擎可定义不同的最大索引数量和最大索引长度,每种存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。

MyISAM和InnoDB存储引擎的表默认都是创建BTREE索引,而MEMEORY存储引擎使用HASH索引,但也支持BTREE索引。MySQL目前还不支持函数索引,但是支持前缀索引(即对索引的前N个字符创建索引,前缀的长度和存储引擎相关)。

1.1 创建索引

create [unique|fulltext|spatial] index index_name [using index_type] on table_name (index_col_name......)

例如我们为city表(结构如下所示)创建10个字节的前缀索引,代码如下:
city表内容

create index cityname on city (city(10));  --为city创建10个字节的前缀索引

当我们使用查询语句中涉及where条件包含city列时就会用到我们的cityname索引。
索引的使用
1.2 删除索引

drop index index_name on table_name;

删除索引

二、设计索引的原则

索引的设计需要遵循一些原则,这样可以有效的提升索引的使用效率,更高效的使用索引。

2.1 搜索的索引,不一定是要使用的列
最适合索引的列是出现在WHERE字句中的列,或连接子句中指定的列,而非出现在select关键字后面的表中的列。

2.2 使用唯一索引
索引的列的基数越大,索引的效果越好,也即是我们的索引要尽可能的将表中的记录区分开。

2.3 使用短索引
索引同样是一张表,也会占用空间的,如果使用较段的前缀就能区分开不同的记录,那么我们就不需要在索引中使用多余的字符,同时索引越短,相同的磁盘空间就能存储更多的索引数量。

2.4 利用最左前缀
在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引。多列索引可以起到几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

2.5 不要过度索引
索引同样是一张表,每个额外的索引都会占用额外的磁盘空间,并降低写操作的性能。在修改表的时候,索引必须进行更新,有时可能需要重构,因此索引越多,所花费的时间越长。

2.6 InnoDB尽量自己指定主键
InnoDB存储引擎的表,记录会按照一定的顺序存储,如果有明确定义的主键,则按主键顺序保存。若没有主键,但是有唯一索引,那么就按唯一索引的顺序保存,否则,表中会自动生成一个内部列,按照这个内部列的顺序保存。我们在指定主键时,如果表中有多个列是唯一的,要选择最常作为访问条件的列作为主键。同时InnoDB表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,减少索引的磁盘占用,提高索引的缓存效果。

三、BTREE索引于HASH索引

HASH索引的重要特征:

  1. 只能使用 “=”或者“<=>”操作符的等式比较 。
  2. 优化器不能使用hash索引来加锁order by 操作。
  3. MySQL不能确定在两个值之间大约有多少行。
  4. 只能使用整个关键字来搜索一行。

而对于BTREE索引,当使用> < >= <= BETWEEN != ,或者like 'pattern’操作符时,都可以使用相关列上的索引。

我们还是以city表为例,创建一个结构相同的city_memory表,存储引擎使用Memory,再将city表的中数据完全插入到新表中。
city_memory表
当我们对这两个表使用相同的范围查询语句查询时,可以看到BTREE索引可以通过索引进行访问,而HASH索引实际上是全表扫描。
BTREE索引
HASH索引

总结

索引的优点:
提高数据检索效率,降低数据库的IO成本;通过索引列对数据进行排序,降低了数据排序成本,降低了CPU的消耗。

索引的缺点:
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是占用空间的;对表进行INSERT、UPDATE、DELETE操作时,MYSQL不仅会更新数据,还要保存一下索引文件每次更新添加了索引列字段的相应信息

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读