mysql实践篇

普通索引和唯一索引，应该怎么选择？

        因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
        因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
        普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引

索引选择异常和处理

        一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。
        第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

怎样给字符串家索引

        前缀索引（区分度要高）
        倒序存储。如果你存储身份证号的时候把它倒过来存。
        使用 hash 字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
        然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

为什么mysql会“抖”一下

        当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”
        第一种场景是，粉板满了，记不下了。这时候如果再有人来赊账，掌柜就只得放下手里的活儿，将粉板上的记录擦掉一些，留出空位以便继续记账。当然在擦掉之前，他必须先将正确的账目记录到账本中才行。
        这个场景，对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。我在第二讲画了一个 redo log 的示意图，这里我改成环形，便于大家理解。
        第二种场景是，这一天生意太好，要记住的事情太多，掌柜发现自己快记不住了，赶紧找出账本把孔乙己这笔账先加进去。
        这种场景，对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

        第三种场景是，生意不忙的时候，或者打烊之后。这时候柜台没事，掌柜闲着也是闲着，不如更新账本。
        这种场景，对应的就是 MySQL 认为系统“空闲”的时候。当然，MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即使是“生意好”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

        第四种场景是，年底了咸亨酒店要关门几天，需要把账结清一下。这时候掌柜要把所有账都记到账本上，这样过完年重新开张的时候，就能就着账本明确账目情况了。
        这种场景，对应的就是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
        其中，第三种情况是属于 MySQL 空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。这两种情况下，你不会太关注“性能”问题。所以这里，我们主要来分析一下前两种场景下的性能问题。
        第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
        第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。
InnoDB 刷脏页的控制策略
        ops这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试

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

        delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表。
        不止是删除数据会造成空洞，插入数据也会。

         解决方法? 这里，你可以使用 alter table A engine=InnoDB 命令来重建表。MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
        由于mysql5.6之后添加了日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。

	alter table t engine = InnoDB //重建表去掉空洞

count(*)这么慢，我怎么办？

        按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count()。mysql对count（*）做了优化

mysql

        如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。
        隐式类型转换，也会使用到函数不会走索引。如字段m_id为int，你写成了select * from table where mid = ‘12’,这样是不会走索引的。
        隐式字符编码转换，连表查询时，两表的字符集不同也不会走索引

mysql幻读

        事物的四种隔离：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read） mysql默认隔离级别	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

        间隙锁解决幻读,间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

		在my.cnf里面的[mysqld]添加
		[mysqld]
		innodb_locks_unsafe_for_binlog = 1
		重启MySQL后生效.

        间隙锁的范围，根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。

number	id
1	2
5	10
6	11

        select * from t where number=6;那么间隙锁锁定的间隙为：（5，11），所以你再想插入5到11之间的数就会被阻塞。
        更需要你注意的是，当你再执行update t set number = 6 where id = 1也会被阻塞。这是为什么？你想想看，要保证每次查询number=6的数据行数不变，如果你将另外一条数据修改成了6，岂不会多了一条？所以此时不会允许任何一条数据被修改成6。

到底可以不可使使用join

         join的算法分为Index Nested-Loop Join（索引嵌套循环联接）Simple Nested-Loop Join（简单嵌套循环联接）。
         如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
         如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。所以你在判断要不要使用 join 语句时，
         就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

         日常：如果查询的行数较多使用join，反之使用循环。尽量不适用join查询（个人习惯，没有依据）