MySQL创建高性能索引

mysq索引的基础和类型

概念、理解：

MySQL官方对索引的定义：索引（index）是帮助mysql高效获取数据的数据结构。

所以索引的本质是一种数据结构。

索引类似于书籍的目录，要想找到一本书的某个特定主题，需要先查找书的目录，定位对应的页码。

存储引擎使用类似的方式进行数据查询，先去索引当中找到对应的值，然后根据匹配的索引找到对应的数据行。

数据库系统在存储数据之外，还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

数据写入到mysql的时候，每行数据按照过来的先后顺序，顺序写入到磁盘。如果没有任何索引，我们查找某个值的时候，需要遍历整个磁盘进行查找，对于那些经常用于查找的列，我们可以为其创建索引，例如简单的二叉树。二叉树的节点的值是该列的值，同时有一个指针指向该值对应的磁盘的位置，通过二叉树很快定位到要查找的值，获取到磁盘上的对应的行的位置，从而很快取出这一行，比遍历整个磁盘要快得多。

类型：

• 普通索引：最基本的索引，没有任何约束限制，索引列的值可重复。
• 唯一索引：与普通索引类似，但是具有唯一性约束，索引列的值不可重复。
• 主键索引：特殊的唯一索引，不允许有空值。
• 组合索引、联合索引、复合索引：将多个列组合在一起创建索引，可以覆盖多个列（例如：要查询第一章第1小节，章可以作为一个索引，节也可以作为一个索引，将两个组合起来作为组合索引，查询更快）。
• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性，完整性和实现级联操作。（基本不会使用，一般通过业务逻辑实现）
• 全文索引：mysq自带的全文索引只能用于MyISAM，并且只能对英文进行全文索引。（基本不会使用，对引擎、索引内容限制太多）但从5.6版本之后InnoDB存储引擎也开始支持全文索引了，5.7版本之后通过使用ngram插件开始支持中文的全文索引。之前仅支持英文是因为需要通过空格作为分词的分隔符，对于中文来说不合适。mysql允许在char、varchar、text类型上建立全文索引。

 

主键索引、唯一索引区别：

• 一个表只能有一个主键索引，可以有多个唯一索引。
• 主键索引一定是唯一索引，但唯一索引不是主键索引。
• 主键可以与外键构成参照完整性约束，防止数据不一致。

 

mysql索引的创建原则

1. where子句中的列或连接子句中的列，适合创建索引。
2. 索引列的基数越大，索引的效果越好。
3. 对字符串进行索引时，应该制定一个前缀长度（就是指字符串的前几个字符），可以节省大量的索引空间。
4. 根据情况创建复合索引，复合索引可以提高查询效率。
5. 避免创建过多索引，索引会额外占用磁盘空间，降低写效率。
6. 主键尽可能选择较短的数据类型，可以有效的减少索引的磁盘占用，提高查询效率。
7. 在经常需要根据范围搜索的列上适合创建索引，因为索引本身已经排序，范围查找更快。
8. 在经常需要排序的列上适合创建索引，因为索引本身已经排序，利用索引进行排序的话更快。