MySQL设计开发基础

MySQL设计开发基础

周卫丰

◆MySQL数据库设计基础

◆MySQL InnoDB存储原理◆MySQL数据库设计原则◆MySQL数据库开发基础

◆MySQL事务隔离级别

◆MySQL SQL书写规范

◆MySQL SQL性能优化

MySQL InnoDB单表记录上限

•MySQL InnoDB单表记录数有没有上限？

•500万？

•1000万？

•2000万？

•还是更多？

官方给的limitation看起来还不错•每个InnoDB数据库最多可创建40亿张表，InnoDB permits up to 4 billion tables

•InnoDB16KB页的单个tablespace最大容量64T，The maximum tablespace size depends on the InnoDB page size

•每个表允许定义1017列，A table can contain a maximum of 1017 columns

•MySQL针对所有引擎限制最大行长64K（text和lob除外）The MySQL maximum row size limit of 65,535 bytes

•InnoDB16KB页每行最大页内存储长度为8K（text和lob除外），超出部分页外存储（增加IO次数）。the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size

•每个表中行的数量没有直接限制，但它受数据库存储空间的限制。

InnoDB表存储和Oracle有何区别

•为什么InnoDB表记录数多了性能会下降？

•InnoDB表和Oracle有什么区别？

•Oracle表的存储格式为堆表；InnoDB表的存储格式为IOT索引组织表

什么是

IOT IOT索引组织表，索引表合一，索引保存表的所有列

IOT表访问索引就可以获得全部数据，不需要回表

IOT表适合表中列数非常少，且全部列经常频繁访问

同Cluster Table一样，IOT是在Oracle 的场景比较少

从索引回表访问全部列数据访问索引即可获得全部列数据

为什么InnoDB页内存储限制8K字节

•MySQL 5.5及更低版本，每个InnoDB页的大小固定为16 KB

•MySQL 5.6开始，InnoDB实例的页大小可以是4KB，8KB或16KB（缺省值），innodb_page_size参数控制•MySQL 5.7.6开始，InnoDB还支持32KB和64KB页大小，对于32KB和64KB页大小，不支持ROW_FORMAT = COMPRESSED，最大记录大小为16KB（超出部分页外存储）

•Innodb表存储采用索引组织表的格式，即B+TREE结构，缺省设置每个页大小为16k，MySQL限制每个页块中至少有两行数据，否则就失去了B+TREE的意义（每一个页中只有一条数据，整个树就退化成为了一条双向链表），这样就得出了单行页内存储的最大长度就限制为了8k

•Blob和Text类型，innodb_file_format参数的影响：

• 5.6版本Antelope（羚羊）前864个字节存储在page里；

• 5.7版本Barracuda（梭鱼）page里头只存储一个20byte大小的指针

•何为B+树？

B树结构

B+树结构

B树和B+树

B+树和B树的区别

•B树的节点(根节点/分支节点//叶子节点)中没有重复元素，B+树有。

•B树的分支节点会存储索引条目，而B+树只有叶子节点才存储（分支节点保存每个叶子的第一条）。•B+树的每个叶子节点有一个指针指向下一个节点，把所有的叶子节点串在了一起。

那B+树比起B树有什么优势

• 1. 由于分支节点不保存全部的索引条目（大部分数据库都是保留每个叶子节点的第一条记录指针），同样大小的磁盘页可以容纳更多的节点元素，树的高度就小。（数据量相同的情况下，B+树比B树更加“矮胖”），查找起来就更快。

• 2. B+树每次查找都必须到叶子节点才能获取数据，而B树不一定，B树可以在非叶子节点上获取数据。因此B+树查找的时间更稳定。

• 3. B+树的每一个叶子节点都有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从第一个叶子节点开始顺着指针一直扫描下去即可，而B树则要对树做中序遍历。

InnoDB的B+树和Oracle的B+树的区别

•InnoDB表存储采用B+树（聚簇索引）格式，B+树保存了表里所有列

•Oracle索引存储采用B+树格式，B+树仅保存了索引列键值和ROWID

•MySQL聚簇索引叶子节点保存的记录数会比Oracle索引叶子节点少很多

•如果行长很大，索引层高增长会比较快

•MYSQL InnoDB二级索引存储主键值而不是存储行指针（ROWID）

•减少行移动或页分裂时二级索引的维护工作（Oracle行迁移链接ROWID不变）•二级索引访问需要两次索引查找，第二次通过主键去聚簇索引查找

InnoDB表记录数和聚簇索引层高

•假设B+树层高为2，一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数（叶子节点个数）* 单个叶子节点记录行数。

•假设单个叶子节点（页）中的记录数=16K/1K=16。（假设一行记录大小为1k，现在很多互联网业务行长大小通常就是1K左右）。

•假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放16384/14=1170 个叶子节点指针。可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据。

•同样可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条数据。所以在InnoDB中B+树高度为3时，大约可以存放2000多万数据，这个的前提是主键列选择的整型等比较小的列而且单行长度为1K。通过主键索引查询只需要3次IO操作即可查找到数据。

•MySQL单表记录数没有固定的理论上限，【聚簇】索引的层高由索引键类型的选择和行长大小决定，通常来说高并发系统索引层高控制在3层之内可以保证访问效率。如果索引层高增长一层，索引唯一查找效率就会下降，索引范围查找以及在嵌套连接循环体内效果更明显。所以MySQL数据库单表数据量大了会变慢主要是由于索引层高的增长导致。

•注意在数据库设计的时候索引键要选择递增的列，这一点至关重要，不要用递减的列及uuid等随机数的列！！！