MySQL笔记之实践篇

码说256大约 5 分钟

唯一索引和普通索引的选择

主要从更新性能考虑
同一个字段，唯一索引和普通索引的查询消耗的性能对MySQL来说是相差不大的，普通索引比唯一索引多一次操作
数据插入时针对于这两个索引，Innodb存储引擎处理的逻辑是不一样的

唯一索引：判断有没有冲突，插入值，语句更新结束，如果插入的记录所在页不在内存中，还要多一步：将数据页读到内存。
唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索
普通索引：数据页在内存中直接插入值，数据页不在内存中，直接将数据写入change buffer中，结束
普通索引，查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录
change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上
change buffer类似于缓冲，先将数据写入缓冲池，然后在下次查询的时候把数据读入内存，执行数据页中相关的操作，减少了读磁盘的次数
只有普通索引会用到change buffer
在更新之后需要立马查询的表中，change buffer反而会增加维护成本，一般适用于更新频繁但查询比较少的表，比如日志表，账单流水表

索引选择是要根据业务来做的，业务正确性优先

change buffer 和 redo log

先操作change buffer，然后将这个语句写到redolog里
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
changebuffer跟普通数据页一样也是存在磁盘里，区别在于changebuffer是在共享表空间ibdata1里
redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动
只要内存里脏页（innodb buffer pool）里的数据发生了变化，就一定会记录2中前一种redolog
（对数据的修改记录在changebuffer里的时候，内存里是没有这个物理页的，不存在脏页）
真正对磁盘数据页的修改是通过将内存里脏页的数据刷回磁盘来完成的，而不是根据redolog

MySQL为何有的时候选择的索引不是理想中的，如何避免

针对于大数据表，并且有删除过数据的表，往往使用索引时都不尽任意，一般是由优化器在对SQL语句进行分析时造成的，优化器是根据索引的”区分度“来做的
一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
MySQL里是采用采样统计的方式来拼比基数的，当数据更新导致的统计出错的时候可以使用 analyze table t命令对数据进行重新统计
可以采用 force index的方式强制使用指定的索引
修改查询语句，引导MySQL使用我们期望的索引
创建更加合理的索引，删除无用索引

字符串类型的字段如何加索引

全字段加入索引，这样可能比较占用空间；
字段值的前几个创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
对于特殊的字符可以采用倒序的方法创建索引，也就是把字段值倒序过来存储，取前几个创建前缀索引
新增一个针对于当前字段的hash值列，在每次存储字段值的时候计算hash值并把hash存储hash列，对当前列做索引
倒序和哈希两种方法都不支持范围查询，针对于精确查询hash更加稳定
还是那句话脱离具体业务的技术选型，都是耍流氓

MySQL在fsync操作时执行查询或更新语句很慢

数据删除问题

delete语句执行后数据文件大小并没有改变，这是因为delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，
表数据信息可能较小也可能巨大无比，她可以存储在共享表空间里，也可以单独存储在一个以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，建议总是作为一个单独的文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。
执行过delete语句之后建议执行重建表语句，对数据表进行相关重建，比如：
alter table xxx engine=innodb;
optimize table xxx;

count(*)

尽量使用count(*)，在innodb里引擎对他做了相关优化
innodb里是全表扫描，一行一行进行累加，未指定条件下
myisam里如果未指定条件就会取自身维护的总数