13518219792
我们仍然使用两个会话，一个会话 run，用于运行主 SQL；另一个会话 ps，用于进行 performance_schema 的观察：
创新互联建站专注为客户提供全方位的互联网综合服务，包含不限于做网站、网站设计、汪清网络推广、重庆小程序开发、汪清网络营销、汪清企业策划、汪清品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联建站为所有大学生创业者提供汪清建站搭建服务，24小时服务热线：18980820575，官方网址：www.cdcxhl.com
主会话线程号为 29，
将 performance_schema 中的统计量重置，
临时表的表大小限制取决于参数 tmp_table_size 和 max_heap_table_size 中较小者，我们实验中以设置 max_heap_table_size 为例。
我们将会话级别的临时表大小设置为 2M（小于上次实验中临时表使用的空间），执行使用临时表的 SQL：
查看内存的分配记录：
会发现内存分配略大于 2M，我们猜测临时表会比配置略多一点消耗，可以忽略。
查看语句的特征值：
可以看到语句使用了一次需要落磁盘的临时表。
那么这张临时表用了多少的磁盘呢？
我们开启 performance_schema 中 waits 相关的统计项：
重做实验，略过。
再查看 performance_schema 的统计值：
可以看到几个现象：
1. 临时表空间被写入了 7.92MiB 的数据。
2. 这些数据是语句写入后，慢慢逐渐写入的。
来看看这些写入操作的特征，该方法我们在 实验 03 使用过：
可以看到写入的线程是 page_clean_thread，是一个刷脏操作，这样就能理解数据为什么是慢慢写入的。
也可以看到每个 IO 操作的大小是 16K，也就是刷数据页的操作。
结论：
我们可以看到，
1. MySQL 会基本遵守 max_heap_table_size 的设定，在内存不够用时，直接将表转到磁盘上存储。
2. 由于引擎不同（内存中表引擎为 heap，磁盘中表引擎则跟随 internal_tmp_disk_storage_engine 的配置），本次实验写磁盘的数据量和 实验 05 中使用内存的数据量不同。
3. 如果临时表要使用磁盘，表引擎配置为 InnoDB，那么即使临时表在一个时间很短的 SQL 中使用，且使用后即释放，释放后也会刷脏页到磁盘中，消耗部分 IO。
1、从内存中读取数据是微秒级别的。而从磁盘读则是毫秒级别的。二者相差一个数量级。所以想优化数据库，第一个要做到的就是优化io。
2、key_buffer_size[global]设置的内存区域大小缓存了myisam表的索引。由于myisam只缓存索引在内存中，并不缓存数据在内存，所以如果内存允许，要让这个参数足够能容纳所有myisam的所有索引来提高性能。另外，在myisam表上，尽量让所有的查询条件都限制在索引上，以便能让缓存替我们提高查找效率。
3、bulk_insert_buffer_size[thread]仅仅用在myisam中，用于在插入数据的时候临时缓存数据。当我们使用如下的写入语句的时候，会使用这个内存区域帮助批量写入数据文件：
insert ... select ...
insert into ... values ...
MySQL 自身内存规划
说到 MySQL 自身的内存规划，最先想到的就是 MySQL 中各种 buffer 的大小，innodb buffer pool 就是最鹤立鸡群的那个。innodb_buffer_pool_size 参数的大小究竟如何设置，才能保证 MySQL 的性能呢？在官网文档中可以找到这个参数的一些描述：
A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.
意思是在专用数据库服务器上，可以将 innodb_buffer_pool_size 设置为计算机物理内存大小的 80％。在许许多多前辈的的经验中了解到，此参数的值设置为物理内存的 50%~80% 颇为合理。
举个栗子：
innodb buffer pool 分配 76G，每个连接线程最大可用 160M，最大有 3000 连接数，最大可能使用内存总量 545G，但是这台实例所在服务器的物理内存仅仅有 97G，远超物理内存总量。结果可想而知，这个实例在运行中经常被 oom-killer 杀死，想必原因之一即是因为一开始 MySQL 自身的内存规划欠妥。
innodb buffer pool 缓存数据的作用相信大家都懂，比如这个 case 中，可以发现该实例为写密集，读请求很少，innodb buffer 对性能改善作用不大，80% 的内存没必要，完全可以降低到物理内存的50%。
MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：
· MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。
· MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。
首先创建表
我们能够看到，表是不支持TEXT字段的
我们再看下文件系统
只有一个保存表结构的文件
下面我们再看下表的索引
首先，新建两个索引
我们查看当前索引类型
存在两个索引，一个为默认的，一个是指定的BTree。
接下来我们查看表的状态
Memory存储引擎表和临时表的区别
临时表分两类：系统使用临时表，create temporary table 建立的临时表。无论哪种表，只有当前session是可见的。而Memory表是所有线程都可以使用的。
系统使用临时表又分为两类：查过限制使用Myisam临时表，未超过限制使用Memory表。
使用场景
注意一点是：Memory数据易丢失，所以要求数据可再生
memory存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB，MyISAM存储引擎不同。
OK，这里我们讲解一些memory存储引擎的文件存储形式，索引类型，存储周期和优缺点。
每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型。该文件只存储表的结构，而其数据文件，都是存储在内存中的，这样有利于对数据的快速的处理，提高整个表的处理效率。
值得注意的是：服务器需要有足够的内存来维持memory存储引擎的表的使用。如果不需要了，可以释放这些内存，甚至可以删除不需要的表。
Memory存储引擎默认使用哈希(HASH)索引，其速度比使用B型树(BTREE)索引快。如果我们需要使用B型树索引，可以在创建索引时选择使用。
这里来整理一个小的技巧：
Memory存储引擎通常很少用到，至少我是没有用到过。因为Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。
如果重启机器或者关机，表中的所有数据都将消失，因此，基于Memory存储引擎的表的生命周期都比较短，一般都是一次性的。
Memory表的大小是受到限制的，表的大小主要取决于2个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定，max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
因此，其基于内存中的特性，这类表的处理速度会非常快，但是，其数据易丢失，生命周期短。基于其这个缺陷，选择Memory存储引擎时需要特别小心。
存储引擎是什么？
MySQL中的数据用各种不同的技术存储在文件(或者内存)中 这些技术中的每一种技术都使用不同的存储机制 索引技巧 锁定水平并且最终提供广泛的不同的功能和能力 通过选择不同的技术 你能够获得额外的速度或者功能 从而改善你的应用的整体功能
例如 如果你在研究大量的临时数据 你也许需要使用内存存储引擎 内存存储引擎能够在内存中存储所有的表格数据 又或者 你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型) MySQL默认配置了许多不同的存储引擎 可以预先设置或者在MySQL服务器中启用 你可以选择适用于服务器 数据库和表格的存储引擎 以便在选择如何存储你的信息 如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性
选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因 其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储 遗憾的是 其它类型的数据库解决方案采取的 一个尺码满足一切需求 的方式意味着你要么就牺牲一些性能 要么你就用几个小时甚至几天的时间详细调整你的数据库 使用MySQL 我们仅需要修改我们使用的存储引擎就可以了
在这篇文章中 我们不准备集中讨论不同的存储引擎的技术方面的问题(尽管我们不可避免地要研究这些因素的某些方面) 相反 我们将集中介绍这些不同的引擎分别最适应哪种需求和如何启用不同的存储引擎 为了实现这个目的 在介绍每一个存储引擎的具体情况之前 我们必须要了解一些基本的问题
如何确定有哪些存储引擎可用
你可以在MySQL(假设是MySQL服务器 以上版本)中使用显示引擎的命令得到一个可用引擎的列表
mysql show engines; + + + + | Engine | Support | Comment | + + + + | MyISAM | DEFAULT | Default engine as of MySQL with great performance | | HEAP | YES | Alias for MEMORY | | MEMORY | YES | Hash based stored in memory useful for temporary tables | | MERGE | YES | Collection of identical MyISAM tables | | MRG_MYISAM | YES | Alias for MERGE | | ISAM | NO | Obsolete storage engine now replaced by MyISAM | | MRG_ISAM | NO | Obsolete storage engine now replaced by MERGE | | InnoDB | YES | Supports transactions row level locking and foreign keys | | INNOBASE | YES | Alias for INNODB | | BDB | NO | Supports transactions and page level locking | | BERKELEYDB | NO | Alias for BDB | | NDBCLUSTER | NO | Clustered fault tolerant memory based tables | | NDB | NO | Alias for NDBCLUSTER | | EXAMPLE | NO | Example storage engine | | ARCHIVE | NO | Archive storage engine | | CSV | NO | CSV storage engine | + + + + rows in set ( sec)
这个表格显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎
对于MySQL 以前版本 可以使用mysql show variables like have_% (显示类似 have_% 的变量):
mysql show variables like have_% ; + + + | Variable_name | Value | + + + | have_bdb | YES | | have_crypt | YES | | have_innodb | DISABLED | | have_isam | YES | | have_raid | YES | | have_symlink | YES | | have_openssl | YES | | have_query_cache | YES | + + + rows in set ( sec)
你可以通过修改设置脚本中的选项来设置在MySQL安装软件中可用的引擎 如果你在使用一个预先包装好的MySQL二进制发布版软件 那么 这个软件就包含了常用的引擎 然而 需要指出的是 如果你要使用某些不常用的引擎 特别是CSV RCHIVE(存档)和BLACKHOLE(黑洞)引擎 你就需要手工重新编译MySQL源码
使用一个指定的存储引擎
你可以使用很多方法指定一个要使用的存储引擎 最简单的方法是 如果你喜欢一种能满足你的大多数数据库需求的存储引擎 你可以在MySQL设置文件中设置一个默认的引擎类型（使用storage_engine 选项）或者在启动数据库服务器时在命令行后面加上 default storage engine或 default table type选项
更灵活的方式是在随MySQL服务器发布同时提供的MySQL客户端时指定使用的存储引擎 最直接的方式是在创建表时指定存储引擎的类型 向下面这样:
CREATE TABLE mytable (id int title char( )) ENGINE = INNODB
你还可以改变现有的表使用的存储引擎 用以下语句:
ALTER TABLE mytable ENGINE = MyISAM
然而 你在以这种方式修改表格类型的时候需要非常仔细 因为对不支持同样的索引 字段类型或者表大小的一个类型进行修改可能使你丢失数据 如果你指定一个在你的当前的数据库中不存在的一个存储引擎 那么就会创建一个MyISAM(默认的)类型的表
各存储引擎之间的区别
为了做出选择哪一个存储引擎的决定 我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能 这种功能使我们能够把不同的存储引擎区别开来 我们一般把这些核心功能分为四类:支持的字段和数据类型 锁定类型 索引和处理 一些引擎具有能过促使你做出决定的独特的功能 我们一会儿再仔细研究这些具体问题
字段和数据类型
虽然所有这些引擎都支持通用的数据类型 例如整型 实型和字符型等 但是 并不是所有的引擎都支持其它的字段类型 特别是BLOG（二进制大对象）或者TEXT文本类型 其它引擎也许仅支持有限的字符宽度和数据大小
这些局限性可能直接影响到你可以存储的数据 同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响 这些区别能够影响你的应用程序的性能和功能 因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策
锁定
数据库引擎中的锁定功能决定了如何管理信息的访问和更新 当数据库中的一个对象为信息更新锁定了 在更新完成之前 其它处理不能修改这个数据(在某些情况下还不允许读这种数据)
锁定不仅影响许多不同的应用程序如何更新数据库中的信息 而且还影响对那个数据的查询 这是因为查询可能要访问正在被修改或者更新的数据 总的来说 这种延迟是很小的 大多数锁定机制主要是为了防止多个处理更新同一个数据 由于向数据中插入信息和更新信息这两种情况都需要锁定 你可以想象 多个应用程序使用同一个数据库可能会有很大的影响
不同的存储引擎在不同的对象级别支持锁定 而且这些级别将影响可以同时访问的信息 得到支持的级别有三种:表锁定 块锁定和行锁定 支持最多的是表锁定 这种锁定是在MyISAM中提供的 在数据更新时 它锁定了整个表 这就防止了许多应用程序同时更新一个具体的表 这对应用很多的多用户数据库有很大的影响 因为它延迟了更新的过程
页级锁定使用Berkeley DB引擎 并且根据上载的信息页( KB)锁定数据 当在数据库的很多地方进行更新的时候 这种锁定不会出现什么问题 但是 由于增加几行信息就要锁定数据结构的最后 KB 当需要增加大量的行 也别是大量的小型数据 就会带来问题
行级锁定提供了最佳的并行访问功能 一个表中只有一行数据被锁定 这就意味着很多应用程序能够更新同一个表中的不同行的数据 而不会引起锁定的问题 只有InnoDB存储引擎支持行级锁定
建立索引
建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能 不同的存储引擎提供不同的制作索引的技术 有些技术也许会更适合你存储的数据类型
有些存储引擎根本就不支持索引 其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)
事务处理
事务处理功能通过提供在向表中更新和插入信息期间的可靠性 这种可靠性是通过如下方法实现的 它允许你更新表中的数据 但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改 例如 在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改 你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成 才接受所做的修改 如果任一项操作失败了 你都可以取消这个事务处理 这些修改就不存在了 如果这个事务处理过程完成了 我们可以通过允许这个修改来确认这个操作
lishixinzhi/Article/program/MySQL/201311/29301
