对面坐着仁爱的面试官
笑眯眯的问:小伙子索引是啥子
索引是什么
帮助 MySQL 高效获取数据的数据结构,也可以称为快速查找排序的一种数据结构。Mysql 索引主要有两种结构:B+Tree 索引和 Hash 索引。我们平常所说的索引,如果没有特别指明,一般都是指 B 树结构组织的索引 (B+Tree 索引)。
Mysql 索引为什么选择 B + 树
- mysql 主要是由 server 层和存储层两部分构成的。server 层主要包括连接器、查询缓存,分析器、优化器、执行器。存储层主要是用来存储和查询数据的,常用的存储引擎有 InnoDB、MyISAM,MySQL 5.5.5 版本后使用 InnoDB 作为默认存储引擎。
- 几种常见的数据类型哈希表适合等值查询,由于是无序的,区间查询会很慢有序数组适合等值和区间查询,但是数组具有连续性,插入和删除操作都可能需要移动其他元素。二叉搜索树由于树的高度,区间查询需要中序遍历,都会导致查询效率很慢。 B+ 树就是通过二叉搜索树推演改进的。
- B+ 树就是一种多叉树,是由二叉搜索树不断演变过来的,为了满足区间快速查询,B+ 树的叶子节点通过双向链表串联起来。
- 这里使用双向链表是为了支持顺序和倒序查询,虽然双向链表相对于单向链表虽然会浪费一倍的指针空间,但是在硬盘中这点空间几乎微乎其微,用这点空间换时间是一件很值得的事情。
- B+ 树的子节点数不超过 m 个,同时也不能少于 m/2 个,一旦超过就需要分裂,一旦少于就需要合并。
- 而 InnoDB 在底层是采用 B+ 树这种数据结构来存储数据的。
Mysql 为什么加索引就快
不加索引,会比较整个数据库,因为他不知道数据是不是规律的。添加了索引,相当于加了一个目录,给索引字段排序,比较的时候只用几次就可以查找到你需要的数据。数据越多,索引越有用。也可以说拿空间换时间。
索引对性能的影响:
- 大大减少服务器需要扫描的数据量。
- 帮助服务器避免排序和临时表。将随机 I/O 变顺序 I/O。
- 大大提高查询速度,降低写的速度、占用磁盘。
索引的使用场景:
- 对非常小的表,大部分情况下全表扫描效率更高。
- 对中大型表,索引非常有效
- 特大型的表,建立和使用索引的代价随着增长,可以使用分区技术来解决。
索引的类型:
- 索引都是实现在存储引擎层的。
- 普通索引:最基本的索引,没有任何约束
- 唯一索引:与普通索引类似,但具有唯一性约束
- 主键索引:特殊的唯一索引,不允许有空值
- 组合索引:将多个列组合在一起创建索引,可以覆盖多个列
- 外键索引:只有 InnoDB 类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作
- 全文索引:MySQL 自带的全文索引只能用于 MyISAM,并且只能对英文进行全文检索,一般使用全文索引引擎
主键索引和唯一索引的区别
- 一个表只能有一个主键索引,可以有多个唯一索引
- 主键索引一定是唯一索引,唯一索引不是主键索引
- 主键和外键构成参照完整性约束,防止数据不一致
MySQL 索引的创建原则
- 索引并不是越多越好,要根据查询有针对性的创建,考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引,可根据 EXPLAIN 来查看是否用了索引还是全表扫描
- 应尽量避免在 WHERE 子句中对字段进行 NULL 值判断,否则将导致引擎放弃使用索引而进行全表扫描
- 使用多列索引必须满足最左匹配,注意顺序和查询条件保持一致,同时删除不必要的单列索引
- OR 关键字的两个字段必须都是用了索引,该查询才会使用索引.
- LIKE 关键字匹配 ‘%’ 开头的字符串,不会使用索引.
- 值分布很稀少的字段不适合建索引,例如 “性别” 这种只有两三个值的字段
- 字符字段只建前缀索引,最好不要做主键
- 不用外键,由程序保证约束
- 尽量不用 UNIQUE,由程序保证约束;
选择合适的索引列
- 查询频繁的列,在 where,group by,order by,on 从句中出现的列
- where 条件中 <,<=,=,>,>=,between,in,以及 like 字符串 + 通配符(%)出现的列
- 长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好
- 离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count 越大,离散程度越高