MySQL Feature

发表于 | 分类于 ， ，

资源利用特点

CPU

  • 5.1可以利用4个核；5.5可以利用到24个核；5.6可以利用到64个核
  • 每个连接一个是一个线程(非thread pool)，每个query只能使用到一个核，随着连接数增加，性能下降
  • 无执行计划缓存(无SQL执行计划预编译)
  • 有Result缓存，但比较鸡肋

内存

  • 64位系统可利用内存（2的64次方）GB
  • 在高速并发环境，基本是靠内存缓存来减少对磁盘的IO冲击
  • 通常内存按实际数据的15%-20%规划，如果特别热的数据，需要考虑更大的比例来缓存数据
  • 可以根据Query响应时间来做指导分配
  • query cache建议关闭或设置很小

磁盘

  • Binlog，redo log，undo log顺序IO
  • Datafile 随机IO和顺序IO相结合
  • OLTP业务更多的需要随机IO（可以利用内存做缓存，从而减少随机IO）
  • OLAP业务更多需要顺序IO（内存缓存作用不大）

性能特性

HOT（堆表）和IOT（索引组织表）的区别

  • 起源

堆组织表的存储速度因为不用考虑排序，所以存储速度会比较快。但是要查找符合某个条件的记录，就必须得读取全部的记录以便筛选。而这个时候为了加快查询速度，索引就出现了，索引是针对少量特定字段的值拿出来进行排序存储，并记录在表中的位置，而因为索引是有序的，所以就会很容易通过索引查询到具体的记录位置（普遍使用二分查找法），然后再根据记录位置直接从表中读取该记录。同时因为索引的字段较少，所以索引通常会比其基表小得多。

从上面通过索引访问表记录的方式可以看出，当要访问的数据量较大时，通过每一条记录的位置去访问原始记录，每一条符合条件的记录都需要经过索引访问后再访问基表这样一个复杂的过程，这会花费很多时间。同样，如果不经过索引而直接查询表，也可能因为表字段太多，记录较大的情况下把全部的数据读取进来，这也会花费很多时间。

那怎么办呢？这个时候就会想到，如果表中数据本身就是有序的，这样查询表的时候就可以快速的找到符合条件的记录位置，而很容易判断符合条件记录的位置，这样只需要读取一小部分数据出来就可以了，不需要全表记录都读取出来进行判断。索引组织表就这样产生了，当然索引表中插入，更新的时候可能会因为需要排序而将数据重组，这时候数据插入或更新速度会比堆组织表慢一些。如果堆组织表上有索引，那么对堆组织表的插入也会因为要修改索引而变慢。

  • 堆表

堆表（heap table）数据插入时时存储位置是随机的，主要是数据库内部块的空闲情况决定，获取数据是按照命中率计算，全表扫表时不见得先插入的数据先查到。

堆表的特点就是索引和数据分开，所有索引都是二级索引，或叫辅助索引。所以主键索引也是二级索引，没有完整记录，区别只有唯一或非唯一。索引中存储的是key与指针，指针指向具体数据记录。当然，查找key的算法都是一样的，使用二分查找，也叫书签查找。

两者的查找方式都一样，通过先找到key，然后定位到数据。不论是通过主键还是二级索引，两者的开销都是一样的。

  • 索引表

索引表（IOT）数据存储是把表按照索引的方式存储的，数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。

索引表的查询效率逼堆表高（相当于查询索引的效率），插入数据的速度比堆表慢。

索引组织表必须要有主键，如果非显式创建，InnoDB存储引擎会默认创建一个ROWID当做主键；而堆表则无强制要求。

这就是经常有文章说MyISAM比InnoDB快的原因吧，但这个说法并不完全正确，索引组织表由于索引项和数据存储在一起，且InnoDB聚集索引各个叶子节点之间都是同过双向链表组织，且都是根据主键逻辑顺序存放，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。

特别对于范围查询，只需要定位到开始key的位置，就可以顺着这个位置扫描到结束key。如下SQL语句：

1
select * from table where id between 1000 and 2000

超大单表问题

常见的水平切分方式：分库分表；分区表

  • 什么是分库分表？

把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的mysql实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的。分库分表往往是业务层实施的，分库分表后，为了满足某些特定业务功能，往往需要rd修改代码。

  • 什么是分区表？

所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务rd代码无需改动。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

    • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
    • 旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
    • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

水平拆分设计与约束

缺点

  • 数据分散，group by ,order by , sum, count等排序，聚 集函数不能支持，如果一定需要，需要某一个地方存全量
  • 分片过多，会造成维护难度增加，比较定位(要求分区尽可能的简单)
  • 后期迁移较复杂

优点

  • 同一个功能内的关连，事务操作都可以进行
  • 不会存在超大规模的表
  • 应用程序端改动比较小
  • 拆分规则设计的好的情况，较难触到性能瓶颈，也比较 易扩展

Reference