核心架构模块
数据物理存放位置
常用日志文件
一条完整SQL语句执行流程：
一条SQL语句:
select c id,first name,last name from customer where c id=14;
Server层执行：连接器、查询缓存、分析器、优化器、执行器等
存储引擎层执行：负责数据的存储和提取
查询缓存数据
MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句hash之后的值，value 是查询的结果。
1)查看是否开启缓存
mysql>show variables like ‘query cache type’;
2)查看缓存的命中次数:
mysql> show status like ‘qcache hits’,
3)开启缓存
在/etc/my.cnf文件中修改“query_cache_type”参数值为0或OFF会禁止使用缓存。
值为1或ON将启用缓存，但以SQ!拿到个查询信息层的语句除外。
值为2或DEMAND时，只缓存以SELECTSQL CACHE开头的语句。
修改配置文件my.cnf，在文件中增加如下内容开启缓存:query cache type=1
SQL语句如何解析
分析前SQL语句: select c_id,first name,last name from customer where c id=14;分析后关键字分隔:select,c_id,first name,last name,from，customer，where c_id，=，14
预处理器
进一步检查解析树是否合法，例如表名是否存在，语句中查询的列是否存在等
执行SQL语句
判断执行权限
调用存储引擎接口进行查询
MyISAM：高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB：5.5版本后MySQL的默认数据库存储引擎，支持事务和行级锁，比MyISAM处理速度稍慢
ISAM：MyISAM的前身，MySQL5.0以后不再默认安装
MRG MYISAM：将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory：内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Archive：将数据压缩后进行存储，非常适合存储大量的独立的作为历史记录的数据，但是只能进行插入和查询操作
CSV：CSV存储引擎是基于CSV格式文件存储数据(应用于跨平台的数据交换)
InnoDB内存结构
（1）缓冲池
LRU:根据页数据的历史访问来淘汰数据，如果数据最近被访问过，那么将来被访问的几率也更高，优先淘汰最近没有被访问到的数据。
（2）修改缓冲(Change Buffer)
Change Buffer(在 MySQL 5.6 之前叫insert buffer，简称 ibuf)是InnoDB 5.5 引入的一种优化策略。Change Buffer(在MySQL5.6 之前叫 insert buffer，简称 ibuf)是InnoDB5.5 引入的一种优化策略。Change Buffer 用于加速非热点数据中二级索引的写入操作。Change Buffer 用于加速非热点数据中二级索引的写入操作。由于二级索引数据的不连续性，导致修改二级索引时需要进行频繁的磁盘10 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同时将写操作录入redolog中，在缓冲到一定量或系统较空闲时进行 由于二级索引数据的不连续性，导致修改二级索引时需要进行频繁的磁盘 10 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同时将写操作录入redolog中，在缓冲到一定量或系统较空闲时进行merge操作将修改写入磁盘中。Change Buffer在系统表空间中有相应的持久化区域。
Change Buffer 大小默认占 Buffer Pool的 25%，最大50%，在引擎启动时便初始化完成。
其物理结构为一棵名为 ibuf 的 B Tree。
二级索引就是辅助索引，除了聚簇索引之外的所有索引都是二级索引。聚簇索引也叫聚集索引，索引组织表，指的是一种数据存储方式，指数据与索引的数据结构存储在一起。如 InnoDB 的主键索引中所有叶子节点都存储了对应行的数据。因为数据肯定只是存储在一个地方，所以一个表只能有一个聚集索引。
（3）自适应哈希索引(AHI)
自适应哈希索引(Adaptive HashIndex，AHI)用于实现对于热数据页的一次查询。是建立在索引之上的索引!使用聚簇索引进行数据页定位的时候需要根据索引树的高度从根节点走到叶子节点，通常需要3到4 次查询才能定位到数据。InnoDB 根据对索引使用情况的分析和索引字段的分析，通过自调优Self-tuning的方式为索引页建立或者删除哈希索引。
AHI的大小为 Buffer Pool的 1/64，在 MySQL5.7 之后支持分区，以减少对于全局 AHI 锁的竞争，默认分区数为 8。
AHI 所作用的目标是频繁查询的数据页和索引页，而由于数据页是聚簇索引的一部分，因此AHI是建立在索引之上的索引，对于二级索引，若命中 AHI，则将直接从 AHI获取二级索引页的记录指针，再根据主键沿着聚簇索引查找数据;若聚族索引查询同样命中 AHI，则直接返回目标数据页的记录指针，此时就可以根据记录指针直接定位数据页。
（4）日志缓冲(Log Buffer)
**InnoDB 使用 Log Buffer 来缓冲日志文件的写入操作。**InnoDB 使用 Log Buffer 来缓冲日志文件的写入操作。内存写入加上日志文件顺序写的特点，使得InnoD8 日志写入性能极高。 内存写入加上日志文件顺序写的特点，使得InnoDB 日志写入性能极高。
对于任何修改操作，都将录入诸如 redolog与 undo log 这样的日志文件中，因此日志文件的写入操作非常频繁，却又十分零散。这些文件都存储在磁盘中，因此日志记录将引发大的磁盘 10。Log Buffer 将分散的写入操作放在内存中，通过定期批量写入磁盘的方式提高日志写入效率和减少磁盘 IO。
注意:这种将分散操作 改为 批量操作的优化方式将增加数据丢失的风险!
在磁盘中，InnoDB 将所有数据都逻辑地存放在一个空间中，称为表空间(Tablespace)。
表空间由段(Segment)、区(extent)、页(Page)组成。
表空间是 InnoD8 物理存储中的最高层，目前的表空间类别包括:
（1）系统表空间
系统表空间是 InnoDB 数据字典、双写缓冲、修改缓冲和回滚日志的存储位置，如果关闭独立表空间，它将存储所有表数据和索引。
它默认下是一个初始大小 12MB、名为 ibdata1 的文件，系统表空间所对应的文件由innodb_data_file_path定义.
指定系统表空间文件自动增长后，其增长大小由innodb_autoextend_increment设置(默认为 64MB)且不可缩减，即使删除系统表空间中存储的表和索引，此过程释放的空间仅仅是在表空间文件中标记为已释放而已，并不会缩减其在磁盘中的大小。
（2）独立表空间
独立表空间用于存放每个表的数据和索引。独立表空间用于存放每个表的数据和索引。其他类型的信息，如:回滚日志、双写缓冲区、系统事务信息、修改缓冲等仍存放于系统表空间内。因此即使用了独立表空间，系统表空间也会不断增长。在5.7版本中默认开启 其他类型的信息，如:回滚日志、双写缓冲区、系统事务信息、修改缓冲等仍存放于系统表空间内。因此即使用了独立表空间，系统表空间也会不断增长。在5.7版本中默认开启
开启独立表空间(File-per-table TableSpace)(innodb_fi1e_per_table=0N)之后，InnoDB 会为每个数据库单独创建子文件夹，数据库文件夹内为每个数据表单独建立一个表空间文件 table.ibd 。同时创建一个 table.frm 文件用于保存表结构信息。
每个独立表空间的初始大小是 96KB。
（3）其他
通用表空间
通用表空间(GeneralTablespace)是一个由 CREATE TABLESPACE 命令创建的共享表空间，创建时必须指定该表空间名称和 ibd 文件位置，ibd 文件可以放置于任何 MySQL有权限的地方。该表空间内可以容纳多张数据表，同时在创建时可以指定该表空间所使用的默认引擎。
通用表空间存在的目的是为了在系统表空间与独立表空间之间作出平衡。系统表空间与独立表空间中的表可以向通用表空间移动，反之亦可，但系统表空间中的表无法直接与独立表空间中的表相互转化。
Undo 表空间
Undo TableSpace 用于存放一个或多个 undo log 文件。默认 undo log 存储在系统表空间中，MySql5.7中支持自定义 Undo log表空间并存储所有 undo log。一旦用户定义了 Undo Tablespace，则系统表空间中的 Undo log 区域将失效。对于 Undo Tablespace的启用必须在 MySQL初始化前设置，Undo Tablespace 默认大小为 10MB。UndoTablespace 中的 Undo log 表可以进行 truncate 操作。
临时表空间
MySQL 5.7之前的临时表存储在系统空间之中，这样会导致ibdata在使用临时表的场景下疯狂增长。5.7版本之后InnoDB引擎从系统表空间中抽离出临时表空间(TemporaryTablespace)，用于独立保存临时表数据及其回滚信息。该表空间文件路径由innodb_temp_data_fi1e_fath 指定，但必须继承 innodb_data_home_dir。
（1）段【segment】
表空间由各个段(Segment)组成，创建的段类型分为数据段、索引段、回滚段等。由于InnoDB 采用聚簇索引与 B+ 树的结构存储数据，所以事实上数据页和二级索引页仅仅只是B+树的叶子节点，因此数据段称为 Leaf node segment，索引段其实指的是 B+ 树的非叶子节点，称为 Non-Leaf node segment。一个段会包含多个区，至少会有一个区，段扩展的最小单位是区【在索引篇会详细讲解B+数据结构】
（2）区【Extent】
区(Extend)是由连续的页组成的空间，大小固定为1MB，由于默认页大小为16K，因此一个区默认存储 64 个连续的页。如果页大小调整为 4K，则 256 个连续页组成一个区。为了保证页的连续性，InnoDB存储引擎会一次从磁盘申请4~5 个区。
（3）页【Page】
**页(Page)是InnoD8的基本存储单位，**每个页大小默认为16K，从InnoDB1.2.x版本开始，可通过设置 innodb_page_size 修改为 4K、8K、16K。InnoDB 首次加载后便无法更改。
MySQL规定一个页上最少存储2个数据项。如果向一个页插入数据时，这个页已经满了，就会从区中分配一个新页。如果向索引树叶子节点中间的一个页中插入数据，如果这个页是满的，就会发生页分裂。
（4）行【Row)
InnoDB的数据是以行为单位存储的，1个页中包含多个行。在SQL5.7中，InnoDB提供了4种行格式:Compact、Redundant、Dynamic和Compressed行格式，Dynamic为MySQL5.7默认的行格式。
（1）整体思路分析
在数据库中进行读取操作，将从磁盘中读到的页放在缓冲区中，下次再读相同的页时，首先判断该页是否在缓冲区中。若在缓冲区中，称该页在缓冲区中被命中，直接读取该页。否则读取磁盘上的页
对于数据库中页的修改操作，则首先修改在缓冲区中的页，然后再以一定的频率刷新到磁盘上。页从缓冲区刷新回磁盘的操作并不是在每次页发生更新时都触发，而是通过一种称为CheckPoint的机制刷新回磁盘。
内存数据落盘要考虑的核心问题:高性能写入数据，同时保证数据的绝对安全性!
数据库灵魂三问:
写入性能如何保证?
分散写入操作放在内存中，通过定期批量写入磁盘的方式提高写入效率减少磁盘 IO。
如何持久化?
也就是修改后的数据如何到磁盘中去。内存里缓冲池中的数据页要完成持久化通过两个流程来完成 也就是修改后的数据如何到磁盘中去。内存里缓冲池中的数据页要完成持久化通过两个流程来完成
数据安全性怎么保证?
（2）脏页落盘
什么是脏页?
对于数据库中页的修改操作，则首先修改在缓冲区中的页，缓冲区中的页与磁盘中的页数据不一致，所以称缓冲区中的页为脏页。然后再以一定的频率将脏页刷新到磁盘上。页从缓冲区刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。
为什么不是每次更新直接写入磁盘呢?
（3）Redo日志落盘
Log Buffer写入磁盘的时机，由参数 innodb_flush_log_at_trx_commit 控制，默认是 1，表示事务提交后立即落盘
（4）checkpoint落盘
解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。
在InnoDB存储引擎内部，有两种Checkpoint，分别为:
sharp checkpoint: 在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。fuzzy checkpoint: 数据库正常运行时，在不同的时机，将部分脏页写入磁盘。仅刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题。
（5）双写（Double Write）落盘
Double Write分两个部分:
Double write脏页刷新流程:
Double write崩溃恢复
如果脏页数据未来得及落盘，系统就奔溃了，直接应用redo日志重新执行脏页落盘。如果操作系统在将页写入磁盘的过程中发生了崩溃，其恢复过程如下
为了快速查询数据，可以给数据添加索引
定义：高效获取数据的数据结构
类型：聚簇索引、覆盖索引、组合索引、前缀索引、唯一索引等
优点：
缺点：
类型：
创建原则：
索引调优建议：
mysql的索引的总结
MySQL 使用的是 B tree 索引。
【初始化介绍】
一颗 b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色
所示）
【查找过程】
如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29
在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1
针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。
真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，
如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。
B+Tree 与 B-Tree 的区别:
（1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
（2）在 B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录
的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。
为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？
（1）B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点
的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就
越多。相对来说 IO 读写次数也就降低了。
（2）B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须
走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
聚簇索引是一种数据存储方式。
好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多
个数据块中提取数据，所以节省了大量的 io 操作。
限制：
对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是
该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用
无序的 id，比如 uuid 这种。
1.4.4 组合索引
组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，只是B+ 树索引是对一个列排序，组合索引是对多个列排序。组合索引既可以是主键索引，也可以是二级索引（除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）， 或非聚集索引（None Clustered Index）。二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值）。
组合索引心法口诀:
略
为什么要进行MySQL数据库调优?
影响数据库性能主要因素
数据库调优到底调什么?
SQL语句执行计划：如何查看SQL语句执行计划?——EXPLAIN SELECT* FROM tb seckill goods
使用索引：
编写有效的WHERE子句：
优化JOIN操作：
避免全表扫描：
优化GROUP BY和ORDER BY：
使用EXPLAIN分析查询计划：
数据分区：
选择合适的分区键：分区键是用来划分数据的依据，可以选择日期、范围、哈希等字段作为分区键。需根据业务需求和查询模式选择适合的分区键。
创建分区表：使用CREATE TABLE语句创建分区表，并指定分区类型和分区规则。MySQL支持基于范围、列表、哈希和自定义函数等方式进行分区。例如，对于范围分区，可以按照时间范围将数据划分到不同的分区中。
管理分区表：可以通过ALTER TABLE语句添加、删除和管理分区。例如，可以选择根据数据增长情况动态添加新的分区，或者删除不再需要的分区。
查询优化：在查询时，应尽量使用分区键进行过滤，以减少扫描的数据量，提高查询性能。使用EXPLAIN语句可以分析查询执行计划，确认是否正确利用了分区进行查询。
分表：
选择合适的分表策略：常见的分表策略有按范围、哈希、列表和轮换等方式进行。需根据业务特点和查询模式选择适合的分表策略。例如，对于按范围分表，在某个字段的值范围内创建不同的物理表。
创建分表：使用CREATE TABLE语句创建分表，并根据分表策略指定表名和结构。每个分表可以具有相同的列定义，但存储不同的数据。
数据路由和查询优化：在查询时，需要将查询请求路由到对应的分表上，并合并结果。这可以通过应用程序逻辑或者存储过程来实现。同时，也要确保使用合适的索引和优化技巧以提高查询性能。
管理分表：与数据分区类似，可以通过添加、删除和管理分表来扩展和维护数据。需要注意，随着分表增多，可能需要额外的管理工作来处理各个分表之间的关联和统计。
其他建议：
预估数据量和查询模式：在进行数据分区和分表之前，需预估数据库的数据量和查询模式，以选择合适的策略和方案。
定期维护和优化：分区表和分表并不是一劳永逸的解决方案，随着数据和业务的演进，可能需要调整分区策略和索引。定期进行维护和优化，确保系统性能和可扩展性。
考虑版本限制：MySQL的分区和分表功能在不同版本中有所差异，需根据实际使用的MySQL版本来了解和选择合适的功能。
数据库设计原则和范式规范
范式的概念：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
三范式和反范式是空间和时间的关系。三范式是为了降低空间；反范式是通过增加空间来提升运行效率。
三范式：
反范式是经常使用的设计。三范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦。但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低。因此处于性能考虑，可能需要进行反范式设计。
其他建议：*
预估数据量和查询模式：在进行数据分区和分表之前，需预估数据库的数据量和查询模式，以选择合适的策略和方案。
定期维护和优化：分区表和分表并不是一劳永逸的解决方案，随着数据和业务的演进，可能需要调整分区策略和索引。定期进行维护和优化，确保系统性能和可扩展性。
考虑版本限制：MySQL的分区和分表功能在不同版本中有所差异，需根据实际使用的MySQL版本来了解和选择合适的功能。
数据库设计原则和范式规范
范式的概念：为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
三范式和反范式是空间和时间的关系。三范式是为了降低空间；反范式是通过增加空间来提升运行效率。
三范式：
反范式是经常使用的设计。三范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦。但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低。因此处于性能考虑，可能需要进行反范式设计。
因篇幅问题不能全部显示，请点此查看更多更全内容