索引概念
索引的概念如同书籍中的目录,是为了提高数据的读写效率,在MySQL中,索引是在存储引擎层面实现的。索引之所以可以加快读写速度是因为数据模型的优秀,常见的用于索引的数据模型有三种:哈希表、有序数组、搜索树。
哈希表将数据按照K-V的模式存储,整体是无序的,所以只适用于等值查找,无法进行MySQL中常见的区域查找,常用于Memcached等存储引擎,而有序数组查找十分方便,无论是对于等值还是区域查找,但是在进行数据插入、删除时涉及到移动大量数据,所以只适用于静态不怎么更新的数据;而对于二叉搜索树,由于平衡状态下的高度为O(logN),看似读写效率都达到了要求,但是在实际应用中,由于数据库的索引需要存储在磁盘中,二叉搜索树的不同数据块随机存储在磁盘的不同地方,查找的过程涉及到多次对磁盘进行读取,大大降低了查找效率,为了降低对磁盘的访问次数,N叉树最终被应用于InnoDB的索引实现。
在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型,所以数据都存储在B+树中。
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
非主键索引在查找时,先找到主键的位置,再根据主键去主键索引中查找真正的数据内容,这个过程多一次查找,所以建议尽量使用主键索引。
在进行数据的插入或者删除时,可能会出现索引数据页的合并与分裂,如果按照主键自增ID添加数据,则每次插入数据都在数据页的末尾,比采用自定义的主键插入到数据页中间涉及到的移动会少很多,所以建议尽量采用自增ID作为主键,此外用整数作为主键索引比用字符串要节省空间,主键长度越少,普通索引的叶子结点所占用的空间越少。不过,如果你只有一个主键索引,也可以采用非自增ID的方式(没有普通索引也就无从谈起占用空间的问题啦)
索引覆盖
在使用非主键索引进行数据查询时如果需要回到主键索引进行检查,这一过程就被称为回表,而如果查询的内容被非主键索引直接覆盖,则将该索引称为覆盖索引,我们可以看出,覆盖索引的查询效率要更高。
最左前缀
对于联合索引,查询时满足最左前缀,这可以分两种情况考虑:一种可以是联合索引的最左N个字段,另一种是字符串索引的最左M个字符。所以,在建立联合索引的时候,如何安排索引内的字段顺序就显得尤为重要。我们采用的安排原则是:如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。也就是说,如果有(a,b)
的联合索引,就不必在a
上单独建立索引了。
索引下推
索引下推优化(index condition pushdown)是指,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。(MySQL5.6之前没有~只能一行一行回表扫描)
字符串索引优化
1.直接创建完整索引,这样可能比较占用空间;
2.创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
3.倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
4.创建hash字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。
参考文献
MySQL实战45讲