MySQL 千万级日志查询优化

一、数据库表分区

 

二、数据库分表

　　1. 分表是将表按大小划分，每个表只包含一部分数据，以提高数据库的扩展性；

　　2. 跨表查询是指查询多张表中的数据，JOIN可以将多张表的数据关联在一起，而UNION则将多个SELECT语句的结果合并为一个结果集；

　　　　A. UNION：去重且排序；

　　　　B. UNION ALL：不去重不排序，性能较好；

　　　　C. 示例：

SELECT * FROM (
	SELECT * FROM rhxy_log_2023 where timestamp >= '2023-12-27 00:00:00'
	UNION ALL 
    SELECT * FROM rhxy_log_2024 where timestamp <= '2024-2-2 23:59:59'  
) log ORDER BY log.timestamp DESC, log.id DESC LIMIT 10;

　　3. 查询分表名：SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'rhxy_log_%' AND table_rows > 0 ORDER BY table_name desc；

　　4. 注意事项

　　　　A. 分表的动态创建：

　　　　B. 表存在相同的自增ID。

 

三、冷热分离

 

1. explain分析工具使用

　　A. Using index：表示目前的查询语句使用了索引覆盖机制拿到了数据；

　　B. Using where：表示目前的查询语句无法从索引中获取数据，需要进一步做回表查询去拿表数据；

　　C. Using index condition：和Using where类似，要返回的列未完全被索引覆盖，需要回表查询。

 

2. 索引优化

　　A. 若查询中需要查询多列时，应该使用联合索引，联合索引比多个单列索引查询效率要高，但需要考虑列的顺序、适应性和查询效率等因素；

　　B. 建立联合索引，应当遵循最左前缀原则，只有SQL查询条件中包含了最左的字段，才能使用联合索引，但SQL是否会走索引查询跟where后的条件顺序无关，因为MySQL优化器会优化对查询条件重排序，另外最左前缀原则在匹配到范围查询(>、<、between、like》)时会停止匹配，并不会继续使用联合索引；

　　C. 若查询中涉及多个字段且都是单独查询时，应该使用单列索引。

 

可参考：MySQL中大量数据优化方案