一、索引为什么快。
索引是排好序的快速的数据结构。常见的索引类型有哈希表、有序数组和搜索树。
1、哈希表。
哈希表适合于只有等值查询(用等号来匹配查询结果)的场景。
2、有序数组。
有序数组在等值查询和范围查询方面性能都很优秀,但是更新(插入删除)成本比较高,所以有序数组适合作为静态存储引擎(不在修改的数据,例如历史数据)。
3、搜索树。
搜索树是表存储结构。一张表里面有多个段,单位级别是表>段>区>页>行。存储空间的基本单位是页,也就是说读一行或者多行,都是加载行所在的页。一页就是一颗B+树的一个节点(每次新建节点会申请一个页空间),页也是数据库I/O操作的最小单位。B+树非叶子节点中,记录多个索引行,每个索引行存储索引键和指向下一层页面的指针。叶子节点存储了关键字和行记录,在节点内部(即页结构内部)之间维护着一个单向链表。同一层节点与节点之间(同一层页之间)维护了一个双向链表,这也是支持范围查询的原因。
二、聚簇索引与非聚簇索引。
1、聚簇索引。
将数据存储与索引放到一起,找到索引就找到了数据。
2、非聚簇索引。
将数据存储与索引分开的结构,索引结构的叶子节点指向了数据的对应行。myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,通过索引找到磁盘相应数据,这也是当索引不在key_buffer命中时查询速度慢的原因。
三、创建索引注意事项。
1、非空字段。
如果不想存储NULL,指定列为NOT NULL。在mysql中,含有空值的列很难进行查询优化,NULL会使得索引、索引的统计信息及比较运算更复杂。可以使用0、一个特殊值或者一个空串代替空值NULL。
2、取值离散大的字段。
变量各个取值差异程度大的列放在联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段离散程度越高。
3、索引字段越小越好。
数据库的存储数据以页为单位,一页存储的数据越多,一次I/O操作获取的数据越大效率越高。
四、索引优缺点。
1、优点。
大大加快数据检索速度(主要优点)。使用索引,在查询过程中,可以使用优化隐藏器,提高系统性能。
2、缺点。
时间方面,创建和维护索引需要耗费时间,比如对表中数据进行增删改要维护索引,会降低增删改效率。空间方面,索引需要占用物理空间。
五、索引类型。
1、单列索引。
索引只包含表中的一个列。
2、组合索引。
表中多列值组成一个索引,用于组合搜索,效率大于索引合并。组合索引遵循最左原则。
3、主键索引。
数据列不允许重复,不允许为NULL,一张表只能有一个主键。
4、唯一索引。
数据列不允许重复,可以为NULL,一张表可以有多个唯一索引。
5、普通索引。
基本索引类型,没有重复和NULL值限制。
6、全文索引。
是搜索引擎使用的一种关键技术。可以通过ALTER TABLE tablename ADD FULLTEXT(column);创建全文索引。
只有myisam引擎上才能使用,只能在char、varchar、text类型字段上使用全文索引。全文索引简单来说,就是在一堆文字中,通过某个关键字匹配到记录行,例如存储了“我是Showne,我今年18岁了。。。”,通过Showne可以找到这条记录。
7、空间索引。
是对空间数据类型的字段建立的索引,MySQL中空间数据类型有GEOMETRY、 POINT、 LINESTRING、 POLYGON四种。使用SPATIAL关键字创建空间索引,且索引列必须为NOT NULL。