目录
一 mysql常见问题
1.Server returns invalid timezone 时区不对导致
解决:mysql> set global time_zone= '+8:00';
生产配置一般会在url加上时区如:
jdbc:mysql://ip:port/xk?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
2. Data too long for column 表字段定义过短导致,调大字段长度或者类型
alter table table_name modify column_name varchar(100) comment '校验类型';表较大时,建议使用gh-ost方式进行无锁表更新;or临时表方式 ?
3.1064 syntax
执行存储过程的话:将内容转义
delimiter // 存储过程 //如是sql语句:则一般是特殊字符导致
4 ORA-00907 ibatis中的报错 使用属性加[]解决 (ids为逗号分割字符串)
<iterate property="ids" conjunction="," open="(" close=")">
#ids[]#
</iterate>
二、常用
字符串拼接:
update table_name set column_name=concat(column_name,'xxxx') where id ='xx';
日期格式化
date_format(create_time, '%Y-%m-%d %H:%i:%S') create_time
create_time datetime default now() comment '创建时间', create_by varchar(60) default 'system' not null comment '创建人', update_time datetime default now() on update now() comment '更新时间', update_by varchar(60) comment '更新建人'
当前时间减去秒 分钟、小时
select * from check_config where create_time<=now() -interval 6 secondselect * from check_config where create_time<=now() -interval 6 minuteselect * from check_config where create_time<=now() -interval 6 hour
tips:
若是mybatis mybatis-plus xml中比较数字建议用.toString()
<if test="status!=null and status=='0'.toString()"> status=#{status} </if>
结合类则用size判断 <if test="xxlist!=null and xxlist.size()>0"></if>
字符集查看
show variables like '%char%';
查看连接数,若遇到连接不够用的情况,可以用jstack -l pid > /tmp/xx.txt 下载:sz /tmp/xx.txt 看看具体线程池是不是过大
#当前全部的连接数 show full processlist; #查询最大连接数 show variables like 'max_connections'; -- 设置最大连接数,根据需要进行设置 set global max_connections=200; show variables like '%max_connections%';
修改表字段or删除
alter table table_name add column xx varchar(200) comment '校验模型' after xx0; alter table table_name rename column xx to xx2;alter table table_name drop xx;
索引创建or删除
create index idx_xx on table_name(xx); create unique index uq_xx on table_name(xx);drop index idx_xx on table_name;
插入语句
忽略插入,若遇到主键冲突则进行忽略 INSERT IGNORE INTO table_name ...重复更新,尽量少于大量插入数据会导致锁表
INSERT INTO check_config on duplicate key update xx=values(xx)insert into ... select 插入方式(数据多导致锁表,少量数据备份可以使用)
insert into xx select * from xx2 on duplicate key update column_name=values(column_name);
特殊符号转义处理:mybatis、mybati-splus中的使用 <![CDATA[ 内容 ]]>
idea 简化sql快捷键(前提是通过idea右侧导航栏可以配置,如遇到mysql-connector问题,可以通过maven先下载下来再通过本地添加方式解决) 如图
类似plsql中可以是用的快捷键命令一样:如SF空格,则产生select * from
整理表:(常规delete数据并不能把数据存储的数据清理掉,通过整理表语句则可以但建议不要少操作,整理表引入的问题可以搜索关键字查询) 比如清理一下历史数据可能就用的上了
optimize table user_address;
删除表
truncate table xx; drop table if exists xx;
查锁
show engine innodb status; show status like 'innodb_row_lock%'; select * from information_schema.innodb_trx; SELECT * from information_schema.`PROCESSLIST`;
mybati-plus 整合springboot maven
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.28</version> </dependency>
三、使用注意
锁表问题:不建议在主要业务表上使用 update xx select xx from xx 语句进行更新
in某个字段更新时,主键内容控制越少越好,尽量不超过200-300 ?:update xx set xx=xxx where id in() 语法 in的数据量尽量越少越好,多了会导致锁升级问题?
insert导致不恰当插入也会引发死锁问题(唯一索引和联合索引),另外一些大事务未提交重复消费消费也会偶发insert死锁问题,关于insert死锁问题可以查看网上其他文章或者mysql官网看看
查看锁超时时间配置:SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
索引:常用字段建,建议增加状态值来区分数据,不是越多越好 在“时间和空间上做个取舍” 时间换空间 空间换时间
查询效率方面:避免在索引列上进行计算 组合索引最左匹配规则 ,其他该加的索引加索引
mysql回表查询:通过索引定为主键,通过主键找到具体的数据; 如果查询列就是索引列,那么可不通过回表查询二次定为数据(即索引覆盖 索引优化中的部分 explain 解释计划中看)
通过如下测试代码看具体情况:
SELECT user_name FROM user_address where user_name='u2'; SELECT * FROM user_address where user_name='u2';
不走回表查询,直接索引覆盖 (Using index)
未使用索引覆盖,通过回表查询
特殊的主键id: 如果是自增主键,既是数据上的连续又是物理上的连续
非自增主键则会存在页的分裂:具体可以参考其他文章
四 、慢sql定位
主要是开启慢sql日志,另外知道是哪里则用explain解释执行一下看看具体走的索引
windows环境
show variables like '%slow_query_log%';
set global slow_query_log = on;
# DESKTOP-R3DF1QE-slow.log
set global slow_query_log_file="C:/data/mysql/slow_query_log.log";
show variables like '%long_query_time%';
# 10.000000 默认10秒
set global long_query_time = 1.000000;
如本地查询的慢sql 测试:
select sleep(2);
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.18 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 2022-09-28T05:45:03.937414Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 8
# Query_time: 11.003896 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
use xk;
SET timestamp=1664343892;
/* ApplicationName=IntelliJ IDEA 2022.1 */ select sleep(11);
# Time: 2022-09-28T08:44:23.059353Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 16
# Query_time: 2.001915 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1664354661;
/* ApplicationName=IntelliJ IDEA 2022.1 */ select sleep(2);
linux环境
退出后二次登录查看
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> set global long_query_time = 1.000000;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
再次进入
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.00 sec)
如果是一些云平台则会有导出mysql文件的功能
至于多少算是慢sql取决于实际业务
五.mysql关于锁的官网描述
MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking