mysql数据库物理查询优化之索引与表连接

物理查询优化是将逻辑查询的内容变成可以被执行的物理操作符,为后续执行器的执行提供准备。物理查询优化的核心是建立高效的索引,此外还可以借助表连接方式优化物理查询。

索引的用途
索引的常见用途
(1):提高常用查询语句的性能。

创建的索引,会将某些列以特定的数据结构(如 B-Tree)有序存储起来。维持一个这样的数据结构,在写数据的时候会带来一定的系统开销;但是,对于真正的高频查询而言,这种系统开销就很值得了。
因此,您在建表创建索引时,一方面需要考虑到所有可能的高频查询;另一方面,也要避免过度地“为未来设计”而加一堆可能根本不常用的索引,反而增加了写数据时候的成本和负担。
索引的常见用途
(2):保证某一列或者某几列的组合是唯一的,也即唯一性索引,在写业务逻辑代码的时候会常常用到。

索引的设计
1. 重复度高的数据无需创建索引:通常情况下,当重复度超过10%时(如性别),无需为该字段创建索引。
2. 索引的个数:索引并非越多越好,因为每个索引都需要存储空间,且索引过多会影响优化器的评估效率。建议单表的索引个数不超过5个,单个索引中的字段数不超过5个。
3. 索引的存储:强制InnoDB的索引使用b+tree存储。因为在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整,过多的索引会减慢更新的速度。
4. 建议增加索引的字段:建议对ORDER BY,GROUP BY,DISTINCT的字段加入索引,避免排序。建议对UPDATE、DELETE语句,根据WHERE条件添加索引。

索引的使用
1. 联合索引使用注意事项:创建联合索引时要对多个字段创建索引,请注意索引的顺序(不同的顺序在执行时会有差别),建议将高频使用字段靠前放置。此外,建议尽量避免冗余索引。
2. 索引使用不当导致索引失效:例如,在where子句中对索引字段进行了表达式的计算,会造成该字段索引失效。
3. 建议创建索引与查询语句相结合:当使用到覆盖索引时可以获得性能提升。比如SELECT a,b FROM test WHERE b=xx;如果a不是主键,适当时候可以将索引添加为index(b,a),以获得性能提升。
4. 避免使用重复索引。如同一列既有主键索引又有唯一索引。
表连接方式优化物理查询优化阶段,也需要确定这些查询所采用的路径,具体包含如下情况: 单表扫描范围:对于单表扫描,可以进行全表扫描,也可以进行局部扫描。 两表连接方式:常用的连接方式,包含嵌套循环连接、Hash连接、合并连接等。 多表连接顺序:多表连接时,需要特别关注连接的顺序,连接顺序直接关系到查询效率与搜索空间。多表连接时,搜索空间可能会达到很高的数据量级,巨大的搜索空间会占用更多的资源。通过调整连接顺序,将搜索空间控制在一定范围。