Select查询

1. Select语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

其中into_option表示：

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

2. 普通查询

mysql> select * from sku_attr_value limit 10;
+----+---------+----------+--------+---------------+--------------+---------------------+--------------+
| id | attr_id | value_id | sku_id | attr_name     | value_name   | create_time         | operate_time |
+----+---------+----------+--------+---------------+--------------+---------------------+--------------+
|  1 |     106 |      176 |      1 | 手机一级1     | 安卓手机     | 2021-12-14 00:00:00 | NULL         |
|  2 |     107 |      177 |      1 | 二级手机2     | 小米         | 2021-12-14 00:00:00 | NULL         |
|  3 |      23 |       83 |      1 | 运行内存      | 8G           | 2021-12-14 00:00:00 | NULL         |
|  4 |      24 |       82 |      1 | 机身内存      | 128G         | 2021-12-14 00:00:00 | NULL         |
|  5 |     106 |      176 |      2 | 手机一级      | 安卓手机     | 2021-12-14 00:00:00 | NULL         |
|  6 |     107 |      177 |      2 | 二级手机      | 小米         | 2021-12-14 00:00:00 | NULL         |
|  7 |      23 |       83 |      2 | 运行内存      | 8G           | 2021-12-14 00:00:00 | NULL         |
|  8 |      24 |      166 |      2 | 机身内存      | 256G         | 2021-12-14 00:00:00 | NULL         |
|  9 |     106 |      176 |      3 | 手机一级      | 安卓手机     | 2021-12-14 00:00:00 | NULL         |
| 10 |     107 |      177 |      3 | 二级手机      | 小米         | 2021-12-14 00:00:00 | NULL         |
+----+---------+----------+--------+---------------+--------------+---------------------+--------------+
10 rows in set (0.00 sec)

3. 查询带排序

## gmall_v4.user_info有3737933条记录
mysql> SELECT * FROM gmall_v4.user_info order by user_level desc limit 100 ;

发现查询比较慢，如果不能利用索引，可以调整排序的内存大小：

## 查询当前排序的状态，Sort_merge_passes表示磁盘排完序到内存中合并的次数是1203
mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 1203  |
| Sort_range        | 0     |
| Sort_rows         | 100   |
| Sort_scan         | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
## 默认的排序内存大小是256K, 它支持会话级别参数设置
mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)
## 调整为256M
mysql> set sort_buffer_size=256*1024*1024;
## 清空状态记录
mysql> flush status;
mysql> SELECT * FROM gmall_v4.user_info order by user_level desc limit 100 ;
## 查询状态，发现排序merge的次数为0，说明直接内存中排完了序
mysql> show status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 100   |
| Sort_scan         | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

4. 查询带分组

mysql> SELECT date_format(create_time, '%Y%m') , count(*) 
FROM user_info 
GROUP BY date_format(create_time, '%Y%m') ;
## 查询当前的临时表内存大小
mysql> show variables like '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.00 sec)