mysql超大分页查询数据以及优化性能

sql普通接口查询：

• 一般指的是后台管理系统，数据量访问不大，以后也不会很大，就可以使用最简单的查询接口

大访问量查询接口

超大分页问题

使用本地表：

jdbc:mysql://localhost:3306/baiwan_ceshi?useUnicode=true&characterEncoding=UTF-8&useSSL=false

普通的分页查询：

 SELECT * FROM app_user WHERE age>20 LIMIT 1000000,100

测试

• 一般的查询

  select * from app_user
  
  三次查询：4494ms、5943ms、6069ms
  

• 采用一般的分页查询，使用limit（第一个参数代表偏移量、第二个参数代表记录行最大数目）

  select * from app_user where age > 20 limit 1000,10
  
  三次查询时间：21ms、22ms、20ms
  

• • 下面测量偏移量不变，记录数变大时间的长短

    select * from app_user where age >20 limit 10000,10
    select * from app_user where age >20 limit 10000,100
    select * from app_user where age >20 limit 10000,1000
    select * from app_user where age >20 limit 10000,10000
    
    查询10条记录：40ms 24ms 27ms
    查询100条记录：30ms 26ms 27ms
    查询1000条记录：26ms 29ms 28ms
    查询10000条记录：45ms 48ms 50ms
    查询100000条记录：239ms 265ms 335ms
    

• • 下面测量看记录数不变，偏移量变大的时间长短

    select * from app_user where age >20 limit 100,10
    select * from app_user where age >20 limit 1000,10
    select * from app_user where age >20 limit 10000,10
    select * from app_user where age >20 limit 100000,10
    select * from app_user where age >20 limit 1000000,10
    
    查询100偏移：21ms 20ms 20ms
    查询1000偏移：19ms 20ms 25ms
    查询10000偏移：28ms 24ms 24ms
    查询100000偏移：64ms 63ms 68ms
    查询1000000偏移：561ms 582ms 579ms
    

总结：不管是偏移量变大还是记录数变大都会使时间增大，尤其是偏移量10万数据之后数据成倍数的增加

优化：

方法一（使用覆盖索引也就是子查询）

• 覆盖索引指的是：查询的时候尽量使用主键索引去查询，因为主键索引的叶子节点上具有这一条数据的所有字段数据，不用再做回表查询，例如下方写的覆盖索引

select * from app_user a1 join (select id from app_user) a2 on a1.id = a2.id

方法二：mysql性能优化

• 原因分析
• 慢查询日志

  慢查询：就是指的是查询的时间长，前台导致响应时间过长。
  解决方案：实用工具进行检测，或者使用mysql自带的慢查询日志的方式，开启慢日志并设置超时时间
  

  • 慢日志查询方法

  1.查询是否开启了慢日志，输入命令
    show variables like '%query%';
    
  

主要看这三个属性：

long_query_time ：10.000000：查询超过10秒被定义为慢语句
slow_query_log ：OFF：是否打开慢查询日志
slow_query_log_file ： /usr/local/mysql/data/slow.log：慢查询日志文件所在位置

set global slow_query_log = ON; # 打开慢查询日志
set global long_query_time = 1; # 超过1秒的语句被定义为慢语句，注意设置了之后需要重新连接才有效
set global slow_query_log_file='D:/ASoftWare/SQL/MySQL/slow_query.log'  # 修改一下日志存放的位置

如果出现慢日志就会出现一下日志：
# Time: 2023-09-21T09:02:35.604977Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:   170
# Query_time: 4.614108  Lock_time: 0.000070 Rows_sent: 2000000  Rows_examined: 2000000
SET timestamp=1695286955;
SELECT * FROM app_user;

• 优化慢日志查询
  • 使用命令explain分析对应日志中耗时的sql语句

explain SELECT * FROM register

table：表示属于哪张数据表

type：最重要的参数，表示连接使用了何种类型。从最好到最差的连接类型为const，eq_reg，ref，range，index和ALL。

possible_keys：显示可能应用在这张表中的索引。如果为null，则表示没有可能的索引。

key：实际使用的索引。如果为null，则表示没有使用索引。

key_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好。

ref：表示索引的哪一列被使用了，如果可能的话，是一个常数。

rows：Mysql认为必须检查的用来返回请求数据的行数。

索引

• 索引结构

  • 二叉树

    二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低。只有两个节点，大数据的情况下层级比较深，检索速度慢
    

    
    
    

  • B树（多路平衡查找树）

    以一颗最大度数为5的b树为例（每个节点最多存储4个key，5个指针）
    当某个节点下的元素key或者指针超过5个的时候，取中间元素向上分裂，剩下的两个元素向两边分裂开。
    
    特点：每个key下面都存放着数据
    

    

  • B+树

  b+树的叶子节点上存储数据，非叶子节点只存储key。并且叶子节点上的key包含了非叶子节点上的所有key
  
  特点：所有的数据都会出现在叶子节点，叶子节点形成一个单向链表，
  

  

  • 在mysql中的b+树，对原始的树做了优化，就是叶子节点上的数据是双向循环链表，并且尾部指向了头部

  

• 注意：
    对于b+树种的key重复值问题：b+树上的key是可以重复的，因为不只是有聚簇索引，还有非聚簇索引，非聚簇索引中的数据
    是可以重复的，那么可以重复的字段是如何存储的呢？
    
    例如：select * from user where user_name = "小明";  假设此时的user_name加上了索引。
    
    那么他的存储形式应该是，非叶子节点上的key都是user_name的值，叶子节点上的key是user_name，则数据的值就是主键key。
    想查找整条数据的话需要回表，根据主键值进行重新查找整条数据
    
    例如：select * from user from id = 5;
    那么他的存储形式就是非叶子节点的key都是主键id，叶子节点上的key是主键id，数据就是整条数据。
    
    如下图结果：
  

• 索引

  索引指的是：帮助mysql查询的高校获取数据的一种数据结构（有序），使用的是B+Tree。
  

  • 聚簇索引、非聚簇索引（二级索引）、覆盖索引、回表

    聚簇索引：指的就是表中如果存在主键的话，主键就是聚簇索引，没有主键，唯一索引就是聚簇索引。
              如果没有唯一索引，那么默认创建rowid作为聚簇索引。
              所以每一张表中，必须存在一个聚簇索引，且有且只有一个聚簇索引。
              
    非聚簇索引：除了聚簇索引，剩下的所有索引都是非聚簇索引，也叫二级索引，可有可无，可以有多个。
    
    回表：在使用二级索引查询数据的时候，二级索引上查出来的数据叶子节点上，只有一个聚簇索引，
          此时如果想获取整条数据，就需要拿着这个聚簇索引字段进行重新查询获取整条数据。
    
    覆盖索引：覆盖索引的出现就是为了防止回表的产生，因为回表会产生不必要的时间。
              覆盖索引出现的原因就是查询中使用了非聚簇索引，查询出来的
    

• 创建索引的原则

  1. 数量大，频繁使用某个或连续多个字段，单表超10w数据
  2.经常作为查询数据 where、order by、group by操作时创建索引
  3.尽量选择区分度高的列作为索引
  4.对于字段的长度比较长的，可以针对字段的特点建立前缀索引
  5.尽量使用联合索引，避免回表
  

• 索引失效的情况

  1.对于联合索引，需要遵循最左前缀，如果跳过中间那一列，则只有最左列生效
  2.如果某一列使用了范围查询，则此右边的列索引都不生效
  3.如果在索引字段上进行运算操作，索引不生效
  4.如果索引字段上的类型与赋值的类型不一致的时候，索引不生效
  5.如果索引字段进行模糊匹配，若%开头，则索引不生效
  

• sql优化经验

  1.表的设计优化：选择合适的数值（tinyint、int、bigint）和合适的字符串类型（varchar、char）
  2.避免在查询的过程中产生回表，尽量使用覆盖索引
  3.尽量将union all代替union，因为union会多一层过滤，将重复的数据过滤掉
  4.避免在where中字段进行表达操作，如使用subString，会使索引失效