MySQL技术手册要点解析-3

21.mysql默认情况下是autocommit的，如果要修改可以设置SET autocommit = 0或者以START TRANSACTION;起头后面就需要commit了

13.2.2.2. Grouping DML Operations with Transactions
By default, connection to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. This mode of operation might be unfamiliar if you have experience with other database systems, where it is standard practice to issue a sequence of DML statements and commit them or roll them back all together. To use multiple-statement transactions, switch autocommit off with the SQL statement SET autocommit = 0 and end each transaction with COMMIT or ROLLBACK as appropriate. To leave autocommit on, begin each transaction with START TRANSACTION and end it with COMMIT or ROLLBACK. The following example shows two transactions. The first is committed; the second is rolled back.

22.mysql传统创建和删除索引都需要自动重新建表？然后将数据insert进去，然后再删除原表rename新表？

是的，因为所有的innodb表都相当于oracle里面的iot表。

现在针对iot表创建了clusterd index，所以创建secondary index更快? 确实，因为只需要保存key->主键key

那么没有主键的表建索引是什么情况呢？----自动创建隐含的主键

是不是可以认为innodb engine所有的表都是类似于iot表？的确如此

13.2.2.6.1. Overview of Fast Index Creation With MySQL 5.5 and higher, or MySQL 5.1 with the InnoDB Plugin, creating and dropping secondary indexes for InnoDB tables is much faster than before. Historically, adding or dropping an index on a table with existing data could be very slow. The CREATE INDEX and DROP INDEX statements worked by creating a new, empty table defined with the requested set of indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows are inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.
The performance speedup for fast index creation applies to secondary indexes, not to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an “index-organized table”. Because the table structure is so closely tied to the primary key, redefining the primary key still requires copying the data.

•在InnoDB中，即使用户不指定主键，InnoDB也会生成一个隐含主键，这种情况下，InnoDB的性能比采用序列主键性能下降30%左右。

•主键索引即聚集索引（Cluster Index），它不仅有主键，而且有主键所属的全部数据，所以在InnoDB中，主键索引即数据。

•InnoDB实现两种B+Tree索引，一种是列值为Key，主键位置为Value即 (列值, 主键位置) 的非主键索引（Secondary Index），另一种是主键索引，两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点。23.mysqlbackup命令可以针对innodb表进行热备，不妨碍读写操作；对于myISAM表，则只能读不能写。

23.mysql热备份,mysqlbackup来备份InnoDB表，则读写都可以不妨碍，如果备份MyISAM表，则可以读，不能写入。

MySQL Enterprise Backup可以对表进行部分备份，结合binary log可以实现 PITR(point in time Recovery).

Hot Backups
The mysqlbackup command, part of the MySQL Enterprise Backup component, lets you back up a running MySQL instance,including InnoDB and MyISAM tables, with minimal disruption to operations while producing a consistent snapshot of the database.
When mysqlbackup is copying InnoDB tables, reads and writes to both InnoDB and MyISAM tables can continue. During the copying of MyISAM tables, reads (but not writes) to those tables are permitted. MySQL Enterprise Backup can also create compressed backup files, and back up subsets of tables and databases. In conjunction with MySQL’s binary log, users can perform point-in-time recovery. MySQL Enterprise Backup is part of the MySQL Enterprise subscription. For more details, see Chapter 23, MySQL Enterprise Backup.

24.CSV的storage engine

   当创建一个表

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)  engine=csv;

insert into test values(10,'abc');

然后找到该表对应的文件:

/var/lib/mysql/test/test.CSV与之对应的还有一个二进制文件test.CSM我估计是用于保存元数据的。

而后将这个文件copy到windows下，用execel进行修改，插入新数据，之后重启mysqld

然后再次查询会发现数据增加了。

25.ACID --atomicity原子性, consistency一致性, isolation隔离性, and durability持久性.

26.从mysql 5.5.5版本之后，mysql才将InnoDB设置为默认的storage engine，在此之前都是MyISAM

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB.

2010年之前，mysql在业界的使用普遍没有达到其并发性和可用性边界，之后则对故障恢复、高可用的要求越来越普遍因此修改为innodb为默认存储引擎。

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)
 

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
 

Trends in Storage Engine Usage
In the first years of MySQL growth, early web-based applications didn't push the limits of concurrency and availability. In 2010,hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important. InnoDB hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine.

27.InnoDB table的优势

1.支持类似oracle的实例恢复

2.支持类似oracle的 buffer pool caches t

3.支持外键

4.支持corrupted数据校验

5.基于主键的访问效率高

6.change buffering提高insert/update/delete效率，这一点还不清楚具体怎么样

7.When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

 

Benefits of InnoDB Tables
If you use MyISAM tables but aren't tied to them for technical reasons, you'll find many things more convenient when you use InnoDB tables in MySQL 5.5:
• If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time,you don't need to do anything special after restarting the database. InnoDB crash recovery automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off. This process is now much faster than in MySQL 5.1 and earlier.
• The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information, and speeds up processing so much, that dedicated database servers assign up to 80% of their physical memory to the InnoDB buffer pool.
• If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.
Storage Engines
• If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
• When you design your database with appropriate primary key columns for each table, operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.
• Inserts, updates, deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.
• Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

28.MySQL Storage Engine Architecture

29.memory storage engine主要用于临时表，因为不支持掉电恢复，而且innodb支持buffer cache，所以其性能优势也不明显了，现在用的越来越少了。

13.4. The MEMORY Storage Engine
The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or readonly caches for data pulled from other tables.

30. Storage Engines的功能对比

Multi-Version Concurrency Control (MVCC)

31.第三方的sotrage engine,mysql不提供官方技术支持

• PrimeBase XT (PBXT):支持基于web的高并发环境 PBXT has been designed for modern, web-based, high concurrency environments.
• RitmarkFS: 以SQL读取和操作文件系统的存储引擎,支持文件系统复制和目录变化跟踪.RitmarkFS enables you to access and manipulate the file system using SQL queries. RitmarkFS also supports file system replication and directory change tracking.
• Distributed Data Engine:分布式存储引擎 The Distributed Data Engine is an Open Source project that is dedicated to provide a Storage Engine for distributed data according to workload statistics.
• mdbtools: 支持读取微软的Access.mdb文件A pluggable storage engine that enables read-only access to Microsoft Access .mdb database files.
• solidDB for MySQL: 一个事务支持的存储引擎solidDB Storage Engine for MySQL is an open source, transactional storage engine for MySQL Server. It is designed for mission-critical implementations that require a robust, transactional database. solidDB Storage Engine for MySQL is a multi-threaded storage engine that supports full ACID compliance with all expected transaction isolation levels, row-level locking, and Multi-Version Concurrency Control (MVCC) with nonblocking reads and writes.
• BLOB Streaming Engine (MyBS): 主要支持多媒体存储The Scalable BLOB Streaming infrastructure for MySQL will transform MySQL into a
scalable media server capable of streaming pictures, films, MP3 files and other binary and text objects (BLOBs) directly in and out of the database.

32.InnoDB引擎的最佳实践.

Best Practices for InnoDB Tables
If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them throughout this chapter. To make a long story short:
• Specify a primary key for every table using the most frequently queried column or columns, or anauto-increment value if theres no obvious primary key.

表要有主键，如果没有最好设置自增字段作为主键
• Embrace 拥抱；信奉 the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.

使用和定义外键
• Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).

关闭自动提交
• Group sets of related DML operations into transactions, by bracketing them with START TRANSACTION and COMMIT statements. While you don't want to commit too often, you also don't want to issue huge batches of INSERT, UPDATE, or DELETE statements that run for hours without committing.

将dml操作组合到事务中执行，使用start transaction 和commit控制事务。有个疑问，如果关闭了自动提交还需要start transaction吗?

• Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use the SELECT ... FOR UPDATE syntax to lock just the rows you intend to update.

不要使用lock table,如果需要可以使用select ...for update,这一点和oracle类似了。

• Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)

innodb_file_per_table 实质一个inodb_file放多少个表
• Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature
(ROW_FORMAT=COMPRESSED on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/ write capability.

使用压缩
• Run your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the one specified in the ENGINE= clause of CREATE TABLE.

启动server的时候加上--sql_mode=NO_ENGINE_SUBSTITUTION，这样在sql中创建表就不能指定其他的存储引擎了。

这种情况下，如果执行ALTER TABLE table_name ENGINE=MyIsAM;还有效吗?

33.不要将Mysql的系统表转化为InnoDB  engine.不要使用NFS来存储InoDB数据文件和日志文件。

13.2.8. Limits on InnoDB Tables
Warning
Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables! This is an unsupported
operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or regenerate
them with the mysql_install_db script.
Warning
It is not a good idea to configure InnoDB to use data files or log files on NFS volumes. Otherwise, the files might be
locked by other processes and become unavailable for use by MySQL.

34. FEDERATED Storage Engine实现类似oracle dblink的功能，但是这个engine默认没有开启，需要编译时候加上-DWITH_FEDERATED_STORAGE_ENGINE 参数并且启动时候加上 --federated 参数.

13.9. The FEDERATED Storage Engine
The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster techno-Storage Engines logy. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.
To include the FEDERATED storage engine if you build MySQL from source, invoke CMake with the -
DWITH_FEDERATED_STORAGE_ENGINE option.
The FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.

35.example storage engine主要是提供一个如何开发新的storage engine的例子。

13.10. The EXAMPLE Storage Engine
The EXAMPLE storage engine is a stub engine that does nothing. Its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.