缓存MySQL的缓存区使用LRU架构默认大小为128MB每个页默认16KB。缓存区分为三个部分空闲页LRU页已使用的页脏页修改过的页。默认情况下LRU算法的工作原理如下缓冲池中有3/8用于旧数据子列表近期未使用5/8用于新数据子列表当插入一个新的页时插入旧数据列表头部并同时删除旧数据列表队尾。当再次访问旧数据列表中的数据时将其放入新数据列表头部。可以在控制台输入SHOW ENGINE INNODB STATUS\G命令查看相关信息。当我们执行一条select语句时MySQL会首先去缓存区查询如果缓存命中则直接返回如果未命中则从磁盘读取。具体步骤如下从free list 拿到一个空闲页查询磁盘读入数据页插入返回数据。当我们执行一条修改表内数据的语句如update时MySQL会先执行查询功能然后对缓存中查询到的对应页进行修改并加入flush list脏页然后进行日志书写。先向redo log中写入本次修改然后再向binlog中写入主从复制用然后提交事务后台线程刷脏页进行数据持久化。脏页刷盘缓冲区空间有限脏页需要写回磁盘释放空间同时保证数据不丢失。这里存在一个冲突问题MySQL 的页是 16KB操作系统写磁盘通常是 4KB一个页要写 4 次。如果写到一半崩溃就会造成部分写失效页中新旧数据混杂无法恢复造成页损坏。MySQL使用Doublewrite Buffer双写机制来解决此问题。Doublewrite Buffer是一块1MB大小的连续磁盘区域充当了临时备份的作用。在页数据全部写入Doublewrite Buffer之后进行fsync刷盘系统命令强制写入磁盘然后将随机写到实际数据文件.ibd中。如果程序不幸崩溃就可以从 Doublewrite Buffer 找到完整页进而覆盖.ibd中损坏的页。数据写入磁盘文件成功后脏页从Flush List中移除。脏页刷盘的触发条件空闲页不足Free List 快空了必须淘汰旧页腾出空间redo log 快满redo log 是循环写的满之前必须刷脏页定期刷新后台线程默认每秒刷一次脏页比例过高超过innodb_max_dirty_pages_pct默认 75%正常关闭SHUTDOWN时全部脏页刷盘Buffer Pool实例Buffer Pool实例本身不是锁它把整个 Buffer Pool 物理拆分成多个独立的分区目的是减少多线程并发访问时的锁竞争。举个例子一张表为单实例时全表仅有一把管理结构的锁如LUR List Mutex)并发越高性能越差。而如果设置4个实例也就是将表分为4个分区每个分区都有自己的管理结构的锁那么并发访问不同实例便无锁竞争加快了速度。MySQL 内部会根据(表空间ID,页号)哈希决定该页属于哪个实例同一个页永远在同一个实例中保证一致性。即使内部锁粒度很细但某些操作比如LRU链表操作、分配空闲页仍然是高频率的在单个实例中这些操作会成为整个Pool级别的瓶颈。多个实例是把这种“逻辑上可分割”的资源内存、链表物理隔离让竞争进一步分散到不同的数据分区上。一个线程访问数据页A另一个访问数据页B如果A和B被哈希到不同的实例它们从链表操作到页访问就完全没交集了。分区是在多锁基础上的另一层并发优化是为了应对极高并发下仍然可能出现的资源争抢。内部多锁是纵向细化锁类型多实例是横向拆分资源范围两者结合才能达到高并发下的最大吞吐。索引索引分类按数据结构分类B树索引最常见所有数据按顺序存在叶子节点内部节点只存键值作为路标。叶子节点之间用双向链表连接支持范围查询和排序。查找、插入、删除均为 O(log n)范围查询效率高。Hash索引基于哈希表将索引列值通过哈希函数映射到固定桶中桶内保存实际数据指针。用于等值索引时间复杂度O(1)但是不适用其他查询存在hash冲突。全文索引解决like ‘%xxx%’ 模糊查询的性能问题。原理是建立倒排索引将文档拆分为单词记录每个单词出现在哪些文档中。按物理存储分类聚簇索引聚簇索引的叶子节点直接存储整行数据。一张表只能有一个聚簇索引通常是主键数据按主键顺序物理存储。如果表没有显式主键InnoDB会选择唯一非空索引代替或自动生成隐藏的row_id。主键查询速度极快不需要回表。但是主键乱序插入会导致页分裂性能下降。建议主键设置为自增int类型不仅避免了页分裂单个主键占用的内存空间也小相同空间能存储更多信息。非聚簇索引二级索引所有创建的普通索引都是非聚簇索引。非聚簇索引的叶子节点存储的是主键值而不是整行数据。查询过程为先在二级索引树找到主键再回表至聚簇索引查完整数据。按逻辑功能分类主键索引primary key值唯一且不为null自动为聚簇索引唯一索引unique key值唯一但允许为null通常配合主键使用.普通索引index二级索引加速查询。复合索引index(R1,R2,R3...)多列组成索引遵循最左前缀原则相当于索引R1,(R1,R2),(R1,R2,R3)......前缀索引index(r1(5))对字符列前n个字符建立索引减少索引大小。无法进行排序和分组。注意需要前缀有足够的区分度不然回表之后还需要二次过滤。索引失效索引失效指的是明明建了索引但SQL执行时却没有使用或无法有效使用该索引通常导致全表扫描。复合索引违反最左前缀原则复合索引中使用范围查询导致后续列索引失效在索引列上使用函数或表达式字符串与数字比较时出现隐式转换等效于使用函数like ‘%xxx’ 以%通配符开头通常解决办法是将列反转匹配或者创建全文索引or两边条件存在无索引列使用否定条件如!,not in等等否定查找意味着查找除了某些值之外的所有数据倾向于全表扫描。优化器认为全表扫描更高效索引列包含大量重复元素注意索引本身也是一张表也会占用存储空间和时间空间成本在创建索引的时候一定要重视空间和索引的连续性。对于索引的运用我们可以使用explain关键字进行验证查看是否正确使用了索引解决慢查询的问题。总结缓存和索引目的都是提高查询性能但是二者各有千秋。索引持久化存储在磁盘中帮助数据库减少扫描量快速定位需要的数据缓存存储在内存中避免向磁盘重复读取同一数据加快数据访问。二者配合使数据库性能达到最佳。