mysql实战操作总结

1、问题描述

关于mysql操作,记录下;

2、问题说明

1.停止正在执行的sql

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

show processlist;

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

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
);

用的范围分区,比如: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';  

(4)分区操作

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

alter table zy_sjgl truncate partition p20221101; 

删除分区,分区直接就删除了,跟drop table一个概念:

alter table bm_scenes_data_reminder drop partition p20210104; 

增加分区,这里有个点,新增分区,假如上次第一次分区有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);

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

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

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

select * from performance_schema.events_statements_current

然后再kill id

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
        </where>

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

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


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

posted @ 2022-11-26 13:27  软件老王  阅读(70)  评论(0编辑  收藏  举报