MySQL OCP-09-存储引擎

1月 28th, 2017

— 存储引擎和MySQL;
1.每个存储引擎具有一组特定运行特征:
1.这些特征包括用于管理查询争用的锁的类型以及该存储引擎是否支持事务;
2.这些引擎属性对查询处理性能,并发性以及死锁预防具有一定影响;
2.虽然可以使用许多其他存储引擎,但对于大多数用例,InnoDB都是最适合的;

— 可用存储引擎;
1.MySQL提供并维护多个存储引擎,MySQL服务器还可以与许多第三方存储引擎兼容;
2.MySQL存储引擎是数据库服务器内的低级别引擎,它负责存储和检索数据,并且可以通过内部MySQL API进行访问,在某些情况下,可以由应用程序直接访问;
3.当前支持的存储引擎:
• InnoDB
• MyISAM
• MEMORY
• ARCHIVE
• FEDERATED
• EXAMPLE
• BLACKHOLE
• MRG_MYISAM
• NDBCLUSTER
• CSV
TIPS:
1.请注意,InnoDB和NDBCLUSTER是具有事务性的唯一两个MySQL存储引擎;
2.请注意,一个应用程序可以在任何给定时间使用多个存储引擎;

— InnoDB存储引擎;
InnoDB是MySQL的默认存储引擎,具有高可靠性和高性能的特点,同时也被认为是效率最高的引擎之一:
• 事务安全(遵从 ACID):通过事务提交,回滚以及用于保护用户数据的故障恢复功能,可以实现ACID遵从性;
• MVCC(Multi-Versioning Concurrency Control,多版本并发控制)
– InnoDB行级别锁定;
– Oracle样式一致非锁定读取;
• 表数据进行整理来优化基于主键的查询;
• 支持外键引用完整性约束:包括级联删除和更新;
• 大型数据卷上的最大性能;
• 将对表的查询与不同存储引擎混合:在同一语句内,可以将InnoDB表与来自其他MySQL存储引擎的表混合;
• 出现故障后快速自动恢复:支持一致而联机的逻辑备份;
• 用于在内存中缓存数据和索引的缓冲区池;
• 全文索引:可以在文本列中有效地搜索单词或短语;

— InnoDB作为默认存储引擎;
1.查看默认引擎:SHOW VARIABLES LIKE ‘%engine%’;SELECT @@default_storage_engine;
2.设置默认引擎:SET default_storage_engine = MyISAM;

— InnoDB功能;
1.[a]需要InnoDB Barracuda文件格式;
2.[b]在服务器中(通过加密功能)而不是在存储引擎中实现;
3.[c]在服务器中而不是在存储产品中实现;使用MEB进行备份时例外,其不是在服务器级别进行;

— 显示存储引擎设置;
1.查看默认引擎:
• SHOW VARIABLES LIKE ‘%engine%’;
• SELECT @@default_storage_engine;
2.使用SHOW确认每个表的存储引擎:
• SHOW CREATE TABLE City\G
• SHOW TABLE STATUS LIKE ‘CountryLanguage’\G
3.使用INFORMATION_SCHEMA确认每个表的存储引擎:
SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’ AND TABLE_SCHEMA = ‘world_innodb’\G

— 设置存储引擎;
1.在启动配置文件中设置服务器存储引擎:
[mysqld]
default-storage-engine=
2.设置默认引擎:
• SET default_storage_engine = MyISAM;
• SET @@storage_engine=
;
3.使用CREATE TABLE语句指定:CREATE TABLE t (i INT) ENGINE =
;

— 将现有表转换为InnoDB;
1.使用ALTER TABLE更改存储引擎:
1.语法:ALTER TABLE t ENGINE = InnoDB;
2.使用ALTER TABLE更改引擎是一项成本很高的操作,因为它在内部将所有数据从一个引擎复制到另一个引擎;
3..不要将mysql数据库(例如user或host)中的MySQL系统表转换为InnoDB,此操作不受支持,系统表使用MyISAM引擎;
2.从其他存储引擎克隆表:
1.语法INSERT INTO SELECT * FROM ;
2.创建作为使用其他存储引擎的表的克隆的InnoDB表时,还可以在插入数据后创建索引;
3.以较小的块插入大型表以获得更大的控制:通过添加Key的范围,多次插入表;
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey < = somethingelse; 补充:插入了所有记录后,可以重命名表; 1.在转换大型表的过程中,增加InnoDB缓冲区池的大小来减少磁盘I/O; 2.还可以增加InnoDB日志文件的大小; -- InnoDB系统表空间; 1.InnoDB使用两个主要的基于磁盘的资源来运行: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 2.日志文件(ib_logfileN文件):记录重做和恢复的事务活动; 2.InnoDB在表空间中存储数据,索引,元数据,日志和缓冲区; 1.默认情况下(5.6之后),数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdataN文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 3.共享表空间是逻辑概念,可以包含多个文件;可以将共享表空间中的最后一个文件配置为自动扩展,在这种情况下,如果表空间装满,InnoDB会自动扩展该表空间; 4.默认情况下,共享表空间还包含回滚段,事务修改行时,将在回滚段中存储撤消日志信息;此信息用于回滚失败的事务,通过将innodb_undo_logs选项设置为非零值并配置innodb_undo_tablespaces的值,来将回滚段移出共享表空间; 1.innodb_undo_logs:定义在InnoDB引擎使用的系统表空间中,一个事务可以使用的回滚段的数量; 2.innodb_undo_tablespaces:当innodb_undo_logs参数为非零值时,undo日志使用的表空间文件个数;默认情况下所有的重做日志都在系统表空间中;可以通过innodb_undo_directory参数来指定文件的目录; TIPS:除了系统表空间可以包含多个文件,其它的数据表空间只能包含一个文件; -- 数据表空间; 1.除了系统表空间之外,InnoDB还在数据库目录中创建另外的表空间,用于每个InnoDB表的.ibd文件; 1.InnoDB创建的每个新表在数据库目录中设置一个.ibd文件来搭配表的.frm文件; 2..ibd文件用作表自己的表空间文件,存储表内容和索引; 3.InnoDB数据字典和回滚段存放在共享表空间; 4.可以使用innodb_file_per_table选项控制此设置; 5.您需要默认设置来使用一些其他功能,例如表压缩和快速截断; 2.基于表的表空间具备以下优势: 1.表压缩 2.空间回收(通过 TRUNCATE) 3.动态行格式 3.(可选)在共享表空间中存储数据: 1.使用skip_innodb_file_per_table选项; 2.在启动时或者会话过程中进行设置,将innodb_file_per_table选项设置为OFF;禁用该选项不会影响已经创建的任何InnoDB表的可访问性;如果要修改这个值的话要关闭服务器,修改配置文件,启动服务这几步; 4.使用共享表空间可获得以下优势: 1.移除大量数据的语句使用较少文件系统开销,例如DROP TABLE或TRUNCATE TABLE; 2.避免冗余临时数据文件; TIPS:可以混合同一数据库中不同表中的表空间类型,更改该设置仅会更改已创建的新表的默认值; -- 表空间目录结构; MySQL服务器使用磁盘空间有几种方式: 1.数据库目录:每一个数据库对应一个数据目录(datadir)下的目录,不管你创建哪种类型的表.比如,一个数据库目录可以包含MYISAM引擎的表,INNODB引擎的表或者混合的表; 1..ibd文件:用作表自己的表空间文件,存储表内容和索引; 2..frm文件(Table Format Files):包含了表结构的描述信息,每一个表都有一个.frm文件在对应的数据库目录下.它与表使用的哪种引擎没有关系; 3..MYD/.MYI:由MYISAM存储引擎在适当的数据库目录下创建的数据库数据文件和索引文件; 2.InnoDB存储引擎: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 1.默认情况下,数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdata1文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 2.日志文件(ib_logfileN文件):记录回滚和恢复的事务活动; 3.服务器日志文件和状态文件:这些文件包含服务器上执行过的语句信息,日志被用于复制和数据恢复,获得优化查询性能的信息和误操作信息; 1.auto.cnf:保存服务器的server_id; 2.mysql.sock:套节字文件; 3.hosname.err:错误日志; 4.hostname.pid:运行进程id; 5.mysql_upgrade_info:升级信息; -- 共享表空间配置; 1.通过添加数据文件增加共享表空间大小; 1.在my.cnf文件中使用innodb_data_file_path选项,值应该是一个指定项列表: [mysqld] innodb_data_file_path=datafile_spec1[;datafile_spec2]... 2.如果命名了多个数据文件,则通过分号[;]符号分隔这些文件; 3.增加InnoDB系统表空间的一种更容易(但是不太可取)的方式是从一开始就将其配置为自动扩展;在表空间定义中指定最后一个数据文件的autoextend属性,然后,InnoDB在用完空间时会以64MB增量自动增加该文件的大小; 4.可以通过设置innodb_autoextend_increment系统变量的值来更改该增量大小,以MB为单位; 5.如果使用关键字autoextend定义了最后一个数据文件,重新配置表空间的过程必须考虑最后一个数据文件已经增长到的大小;获取数据文件的大小,将其向下舍入到1024×1024字节(=1MB)的最近倍数,并在innodb_data_file_path中显式指定舍入的大小;然后,可以添加其他数据文件(使用尚不存在的文件名); 6.仅innodb_data_file_path中的最后一个数据文件可以指定为自动扩展; 2.配置示例:创建一个表空间,其中包含一个名为ibdata1且大小为50MB(固定)的数据文件和一个名为ibdata2且大小为50MB(自动扩展)的数据文件: [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 3.默认情况下将文件放置在data目录中,如果需要,使用innodb_data_home_dir参数显式指定文件位置; -- 日志文件和缓冲区:图表; 1.日志缓冲区: 1.客户机执行事务时,其进行的更改存放在InnoDB日志中,最新日志内容缓存在内存中(日志缓冲区); 2.主要包括:事务记录和重做日志记录等; 3.缓存的日志信息会在事务提交时写入并刷新到磁盘上的日志文件中,虽然也可能提前发生该操作; 4.如果修改表时发生故障,将使用日志文件进行自动恢复; 5.MySQL服务器重新启动时,它会重新应用日志中记录的更改,以确保表中反映所有已提交事务; 6.更改InnoDB日志文件的数量和大小的信息: 1.innodb_log_buffer_size 2.innodb_log_compressed_pages 3.innodb_log_file_size 4.innodb_log_files_in_group 5.innodb_log_group_home_dir 2.缓冲区池: 1.InnoDB维护其自己的缓冲区池用于在主内存中缓存常用的数据和索引;InnoDB在表空间中存储其表和索引;InnoDB表可以非常大,甚至在文件大小限制为2GB的操作系统上也是如此; 2.主要是包括插入插销日志和更新撤销日志; 3.可以启用多个缓冲区池来最大程度地降低争用; 4.此高速缓存应用于如此多种类型的信息并且将处理速度提高如此多,应该将多达80%的数据库服务器的物理内存分配给InnoDB缓冲区池; 5.由innodb_buffer_pool_%参数控制; -- 配置缓冲区池以获得良好性能; InnoDB缓冲区池很大时,可以从内存(而不是硬盘)快速检索许多数据请求: 1.MySQL使用多个缓冲区池实例来最大程度地降低线程之间的争用:用来改进大型缓冲区池的局限; 2.存储在缓冲区池中或者从缓冲区池读取的每个页将基于散列值分配给一个缓冲区池:使用散列函数将缓冲区池中的每个页随机分配给一个缓冲区池 3.每个缓冲区池管理其自己的数据:每个缓冲区池管理其自己的空闲列表,刷新列表,LRU以及连接到缓冲区池的所有其他数据结构,并由其自己的缓冲区池互斥锁进行保护; 4.配置有下列选项: 1.innodb_buffer_pool_instances:默认情况下,MySQL配置八个缓冲区池实例,范围为1-64;仅当innodb_buffer_pool_size的值大于等于1GB时才生效; 2.innodb_buffer_pool_size:缓冲池的总大小,要保证每个缓冲池实例都大于1G; 5.在启动时预装入缓冲区池来提高性能: 1.innodb_buffer_pool_load_at_startup; 2.innodb_buffer_pool_dump_at_shutdown; -- NoSQL Memcached API; 1.在相同进程空间中运行:InnoDB支持具有集成的Memcached守护进程的插件,其在相同进程空间中运行,从而以较低开销进行高效数据通信; 2.使用Memcached API:对于某些操作,通过使用较简单的Memcached API(基于文本的协议和二进制协议),而不是使用SQL层,应用程序可以在提交SQL语 句时绕过所需的解析和优化阶段,并避免检查强类型数据的开销;此类型的应用程序称为NoSQL(Not Only SQL,不仅仅是SQL); 3.使用InnoDB存储:除了传统的Memcached环境(其在重新启动Memcached时会丢失键/值存储),InnoDB集成表示可以将Memcached存储配置为持久存储到InnoDB;因为InnoDB支持该存储,所以可以受益于InnoDB的其他功能; 1.自动持久存储到磁盘; 2.缓冲区和故障恢复的优势:进行保护以免于故障,中断和损坏;可以使用内存存储的Memcached存储(由强大而持久的InnoDB表支持)来获得速度和简化; 4.仍可以通过SQL访问基础表以进行报告,分析,即席查询,批量装入,集合运算(例如并集和交集)以及非常适合于SQL的表达性和灵活性的其他运算(例如汇总和聚合); 5.因为Memcached消耗相对较少的CPU并且易于控制其内存容量,所以可以与MySQL实例在同一系统上一起顺利运行; -- 配置Memcached; 1.配置Memcached表:SOURCE /scripts/innodb_memcached_config.sql(/usr/share/mysql/innodb_memcached_config.sql);
1.innodb_memcached_config.sql脚本设置Memcached配置数据库及其表;
1.创建innodb_memcache数据库;
2.cache_policies表:存放每个Memcached操作的后备存储策略;本地RAM高速缓存,InnoDB或两者;
3.Containers表:存放用作Memcached后备存储的每个InnoDB表的配置;
4.config_options表:存放separator和table_map_delimiter配置选项的值;
2.默认InnoDB后备存储是test数据库中的demo_test表,可以通过修改innodb_memcache数据库中的容器表来更改此项;
3.该插件启用许多可配置变量:SHOW VARIABLES LIKE ‘daemon_memcached%’;
2.安装Memcached插件:
INSTALL PLUGIN daemon_memcached SONAME “libmemcached.so”;
3.配置Memcached变量:
1.daemon_memcached_option:在启动时传递给Memcached守护进程的空格分隔的选项;
2.daemon_memcached_enable_binlog:启用Memcached操作的二进制日志记录,其允许复制Memcached API操作;
3.daemon_memcached_r_batch_size:控制读取批处理大小,默认值为1,出于性能考虑;
4.daemon_memcached_w_batch_size:控制写入批处理大小,默认值为1,所以会立即提交每个Memcached写入操作;
例子:可以从test.demo_test中查看;
— Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
— Bytes
— Value (c2) must be VARCHAR or CHAR type
— Flag (c3) is a 32 bits integer
— CAS (c4) is a 64 bits integer, per memcached define
— Exp (c5) is again a 32 bits integer

— 键/值数据;
1.使用Memcached的主要好处是读取或写入键/值数据时效率较高;
2.Memcached协议支持add/delete/set/get/incr和decr等操作;
3.每个操作处理高速缓存中的键/值对:对于键/值查找,Memcached件使用的直接低级别数据库访问路径比等效SQL查询高效得多;
1.键和值作为字符串来传递;
2.键不能包含空格,回车符,换行符,制表符等空白字符:由于Memcached协议的规定;
3.键类似于表中的主键,值类似于同一表中的另一列:在概念上,这类似于一个包含两列的表,其中键列是主键(因此是唯一的);
4.高速缓存是平面名称空间:
1.确保每个键都是唯一的;
2.例如,如果您提供其他表中的键/值,则在键前面附加该表名称;
5.值可以是简单字符串(或存储为字符串的数值)或序列化复杂数据:值存储为字符串,可以表示简单值,例如数值,字符串或已经序列化的复杂值(即,置于可以重新构造原始指针和嵌套的文本形式);必须在API接口的Memcached外部执行此序列化(例如,通过在代码中实现JavaSerializable接口)或者通过将复杂数据格式化为JSON或XML;
类似地,必须在访问memcached表的SQL语句中将简单数值从字符串转换为数值(例如,通过使用CAST()函数);

— 将NoSQL用于MySQL数据库;
1.使用InnoDB表作为Memcached操作的容器(后备存储);
1.Memcached 协议不支持简单聚合,联接数据或SQL协议支持的其他功能;
2.使用InnoDB表作为Memcached键/值对的容器,可以运行功能强大的SQL语句,来执行使用Memcached协议创建和修改的数据分组,聚合和联接;
2.键和值是字符串:
1.键最大大小为250字节;对于更长的键:
1.将键值散列到小于250字节的值;
2.为Memcached键重新配置允许的大小;
3.通过指定不同类的Memcached数据来使用多个InnoDB表作为Memcached容器;
1.在containers表中配置此项;
2.指定容器表的名称,模式和键列,值,标志以及其他设置;
3.每个容器具有一个Memcached名称,可以用来标识Memcached操作的容器;操作定位于名为“default”的容器,直到被要求更改容器;
4.例如,以get @@newcontainer形式发出Memcached请求,将后续请求重定向到名为“newcontainer”的容器;

— 引用完整性;
将相关的数据分割到单独表中时,可以使用主键和外键来加强表关系;
1.一个表的主键被另一个表中的键引用:
1.引用键称为外键;
2.每个表都需要主键:通过使用最常查询的列为每个表指定主键,如果没有明显主键则指定自动增量值;
2.关系确定SQL语句的结果:
1.INSERT到辅助表(而没有指向主表的外键)不会完成;
2.通过在联接的列上使用外键将提高JOIN性能:而且外键列需要添加索引;
3.UPDATE或DELETE数据时,可以使用CASCADE选项自动更新或删除辅助表中的任何相关数据;
3.可以查看示例数据库中的City表;
TIPS:一般不会使用外键;

— 多版本控制;
1.保存关于已更改行的旧版本的信息:
1.支持并发和回滚等事务功能;
2.InnoDB使用回滚段中的信息执行事务回滚中所需的撤消操作;
3.它还使用该信息来构建行的早期版本以实现一致读取;
2.使用称为回滚段的数据结构在表空间中存储信息:回滚段是包含撤消日志的存储区域,其默认情况下是系统表空间的一部分;
3.InnoDB向数据库中存储的每行添加三个字段:
1.DB_TRX_ID:六个字节的字段,指示插入或更新该行的最后一个事务的事务标识符;此外,删除在内部被视为更新处理,其中设置行的特殊位来将其标记为删除;
2.DB_ROLL_PTR:称为滚动指针的七个字节的字段;它指向写入回滚段的撤消日志记录,如果更新了行,撤消日志记录包含重建行被更新前的内容所需的信息;
3.DB_ROW_ID:包含行ID的六个字节的字段,随着插入新行而单调增加;如果InnoDB自动生成群集索引,该索引包含行ID值;否则,DB_ROW_ID列不显示在任何索引中;
4.仅当行放弃针对删除写入的更新撤消日志记录时,才物理地删除该行(及其索引):
1.这是快速清除操作;
2.清除线程可能需要更多资源,因为它可能会变慢并占用磁盘空间;
3.如果在表中按相同速度以小批量插入和删除行,清除线程会滞后,表会因为所有“死”行而变得越来越大,使所有操作受到磁盘限制并且非常慢;在这种情况下,通过使用innodb_max_purge_lag系统变量限制新行操作并向清除线程分配更多资源;

— 锁定;
InnoDB具有下列一般锁定属性:
1.InnoDB无需设置锁即可实现一致读取,因为它使用多版本控制,从而不需要锁;修改行的事务可以查看其自己版本的那些行,撤消日志使其他事务可以查看原始行;要强制SELECT语句锁定数据,可以向该语句添加锁定修饰符(FOR UPDATE, LOCK IN SHARE MODE);
2.需要锁时,InnoDB使用行级别锁定,与多版本控制一起,这将产生良好的查询并发性,因为给定表可以由不同客户机同时读取和修改;
3.InnoDB在发现需要锁时可以获取行锁,从不通过将锁转换为页锁或表锁来提升该锁;这样可以最大程度降低锁争用,提高并发性(虽然其对DDL操作使用表级别锁定);
4.可能会出现死锁,因为InnoDB在事务过程中直到需要锁时才获取锁,InnoDB可以删除死锁并回滚一个事务来解决死锁;
5.失败的事务最终开始超时,InnoDB将回滚这些事务;

— 下一键锁定;
1.InnoDB使用一种称为下一键锁定的算法:
1.使用行级别锁定;
2.搜索/扫描表索引并在索引记录上设置共享锁或互斥锁;
3.因此,行级别锁实际是索引记录锁;
2.InnoDB在索引记录上设置的下一键锁定会影响索引记录前面的“间隙”:
1.如果用户在索引中的记录上具有共享锁或互斥锁,其他用户无法按索引顺序正好在锁定记录前面(间隙)插入新记录;
3.锁定间隙可防止出现“幻影问题”:
1.同一查询在不同时间产生不同行集时,事务内会出现幻影问题;
2.在下面的示例中,第一个事务锁定大于10的任何值,即使该值不存在也是如此:
事务 1:
SET autocommit = ON;
START TRANSACTION;
SELECT c FROM t WHERE c > 10 FOR UPDATE;
事务 2:
INSERT INTO t(c) VALUES (50);

— 一致非锁定读取;
1.一致读取表示InnoDB使用多版本控制对查询提供某个时间点的数据库快照;查询看见在时间点之前提交的事务所进行的更改,不会看见后面或未提交事务进行的更改;
2.在幻灯片示例中,仅当会话2已提交了插入并且会话1也已提交(从而时间点超过会话2的提交)时,会话1才看见会话2插入的行;

— 减少死锁;
1.要减少死锁的可能性,使用事务,而不是LOCK TABLE语句(不升级锁):
1.保持事务较小并经常提交:从而其不会保持打开状态很长时间;
2.不同事务更新多个表时使用相同顺序的操作;
3.在WHERE子句中使用的列上创建索引;
2.如果事务由于死锁而失败则重新发出该事务:
1.死锁并不危险,只需要重试;
2.如果出现死锁,InnoDB会检测到该情况并回滚一个事务(牺牲者);
3.按固定顺序访问表和行。
4.向表添加精心选择的索引。
5.不要设置隔离级别以避免死锁。
1.隔离级别更改读取操作的行为,而死锁是由于写入操作而出现的;
6.监视死锁出现的频率;
1.使用SHOW ENGINE INNODB STATUS\G命令;

— 外键锁定;
InnoDB支持外键CONSTRAINT设置:
• 需要检查约束条件
• 用于 INSERT、UPDATE 和 DELETE
• 在要检查的记录上设置共享记录级别锁
• 在约束失败的情况下也将设置锁

1.约束就是在表中的一个或多个列值上放置的限制,从而积极强制执行完整性规则;约束使用索引来实现;
2.如果在表上定义了外键约束,将在用于引用外键约束的插入,更新或删除操作中的任何记录上放置共享记录级别锁;
3.InnoDB逐行检查外键约束;执行外键检查时,InnoDB在其必须查看的子或父记录上设置共享行级别锁;InnoDB立即检查外键约束;该检查不会延迟到事务提交时;
4.InnoDB在约束失败的情况下也设置这些锁;
5.幻灯片中的约束示例显示City表的CountryCode列与Country表的Code列相关;

— MyISAM存储引擎;
1.在硬盘上MyISAM引擎用三种文件管理表,这些文件都在数据目录中:
1..frm文件:存储表结构的定义;
2..MYD文件:存储表行的内容;
3..MYI文件:存储表的索引;
2.在操作系统上支持链接文件的特性,可以把数据文件和索引文件存放在不同的磁盘上以减小I/O:
1.查看当前服务器指定的默认存储引擎:show variables like ‘storage_engine’;
2.通过查看CREATE TABLE的帮助(? CREATE TABLE),我们可以看到,可以通过指定DATA DIRECTORY和INDEX DIRECTORY选项来把数据文件和索引文件存放在不同的目录;
1.创建存放数据文件和索引文件的目录:mkdir -p /tmp/mysql/data /tmp/mysql/index;
2.修改文件的权限:chown -R mysql:mysql /tmp/mysql;
3.创建表,并指定数据文件和索引文件的目录:CREATE TABLE t (id INTEGER) ENGINE=MyISAM DATA DIRECTORY=’/tmp/mysql/data’ INDEX DIRECTORY=’/tmp/mysql/index’;
4.此时就会在数据文件下创建真正数据文件和索引文件的链接;
5.有没有参数可以指定缺省的目录,每次创建表都去指定太费劲了吧;??????
6.分区表指定数据文件和索引文件;???????
3.MyISAM与其它引擎相比具有最灵活的AUTO_INCREMENT COLUMN功能,自动增长列:
1.创建自动增长列:create table t(id integer auto_increment primary key, name varchar(50));
2.使用自动增长列(auto_increment),此列必须是主键或者是主键中的一列;
3.插入时可以指定id列也可以不指定,如果指定的话就插入指定的值,如果不指定则插入比当前最大值大1的值,即max(id)+1;
4.获得上次插入的id值使用last_insert_id()函数:select last_insert_id();
5.设置自动增长id的初始值:alter table table_name auto_increment=n;但是如果这是的n小于max(id)则不生效;
6.为一个已存在的表增加一个自增长列:alter table table_name add column col_name integer auto_increment not null, add primary key(id);
4.不支持事务:
1.查看自动提交事务的选项:show variables like ‘autocommit’;
2.修改此选项:set session autocommit=0|1;
3.因为不支持事务,所以不管此选项为何值,都不能commit和rollback;
5.MyISAM的表可以被转换成一个fast,compressed,read-only的表从而节省空间:
1.使用myisampack工具:mysqlpack –help;
2.进入到数据文件目录:myisampack table_name;
3.观察发现压缩比例为90%,只剩下10%的数据量大小;对指定DATA DIRECTORY和INDEX DIRECTORY选项的表同样使用,亲测;
4.重启之后检测表(check table table_name)就会发生错误,表内数据丢失,对只读的性能支不好,如果使用的话,压缩完之后一定要备份;
6.MyISAM支持FULLTEXT索引和spatial数据类型,全文索引和空间数据类型:
1.对全文索引支持不够好,可以使用instr()函数去实现;
2.也可以使用第三方的插件去实现;
7.MyISAM引擎对锁的支持:
1.MySQL管理使用MyISAM引擎的表之间的查询竞争使用表级的锁,这使得查询的性能非常快,多个查询可以同时访问一张表.对于写操作,会使用一个exclusive的表级锁去阻止其它读写操作.虽然表级锁会影响点性能,但是不会发生死锁;
2.显式的对表加锁:lock table table_name lock_type; lock tables table_name1 lock_type, table_name2 lock_type;
3.解锁:unlock tables;
4.如果在session中加write锁,则本session可以进行读写操作,其它的session对表进行读和写就需要等待锁释放;
5.如果在session中加read锁,则本session和其它session都可以进行读操作,本session无法进行写操作,其它的session对表进行读和写就需要等待锁释放;
6.如果在session中加read local锁,即只锁住加锁前一时刻的表的数据,则本session可以进行读操作(查到的只是加锁前的数据),但是不能进行写操作,其它session可以进行插入操作(查到的是所有的数据),但是删除和更新操作需要等待锁释放;
8.可以通过LOW_PRIORITY和HIGHT_PRIORITY来影响MyISAM表的内部调度机制;可以通过INSERT DELAYED先把表中数据缓存到服务端,等到表不忙的时候再插入;
1.一般情况下在OLTP系统中,更新操作优先于查询操作,因为更新操作时间比较短,查询操作时间比较长(所以一般要提升查询操作的优先级,降低更新操作的优先级);要修改查询的优先级使用:SELECT HIGH_PRIORITY * FROM t;修改更新语句的优先级使用:INSERT INTO t VALUES(1);可以使用read local锁做实验,优先级低的无法插入,优先级高可以插入;
2.数据延迟插入:INSERT DELAYED INTO t VALUES(1);可以使用write锁来模拟这种情况,不实用delayed则等待,使用就马上返回成功,但是真正插入要等表不忙的时候;当有延迟操作时会产生一个延迟进程,它是一个共用的线程,只有一个;
3.查看当前链接到服务器的进程列表:show processlist;杀掉进程:kill id;
4.与延迟插入有关的全局变量:show global variables ‘%delayed%’;
1.delayed_insert_limit:延迟插入时,插入多少条数据后检查是否有查询操作,如果有查询操作,则查询操作先执行;
2.delayed_insert_timeout:限制延迟操作的等待时间;
3.delayed_queue_size:定义延迟线程队列的大小,以行为单位;
5.与延迟插入有关的全局状态:show global status ‘%delayed%’;
1.delayed_errors:记录延迟插入错误的次数;
2.delayed_insert_threads:当前有多少线程在使用延迟操作;
3.delayed_writes:使用延迟线程插入的记录行数;
9.数据表的存储格式是非常的轻便的,因此可以通过直接拷贝表所在的目录到其它的主机以实现对表的备份额迁移:
1.拷贝时需要注意文件的权限和拥有者,一般指定cp -a选项;
2.拷贝表时可能会有缓存,最好能在关闭服务器的情况系进行,以保证数据完整性;
10.可以指定一个MyISAM表最少存储多少条记录,这允许MyISAM表去调整表内部行指针的大小,也可以配置缺省的表内部行指针大小供服务器使用:
1.在创建表时使用MAX_ROWS和MIN_ROWS选项(? CREATE TABLE),这个值只是一个参考值,实际的数据行数可以大于也可以小于这个值;
2.配置系统默认大小使用myisam_data_pointer_size选项:show variables like ‘myisam_data_pointer_size’,这个选项是当创建表时没有使用MAX_ROWS选项时使用,默认为6,不能小于2也不能大于7.它代表可以用几个字节(一个字节是8位)去寻址,指定n,表示2的8n次方,即指定为2,3,4,5,6时依次代表可以存放64K,16M,4G,1T,256T的数据;
3.把max_rows设为5,myisam_data_pointer_size范围是2~7,2个字节就足够了(2^16),所以其实就是让myisam是用2个字节的指针,并不是说最多5行.如果把max_rows设置为大于2^16的值,就要用3个字节表示,所以myisam_data_pointer_size会设为3,此时最多存放2^24行.
4.如果创建表时指定了max_rows,那么表至受限于最大的行数;如果没有max_rows,则表受限于最大大小;
11.导入数据时,可以先禁用掉索引,等到导入数据后再打开索引,这样会加快导入数据的速度.当使用LOAD DATA [LOCAL] INFILE导入数据时,它会自动的禁用和启用索引,以加快导入速度
1.查看LOAD DATA [LOCAL] INFILE的帮助:? load data; load data [local] infile file_name into table table_name;
2.与LOAD DATA INFILE相对应的是SELECT … INTO OUTFILE,把表中的数据导入到文件中;
3.语法:select * from t into outfile ‘/tmp/t.txt’ fields terminated by ‘,’ enclosed by ‘”‘; 字段以[“]包围,字段之间以[,]号分割,默认每行之间以换行分割;
4.语法:load data infile ‘/tmp/t.txt’ into table t fields terminated by ‘,’ enclosed by ‘”‘;
12.向MyISAM表中添加数据时,如果磁盘空间不足时服务器会挂起操作,直到空间变为可用状态,然后继续完成操作;
13.MyISAM表的行存储格式
1.查看表使用哪种行存储格式:与查看表使用引擎的方式一样,表信息的Row_format字段.
1.表中包含有可变长度的列,则表就是Dynamic的;
2.表中没有包含可变长度的列,表就是Fixed的;
2.固定行存储格式:
1.所有的行有固定的大小;
2.行存储的位置是在行长度的整数倍的位置,方便查找;
3.占用更多的存储空间;
3.动态行存储格式:
1.行占用动态的长度;
2.查看起来不是很高效;
3.节省空间;
4.更容易产生碎片;
4.压缩行存储格式:
1.表被压缩以节省空间;
2.优化的存储以加快检索;
3.表是只读的;
5.做实验时可以使用hexdump工具,查看数据文件中实际存放的数据,可以加上-C选项;
6.手动像数据表文件中添加数据后不可用,需要经历check table table_name; optimize table table_name; repair table table_name;三个过程,应该是在information_schema库中写入统计信息;

— MEMORY存储引擎;
1.MEMORY表的.frm文件在数据库目录下,数据和索引都存储在内存中;
2.对MEMORY表的操作性能都很高;
3.在服务器重启之后,MEMORY表中的数据就不存在了,但是他的表结构还是存在的;
4.因为MEMORY表使用的是内存,所以不适用于大表;
5.MEMORY表使用表级锁来处理查询竞争,所以不会发生死锁;
6.MEMORY表不支持TEXT和BLOB类型;
7.它支持两种索引:HASH和BTREE
8.缺省使用HASH索引,这种索引算法使用唯一索引会非常高效,然而HASH索引只能用于比较运算符(=, <>);
1.BTREE索引算法更适合于范围查找,例如>,< 或者between; 2.可以使用创建表时的max_rows和服务器参数max_heap_table_size来限制MEMORY表的大小; 9.设置索引: 1.hash:alter table table_name add index idx_name using hash(col_name); 2.btree:alter table table_name add index idx_name using btree(col_name); 10.当不需要MEMORY表的内容时,要释放被MEMORY表使用的内存,使用DELETE FROM, TRUNCATE TABLE或者删除整个表DROP TABLE; 11.通过指定--max-heap-table-size选项来限制表的最大大小; -- ARCHIVE存储引擎; 1.ARCHIVE引擎被用来以非常小的空间存储大量无索引数据,从而占用更少的资源; 2.要使用此引擎需要在configure时添加--with-archive-storage-engine选项;可以通过show variables like 'have_archive'查看; 3.创建一个ARCHIVE表会有一个保存表结构的.frm文件,保存数据和元数据的.ARZ和.ARM文件,如果有优化操作的话还有一个.ARN文件; 4.ARCHIVE引擎仅仅支持SELECT和INSERT操作,以及除了几何数据类型外的所有数据类型; 5.存储:当inesrt数据时,archive引擎使用zlib无损数据压缩的方式压缩,optimize table可以分析表,并打包为更小的格式; 6.查询:在查询数据时,记录根据需要被加压缩,没有行缓存.SELECT操作执行完全表格扫描,当一个SELECT发生时,它找出当前有多少行可用,并读取行的数量; 7.不支持索引; 8.使用行级别锁定; 9.支持AUTO_INCREMENT列; 10.支持ORDER BY操作和BLOB列; -- BLACKHOLE存储引擎; 1.BLACKHOLE引擎就像黑洞一样,它接收数据但是是丢弃它而不是存储它,查询时总返回NULL; 2.创建BLACKHOLE引擎后会在数据库目录创建一个.frm文件,没有其它文件与之关联; 3.它支持所有的索引; 4.要想使用此引擎在configure时使用--with-blackhole-storage-engine选项; 5.用途: 1.BLACKHOLE表不记录任何数据,如果二进制日志被允许,SQL语句被写入日志,可以用作重复器或者过滤器机制; 2.转储文件语法的验证; 3.来自二进制记录的开销测量,通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制功能的BLACKHOLE的性能; 4.因为BLACKHOLE本质是一个no-op存储引擎,可以用来查找与引擎自身不相关的性能瓶颈; -- 课后练习; 补充: -- BDB存储引擎; 1.需要下载包含BDB版本的MYSQL(MySql-Max分发版支持BDB); 2.安装时在configure加入--with-berkeley-db选项; 3.BDB启动选项 --bdb-home:指定BDB表的基础目录,应该和--datadir相同; --bdb-lock-detect:BDB锁定检测方式,DEFAULT,OLDEST,RANDOM,YOUNGEST; --bdb-logdir=path:BDB日志文件目录; --bdb-no-recover:不在恢复模式启动Berkeley DB; --bdb-no-sync:不同步刷新BDB日志,使用--skip-sync-bdb-logs代替; --bdb-shared-data:以多处理模式启动Berkeley DB(初始化Berkeley DB之时,不要使用DB_PRIVATE); --bdb-tmpdir=path:BDB临时文件目录; --skip-bdb:禁止BDB存储引擎; --sync-bdb-logs:同步刷新BDB日志.这个选项默认被允许,请使用--skip-sync-bdb-logs来禁止它; 4.创建一个BDB表会有两个文件,一个是.frm文件,一个是存放表数据和索引的.db文件; 5.支持事务; 6.每一个BDB表都需要一个primary key,如果创建时不指定则会隐式创建一个; 7.SELECT COUNT(*) FROM tbl_name对BDB表很慢,因为在该表中没有行计数被维持; 8.使用页面级别的锁; 9.使用mysql客户端是,应该使用--no-auto-rehash选项; 10.BDB表的限制: 1.每个BDB表在.db文件里存储文件被创建之时到该文件的路径,这个被做来允许在支持symlinks的多用户环境里检测锁定.因此,从一个数据库目录往另一个目录移动BDB表是不能的; 2.当制作BDB表的备份之时,你必须使用mysqldump要么做一个包含对每个BDB表的文件(.frm和.db文件)及BDB日志文件的备份.BDB存储引擎在它的日志文件存储未完成的事务以及要求它们在mysqld启动的时候被呈出来.BDB日志在数据目录里,具有log.XXXXXXXXXX(10位数字)形式名字的文件; 3.如果允许NULL值的列有唯一的索引,只有单个NULL值是被允许的,这不同于其它存储引擎; -- Federated存储引擎; 1.它访问的是在远程数据库表中的数据,而不是本地的表,仅在-MAX版的MySql可用; 2.如果要使用需要在在configure时添加--with-federated-storage-engine选项; 3.创建一个FEDERATED表时,服务器在数据库目录创建一个.frm文件,没有表数据文件,因为实际数据在远程数据库上; 4.操作表的内容时需要MYSQL客户端API,读取数据通过SELECT * FROM table_name来初始化,然后通过mysql_fetch_row()的c函数去一行行读取; 5.创建一个FEDERATED表 1.假设在远程服务器上有一个表为tbl; 2.在本地创建表:create table federated_tbl(id int, name varchar(10)) engine=federated 3.connection='mysql://root@remote_host:3306/federated/tbl'; 4.其他CONNECTION的格式: 1.CONNECTION='mysql://username:password@hostname:port/database/tablename'; 2.CONNECTION='mysql://username@hostname/database/tablename'; 3.CONNECTION='mysql://username:password@hostname/database/tablename'; 6.局限性: 1.远程服务器必须是一个MYSQL服务器; 2.不支持事务; 3.支持增删改查的操作和索引,但是不支持ALTER TABLE和DROP TABLE; 7.用途:可以跨服务器访问,不用创建DB LINK了; -- MRG_MYISAM存储引擎; 1.MGR_MYISAM表是一组MyISAM表的集合,每一个MERGE表在磁盘上都有2个文件,一个是.frm文件和一个包含组成MERGGE表的MyISAM表的名称的.MRG文件.这两个文件都存放在数据库目录下; 2.当对一个MERGE表操作时,相当于对组成MERGE表的所有的MyISAM表的操作; 3.一个MERGE表可以突破MyISAM表的最大大小的限制; 4.MYSQL管理MERGE表的查询竞争使用表级锁,即锁住组成它的MyISAM表,所以不会产生死锁; 5.一个MERGE表是很轻便的,因为.MRG文件是一个文本文件; 6.可以进行增删改查操作,在插入操作时可以指定是往哪个表中插入数据; 7.当MERGE引擎要锁住一个MERGE表时,就会对组成它的所有MyISAM表加锁; 8.对MERGE表执行SEELCT操作时,对底层的表加read lock; 9.对MERGE表执行更新操作(delete, update)时,对底层的表加write lock; 10.对MERGE表的操作: 1.创建MyISAM表m1:create table m1(id int, name varchar(10)) engine=myisam; 2.创建MyISAM表m2:create table m2(id int, name varchar(10)) engine=myisam; 3.创建MERGE表:create table m(id int, name varchar(10)) engine=merge union=(m1, m2); 4.插入数据:insert into m1 values(1, 'a');insert into m1 values(1, 'a'); 5.查询:select * from m; 6.创建MyISAM表m3:create table m3(id int, name varchar(10)) engine=myisam; 7.加入merge表:alter table m union=(m1, m2, m3);很灵活,可以互相组合; 8.修改表使得merge表可以插入数据,create table m(id int, name varchar(10)) engine=mgr_myisam union=(m1, m2) insert_method=last;method_method=0:不允许插入;first:插入到union中的第一个表;last:插入到union中最后一个表; 11.可以通过直接修改.MRG文件来修改MERGE表,修改后使用flush tables;来刷新表缓存; -- CSV存储引擎; 1.CSV引擎使用逗号分隔值格式的文本文件存储数据(eg:["1","aaa"]); 2.要想使用此引擎在configure时使用--with-csv-storage-engine选项; 3.CSV引擎不支持null值,所以在创建时应加上not null选项; 4.CSV引擎不支持索引; 5.创建CSV表会在数据库目录创建一个.frm文件,一个.CSV的文本文件用来存储数据和一个.CSM文件; -- EXAMPLE存储引擎; 1.EXAMPLE引擎是一个不做适合事情的存储引擎,主要用于MySql源码中一个例子用来演示如何开始编写一个新的存储引擎; 2.需要在configure时添加--with-example-storage-engine选项; 3.EXAMPLE引擎不支持编译索引;

标签: ,
目前还没有任何评论.