http://dinglin.iteye.com/blog/1682188

上一篇我们介绍了ibd_used这个工具，我们用来量化看表数据文件的page使用率。这里用来说明optimize table这个命令的问题和优化。

实例准备

建一个这样的表

CREATE TABLE tb (

seq_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

a varchar(32) DEFAULT NULL,

b varchar(32) DEFAULT NULL,

c varchar(32) DEFAULT NULL,

d char(255) DEFAULT NULL,

Primary key (seq_id),

KEY a (a),

KEY bc (b,c),

KEY cb (c,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行语句为“insert into tb(a,b,c) values(randstr, randstr, randstr);” randstr是客户端程序生成的长度30字节的随机字符串。30个线程并发，每个线程插入1w条记录。

等待更新完成后（包括purge完成，从系统的vmstat上看无任何io），执行./ibd_used tb.ibd 0 100000000，可以从最后4行看到各个索引的page平均利用率如下图。

说明： 你会发现即使是主键索引，利用率也不一定很高。原因是什么？

Optimize table 效果

我们知道Optimize table是用来作表整理的， 执行一下 optimize table tb，再看ibd_used的结果。

说明：这里我们发现，pk的page利用率明显提升，是optimize效果，但是其他几个索引的page利用率却没有明显效果。为什么呢？

有同学问到InnoDB的索引长度问题，简单说几个tips。

关于3072

大家经常碰到InnoDB单列索引长度不能超过767bytes，实际上联合索引还有一个限制是3072。

[][1]

可以看到，由于每个字段占用255*3, 因此这个索引的大小是3825(255*3*5)>3072，报错。

为什么3072

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。

所以一个记录最多不能超过8k。 又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。 由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

单列索引限制

上面有提到单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.6以后，开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做 innodb_large_prefix。

这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072。

但是MySQL有点着急，在5.5的时候就引入这个参数，在加索引的入口处没有这个限制，但是内部的限制没有去掉。

因此导致如下效果(5.5)：

[][2]

可以看到默认行为是建表成功，报一个warning，并且将长度阶段为255。

使用large_prefix后，报的是error，建表不成功。这个参数要等到 5.6才有效。

原文地址: 关于InnoDB索引长度限制的tips

由浅入深理解InnoDB的索引实现(1)

由浅入深理解InnoDB的索引实现(2)

MySQL数据库InnoDB存储引擎Log漫游(1)

MySQL数据库InnoDB存储引擎Log漫游(2)

MySQL数据库InnoDB存储引擎Log漫游(3)

以上转自 宋利兵 老师的公众号“MySQL代码研究”

在5.6以后，可以通过命令动态修改.

注意有些命令是需要主从都要执行，有些命令是只在slave执行。

gtid_mode 的几种状态值说明： OFF: 不产生 GTID, 基于 binlog+position,也不能接受GTID的日志。默认值 OFF_PERMISSIVE:  不生产 GTID,但作为slave可以识别GTID事务也可以识别非GTID事务 ON_PERMISSIVE: 产生GTID，slave可以处理GTID事务和非GTID事务 ON: 产生GTID事务，slave只接受GTID事务

实验一：将传统复制切换到GTID复制

启用GTID:

set @@global.enforce_gtid_consitency=warn;
set @@global.enforce_gtid_consistency=on;
set @@global.gtid_mode=OFF_PERMISSIVE; #不产生gtid,但可以处理gtid
set @@global.gtid_mode=ON_PERMISSIVE; #产生gtid,也可以处理gtid
show status like ‘ONGOING_ANONYMOUS_TRANSACTION_COUNT’;
set @@global.gtid_ode=on;

stop slave [for channel ‘channel’];change master to master_auto_postion=1; start slave;（slave)

更改复制到自动识别GTID环境

3.修改配置文件 my.cnf

gtid-mode=on
enforce_gtid_consistency=on

实验二：将GTID复制切换到传统复制

禁用GTID:

1.停止复制,禁止master_auto_position

stop slave [for channel ‘channel_name’];
change master to mster_auto_position=1,master_log_file=file，MASTER_LOG_POS=position [FOR CHANNEL ‘channel’];
start slave [for channel ‘channel_name’];

其中master_log_pos为’Exec_Master_Log_Pos’的值

2.更改日志格式

set @@global.gtid_mode=ON_PERMISSIVE;
set @@global.gtid_mode=OFF_PERMISSIVE;
select @@global.gtid_owned;
set @@global.gtid_mode=off;
set global enforce_gtid_consistency=off;

3.修改配置文件 my.cnf

在5.7下半同步是以插件的形式出现的，所以在启用半同步前要先安装半同步插件 semisync_master.so

On the master:

INSTALL PLUGIN rpl_remi_sync_master SONAME 'semisync_master.so';

On slave slave:

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

在my.cnf里配置,要写在mysqld段

master:

[mysqld]
repl_semi_sync_master_enable=1
repl_semi_sync_master_timeout=1000 #1 second

slave:

[mysqld]
repl_semi_sync_slave_enable=1

卸载插件：

uninstall plugin rpl_semi_sync_master; # master
uninstall plugin rpl_semi_sync_slave;  # slave

在主上设置slave的数量，用来接受slave返回的次数数量2,用来分析 show global variables like ‘%semi%’;

set global rpl_semi_sync_master_wait_for_slave_count=2;

监控：

show global status like 'rpl_semi_sync%';

架构为两主一从，两主为同一台服务器的多实例，安装方法请参考上篇文章 http://blog.haohtml.com/archives/17300。

主master1    IP: 192.168.1.116     PORT: 3306 主master2    IP: 192.168.1.116     PORT: 3307 从slave        IP: 192.168.1.200     PORT: 3306

两主为全新安装。如果以前安装过的话，可以将原来的数据库删除掉，再执行 reset master 即可。(否则需要将两个主的想着库表使用 mysqldump到从中) my.cnf 配置

[master1 3306] 
[client]
port=3306
socket=/data/mysql/mysql3306/tmp/mysql.sock

[mysqld]
basedir=/data/mysql/mysql3306
datadir=/data/mysql/mysql3306/datadir
#socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/mysql3306/tmp/mysql.sock
port=3306

log-bin=mysql-bin
binlog-format=row 	# 二进制日志的格式：有 `row`、`statement` 和 `mixed` 三种

注：当设置隔离级别为 READ-COMMITED 必须设置二进制日志格式为 ROW ,MySQL官方认为 STATEMENT这个已经不再适合继续使用; 但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致; 推荐使用 row

server-id=1		# 设置server_id,一般建议设置为IP,或者再加一些数字,如端口号[在以前版本为server-id]

gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=true
skip_slave_start=1  # log-slave-updates/gtid-mode/enforce-gtid-consistency/report-port/report-host：用于启动GTID及满足附属的其它需求[其中启动GTID必须同时设置gtid-mode/enforce-gtid-consistency/]

master-info-repository=TABLE
relay-log-info-repository=TABLE

# master-info-repository/relay-log-info-repository都设置为TABLE,mysql.slave_master_info与 mysql.slave_relay_log_info 中,table都是innodb类型的,支持事务,比文件安全
# 默认值是FILE, 比如master info就保存在master.info文件中,relay log info保存在relay-log.info文件中,如果服务器意外关闭,正确的relay info 没有来得及更新到 relay-log.info文件,这样会造成数据丢失

sync-master-info=1	# 启用之后,使binlog在每N次binlog写入后与硬盘 同步

slave-parallel-workers=4	# 开启基于库的多线程复制.默认是0,不开启,最大并发数为1024个线程

binlog-do-db # 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可 http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

binlog-ignore-db	# 不需要复制的数据库苦命，如果复制多个数据库，重复设置这个选项即可,http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db

user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/mysql3306/log/mysqld.log
pid-file=/data/mysql/mysql3306/mysqld.pid

对于另一个主master2（3307）的配置文件 my.cnf 与上面差不多 ，只需要将相应的3306字眼修改为3307即可，记得修改server-id=2。

http://www.cnblogs.com/gomysql/p/3650645.html

1. 一个存放记录(row)的page，由page header、page trailer、page body组成。如下图:2

page的完整结构

page的结构详情参看如下：

from: http://forge.mysql.com/wiki/MySQL_Internals_InnoDB#InnoDB_Page_Structure High-Altitude Picture

The chart below shows the three parts of a physical record.

Name****Size Field Start Offsets

(F1) or (F2) bytes

Extra Bytes

6 bytes

Field Contents

depends on content

Legend: The letter ‘F’ stands for ‘Number Of Fields’.

The meaning of the parts is as follows:

• The FIELD START OFFSETS is a list of numbers containing the information “where a field starts”.
• The EXTRA BYTES is a fixed-size header.
• The FIELD CONTENTS contains the actual data.

更多查看： http://www.cnblogs.com/Arlen/articles/1768586.html

这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14″这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

MyISAM索引用的B+ tree来储存数据，MyISAM表的索引和数据是分开的，MyISAM索引的指针指向的是键值的地址(0XX开始之类的物理地址)，地址存储的是数据，如下图：

[]

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

[

[][3]

我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2 辅助索引使用主键作为”指针” 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个”指针”。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

参考： http://www.tuicool.com/articles/ZRN3qu

http://fireflyclub.org/?/article/37

http://www.cnblogs.com/yuyue2014/p/4738007.html

注意这篇文章并非死锁的，而是锁等待

到information_schema库下面，查看下面这个表：

innodb_trx         ## 当前运行的所有事务

innodb_locks       ## 当前出现的锁

innodb_lock_waits  ## 锁等待的对应关系

如果遇到死锁了，怎么解决呢？找到原始的锁ID，然后KILL掉一直持有的那个线程就可以了， 但是众多线程，可怎么找到引起死锁的线程ID呢？ MySQL 发展到现在，已经非常强大了，这个问题很好解决。 直接从数据字典连查找。

我们来演示下。

线程A，我们用来锁定某些记录，假设这个线程一直没提交，或者忘掉提交了。 那么就一直存在，但是数据里面显示的只是SLEEP状态。

1. mysql> set @@autocommit=0;

2. Query OK, 0 rows affected (0.00 sec)

3. mysql> use test;

4. Reading table information for completion oftableandcolumn names

5. You can turn off this feature to get a quicker startup with -A

6. Database changed

7. mysql> show tables;

8. +—————-+

9. | Tables_in_test |

10. +—————-+

11. | demo_test      |

12. | t3             |

13. +—————-+

14. 2 rowsinset (0.00 sec)

1. mysql> show status like ‘handler_read_%’;

2. +———————–+——-+

3. | Variable_name | Value |

4. +———————–+——-+

5. | Handler_read_first | 1 |

6. | Handler_read_key | 1 |

7. | Handler_read_last | 0 |

8. | Handler_read_next | 0 |

9. | Handler_read_prev | 0 |

10. | Handler_read_rnd | 0 |

11. | Handler_read_rnd_next | 21 |

12. +———————–+——-+

13. 7 rows in set (0.01 sec)

如上所示，mysql中关于read的计数器，有7个。他们的数值对于系统的状况的了解，对于系统的调优都十分重要。我们应该理解他们的含义。本文是自己的一些理解。 首先7个计数器，我们应该分为两部分： 1）对索引读的计数器：前面的5个都是对索引读情况的计数器， Handler_read_first：是指读索引的第一项（的次数）； Handler_read_key：是指读索引的某一项（的次数）； Handler_read_next：是指读索引的下一项（的次数）； Handler_read_last：是指读索引的最后第一项（的次数）； Handler_read_prev：是指读索引的前一项（的次数）； 5者应该有四种组合：

1. Handler_read_first 和 Handler_read_next 组合应该是索引覆盖扫描
2. Handler_read_key 基于索引取值
3. Handler_read_key 和 Handler_read_next 组合应该是索引范围扫描
4. Handler_read_last 和 Handler_read_prev 组合应该是索引范围扫描（orde by desc）

2）对数据文件的计数器：后面的2个都是对数据文件读情况的计数器， Handler_read_rnd: The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.