mysql-5.6新特性

jingshen

1. undo log
　　undo log分离到独立的表空间，并放到单独的文件目录下；这给我们部署不同IO类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上。
1.1. 设置
　　vim /etc/my.cnf
　　[mysqld]
　　innodb_undo_directory=/data/undolog
　　innodb_undo_log=128
　　innodb_undo_tablespaces=16
1.2. 说明
　　innodb_undo_directory 用来指定undo日志文件的路径。如果需要转移undo日志文件的位置，只需要把undo文件拷贝过去并修改该配置项即可（需要修改新目录的用户为mysql）。
　　innodb_undo_log 用来表示回滚段的个数（之前版本命名为innodb_rollback_segments），该变量可以动态调整，默认为128，但是物理回滚段不会减少，只会控制用到的回滚段的个数。
　　innodb_undo_tablespaces 用于设定创建的undo表空间的个数。默认值为0，即不设置独立的undo表空间，记录到ibdata中。此处设定为16，初始化中会自动创建命名为undo001-undo016的16个文件，每个文件默认10M。在数据库初始化后，这个配置项就无法再更改，否则会导致mysql启动失败。
2. GTID sql多进程
2.1. 确认GTID正常
　　mysql> show slave status\G;
　　*************************** 1. row***************************
　　Slave_IO_State: System lock
　　Master_Host: 10.90.5.143
　　Master_User: repluser
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000006
　　Read_Master_Log_Pos: 237106479

　　>
　　>　　Relay_Master_Log_File: mysql-bin.000006
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 237092346

　　>　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: 4
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 1
　　Master_UUID:eff879d0-3d3f-11e4-9797-080027019055
　　Master_Info_File: mysql.slave_master_info
　　SQL_Delay: 0
　　SQL_Remaining_Delay: NULL

　　Slave_SQL_Running_State: Slave has read all>　　Master_Retry_Count: 86400
　　Master_Bind:
　　Last_IO_Error_Timestamp:
　　Last_SQL_Error_Timestamp:
　　Master_SSL_Crl:
　　Master_SSL_Crlpath:
　　Retrieved_Gtid_Set: eff879d0-3d3f-11e4-9797-080027019055:1-550
　　Executed_Gtid_Set: eff879d0-3d3f-11e4-9797-080027019055:1-522
　　Auto_Position: 1
　　1 row in set (1.10 sec)
2.2. 设置开启多线程
　　vim /etc/my.cnf
　　[mysqld]
　　slave_parallel_workers=10
　　#默认为0，即不开启多线程，最大为1024。
　　在mysql中进行查看，
　　mysql> show variables like"slave_parallel_workers";
　　+-------------------------------------+----------+
　　| Variable_name          | Value  |
　　+-------------------------------------+----------+
　　| slave_parallel_workers    |10    |
　　+-------------------------------------+-----------+
3. 优化器
　　1.子查询优化，select .. from… order by … limit ..;
　　2.使用Multi-Range Read。顺序访问代替随机访问。
　　3.使用ICP，whereindex优化。
　　4.explain可以解释delete，insert，replace，update。
　　5.BKA算法。
　　6.优化器追踪功能。
3.1. select子查询
　　---mysql-5.5.38

　　select * from employees where emp_no in(select emp_no from>　　……
　　| 499996 | 1953-03-07 | Zito           | Baaz             | M      | 1990-09-27 |
　　| 499997 | 1961-08-03 | Berhard        | Lenart           | M      | 1986-04-21 |
　　+-----------+-----------------+------------------------+---------------------------+--------------+----------------+
　　97750 rows in set(0.69 sec)
　　---mysql-5.6.16

　　select * from employees where emp_no in(select emp_no from>　　……
　　| 499996 | 1953-03-07 | Zito           | Baaz             | M      | 1990-09-27 |
　　| 499997 | 1961-08-03 | Berhard        | Lenart           | M      | 1986-04-21 |
　　+-----------+-----------------+------------------------+----------------------------+-------------+----------------+
　　97750 rows in set(0.52 sec)
　　---mysql-5.5.38

　　select * from salaries where emp_no in (selectemp_no from>　　……
　　| 499997 | 82750 | 2000-08-26 | 2001-08-26 |
　　| 499997 | 83441 | 2001-08-26 | 9999-01-01 |
　　+-----------+-------------+----------------+-----------------+
　　1137450 rows in set (6.21sec)
　　---mysql-5.6.16

　　select * from salaries where emp_no in(select emp_no from>　　…….
　　| 499997 | 82750 | 2000-08-26 | 2001-08-26 |
　　| 499997 | 83441 | 2001-08-26 | 9999-01-01 |
　　+-----------+-------------+----------------+-----------------+
　　1137450 rows in set (2.12sec)
　　mysql-5.6 对于同一条命令总是开启新查询。
3.2. ICP MRR
3.2.1. 新增项
　　---mysql-5.5.38
　　mysql> show variables like"optimizer_switch";
　　+----------------------------------------+-------------------------------------------------------------------------+
　　| Variable_name            | Value                                        |
　　+----------------------------------------+-------------------------------------------------------------------------+
　　| optimizer_switch |index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on|
　　+----------------------------------------+--------------------------------------------------------------------------+
　　---mysql-5.6.16
　　mysql> show variables like"optimizer_switch";
　　+-----------------------------+-----------------------------------------------------------------------------------+
　　| Variable_name     |Value                                              |
　　+-----------------------------+------------------------------------------------------------------------------------+
　　| optimizer_switch | 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 |
　　+-----------------------------+------------------------------------------------------------------------------------+
3.2.2. 验证
　　---5.5.38
　　mysql> explain select log_time fromperson where nick_name = 'Lucy';
　　+----+-----------------+-----------+--------+--------------------+---------------+---------+-------+------+-------------+

　　|>　　+----+-------------+--------+------+---------------+---------------+---------+-------+------+-------------+

　　|  1| SIMPLE      | person | ref  |>　　+----+-----------------+-----------+--------+--------------------+---------------+---------+-------+------+-------------+
　　mysql> explain select log_time fromperson where nick_name = 'Lucy';
　　+----+-------------+--------+------+---------------+---------------+---------+-------+------+-----------------------+

　　|>　　+----+-------------+--------+------+---------------+---------------+---------+-------+------+-----------------------+

　　|  1 |SIMPLE      | person | ref  |>　　+----+-------------+--------+------+---------------+---------------+---------+-------+------+-----------------------+
　　explain中显示为using index condition，则证明优化器使用indexcondition pushdown。
3.3. 执行计划
　　在mysql-5.6中对explain解释器新增对insert，delete，update，replace语句的支持。
　　---mysql-5.5.38
　　mysql> explain delete from person;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check themanual that corresponds to your MySQL server version for the right syntax touse near 'delete from person' at line 1
　　---mysql-5.6.16
　　mysql> explain delete from person;
　　+----+----------------+---------+---------+--------------------+-----+-----------+-------+--------+-------------------+

　　|>　　+----+----------------+---------+---------+--------------------+-----+------------+-------+--------+-------------------+
　　|  1| SIMPLE  | NULL  | NULL | NULL        |NULL | NULL    | NULL |    5 | Deleting allrows |
　　+----+----------------+---------+---------+--------------------+-----+-------------+------+------+---------------------+

　　mysql> explain update person setnick_name='Joe' where>　　+----+-----------------+-----------+----------+-----------------+------+-----------+---------+--------+------------------+

　　|>　　+----+-----------------+-----------+----------+------------------+-----+-----------+---------+--------+------------------+
　　|  1| SIMPLE      | person | range |PRIMARY       | PRIMARY | 4       | const |    1 | Using where|
　　+----+-----------------+-----------+----------+------------------+-----+-----------+---------+---------+-----------------+
4. timestamp
4.1. 字段属性
　　在MySQL5.5里（或更早的MySQL5.1）timestamp类型一个表里只允许一列字段拥有自动插入时间和自动更新时间、或只允许一列字段有自动插入时间，另一列字段不能有自动更新时间。
　　---mysql-5.5.38
　　mysql> create table time1(id int,t timestamp default current_timestamp on updatecurrent_timestamp,t2 timestamp default current_timestamp on updatecurrent_timestamp);
　　ERROR 1293 (HY000): Incorrect table definition; there can be onlyone TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATEclause
　　mysql> create table time(id int,ttimestamp,t2 timestamp);
　　Query OK, 0 rows affected (0.04 sec)
　　mysql> insert into time(id)values(1),(2);
　　Query OK, 2 rows affected (0.02 sec)
　　Records: 2 Duplicates: 0  Warnings: 0
　　mysql> select * from time;
　　+---------+-----------------------------+-----------------------------+

　　|>　　+---------+-----------------------------+-----------------------------+
　　|    1| 2014-09-23 16:49:09 | 0000-00-00 00:00:00 |
　　|   2 | 2014-09-23 16:49:09 | 0000-00-00 00:00:00 |
　　+---------+-----------------------------+-----------------------------+
　　---mysql-5.6.16
　　MySQL5.6中的timestamp类型一个表里可以有多列字段拥有自动插入时间和自动更新时间。
　　mysql> create table time(id int,t timestamp default current_timestamp on updatecurrent_timestamp,t2 timestamp default current_timestamp on updatecurrent_timestamp);
　　Query OK, 0 rows affected (0.10 sec)
　　mysql> insert into time(id)values(1),(2);
　　Query OK, 2 rows affected (0.01 sec)
　　Records: 2 Duplicates: 0  Warnings: 0
　　mysql> select * from time;
　　+---------+-----------------------------+-----------------------------+

　　|>　　+---------+-----------------------------+-----------------------------+
　　|   1 | 2014-09-23 16:52:16 | 2014-09-23 16:52:16 |
　　|   2 | 2014-09-23 16:52:16 | 2014-09-23 16:52:16 |
　　+---------+-----------------------------+-----------------------------+
　　2 rows in set (0.00 sec)
4.2. 数据精确位
　　mysql> create table time2(id int,t1timestamp(6));
　　Query OK, 0 rows affected (0.07 sec)
　　mysql> insert into time2(id) values(1);
　　Query OK, 1 row affected (0.02 sec)
　　mysql> select * from time2;
　　+---------+---------------------------------------+

　　|>　　+---------+----------------------------------------+
　　|   1 | 2014-09-23 16:57:03.886880 |
　　+---------+----------------------------------------+
4.3. 初始化数据警告处理
　　mysql-5.6启动报警告：
　　[Warning] TIMESTAMP with implicit DEFAULTvalue is deprecated.
　　Please use --explicit_defaults_for_timestampserver option
　　(seedocumentation for more details).
　　在启动文件中添加：
　　[mysqld]
　　explicit_defaults_for_timestamp=true
5. online DDL
　　1.LOCK语句的使用是在alter table中以逗号作为分隔的。
　　2.在线DDL操作可能被推迟，直到任何访问该表的现有事务被提交或回滚。
　　3.在线DDL操作可能要在其他任何访问该表的并发事务被提交或回滚之后再完成。
　　4.当一条DDL操作在运行时，只要altertable语句使用了LOCK=NONE或者LOCK=SHARED，并发查询是相对简单的。
　　5.注意是否打开或关闭自动提交。如果它是关闭的，执行DDL操作之前要注意其他会话中结束事务（甚至只是查询）的操作。
　　6.对于LOCK=SHARED的情况，混合查询以及DML操作的并发事务遇到死锁错误时，在DDL完成后必须重新启动。
　　7.对于LOCK=NONE的情况，并发事务可以自由组合查询和DML。DDL操作会等待并发事务的提交或者回滚。
　　8.对于LOCK=NONE的情况，并发事务可以自由混合查询及DML，但是这些事务会等到DDL操作完成后才能去访问表。
　　注：以下示例来自于mysql-5.6官方文档section—14.11.5。