SQL经验
# 前言
常规记录笔记,不定时更新
# 基础理论
# 为什么查询的时候会出现结果不按主键排序的情况
我个人一直认为不加
order by
的情况下,select
查询是默认按照id
进行正序排列的,但是今天在实际使用的时候发现了不是这样的,所以就去查了一下资料,发现了一些有意思的东西。
mysql
在不给定 order by
条件的时候,得到的数据结果的顺序是跟查询列有关的。因为在不同的查询列的时候,可能会使用到不同的索引条件。Mysql
在使用不同索引的时候,得到的数据顺序是不一样的。这个可能就跟 Mysql
的索引建立机制,以及索引的使用有关了。
# 优化
# group by
慢查询优化
当表的数据达到千万级别的时候单表查询时携带了 group by
会导致一个查询达到 20s
。分表这个事情牵扯的太多,只好从语句上进行优化,后面发现了 DISTINCT
尝试了一下,直接按到了 300ms
-- 原SQL
select * from xingcxb group by username
-- 优化后的SQL
select DISTINCT username from xingcxb
1
2
3
4
2
3
4
# mysql
表分区是否存在意义
前几天看到了一个表分区的说法,然后我手里头正好有一个日增长近百万数据的表,并且还是个热表,仔细看了下表分区,后来果断放弃了,理由如下:
- 表分区后如果要进行查询时不指定分区的情况下和不分区表的查询结果是一致的,意义不大
- 表分区来进行操作的话实际和分表的操作而言,存在部分数据不协调需要人工补录一下,旧的数据可以直接进行冷备份
# 使用 mysql
执行 UNION ALL
语句时出现 The used SELECT statements have a different number of columns
SELECT *
FROM (
SELECT * FROM order_table_1 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT * FROM order_table_2 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT * FROM order_table_3 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT * FROM order_table_4 WHERE due_date <= CURRENT_TIMESTAMP
) AS all_due_orders
ORDER BY due_date DESC;
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
这是因为表中的列不一致导致的。
# 解决方案
指定好需要的列即可,如果表中存在异类的列要展示的话...抱歉我也不会😂
SELECT due_date, column1, column2, ... -- 选择共有的列
FROM (
SELECT due_date, column1, column2, ... FROM order_table_1 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT due_date, column1, column2, ... FROM order_table_2 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT due_date, column1, column2, ... FROM order_table_3 WHERE due_date <= CURRENT_TIMESTAMP
UNION ALL
SELECT due_date, column1, column2, ... FROM order_table_4 WHERE due_date <= CURRENT_TIMESTAMP
) AS all_due_orders
ORDER BY due_date DESC;
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 使用 mysql
执行 UNION ALL
语句时出现 Illegal mix of collations for operation 'UNION'
基本上是因为展示的字段中存在不一致的「字符集」或「排序规则」导致的。
这个就需要一个一个字段进行排除检查了。
我的是两个表中的 username
字段,字符集均为:utf8mb4
但是排序规则一个是:utf8mb4_general_ci
另一个是:utf8mb4_0900_ai_ci
导致的。
更新时间: 2024/1/5 10:06:26