MySQL为什么很受大家青睐 mysql为什么使用innodb

InnoDB是一个通用存储引擎，平衡了高可靠性和高性能。 在MySQL 5.7中，InnoDB是默认的MySQL存储引擎。 除非已配置其他默认存储引擎，否则发出不带ENGINE =子句的CREATE TABLE语句会创建一个InnoDB表。

InnoDB的主要优点：

:其DML（Data manipulation language 数据操作语言）操作遵循ACID（atomicity 原子性，consistency 一致性，isolation 隔离性和durability耐久性）模型，具有提交，回滚和崩溃恢复功能的事务来保护用户数据。

InnoDB存储引擎特征

1 如果服务器由于硬件或软件问题而崩溃，无论当时在数据库中发生了什么，您都不需要在重新启动数据库后做任何特殊操作。 InnoDB崩溃恢复会自动完成在崩溃时间之前提交的任何更改，并撤销正在处理但未提交的任何更改。 只需重新启动并继续你离开的地方。

2 nnoDB存储引擎维护自己的缓冲池，当数据被访问时，它缓存主存储器中的表和索引数据。 经常使用的数据直接从内存处理。 此缓存适用于许多类型的信息并加速处理。 在专用数据库服务器上，高达80％的物理内存通常分配给InnoDB缓冲池。 3 如果将相关数据拆分为不同的表，则可以设置强制引用完整性的外键。 更新或删除数据，并自动更新或删除其他表中的相关数据。 尝试将数据插入辅助表，而在主表中没有相应的数据，并且不良数据会自动跳出。 4 如果数据在磁盘或内存中损坏，则在使用伪造数据之前，校验机制会提醒。 5 使用适当的主键列为每个表设计数据库时，将自动优化涉及这些列的操作。 在WHERE子句，ORDER BY子句，GROUP BY子句和连接操作中引用主键列非常快。 6 插入，更新和删除通过称为更改缓冲的自动机制进行优化。 InnoDB不仅允许并发读取和写入访问同一个表，它缓存更改的数据以简化磁盘I / O。 7 性能优势不限于具有长时间运行查询的巨型表。 当从表中重复访问相同的行时，称为自适应散列索引的功能将使这些查找更快，如同它们从散列表中出来一样。 8 可以压缩表和关联的索引。 9 可以创建和删除索引，对性能和可用性的影响要小得多。 10 截断每个表的文件表空间非常快，并且可以释放磁盘空间供操作系统重用

表数据的存储布局对于BLOB和长文本字段（使用DYNAMIC行格式）更有效。

11 可以通过查询INFORMATION_SCHEMA表来监视存储引擎的内部工作原理。 12 可以通过查询性能模式表来监视存储引擎的性能详细信息。 13 可以自由地混合InnoDB表与其他MySQL存储引擎的表，即使在同一语句中。 例如，可以使用连接操作在单个查询中组合来自InnoDB和MEMORY表的数据。 14 InnoDB设计用于处理大数据量时的CPU效率和最大性能。 15 InnoDB表可以处理大量数据，即使在操作系统上文件大小限制为2GB。

1 使用最常查询的列为每个表指定主键，如果没有明显的主键，则使用自动递增的值。

2 在从多个表中提取数据的地方，根据这些表中相同的ID值使用连接。 对于快速连接性能，请在连接列上定义外键，并在每个表中声明具有相同数据类型的那些列。 添加外键可确保将引用的列编制索引，这可以提高性能。 外键还会将删除或更新传播到所有受影响的表，并且如果父表中不存在相应的ID，则防止在子表中插入数据。 3 关闭自动提交。 每秒提交数百次会对性能（由存储设备的写入速度限制）限制上限。 4 不使用LOCK TABLES语句。 InnoDB可以处理多个会话，同时读取和写入同一个表，而不会牺牲可靠性或高性能。 5 通过使用START TRANSACTION和COMMIT语句包围它们，将相关DML操作的集合分组为事务 6 启用innodb_file_per_table选项可将单个表的数据和索引置于单独的文件中，而不是放在单个巨大系统表空间中。 需要使用此设置才能使用某些其他功能，例如表压缩和快速截断。 7 用选项–sql_mode = NO_ENGINE_SUBSTITUTION运行服务器以防止使用不同的存储引擎创建表，如果在CREATE TABLE的ENGINE =子句中指定的引擎存在问题。

1 在某些情况下，如果数据并非全部放在同一物理磁盘上，数据库性能会提高。 将日志文件放在不同磁盘上的数据通常对性能有益。 例如，可以将系统表空间数据文件和日志文件放在不同的磁盘上。 您还可以将原始磁盘分区（裸设备）用于InnoDB数据文件，这可能加快I / O。

2 InnoDB是一个适用于MySQL的事务安全（ACID兼容）存储引擎，具有提交，回滚和崩溃恢复功能以保护用户数据。但是，如果底层操作系统或硬件不能正常工作，它不能这样做。许多操作系统或磁盘子系统可以延迟或重新排序写入操作以提高性能。在某些操作系统上，应该等待一个文件的所有未写入数据被刷新的fsync（）系统调用可能实际上在数据被刷新到稳定存储之前返回。因此，操作系统崩溃或断电可能会破坏最近提交的数据，或者在最坏的情况下，甚至由于写操作已重新排序，甚至损坏数据库。如果数据完整性对您很重要，请在生产中使用任何内容之前执行一些“拉插拔”测试。在OS X 10.3及更高版本上，InnoDB使用特殊的fcntl（）文件flush方法。在Linux下，建议禁用回写缓存。 3 在ATA / SATA磁盘驱动器上，诸如hdparm -W0 / dev / hda之类的命令可能会禁用写回缓存。请注意，某些驱动器或磁盘控制器可能无法禁用回写高速缓存 4 关于保护用户数据的InnoDB恢复功能，InnoDB使用一种文件刷新技术，该技术涉及一种称为双重写缓冲区的结构，默认情况下启用（innodb_doublewrite = ON）。 双重缓冲区增加了在崩溃或断电后恢复的安全性，并通过减少对fsync（）操作的需求来提高大多数Unix版本的性能。 如果您关心数据完整性或可能的故障，建议innodb_doublewrite选项保持启用。 5 如果可靠性是您的数据的考虑因素，请不要配置InnoDB在NFS卷上使用数据文件或日志文件。 潜在问题根据NFS的操作系统和版本而异，并且包括诸如缺少对冲突写入的保护以及对最大文件大小的限制等问题。

系统表空间数据文件配置

[mysqld]
innodb_data_home_dir = /path/to/myibdata/
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

注意：为innodb_data_home_dir指定值时，需要尾部斜杠。
InnoDB不创建目录，因此在启动服务器之前，请确保myibdata目录存在。 使用Unix或DOS mkdir命令创建任何必要的目录。
确保MySQL服务器具有在数据目录中创建文件的正确访问权限。 更一般地，服务器必须在需要创建数据文件的任何目录中具有访问权限。

InnoDB日志文件配置

[mysqld]
innodb_log_group_home_dir = /dr3/iblogs
innodb_log_files_in_group = 2
innodb_log_file_size = 48M

定义日志组中的日志文件数。 默认值和推荐值为2
innodb_log_file_size 定义日志组中每个日志文件的大小（以字节为单位）。 日志文件的总大小（innodb_log_file_size * innodb_log_files_in_group）不能超过稍小于512GB的最大值。 例如，一对255 GB的日志文件接近限制，但不超过它。 默认日志文件大小为48MB。 通常，日志文件的组合大小应足够大，以便服务器可以平滑工作负载活动中的峰值和谷值，这通常意味着有足够的重做日志空间来处理超过一个小时的写入活动。 值越大，缓冲池中需要的检查点刷新活动越少，从而节省磁盘I / O。

InnoDB Redo 表空间配置

默认情况下，InnoDB撤销日志是系统表空间的一部分。 但是，您可以选择将InnoDB撤销日志存储在一个或多个单独的撤销表空间中，通常位于不同的存储设备上。

[mysqld]
innodb_undo_directory = /path/to/undodir/
innodb_undo_logs = #int 起始值，该选项定义InnoDB使用的回滚段数。
innodb_undo_tablespaces = #int 非零值。

由innodb_undo_logs值指定的回滚段在这个数量的单独表空间之间进行划分。 innodb_undo_tablespaces值在MySQL实例的生命周期是固定的，所以如果你不确定最优值，估计在高端。

InnoDB临时表空间配置
默认情况下，InnoDB会创建一个名为ibtmp1的单一自动扩展临时表空间数据文件，该文件在innodb_data_home_dir目录中稍大于12MB。

innodb_temp_data_file_path = /path/to/tmpdir/ibtmp1:15M:autoextend

innodb_temp_data_file_path选项指定InnoDB临时表空间数据文件的路径，文件名和文件大小。

InnoDB Page Size 配置

innodb_page_size = 16K

innodb_page_size选项指定MySQL实例中所有InnoDB表空间的页大小。 此值在创建实例时设置，之后保持不变。 有效值为64k，32k，16k（默认值），8k和4k。 或者，您可以指定页大小（以字节为单位）（65536，32768，16384，8192，4096）。
默认页大小为16k适用于各种工作负载，特别是涉及表扫描和涉及批量更新的DML操作的查询。 对于涉及许多小型写入的OLTP工作负载，较小的页面大小可能更有效，其中当单个页面包含许多行时，争用可能是一个问题。 较小的页面对于SSD存储设备也可能是高效的，其通常使用小块大小。 保持InnoDB页面大小接近存储设备块大小，可以最大限度地减少重写到磁盘的未更改数据量。

InnoDB内存配置
MySQL为各种缓存和缓冲区分配内存，以提高数据库操作的性能。 当为InnoDB分配内存时，总是考虑操作系统所需的内存，分配给其他应用程序的内存，以及为其他MySQL缓冲区和缓存分配的内存。 例如，如果您使用MyISAM表，请考虑为键缓冲区分配的内存量（key_buffer_size）。

innodb_buffer_pool_size = 128M

定义缓冲池的大小，缓冲池是保存InnoDB表，索引和其他辅助缓冲区的缓存数据的内存区域。 缓冲池的大小对于系统性能很重要，通常建议将innodb_buffer_pool_size配置为系统内存的50％到75％。 默认缓冲池大小为128MB。
缓冲池大小必须始终等于或为innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。 如果将innodb_buffer_pool_size配置为不等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，则缓冲池大小将自动调整为等于或不小于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数（不小于指定的缓冲池大小）的值 。

innodb_buffer_pool_chunk_size = byte

定义InnoDB缓冲池调整大小操作的块大小。可以以1MB（1048576字节）单位增加或减少，但只能在启动时在命令行字符串或MySQL配置文件中修改。

innodb_buffer_pool_instances = 2 //(1-64)

在具有大量内存的系统上，可以通过将缓冲池分为多个缓冲池实例来提高并发性。 缓冲池实例的数量由innodb_buffer_pool_instances选项控制。 默认情况下，InnoDB创建一个缓冲池实例。 可以在启动时配置缓冲池实例的数量 .此选项仅在将innodb_buffer_pool_size设置为1GB或更大的时候生效。

innodb_old_blocks_pct = 37

控制LRU列表中的插入点，并选择InnoDB是否对通过表或索引扫描引入缓冲池的块应用相同的优化。 配置参数innodb_old_blocks_pct控制LRU列表中“旧”块的百分比。 innodb_old_blocks_pct的默认值为37，对应于原始固定比率3/8。 值范围是5（缓冲池中的新页面非常快地过期）到95（只有5％的缓冲池被保留用于热页面，使算法接近熟悉的LRU策略）。

innodb_old_blocks_time = 1000

指定插入旧子列表的页面在以毫秒（ms）为单位的第一次访问之后必须保留在那里，然后才能移动到新的子列表。 如果值为0，则插入旧子列表的页面将在第一次访问时立即移动到新子列表，无论插入后多长时间都会发生访问。 如果值大于0，则页面保留在旧子列表中，直到在第一次访问后的至少几毫秒内发生访问。 例如，值为1000会导致页面在首次访问之后保留在旧子列表中1秒钟，然后它们才能移动到新的子列表。

innodb_read_ahead_threshold = 56

控制InnoDB用于将页面预取到缓冲池中的线性预读的灵敏度。可以设置为0-64中的任何值。 默认值为56.

innodb_random_read_ahead

随机预读是一种基于已经在缓冲池中的页面来预测何时可能需要页面的技术，而不管这些页面被读取的顺序如何。 如果在缓冲池中找到来自相同范围的13个连续页面，则InnoDB异步地发出请求以预取该extent的剩余页面。 要启用此功能，请将配置变量innodb_random_read_ahead设置为ON。

innodb_max_dirty_pages_pct = 75 //介于0到99之间的整数。默认值为75

当缓冲池中的脏页的百分比达到innodb_max_dirty_pages_pct_lwm定义的低水位设置时，InnoDB开始刷新缓冲池页面。 此选项旨在控制缓冲池中脏页的比率，并且理想地防止脏页的百分比到达innodb_max_dirty_pages_pct。 如果缓冲池中脏页的百分比超过innodb_max_dirty_pages_pct，InnoDB开始积极刷新缓冲池页面。
InnoDB使用算法基于重做日志生成的速度和当前刷新速率来估计所需的刷新速率。 目的是通过确保缓冲区刷新活动满足保持缓冲池“干净”的需要来平滑整体性能。 当过多的缓冲池刷新限制了可用于普通读取和写入活动的I / O容量时，自动调整刷新速率有助于避免吞吐量突然下降。
InnoDB以循环方式使用其日志文件。 在重用日志文件的一部分之前，InnoDB会将所有脏缓冲池页面刷新到磁盘，其重做条目包含在日志文件的该部分中，这个过程称为尖锐检查点。 如果工作负载是写密集型的，它会生成大量的重做信息，全部写入日志文件。 如果日志文件中的所有可用空间都已用尽，则会出现尖锐的检查点，导致吞吐量暂时降低。 即使未达到innodb_max_dirty_pages_pct，也可能发生这种情况。

innodb_adaptive_flushing = on //or off

指定是否根据工作负载动态调整缓冲池中清除脏页的速率。 动态调整刷新率旨在避免I / O活动的突发。 默认情况下启用此设置

innodb_lru_scan_depth = 1024 //100 ~ 2^64-1 32位系统最大为2^32-1

影响缓冲池刷新操作的算法和启发式的参数。 主要关注性能专家调整I / O密集型工作负载。 它指定，每个缓冲池实例，多少向下缓冲池LRU列表page_cleaner线程扫描寻找脏页刷新。这是每秒执行一次的后台操作。

innodb_flush_neighbors = 0|1|2

指定从缓冲池刷新页面是否也刷新同一范围内的其他脏页。
0 使innodb_flush_neighbors关闭，并且没有其他脏页从缓冲池中刷新。
1 刷新缓冲池中相同范围内的连续脏页。(默认)
2 刷新缓冲池中相同范围内的脏页。
配置选项innodb_flush_neighbors和innodb_lru_scan_depth允许您微调InnoDB缓冲池的刷新过程的某些方面。 这些选项主要帮助写入密集型工作负载。 对于重型DML活动，如果不够积极，刷新可能落后，导致缓冲池中使用过多的内存; 或者，如果该机制过于激进，则由于刷新而导致的磁盘写入可能会使I / O容量饱和。 理想设置取决于您的工作负载，数据访问模式和存储配置（例如，数据是否存储在HDD或SSD设备上）。

对于具有持续繁重工作负载或工作负载波动较大的系统，有几个配置选项可调整InnoDB表的刷新行为：

innodb_adaptive_flushing_lwm = 10 //0~70

InnoDB的自适应冲洗机制并不适合所有的情况。 当重做日志有填满的危险时，它给最大的好处。 innodb_adaptive_flushing_lwm选项指定重做日志容量的“低水位标记”百分比; 当超过该阈值时，即使innodb_adaptive_flushing选项未指定，InnoDB也会打开自适应刷新。

innodb_max_dirty_pages_pct_lwm = 0 //0~99.99

低水位标记代表脏页的百分比，其中启用预冲来控制脏页率。 默认值为0将完全禁用预刷新行为。

innodb_io_capacity_max = 300

如果Flushing活动远远落后，InnoDB可以比innodb_io_capacity指定的更强大。 innodb_io_capacity_max表示在这种紧急情况下使用的I / O容量的上限，使得I / O中的尖峰不消耗服务器的所有容量。

innodb_io_capacity_max设置是所有缓冲池实例的总限制。

如果在启动时指定innodb_io_capacity设置但未指定innodb_io_capacity_max的值，则innodb_io_capacity_max的默认值为innodb_io_capacity的值的两倍，最小值为2000。

当配置innodb_io_capacity_max时，innodb_io_capacity的两倍通常是一个很好的起点。 默认值2000适用于使用固态磁盘（SSD）或多个常规磁盘驱动器的工作负载。 对于不使用SSD或多个磁盘驱动器的工作负载，2000的设置可能过高，并且可能会导致过多的刷新。 对于单个常规磁盘驱动器，建议设置在200到400之间。 对于高端，总线连接的SSD，请考虑更高的设置，如2500.与innodb_io_capacity设置一样，保持设置尽可能低，但不要低到InnoDB不能充分扩展超出innodb_io_capacity限制，

innodb_flushing_avg_loops = 30 //1~1000

InnoDB保留先前计算的刷新状态快照的迭代次数，控制自适应刷新对不断变化的工作负载的响应速度。大型innodb_log_file_size和小峰值（不会达到75％重做日志空间利用率）的系统应使用高innodb_flushing_avg_loops值以尽可能平滑地保持刷新。 对于具有极端负载峰值或日志文件但不提供大量空间的系统，请考虑使用较小的innodb_flushing_avg_loops值。 较小的值允许冲刷以紧密跟踪负载，并有助于避免达到75％的重做日志空间利用率。
这些选项计入innodb_adaptive_flushing选项使用的公式。

innodb_buffer_pool_filename = 'ib_buffer_pool' //filename

指定保存由innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now生成的表空间ID和页面ID的列表的文件的名称。

innodb_buffer_pool_dump_at_shutdown=on

指定在MySQL服务器关闭时是否记录缓存池中缓存的页面，以便在下次重新启动时缩短预热过程。

innodb_buffer_pool_load_at_startup=on

指定在MySQL服务器启动时，缓冲池会通过加载早期保存的相同页面来自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用。

innodb_buffer_pool_dump_pct = 25 //1~100

指定每个缓冲池读取和转储的最近使用的页面的百分比。 范围为1到100。
例如，如果有4个缓冲池，每个缓冲池有100个页面，并且innodb_buffer_pool_dump_pct设置为25，那么将转储来自每个缓冲池的25个最近使用的页面。

innodb_log_buffer_size = 16M

定义InnoDB用于写入磁盘上日志文件的缓冲区大小（以字节为单位）。 默认大小为16MB。 大型日志缓冲区使大型事务可以运行，而不需要在事务提交之前将日志写入磁盘。 如果具有更新，插入或删除多行的事务，则可以考虑增加日志缓冲区的大小以保存磁盘I / O。

使用性能模式监控缓冲池负载进度

启用stage / innodb / buffer pool加载工具：

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
       WHERE NAME LIKE 'stage/innodb/buffer%';

启用阶段事件使用者表，其中包括events_stages_current，events_stages_history和events_stages_history_long

mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
       WHERE NAME LIKE '%stages%';

通过启用innodb_buffer_pool_dump_now来转储当前缓冲池状态

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

检查缓冲池转储状态，以确保操作已完成

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'\G

通过启用innodb_buffer_pool_load_now加载缓冲池：

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

通过查询性能模式events_stages_current表来检查缓冲池装入操作的当前状态。 WORK_COMPLETED列显示加载的缓冲池页面数。 WORK_ESTIMATED列提供了剩余工作的估计值（以页为单位）

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;

如果缓冲池装入操作已完成，events_stages_current表将返回空集。 在这种情况下，可以检查events_stages_history表以查看已完成事件的数据。 例如：

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 
       FROM performance_schema.events_stages_history;

中止缓冲池加载操作

mysql> SET GLOBAL innodb_buffer_pool_load_abort=ON;

InnoDB表的优化

一旦您的数据达到稳定的大小，或增长的表增加了几十或几百兆字节，请考虑使用OPTIMIZE TABLE语句重新组织表并压缩任何浪费的空间。 重组的表需要较少的磁盘I / O才能执行全表扫描。 这是一个简单的技术，可以提高性能，而其他技术，如提高索引的使用或调整应用程序代码是不实际的。 在InnoDB中，具有长PRIMARY KEY（具有长值的单个列或形成长复合值的多个列）会浪费大量磁盘空间。 行的主键值在指向同一行的所有辅助索引记录中重复。 使用VARCHAR数据类型而不是CHAR来存储可变长度字符串或具有许多NULL值的列。 CHAR（N）列始终使用N个字符来存储数据，即使字符串较短或其值为NULL。 较小的表在缓冲池中更适合，并减少磁盘I / O。 对于较大或包含大量重复文本或数字数据的表，请考虑使用COMPRESSED行格式。 减少磁盘I / O将数据带入缓冲池或执行全表扫描是必需的。 在做出永久决定之前，请测量使用COMPRESSED和COMPACT行格式可以实现的压缩量。 优化InnoDB事务管理 要优化InnoDB事务处理，请在事务功能的性能开销和服务器的工作负载之间找到理想的平衡。 例如，如果应用程序每秒提交数千次，而应用程序可能会遇到性能问题，以及如果每2-3小时提交就会出现不同的性能问题。 默认的MySQL设置AUTOCOMMIT = 1可以对繁忙的数据库服务器施加性能限制。 在可行的情况下，通过在进行所有更改后发出SET AUTOCOMMIT = 0或START TRANSACTION语句，后跟一个COMMIT语句，将多个相关的数据更改操作包含到单个事务中。 如果该事务对数据库进行了修改，InnoDB必须在每次事务提交时将日志刷新到磁盘。 当每次更改后都进行提交（与默认自动提交设置一样）时，存储设备的I / O吞吐量将限制每秒可能的操作数。 另外，对于仅包含一个SELECT语句的交易，打开AUTOCOMMIT有助于InnoDB的认只读事务并对其进行优化。 避免在插入，更新或删除大量行后执行回滚。 如果一个大事务正在减缓服务器性能，将其回滚可能会使问题更严重，可能需要几倍的时间来执行原始数据更改操作。 杀死数据库进程没有帮助，因为在服务器启动时再次启动回滚。 为了尽可能减少发生此问题的几率： 增加缓冲池的大小，使所有的数据的变化而改变可被高速缓存，而不是立即写入磁盘。 设置innodb_change_buffering = all，以便除了插入之外还缓冲更新和删除操作。 考虑在大数据更改操作期间定期发出COMMIT语句，可能会将单个删除或更新分解为对较小数量行操作的多个语句。 为了摆脱一旦出现失控的回滚，增加缓冲池使得回滚变成CPU绑定且跑得快，或者杀了服务器且innodb_force_recovery = 3重启 修改或删除行时，行和关联的撤销日志不会立即物理删除，甚至不会立即在事务提交后删除。 保留旧数据，直到较早或并发启动的事务完成，以便那些事务可以访问修改或删除行的先前状态。 因此，长时间运行的事务可能会阻止InnoDB清除由不同事务更改的数据。 当在长时间运行的事务中修改或删除相同的行时，使用READ COMMITTED和REPEATABLE READ隔离级别的其他事务必须做更多的工作来重建较旧的数据 优化InnoDB Redo 日志 使您的重做日志文件大，甚至与缓冲池一样大。 当InnoDB将重做日志文件写入完整时，它必须在检查点中将缓冲池的修改内容写入磁盘。 小型重做日志文件会导致许多不必要的磁盘写入。 请考虑增加日志缓冲区的大小。 大型日志缓冲区使大型事务可以运行，而不需要在事务提交之前将日志写入磁盘。 因此，如果具有更新，插入或删除多行的事务，则使日志缓冲区更大可节省磁盘I / O。 优化InnoDB表的批量数据加载 将数据导入到InnoDB中时，请关闭自动提交模式，因为它会对每个插入执行日志刷新到磁盘。 要在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句将其包围：

SET autocommit=0;
... SQL import statements ...
COMMIT;

如果对辅助键具有UNIQUE约束，则可以通过在导入会话期间临时关闭唯一性检查来加快表导入：

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

: 如果在表中具有FOREIGN KEY约束，则可以通过在导入会话持续时间内关闭外键检查来加快表导入

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

: 如果需要插入许多行，请使用多行INSERT语法来减少客户端和服务器之间的通信开销

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

在使用自动递增列对表进行批量插入时，请将innodb_autoinc_lock_mode设置为2，而不是默认值1.
>

1 在表创建时定义列FTS_DOC_ID，类型为BIGINT UNSIGNED NOT NULL，具有名为FTS_DOC_ID_INDEX的唯一索引。 例如：

CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT ”,
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

2将数据加载到表中。

3 在加载数据后创建FULLTEXT索引。

优化InnoDB查询

因为每个InnoDB表都有一个主键（无论您是否请求），请为每个表指定一组主键列，这些列用于最重要和时间要求较高的查询。

不要在主键中指定太多或太长的列，因为这些列值在每个辅助索引中都重复。 当索引包含不必要的数据时，读取此数据和内存以进行高速缓存的I / O会降低服务器的性能和可伸缩性。

不要为每个列创建单独的辅助索引，因为每个查询只能使用一个索引。 对极少测试的列或仅具有几个不同值的列的索引可能对任何查询都无帮助。 如果对同一个表有很多查询，测试不同的列组合，请尝试创建少量的并置索引，而不是创建大量单列索引。 如果索引包含结果集所需的所有列（称为覆盖索引），则查询可能能够完全避免读取表数据。 如果索引列不能包含任何NULL值，则在创建表时将其声明为NOT NULL。 当知道每个列是否包含NULL值时，优化器可以更好地确定哪个索引最有效地用于查询。 如果您经常对不经常更新的表执行循环查询，请启用查询缓存：

[mysqld]
query_cache_type = 1
query_cache_size = 10M