TheWe1

Appearance

MySQL-常见问题及解决方案 ​

连接数 ​

最大连接数设置：

Max_used_connections / max_connections * 100% ≈ 85% 最大连接数占上限连接数的85%左右，如果发现比例在10%以下，sql服务器连接数上限设置的过高了。

-- sql最大连接数
sql> show variables like 'max_connections'; 

-- 服务器响应的最大连接数
sql> show global status like ‘Max_used_connections’;
-- sql最大连接数
sql> show variables like 'max_connections'; 

-- 服务器响应的最大连接数
sql> show global status like ‘Max_used_connections’;

临时表 ​

每次创建临时表，Created_tmp_tables增加

如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加【Created_tmp_files表示sql服务创建的临时文件文件数】

比较理想的配置是： 　　Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

-- 查询临时表
sql> show global status like 'created_tmp%'; 
　　+-------------------------+---------+ 
　　| Variable_name | Value | 
　　+-------------------------+---------+ 
　　| Created_tmp_disk_tables | 21197 | 
　　| Created_tmp_files | 58 | 
　　| Created_tmp_tables | 1771587 | 
　　+-------------------------+---------+　　
　　
-- 只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。
sql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
　　+---------------------+-----------+ 
　　| Variable_name | Value | 
　　+---------------------+-----------+ 
　　| max_heap_table_size | 268435456 | 
　　| tmp_table_size | 536870912 | 
　　+---------------------+-----------+
-- 查询临时表
sql> show global status like 'created_tmp%'; 
　　+-------------------------+---------+ 
　　| Variable_name | Value | 
　　+-------------------------+---------+ 
　　| Created_tmp_disk_tables | 21197 | 
　　| Created_tmp_files | 58 | 
　　| Created_tmp_tables | 1771587 | 
　　+-------------------------+---------+　　
　　
-- 只有256MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。
sql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size'); 
　　+---------------------+-----------+ 
　　| Variable_name | Value | 
　　+---------------------+-----------+ 
　　| max_heap_table_size | 268435456 | 
　　| tmp_table_size | 536870912 | 
　　+---------------------+-----------+

表锁 ​

Table_locks_immediate表示立即释放表锁数

Table_locks_waited表示需要等待的表锁数，

如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁

-- 查询表锁情况
sql> show global status like 'table_locks%'; 
　　+-----------------------+-----------+ 
　　| Variable_name | Value | 
　　+-----------------------+-----------+ 
　　| Table_locks_immediate | 490206328 | 
　　| Table_locks_waited | 2084912 | 
　　+-----------------------+-----------+
-- 查询表锁情况
sql> show global status like 'table_locks%'; 
　　+-----------------------+-----------+ 
　　| Variable_name | Value | 
　　+-----------------------+-----------+ 
　　| Table_locks_immediate | 490206328 | 
　　| Table_locks_waited | 2084912 | 
　　+-----------------------+-----------+

表扫描 ​

计算表扫描率： 　　表扫描率 = Handler_read_rnd_next / Com_select 如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值(max=8MB)

sql> show global status like 'handler_read%'; 、
　　+-----------------------+-------------+ 
　　| Variable_name | Value | 
　　+-----------------------+-------------+ 
　　| Handler_read_first | 5803750 | 
　　| Handler_read_key | 6049319850 | 
　　| Handler_read_next | 94440908210 | 
　　| Handler_read_prev | 34822001724 | 
　　| Handler_read_rnd | 405482605 | 
　　| Handler_read_rnd_next | 18912877839 | 
　　+-----------------------+-------------+　　

-- 服务器完成的查询请求次数
sql> show global status like 'com_select'; 
　　+---------------+-----------+ 
　　| Variable_name | Value | 
　　+---------------+-----------+ 
　　| Com_select | 222693559 | 
　　+---------------+-----------+
sql> show global status like 'handler_read%'; 、
　　+-----------------------+-------------+ 
　　| Variable_name | Value | 
　　+-----------------------+-------------+ 
　　| Handler_read_first | 5803750 | 
　　| Handler_read_key | 6049319850 | 
　　| Handler_read_next | 94440908210 | 
　　| Handler_read_prev | 34822001724 | 
　　| Handler_read_rnd | 405482605 | 
　　| Handler_read_rnd_next | 18912877839 | 
　　+-----------------------+-------------+　　

-- 服务器完成的查询请求次数
sql> show global status like 'com_select'; 
　　+---------------+-----------+ 
　　| Variable_name | Value | 
　　+---------------+-----------+ 
　　| Com_select | 222693559 | 
　　+---------------+-----------+

查某项重复的数据 ​

sql
SELECT * FROM XX表 WHERE 重复项 in(SELECT 重复项 FROM XX表 GROUP BY 重复项 HAVING count(1) >= 2)
SELECT * FROM XX表 WHERE 重复项 in(SELECT 重复项 FROM XX表 GROUP BY 重复项 HAVING count(1) >= 2)

group_concat函数使用 ​

sql
select name,group_concat(code) from `user` group by name;
select name,group_concat(code) from `user` group by name;

使用group_concat函数，可以轻松的把分组后name相同的数据拼接到一起，组成一个字符串，用逗号分隔。

如何不批量插入重复数据?

insert ignore into插入数据 ​

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。

这种方法很简便，但是有一种可能，就是插入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了

sql
INSERT IGNORE INTO user (name) VALUES ('telami')
INSERT IGNORE INTO user (name) VALUES ('telami')

on duplicate key update更新数据 ​

当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。

这种方法有个前提条件,需要插入的约束，是主键或者唯一约束（在你的业务中那个要作为唯一的判断就将那个字段设置为唯一约束也就是unique key）。

xml
//mobile_number有唯一约束
<insert id="batchSaveUser" parameterType="list">
insert into user (id,username,mobile_number)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id},
#{item.username},
#{item.mobileNumber}
)
</foreach>
ON duplicate KEY UPDATE id = id
</insert>
//mobile_number有唯一约束
<insert id="batchSaveUser" parameterType="list">
insert into user (id,username,mobile_number)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id},
#{item.username},
#{item.mobileNumber}
)
</foreach>
ON duplicate KEY UPDATE id = id
</insert>
sql
INSERT INTO user (name) VALUES ('telami') ON duplicate KEY UPDATE id = id
INSERT INTO user (name) VALUES ('telami') ON duplicate KEY UPDATE id = id

insert … select … where not exist插入数据 ​

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件；

这种方法其实就是使用了sql的一个临时表的方式，但是里面使用到了子查询，效率受影响。

sql
INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)
INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

replace into插入数据 ​

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

sql
REPLACE INTO user SELECT 1, 'telami' FROM books
REPLACE INTO user SELECT 1, 'telami' FROM books

通过unsigned强制修改主键字段 ​

sql
-- 当主键无法修改时 加上 unsigned 标记
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键'
-- 当主键无法修改时 加上 unsigned 标记
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键'

sql查询日期格式化 ​

sql
SELECT 
*
FROM
order_title
WHERE
DATE_FORMAT(create_date,'%Y-%m') in('2021-01','2021-02','2021-03')
SELECT 
*
FROM
order_title
WHERE
DATE_FORMAT(create_date,'%Y-%m') in('2021-01','2021-02','2021-03')

子查询减少回表SQL优化 ​

sql
-- 待优化SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
-- 待优化SQL
SELECT 各种字段
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
sql
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN(
SELECT  子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键
-- 优化后SQL
SELECT 各种字段
FROM `table_name` main_tale
RIGHT JOIN(
SELECT  子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键
sql
-- 示例
#原SQL
SELECT * FROM test_user WHERE age>50 LIMIT 100000,10;
#优化后SQL
SELECT *
FROM test_user t0
RIGHT JOIN(
SELECT user_id
FROM test_user
WHERE age>50 LIMIT 100000,10
) t1 ON t1.user_id = t0.user_id;
-- 示例
#原SQL
SELECT * FROM test_user WHERE age>50 LIMIT 100000,10;
#优化后SQL
SELECT *
FROM test_user t0
RIGHT JOIN(
SELECT user_id
FROM test_user
WHERE age>50 LIMIT 100000,10
) t1 ON t1.user_id = t0.user_id;

select 1进行SQL查找是否"存在" ​

sql
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1
java
// java写法:  
Integer exist = xxDao.existXxxxByXxx(params);  
if ( exist != NULL ) {  
    //当存在时，执行这里的代码  
} else {  
    //当不存在时，执行这里	的代码  
}
// java写法:  
Integer exist = xxDao.existXxxxByXxx(params);  
if ( exist != NULL ) {  
    //当存在时，执行这里的代码  
} else {  
    //当不存在时，执行这里	的代码  
}

sql日期之间比较 ​

sql
-- 对于比较的两个时间，时间小的放在前面，时间大的放在后面
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
-- 对于比较的两个时间，时间小的放在前面，时间大的放在后面
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

sql添加创建时间和修改时间 ​

-- 创建的时候设置当前时间
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
-- 创建的时候设置当前时间
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
-- 更新的时候设置更新时间为当前时间
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- 更新的时候设置更新时间为当前时间
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

查询表信息 ​

sql
-- 查询表信息
show table status like '表名'
-- 查询表信息
show table status like '表名'

SQL 语句中 left join 后用 on 还是 where 的区别 ​

在使用left jion时，on和where条件的区别如下：

1.on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2.where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

left join,right join,full join 有相同的特殊性，不管on上的条件是否为真都会返回left或right表中的记录
full join 则具有left和right的特性的并集。
而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
在使用left jion时，on和where条件的区别如下：

1.on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2.where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

left join,right join,full join 有相同的特殊性，不管on上的条件是否为真都会返回left或right表中的记录
full join 则具有left和right的特性的并集。
而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

MySQL 添加虚拟唯一键 ​

`unique_key` varchar(500) GENERATED ALWAYS AS (md5(concat_ws(if((`is_deleted` = 1),`id`,0),_utf8mb4'_',`xxx`))) VIRTUAL COMMENT '虚拟唯一键'

UNIQUE KEY `uniq_xxx` (`unique_key`),
`unique_key` varchar(500) GENERATED ALWAYS AS (md5(concat_ws(if((`is_deleted` = 1),`id`,0),_utf8mb4'_',`xxx`))) VIRTUAL COMMENT '虚拟唯一键'

UNIQUE KEY `uniq_xxx` (`unique_key`),

慢查询日志使用 ​

sql
-- 查看是否开启慢查询日志
sql> show variables like 'slow_query_log'  
-- 设置慢查询日志的位置
sql> set global slow_query_log_file=' /usr/share/sql/sql_log/sql-slow.log' 
-- 开启慢查询日志
sql> set global log_queries_not_using_indexes=on;
-- 设置大于1秒钟的数据记录到慢日志
sql> set global long_query_time=1;
-- 查看是否开启慢查询日志
sql> show variables like 'slow_query_log'  
-- 设置慢查询日志的位置
sql> set global slow_query_log_file=' /usr/share/sql/sql_log/sql-slow.log' 
-- 开启慢查询日志
sql> set global log_queries_not_using_indexes=on;
-- 设置大于1秒钟的数据记录到慢日志
sql> set global long_query_time=1;