mysq索引的基础和类型
概念、理解:
MySQL官方对索引的定义:索引(index)是帮助mysql高效获取数据的数据结构。
所以索引的本质是一种数据结构。
索引类似于书籍的目录,要想找到一本书的某个特定主题,需要先查找书的目录,定位对应的页码。
存储引擎使用类似的方式进行数据查询,先去索引当中找到对应的值,然后根据匹配的索引找到对应的数据行。
数据库系统在存储数据之外,还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
数据写入到mysql的时候,每行数据按照过来的先后顺序,顺序写入到磁盘。如果没有任何索引,我们查找某个值的时候,需要遍历整个磁盘进行查找,对于那些经常用于查找的列,我们可以为其创建索引,例如简单的二叉树。二叉树的节点的值是该列的值,同时有一个指针指向该值对应的磁盘的位置,通过二叉树很快定位到要查找的值,获取到磁盘上的对应的行的位置,从而很快取出这一行,比遍历整个磁盘要快得多。
类型:
- 普通索引:最基本的索引,没有任何约束限制,索引列的值可重复。
- 唯一索引:与普通索引类似,但是具有唯一性约束,索引列的值不可重复。
- 主键索引:特殊的唯一索引,不允许有空值。
- 组合索引、联合索引、复合索引:将多个列组合在一起创建索引,可以覆盖多个列(例如:要查询第一章第1小节,章可以作为一个索引,节也可以作为一个索引,将两个组合起来作为组合索引,查询更快)。
- 外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性,完整性和实现级联操作。(基本不会使用,一般通过业务逻辑实现)
- 全文索引:mysq自带的全文索引只能用于MyISAM,并且只能对英文进行全文索引。(基本不会使用,对引擎、索引内容限制太多)但从5.6版本之后InnoDB存储引擎也开始支持全文索引了,5.7版本之后通过使用ngram插件开始支持中文的全文索引。之前仅支持英文是因为需要通过空格作为分词的分隔符,对于中文来说不合适。mysql允许在char、varchar、text类型上建立全文索引。
主键索引、唯一索引区别:
- 一个表只能有一个主键索引,可以有多个唯一索引。
- 主键索引一定是唯一索引,但唯一索引不是主键索引。
- 主键可以与外键构成参照完整性约束,防止数据不一致。
mysql索引的创建原则
- where子句中的列或连接子句中的列,适合创建索引。
- 索引列的基数越大,索引的效果越好。
- 对字符串进行索引时,应该制定一个前缀长度(就是指字符串的前几个字符),可以节省大量的索引空间。
- 根据情况创建复合索引,复合索引可以提高查询效率。
- 避免创建过多索引,索引会额外占用磁盘空间,降低写效率。
- 主键尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高查询效率。
- 在经常需要根据范围搜索的列上适合创建索引,因为索引本身已经排序,范围查找更快。
- 在经常需要排序的列上适合创建索引,因为索引本身已经排序,利用索引进行排序的话更快。