MySQL索引连环问

MySQL索引连环问

什么是索引？

• 索引类似于书本的目录，是存储引擎用于提高数据库表的访问速度的一种数据结构。
• 数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。
• 有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。

MySQL的索引都有哪些类型？

• BTREE索引。MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

• 哈希索引。哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

• 全文索引（FULLTEXT），目前只有MyISAM引擎支持。只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。

• RTREE索引。RTREE在MySQL很少使用，仅支持geometry数据类型。相对于BTREE，RTREE的优势在于范围查找。

哈希索引和BTREE索引有什么区别呢？

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

什么是最左匹配原则？

• 如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

什么是覆盖索引吗？

• select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。
• 不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以只有B+树索引可以用作覆盖索引。

什么是前缀索引？

• 前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
• 有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
• 创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。
• 创建前缀索引的语法如下：ALTER TABLE table_name ADD KEY(column_name(prefix_length))

索引的设计原则？

• 使用区分度高的字段作为索引列，索引的效果更好。如果使用性别这种区分度很低的列作为索引，效果就会很差。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
• 利用最左前缀原则。

索引什么情况下会失效吗？

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
• 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
• 判断索引列是否不等于某个值时
• 对索引列进行运算
• 查询条件使用or连接，也会导致索引失效