--查看数据库引擎
show engines;
ENGINE | SUPPORT | Comment | Transactions | XA分布式事务 | Savepoints回滚 |
FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
MyISAM | YES | MyISAM storage engine | 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 |
MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
ARCHIVE | YES | Archive storage engine | NO | NO | NO |
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
PERFORMANCE _SCHEMA | YES | Performance Schema | NO | NO | NO |
InnoDB 支持事务、行级锁定和外键,支持崩溃修复和自增列,通过事务保证数据的完整性,有缓冲池,用于缓冲数据和索引,缺点读写效率差,占用的数据空间大
MyISAM 读取效率较高,占用数据空间较少,但不支持事务、不支持行级锁、不支持外键等特性。因为不支持行级锁,因此在添加和修改操作时,会执行锁表操作,所以它的写入效率较低。
MEMORY 基于哈希的,存储在内存中,用于临时表
ARCHIVE 归档存储引擎
InnoDB索引
索引方法 B+树索引,哈希索引
B+树索引
B+树是为磁盘或者其他直接存储辅助设备设计的平衡查找树,所有的记录节点都是按照键值的大小顺序的存放在同一层的叶子节点中,所有的叶子节点组成一个链表,所以B+树支持范围操作。B+树索引可以分为聚集索引和非聚集索引。
索引 | 叶子节点 | 非叶子节点 | |
---|---|---|---|
聚集索引 | 数据在叶子节点上 | 非叶子节点保存键值和指向数据的偏移量 | 只有一个聚集索引 |
非聚集索引 | 叶子节点并不包含行记录的全部数据 | 可以有多个非聚集索引 |
索引类型
Normal 普通索引
Unique 唯一索引
Full Text 全文索引
SPATIAL 空间索引 (GEOMETRY,POINT,LINESTRING,POLYGON)
全文检索将存储与数据库的任意内容查找出来的技术,它根据数据中的词、段、句等信息进行统计分析来实现。全文检索通常通过倒排索引来实现,倒排索引与B+树一样,也是一种索引结构。倒排索引通过在辅助表中存储单词和单词出现的位置映射来实现。
alter table users add unique index uniq_username (username) using btree
--IDX_XXX
--UNIQ_XXX
一个索引最多可以包含 16 列
建立索引时,数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
索引的最左前缀
EXPLAIN 输出列
MySQL :: MySQL 5.6 Reference Manual :: 8.8.2 EXPLAIN Output Format
柱子 | JSON 名称 | 意义 |
---|---|---|
id | select_id | SELECT 标识符_ |
select_type | 没有任何 | SELECT 类型_ |
table | table_name | 输出行的表 |
partitions | partitions | 匹配的分区 |
type | access_type | 联接类型 |
possible_keys | possible_keys | 可供选择的索引 |
key | key | 实际选择的索引 |
key_len | key_length | 所选密钥的长度 |
ref | ref | 与索引比较的列 |
rows | rows | 估计要检查的行数 |
filtered | filtered | 按表条件过滤的行百分比 |
Extra | 没有任何 | 附加信息 |
事务隔离级别
MySQL :: MySQL 5.6 Reference Manual :: 14.7.2.1 Transaction Isolation Levels
REPEATABLE READ 可重复读
READ COMMITTED 提交读 读取已提交
READ UNCOMMITTED 未提交读 读取未提交
SERIALIZABLE 可串行化
--查看事务级别
select @@tx_isolation;
show variables like 'tx_isolation'
--设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
--例如
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1、脏读(dirty read):一个事务可以读取另一个尚未提交事务的修改数据。
2、非重复读(nonrepeatable read):在同一个事务中,同一个查询在T1时间读取某一行,在T2时间重新读取这一行时候,这一行的数据已经发生修改,可能被更新了(update),也可能被删除了(delete)。
3、幻像读(phantom read):在同一事务中,同一查询多次进行时候,由于其他插入操作(insert)的事务提交,导致每次返回不同的结果集。
行锁是建立在索引的基础上。
普通索引的数据重复率过高导致索引失效,行锁升级为表所
ACID (atomicity consistency isolation durability)原子性 一致性 隔离性 耐久性
保存在磁盘的数据格式
数据块:由多个磁盘 block 组成的块,存储引擎负责管理数据块。
mysql索引以B树格式保存
B+树上的叶子结点存储关键字以及相应记录的地址,叶子结点以上各层作为索引使用。
MyISAM .MYD .MYI .frm(表定义)
InnoDB .ibd(数据文件、索引文件) .frm
MySQL 整个查询执行过程,总的来说分为 5 个步骤 :
- 客户端向 MySQL 服务器发送一条查询请求
- 服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果,否则进入下一阶段
- 服务器进行 SQL解析、预处理、再由优化器生成对应的执行计划
- MySQL 根据执行计划,调用存储引擎的 API来执行查询
- 将结果返回给客户端,同时缓存查询结果