MySQL知识点技能点总结贴--个人版！

mchdba

SQLyog试用期30天破解：
注册表里面删除HKEY_CURRENT_USER\Software\{8E919370-318F-4E5E-9EbE-9147B1DB66C9}/InD，然后重启SQLyog即可。

coolos

mchdba 发表于 2011-9-9 17:56
-- ============================================
-- mysql分组排序取前N条记录的最简洁的单条sql。
--  ...

请问下，这个问题也可如下解决：

mysql> select cid,author,count(*) number from test group by cid,author having nu
mber>2 order by cid,number asc;
+------+--------+--------+
| cid  | author | number |
+------+--------+--------+
|    1 | test2  |      3 |
|    1 | test3  |      4 |
|    2 | test22 |      3 |
|    2 | test33 |      4 |
+------+--------+--------+
4 rows in set (0.00 sec)

楼主觉得呢？

mchdba

coolos 发表于 2012-5-23 17:48
请问下，这个问题也可如下解决：

mysql> select cid,author,count(*) number from test group by cid, ...

你的业务逻辑跟我的不是一样的。

你的是只要统计个数大于2的分组记录。

我要的是取每个分组前2条记录。

mchdba

[root@odb-s12 tmp]# mysqlbinlog mysql-bin.001696 > a1.log
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 84, event_type: 19
Could not read entry at offset 376421:Error in log format or read error
[root@odb-s12 tmp]# history mysqlbinlog
-bash: history: mysqlbinlog: numeric argument required
[root@odb-s12 tmp]# history |grep mysqlbinlog
1019  mysqlbinlog mysql-bin.001696 > a1.log
1020  history mysqlbinlog
1021  history |grep mysqlbinlog
[root@odb-s12 tmp]# type mysqlbinlog
mysqlbinlog is hashed (/usr/bin/mysqlbinlog)
[root@odb-s12 tmp]# /usr/bin/mysqlbinlog mysql-bin.001696 > a1.log
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 84, event_type: 19
Could not read entry at offset 376421:Error in log format or read error
[root@odb-s12 tmp]#
[root@odb-s12 tmp]#
[root@odb-s12 tmp]#
[root@odb-s12 tmp]#
[root@odb-s12 tmp]#
[root@odb-s12 tmp]# whereis mysqlbinlog

mysqlbinlog: /usr/bin/mysqlbinlog

[root@odb-s12 tmp]# locate mysqlbinlog
/usr/bin/mysqlbinlog
/usr/local/mysql-5.1.56/bin/mysqlbinlog
/usr/local/mysql-5.1.56/man/man1/mysqlbinlog.1
/usr/local/mysql-5.1.56/mysql-test/include/mysqlbinlog_row_engine.inc
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog-cp932.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog2.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_base64.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_row.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_row_big.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_row_innodb.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_row_myisam.result
/usr/local/mysql-5.1.56/mysql-test/r/mysqlbinlog_row_trans.result
/usr/local/mysql-5.1.56/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_db_filter.result
/usr/local/mysql-5.1.56/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result
/usr/local/mysql-5.1.56/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test
/usr/local/mysql-5.1.56/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test
/usr/local/mysql-5.1.56/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
/usr/local/mysql-5.1.56/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog-master.opt
/usr/local/mysql-5.1.56/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog-cp932-master.opt
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog-cp932.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog-master.opt
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog2.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_base64.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_row.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_row_big.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_row_innodb.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_row_myisam.test
/usr/local/mysql-5.1.56/mysql-test/t/mysqlbinlog_row_trans.test

[root@odb-s12 tmp]# /usr/local/mysql-5.1.56/bin/mysqlbinlog mysql-bin.001696 > a1.log                    
[root@odb-s12 tmp]#

coolos

这个我也遇到过，需要用到mysqlbinlog的绝对地址

mchdba

开发人员优化SQL：
SELECT sql_no_cache a.*,b.number as undistribute_number from xxxxxxa a left join
xxxxxxb  b on (a.sku=b.sku and a.period=b.period_id) where 1 and a.platform not in(2,3)
order by a.period ,a.sale_number desc limit 1200,20;

方案：建联合索引
alter table xxxxxxa add key in_period(period ,sale_number desc);

搞定，只需要0.5秒不到。但是问题又来了在limit 1200000,20;的时候，很慢，需要55秒钟。

mchdba

--  命令行下面 创建存储过程 需要设定临时间隔符号

delimiter //   --设定自己的临时间隔符号
drop procedure if exists test.tslap_update;//
create procedure test.tslap_update()
begin
    declare tid int default 0;
    set tid=floor(rand()*210972);
    update test.test set excel_id=concat(id,'_',excel_id) where id=tid;
    commit;
end; // 用临时间隔符号来执行sql代码块
delimiter ;   -- 恢复正常的间隔符号

mchdba

-- ----------------------------------------------------------------
-- 修复myisam表，问题多多，时不时会报点错误
1194 - Table 'wk_goods_ship_logs' is marked as crashed and should be repaired
-- ----------------------------------------------------------------
1 shell修复
Shell> myisamchk -of  wk_goods_ship_logs.MYI
Shell> myisamchk -r  wk_goods_ship_logs.MYI
Shell> myisamchk safe-recover wk_goods_ship_logs.MYI
-- 查询失败，还是报同样的错误
2 第一步失败，则登录mysql修复
mysql> repair table bglogdb.wk_goods_ship_logs;
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                                                                                           |
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------+
| bglogdb.wk_goods_ship_logs | repair | error    | 1 when fixing table                                                                                |
| bglogdb.wk_goods_ship_logs | repair | Error    | Can't change ownership of the file '/data/mbdata/open/bglogdb/wk_goods_ship_logs.MYD' (Errcode: 1) |
| bglogdb.wk_goods_ship_logs | repair | status   | Operation failed                                                                                   |
+----------------------------+--------+----------+----------------------------------------------------------------------------------------------------+
3 rows in set (1.24 sec)
3 第二步失败，
进入MYI目录
发现多了一个wk_goods_ship_logs.TMD文件，于是
mv wk_goods_ship_logs.TMD wk_goods_ship_logs.MYI
进入mysql目录：
mysql>
mysql> repair table bglogdb.wk_goods_ship_logs;
+----------------------------+--------+----------+----------+
| Table                      | Op     | Msg_type | Msg_text |
+----------------------------+--------+----------+----------+
| bglogdb.wk_goods_ship_logs | repair | status   | OK       |
+----------------------------+--------+----------+----------+
1 row in set (1.17 sec)
4 搞定，一切OK！

成都飞飞

学习了 还没看完

mchdba

成都飞飞 发表于 2012-6-12 16:29
学习了 还没看完

MySQL里面的slow日志中的Last_errno也很重要，如果值>0表示，执行的sql有问题，需要检查，下面摘自自己的一个案例：

# Time: 120614 2:01:30
# User@Host: cms_user[xxx_user] @ [xxxxx]
# Thread_id: 45996856 Schema: openshop Last_errno: 1265 Killed: 0
# Query_time: 2.994615 Lock_time: 0.000374 Rows_sent: 0 Rows_examined: 565309 Rows_affected: 11631 Rows_read: 1
# Bytes_sent: 65 Tmp_tables: 3 Tmp_disk_tables: 0 Tmp_table_sizes: 268427424
# InnoDB_trx_id: 4CAA4208
SET timestamp=1339610490;
update channel_goods_xxx_cgs,(select id_value as goods_sn, AVG(xxx_rank) as gsi_rank,max(add_time) as last_add_time from mall_comment where id
_value>0 and status=1 and question_type=0 and parent_id=0 group by id_value) mc set cgs.gsi_rank = mc.gsi_rank, last_update_time=UNIX_TIMESTAMP() where cgs.c
hannel_code = 'xxxx' and cgs.goods_sn = mc.goods_sn;


【】检测到报警信息：
mysql> show Warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------+
| Note | 1265 | Data truncated for column 'gsi_rank' at row 1 |
| Note | 1265 | Data truncated for column 'gsi_rank' at row 1 |

最后查询到是gsi_rank列的长度小了了导致数据更新的时候被截断一部分。