1 开启event_scheduler
1.1 找到my.cnf的位置
find / |grep my.cnf
1.2 修改my.cnf配置
vim ./usr/my.cnf
[mysqld]
event_scheduler=ON
1.3 restart mysql
service mysql restart.
2 创建分区表
2.1 创建分区表
DROP TABLE IF EXISTS `tb_report`;
CREATE TABLE IF NOT EXISTS `tb_report` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`gen_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment "for partition used",
`time` datetime NOT NULL DEFAULT '1972-01-01 00:00:00' comment "for service used",
`dir` tinyint(1) NOT NULL DEFAULT '0',
`domain_id` int(10) not null default 0,
`accept` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'TOTAL = accept + reject',
`reject` int(10) unsigned NOT NULL DEFAULT '0' ,
PRIMARY KEY (id,gen_time),--partion column must in key
INDEX (domain_id)
) ENGINE=innodb DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS(gen_time))
(
PARTITION pbasic VALUES LESS THAN (0)
);
2.2 定时创建新分区和删除过期分区
delimiter $$
DROP EVENT if exists `event_auto_partition`;$$
CREATE EVENT `event_auto_partition` ON SCHEDULE EVERY 1 day STARTS '1972-01-01 00:00:00' ON COMPLETION PRESERVE DO
begin
call create_partition_per_day(DATE_ADD(now(), interval 0 month),'tb_report');
call create_partition_per_day(DATE_ADD(now(), interval 1 month),'tb_report');
call drop_partition_by_month(DATE_ADD(now(), interval -3 month),'tb_report');
end;
$$
ps：create_partition_per_day and drop_partition_by_month need your implementation。
2.3 对于分区表，MySQL不支持命令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE
那么当一个分区表经过多次改变或分区中删除了大量的行留下很多碎片时怎么解决呢？msyql 有个代替方案，ALTER TABLE ... OPTIMIZE PARTITION。
ALTER TABLE t1 OPTIMIZE PARTITION p；
在一个给定的分区表上使用“OPTIMIZE PARTITION”等同于在那个分区上运行CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。
这是个低效的做法，最好的做法是按照日期直接删除不再使用的partition。
3 导入的sql文件过大,导致连接超时，可以重新设定如下的参数：
set global max_allowed_packet=100 000 000;
set global net_buffer_length=100000;
SET GLOBAL interactive_timeout=28800 000;
SET GLOBAL wait_timeout=28800000
以上语句的解释：
max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行
interactive_timeout = 10; 对后续起的交互链接有效；
wait_timeout 对当前交互链接有效；
4 Mysql upsert 可以提交的语句有无限制？
这个问题的意思是，在一个事务中，你可以提交多少insert/update?之前我认为可以提交的sql总量有有限制的，经过验证，mysql对此完全没有限制，其做法是：
Mysql将需要提交的数据放在redo log/undo log中（错了，是放在缓冲池中的，受制于参数innodb_buffer_pool_size），redo log受制于参数innodb_log_file_size和innodb_log_files_in_group，参数innodb_log_file_size如何设置过小容易使checkpiont 频繁（checkpoint 的作用是将redo/undo数据刷到磁盘，这些数据是否最终提交要靠事务最后是否提交，如果没有提交回有undo log回滚磁盘上的数据(错了)）。
也就是说，只要每条提交的语句大小没有超过max_allowed_packet的限制，就可以一直提交sql语句，因为如果这些数据达到checkpoint的门限，就会放到磁盘上，而磁盘一般容量足够你提交数据了。
另外，如果优化innodb_log_file_size 参数，关闭数据库后，要把旧的ib_logfile移走，再启动数据库，否则会报错。
大概的理解如此，大概整理下思路，后续再完善下。
5 Mysql二进制日志与Innodb redo log之间的区别
1、首先2者都是记录数据的改变，不同的是，binlog是记录所有数据的改变信息，而innodb的redo log只是记录所有innodb表数据的变化。
2、binlog是记录已经提交完毕之后的dml以及ddl sql语句，而innodb redo log是正在执行中的dml以及ddl语句
3、binlog可以作为恢复数据使用 redo log可以作为异常down机或者介质故障后的数据恢复使用
4、在db文件目录下，也分属于不通的日志文件中。
6 待验证
有时侯Mysql会报错:
120418 8:00:14 InnoDB: ERROR: the age of the last checkpoint is 9434204,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row
这主要是由于 innodb_log_file_size 默认是5M, 不够用引起的.
增大innodb_log_file_size的方法:
-
- 暂停mysql, /etc/init.d/mysql stop
- 转移ib_logfile0和ib_logfile1, mv /usr/local/mysql/var/ib_logfile* ../
- 编辑my.cnf , 增加 innodb_log_file_size=64M
- 启动mysql, /etc/init.d/mysql start
7 mysql savepoint 可以用于给transaction打标记，用于回滚到特定的位置。
8 mysql 命令提示符下执行begin 或者start transaction的效果是开启事务，此时autocommit默认关闭。
9 mysql 过滤含有反斜线的字段方法：like '%\\\%'
反斜线符号会被语法分析程序剥离一次，在进行模式匹配时，又会被剥离一次，最后会剩下一个反斜线符号接受匹配
10 函数coalesce ：作用是将返回传入的参数中第一个非null的值，当使用join时，如果遇到无法join的记录时，此时自定义function会返回NULL，使用该函数可以更友好输出。
11 实现group by and top N的方法：
USE hesui;
DROP TABLE IF EXISTS tb_index_test;
CREATE TABLE tb_index_test(
`id` INT NOT NULL AUTO_INCREMENT,
`domain_id` INT NOT NULL,
`domain_name` VARCHAR(255) NOT NULL,
`s_time` DATETIME NOT NULL DEFAULT '1972-01-01 00:01:01',
`e_time` DATETIME NOT NULL DEFAULT '1972-01-01 00:02:01',
`dir` TINYINT NOT NULL COMMENT '0:incoming 1:relay',
`count` INT NOT NULL DEFAULT 0,
PRIMARY KEY(id),
INDEX (domain_id),
INDEX (domain_name),
INDEX (dir)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
TRUNCATE TABLE tb_index_test;
DELIMITER $$
DROP PROCEDURE IF EXISTS add_data$$
CREATE PROCEDURE add_data()
BEGIN
DECLARE loopindex INT;
DECLARE var_domain_id INT;
DECLARE var_domain_name VARCHAR(255);
DECLARE var_s_time DATETIME;
DECLARE var_e_time DATETIME;
SET loopindex=0;
SET var_domain_id=210000;
SET var_domain_name='evelyn.com';
SET var_s_time=NOW();
SET var_e_time=DATE_ADD( NOW(),INTERVAL 1 HOUR );
WHILE loopindex <1000
DO
INSERT INTO tb_index_test (`domain_id`,`domain_name`,`s_time`,`e_time`,`dir`,`count`) VALUES (var_domain_id,var_domain_name,var_s_time,var_e_time,0,loopindex);
SET loopindex=loopindex+1;
INSERT INTO tb_index_test (`domain_id`,`domain_name`,`s_time`,`e_time`,`dir`,`count`) VALUES (var_domain_id,var_domain_name,var_s_time,var_e_time,0,loopindex);
SET loopindex=loopindex+1;
INSERT INTO tb_index_test (`domain_id`,`domain_name`,`s_time`,`e_time`,`dir`,`count`) VALUES (var_domain_id,var_domain_name,var_s_time,var_e_time,0,loopindex);
SET loopindex=loopindex+1;
INSERT INTO tb_index_test (`domain_id`,`domain_name`,`s_time`,`e_time`,`dir`,`count`) VALUES (var_domain_id,var_domain_name,var_s_time,var_e_time,0,loopindex);
SET loopindex=loopindex+1;
INSERT INTO tb_index_test (`domain_id`,`domain_name`,`s_time`,`e_time`,`dir`,`count`) VALUES (var_domain_id,var_domain_name,var_s_time,var_e_time,0,loopindex);
SET loopindex=loopindex+1;
SET var_domain_id=var_domain_id+1;
SET var_domain_name=CONCAT(var_domain_name,loopindex);
END WHILE;
END$$
DELIMITER ;
CALL add_data();
SELECT a.domain_name, a.domain_id,a.count FROM tb_index_test AS a WHERE (SELECT COUNT(*) FROM tb_index_test AS b WHERE b.domain_id = a.domain_id AND b.count >= a.count) <= 3
ORDER BY a.domain_id DESC, a.count DESC
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
