MySQL-day1|青训营笔记

HEU小Q
136 阅读4分钟

数据库分页性能优化

在MySQL数据库查询中，可以使用limit关键字，实现分页查询。

例如：

SELECT prod_name FROM products LIMIT 5, 5;

但是，例如LIMIT 10000,20这样的分页，代价是非常高的。

当我们的offset（偏移量）特别大，而pagesize却比较小时。以LIMIT 10000,20为例。这时MySQL需要查询10020条记录，然后丢弃掉前10000条数据，只返回最后20条有用数据，会造成极大的性能浪费。

这种性能浪费是因为offset过大和MySQL的扫描机制导致的，我们可以从两个角度进行优化：一是尽可能控制offset的值不要太大，二是使用索引覆盖扫描。

索引覆盖扫描也是本篇讲述的重点，其核心思想就是利用索引，跳过MySQL逐行遍历数据的这个过程，以优化查询性能。

索引覆盖扫描

当我们进行查询时，不是返回表中所有的列，而是以子查询的方式返回索引列。然后将返回的子表通过inner join和using关键字和主表进行关联。这里我们以主键索引为例

SELECT film.film_id,film.description 

FROM sakila.film

INNER JOIN (

    SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5

)  USING(film_id);

在MySQL的InnoDB存储引擎中：

  • 聚簇索引一个表中只能有一个。
  • 主键索引是一种聚簇索引，其他的索引都是非聚簇索引。在创建表时如果没有指定主键,InnoDB会自动选择一个唯一非空索引作为主键和聚簇索引。
  • 聚簇索引的读取效率最高，因为非聚簇索引在读取时需要进行双重查找。
  • 聚簇索引的叶子结点中存储的是实际的数据行，而非聚簇索引的叶子结点中存储的是指向该数据行的指针。
  • 利用非聚簇索引读取数据：如果存在聚簇索引，则该非聚簇索引的叶子结点存储的是指向聚簇索引叶子结点的指针。如果不存在聚簇索引，则该非聚簇索引的叶子结点，存储的是指向文件位置的指针。

我们在使用了索引覆盖扫描和延迟关联的操作大大提升了查询效率。他让MySQL尽可能少的去扫描页面。

采用精确位置的limit查询

针对offset（偏移量）较大导致性能浪费的问题，除了采用索引覆盖查找+延迟关联的方法，还可以采用书签＋where进行条件过滤的方法。我们采用书签记录上一次查找的边界，进而确定下一次查找开始的位置，并使用where进行过滤，这样也可以使查询大量无用数据导致MySQL查询性能浪费的问题得到解决。

例如：rental_id = 16030就是我们的书签，通过书签＋where的方式，可以实现精确的limit查询，极大地减少了MySQL对于无用数据的查询次数。

SELECT * FROM sakila.rental 
WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

SQL中的聚合函数

常见的有COUNT(),AVG(),SUM(),MIN(),MAX()。这五个聚合函数，全部都可以与GROUP BY一起使用。

注意：

  • COUNT()有三种用法：COUNT(1),COUNT(*),COUNT(字段名)
  • COUNT(1),COUNT(*)在InnoDB引擎下性能和结果完全一致。
  • COUNT(1),和COUNT(*)都会统计null值。而COUNT(字段名不会统计null值)。
  • MIN(),MAX()也可以对字符串操作
  • 如果MySQL的sql_mode=only_full_group_by，如果采用了GROUP BY子句进行分组，则返回的字段必须是分组字段或聚合函数。其他字段无法直接进行select，但是可以作为聚合函数的参数进行传入。

SQL的执行流程

首先解析SQL语句，并检查SQL语法-->from-->on-->join-->where-->group by--> 聚合函数 -->having -->select -->distinct -->order by --> limit

表与表之间是如何关联的

有三种度量：是否等值、内/外？、对应关系

是否等值：

等值连接： =

非等值连接：>,<,>=,<=,like

内/外？：

内连接：inner join 他会返回两张表中满足条件的数据，不满足条件的数据不会被展示出来

外连接：left join会返回左表中的所有数据和右表中满足条件的数据,right join

对应关系：一对一、一对多、多对多、自关联

这里主要说下：一对多、多对多、自关联

一对多：

分为主表、从表，存在主从关系，可以通过外键实现，但是外键会影响查询性能。因为外键会占用存储空间，并且为了维护数据完整性在每次插入、删除、更新数据的时候都需要进行外键约束的检查，这都会占用性能。如果一定要使用外键，最好使用数值型字段作为外键，有利于建立索引。最好使用同一个表的多个外键,使用相同的列作为外表的主键列。这可以减少空间的占用。巧妙地运用SET FOREIGN_KEY_CHECKS=0临时禁用外键约束来优化性能。

多对多：

可以采用中间表进行连接来实现。

自关联：

自关联就是一张表自己与自己相关联，为了避免表名的冲突，需要在关联时通过别名将它们当做两张表来看待，当表中具有多个层级的数据时，可以采用自关联，一次查询出多个层级的数据。

avatar
文章
阅读
粉丝