MySQL CPU超过100%解决办法 记一次项目优化过程

问题

1. mysql占用CPU超过100% 导致post 请求过慢网页打不开卡死等等问题
2. 某鹅服务器 4g cpu 8g内存 5m 带宽 买的3年套餐

解决办法

1. 通过jdbc sql监控 找到执行慢的语句
2. 分析原因 数据量过大 47万条筛选数据 并发访问导致时间过慢, 在不升级配置的情况下(升级配置狗都会)
3. linux top -c 查看占用 , 然后sql插入50W条数据,jmeter去进行50并发测试,发现执行最慢的接口3900ms 而且20秒之后cpu满载后端网页都打不开了!!!
4. 尝试去解决问题
   1. 建立索引
   2. 优化SQL语句
   3. 增加令牌桶,防止高并发的去同一时间读写SQL
   4. 未使用的可选备用方法 RabbitMQ+Redis 把SQL里面数据都放在Redis中储存 , 只在增删改的时候更新数据时刷新缓存。从而减少高频率查询， 刚方法作为替代方法。因为重新改SQL和代码逻辑并且目前已经足够业务使用。固不做考虑，但该方法可节省更多的cpu 提升性能。该方法适用于商城秒杀高频率查询少量插入修改删除的情况。
5. 问题解决 200并发的情况下 4s的查询降低到了100ms以内. 并且cpu占用率也仅在30上下浮动。仅仅只是把访问时间从300ms到800ms ，相比之前50并发满载，开始很快到后面3-4s一次请求，对于整个业务而言完全可以说是大提升。

总结

1. 按照逻辑，条件越简单速度越快
2. 所以再写的时候找出他的唯一值，建立索引，用索引做关联条件。
3. where后面的条件尽量不要反锁，能说明要的东西就行
4. 表的别名，像今天这个就没必要起了。反而会让他在判断一次占用时间
5. 结构简单，表少的情况，就用in，尽量不要用not in
6. 像今天这个，你只要一个表的数据，就可以用in内连接，不要用左右链接。
7. 如果是展示两个表的字段的话，就用左右连接
8. b表只是作为数据筛选的条件

1.MySQL

-- 创建索引
CREATE INDEX sys_orders_log_orderstype_requestid ON sys_orders_log ( orders_type, request_id );
-- 用不上的索引
CREATE INDEX sys_orders_ordersstate_lockstate_orderstype_goodstype ON sys_orders ( orders_state, lock_state, orders_type, goods_type );
CREATE INDEX sys_orders_lockstate ON sys_orders ( lock_state );
CREATE INDEX sys_orders_lockstate_ordersstate ON sys_orders ( lock_state , orders_state );
-- 分析索引 sys_orders_log
        EXPLAIN
        select *
        from sys_orders_log
        where orders_type = '0'
          and request_id = '60e71f48000000000100b25e';
-- 分析索引 sys_orders
        EXPLAIN
        select *
        from sys_orders
        where (orders_state = '0' or orders_state = '1')
          and lock_state = '0'
          and orders_type = '0'
          and goods_type = '0';
-- 分析索引 联合查询  
        EXPLAIN
        select a.*
        from sys_orders as a
                 left join(
            select *
            from sys_orders_log
            where orders_type = '0'
              and request_id = '60e71f48000000000100b25e') as b
                          on a.note_id = b.note_id
        where a.orders_state = '0'
          and a.lock_state = '0'
          and a.orders_type = '0'
          and a.goods_type = '0'
          and b.note_id is null;
-- 优化后的查询
select
	a.*                            
where a.lock_state = '0' 
	and a.orders_type = '0' 
	and a.goods_type = '0' 
	and a.orders_state in ('0', '1')
	and a.note_id not in (
	select
		note_id 
	from
		sys_orders_log 
	where
		request_id = '60e7235b000000000101e9e4' 
		and orders_type = '0' )
-- 优化前的查询
select a.*
from sys_orders as a
		 left join (
	select note_id
	from sys_orders_log
	where request_id = '60e7235b000000000101e9e4'
	  and orders_type = '0') as b
				   on a.note_id = b.note_id
where a.orders_state in ('0', '1')
  and a.lock_state = '0'
  and a.orders_type = '0'
  and a.goods_type = '0'
  and b.note_id is null;
-- 使用EXPLAIN查询之后确实又发现有变快

2.MyBatis

    <!--在用-->
	<select id="selectSysOrdersTaskApiPull" parameterType="com.alibaba.fastjson.JSONObject" resultMap="SysOrdersResult">
        select a.*
        from sys_orders a
        where a.lock_state = #{lockState}
          and a.orders_type = #{ordersType}
          and a.goods_type = #{goodsType}
          and a.orders_state in ('0', '1')
          and a.note_id not in (
            select note_id
            from sys_orders_log
            where request_id = #{requestId}
              and orders_type = #{ordersType})
        order by rand()
        limit 1
    </select>
    <!--过时-->
	<select id="selectSysOrdersTaskApiPull" parameterType="com.alibaba.fastjson.JSONObject" resultMap="SysOrdersResult">
        select a.*
        from sys_orders as a
                 left join(
            select note_id
            from sys_orders_log
            where request_id = #{requestId}
              and orders_type = #{ordersType}) as b
                          on a.note_id = b.note_id
        where (a.orders_state = '0' or a.orders_state = '1')
          and a.lock_state = #{lockState}
          and a.orders_type = #{ordersType}
          and a.goods_type = #{goodsType}
          and b.note_id is null
        order by rand()
        limit 1
    </select>
    <!-- 单个测试 sys_orders -->
    <select id="selectSysOrdersTaskApiPull" parameterType="com.alibaba.fastjson.JSONObject" resultMap="SysOrdersResult">
        select *
        from sys_orders
        where orders_state in ('0', '1')
          and lock_state = #{lockState}
          and orders_type = #{ordersType}
          and goods_type = #{goodsType}
        order by rand()
        limit 1
    </select>
    <!-- 单个测试 sys_orders_log -->
	<select id="selectSysOrdersTaskApiPull" parameterType="com.alibaba.fastjson.JSONObject" resultMap="SysOrdersResult">
        select note_id
        from sys_orders_log
        where request_id = #{requestId}
          and orders_type = #{ordersType}
    </select>