mysql 5.6版本说明_初识 MySQL 5.6 新特性、功能

最新推荐文章于 2022-10-25 21:40:31 发布

weixin_39535527

最新推荐文章于 2022-10-25 21:40:31 发布

阅读量359

点赞数

文章标签： mysql 5.6版本说明

本文链接：https://blog.csdn.net/weixin_39535527/article/details/113116203

版权

现在来说明下MyISAM和InnoDB对全文索引的使用。

1、索引长度的设置：

#MyISAM

ft_min_word_len= 1ft_max_word_len ＝84#InnoDB

innodb_ft_min_token_size= 1innodb_ft_max_token_size=84

2、停止词的设置(stopword)：

#MyISAM 停止词存放在文件

ft_stopword_file= /var/lib/mysql/stopword.txt

#InnoDB，停止词存放在表

innodb_ft_server_stopword_table= dbname/ft_stopwords

innodb_ft_user_stopword_table= dbname/ft_stopwords

3、测试

mysql> show create tabletmp_mfulltext\G;*************************** 1. row ***************************

Table: tmp_mfulltextCreate Table: CREATE TABLE`tmp_mfulltext` (

`id`int(11) DEFAULT NULL,

`name`varchar(30) DEFAULT NULL,

`content`text,

FULLTEXTKEY`idx_content` (`content`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> show create tabletmp_ifulltext\G;*************************** 1. row ***************************

Table: tmp_ifulltextCreate Table: CREATE TABLE`tmp_ifulltext` (

`id`int(11) DEFAULT NULL,

`name`varchar(30) DEFAULT NULL,

`content`text,

FULLTEXTKEY`idx_content` (`content`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from tmp_mfulltext where MATCH(content) AGAINST('Auxiliary'); #正常+------+------+---------------------------------------------------------------------------------+

| id | name | content |

+------+------+---------------------------------------------------------------------------------+

| 2 | jin | Auxiliary index table names are prefixed with FTS_ and postfixed with INDEX_*. |

+------+------+---------------------------------------------------------------------------------+

1 row in set (0.00sec)

mysql> select * from tmp_ifulltext where MATCH(content) AGAINST('Auxiliary'); #正常+------+------+---------------------------------------------------------------------------------+

| id | name | content |

+------+------+---------------------------------------------------------------------------------+

| 2 | jin | Auxiliary index table names are prefixed with FTS_ and postfixed with INDEX_*. |

+------+------+---------------------------------------------------------------------------------+

1 row in set (0.01sec)

mysql> select * from tmp_mfulltext where MATCH(content) AGAINST('when'); #搜索长度大于ft_min_word_len，但是没有找到结果。

Emptyset (0.00sec)

mysql> select * from tmp_ifulltext where MATCH(content) AGAINST('when'); #搜索长度大于innodb_ft_min_token_size，但是没有找到结果。

Emptyset (0.00sec)

原因可能就停止词(stopword)的问题，之前介绍过：MySQL全文检索Stopwords的设置手动指定停止词后：

mysql> repair tabletmp_mfulltext; #需要修复下才能进行match+------------------------+--------+----------+----------+

+------------------------+--------+----------+----------+

+------------------------+--------+----------+----------+

1 row in set (0.00sec)

mysql> select count(*) from tmp_mfulltext where MATCH(content) AGAINST('when');#停止词的问题，指定好之后有结果。+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00sec)

mysql> select * from tmp_ifulltext where MATCH(content) AGAINST('when'); #没有结果，这里说明Innodb的全部文索停止词和MyISAM的不一样。

Emptyset (0.01 sec)

mysql> select * fromINFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;36 rows in set (0.00 sec)

手动设置停止表：

#因为InnoDB的停止词是以表形式存的，先建立停止词表：停止词表必须是InnoDB表，只包含一个VARCHAR类型名为VALUE的列

mysql> create table innodb_ft_stopwords(value varchar(30))ENGINE=INNODB; #结构和INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD一样

Query OK,0 rows affected (0.01sec)

#插入停止词：

mysql> INSERT INTO innodb_ft_stopwords(value) VALUES('a');

Query OK,1 row affected (0.00sec)

#加载停止词表，注意格式

mysql> SET GLOBAL innodb_ft_server_stopword_table = 'dba_test/innodb_ft_stopwords';

Query OK,0 rows affected (0.00sec)

mysql> SET GLOBAL innodb_ft_user_stopword_table = 'dba_test/innodb_ft_stopwords';

Query OK,0 rows affected (0.00sec)

#因为fulltext的创建是在加载停止词之后的，之前建立的fulltext找不到结果。

mysql> select * from tmp_ifulltext where MATCH(content) AGAINST('when');

Emptyset (0.00sec)

#optimizetable之后可以

mysql> optimize tabletmp_ifulltext;+------------------------+----------+----------+-------------------------------------------------------------------+

+------------------------+----------+----------+-------------------------------------------------------------------+

+------------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (0.07sec)

#搜索出结果

mysql> select count(*) from tmp_ifulltext where MATCH(content) AGAINST('when');+----------+

| count(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

这里需要注意的是在创建全文索引之前就应先设置好停止词表，不然就得执行optimize。停止词表确定之后，请加入到配置文件中：

innodb_ft_server_stopword_table = dba_test/innodb_ft_stopwords

innodb_ft_user_stopword_table= dba_test/innodb_ft_stopwords

Tips:·全文搜索在MyISAM表中，将忽略至少在一半数据行中出现的单词，而Innodb没有此限制:

mysql> select count(*) from tmp_ifulltext where MATCH(content) AGAINST('the');+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00sec)

mysql> select count(*) from tmp_mfulltext where MATCH(content) AGAINST('the');+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

#生成测试数据

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00sec)

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/tmp/mysqldata/'; #会在该目录下再生成一个以数据库命名的目录

Query OK,0 rows affected (0.02sec)

mysql> insert into t1 values(1),(2),(3);

Query OK,3 rows affected (0.00sec)

Records:3 Duplicates: 0 Warnings: 0#查看物理文件

root@zjy:~# ls -lh /var/lib/mysql3/dba_test/

-rw-rw---- 1 mysql mysql 8.4K 8月 21 00:44 t1.frm

-rw-rw---- 1 mysql mysql 30 8月 21 00:44 t1.isl

root@zjy:~# ls -lh /tmp/mysqldata/dba_test/

-rw-rw---- 1 mysql mysql 96K 8月 21 00:45 t1.ibd

上面被看到数据文件.ibd和表结构定义文件.frm已分离。注意：表空间分离表会产生一个table_name.isl的连接。

③导入和导出表空间：复制文件比mysqldump&restore快

#生成测试数据

server1：

mysql> CREATE TABLE test (c1 INT PRIMARY KEY)engine =innodb;

Query OK,0 rows affected (0.02sec)

mysql> insert into test values(1),(2),(3);

Query OK,3 rows affected (0.00sec)

Records:3 Duplicates: 0 Warnings: 0server2:

mysql> CREATE TABLE test (c1 INT PRIMARY KEY)engine =innodb;

Query OK,0 rows affected (0.02sec)

mysql> insert into test values(1),(3);

Query OK,3 rows affected (0.00sec)

Records:3 Duplicates: 0 Warnings: 0#接着把server2的表空间给discard掉：

root@zjy:/var/lib/mysql/dba_test# ls -lh test.*

-rw-rw---- 1 mysql mysql 8.4K 8月 21 01:37 test.frm

-rw-rw---- 1 mysql mysql 96K 8月 21 01:37 test.ibd

#discard 表空间

mysql> ALTER TABLEtest DISCARD TABLESPACE;

Query OK,0 rows affected (0.00sec)

#表空间没了

root@zjy:/var/lib/mysql/dba_test# ls -lh test.*

-rw-rw---- 1 mysql mysql 8.4K 8月 21 01:37 test.frm

#然后把server1的表空间复制给server2：

mysql>FLUSH TABLES test FOREXPORT; #必须要生成cfg metadata文件

Query OK,0 rows affected (0.00sec)

root@zjy:/var/lib/mysql3/dba_test# ls -lh test.*

-rw-rw---- 1 mysql mysql 330 8月 21 01:40 test.cfg

-rw-rw---- 1 mysql mysql 8.4K 8月 21 01:36 test.frm

-rw-rw---- 1 mysql mysql 96K 8月 21 01:36 test.ibd

#需要把ibd和cfg文件复制到server2上

root@zjy:/var/lib/mysql3/dba_test# cp test.cfg /var/lib/mysql/dba_test/root@zjy:/var/lib/mysql3/dba_test# cp test.ibd /var/lib/mysql/dba_test/#最后修改server2文件权限和import表空间：

root@zjy:/var/lib/mysql/dba_test# chown -R mysql.mysql *mysql> ALTER TABLEtest IMPORT TABLESPACE;

Query OK,0 rows affected (0.02sec)

#值从1，3变成了1，2，3。数据已成功迁移过来

mysql> select * fromtest;+----+

| c1 |

+----+

| 1 |

| 2 |

| 3 |

+----+

#最后解锁server1上的锁：

mysql> unlock tables;

3)innodb内部性能增强：

①将flushing操作独立出主线程，减少核心互斥锁。可设置多个清除线程，减少内存资源争夺。回收UNDO，通过innodb_purge_threads 来开启，在5.5中只能也只有1可以设置。5.6可以设置大于1。刷写脏页，5.6之后支持，Page cleaner线程，从Master Thread独立出来，减轻了Master Thread 的工作和对用户查询的阻塞。进一步提高Innodb 存储引擎的性能和并发。

②检测死锁算法增强。在非递归情况下死锁检测：死锁信息不止可以通过show engine innodb status一种方法查看，还可以记录到 error 日志，并且可以查看历史的死锁，方便分析。开启参数：innodb_print_all_deadlocks，默认关闭。

如果一台高负荷的机器重启后，buffer pool中的热数据被丢失，此时就会重新从磁盘加载到Buffer_Pool缓冲池里，这样当高峰期间，性能就会变得很差，连接数就会很高，应用的性能也受到影响。MySQL5.6里，一个新特性避免的这种问题的出现，在配置文件里添加：

当数据库正常关闭/重启之后，错误日志里面会记录：会生一个ib_buffer_pool的文件，这个文件名字有参数

2015-08-21 19:55:07 7fe34dff8700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool

2015-08-21 19:55:07 7fe34dff8700 InnoDB: Buffer pool(s) dump completed at 150821 19:55:07

2015-08-21 19:55:08 31943 [Note] InnoDB: Shutdown completed; log sequence number 3052155687

当数据库重新起来之后，错误日志里会记录：

2015-08-21 19:55:59 7f5eaf7f8700 InnoDB: Buffer pool(s) load completed at 150821 19:55:59

当数据库正在运行时执行：

查看BP里dump文件的时间：

root@zjy:/var/lib/mysql# ls -lh ib_buffer_pool-rw-rw---- 1 mysql mysql 52K 8月 25 18:30 ib_buffer_pool

执行：

mysql> SET GLOBAL innodb_buffer_pool_dump_now=ON;

Query OK,0 rows affected (0.01sec)

查看BP里dump文件的时间：时间变成当前时间，说明dump成功。

root@zjy:/var/lib/mysql# ls -lh ib_buffer_pool-rw-rw---- 1 mysql mysql 52K 8月 25 19:02 ib_buffer_pool

loaddump文件到BP：

mysql> SET GLOBAL innodb_buffer_pool_load_now=ON;

Query OK,0 rows affected (0.01 sec)

显示BP导入、导出的状态：重启数据库虽然在日志文件里面可以查看到dump和load的一些信息，但在运行时，也可以监控是否成功：

#监控查看BP dump的状态：Innodb_buffer_pool_dump_status，手动执行BP的dump动作

mysql> select now();SET GLOBAL innodb_buffer_pool_dump_now=ON;SHOW STATUS LIKE'Innodb_buffer_pool_dump_status';+---------------------+

| now() |

+---------------------+

| 2015-08-25 19:08:49 |

+---------------------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)+--------------------------------+--------------------------------------------------+

| Variable_name | Value |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 150825 19:08:49 |#和dump执行的时间一致，说明dump成功，是上一个dump的结果+--------------------------------+--------------------------------------------------+

1 row in set (0.00sec)

#监控查看BP load的状态：Innodb_buffer_pool_load_status，手动执行load dump文件动作

mysql> select now();SET GLOBAL innodb_buffer_pool_load_now=ON;SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';+---------------------+

| now() |

+---------------------+

| 2015-08-25 19:10:21 |

+---------------------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)+--------------------------------+--------------------------------------------------+

| Variable_name | Value |

+--------------------------------+--------------------------------------------------+

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 150825 19:10:21 |#和load执行的时间一致，说明load成功，是还原上一个dump文件+--------------------------------+--------------------------------------------------+

1 row in set (0.00 sec)

通过show status 查看之外，还可以通过在information_schema里查看：

dump:SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'INNODB_BUFFER_POOL_DUMP_STATUS';load:SELECT variable_value FROM information_schema.global_status WHEREvariable_name= 'INNODB_BUFFER_POOL_LOAD_STATUS';

此外，也可以中止BP的load，参数：

#在MySQL里运行：

mysql> set global innodb_buffer_pool_load_abort = on;

Query OK,0 rows affected (0.00sec)

#也可以在配置文件里添加：

innodb_buffer_pool_load_abort= 1

另外，innodb_buffer_pool_dump_pct控制备份buffer pool的百分比，5.7中支持。5.6默认dump所有的Buffer pool。总之，引入看BP的自动预热解决了数据库维护重启的时候导致性能变差的问题。

注意：只有在正常关闭MySQL服务，或者pkill mysql时，会把热数据dump到内存。机器宕机或者pkill -9 mysql，是不会dump。

④新增参数innodb_page_size设置页大小。指定在一个MySQL实例的所有InnoDB表空间的页大小。该值是在创建实例时设置的，并且在随后的情况中保持不变。你可以使用16K指定页面大小(默认)，8K，或4K。或者，您可以指定字节的页大小(4096，8192，16384)。16K页面适合大部分场景，特别是表扫描、DML操作和批量更新；较小的页面可能更是适合OLTP和SSD存储设备。

⑤ undo log可独立出系统表空间，原来存在ibd系统表空间里。undo log分离到独立的表空间，并放到单独的文件目录下。对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上。缺点就是不能进行回收(收缩)空间大小。直到MySQL5.7 ，才支持在线收缩。由参数控制：需要在数据库实例建立之后就开启，否则会初始化innodb 失败，开启之后不能修改。具体信息可以看这篇文章

在配置文件里设置：

innodb_undo_directory= /var/lib/mysql4/undolog/ #undo log 存放的目录innodb_undo_tablespaces= 10 #默认表空间的个数，大小默认是10M，以undo001形式存在，值为0则表示使用系统表空间。

另外一个参数是：inodb_undo_logs：代替了先前的参数innodb_rollback_segments，控制着回滚段的数量(注意范围是0-128) 默认不指定的时候是128个回滚段。(注意要想增加回滚段的时候必须要重启mysql)。

要是开启的时候遇到innodb数据初始化失败：Plugin 'InnoDB' init function returned error。则需要先把数据dump出来，删除掉ibdata、ib_logfile等文件，再开启。

⑥ 优化器统计持续化：重启不丢失。缓冲池flush算法增强、支持read-only事务、redo log最大增至512G。

4)复制、日志功能增强

①这里。该参数可以控制ROW下的记录格式，可以动态修改，3个值可以设置：

full：默认行为，记录所有的列，和MySQL5.6之前的版本一样。

minimal：记录被修改的列，其他未修改的列不记录。

noblob ：记录所有的列，除text、blob列外。

测试：

BINLOG:

binlog_format=ROW

binlog_row_image=minimal

SQL:update doctors set username ='XXXYYYZZZ',cellphone='110110110110',modifyTime=now() where id = 101;LOG: #记录了纸杯修改的三个列

###UPDATE`dba_test`.`doctors`

###WHERE###@1=101 /*INT meta=0 nullable=0 is_null=0*/###SET###@4='XXXYYYZZZ' /*VARSTRING(192) meta=192 nullable=0 is_null=0*/###@13='110110110110' /*VARSTRING(48) meta=48 nullable=1 is_null=0*/###@19='2015-08-28 11:20:09' /*DATETIME(0) meta=0 nullable=1 is_null=0*/其他2个可以自行测试。

所以在5.6中binlog_row_image设置为minimal，这样就可以大大减小了binlog的长度，进而减少了空间的使用。但不能通过脚本实现回滚了。

②log_bin_basename、log_bin_index、relay_log_basename，显示binlog和relay log 日志名字，直接用log_bin指定。5.5和5.6的区别如下：

mysql> show global variables like 'log%';+---------------------------------+-------------------------------+

| Variable_name | Value |

+---------------------------------+-------------------------------+

| log_bin | ON |#看不到，需要到配置文件、目录里找。+---------------------------------+-------------------------------+

mysql> show global variables like 'log%';+----------------------------------------+------------------------------------+

| Variable_name | Value |

+----------------------------------------+------------------------------------+

| log_bin | ON |

zjy@192.168.100.2 : analyze_data 05:12:57>show variables like 'relay%';+-----------------------+-----------------------+

| Variable_name | Value |

+-----------------------+-----------------------+

+-----------------------+-----------------------+

mysql> show global variables like 'relay%';+---------------------------+-------------------------------------------+

| Variable_name | Value |

+---------------------------+-------------------------------------------+

| relay_log | mysqld-relay-bin3306 |

+---------------------------+-------------------------------------------+

View Code

③ master.info和relay-log.info支持存储在表中，通过参数：master-info-repository、relay-log-info-repository控制，默认是存文件(file):

mysql> show variables like '%info%';+---------------------------+----------------+

| Variable_name | Value |

+---------------------------+----------------+

| master_info_repository | FILE |

| relay_log_info_repository | FILE |

+---------------------------+----------------+

在配置文件里添加：

master_info_repository= tablerelay_log_info_repository= tablemysql> show variables like '%info%';+---------------------------+----------------+

| Variable_name | Value |

+---------------------------+----------------+

| master_info_repository | TABLE |

| relay_log_info_repository | TABLE |

+---------------------------+----------------+

这里需要知道的是，设置成Table之后，表存储在mysql数据库中。对应的表名是：slave_master_info，slave_relay_log_info。

④ mysqlbinlog命令支持binlog备份。在5.6之前mysqlbinlog只是读取binglog日志，并解析出一个文本格式的内容。5.6增加了一个--raw参数，作用是读取binglog日志，并重新生成binlog格式的文件。其原理就是开启一个IO(dump)线程来拉取binlog日志，下面通过使用方法介绍下：

A Server:

mysql> SHOW BINARYLOGS;+---------------------+-----------+

| Log_name | File_size |

+---------------------+-----------+

| mysql-bin_06.000001 | 174 |

| mysql-bin_06.000002 | 201 |

| mysql-bin_06.000003 | 201 |

| mysql-bin_06.000004 | 1864 |

| mysql-bin_06.000005 | 1420 |

| mysql-bin_06.000006 | 191 |

+---------------------+-----------+

B Server：

备份1～6的二进制日志：1：备份001和002的二进制日志，再备份其他的就继续指定binlog name

mysqlbinlog--read-from-remote-server --user=zjy --password=123456 --host=127.0.0.1 --raw mysql-bin_06.000001 mysql-bin_06.000002

2：从000001开始备份所有的二进制日志：

mysqlbinlog--read-from-remote-server --user=zjy --password=123456 --host=127.0.0.1

3：从000001开始备份所有的二进制日志，命令行不退出，一直监控着日志的轮巡，一有新的event就马上备份。

mysqlbinlog--read-from-remote-server --user=zjy --password=123456 --host=127.0.0.1 --raw --stop-never mysql-bin_06.000001

4：--stop-never-slave-server-id=id，指定binlog server的id，开启多个binlog server，可以用不同的server-id，默认是65535。mysqlbinlog ----raw --stop-never mysql-bin_06.000001

--read-from-remote-server --user=zjy --password=123456 --host=127.0.0.1 --raw --stop-never mysql-bin_06.000001 --result-file=/home/zhoujy/

在Server B里面看到从Server A上备份过来的binlog日志，具体的使用方法请见官方文档和这篇说明。

⑤支持多线程复制：多线程复制是基于库的，一个线程也只针对一个数据库。注意：当开启多线程复制后，需要关闭slave_transaction_retries，设置其为0，否则会在start/stop slave中遇到：

slave_transaction_retries is not supported in multi-threaded slave mode. In the event of a transient failure, the slave will not retry the transaction and will stop.

5.6以前的从服务器，有一个io线程负责接收binary log，还有一个sql线程负责执行binary log中的sql语句。如果主服务器的数据更新相当频繁，而从服务器由于某些原因跟不上，会导致从服务器落后比较长的时间。5.6之后采用多个sql线程，每个sql线程处理不同的database，提高了并发性能，即使某database的某条语句暂时卡住，也不会影响到后续对其它的database进行操作。

要把多线程改成单线程，即 slave_parallel_workers 从非0改成0，则执行：

zjy@192.168.110.xx : (none) 02:28:49>stop slave;

Query OK,0 rows affected (0.30sec)

zjy@192.168.110.xx : (none) 02:28:55>START SLAVE UNTIL SQL_AFTER_MTS_GAPS;

Query OK,0 rows affected (0.02sec)

zjy@192.168.110.xx : (none) 02:29:07>SET @@GLOBAL.slave_parallel_workers = 0;

Query OK,0 rows affected (0.01sec)

zjy@192.168.110.xx : (none) 02:29:29>START SLAVE SQL_THREAD;

Query OK,0 rows affected (0.11 sec)

查看，只有一个线程：

更多的信息见官方文档。

⑥支持延迟复制，MASTER_DELAY。在change的时候指定，单位是秒。

CHANGE master TO MASTER_DELAY=600;

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State:

Master_Host:127.0.0.1Master_User: rep

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin3306.000006Read_Master_Log_Pos:23940536...

...

SQL_Delay:0...

⑦新增GTID复制，详细信息见：

⑧

binlog_checksum=CRC32 ：事件写入二进制进行校验，默认CRC32。

=1 ：检查二进制日志，bin log。默认禁止。

=1 ：检查中继日志，relay log。默认禁止。

⑨ thread_pool_size 的引入：在引入线程池之前，MySQL支持的线程处理方式(thread_handling参数控制)有no-threads和one-thread-per-connection两种方式，no-threads方式是指任一时刻最多只有一个连接可以连接到server，一般用于实验性质。 one-thread-per-connection是指针对每个连接创建一个线程来处理这个连接的所有请求，直到连接断开，线程结束。是thread_handling的默认方式。one-thread-per-connection存在的问题就是需要为每个连接创建一个新的thread，当并发连接数达到一定程度，性能会有明显下降，因为过多的线程会导致频繁的上下文切换，CPU cache命中率降低和锁的竞争更加激烈。解决one-thread-per-connection的方法就是降低线程数，这样就需要多个连接共用线程，这便引入了线程池的概念。

thread_handling =pool-of-threads

5)优化器增强：可以看这里

mysql >show variables like 'optimizer_switch'\G; *************************** 1. row ***************************Variable_name: optimizer_switch

Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

①MRR优化(Multi-Range Read)：mrr=on,mrr_cost_based=on。MRR的优化用于range, ref, eq_ref, and Batched Key Access访问方法

Multi-Range Read 多范围读(MRR) 它的作用是基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。在没有MRR之前(MySQL5.6之前)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)，不同主键不在同一个page里面时必然导致多次IO 和随机读。使用MRR优化(MySQL5.6之后)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率。

②ICP优化(Index Condition Pushdown)：index_condition_pushdown=on。ICP的优化用于range, ref, eq_ref, and ref_or_null访问方法

Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。禁用ICP(MySQL5.6之前)，引擎层会利用索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤(回表)。启用ICP(MySQL5.6之后)，如果部分WHERE条件能使用索引中的字段，MySQL会把这部分下推到引擎层。存储引擎通过使用索引把满足的行从表中读取出。ICP减少了引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，减少io次数，提高查询语句性能。

SELECT … FROM single_table … ORDER BY non_index_column [DESC] LIMIT N [OFFSET M];

当有足够的内存(sort_buffer_size)来存储N+M行记录时，会在内存中创建一个优先队列来存储数据，这意味着一次扫描表就可以获得想要的数据，避免了创建/写入临时表及归并排序操作(之前版本的逻辑)

大概逻辑为：

1.扫描表，将数据有序的插入到优先队列中，如果队列满了，则按顺序移除多余的记录

2.返回队列中的N行记录，如果指定了OFFSET M，则忽略开始的M条记录，然后返回剩下的N条

use_index_extensions＝on。

InnoDB的二级索引都包含主键信息，隐性的包含了主键列，可以通过在二级索引上添加主键列来查看(显性的添加主键列到二级索引，索引大小不变)。在MySQL5.6之前，MySQL优化器选择索引时不会考虑到这些隐性的主键列，一般都需要手动添加。而在MySQL5.6之后，优化器选择时，会考虑到二级索引上的primary key。

⑤BKA优化(Batched Key Access)：mrr=on,mrr_cost_based=off,batched_key_access=on.

提高表join性能的算法，在介绍BKA之前，先了解下Join表的优化历史：

Nested Loop Join算法：将驱动表/外部表的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次，效率非常差。

Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。主要用于当被join的表上无索引。

Batched Key Access算法：当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表。对这些行按照索引字段进行排序，因此减少了随机IO。如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)。BKA默认是关闭的，要使用BKA，必须调整系统参数optimizer_switch的值，batched_key_access设置为on，因为BKA使用了MRR，因此也要打开MRR,但是基于成本优化MRR算法不是特别准确官方文档推荐关闭mrr_cost_based，将其设置为off。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

⑥explain支持对insert、delete、update、replace语句，并且支持json格式的

mysql>set optimizer_trace='enabled=on';

mysql>select * frominformation_schema.optimizer_trace\G;

⑦子查询优化：subquery_materialization_cost_based

在较多等值查询(例如多值的IN查询)情景中，预估可能会扫描的记录数，从而选择相对更合适的索引。用于优化in()，以确认是否直接使用索引统计，在where条件中列的等值条件个数小于这个值时，使用index dive来估算行数，否则使用index statistics来估算；设置为0则禁用index statistics, index dive更准确但效率低

...

5.6的新参数会在另一篇文章说明。

...

三总结

上面大概介绍了5.6的一些新的特性，后续会不定时更新，尽量避免5.6的一些问题。

四参考文章

weixin_39535527

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 5.6版本说明_初识 MySQL 5.6 新特性、功能

现在来说明下MyISAM和InnoDB对全文索引的使用。1、索引长度的设置：#MyISAMft_min_word_len= 1ft_max_word_len ＝84#InnoDBinnodb_ft_min_token_size= 1innodb_ft_max_token_size=842、停止词的设置(stopword)：#MyISAM 停止词存放在文件ft_stopword_f...
复制链接

扫一扫