关于mysql的一些问题_深入思考mysql的一些问题

为什么我的mysql会 抖 一下？

这个抖一下，就很有意思。。

这个抖一下是将内存中的数据flush到磁盘中，我们直到对数据的修改都是在内存中进行的，并且在内存中修改后，不会立即刷回磁盘，但是总是要flush到磁盘的，这时就抖了一下了！！！

那么， 什么时候将内存中的数据flush到磁盘中呢？

1)当redoLog满了时候，也就是checkpoint 与 write position相等了，这时候就需要进行flush到磁盘中，否则没法继续持久化了。

2)当内存满了的时候，(Innodb用buffer pool(缓冲池)来管理内存，也就是磁盘中的数据页都会被读到buffer pool中，changge buffer也在这个里面是不？？哈哈)

3)mysql认为它自己空闲的时候

4)正常关闭mysql的时候

有两种情况尽量避免：

1)一个查询或者更新操作要淘汰的脏页个数太多，导致查询的响应时间明显变长！(抖了一下！)

数据在内存中都是在 buffer pool(缓冲池)中的。这里面的数据页就会有三种状态：

- 未使用的页

- 使用了的，但是是干净页(内存中的数据与磁盘中一致)

- 使用了的，但是是脏页(内存中的数据与磁盘中不一致，说明还没有flush到磁盘中，如果这些buffer pool满了，那么要淘汰的页有很多这中脏页时，就要将他们都flush到磁盘，这就比较耗时)

2)redolog满了，这时没法进行任务更新操作！

为什么表数据删掉一半，表文件大小不变？

有这么神奇的事情？忙猜一下，并没有真正的删掉表数据！

哈哈，还真是。执行delete语句并没有将数据真正的删除，而是标记为可复用(所以表文件大小没变)。其实也可以理解，底层的数据结构是B+树，B+树的调整耗费时间，应尽量减少树的调整，所以这里采用标记为可复用的方式还是挺好的！

两阶段提交怎么保证的数据的一致性？

1)在时刻A，mysql发生故障，那么redolog的不是commit状态，会回滚，而根本没有往binlog中写东西，所以，这个地方很好理解。

2)在时刻B，mysql发生故障，redolog里面有完整的prepare, 这时会检查binlog中的是否是完整的，如果完整提交，否则回滚。(mysql能够验证binlog的完整性，这个可以放心)

保存到磁盘中的redolog，也有可能是 prepare状态的啊？正常情况下是commit才会持久化，这个估计就是崩溃前做的事情把！！

redoLog 和binlog是怎么配合的？

崩溃恢复的时候，会先按顺序扫描redolog, 如果既有prepare又有commit，那么就直接提交，只有prepare，就去查找binlog, 如果binlog完整就恢复。就是通过这种方式保证的一致性。否则，binglog是完整的，那么创建从库的时候就会和主库不一致。所以，采用 redolog是prepare加上完整的binlog来恢复主库，就保证了数据的一致性。(binlog主要用于从库的创建，里面存的是sql语句)这个最后的commit状态也是挺重要的！

order by是怎么工作的？

对查找到的结果按照某个字段进行排序。这时就需要在内存中有一个临时表，来进行排序。有两种方式

比如： select  city,name,age from T where city='rz' order by 'name' limit 1000;  (取出city字段为rz的记录，然后按照name进行排序，取前1000个)

1)将查询的结果全部放到内存中进行排序，可能回出现内存放不下的情况，这时就会借助于外部磁盘存储来进行排序(这时一般就是归并排序了)

2)只查出id和要排序的字段，排好序后，再进行回表，按序返回。(尽量不要选择这个，因为多进行了磁盘的IO操作，耗时会比较严重)

哇! 如果数据本来就是按照order by的后面的字段进行排序的，那不是更好！！

可是这里是按照city字段进行查询的啊！ 呵呵，联合索引上场！ 用 city和name建立联合索引！！！这就不需要在内存中创建临时表，也不需要进行排序了！！

(把握住一点，先查出结果，然后再排序)

如何正确的显示随机消息？

什么是随机消息？   从表中随机返回几个数据

例如： select word from T order by rand() limit 3;   从表T中随机选取3个word返回。 (随机排序，取前三个)

那么它的执行流程是怎样的呢？

会先创建一个临时内存表，(这样就不会进行磁盘IO操作了)

然后从磁盘读取数据，并存放到临时内存表中

进行排序(最小堆 或 最大堆，因为不借助于外部的磁盘临时文件，所以不用归并算法) (再sort buffer中进行的排序)

得到结果

索引失效：

索引字段进行了函数运算：

例如 ： select count(*)  from T where month(date)=7;    month()  一个函数，用来求日志参数的月份的，这里date索引会失效。(但是因为查询的是 count(*) 因此还是会走索引，但是目的是为了快速定位，关于count(*) 后面会详细解释！！！！》》》》》》》》》》》》》》)

select * from T where id+1=100;   索引失效  (索引字段不能进行任何运算！！！)

select * from T where id=100-1; 索引不失效   (也就是索引这个地方不能进行任何操作)

(这是因为，对索引字段做函数操作，很有可能会破坏索引的有序性，B+树就是根据这个有序性来实现的范围的查询啊！)

隐式类型转换：

字符串与数字作比较时，会将字符串转换为数字；

例如 ： select * from T where score>90;  如果score是varchar类型，并且是个索引，那么这时就会进行全表扫描，也就是索引失效了。

(这是由于mysql在执行时，给字段加上了函数，让他进行从varchar到int类型的转换，所以，索引失效了)

select * from T where id>'90'  ;     索引不会失效，因为是varchar转为int类型，所以没有对字段进行任何函数操作。

隐式字符编码转换：

这个与隐式类形转换类似，不过这个指的是不同的表的字段作比较时的情况：

select * from T1 t1 ,T2 t2 where t1.name=t2.name and t1.id>2;  如果t1和t2的字符编码不一样，比如一个是 utf8，一个是另外一个，那么在进行比较的时候，会先转为同样的字符集，然后再进行比较，也是给字段进行函数操作，也就是字符类型的转换。

(上面3个失效的原因的本质都是对 索引进行了函数操作)

为什么有时只查询一行语句，也会很慢？

查询长时间不返回

select * from T where id=1;

原因：

1)表被锁住了

2)在执行flush (flush也被别的阻塞了)

3)查询的这一行被锁住了，加上了写锁，select语句被阻塞

查询慢：

幻读

如何解决