mysqld – 服务器端程序 mysql – 客户端程序
mysql可分为两层
储存引擎 show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
create table . . . ( ) ENGINE = InnoDB ;
alter table < 表名> ENGINE = . . . ;
describe < 表名> ;
show create table < 表名> ;
查看系统变量: show [global | session] variable [like 正则]
设置系统变量: set [global | session] variable [like 正则] 状态变量（只读）show [global | session] status [like 正则] utf8mb3：精简过的utf-8，占用1~3 bytes utf8mb4: 完整的utf-8，占用1~4 bytes 比较规则：一种字符集对应若干比较规则(按编码、是否区分大小写…) 字符集和比较规则设置
服务器级别: 由系统变量character_set_server 和 collation_server控制 数据库级别：create database 数据库 [ [ default ] character set 字符集] [ [ default ] collate 比较规则]
alter database 数据库 [ [ default ] character set 字符集] [ [ default ] collate 比较规则]
系统变量 character_set_database collation_database 只读 表级别：create table . . . [ [ default ] character set 字符集] [ collate 比较规则]
alter table 表名 [ [ default ] character set 字符集] [ collate 比较规则]
列级别：create table 表名(
列名 类型 [ character set 字符集] [ collate 比较规则] ,
. . . . . .
)
alter table 表名 modify 列名 类型 [ character set 字符集] [ collate 比较规则]
客户端与服务器通信的字符集
系统变量 character_set_client 服务器以该字符集解释客户端发送的请求 系统变量 character_set_connection 处理请求时，将客户端的请求由character_set_client编码转为character_set_connection编码 系统变量 character_set_results 服务器返回给客户端的消息以此编码 （👆作用范围都是session） 客户端以启动项 --default-character-set=… 指定其使用的编码 行格式
COMPACT
大体格式：变长字段长度列表|NULL值列表|记录头信息|列1值|列2值 …
变成字段长度列表，逆序（列的顺序）储存可变长列的长度 NULL值列表：以二进制位标记那一列为null 使用可变长编码时，CHAR(M) 当作可变长 REDUNDANT - 较旧
DYNAMIC – 类似COMPACT，对溢出列的处理有差异 COMPRESSED – 类似COMPACT，对溢出列的处理有差异 溢出列(off-page) – 数据太大以至于一个页无法装下。剩余的数据会储存到其他页面(溢出页 )。
对于COMPACT和REDUNDANT行格式来说，原来的记录处只记录一部分数据 和 指向储存着剩余数据的页面（溢出页）的地址 。（几个溢出页面通过链表连接起来） 对于DYNAMIC行格式来说，原来的记录处只储存指向溢出页 的地址 对于COMPRESSED行格式来说，使用与DYNAMIC相同的方式，但会对页面进行压缩 隐藏列 – 自动添加
ROW_ID: 优先使用用户定义的主键。若未定义主键，则选取NOT NULL、UNQIUE的列作为主键。若都没有，则自动添加ROW_ID列作为主键。 TRX_ID: 事务ID ROLL_POINTER: 回滚指针 指定/修改行格式 create table < table_name> ( . . . ) ROW_FORMAT = . . .
alter table < table_name> ROW_FORMAT = . . .
页：InnoDB管理储存空间的基本单位。页作为磁盘与内存之间交互的基本对象。 (一般为16KB) (系统变量innodb_page_size指定) 索引页 -用于存放记录（表项）的页索引页结构：
File Header:储存页的信息（各类页都有该结构）。部分信息：
校验和 页号 上一页的页号 (相邻页通过双向链表组织 ) 下一页的页号 页面被最后修改时对应的LSN（日志序列号） 页类型 所属的表空间 Page Header
页面中槽的数量 FreeSpace的最小地址 已删除记录组织成的链表 的头节点 该页在B+树中的层级 索引ID，当前页面属于哪个索引 Infimum + Supremum 两个虚拟的记录（记录链表的头尾节点）
Infimum: 用其表示该页中最小的记录（相当于头节点） Supremun: 用其表示该页中最大的记录（相当于尾节点） User Records
储存每一条记录（行） 每条记录储存着 从其真实数据开始 到 下一条记录的真实数据 的相对位置。（形成链表 ） 记录按主键排序组成链表 Free Space
Page Directory
页中某些记录的相对位置（槽的最后一个记录的位置 ），用于把整个链表分割成几段（称为槽 ），加速查询。 二分法查找槽，遍历槽找到相应记录 File Trailer–用于检测页是否完整
记录头信息中的record_type属性：
0：用户的记录->一条表项数据 1：目录项记录 ->指向另一个页（B+树的另一个节点 ） 2：Infimum 记录->一个页中表示最小值的“哑节点” 3：Supremum 记录->一个页中表示最大值的“哑节点” 聚簇索引 ：以主键 建立B+树 ，每个表都有。
B+树的每个节点为一个页。储存目录项记录的页（B+树的内部节点） 和 储存表项数据的页（B+树的叶节点） 都为索引页 二级索引 ：以非主键的列建立另一棵B+树
其叶节点的目录项记录包括： （按(索引列,主键)排序 ）
其内部节点的目录项记录包括：（按(索引列,主键)排序 ）
若要获取全部列的值，需要再通过取到的主键值再从聚簇索引 上找到全部列的值–>回表 索引条件下推(Index Condition Pushdown)–使用二级索引时，将查询条件中与所使用的索引有关的条件 下推到引擎层进行，而不是回表后返回给server层再判断–减少回表次数
select * from t1 where k1 >= 0 and k1 <= 10 and k2 != 1 存在索引(k1,k2,k3) 在该索引中进行判断筛选 只为用于搜索、排序、分组的列创建索引 索引列的类型尽量小 建立前缀索引 alter table < table_name> add index idx_key1 key1( n)
让索引列以列名的形式在搜索条件中出现 .若以其他的表达式形式出现，会导致无法使用索引。使用AUTO_INCREMENT主键，可以减少聚簇索引B+树的节点（页）的分裂。 索引命名： idx_column_name 系统变量 datadir 指示数据目录的路径 每个数据库对应 数据目录下的一个同名目录 InnoDB：
表数据可以储存到 系统表空间(system tablespace) 或 独立表空间(file-per-table tablespace)(同名的数据库目录下的table_name.ibd文件) MyISAM：每张表对应同名的数据库目录下的 table_name.MYD（数据文件） 和 table_name.MYI（索引文件） 文件 系统数据库：
mysql： 用户账户、权限信息、储存过程、事件定义、日志… information_schema：元数据（表、列、视图、索引…) performance_schema：服务器运行的状态信息 sys：通过视图将information_schema和performance_schema结合起来，便于查看 表空间：页面(page) --> 区(extend) --> 组 每个区的第一个页面储存着该区所有页面的一些属性（区内每个页面对应一个XDES Entry结构） 段: 逻辑上的结构。 对于索引来说 存放叶节点的区和页面 、 存放非叶节点的区和页面 即为一个段。 段由区和零撒的页面 （碎片区中）组成，当使用零散的页面大于一定数量时合并为一个区 --> 减少硬盘的随机IO 每个段对应一个INODE Entry结构 ，其描述了 段ID、段内各种链表的集结点、零撒页面的页号 （INODE Entry在INODE页中，第一个INODE页为表空间的第3个页，多个INODE页构成链表）区的类型：
空闲的区：FREE链表中 有剩余空闲页面的碎片区：FREE_FRAG链表中 没有剩余页面的碎片区：FULL_FRAG链表中 附属于某个段的区：这些区在段中被组织成几个链表
对驱动表 的每一条记录，查询被驱动表 中符合条件的记录，连接起来，加入结果集 select . . . from t1, t2, t3 where . . .
for each row in t1 satisfying conditions about t1
for each row in t2 satisfying conditions about t2
for each row in t3 satisfying conditions about t3
join
将对被驱动表查询多次 基于块的嵌套循环连接
被驱动表的过大无法一次读入时，将一部分读入Join Buffer 驱动表与Join Buffer内的表连接完成后，再换入剩余的表 只有与查询列表 和 过滤条件中的列会被装入Join Buffer->避免使用 ‘*’ 提供连接速度 调整join_buffer_size–>优化连接速度 MySQL根据成本常等估计查询的成本
成本常数：mysql数据库的engine_cost表和server_cost表储存着成本不同层次的操作的常数 server_cost表中的成本常数:
disk_temptable_create_cost: 创建基于硬盘的表时的成本 row_evaluate_cost: 读取并检测一条记录是否符合搜索条件的成本 … engine_cost表中的成本常数：
io_block_read_cost: 从硬盘上读取一个块的成本 memory_block_read_cost: 从内存中读取一个块的成本 显示表的信息（包括统计数据->用于估计成本 ）： show table status [like ‘…’] 显示表的索引的信息: show index from <table_name> 统计数据的储存方式：永久性储存（硬盘），非永久性储存（内存）— innodb_stats_persistent 系统变量控制 永久性储存:
统计数据储存在两个表中: innodb_index_stats表： 关于索引的统计数据 innodb_table_stats表： 关于表的统计数据
n_rows 表中记录条数，为估计值 。采样若干个聚簇索引的叶节点->平均每个叶节点的记录数 * 叶节点数量 。采样数量由innodb_stats_persistent_sample_pages系统变量指定，或由表的属性STATS_SAMPLE_PAGES指定。 聚簇索引页数量 其他索引页的数量 innodb_stats_method系统变量：统计每个索引列中不重复的值的数量时，如何对待NULL（将其看作不同值、看作相同值、忽略） innodb_stats_method系统变量：是否自动计算统计数据–若发生改动的记录数量超过表大小的10%，则更新 （或单独设置表的属性STATS_AUTO_RECALC） 手动更新表的统计信息：ANALYZE TABLE <表名> IN子查询：
若可以转换为 半连接（semi-join）的形式 （不关心子查询结果中的重复），优先转化为半连接。可选的半连接形式：
table pullout: 子查询的 查询列表中只有主键 或 唯一索引列时，直接将子查询的条件上拉到外层的from中 duplicate weedout: 使用临时表消除半连接结果中的重复 LooseScan 松散扫描 Semi-join Materialization 半连接物化 FirstMatch 首次匹配：对外层查询的每一条记录，检测子查询中是否存在符合条件的记录，若存在则加入 若无法进行半连接，则可选择：
物化（建立临时表）子查询后，执行查询 将in转化为exists形式 EXPLAIN语句：输出某个 select delete insert replace update 语句的执行计划 explain select / delete / . . . . . . . . . .
缓冲池大小由innodb_buffer_pool_size启动项 控制 BufferPool：前部为若干 控制块 ，后部为若干 缓冲页 ， 每个控制块对应一个缓冲页（代表该缓冲页） 控制块中包next指针、prev指针，通过双链表 组织缓冲页
free链表：空的缓冲页 flush链表：脏页。更新了缓冲页中的数据，使该缓冲页变为脏页(dirty page) ，在未来刷新到磁盘 LRU链表：通过LRU（最近最少使用）的策略淘汰缓冲页 预读
线性预读：如果顺序访问某个区的页面的数量超过 系统变量innodb_read_ahead_threshold， 则将下一个区中的所有页面异步读入BufferPool 随机预读：如某个区的13个连续的页面被读入BufferPool，异步地将该区中其他所有页面读入BufferPool 优化LRU-将LRU链表分为两段： 前段–热数据（young区） 后段–冷数据（old区） （系统变量innodb_old_blocks_pct控制young所占的比例）
页面初次从磁盘上加载到BufferPool，放到old区头部—避免预读的页面不能进行后续访问的优化，且将使用频率较高的young区淘汰 对于在old区的页面，如果后续访问的时间与第一次访问的时间间隔小于 某个值（系统变量innodb_old_blocks_time），则不会被移动到young区头部 刷新脏页到磁盘
BUF_FLUSH_LRU ：扫描LRU链表尾部的部分页面，将扫描到的脏页写入磁盘 （系统变量innodb_lru_scan_depth 指定扫描数量） BUF_FLUSH_LIST : 刷新flush链表中的部分页面 BUF_FLUSH_SINGLE_PAGE : 从磁盘中加载一个页面到BufferPool时，将LRU链表尾部的单个脏页刷新到磁盘 … 为提高并发能力，使用独立的多个 BufferPool （系统变量 innodb_buffer_pool_instances 指定数量） BufferPool的内存空间由若干个chunk（块）组成 （5.7.5之后）（chunk大小 由 系统变量 innodb_chunk_size指定）
扫一扫
3539
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
