第七章 创建⾼性能的索引

简介: 第七章 创建⾼性能的索引

索引基础

索引可以包含⼀列或多列,顺序也⼗分重要,因为MySQL只能使⽤索引的最左前缀列。

索引的类型


BTree索引,⼀般使⽤B-tree索引,MySQL使⽤B+Tree,

B-Tree所有值都是按顺序排序的,B-tree是按照索引列中的数据⼤⼩顺序存储的,所以很适合按照范围来查询。


⾃适应hash索引,具有hash的优势,由MySQL⾃动⽣成


全值匹配

匹配索引的所有列.

匹配最左前缀

只使⽤索引的第⼀列。

匹配列前缀

只匹配某⼀列的开头部分。

匹配范围值

关于B-tree的限制

不是从最左列索引开始查找,不能使⽤索引。

不能跳过索引的列

如果某索引列中有范围查找,那么右边的索引列都不能

使⽤去优化查找


全⽂索引

是⼀种特殊的索引类型,适⽤于match against,⽽不是普通的where条件操作。

使⽤索引的优点

加速查找的过程,减少扫描⾏数,避免排序和临时表,将随机io转换成顺序io。

⾼性能索引的策略

前缀索引和索引的选择性

保证⼀定选择性的前缀索引,但是不能太⻓。

创建前缀索引 :

多列索引

最常⻅的问题就是为每⼀列都创建单独的索引。

使⽤or或者union时,优化器会使⽤多个索引的联合,索引合并说明了索引建⽴得很糟糕,需要耗费⼤量cpu资源,并且优化器会忽略这些查询成本,有时还不如全表扫描。

alter table t1 add key (

city(7))explain看到索引合并,检查语法和表结构。或者关闭索引功能,使⽤ignore index语法忽略索引。

选择合适的索引列顺序

经典法则:将选择性最⾼的索引放在符合索引的最前⾯。(不是全部都适合)

聚簇索引

聚簇表示数据⾏和索引的键值紧凑的存储在⼀起。⼀般根据主键聚簇数据,没有主键innodb选择⼀个⾮空的键,如果没有⾮空键就创建⼀个rowid,但是会导致锁竞争。

聚簇索引的优点:减少io次数,数据访问更快,更新聚簇索引的代价很⾼,会将⾏移动到新的位置。

聚簇索引可能导致全表扫⾯变慢,尤其是⻚⽐较稀疏的情况下。

⼆级索引需要两次查找。

使⽤⾃增主键做聚簇索引主键较好,使⽤uuid,由于uuid是随机的会导致插⼊时性能差。

使⽤uuid的⼀些缺点:

a. 频繁做⻚分裂

b. 造成⼤量随机io

c. 频繁⻚分裂导致⻚间隙,内存碎⽚。

最好插⼊后做⼀次optimize table 调整⼀下⻚。

什么时候按主键顺序插⼊性能较差?

在并发插⼊的情况下,可能间隙锁的竞争⼤。

覆盖索引

索引的叶⼦节点已经包含要查询的数据,就称为索引覆盖。

只有当索引的顺序和order by的顺序完全⼀致才能使⽤索引对结果排序。即使不满⾜索引的最左前缀要求,如果指定了最左的索引列为常数,也可以使⽤索引进⾏排序。

冗余和重复索引

删除冗余的索引,使⽤performance和sys数据库,查看

sys.schema_unused_indexs查看未使⽤的索引。

维护索引和表

由于⼀些硬件错误导致索引出现问题,可以使⽤check table来检查表的问题。

相关文章
|
4月前
|
机器学习/深度学习 存储 算法
【初阶数据结构篇】时间(空间)复杂度
复杂度是计算机科学中的一个基础概念,它帮助我们理解和评估算法的效率,对于算法设计和优化至关重要。
48 2
【初阶数据结构篇】时间(空间)复杂度
|
6月前
|
存储 SQL 缓存
第八章,索引的创建与设计原则(3)
第八章,索引的创建与设计原则
29 0
|
6月前
|
存储 关系型数据库 MySQL
第八章,索引的创建与设计原则(2)
第八章,索引的创建与设计原则
32 0
|
7月前
|
存储 人工智能 程序员
【重学C++】【内存】关于C++内存分区,你可能忽视的那些细节
【重学C++】【内存】关于C++内存分区,你可能忽视的那些细节
183 1
|
7月前
|
存储 关系型数据库 MySQL
mysql索引优化,更好的创建和使用索引
mysql索引优化,更好的创建和使用索引
数据结构上机实践第七周项目1 - 自建算法库——顺序环形队列
数据结构上机实践第七周项目1 - 自建算法库——顺序环形队列
158 0
数据结构上机实践第七周项目1 - 自建算法库——顺序环形队列
|
存储 关系型数据库 索引
第七章《索引》
第七章《索引》
第七章《索引》
|
SQL 存储 缓存
索引不是越多越好,理解索引结构原理,才有助于我们建立合适的索引!
MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引。
662 0
|
存储 数据采集 算法
深究索引:Mysql索引模型及其不同结构优劣势
深究索引:Mysql索引模型及其不同结构优劣势
167 0
深究索引:Mysql索引模型及其不同结构优劣势
|
存储 缓存 算法
数据结构和算法之数组(难度级别:初级)
数据结构和算法之数组(难度级别:初级)
148 0
数据结构和算法之数组(难度级别:初级)