mysql 语句/常用函数 | 问题排查

show full processlist 用来查看当前线程处理情况

一般用到 show processlist 或 show full processlist 都是为了查看当前 mysql 是否有压力，都在跑什么语句，当前语句耗时多久了，有没有什么慢 SQL 正在执行之类的

可以看到总共有多少链接数，哪些线程有问题(time是执行秒数，时间长的就应该多注意了)，然后可以把有问题的线程 kill 掉，这样可以临时解决一些突发性的问题。

Id：链接mysql 服务器线程的唯一标识，可以通过kill来终止此线程的链接。

User：当前线程链接数据库的用户

Host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户

db: 线程链接的数据库，如果没有则为null

Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）

Time: 线程处在当前状态的时间，单位是秒

State：显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成

Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句

-- 查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc 

1.查询不是sleep或者有状态的sql

select * from `information_schema`.processlist where command !='Sleep' or state !='';

2.查询运行中的事务
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

3.查看死锁

SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time
, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id
, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;

MySQL中group_concat函数
完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

批量更新:

UPDATE b_cover  SET  

episode_updated = CASE cover_id 

WHEN cover_id THEN xxx END,";

subtype_id = CASE cover_id

WHEN cover_id THEN xxx  END

启用MySQL查询缓存

查看查询缓存情况：

mysql> show variables like '%query_cache%'; 

 

 

MySQL 的相关优化

1. MySQL 性能优化：组成、表的设计

1. 开启查询缓存。避免某些 SQL 函数直接在 SQL 语句中使用，从而导致 Mysql 缓存失效。

2. 避免画蛇添足。目的是什么就取什么，例如某个逻辑是只需要判断是否存在女性，若是查到了一条即可，勿要全部都查一遍，此时要善用 limit。

3. 建合适的索引。所以要建在合适的地方，合适的对象上。经常操作 / 比较 / 判断的字段应该建索引。

4. 字段大小合宜。字段的取值是有限而且是固定的，这种情况下可以用 enum，IP 字段可以用 unsigned int 来存储。

5. 表的设计。垂直分割表，使得固定表与变长表分割，从而降低表的复杂度和字段的数目。

2. SQL 语句优化：避免全表扫描

1. 建索引：一般在 where 及 order by 中涉及到的列上建索引，尽量不要对可以重复的字段建索引。

2. 尽量避免在 where 中使用 !（<>）或 or，也不要进行 null 值判断。

3. 尽量避免在 where 中对字段进行函数操作、表达式操作。

4. 尽量避免使用 like- %，在此种情况下可以进行全文检索。