wang2020
1.mysql的历史和体系结构
1979年：TcX公司 Monty Widenius，Unireg
1996年：发布MySQL1.0，Solaris版本，Linux版本
1999年：MySQL AB公司，瑞典
2003年：MySQL 5.0版本，提供视图、存储过程等功能
2008年：sun 收购
2009年：oracle收购sun
2009年：Monty成立MariaDB
MySQL和MariaDB
官方网址：
https://www.mysql.com/
http://mariadb.org/
官方文档
https://dev.mysql.com/doc/
https://mariadb.com/kb/en/
版本演变：
MySQL： 5.1 --> 5.5 --> 5.6 --> 5.7
MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3
MySQL体系结构
MariaDB [(none)]> show variables like '%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 151 | #就是整个MySQL实例的最大连接数限制 | max_user_connections | 0 | #是单个用户的最大连接数，这里未指明是哪个用户，是任意一个用户。 +-----------------------+-------+ 3 rows in set (0.00 sec)
2.Management Serveices & Utilities：系统管理和控制工具。
备份和恢复的安全性，复制，集群，管理，配置，迁移和元数据
3.Connection Pool：连接池
进行身份验证、线程重用，连接限制，检查内存，数据缓存；管理用户的连接，线程处理等需要缓存的需求。
4.SQL Interface：SQL 接口
进行 DML、DDL，存储过程、视图、触发器等操作和管理；用户通过 SQL 命令来查询所需结果。
5.Parser：解析器
查询翻译对象的特权；SQL 命令传递到解析器的时候会被解析器验证和解析。
6.Optimizer：查询优化器
访问路径的统计数据；
MariaDB [(none)]> select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on 1 row in set (0.00 sec)
在 MySQL 优化语句过程中，可以通过设置 optimize_switch 控制优化行为。在生产环境上，某时间段 MySQL 服务器压力特别大，load 一度达到了 100，查询发现数据库中有大量的 sql 语句 state 状态 result sorting ，result sorting 这种排序特别消耗 cpu 和内存资源。抽取其中的一条 sql 查看执行计划。
7.Cache 和 Buffer：查询缓存
全局和引擎特定的缓存和缓冲区；
MariaDB [(none)]> show variables like '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 33554432 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 7 rows in set (0.00 sec)
8.Engine：存储引擎
MariaDB [(none)]> show engines; +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec)
MySQL 的 Windows 版本默认存储引擎为 InnoDB，InnoDB 支持事务，并且提供行级的锁定。
2.存储引擎
插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
存储引擎比较：
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html
单进程，多线程
诸多扩展和新特性
提供了较多测试组件
开源
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and later.
1.MyISAM引擎特点：
不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎
适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
MyISAM引擎文件：
tbl_name.frm: 表格式定义
tbl_name.MYD: 数据文件
tbl_name.MYI: 索引文件
2.InnoDB引擎特点
支持事务，适合处理大量短期事务
行级锁
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
表空间文件：datadir定义的目录下
数据文件：ibddata1, ibddata2, ...
每个表单独使用一个表空间存储表的数据和索引
启用：innodb_file_per_table=ON
两类文件放在数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm
其它存储引擎
Performance_Schema:Performance_Schema数据库
Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
MariaDB支持的其它存储引擎：
OQGraph
SphinxSE
TokuDB
Cassandra
CONNECT
SQUENCE
3.管理存储引擎
查看mysql支持的存储引擎:
show engines;
MariaDB [mysql]> show engines; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'engnes' at line 1 MariaDB [mysql]> show engines; +--------------------+---------+---------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+---------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | +--------------------+---------+----------------------------------+--------------+------+------------+ 8 rows in set (0.00 sec)
查看当前默认的存储引擎:
show variables like '%engine%';
MariaDB [(none)]> show variables like '%engine%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | | | enforce_storage_engine | | | storage_engine | InnoDB | +----------------------------+--------+ 4 rows in set (0.01 sec)
设置默认的存储引擎：
vim /etc/my.conf
[mysqld] default_storage_engine= InnoDB;
管理存储引擎
查看库中所有表使用的存储引擎
Show table status from db_name;
查看库中指定表的存储引擎
show table status like 'tb_name'; show create table tb_name;
设置表的存储引擎：
CREATE TABLE tb_name(... ) ENGINE=InnoDB; #创建表时指定存储引擎 ALTER TABLE tb_name ENGINE=InnoDB; #修改表的存储引擎
MySQL中的系统数据库
mysql数据库：是mysql的核心数据库,类似于sql server中的master库,主要负责存储数据库的用户,权限设置,关键字等mysql自己需要使用的控制和管理信息
PERFORMANCE_SCHEMA:MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表
information_schema数据库:MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在。information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）
没有帐号? 立即注册