【高性能Mysql 】读书笔记（第五章）

wuxiren
376 阅读7分钟

索引：是存储引擎快速定位记录的一种数据结构，类比800页书的目录

5.1 索引类型

5.1.1  B-TREE：最常见的索引，如果在讨论索引的时候没有指定类型时指定默认为b-tree，大多数mysql引擎都支持，Archive例外。

+ 树的深与表的数据量有关
+ B-TREE适用于全键值、键值范围、键前缀查找（最左匹配原则）

限制：
1.如果不是按照索引的最左列开始查找，则无法适用索引。
2.不能跳过索引中的列
3.如果查询中有对列的范围查询，则其右边所有的列都无法使用索引优化查找（如果范围查询的列不多，可以
通过使用多个等于条件来代替范围查询条件）

5.1.2  哈希索引：基于哈希表实现，只有精确定位所有列的查询才能定位数据，对于每一行的记录都会生产一个哈希码，哈希索引将所有计算出来的哈希码存储在索引中，同时在哈希表中保存指向每条记录的指针（彩虹表）。只有Memory引擎支持哈希索引。适用于数据仓库的经典"星型"schema

1.哈希索引只存储哈希码和行指针，不存储字段值
2.无法排序
3.无法范围查找
4.不支持部分部分索引匹配查找
5.存在哈希冲突问题。如果出现哈希冲突时，存储引擎会遍历所有的数据查询

InnoDB有一个特殊功能：自适应哈希，当引擎注意到某些索引值被引用很频繁时，他会在内存中基于B-TREE索引上再创建一个哈希索引，提高查询效率，用户可选择开闭。


在存储大量URL这类比较长的数据时，可以考虑使用CRC32（10位数）这类哈希函数对URL进行处理再存储，节省磁盘空间，间接达到减少磁盘页的存在，同时减少了索引层级。注意不要使用MD5这类高要求哈希函数，因为得出的哈希码会更长，与初衷相违背。

当数据量较大时，很容易出现哈希冲突，只根据哈希函数并不能满足，如下。

select * from words where crc = CRC32('www.baidu.com') and word = 'www.baidu.com'


5.1.3  空间数据索引

MyISAM引擎支持空间数据索引，可以用做地理数据存储。与B-TREE索引不同，查询时不需要前缀查询，可以使用任意维度来组合查询。必须使用Mysql的GIS函数来维护数据。


5.1.3   全文索引

全文索引是一种特殊的索引，他查找的是文本的关键字，而不是比较索引的值。


5.2  索引的优点

  • 1.大大的减少了服务器扫描数据量
  • 2.帮助服务器避免排序和临时表
  • 3.将随机IO变成顺序IO


注意：只有当数据量到达一定的量时使用索引才有收益，，对于小的表，全表扫描才是简单快速的办法


5.3  高性能的索引策略


5.3.1  独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数


5.3.2  前缀索引和索引的选择性

有的时候需要索引很长的字符列，这会让索引很慢且大有两点可以解决

  • 第一是哈希处理
  • 可以索引开始的部分字符

缺点：

  • mysql无法使用前缀索引做order by 和group by操作
  • 不能做覆盖扫描


5.3.3  多列索引

如果用explain看到有索引合并情况，这时需要自检了

  • 当出现对多个列进行and、or操作时，需要耗费大量的cpu和内存资源来执行，而且优化器会把这样的时间不算到查询成本上，导致对执行计划的计算上被低估，甚至不如全表扫描来得快


5.3.4  选择合适的索引顺序

将选择性最高的列放在最前面。

select * from table where a=1 and b=1 

a的数据量少放前面，辨识度高。


5.3.5  聚簇索引

这是一种索引存储方式，而不是一种索引类型，即在同一个结构中保存了B-Tree索引和数据行。

  • 一个表只能有一个聚簇索引
  • 存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引
  • 最大限度的提高了IO密集型英勇的性能，但如果数据都放在内存中，那么访问的顺序就无关紧要了，聚簇索引也没有优势
  • 插入速度验证依赖于插入顺序
  • 更新聚簇索引的列的代价很大，因为会强制InnoDB将每个被更新的行移动到新的位置
  • 基于聚簇索引的表插入新行，或者主键更新需要导致移动行的时候，可能面临页分裂“”的问题
  • 聚簇索引可能会导致全表扫描变慢，特别是航比较稀疏、或者由于页分裂导致数据存储不连续
  • 二级索引会更大，因为二级索引的叶子节点包含了引用行的主键列
  • 二级索引需要两次索引查找（回表）


顺序主键在什么情况下会出现问题：在高并发情况下，在Innodb中按主键顺序插入可能会造成明显的争用，主键的上界会成为热点。并大插入并发插入会导致间隙锁的竞争。另一个热点竞争是AUTO_CREATEMENT锁机制。


5.3.4  覆盖索引

大家通常会根据where条件后的列进行索引设计，只是索引优化考虑的一部分，但是考虑全局索引查询时，需要考虑是不是可以通过索引直接查询出数据，这样就不需要回表操作。

  • 不是所有的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，索引Mysql只能使用B-TREE索引做覆盖索引


5.3.7  使用索引扫描来做排序

mysql有两种途径排序

1.通过排序操作

2.通过索引顺序扫描

只有当索引的列顺序和order by子句顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引做排序（排序的多列要排序方向也一致）


5.3.8  压缩索引（MyISAM）

MyISAM使用前缀压缩来减少索引大小，从而让索引占磁盘空间更小。默认只压缩字符串，通过参数设置也可以压整数。压缩索引大小可以使占用空间更小，但是会使得某些操作更慢。因为每个值的压缩前缀都依赖于前面的值，所以MyISAM查找时无法在索引使用二分查找，只能从头开始扫描。正序还算尚可，倒序时很差。


5.3.9  冗余和重复索引

mysql允许在相同列上建立多个索引，需要单独维护重复的索引，并且优化器在优化查询时也要逐个参考，会影响性能


5.4  索引案例学习


  • 支持多种过滤条件
  • 避免多个范围查询
  • 优化排序


5.5  维护索引和表

1.找到并修复损坏的表

2.更新索引统计信息：mysql的查询优化器会通过两个api来了解存储引擎的索引值的分布信息，以决定如何使用索引。

  • records_in_range()通过向存储引擎传入两个边界值，获取在这个范围大概有多少条记录。
  • info()，该接口返回各种类型的数据，包括索引的基数

3.减少索引和数据的碎片：B-Tree索引可能会碎片化，这将会降低查询速度。

  • 行碎片化
  • 行间碎片化：指逻辑上顺序的页，或者行在磁盘上不是顺序存储的，行间碎片对诸如全表扫描和聚簇索引扫描之类操作有很大影响，
  • 剩余空间碎片：指数据页有大量的空间剩余，这会导致innodb不会出现短小的行碎片，innodb会移动缩小短小的行并重写到一个碎片中


avatar
文章
阅读
粉丝