经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » MySQL » 查看文章
Mysql慢查询优化
来源：cnblogs　　作者：realeo　　时间：2023/9/15 13:31:52　　对本文有异议

Mysql慢查询优化实战

效果：效率提升十倍左右

  • 优化前

    1. mysql> use test_old;
    2. Database changed
    3. mysql> set profiling = 'ON';
    4. Query OK, 0 rows affected
    6. mysql> show variables like 'profiling';
    7. +---------------+-------+
    8. | Variable_name | Value |
    9. +---------------+-------+
    10. | profiling     | ON    |
    11. +---------------+-------+
    12. 1 row in set
    14. mysql> show profiles;
    15. +----------+------------+---------------------------------------------------------------------------------------------+
    16. | Query_ID | Duration   | Query                                                                                       |
    17. +----------+------------+---------------------------------------------------------------------------------------------+
    18. |        1 |    0.00419 | show variables like 'profiling'                                                             |
    19. |        2 | 1.78590175 | SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824') |
    20. +----------+------------+---------------------------------------------------------------------------------------------+
    21. 2 rows in set
    23. mysql> EXPLAIN SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824');
    24. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
    25. | id | select_type | table                | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                            |
    26. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
    27. |  1 | SIMPLE      | table_test | NULL       | range | test_id      | test_id | 33      | NULL | 170496 |      100 | Using index condition; Using MRR |
    28. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
    29. 1 row in set

  • 优化后

    1. mysql> use test;
    2. Database changed
    3. mysql> set profiling = 'ON';
    4. Query OK, 0 rows affected
    6. mysql> show variables like 'profiling';
    7. +---------------+-------+
    8. | Variable_name | Value |
    9. +---------------+-------+
    10. | profiling     | ON    |
    11. +---------------+-------+
    12. 1 row in set
    14. mysql> show profiles;
    15. +----------+-----------+---------------------------------------------------------------------------------------------+
    16. | Query_ID | Duration  | Query                                                                                       |
    17. +----------+-----------+---------------------------------------------------------------------------------------------+
    18. |        1 | 0.0060565 | show variables like 'profiling'                                                             |
    19. |        2 | 0.1755525 | SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824') |
    20. +----------+-----------+---------------------------------------------------------------------------------------------+
    21. 2 rows in set
    23. mysql> EXPLAIN SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824');
    24. +----+-------------+----------------------+--------------------------------------------------------------------------------------+-------+------------------+---------+---------+------+--------+----------+-------------+
    25. | id | select_type | table                | partitions                                                                           | type  | possible_keys    | key     | key_len | ref  | rows   | filtered | Extra       |
    26. +----+-------------+----------------------+--------------------------------------------------------------------------------------+-------+------------------+---------+---------+------+--------+----------+-------------+
    27. |  1 | SIMPLE      | table_test | p20221126,p20221127,p20221128,p20221129,p20221130,p20221201,p20221202,p20221203,pmax | range | PRIMARY,test_id | PRIMARY | 32      | NULL | 185501 |      100 | Using where |
    28. +----+-------------+----------------------+--------------------------------------------------------------------------------------+-------+------------------+---------+---------+------+--------+----------+-------------+
    29. 1 row in set

说说在 MySQL 中一条查询 SQL 是如何执行的

  1. 取得链接，使用使用到 MySQL 中的连接器。
  2. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存， 在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。
  3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错 误在此阶段。
  4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时 候（join），决定各个表的连接顺序。
  5. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执 行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则 打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都 等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取 到这个表的最后一行，最后返回。

慢sql定位

table_test表信息

  1. -- ----------------------------
  2. -- Table structure for table_test
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `table_test`;
  5. CREATE TABLE `table_test` (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  7.   `test_id` varchar(10) DEFAULT NULL COMMENT '场景id',
  8.   `data_time` timestamp NULL DEFAULT NULL COMMENT '数据时间',
  9.   `service_id` varchar(50) DEFAULT NULL COMMENT '流量类型',
  10.   `total_traffic` varchar(50) DEFAULT NULL COMMENT '总流量',
  11.   `ul_traffic` varchar(50) DEFAULT NULL COMMENT '上行流量',
  12.   `dl_traffic` varchar(50) DEFAULT NULL COMMENT '下行流量',
  13.   `tcp_conn_req_times` varchar(50) DEFAULT NULL COMMENT 'TCP连接请求次数',
  14.   `tcp_conn_succ_times` varchar(50) DEFAULT NULL COMMENT 'TCP连接成功次数',
  15.   `tcp_conn_succ_rat` varchar(50) DEFAULT NULL COMMENT 'TCP连接成功次率',
  16.   `tcp_conn_total_delay` varchar(50) DEFAULT '0' COMMENT 'TCP连接建立总时长',
  17.   `tcp_conn_avg_delay` varchar(50) DEFAULT '0' COMMENT 'TCP连接建立平均时延',
  18.   `tcp_ul_rtt_total_delay` varchar(50) DEFAULT '0' COMMENT 'TCP上行RTT总时延',
  19.   `tcp_dl_rtt_total_delay` varchar(50) DEFAULT '0' COMMENT 'TCP下行RTT总时延',
  20.   `tcp_ul_rtt_stat_times` varchar(50) DEFAULT NULL COMMENT 'TCP上行RTT总次数',
  21.   `tcp_dl_rtt_stat_times` varchar(50) DEFAULT NULL COMMENT 'TCP下行RTT总次数',
  22.   `tcp_ul_rtt_avg_delay` varchar(50) DEFAULT '0' COMMENT 'TCP上行RTT平均时延',
  23.   `tcp_dl_rtt_avg_delay` varchar(50) DEFAULT '0' COMMENT 'TCP下行RTT平均时延',
  24.   `day_id` varchar(50) DEFAULT NULL COMMENT 'day_id',
  25.   PRIMARY KEY (`id`) USING BTREE,
  26.   KEY `test_id` (`test_id`) USING BTREE,
  27.   KEY `data_time` (`data_time`) USING BTREE
  28. ) ENGINE=InnoDB AUTO_INCREMENT=19671082 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='热门app';

  1. 开启slow_query_log

    1. mysql> set global slow_query_log = 'ON';
    2. Query OK, 0 rows affected
    4. mysql> show variables like 'slow_query_log%';
    5. +---------------------+------------------------------------------+
    6. | Variable_name       | Value                                    |
    7. +---------------------+------------------------------------------+
    8. | slow_query_log      | ON                                       |
    9. | slow_query_log_file | /usr/local/mysql/data/hadoop102-slow.log |
    10. +---------------------+------------------------------------------+
    11. 2 rows in set

  2. 修改long_query_time阈值

    1. mysql> set global long_query_time = 0.5;
    2. Query OK, 0 rows affected
    4. -- 经过测试，发现设置global时，只针对新的会话有效，对当前会话无效。
    5. -- 所以还需要针对当前会话设置一次。
    6. mysql> set long_query_time = 0.5;
    7. Query OK, 0 rows affected
    9. mysql> show variables like 'long_query_time';
    10. +-----------------+----------+
    11. | Variable_name   | Value    |
    12. +-----------------+----------+
    13. | long_query_time | 0.500000 |
    14. +-----------------+----------+
    15. 1 row in set
    17. mysql>

执行超过配置时间的慢sql查看日志

  1. [realeo@hadoop102 ~]$ cd /usr/local/mysql/data/
  2. [realeo@hadoop102 data]$ sudo cat hadoop102-slow.log
  3. /usr/sbin/mysqld, Version: 5.7.27 (MySQL Community Server (GPL)). started with:
  4. Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
  5. Time                 Id Command    Argument
  6. # Time: 2023-04-03T14:46:51.877547Z
  7. # User@Host: root[root] @  [192.168.10.1]  Id:   118
  8. # Query_time: 1.284210  Lock_time: 0.000317 Rows_sent: 91767  Rows_examined: 91767
  9. use test;
  10. SET timestamp=1680533211;
  11. SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824');

慢Sql分析

  1. 开启show profile

    1. -- 仅对当前会话开启
    2. mysql> set profiling = 'ON';
    3. Query OK, 0 rows affected
    5. mysql> show variables like 'profiling';
    6. +---------------+-------+
    7. | Variable_name | Value |
    8. +---------------+-------+
    9. | profiling     | ON    |
    10. +---------------+-------+
    11. 1 row in set

  2. 查看会话中sql执行情况

    1. mysql> show profiles;
    2. +----------+------------+---------------------------------------------------------------------------------------------+
    3. | Query_ID | Duration   | Query                                                                                       |
    4. +----------+------------+---------------------------------------------------------------------------------------------+
    5. |        1 | 0.00193025 | show variables like 'profiling'                                                             |
    6. |        2 |   2.095192 | SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824') |
    7. +----------+------------+---------------------------------------------------------------------------------------------+
    8. 2 rows in set

  3. 查看当前会话某条sql执行记录的资源消耗情况

    1. mysql> show profile cpu, block io for query 2;
    2. +----------------------+----------+----------+------------+--------------+---------------+
    3. | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
    4. +----------------------+----------+----------+------------+--------------+---------------+
    5. | starting             | 0.000125 | 6.8E-5   | 4.5E-5     |            0 |             0 |
    6. | checking permissions | 1E-5     | 5E-6     | 3E-6       |            0 |             0 |
    7. | Opening tables       | 0.00114  | 0.001145 | 0          |            0 |             0 |
    8. | init                 | 5.1E-5   | 3.1E-5   | 1.6E-5     |            0 |             0 |
    9. | System lock          | 1E-5     | 6E-6     | 4E-6       |            0 |             0 |
    10. | optimizing           | 1E-5     | 6E-6     | 4E-6       |            0 |             0 |
    11. | statistics           | 0.000519 | 0.000521 | 0          |            0 |             0 |
    12. | preparing            | 1.8E-5   | 1.6E-5   | 0          |            0 |             0 |
    13. | executing            | 3E-6     | 2E-6     | 0          |            0 |             0 |
    14. | Sending data         | 2.093149 | 1.301995 | 0.998561   |            0 |           384 |
    15. | end                  | 4.5E-5   | 0        | 1.9E-5     |            0 |             0 |
    16. | query end            | 1.2E-5   | 0        | 1.2E-5     |            0 |             0 |
    17. | closing tables       | 1.7E-5   | 0        | 1.7E-5     |            0 |             0 |
    18. | freeing items        | 2E-5     | 0        | 2E-5       |            0 |             0 |
    19. | logging slow query   | 5.1E-5   | 0        | 5.1E-5     |            0 |             0 |
    20. | cleaning up          | 1.5E-5   | 0        | 1.4E-5     |            0 |             0 |
    21. +----------------------+----------+----------+------------+--------------+---------------+
    22. 16 rows in set

    show profile常用查询参数：

    • all：显示所有的开销信息。
    • block io：显示块io开销。
    • context switches： 上下文切换开销。
    • cpu：显示cpu开销信息。
    • ipc：显示发送和接受开销信息。
    • memory：显示内存开销信息。
    • page faults：显示页面错误开销信息。
    • source：显示和 source_function,source_file,source_line 相关的开销信息。
    • swaps：显示交换次数开销信息。

存储引擎/索引结构选择

Hash索引与B+树索引的区别

  1. Hash索引不能进行范围性的一个查找，因为hash指向的数据是无序的，而B+树的叶子节点是个有序的链表。Hash索引仅能满足（=、<>）和in查询。如果进行范围查询，哈希型索引，时间复杂化会退化为O（n）而树型的有序特性，依然能保持O（log2n）的高效率
  2. Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash索引在计算Hash值得时候将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算hash值。因此如果用到联合索引的一个或者多个索引时，无法被利用。
  3. Hash不支持OrderBy排序，以为Hash索引指向的数据无序，因此无法起到排序的作用。而B+树索引数据是有序的，可以起到对该字段order by排序优化的作用，同理，我们也无法对hash索引进行模糊查找,而B+树使用模糊查询的方式时，like后面后模糊查询的话就可以起到优化作用。
  4. 对于InnoDB的哈希索引，确切的应该这么说：
    1. InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引；
    2. InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的；
  1. mysql> show profiles;
  2. +----------+------------+---------------------------------------------------------------------------------------------+
  3. | Query_ID | Duration   | Query                                                                                       |
  4. +----------+------------+---------------------------------------------------------------------------------------------+
  5. |        1 | 0.00187275 | show variables like 'profiling'                                                             |
  6. |        2 | 1.63446275 | SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824') |
  7. +----------+------------+---------------------------------------------------------------------------------------------+
  8. 2 rows in set
  10. mysql> show index from `table_test`;
  11. +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  12. | Table                | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  13. +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  14. | table_test |          0 | PRIMARY   |            1 | id          | A         |     1302941 | NULL     | NULL   |      | BTREE      |         |               |
  15. | table_test |          1 | test_id  |            1 | test_id    | A         |          94 | NULL     | NULL   | YES  | BTREE      |         |               |
  16. | table_test |          1 | data_time |            1 | data_time   | A         |          75 | NULL     | NULL   | YES  | BTREE      |         |               |
  17. +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  18. 3 rows in set

字段设计优化

  • 字段类型：确认长度的字段采用char类型
  • 字段长度：索引字段即常用区分字段尽量简短
  1. mysql> SELECT max(LENGTH(test_id))  FROM `table_test`;
  2. +-----------------------+
  3. | max(LENGTH(test_id)) |
  4. +-----------------------+
  5. |                     8 |
  6. +-----------------------+
  7. 1 row in set
  9. mysql> show profiles;
  10. +----------+------------+---------------------------------------------------------------------------------------------+
  11. | Query_ID | Duration   | Query                                                                                       |
  12. +----------+------------+---------------------------------------------------------------------------------------------+
  13. |        1 | 0.00324825 | show variables like 'profiling'                                                             |
  14. |        2 |  12.979875 | alter table table_test modify test_id char(8)                                    |
  15. |        3 |   1.683254 | SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824') |
  16. +----------+------------+---------------------------------------------------------------------------------------------+
  17. 3 rows in set

查询语句优化

  • 查询语句的优化对于MySQL大数据查询速度的提升非常重要。应该避免使用SELECT *，因为这会导致MySQL检索整个表的所有列，从而降低查询速度。应该只查询需要的列，并使用WHERE子句限制检索的行数。

  • MySQL组合索引（复合索引）的最左优先原则。最左优先就是说组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。只要组合索引最左边第一个字段出现在Where中，那么不管后面的字段出现与否或者出现顺序如何，MySQL引擎都会自动调用索引来优化查询效率。

  • 在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

索引字段优化

  • 大多数情况下通过test_id来查询，根据此字段建索引
  1. -- 查看当前表信息
  2. show create table table_test;
  4. -- 创建新增索引
  5. ALTER TABLE table_test ADD INDEX test_id_idx (test_id(8));
  • 其次可根据查询场景合理建立组合索引等

使用EXPLAIN分析

含义可参考：https://blog.csdn.net/jibaole/article/details/121293188

  1. mysql> EXPLAIN SELECT * FROM `table_test` where test_id in ('99863885543', '99863900221', '99821363824');
  2. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
  3. | id | select_type | table                | partitions | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                            |
  4. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
  5. |  1 | SIMPLE      | table_test | NULL       | range | test_id      | test_id | 33      | NULL | 163326 |      100 | Using index condition; Using MRR |
  6. +----+-------------+----------------------+------------+-------+---------------+----------+---------+------+--------+----------+----------------------------------+
  7. 1 row in set
  • 可用于分析常见索引失效问题，例如字符串字段作为索引时需要在where中加单引号''
  1. mysql> EXPLAIN SELECT * FROM `table_test` where test_id in (99863885543,99863900221,99821363824);
  2. +----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
  3. | id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
  4. +----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
  5. |  1 | SIMPLE      | table_test | NULL       | ALL  | test_id      | NULL | NULL    | NULL | 1397231 |       30 | Using where |
  6. +----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
  7. 1 row in set

分区优化

分区表是将大表分成小表的一种方法。在处理大数据时，使用分区表可以大大提高查询速度。分区表将数据分成多个分区，每个分区可以独立地进行查询。当进行查询时，MySQL只需要扫描需要的分区，而不是整个表。

  1. 在进行自动增加分区前一定得先对表手动分几个区

    1. -- 创建复合主键
    2. alter table table_test drop primary key,add primary key(`test_id`,`data_time`,`id`);
    4. ALTER TABLE table_test PARTITION BY RANGE (UNIX_TIMESTAMP(data_time))(
    5. 	PARTITION p20221126
    6. 	VALUES
    7. 		LESS THAN (
    8. 			UNIX_TIMESTAMP('2022-11-27')
    9. 		),
    10. 		PARTITION p20221127
    11. 	VALUES
    12. 		LESS THAN (
    13. 			UNIX_TIMESTAMP('2022-11-28')
    14. 		),
    15. 		PARTITION p20221128
    16. 	VALUES
    17. 		LESS THAN (
    18. 			UNIX_TIMESTAMP('2022-11-29')
    19. 		),
    20. 		PARTITION p20221129
    21. 	VALUES
    22. 		LESS THAN (
    23. 			UNIX_TIMESTAMP('2022-11-30')
    24. 		)
    25. )
    27. -- 如果有大于分区上限的值想插入表中，系统会返还错误，为了兼容这种情况，我们可以新增一个分区，上限为maxvalue。所有大于当前上限的值都会放入这个分区：
    28. alter table table_test add partition(partition pmax values less than(maxvalue));
    29. ALTER TABLE table_test ADD PARTITION (PARTITION p20221130 VALUES LESS THAN (TO_DAYS ('2022-11-30')))
    31. -- 删除分区，同时清除历史数据
    32. alter table table_test drop partition p20221127;

  2. 查询表分区信息

    1. mysql> SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,
    3. TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
    5. FROM information_schema.PARTITIONS
    7. WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='table_test';
    8. +----------------+------------------+---------------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
    9. | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION      | PARTITION_DESCRIPTION | TABLE_ROWS | SUBPARTITION_NAME | SUBPARTITION_METHOD | SUBPARTITION_EXPRESSION |
    10. +----------------+------------------+---------------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
    11. | p20221126      | RANGE            | UNIX_TIMESTAMP(data_time) | 1669536000            |     470450 | NULL              | NULL                | NULL                    |
    12. | p20221127      | RANGE            | UNIX_TIMESTAMP(data_time) | 1669622400            |     378562 | NULL              | NULL                | NULL                    |
    13. | p20221128      | RANGE            | UNIX_TIMESTAMP(data_time) | 1669708800            |     419724 | NULL              | NULL                | NULL                    |
    14. | p20221129      | RANGE            | UNIX_TIMESTAMP(data_time) | 1669795200            |     135171 | NULL              | NULL                | NULL                    |
    15. +----------------+------------------+---------------------------+-----------------------+------------+-------------------+---------------------+-------------------------+
    16. 4 rows in set
    18. -- 查询指定分区数据
    19. SELECT * FROM `table_test` PARTITION(p20221129) where test_id in ('99863885543', '99863900221', '99821363824');

  3. 按天自动分区存储过程

    1. DELIMITER $$
    2.  
    3.  -- 切换数据库test
    4.  USE `test`$$
    5.  
    6.  DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
    7.  
    8.  CREATE DEFINER=`root`@`%` PROCEDURE `create_partition_by_day`()
    9.  BEGIN
    10.  /* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
    11.      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    12.      START TRANSACTION;
    13.  
    14.  /* 到系统表查出这个表的倒数第二大分区，得到分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
    15.      SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 
    16.      WHERE table_name='table_test' ORDER BY partition_ordinal_position DESC LIMIT 1,1;
    17.       SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
    18.  /* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
    19.      SET @s1=CONCAT('ALTER TABLE table_test REORGANIZE PARTITION pmax INTO (PARTITION p',@Max_date,' VALUES LESS THAN (UNIX_TIMESTAMP (''',DATE(@Max_date+1),''')),partition pmax values less than(maxvalue))');
    20.      /* 输出查看增加分区语句*/
    21.      SELECT @s1;
    22.      PREPARE stmt2 FROM @s1;
    23.      EXECUTE stmt2;
    24.      DEALLOCATE PREPARE stmt2;
    25.  /* 取出最小的分区的名称，并删除掉 。
    26.      注意：删除分区会同时删除分区内的数据，慎重 */
    27.      /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
    28.      where table_name='table_test' order by partition_ordinal_position limit 1;
    29.      SET @s=concat('ALTER TABLE table_test DROP PARTITION ',@P0_Name);
    30.      PREPARE stmt1 FROM @s;
    31.      EXECUTE stmt1;
    32.      DEALLOCATE PREPARE stmt1; */
    33.  /* 提交 */
    34.      COMMIT ;
    35.   END$$
    36.  
    37.  DELIMITER ;

  4. 增加事件执行

    1. -- 开启任务定时器
    2. mysql> SET GLOBAL event_scheduler = ON;
    3. Query OK, 0 rows affected
    5. mysql> SHOW VARIABLES LIKE 'event_scheduler';
    6. +-----------------+-------+
    7. | Variable_name   | Value |
    8. +-----------------+-------+
    9. | event_scheduler | ON    |
    10. +-----------------+-------+
    11. 1 row in set
    13. -- 事件定义
    14. DELIMITER ||
    15.  CREATE EVENT Partition_by_day_event
    16.            ON SCHEDULE
    17.            EVERY 1 day STARTS '2022-11-29 07:00:00'
    18.            DO
    19.        BEGIN  
    20.  
    21.            CALL test.`create_partition_by_day`;  
    22.  
    23.   END ||
    24.  DELIMITER ;

配置可参考：https://www.bbsmax.com/A/gAJG7rZJZR/

性能可参考：https://www.cnblogs.com/mzhaox/p/11201715.html

使用缓存

  • Redis
    • 性能极高 – Redis 能读的速度是 110000 次/s,写的速度是 81000 次 /s 。
    • 基于内存操作，C语言实现，因此相对于Mysql等一些常见关系型数据库基于硬盘存储，大量的I/O操作效率更加高效。

优化服务器硬件

优化服务器硬件可以提高MySQL大数据查询速度。应该使用更快的CPU、更大的内存和更快的硬盘。MySQL可以更快地读取和处理数据。

架构设计

  • 能否根据业务，对该大表使用例如MyCat，对表进行拆分。不过可能在设计上较复杂，且会引入其他问题。
    微信公众号搜索：余生还长着呢

原文链接：https://www.cnblogs.com/realeo/p/17701978.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

MySQL热门文章

  • 大项目之网上书城（九）——订单Demo
  • CentOS7下MySQL服务启动失败原因及解决方法
  • MySQL从删库到跑路_高级（六）——索引
  • 55 道MySQL基础题
  • MySQL与PHP的基础与应用专题之自连接
  • 树形结构的菜单表设计与查询
  • MYSQL语句
  • 《高性能MySQL（第3版）》PDF免费下载
  • 《MySQL必知必会》知识汇总二
  • 【必知必会的MySQL知识】④DCL语言

    • MySQL推荐文章

  • 如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）
  • MySQL中为什么要使用索引合并(Index Merge)？
  • MySQL派生表合并优化的原理和实现
  • 使用EF 连接 数据库 SQLserver、MySql 实现 CodeFirst
  • 升级到 MySQL 8.4，MySQL 启动报错：io_setup() failed with EAGAIN
  • TiKV 源码分析之 PointGet
  • 异构数据源同步之数据同步 → DataX 使用细节
  • 一文搞懂 MySQL 日志
  • 浅析MySQL 8.0直方图原理
  • 分布式任务调度内的 MySQL 分页查询优化

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号