MYSQL的一些总结

索引

索引和事务都需要先理解它的文件系统

MYSQL将表的存储空间划分为索引段与数据段，见下图。数据段再次被分割为  段->区->页->行。每个页中会包含多行数据。页大小可以调整，但区大小会被固定为1M，所以如果需要对页大小进行定制，需要遵循大小控制为2的倍数。每个行都是一条记录，它除了保存自己的列值以外，还包含行首数据，它记录了行中的：不定长字段长度记录、NULL字段标记、下一行记录位置、数据总长度、删除标记、事务ID、回滚信息、隐藏主键(主键未指定时)等。回到页，当需要数据页中的数据时，数据页会完整的读取，并缓存到内存中。页作为最小文件读取的单元，它会同样包含一些信息，包括记录地址、记录数量、目录、未使用空间等。

mysql默认会使用主键创建聚簇索引，普通索引的值则只保存对主键的引用，因此当查询条件不完全在索引中时，将会再次通过主键找到数据行(俗称回表)，这样可以保证数据条目存储位置发生变化时，只需要更新主键索引即可。 所以B-Tree将不适合作为索引的数据结构（它的所有节点都包含了数据条目），B+Tree由于非叶子节点不保存数据，那么非叶子节点占用数据块就小，容易缓存到内存中。索引会指向该记录所在的页，当访问的页不在内存中时，会先完整读取出，然后根据页首信息，找到需要的记录。

相比主键使用的聚簇索引，mysql的普通索引可以定制索引，B+Tree索引、HASH索引 。区别在于数据结构，B+Tree索引由于使了二叉树，在检索时可以使用索引来排序，可以使用<>范围查询。HASH其结构特点，索引是一个HASH数组，每个HASH可是一行数据的主键，或多个主键的列表。

最左匹配原则

以B+Tree为索引方式使用多列联合进行索引时，如，A B C 三个字段顺序组成的索引，如果在查询时仅提供了A与C的值，缺少B，那么整个语句在匹配到索引A后就会停止匹配(此处还有疑问，按理解应该可以全扫B节点)，索引效率较低。如果我们对B使用了范围查询，同样也会在匹配到索引A就停止匹配。B+Tree的数据结构我们可以对它的叶子节点进行遍历就可以拿到有序数据，当我们语句匹配到A、B两个索引时，就可以使用C进行排序。

索引空间

上文中的文件结构我们知道索引是有大小的，mysql正常情况下会使用4级的节点，正常情况下4个级别的所有节点都需要缓存到内存中以提升性能，但如果内存不足以缓存完整的节点时，那么部分节点将会被逐出内存缓存，在遇到多个查询时频繁从硬盘中读取与内存淘汰，性能下降，所以索引数量不是越多越好。

 

QA：

1. 索引后排序规则

假设有A，B，C索引，where A=? and B=? order by C  可以使用索引，where A=? and B>? order by C 无法使用索引

2. 全表扫描性能

业务常有全表扫描需求，比如建立搜索引擎数据、批量更新状态等等，在多种条件全表扫描场景下单纯的增加索引已经无法满足需求了，建议使用主键排序与分段扫描。这样分页查询后，每一行数据仅会读取一次。

3. 索引数据格式

不建议使用长文本作为索引，如果确实存在必要，也需要减少索引长度。主键更不适合使用文本，由于文本存储空间大，其他索引在构建时会保存对主键的引用，这样同样行数索引占据内存空间会加大，内存不足时会淘汰，会增加对硬盘的访问。

事务

待补充

 

连接

待补充

QA:

1. wait_timeout参数：

该参数经常会导致小型工程的稳定性。它代表连接长时间空闲的的情况下是否会失效掉。一些博客经常会设置此值到长期不失效，明显存在误导。

除了在数据库中设定合理的值，也需要在连接池也设定合理的值，避免某些连接长时间空闲后，mysql已经失效了连接但应用程序却仍然使用它从而出现异常。常见连接池：

C3P0   idleConnectionTestPeriod  为 wai_timeout的50%

DBCP   timeBetweenEvictionRunsMillis 为 wai_timeout的50%

proxool   maximum-connection-lifetime 小于 wai_timeout

druid   removeAbandonedTimeout  小于 wai_timeout

 

分库分表

1.唯一ID生成

    由于聚簇索引的原理，我们在生成唯一ID时尽可能的遵守后生成的ID要比前面的ID大的规则，这样在插入时将会是有序的，有序的唯一ID。唯一ID生成分两种，一种是单点生成；一种是统一规则生成。单点是可以利用一个MYSQL实例(或redis等其他数据库)，通过原子操作来一维护一个数据行，行数据内记录最新的ID，每次需要新的ID时更新数据ID，优化后，将每次更新的步进从1调整到合理的数值来降低对数据行的访问，减少访问冲突。第二种规则经典实现时雪花算法，将一个64位的二进制数字分段，第一段代表以某个时间开始后的毫秒(秒/分钟)数，第二段代表机器唯一ID(或机房-机器)，第三段程序内存自增变量。每一段的单位和长度可以根据规模自行设计。、

    单点生成方式缺点是单点故障引起的不可用问题，但可以使用主备实例来容灾，或者使用zookeeper等分布式协调服务提升服务可用性。统一规则生成则有ID过大，可读性较差的问题，不过如果能准确预估业务规模，减少每一段的长度来缩小数字长度。