MySQL中如何创建高性能的索引
2018-01-03| 程成| 966| 0| MySQL

一、索引介绍


索引(在 MySQL 中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。


索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,所以对性能的影响愈发重要。在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增大时,性能则会急剧下降。


不过,索引却经常被忽略,有时候甚至被误解,所以在实际案例中经常会遇到由糟糕索引导致的问题。


索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级,“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。



二、索引的优点


索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用,到目前为止可以看到,根据创建索引的结构不同,索引也有一些其他的附加作用。


最常见的 B-Tree 索引,按照顺序存储数据,所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的,所以 B-Tree 也就会将相关的列值都存储在一起。最后,因为索引中存储了实际的列值,所以某些查询只能使用索引就能够完成全部查询。据此特性,总结下来索引有如下三个优点:


  • 索引大大减少了服务器需要扫描的数据量。

  • 索引可以帮助服务器避免排序和临时表。

  • 索引可以将随机 I/O 变成顺序 I/O。


Lahdenmaki 和 Leach 介绍了如何评价一个索引是否适合某个查询的“三星系统”:索引将相关的记录放到一起则获得一星;如果索引中的数据顺序和查找中的排列顺序一致则获得二星;如果索引中的列包含了查询中需要的全部列则获得“三星”。



三、索引是最好的解决方案吗


索引并不总是最好的工具。总的来说,只有当索引帮助存储引擎快速查找到记录带来的好处大于其大奶的额外工作时,索引才是有效的。对于非常小的表,大部分情况下简单的全表扫描更高效。对于中到大型的表,索引就非常有效。但对于特大型的表,建立合适用索引的代价将随之增长。这种情况下,则需要一种技术可以直接区分出查询需要的一组数据,而不是一条记录一条记录地匹配。例如可以使用分区技术。


如果表的数据特别多,可以建立一个元数据信息表,用来查询需要用到的某些特性。例如执行那些需要聚合多个应用分布在多个表的数据的查询,则需要记录“哪个用户的信息存储在哪个表中”的元数据,这样在查询室就可以直接忽略那些不包含指定用户信息的表。对于大型系统,这是一个常用的技巧。事实上,Infobright 就是使用类似的实现。对于 TB 级别的数据,定位单条记录的意义不大,所以经常会使用块级别元数据技术来代替索引。



四、总结


在选择索引和编写利用这些索引的查询时,有如下三个原则始终需要记住:


  1. 单行访问时很慢的。特别是在机械硬盘存储中(SSD 的随机 I/O 要快很多,不过这一点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置索引用以提升效率。


  2. 按顺序访问范围数据是很快的,这有两个原因。第一,顺序 I/O 不需要多次磁盘寻道,所以比随机 I/O 要快很多(特别是对机械硬盘)。第二,如果服务器能够按需要顺序读取数据,那么就不再需要额外的排序操作,并且 GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。


  3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列,那么存储引擎就不需要再回表查找行。这避免了大量的单行访问,而上面的第 1 点已经写明单行访问是很慢的。


总的来说,编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的顺序排序,并尽可能使用索引覆盖查询。这与本章开头提到的 Lahdenmaki 和 Leach 的书中的“三星”评价系统是一致的。



×
作者:程成
QQ:492245711