1、InnoDB change buffer设置 Change buffer是作为buffer pool中的一部分存在。 Innodb_change_buffering参数缓存所对应的操作：(update会被认为是delete+insert) all: 默认值，缓存insert, delete, purges操作 none: 不缓存 inserts: 缓存insert操作 deletes: 缓存delete操作 changes: 缓存insert和delete操作 purges: 缓存后台执行的物理删除操作 innodb_change_buffer_max_size参数配置change buffer在buffer pool中所占的最大百分比，默认是25%，最大可以设置为50%。当MySQL实例中有大量的修改操作时，要考虑增大innodb_change_buffer_max_size。 2、InnoDB线程并发度配置 InnoDB利用操作系统的线程技术达到多线程实现。 Innodb_thread_concurrency参数限制同时执行的线程数。默认值是0代表没有限制。 Innodb_thread_sleep_delay参数确定 3、InnoDB后台IO线程配置 通过配置innodb_read_io_threads和innodb_write_io_threads参数来指定后台读和写数据页的线程的个数，默认值是4，容许的取值范围是1-64。 ysql> show engine innodb status\G 4、使用Linux异步IO InnoDB在Linux平台使用异步IO子系统完成数据文件页的读写请求，可以通过innodb_user_native_aio参数控制，默认是开启状态，并且需要libaio系统库支持。 5、InnoDB主线程配置 InnoDB的主线程在后台承担了诸多的任务，绝大多数是和IO操作相关的，比如将buffer pool中的修改后的数据刷新的磁盘文件中。 Innodb_io_capacity参数设置了InnoDB的整体IO能力。该参数应该被设置为等同于操作系统每秒的IO操作数量。该参数可以设置为100及以上的任意数值，默认值是200。其中设置为100相当于7200RPM的磁盘性能。 6、InnoDB purge配置 InnoDB的purge操作是一类垃圾回收操作，是由一个或多个独立线程自动执行。通过innodb_purge_threads参数设置purge线程的数量，如果DML操作比较复杂且涉及到多个表时，则可以考虑增加此值，最大可以设置为32。事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页. 7、InnoDB 优化器统计信息配置 Innodb表的优化器统计信息分为永久和非永久两种。 永久的优化器统计信息即使是服务器重启的情况下也会存在，其用来选出更优的执行计划以便提供更好的查询性能。 通过配置innodb_stats_auto_recalc参数来控制统计信息是否在表发生巨大变化（超过10%的行）之后是否自动更新，但由于自动更新统计信息本身是异步的，所以有时未必能马上更新，这是可以执行analyze table语句来同步更新统计信息。 8、Create table和alter table语句中的Stats_persistent, stats_auto_recalc, stats_sample_pages子句可用来配置单个表的优化器统计信息规则 Stats_persistent用来指定是否对此表开启永久统计资料，1代表开启，0代表不开启。当开启之后，可以执行analyze table命令来收集统计资料。 Stats_auto_recalc表示是否自动对表的永久统计资料进行重新计算，默认值和全局参数innodb_stats_auto_recalc一致。1代表当表中数据10%以上更新时重新计算，0代表不自动更新，而是通过analyze table命令重新计算 Stats_sample_pages表示当计算索引列的统计资料是需要的索引页的样本数量 CREATE TABLE `t1` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime, PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25; 优化器永久统计资料数据在系统表mysql.innodb_table_stats和mysql.innodb_index_stats表中存储，这两个表中有个字段last_update可以用来判断统计信息最后更改时间。这两个表的数据也可以被手工更改。当手工更改完数据之后，要执行flush table 表名命令来重新load此表的统计资料。innodb_table_stats表中每个目标表一行记录，而innodb_index_stats表中每个索引会有多条记录! Innodb_table_stats表结构：  Innodb_index_stats表结构：  9、优化器统计信息会被存储在磁盘上，通过设置innodb_stats_persistent=ON参数（默认）。   MySQL的查询优化器会基于评估好的统计资料选择合适的索引参与到执行计划中，而类似analyze table的语句会从索引中随机选取数据页参与到每个索引的基数评估中。而参数innodb_stats_persistent_sample_pages决定了参与评估的数据页的数量，默认值是20。当语句执行的执行计划不是最优选择时，则考虑增加此参数，以便获得正确的统计资料。   当设置innodb_stats_persistent=OFF参数或者对单个表设置stats_persistent=0时，对应的统计资料就仅存在于内存中而非磁盘上，当服务器重启之后统计资料丢失。当然此类统计资料也可以周期性的更新。 比如执行analyze table语句手动刷新统计资料，或者在innodb_stats_on_metadata选项打开之后执行show table status/show index或查询information_schema.tables/statistics表时非永久统计资料会自动更新，当InnoDB检测到1/16的表数据被修改时也会更新。 10、重置InnoDB系统表空间 最简单的增加系统表空间的办法就是在初始化阶段配置数据文件的自增长，通过配置最后一个文件的autoextend属性，当数据文件空间不足时默认自动增长64M大小。也可以通过修改innodb_autoextend_increment参数修改自动增长的大小。 也可以通过增加另一个数据文件方法扩展表空间，步骤如下： 关闭MySQL 检查配置的最后一个数据文件是否是autoextend，如果是则根据当前数据文件的大小去掉自动扩展属性，改成当前大小 在配置文件的innodb_data_file_path参数里增加一个新的数据文件，选择是否自动扩展 启动MySQL innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:10M:autoextend ####改成 innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 11、减小系统表空间大小的方法如下： Mysqldump出所有的InnoDB表，包括mysql系统数据库下的五个表 mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | | slave_master_info | | slave_relay_log_info | | slave_worker_info | +----------------------+ 关闭MySQL 删除所有InnoDB的数据文件和日志文件，包括*.ibd和ib_log文件，还有在MySQL库文件夹下的*.ibd文件 删除所有.frm的InnoDB表文件 在配置文件里配置新的表空间文件 启动MySQL 导入备份出的dump文件 12、重置InnoDB redo log文件大小 关闭MySQL 通过innodb_log_file_size更改文件大小，通过innodb_log_files_in_group更改文件数量 启动MySQL innodb_log_file_size=30M innodb_log_files_in_group=3 13、配置单表数据文件表空间 InnoDB的单表数据文件表空间代表每个InnoDB表的数据和索引数据都存放在单独的.ibd数据文件中，每个.ibd数据文件代表独立的表空间。此属性通过innodb_file_per_table配置。 此配置的主要优势： 当删除表或者truncate表的时候，意味着对磁盘空间可以回收。而共享表空间时删除一个表时空间不会释放而只是文件里有空闲空间 Truncate table命令要比共享表空间快 通过定义create table …data directory=绝对路径，可以将特定的表放在特定的磁盘或者存储空间 可以将单独的表物理拷贝到另外的MySQL实例中 此配置的劣势： 每个表都有未使用的空间，意味着磁盘空间有些浪费 启动单独表空间的方式如下： [mysqld] innodb_file_per_table=1 当设置innodb_file_per_table=0时，所有创建的新表都会放置到共享表空间里，除非在create table命令里显示的使用tablespace选项。 将已经存在于共享表空间的表修改为独立表空间的方法： SET GLOBAL innodb_file_per_table=1; ALTER TABLE table_name ENGINE=InnoDB;   通过命令create table … data directory=绝对路径可以将单表数据文件创建在另外的目录里。在指定的绝对路径下，会创建数据库名相同的文件夹，里面含有此表的.ibd文件，同时在MySQL的默认数据文件下的数据库名文件夹下会创建table_name.isl文件包含了此表的路径，相当于link文件。 当没有开启innodb_file_per_table时，可以将tablespace和data directory两个参数配合使用 CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table DATA DIRECTORY = '/alternative/directory'; 14、设置Undo log独立表空间 默认情况下undo log是存储在系统表空间里，我们也可以将其存放在一个或多个独立表空间下。 Innodb_undo_tablespaces参数定义了有多少个undo表空间，此参数只能在建立MySQL实例时被配置 innodb_undo_directory参数定义了undo表空间的存放路径 innodb_undo_logs参数定义了回滚段的数量 mysql> show variables like '%innodb_undo%'; Innodb_undo_log_truncate参数决定是否开启undo表空间清空 mysql> SET GLOBAL innodb_undo_log_truncate=ON; 当设置了此参数为ON后，则代表undo文件大小超过innodb_max_undo_log_size（默认值是128M）的都标记为清空 15、创建普通表空间 通过create tablespace命令可以创建一个共享的InnoDB表空间，和系统表空间一样，多个表可以在此表空间上存储数据，此表空间的数据文件可以放置在任意的文件夹下。 CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]   mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; ##创建在MySQL数据目录下 mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB; 当创建完表空间之后，就可以通过create table …tablespace或者alter table … tablespace命令将表增加到此表空间上 16、通过alter table命令可以将InnoDB表在系统表空间、独立表空间和普通表空间之间转化： ALTER TABLE tbl_name TABLESPACE [=] tablespace_name ##从系统表空间或者独立表空间上转移到普通表空间 ALTER TABLE tbl_name ... TABLESPACE [=] innodb_system ##从普通表空间或者独立表空间上转移到系统表空间 ALTER TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table ##从系统表空间或者普通表空间转移到独立表空间 Alter table … tablespace语句的执行都会导致此表会重建，即使表空间的属性和之前是一样的。 17、当删除一个普通表空间时，首先需要保证此表空间上的所有表都被删除，否则会报错。删除表空间是用drop tablespace语句来执行。Drop database的动作会删除所有的表，但创建的tablespace不会被自动删除，必须通过drop tablespace显示执行。 普通表空间不支持临时表，而且也不支持alter table … discard tablespace和alter table …import tablespace命令。 18、修改表的存储引擎 通过alter table语句修改已有表的存储引擎 ALTER TABLE table_name ENGINE=InnoDB; 19、自增长字段设置 当对InnoDB表设置了自增长字段之后，表会在内存中保存一个自增长计数器。 默认情况下自增长字段的初始值是1，但也可以通过配置auto_increment_offset参数将所有的自增长字段初始值设置为另外的值，而当表中插入数值时，InnoDB会求出当前表中的该列的最大值，然后在此基础上加1作为插入的数据。默认是以+1为增长的进度，但也可以通过auto_increment_increment配置所有自增长字段的自定义增长进度。