Mysql 问题

死锁

mysql for update 死锁问题排查

Mysql分页order by数据错乱重复

原因

//第二页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    5 , 5;
 
//第三页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    10 , 5
 
//第四页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  
LIMIT    15 , 5

分页数量正常，但这3条SQL的结果集是一样的，第二第三第四页的数据，一模一样，查了mysql官方文档发现，在mysql5.5版本之后，mysql针对排序有修改（5.5及之前版本不会)

If multiple rows have identical values in the ORDER BY columns, the
server is free to return those rows in any order, and may do so
differently depending on the overall execution plan. In other words,
the sort order of those rows is nondeterministic with respect to the
nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY
query with and without LIMIT may return rows in different orders.

解决

一旦 order by 的 colunm 有多个相同的值的话，结果集是非常不稳定

那怎么解决呢，其实很简单
就是 order by 加上唯一不重复的列即可，即在后面加上一个唯一索引

ORDER BY idnumber DESC , id DESC

//第二页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC ,
id DESC
LIMIT    5 , 5;
 
//第三页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC ,
id DESC
LIMIT    10 , 5
 
//第四页
SELECT id, createtime, idnumber, mac FROM `tblmacwhitelist`  
ORDER BY idnumber DESC  ,
id DESC
LIMIT    15 , 5

mysql hibernate Could not open JDBC Connection for transaction

报错 和 原因

Could not open JDBC Connection for transaction; 
nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 0, maxActive 20, creating 0, createErrorCount 2493

 Could not open JDBC Connection for transaction; 
 nested exception is java.sql.SQLTransientConnectionException: HikariCP

原因

maxActive / maxWait: 最大活动连接数，即连接池中最大连接数。
当程序超过此连接数再获取连接时，就处于排队状态， 当 排队时间超过maxWait，就会抛出异常。

MYSQL配置详解

initialSize="10" : 初始化连接，连接池启动时创建的初始化连接数量（默认值为0）

maxActive="80" : 最大活动连接，连接池中可同时连接的最大的连接数（默认值为8）

minIdle="10"  : 最小空闲连接，连接池中最小的空闲的连接数，低于这个数量会被创建新的连接
	（默认为0，该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm参数中的Xmn设置）

maxIdle="60"  :  最大空闲连接，连接池中最大的空闲的连接数，超过的空闲连接将被释放
	如果设置为负数表示不限制（默认为8个，maxIdle不能设置太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数上升超过maxIdle，而造成频繁的连接销毁和创建，类似于jvm参数中的Xmx设置)

maxWait="3000" : 从池中取连接的最大等待时间，单位ms.当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待（默认为无限）

validationQuery = "SELECT 1"  :  验证使用的SQL语句

testWhileIdle = "true"   : 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.

testOnBorrow = "false"  :  借出连接时不要测试，否则很影响性能。
		一定要配置，因为它的默认值是true。false表示每次从连接池中取出连接时，不需要执行validationQuery = "SELECT 1" 中的SQL进行测试。若配置为true,对性能有非常大的影响，性能会下降7-10倍。

timeBetweenEvictionRunsMillis = "30000"  : 每30秒运行一次空闲连接回收器，配置 timeBetweenEvictionRunsMillis = "30000"后，每30秒运行一次空闲连接回收器（独立线程）
		并每次检查3个连接，如果连接空闲时间超过30分钟就销毁。销毁连接后，连接数量就少了，如果小于minIdle数量，就新建连接，维护数量不少于minIdle，过行了新老更替。

minEvictableIdleTimeMillis = "1800000" : 池中的连接空闲30分钟后被回收

numTestsPerEvictionRun="3" : 在每次空闲连接回收器线程(如果有)运行时检查的连接数量

removeAbandoned="true" :  连接泄漏回收参数，当可用连接数少于3个时才执行

removeAbandonedTimeout="180" :  连接泄漏回收参数，180秒，泄露的连接可以被删除的超时值

HikariPool 配置

# 连接池中允许的最小连接数。缺省值：10
spring.datasource.hikari.minimum-idle=10
# 连接池中允许的最大连接数。缺省值：10
spring.datasource.hikari.maximum-pool-size=100
# 自动提交
spring.datasource.hikari.auto-commit=true
# 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
spring.datasource.hikari.idle-timeout=30000
# 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒
spring.datasource.hikari.max-lifetime=1800000
# 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒

注意

maxIdle值与maxActive值应配置的接近
当连接数超过maxIdle值后，刚刚使用完的连接（刚刚空闲下来）会立即被销毁。而不是想要的空闲M秒后再销毁起一个缓冲作用。若maxIdle与maxActive相差较大，在高负载的系统中会导致频繁的创建、销毁连接，连接数在maxIdle与maxActive间快速频繁波动，这不是想要的。高负载系统的maxIdle值可以设置为与maxActive相同或设置为-1(-1表示不限制)，让连接数量在minIdle与maxIdle间缓冲慢速波动。

timeBetweenEvictionRunsMillis建议设置值
minIdle要与timeBetweenEvictionRunsMillis配合使用才有用,单独使用minIdle不会起作用。

initialSize=“5”，会在tomcat一启动时，创建5条连接，效果很理想。但同时我们还配置了minIdle=“10”，也就是说，最少要保持10条连接，那现在只有5条连接，哪什么时候再创建少的5条连接呢？
1、等业务压力上来了， DBCP就会创建新的连接。
2、配置timeBetweenEvictionRunsMillis=“时间”,DBCP会启用独立的工作线程定时检查，补上少的5条连接。销毁多余的连接也是同理。

连接销毁的逻辑

DBCP的连接数会在initialSize - minIdle - maxIdle - maxActive 之间变化。变化的逻辑描述如下：
默认未配置initialSize(默认值是0)和timeBetweenEvictionRunsMillis参数时，刚启动tomcat时，连接数是0。当应用有一个并发访问数据库时DBCP创建一个连接。目前连接数量还未达到minIdle，但DBCP也不自动创建新连接已使数量达到minIdle数量（没有一个独立的工作线程来检查和创建）。随着应用并发访问数据库的增多，连接数也增多，但都与minIdle值无关，很快minIdle被超越，minIdle值一点用都没有。直到连接的数量达到maxIdle值，这时的连接都是只增不减的。 再继续发展，连接数再增多并超过maxIdle时，使用完的连接（刚刚空闲下来的）会立即关闭，总体连接的数量稳定在maxIdle但不会超过maxIdle。
但活动连接（在使用中的连接）可能数量上瞬间超过maxIdle，但永远不会超过maxActive。这时如果应用业务压力小了，访问数据库的并发少了，连接数也不会减少（没有一个独立的线程来检查和销毁），将保持在maxIdle的数量。

默认未配置initialSize(默认值是0)，但配置了timeBetweenEvictionRunsMillis=“30000”（30秒）参数时，刚启动tomcat时，连接数是0。马上应用有一个并发访问数据库时DBCP创建一个连接。目前连接数量还未达到minIdle，每30秒DBCP的工作线程检查连接数是否少于minIdle数量，若少于就创建新连接直到达到minIdle数量。
随着应用并发访问数据库的增多，连接数也增多，直到达到maxIdle值。这期间每30秒DBCP的工作线程检查连接是否空闲了30分钟，若是就销毁。但此时是业务的高峰期，是不会有长达30分钟的空闲连接的，工作线程查了也是白查，但它在工作。到这里连接数量一直是呈现增长的趋势。
当连接数再增多超过maxIdle时，使用完的连接(刚刚空闲下来)会立即关闭，总体连接的数量稳定在maxIdle。停止了增长的趋势。但活动连接（在使用中的连接）可能数量上瞬间超过maxIdle，但永远不会超过maxActive。
这时如果应用业务压力小了，访问数据库的并发少了，每30秒DBCP的工作线程检查连接(默认每次查3条)是否空闲达到30分钟(这是默认值)，若连接空闲达到30分钟，就销毁连接。这时连接数减少了，呈下降趋势，将从maxIdle走向minIdle。当小于minIdle值时，则DBCP创建新连接已使数量稳定在minIdle，并进行着新老更替。

mysql 中 boolean 动态判断

mybatis 使用如下方式

<if test="param.xxx== true">
    and r.xxx= 1
</if>
<if test="param.xxx== false">
    and r.xxx= 0
</if>

不要用占位符格式

<if test="param.xxx!= null">
    r.xxx = #{xxx},
</if>

if 中 boolean 不要使用占位符

SELECT m.menu_id , m.parent_id , m.`name` , opens FROM menu m
WHERE m.is_valid = 1 AND (m.type = 0 or m.type = 1) and m.menu_id IN (${menuId})
and is_ok = #{isOk}

opens是boolean类型

mybatis中当查询条件有Boolean类型的时候，为false时查询无效

isOk为false						  不能查询出is_ok对应的结果
直接在数据库中   is_ok = false     可以查出结果
isOk为false  或者 直接数据库中执行 is_ok = true      可以查出

isOk !=''     可以正确查出

总结

mybatis 的 if 判断里面最好 不要使用 boolean 值

mybatis 会默认把 空值 转为false
所以如果遇见 传空值，这个字段在 mybatis 里面就是 false 了

可以使用 数字类型 代替，但是 不要使用 0 作为参数。