# 常用SQL

# 查看当前数据库正在执行的SQL语句（information_schema数据库）

select DB,TIME,INFO from `PROCESSLIST` where COMMAND<>'Sleep' and DB is not null

# 修改字段名称

update [表名] set 字段名 = replace(与前面一样的字段名,'原本内容','想要替换成什么')

# 创建索引

-- 创建单值索引
ALTER TABLE `xz_master`.`ei_school_student` ADD INDEX `Idx_1`(`system_no`) USING BTREE;
-- 创建复合索引
ALTER TABLE `xz_master`.`ei_school_admin` ADD INDEX `Idx_account_state`(`account`, `state`) USING BTREE;

1
2
3
4

# when…then…批量更新

public Integer batchUpdateScore(Map<String, Float> topicScoreMap) {
    Assert.notEmpty(topicScoreMap, "topicScoreMap is required");
    StringBuffer sql = new StringBuffer("UPDATE PAPER_TOPIC SET SCORE = CASE topicId");
    StringBuffer ids = new StringBuffer();
    for (Map.Entry<String, Float> entry : topicScoreMap.entrySet()) {
        sql.append(" WHEN '").append(entry.getKey()).append("' THEN ").append(entry.getValue());
        ids.append(",'").append(entry.getKey()).append("'");
    }
    sql.append(" END WHERE topicId IN (").append(ids.substring(1)).append(")");
    return jdbcTemplate.update(sql.toString());
}

1
2
3
4
5
6
7
8
9
10
11

# duplicate插入更新

// 前提：id 是主键
@Resource
private JdbcTemplate jdbcTemplate;
@Test
public void test1(){
    String sql = "INSERT INTO duplicate (`id`, `name`, `score`) VALUES (?,?,?) on duplicate key update name = values(name), score = values(score)";
    jdbcTemplate.update(sql, 2, 11, 12);
}

1
2
3
4
5
6
7
8

# 数据库优化常用

# 连接数监控

show global status like 'Max_used_connections';
show variables like 'max_connections%';

1
2

使用率 = Max_used_connections ÷ max_connections
判断方法: 10% < 连接数使用率 < 85% .
- 若<10%，则表明最大连接数上限可能设置过高；
- 若>85%，则表明连接数不够用，或连接建立未释放.
产生原因&解决方法
- 连接数过低或过高(默认100)，均由配置不合理导致
  - 命令修改：msyql > set global max_connections = 1024;
  - 配置修改：vim /etc/my.cnf max_connections = 1024. 然后重启 mysql：service mysqld restart
- 慢查询导致IO阻塞，导致连接长时间不释放；
  - 有时慢查询不是及时能看到的，一般当一个SQL执行完才能判断是否为慢查询
- SQL执行完，连接未释放；
  - 直接看业务组件的文件描述符数（句柄数）就能知道

# 不合理的SQL导致CPU高

什么是不合理的SQL？
- 逻辑上存在死循环或者SQL执行会发生大量表扫描，均会导致CPU高.
监控指标
- 表扫描数(table_scan_count)

监控方法

msyql > show global status like 'handler_read%';
msyql > show global status like 'com_select';

1
2

表扫描数 = Handler_read_rnd_next ÷ Com_select
判断方法
- 若表扫描数 > 4000，则表明存在大量全表扫描，即索引建立不合理或未正确使用索引；
- 若表扫描数 < 4000，且CPU高，则建议查看存储过程中循环分支是否进入死循环.

# 慢查询

什么是慢查询 ?
- 执行耗时超过预期阈值的SQL，均为慢查询. 阈值最大不应超过1s，且原则上应该不超过100ms , 具体根据业务来定 .
监控指标
- 慢查询数(slow_queries)

监控方法

msyql > show global status like 'Slow_queries';

判断方法
- 慢查询数 = 0，备注：有时一些运维临时查询的SQL要过滤掉
产生原因查看
- 一般多为索引未建立或建立不合理，或查询数据量大，或者存在锁等待

修改配置文件my.cnf：

[mysqld]
slow_query_log=1
slow_query_log_file=/data/mysqldata/slow-query.log
long-query-time=1
log-queries-not-using-indexes

1
2
3
4
5

锁等待时间：
```
mysql > show status like '%lock%';
```
1

# 缓存命中率低

监控指标
- 查询缓存命中率(query_cache_hits)

监控方法

mysql > show global status like 'Com_select';
mysql > show global status like 'QCache%';

1
2

查询缓存命中率 = Qcache_hits ÷ (Qcache_hits+ Com_select)
判断方法
- 若查询缓存命中率 < 95%，则表明缓存大小可能设置过低（query_cache_size、query_cache_limit）或存在大量缓存碎片;

← 数据类型数据库SQL →