我的问题

说明sql语句中, 各种关键字的书写顺序


SQL语句的书写顺序和执行顺序是不同的,这是一个非常重要的概念。

一、SQL语句的书写顺序

SELECT DISTINCT 列名           -- 1. 选择列
FROM 表名                     -- 2. 来源表
JOIN 另一张表 ON 连接条件      -- 3. 连接其他表
WHERE 行级过滤条件            -- 4. 行级过滤
GROUP BY 分组列              -- 5. 分组
HAVING 组级过滤条件          -- 6. 组级过滤
ORDER BY 排序列              -- 7. 排序
LIMIT 限制行数               -- 8. 限制条数

二、完整的书写顺序示例

SELECT 
    c.category_name,
    COUNT(p.product_id) AS product_count,
    AVG(p.price) AS avg_price
FROM 
    products p
    INNER JOIN categories c ON p.category_id = c.category_id
WHERE 
    p.status = 'active'
    AND p.price > 100
GROUP BY 
    c.category_name
HAVING 
    COUNT(p.product_id) >= 10
ORDER BY 
    product_count DESC,
    c.category_name ASC
LIMIT 5;

三、各个关键字的含义和作用

关键字作用必填
SELECT指定要查询的列✅ 必须
FROM指定数据来源表✅ 必须
JOIN关联其他表❌ 可选
WHERE过滤行数据❌ 可选
GROUP BY分组统计❌ 可选
HAVING过滤分组后的数据❌ 可选
ORDER BY排序❌ 可选
LIMIT限制返回行数❌ 可选

四、各数据库的差异

MySQL/PostgreSQL/SQLite:

-- LIMIT 放在最后
SELECT * FROM users LIMIT 10;

SQL Server/T-SQL:

-- 使用 TOP
SELECT TOP 10 * FROM users;
 
-- 或使用 OFFSET/FETCH
SELECT * FROM users 
ORDER BY id 
OFFSET 0 ROWS 
FETCH NEXT 10 ROWS ONLY;

Oracle:

-- 使用 ROWNUM
SELECT * FROM users WHERE ROWNUM <= 10;
 
-- 或使用 OFFSET/FETCH (12c+)
SELECT * FROM users 
OFFSET 0 ROWS 
FETCH NEXT 10 ROWS ONLY;

五、常见错误写法

❌ 错误:WHERE 中使用聚合函数

SELECT * FROM products 
WHERE AVG(price) > 100;  -- 聚合函数不能在WHERE中

✅ 正确:聚合函数在HAVING中

SELECT category_id, AVG(price) 
FROM products 
GROUP BY category_id 
HAVING AVG(price) > 100;

❌ 错误:ORDER BY 位置不对

SELECT * FROM products 
LIMIT 10 
ORDER BY price;  -- ORDER BY 应该在 LIMIT 之前

✅ 正确:ORDER BY 在 LIMIT 之前

SELECT * FROM products 
ORDER BY price 
LIMIT 10;

六、快速记忆口诀

SELECT FROM JOIN
WHERE GROUP HAVING
ORDER BY 再 LIMIT
顺序千万不能错

这个顺序是SQL的标准写法,遵循这个规则可以避免大部分语法错误。

count(*)和count(id)和count(1)在mysql8中的性能有区别吗

在MySQL 8 + InnoDB环境下,COUNT(*) 和 COUNT(1) 性能完全没有区别,两者都是效率最高的写法;COUNT(id) 性能略低;COUNT(字段) 性能最差(尤其是非索引字段)。

这是一个流传甚广但已在MySQL 5.7/8.0版本中被证伪的技术误区。下面我用实测数据和原理拆解来为你澄清:

🔍 核心结论:性能排名与实测数据

根据在MySQL 5.7.23/8.0环境下、1100万+数据量的多次实测,结论高度一致-3-6-7:

COUNT(*) ≈ COUNT(1) > COUNT(主键id) > COUNT(索引字段) > COUNT(非索引字段)

写法执行时间 (1100万行)执行计划特点关键行为
COUNT(*)3.11秒走最小二级索引不取值,优化器专门优化,直接累加
COUNT(1)3.04秒走最小二级索引不取值,server层每行放1,累加
COUNT(主键id)16.07秒强制走主键/二级索引需取值,涉及解析数据行、拷贝字段值
COUNT(索引字段)6.63秒走该字段索引需取值、判断NULL
COUNT(非索引字段)16.85秒全表扫描最慢,需读数据页+判断NULL

关键发现:COUNT(*)和COUNT(1)实测都在3秒左右,而COUNT(主键id)超过16秒——差了5倍以上-3。

⚙️ 为什么性能有差异?原理拆解(InnoDB)

1. COUNT(*) 与 COUNT(1):优化器“一视同仁”

  • MySQL 8.0官方文档明确指出:“InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.”-5
  • 两者都不从引擎层“取值”:COUNT(*)被优化为COUNT(0),COUNT(1)直接传常数,server层每行+1即可-5-7
  • 优化器策略:自动选择key_len最小的二级索引遍历,而非主键(因为二级索引叶子节点只存主键值,比聚簇索引小得多)-3-5

2. COUNT(主键id):慢在“取值”

  • 必须从引擎层读取每一行的id字段值并返回给server层,涉及解析数据行、拷贝字段值的操作-1-7
  • 即使走索引,这部分额外开销在大数据量下非常明显

3. COUNT(字段):最慢的两个坑

  • 非索引字段:只能全表扫描,没有索引可用-3-6
  • 所有字段:都需要判断是否为NULL(即使字段是NOT NULL,逻辑判断步骤也存在),而COUNT(*)和COUNT(1)完全跳过NULL判断-1-4

💎 最佳实践建议(针对MySQL 8.0)

  1. 无脑用 COUNT(*)
    阿里巴巴开发规约强制:不要使用count(列名)或count(常量)来替代count(*),COUNT(*)就是SQL92标准统计行数的语法,MySQL对它做了最深度的优化-4-5。
  2. 一定要利用“最小二级索引”
    COUNT(*)虽然快,但依赖于存在一个小字段的二级索引。如果你的表只有主键、没有其他索引,COUNT(*)会走主键,大表依然慢——此时应主动建一个尽量短的二级索引(如ALTER TABLE ADD INDEX idx_small (tiny_column))来加速COUNT(*)-3-5。
  3. 严禁对大表用COUNT(非索引字段)
    这是全表扫描,数据量稍大就是接口性能杀手。
  4. 追求极致精确计数,考虑计数表或缓存
    如果表行数极大(千万级以上)且实时性要求高,COUNT(*)无论如何都扛不住,建议用额外计数表(事务维护)或Redis异步计数(允许短暂不一致)-5-6。

tiny_column指什么; 如何做额外计数表或者redis计数

一、tiny_column 指的是什么?

tiny_column 是指占用存储空间很小的字段,用来作为最小二级索引加速 COUNT(*)。

具体选择建议:

字段类型存储大小示例
tinyint1字节status TINYINT
smallint2字节level SMALLINT
char(1)1字符gender CHAR(1)
date3字节created_date DATE

实战示例:

-- 原表:只有主键,COUNT(*)会走主键索引(较大)
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    amount DECIMAL(10,2),
    created_at DATETIME
);
 
-- 添加一个最小二级索引加速COUNT(*)
ALTER TABLE orders ADD INDEX idx_flag (status); -- status是TINYINT类型
 
-- 或者专门建一个标记字段
ALTER TABLE orders ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0;
ALTER TABLE orders ADD INDEX idx_deleted (deleted);

原理:InnoDB的二级索引叶子节点只存索引字段+主键值,比聚簇索引小得多,遍历更快。


二、额外计数表方案(精确计数)

适用场景:

  • 对计数要求绝对精确
  • 写并发不是极高
  • 可以接受事务中的额外写操作

实现方式:

1. 建表结构

CREATE TABLE order_stats (
    stat_key VARCHAR(50) PRIMARY KEY,  -- 如 'total_orders'
    count_value BIGINT NOT NULL DEFAULT 0,
    version BIGINT NOT NULL DEFAULT 0   -- 乐观锁(可选)
);

2. 事务中同时更新计数表

-- 开启事务
START TRANSACTION;
 
-- 插入订单
INSERT INTO orders (id, order_no, amount, status) 
VALUES (1, 'NO001', 100.00, 1);
 
-- 更新计数表(原子+1)
UPDATE order_stats 
SET count_value = count_value + 1,
    version = version + 1
WHERE stat_key = 'total_orders';
 
-- 提交事务
COMMIT;

3. 查询总数

SELECT count_value FROM order_stats WHERE stat_key = 'total_orders';

多种维度的计数:

-- 按状态统计
CREATE TABLE order_stats_detail (
    stat_date DATE,
    status TINYINT,
    count_value BIGINT,
    PRIMARY KEY (stat_date, status)
);
 
-- 更新
INSERT INTO order_stats_detail (stat_date, status, count_value)
VALUES (CURDATE(), 1, 1)
ON DUPLICATE KEY UPDATE count_value = count_value + 1;

三、Redis计数方案(高性能,最终一致)

适用场景:

  • 超高并发
  • 可以接受短暂不一致
  • 计数丢失可容忍(或可恢复)

1. 基础版:纯Redis计数

写入时:

// 1. 先写数据库
orderMapper.insert(order);

// 2. 再更新Redis(允许失败)
redisTemplate.opsForValue()
    .increment("order:count:total", 1);

查询时:

// 直接从Redis读
Long count = redisTemplate.opsForValue()
    .get("order:count:total");

缺点:Redis更新失败会导致计数不准


2. 改进版:定时同步

方案A:binlog监听

// 监听MySQL binlog变化
@EventListener
public void handleOrderInsert(OrderInsertEvent event) {
    // 异步更新Redis
    stringRedisTemplate.opsForValue()
        .increment("order:count:total", 1);
}

方案B:定时任务补偿

@Scheduled(cron = "0 0/5 * * * ?")  // 每5分钟
public void syncOrderCount() {
    // 从数据库查询真实总数
    Long realCount = orderMapper.selectCount();
    // 更新到Redis
    redisTemplate.opsForValue()
        .set("order:count:total", realCount.toString());
}

3. 多维统计的Redis结构

// Hash结构:按日期统计
String key = "order:stats:2024-01-15";
redisTemplate.opsForHash()
    .increment(key, "total", 1);
redisTemplate.opsForHash()
    .increment(key, "paid", 1);

// Sorted Set:排行榜
redisTemplate.opsForZSet()
    .incrementScore("order:rank:category", "电子产品", 1);

四、三种方案对比

方案精确度性能复杂度推荐场景
计数表完全精确中(事务开销)低电商订单数、账户余额
纯Redis可能丢失/不一致极高低文章PV、点赞数
binlog同步最终一致高高大厂通用方案
定时同步最终一致高中中小项目折中方案

五、实战建议

根据数据量选择:

百万级以下 👉 直接用 COUNT(*) + 最小二级索引

千万级 👉 计数表方案(实现简单,绝对精确)

亿级以上 👉 计数表 + Redis缓存(Redis扛读流量,计数表保底)

示例:二级缓存架构

-- 查询逻辑
public Long getOrderCount() {
    // 1. 读Redis缓存
    Long count = redisTemplate.opsForValue().get("order:count");
    if (count == null) {
        // 2. 缓存失效,从计数表读
        count = orderStatsMapper.getCount("total_orders");
        // 3. 写回Redis(设置过期防雪崩)
        redisTemplate.opsForValue()
            .set("order:count", count, 1, TimeUnit.HOURS);
    }
    return count;
}

-- 更新逻辑(事务内)
@Transactional
public void createOrder(Order order) {
    // 1. 写业务表
    orderMapper.insert(order);
    
    // 2. 更新计数表
    orderStatsMapper.incrementCount("total_orders", 1);
    
    // 3. 删除Redis缓存(下次查询重建)
    redisTemplate.delete("order:count");
}

这个方案既保证了计数的绝对准确(计数表事务保证),又支持高并发查询(Redis缓存),是生产环境最稳妥的选择。

InnoDB的二级索引叶子节点只存索引字段+主键值,比聚簇索引小得多,遍历更快。没懂这句话, 为什么占用空间小的字段索引就更快呢