mysql实战操作总结

1、问题描述

关于mysql操作，记录下；

2、问题说明

1.停止正在执行的sql

数据量太大，数据库没反应，用的navicat，就在查询页面，执行：

show processlist;

---会显示对应的查询sql找到最前面是id，执行kill id就可以了
kill  id 
1
2
3
4

2.mysql分区

分区可以在不改变表名，逻辑不变的情况下，增加mysql处理数据的能力；

分区网上有很多介绍，具体概念自己查吧，我们项目中是数据量太大，要按照日期分区，分区键是：varchar类型的timestamp，网上的用TO_DAYS,unitime什么的都用不了，用的COLUMNS创建的；

说明：

（1） 创建前首先要将分区键设置成主键/联合主键，假如表中已经有id作为主键了，再设置createDate主键就可以了（联合主键）；

（2）分区创建sql

alter table zy_sjgl partition by range COLUMNS(createDate) (   
partition p20221101 values less than ('2022-11-01'),
partition p20221102 values less than ('2022-11-02'),
partition p20221103 values less than ('2022-11-03'),
partition px values less than maxvalue
);

1
2
3
4
5
6
7

用的范围分区，比如：2022-11-01的数据，实际进的p20221102这个分区；

mysql5.7以前最多分1024个，5.7（含）以后最多8196个（亲测有效）

（3）查询各个分区数据情况

自己可以插入测试表数据，可以看下效果

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='zy_sjgl';  
1
2
3
4
5
6
7
8

（4）分区操作

清空分区中数据，分区还在，跟truncate table 一个概念，清理数据：

alter table zy_sjgl truncate partition p20221101; 
1

删除分区，分区直接就删除了，跟drop table一个概念：

alter table bm_scenes_data_reminder drop partition p20210104; 
1

增加分区，这里有个点，新增分区，假如上次第一次分区有max，简单说新增的分区less than，不能中间插入，只能最后插入，假如有比他大的分区，比如你要插入分区5，目前分区已经有6了，插入不成功，需要把6删除了，5和6一起创建，新增分区语句：

先删：
ALTER TABLE zy_sjgl DROP PARTITION px;

后建：
ALTER TABLE zy_sjgl ADD partition (partition p20230701 values less than ('2023-07-01'));
ALTER TABLE zy_sjgl ADD partition (partition px values less than maxvalue);
1
2
3
4
5
6

3、mysql表锁了，Waiting for table metadata lock

mysql表锁了，服务器重启，表正在插入数据，锁了，通过查看发现：Waiting for table metadata lock

有时候通过：show processlist;找不到，可以试试下面这个：

select * from performance_schema.events_statements_current

然后再kill id
1
2
3

4、msyql查询增加序号

用户导出的csv或者excel需要带个序号，方便查看，方式：select 前面用(@i:=@i+1) AS ‘序号’，from后跟(SELECT @i:=0) AS itable，需要特别说明，sql中有groupby的话，序号要放到最外面，否则序号会断，简单说比如1、2、3一个组，最终分组导入的时候就剩下1了，序号就跳了，用select包一下再排序就好了。

select (@i:=@i+1) AS '序号', t2.* from (select
        *
        from zy_sjgl t1
            group by t1.SJGL_SJC
            order by t1.SJGL_SJC asc) t2,(SELECT @i:=0) AS itable
        
1
2
3
4
5
6

5.mysql服务器参数配置

服务器参数优化，并不一定符合所有人，参数配置在my文件中：

table_open_cache=5120
max_allowed_packet=1024M;
innodb_buffer_pool_size=8192M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=3 
innodb_lock_wait_timeout=120 
thread_cache_size=300
bulk_insert_buffer_size=1024M
1
2
3
4
5
6
7
8

innodb_buffer_pool_size，缓冲池大小，Innodb类型数据库，内存够的情况下，大一些；

----20221128新加
varchar类型timestamp，转日期格式，业务要求是按日统计日温差，数据库中varchar类型的时间戳，使用str_to_date(t1.SJGL_SJC,‘%Y-%m-%d’)就可以；

str_to_date(t1.SJGL_SJC,'%Y-%m-%d') >= str_to_date('2022-11-01 00:00:00','%Y-%m-%d')  
and str_to_date(t1.SJGL_SJC,'%Y-%m-%d') <= str_to_date('2022-11-10 10:11:00','%Y-%m-%d') 
1
2

---

更多信息请关注公众号：「软件老王」，关注不迷路，软件老王和他的IT朋友们，分享一些他们的技术见解和生活故事。