MySQL 使用须知

以你之姓@ 2023-10-08 11:09 28阅读 0赞

#### 文章目录 ####

*  1. 索引的操作
 *  2. 查询优化
 *  3. 锁排查
 *   *   *  3.1 问题描述
         *  3.2 排查方法
 *  4. UNION 和 UNION ALL
 *  5. JSON\_EXTRACT() 函数解析 json 字符串

## 1. 索引的操作 ##

1.  查看表的字段结构
    
    > desc user;
2.  查看建表语句，包括表上的索引
    
    > SHOW CREATE TABLE user \\G;
3.  在表字段上创建索引
    
    > 建立普通索引:  
    > `create index idx_user_host on user(Host,User);`  
    > `alter table user add index idx_host(Host);`  
    > 建立唯一索引:  
    > `create unique index idx_user_host on user(Host,User);`  
    > `alter table user add unique index idx_host(Host);`
4.  输出表上建立的索引
    
    > show index from user;
5.  删除表上的索引
    
    > drop index idx\_user\_host on user;
6.  指定查询使用的索引
    
    > // 指定索引，但是查询计划并不一定会使用  
    > select \* from user `use index( index idx_user_host)` where …  
    > // 强制指定索引，查询计划一定会使用  
    > select \* from user `force index( index idx_user_host)` where …

## 2. 查询优化 ##

1.  `group by/order by` 完全依赖索引，也就是其后的字段及字段顺序必须完全与某个索引一致，否则会产生临时表影响性能
2.  `join`优化：小表驱动大表。建索引时，左连接左边都有，索引加右表字段；右连接右边都有，索引加左表字段
    
    > MySQL 联表的内部实现采用`嵌套循环联接(nested loop join)`算法，其实质是通过驱动表的结果集作为循环基础数据，一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果，直观的理解就是多重嵌套循环。因此驱动表的选择很重要，驱动表的数据量小可以显著降低开销，开销节省的原理可参考[Java 细节汇总(1): 嵌套 for 循环性能比较][Java _1_ _ for]
3.  `in` 关键字返回真实数据，`exists` 关键字只返回 bool 值
4.  `group by` 在 MySQL 中会默认产生 `order by` 排序，如果 group by 语句不指定 order by 条件会导致无谓的排序，如果不需要排序建议添加 ‘order by null’

## 3. 锁排查 ##

#### 3.1 问题描述 ####

程序执行报以下错误，看上去是锁等待太久了，需要排查 锁问题

> Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

#### 3.2 排查方法 ####

1.  查看是否有锁表发生
    
        // 查看正在使用中的 db 和 table
        show open tables where in_use > 0;
        // 查看所有进程，找到正在使用目标 db 的进程
        show processlist
        // 干掉该进程
        kill <id>
2.  查看 db 的状态，检查死锁发生的情况
    
        show engine innodb status
3.  查看锁等待状态
    
        // 查看当前运行的事务
        select * from information_schema.INNODB_TRX;
        // 查看当前的锁
        select * from information_schema.innodb_locks; 
         // 查看等待的锁
        select * from information_schema.innodb_lock_waits;

## 4. UNION 和 UNION ALL ##

UNION 的语法如下，UNION ALL 与此类似:

> \[SQL 语句 1\]  
> `UNION/UNION ALL`  
> \[SQL 语句 2\]

UNION 和 UNION ALL 关键字都是将两个结果集合并为一个，但两者有所不同:

> 1.  对重复结果的处理  
>     UNION 在进行表链接后会筛选掉重复的记录，Union All 不会去除重复记录
> 2.  对排序的处理  
>     Union 将会按照字段的顺序进行排序，UNION ALL只是简单的将两个结果合并后就返回

从效率上说 UNION ALL 要比 UNION 快不少，所以如果确认合并的两个结果集中不包含重复数据且不需要排序的话，那 UNION ALL 是更好选择

## 5. JSON\_EXTRACT() 函数解析 json 字符串 ##

MySQL 从 5.7 版本开始支持 json 解析，以下为解析用例，感兴趣的读者可前往[官方传送门][Link 1]

<table> 
 <thead> 
  <tr> 
   <th align="left">取值语法</th> 
   <th align="left">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left"><code>‘$.*’</code></td> 
   <td align="left">返回 json 字符串中所有键的值</td> 
  </tr> 
  <tr> 
   <td align="left"><code>‘$.content’</code></td> 
   <td align="left">返回 json 字符串中指定键为 content 的值</td> 
  </tr> 
  <tr> 
   <td align="left"><code>‘$**.content’</code></td> 
   <td align="left">返回 json 字符串所有键值对中键为 content 的值数据</td> 
  </tr> 
  <tr> 
   <td align="left"><code>‘$.content[*]’</code></td> 
   <td align="left">返回键为 content 的数组所有的内容，可使用数组下标的方式<code>‘$.content[0]’</code> 访问数组第一个元素</td> 
  </tr> 
 </tbody> 
</table>

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
    +---------------------------------------------------------+
    | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
    +---------------------------------------------------------+
    | [1, 2, [3, 4, 5]]                                       |
    +---------------------------------------------------------+
    mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
    +------------------------------------------------------------+
    | JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
    +------------------------------------------------------------+
    | [3, 4, 5]                                                  |
    +------------------------------------------------------------+
    mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
    +---------------------------------------------------------+
    | JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
    +---------------------------------------------------------+
    | [1, 2]                                                  |
    +---------------------------------------------------------+

一个进阶的示例如下:

> 查询 JSON 字符串格式的字段中 key 为 history 的数组中包含 1639985928020 的记录，并取这个 history 数组的最后一个元素

SELECT
    	a1.session_id AS session_id,
    	JSON_EXTRACT(
    		a1.context_data,
    		CONCAT(
    			"$.history[",
    			JSON_LENGTH( a1.context_data -> "$.history" ) - 1,
    			"]" 
    		) 
    	) AS last_node 
    FROM
    	dialog_session AS a1
    	JOIN (
    	SELECT
    		session_id,
    		phone_no 
    	FROM
    		dialog_session 
    	WHERE
    		created_at > "2022-01-04 00:00:00" 
    		AND bot_id = 1 
    	GROUP BY
    		phone_no 
    	HAVING
    		Count( * )> 1 
    	ORDER BY
    	NULL 
    	) AS a2 ON a1.session_id = a2.session_id 
    WHERE
    	JSON_CONTAINS( JSON_EXTRACT( a1.context_data, "$.history" ), '1639985928020' ) 
    	LIMIT 1000

[Java _1_ _ for]: https://blog.csdn.net/weixin_45505313/article/details/100746528#4__for__71
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/json.html