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

mchdba

-- 【MySQL 覆盖索引】 参考网址 http://www.searchdatabase.com.cn/showcontent_53221.htm  http://hi.baidu.com/shinegun/blog/item/e16f72b7184f36e530add189.html

　
     原来自己也做过类似的优化处理，就是尽量做到索引的唯一性和rang性，但是却不知道这样的统称叫覆盖索引。今天被一个前辈问起覆盖索引，我竟然楞住了，没有反应过来！
     覆盖索引就是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
     如果你想要通过索引覆盖select多列，那么需要给需要的列建立一个多列索引，当然如果带查询条件，where条件要求满足最左前缀原则。
     如果explain之后，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引，能使用覆盖索引的先决条件是不能select * from tb  where xxxx;
      
    再比如说在文章系统里分页显示的时候，一般的查询是这样的：
    SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;
   通常这样的查询会把索引建在created字段（其中id是主键），不过当LIMIT偏移很大时，查询效率仍然很低，改变一下查询：
   SELECT id, title, content FROM article
   INNER JOIN (
          SELECT id FROM article ORDER BY created DESC LIMIT 10000, 10
    ) AS page USING(id)
   此时，建立复合索引"created, id"【只要建立created索引就可以吧，Innodb是会在辅助索引里面存储主键值的】，就可以在子查询里利用上Covering Index，快速定位id，查询效率会提升很大。

mchdba

-- xtrabackup备份的原理

XtraBackup基于InnoDB的crash-recovery功能。
它会复制innodb 的data file，由于不锁表，复制出来的数据是不一致的，
在恢复的时候使用crash-recovery，使得数据恢复一致。

InnoDB维护了一个redo log，又称为 transaction log，重做日志，undo日志在ibd数据文件里面。
它们包含了innodb数据的所有改动情况。
当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做二步操作：
1.It applies committed transaction log entries to the data files  -- redo
2.it performs an undo operation on any transactions that modified data but did not commit. --undo

XtraBackup在备份的时候， 一页一页地复制innodb的数据，而且不锁定表，
与此同时，XtraBackup还有另外一个线程监视着transactions log，
一旦log发生变化，就把变化过的log pages复制走。
因为transactions log文件大小有限，写满之后，就会从头再开始写，
所以新数据可能会覆盖到旧的数据。

在prepare过程中，XtraBackup使用复制到的transactions log 对备份出来的innodb data file 进行crash recovery。

所以一般xtraBackup出来的全备文件大小会比原来的要大，因为它记载了相关的很多信息，我的生产环境大概是1.6倍-2倍之间吧。

参考 http://hi.baidu.com/wongxuelei/blog/item/dd7490ee312a3befb3fb9524.html

mchdba

623422929 发表于 2011-10-29 01:06
好东西收藏了···
南宁空调维修

上周遇到一题目，让我画出MySQL的体系结构，结果我凭以前的理解画了画，感觉有点乱糟糟的，今天google出来，一张图，觉得画得真好，思路逻辑很清晰的，呵呵！

jinguanding

mchdba 发表于 2011-10-30 13:15
上周遇到一题目，让我画出MySQL的体系结构，结果我凭以前的理解画了画，感觉有点乱糟糟的，今天google出来 ...

看来是你做的题目，是我出的....

像这类体系架构图，重点在理解，不一定每个模块都能画出来.....

官方手册上有此图的....

mchdba

某个生产库，磁盘空间不足50G，短时间之内cto又没法增加新的磁盘，为了在周末不被公司同事电话骚扰我，所以我不得不flush logs； 尽量不要让binlog日志来占用空间。

在第一天，我在执行
set global expire_logs_days=1;
flush logs;
清理掉了30G的日志空间。

过了3天，去查看磁盘空间，又只剩下50G了，而日志却是有3天之多，呵呵，有点奇怪set global expire_logs_days=1;不起作用吗？
看来的变通下了，重新设置参数为0；
set global expire_logs_days=0;
flush logs;
这回去看磁盘空间，还有67G。

set global expire_logs_days=1;这个设置不能让系统每天执行flush logs吗？看来要好好去看看set global expire_logs_days=1的具体原理了。

canyuanxuanzai

谢谢分享。。
合肥办证
办本科毕业证
办大专毕业证
上海办证书
上海办文凭
办英语四级证
办英语六级证
办英语四级成绩单
办英语六级成绩单

mchdba

The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.2, “MySQL Server Logs”.

To remove binary log files manually, use the PURGE BINARY LOGS statement. See Section 12.5.1.1, “PURGE BINARY LOGS Syntax”.


再看得出的参数设置：
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| flush                          | OFF   |
| flush_time                     | 0     |
| innodb_adaptive_flushing       | ON    |
| innodb_flush_log_at_trx_commit | 2     |
| innodb_flush_method            |       |
+--------------------------------+-------+
5 rows in set (0.02 sec)
mysql>

icer_repls

来张简化版的MySQL体系结构图吧  

愤恕的小草

收藏，
感谢撸主分享

jinguanding

mchdba 发表于 2011-11-14 10:12
某个生产库，磁盘空间不足50G，短时间之内cto又没法增加新的磁盘，为了在周末不被公司同事电话骚扰我，所以 ...

这个参数值为：0 就是不管
=N 且N>0 才起作用，而且日志是保留：N+1天（即当前天）的，日志删除发生在重新启动 或 日志文件切换，或执行FLUSH LOGS类强制命令


已经把你这贴加精华了，继续分享！！！