数据库索引学习笔记
数据库索引优化
是后台开发必备的技能,本文记录下学习数据库索引的一些心得。
MyISAM和InnoDB的区别
MyISAM
适合读多写少的场景,不支持事务。使用的全局锁,写性能差。
InnoDB
适合表更新和写入频繁的场景。支持事务。使用行锁,性能比MyISAM
要好
索引的类型
通常我们提到索引,一般指的就是B-Tree索引
,但其实除了B-Tree索引
,还存在其它的数据库索引,下面列举几种:
-
B-Tree索引
-- 大部分数据库都只用的B-Tree索引 -
哈希索引
-- 查询效率高。不支持范围查询和排序 -
空间数据索引
(R-Tree)-- 地理数据存储场景 -
全文索引
-- 搜索引擎中常用的倒排索引,在MySQL
中用来检索text类型
的长文本字段
使用索引时要注意哪些问题
- 索引能提高读取速度,但会导致写入速度降低
- 适合写少读多的场景,数据量级在百万级
- 会占用大量空间。数据库索引中存的是列值,因此不要在列值比较大的列上建索引
主键上的索引与其它列索引的区别
在MySQL
中,通常主键上的索引就是聚簇索引
。聚簇索引
的特点是索引值和表数据在同一个索引树中,因此通过聚簇索引
的查询效率很高,找到索引意味着就找到了数据。
其它列的索引也叫二级索引
。二级索引
的特点是通过索引值找到的数据行只是一个行的主键值,需要通过这个主键值到聚簇索引
上才能找到完整的行数据。下图是通过二级索引
找到行数据的过程。
多字段联合索引
的实现?
联合索引
包含了多个字段的值,根据多个字段值进行排序。联合索引
的查找遵循最左前缀匹配的原则,因为索引列的顺序是首先按照最左列进行排序,其次是第二列,等等。下图是联合索引
的一个例子:
创建一个表People,并建立一个联合索引
(last_name, first_name, dob)
联合索引
优先使用第一个列last_name排序,然后是第二个列first_name,最后是dob
什么情况下需要建联合索引
?
如果一个查询涉及到了多个字段,那么通常会基于这多个字段建立联合索引
。通常有这么几种场景。
- WHERE条件中涉及到多个字段
- WHERE和ORDER BY涉及到多个字段。利用
联合索引
的索引本身有序性来提高排序的效率 - WHERE和SELECT涉及到多个字段。在
联合索引
中加入要SELECT的字段,以建立覆盖索引,减少二次检索,提高检索速度
如何决定联合索引
的列顺序?
一个经验法则是:通常把选择性最高的列放在前面(可以优先筛选掉更多的行)
什么是选择性高的列?distinct(列)/行数 这个占比越高,选择性就越高。举个反例,性别是个选择性很低的列,因为distinct(性别)就只有两个值,男和女。指定条件"男"去搜索能选出一半的行。
哪些情况不适合建索引?
- 选择性低的列。比如性别,只有男和女两个值
- 值很大的列。比如text,image,bit等数据类型,这种类型的值通常很大,几KB到几M不等,直接建出来的索引占用空间很大,查询效率也很低。虽然无法在这些列上直接创建索引,但是有其它的办法来解决:前缀索引 or 全文索引(只适合文本类型)
- 写多读少的场景。通常的B-Tree索引是为了提高读速度,同时也会降低写速度。如果业务需求是写多读少,更加注重写的速度,那么就少建索引。
数据库怎么决定要不要使用索引?
按照我们的常识,如果列上有索引,那么数据库肯定就会用,不存在要不要用的问题。其实不然,数据库会根据查询的效率去判断要不要用索引。如果直接做全表轮询的效率比用索引的效率高,那么数据库就会选择做全表轮询。
举个例子,比如有张员工表,有Name和Age属性,总行数有1万行,其中Age大于20的有9000行。此时要查询Age大于10的所有员工姓名,如果用索引的话,就需要查询索引表9000次,再通过索引表中的指针去找到行取出Name字段。这个效率明显还不如直接轮询整张表。
提高索引性能的一些技巧
- 使用独立的列。 如果索引的列在表达式或者函数中,那么索引就不会生效。比如where age+1 > 10这个查询语句,就不会使用age列的索引
-
前缀索引
。对于长度很长的列,使用前缀索引
来提高索引效率。注意选出最佳的前缀长度来保证前缀的选择性。 - 多列索引中列的顺序。一般经验:将
选择性高
的列放在前面 - 利用
聚簇索引
的优点。1)相关数据保存在一起 2)数据访问快。索引和数据保存在同一个索引数 -
覆盖索引
。设计索引时,考虑将查询的列加入到索引中,节省掉二级索引的查询时间 - 利用索引加速排序。
B-Tree
索引本身是按顺序存储的,因此如果索引的列与要求排序的列是匹配的,那么就能利用索引本身的有序性直接返回排序结果