LeoKim's Blog

SQL优化的核心：

减少I/0

把随机IO转成顺序IO

分区唯一的场景：用来记日志

mysql hash分区 要求分区表达式必须是整数

range, list, hash

show variables like "%partition%";

mysql 5.1 开始支持分区

SELECT * FROM `PLUGINS` WHERE PLUGIN_NAME LIKE '%partition%'\G

mysql的分区比oracle差距很大 很弱

好处是从官方手册里直接翻译过来的，老师说不要用分区哈哈哈哈

特别不重要的地方才用分区，比如说日志

delete from tb where add_date<'';

数据量太大删除不掉

用分区drop很方便

alter table tb drop partition p1;

5.6种可以自动判断不用显示声明

innodb必须有主键

主键又必须在你的分区表达式里

range(year(add_datetime))

range columns(date(add_datetime))

partition p0 values less than ('2017-01-01'),

list就是一个集合（有限的集合）

子分区名字不能重复

group_concat长度有限制 可以修改

show global variables like "%group%";

入果只命中一个效率最高

 

mysql partition没有全局索引，所以索引都在自己的ipdate里放

一个分区一个索引没有完整性

mysql分区没有全局索引

分区能不能达到行级锁呢？

如果要有行级锁需要针对主键或者针对唯一索引

那么这个主键或者唯一索引就有一个条件必须在分区表达式里面

range 必须by primary key 或者 unique key

如果条件能去where的主键或者unique key那么能做行级锁

如果不能基本上是去锁到一个很大的区间

分区表包括mysql的表在select期间是不能去修改表的结构的

在执行代码的过程中是锁定的

锁定表的结构

这个是由存储引擎来处理不允许更改表结构

因为这个锁是存储引擎自己来处理的 所以每个存储引擎处理的方式也不同

myisam就是全局的锁等待

innodb通过不断重试去拿锁

每个分区都是一个存储引擎的实例 他们都有自己的ibdata

其实对锁的粒度还是挺粗的 动不动就是一个range 锁

不能对临时表进行分区 上面ppt日志写错了

大部分都是时间函数，Mysql就是提倡对时间类的数据进行分期的

可用，但不可以委以重任

不要再更改sql model

master挂了

现在要提升slave为master 要做一些同步对比

以下要熟记，是最基础的

第一： 要确认自已是同步完成的 

master_log_file == relay_master_log_file 和read_master_log_pos =exec_master_log_pos

第二： 要确认slave1和slave2是相等的

slave1.master_log_file == slave2.master_log_file || slave1.master_log_pos == slave2.master_log_pos

第三： 让slave2 change 到slave1上

slave1(在即将成为master的slave上): 上执行一个show master status;

如果是GTID的直接change过去就OK了

show processlist查看master上的slave（和此文无关）

slave2: 执行一下show slave status;

slave2 : stop slave; change master ; start slave; show slave status;

reset slave; 清掉本地的relay log , 把同步调整到起始位置

字典表

比如说 地理信息

多机房同步

选择proxy:

这个proxy有没有维护了，自已能不能维护

如果主库挂掉了

A->B A->C

A挂了只有BC了

要检查B/C是否同步完成并且检查谁更靠前

比较是否同步完成

这个时候的状态是io_thread:NO

if(master_log_file == reley_mast_log_file) and (read_master_log_post == exec_master_log_pos)

只有当这2个条件成立 我们才认为是同步完成的

b.Master_Log_File , c.Master_Log_File比较谁更大 就是同步的更完全

如果Master_Log_File都一样，那么比较 Exec_Master_Log_Pos

GTID会自动补上来 上面是传统模式

主从数据一致性怎么去校验（怎么确认主从是同步的？）

运行一段时间之后有一些不规范的操作搞成同步不一致问题

大的库分段检测 分析数据变换情况

pt-table-checksum

利用语句级复制，在主从库上对表分段进行校验，如果值不一样，说明就不同步了

pt-table-sync

利用语句复制，所有的修复都是在主库上完成的

https://www.percona.com/   Percona toolkit 

下载最新版本

用yum安装 方便管理 把yum源弄进来

pt 大多是基于perl编写的

有部分的shell

同一组内的数据必须同一个端口

每组的端口号是全局唯一的

总结：

row 格式的binlog复制，有些情况下会比statement的快

原因是：sql_thread应用日志时,在有主建的情况，会利用主键更新数据

reset master 清掉Executed_Gtid_Set

gtid_purged 配置在slave上

slave只会读取gtid_purged这个GUID之后的数据之前的就不要了

MySQL 5.6.22的参数，总共432个

change maseter to 

show global variables;

以下所有参数都是mysqld里的

复制中的一些重点参数(最基础的)

log-bin（binlog路径）
server-id（服务标识）
log-bin-index
binlog_format（binlog格式）
binlog_cache_size
max_binlog_size
sync_binlog
expire_logs_days
log_bin_trust_function_creators

server-uuid 　以后有可能代替server-id(标识)

gtid从库没有配置service id也是也是可以的 但是主库一定要配置

Master的参数：

============================================

server-id
  唯一区别ID，同一个集群内不可重复
  可动态修改

binlog_format
  binlog日志格式：statement、row、mixed三种
  可动态修改 建议修改session的不修改全局的 推荐用row

binlog_cache_size
  binlog写入buffer
  可动态修改

max_binlog_size
  限制单个binlog大小
  可动态修改 通常配置1M

max_binlog_size
  限制单个binlog大小
  可动态修改 一般建议设置成200M/500M 根据日志产生量 默认是1G

  5min左右刷一个或者10min左右刷一个

  binlog刷新过程数据是提交不了的 所以让binlog刷新慢一点 写数据不会被卡住

sync_binlog = n
  多少个SQL之后，调用fdatasync()函数刷新binlog到disk

  默认是0，在调优中很重要

  多少个sql刷一次binlog的cache，同步到磁盘

  比如说现在写成0就是由系统来刷新binlog cache

  系统觉得binlog大了就去刷新

  对一些交易型的，账单或者扣钱的东西 建议设置成1

  让没个sql都刷新一下binlog 让binlog更安全一点

  当然设置成1了 性能也是最差的，设置成0的时候性能是最好的

  这个是对性能影响很严重的参数

  tokodb不开binlog写入可以达到5,6w

  开启之后写入只能达到2W的写入

expire_logs_days =n
  n天后自动删除binlog
  可动态修改 默认指定为7

  如果不开启就会造成binlog把磁盘占满了

  应用卡住了 也不知道怎么回事

log_bin_trust_function_creators

  默认是0 要用的话改成1就行了

  允许在存储过程调用的时候用到，如果没有涉及存储过程是用不到

log_warnings

  参数级别 默认是2  默认是0

  error log看到Abort connection拒绝连接请求

  如果不想看到这些就把值设置成1

binlog_error_action 

binlog_error_action=ABORT_SERVER

当空间满了写不进去了 会在应用层直接报错 不会直接卡在那里

5.6之前只能再error log里看到

binlog_row_image=[full|minimal|noblog]

也是5.6的

日志格式为row的时候日志里会记录修改之前的和修改之后的记录所以日志很大

利用这个参数之后只保留写后的记录

update tb set c2=xxx where id=xxx;

[minimal]

update tb 
set 
@2=xxx,
where 
@1=xxx,
@2=xxx;

没有变更到的就不记录了

Binlog_rows_query_log_events=[1|0]

默认关闭，（日志格式是row 的时候）开启参数之后会生成query event

会记录用户原始sql

gtid_mode=on

开启的时候必须设置这些参数

log-bin ,

log-slave-updates(从库上也会记录binlog 可以用来备份或者机连备份) ,  

enforce-gtid-consistency（一致性，控制哪些语句能安全的记录到binlog里）

Executed_Gtid_set 执行过的gtid集合

set gtid_next="uuid:NO."; 指定下一个事物是哪个事物

set global gtid_purged='uuid:NO.';

进行同步的时候忽略这个事件之前的,告诉主库包括NO.之前的事物都不要给我了

reset master;

才能清空，清空之后才能设置

Gtid_executed

从库上到底执行过哪些gtid操作

uuid:1-100:300-10000:20001-NNNNN

发现有空洞

有可能做了互为主从造成了空洞

如果是传统的A->B的应该是不会有空洞的

uuid:1-NNNNN

或者是set global gtid_purged开始的 到 NNNNN

和

show master status\G  

show slave status\G

一样

这个参数也需要reset master

slave的参数

============================================

server-id 

推荐配置service-id可能从库以后要挂从库或者转为主库

relay-log

从本地拿到的binlog会存到relay-log里

relay-log-index

read-only 对非super 用户起作用

set global read-only = 0|1

slave其他参数

log-slow-slave-statements
log_slave_updates
max_relay_log_size
relay-log-info-file
relay_log_purge
relay_log_recovery 
replicate-same-server-id 
skip-slave-start
slave_load_tmpdir
slave_transaction_retries

slave_parallel_workers

log-slow-slave-statements

sql_thread 执行sql超过long_query_time 会记到慢日志中

默认没开启

max_relay_log_size

设置一下relay-log的大小

slave和master不会形成长连接 每次链接就会生成一个relay log

relay-log-info-file =relay.info

relay crash recover

[root@node21 data]# cat relay-log.info 
7
./mysql-relay-bin.000008
4
mybinlog.000005
191
0
0
1
1

执行到005 位置是191

从库忽然挂掉 又起来了同步接不上

要把这个参数打开之后会从binlog005 191这个位置向主库重新请求

在gtid里做了一个变相实现

5.6.21叫 simplified_binlog_gtid_recovery 

5.6.23改名了叫binlog_gtid_recovery_simpliefied

可以从gtid里拿到已经同步到哪个gtid了只要这个gtid之后的数据就行了

5.6.21只前gtid重启之后没有这个重新请求的过程中没有crash recover处理机制

是在5.6.21之后引入进来的

而且这个参数默认是没有开启的

这个功能很好 之前从库挂掉都要去修复

replicate-same-server-id

默认没开启 自己复制自己的server id基本上不用 

skip-slave-start

建议在配置文件里要有

slave启动之后不要默认开启同步

slave_load_tmpdir

指向到tmpdir下 一般不用设置

slave_transaction_retries

sql语句在执行中上层mysql如果发生堵住的情况把某个数据区间锁住了

sql thread就会等待锁的释放 机会有一个重试的过程

当重试超过次数之后 就会报错

start slave sql_thread;

5.6引入  并行复制 库级别的复制

slave_parallel_workers 最大1024

默认是关闭的

如果sql_thread断了但是io_thread还是yes

同步断掉之后本地的relay_log 会记录很多 造成磁盘被写满

所以引入了relay_log_space_limit 限定relay log占用磁盘大小

超过了之后io_thread  会等待relay-log释放空间

relay log执行完会被删掉

SHOW SLAVE HOSTS

slave上需要配置这3个参数

report_password
report_port
report_user

实际生产中很少去配 库都是动态切换的 配置上意义不大

sync_master_info
sync_relay_info

5.5之后 可以吧master info 和 relay info存到数据库里

5.6.17之前这2个参数如果配置成1或者过小会有内存溢出错误

保持默认不变就行了

sync_relay_log   ＝  sync_binlog

========================================

复制过滤规则

1. 库级别的

2. 表级别

支持： 精确和通配符？

正向逻辑和反向逻辑

分binlog 和sql_thread两个部分

一部分可以控制记录binlog的时候记录什么

另一部分可以在sql thread里面控制执行什么

master 部分

只记录某个db的binlog

replicate-do-db = "leokim"

create database jl;

就不会记录

use jl;

insert into leokim.tb(col1) values(1);

语句格式下是记录不上去的

在行格式是能让记录的

replication-ignore-db="mysql"忽略mysql库

想忽略某个表

replicate-ignore-table=leokim.quota

复制中改库

a_db -> b_db

在slave上才能配置

replicate-rewrite-db='a_db->b_db';

把某个库映射到另一个库上

从库上的

binlog

replicate-wild-do-table
replicate-wild-ignore-table

replicate-wild-do-table="leokim.ds_%";（不是这个前缀的就不计）

mysql同步复制里有个坑

slave_net_timeout

从库和主库多少时间断开才会说连不上主库 — 默认3600S

忽然闪断2min 数据不同步 io_thread 和 sql_thread 都是ok

推荐配置成10

slave_net_timeout=10

slave_skip_errors

slive中复制，忽略那些错误

常见的：

1062 主建冲突

1032 找不到记录

一般建议不允许忽略任何错误

除非从库来不及修又要用

就先skip掉晚上用工具修复

sql_slave_skip_counter
忽略多少个复制事件，遇到个别错误（主键冲突、记录不存在等）时，可以忽略这些事件，继续复制进程
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n;
START SLAVE;
SHOW SLAVE STATUS\G
一般一次只忽略一个事件，除非很肯定，否则不要设置大于1

简单总结：

推荐gtid 管理方便不用找偏移量位置

 

server-id 推荐ip最后一位

传统配置：

#replication 
log-bin=/path/mysql_xxxx/logs/mysql-bin
server-id=[ip]port
log-bin-index=mysql-bin.index
binlog_format=row
binlog_cache_size=1M
max_binlog_size=200M
sync_binlog=0
expire_logs_days=7
log_bin_trust_function_creators=1
#master binlog filter

#slave
relay-log=relay-bin
relay-log-recovery=1

log-slow-slave-statements
log_slave_updates
slave_net_timeout=10
master-retry-count= 86400

我在主库清除了所有binlog然后在leo表里删除了几条记录又重新插入了一些

从库操作

change master to master_host='192.168.61.131',master_user='repl',master_password='repl4slave', master_auto_position=1;

可以看到1-3的都copy过来了

示例情况

我在从库插入了一条记录 

在主也插入同样记录的时候，从库同步就会报错

这个时候处理方式是 把从库上的该条重复记录删掉

然后执行

start slave sql_thread;

就继续同步过来了

同样现在从库删除一条记录

然后再去主库删除同样的记录

尽然没有报错 哈哈

update也不会报错 

主库上没有id为2的记录 从库上有id为2的记录

主库上执行delete id 2 从库上的会被干掉

binlog要换成row格式

对于gtid必须要用row格式要么会有很多错误都不爆

上面那些delete update应该出错的就会出现 slave就会起不起来了

跳过错误

set gtid_next=b9c8ef95-27ea-11e7-9c7b-000c29a8f010:10

stop slave；

begin;commit;

跳过错误

同步就继续了数据就同步过来了

初始化master和slave数据

删除所有数据

创建从库账号

前面的都是初始化的log可以不要

从库也是相同配置

change master to master_host='192.168.61.131',master_user='repl', master_password='repl4slave', master_log_file='mybinlog.000001', master_log_pos=120;

warning就是不提倡在命令行里直接写密码 哈哈

这里有个报错 说是因为server id要用不一样的 我去修改一下

还是有error

妈蛋···绑定master的mybinerlog写成mybilog了·····

后来试了一下还是有报错

最后发现是定义master的时候把密码写错了

成功了。

主库上创建leokim的库

从库上也生成了leokim这个库

主库上创建表

从库上也能看到表和数据

MYSQL复制的几种模式

Mysql中的复制可以是基于语句（Statement Level）的和基于行的（RowLevael）。
从 MySQL 5.1.12 开始，可以用以下三种模式来实现：
— 基于SQL语句的复制(statement-based replication, SBR)，
— 基于行的复制(row-based replication, RBR)，
— 混合模式复制(mixed-based replication, MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

在运行时可以动态的改变binlog的格式，除了以下几种情况：
. 存储过程或者触发器中间
. 启用了NDB(Mysql cluster 主要采取的存储引擎)
. 当前会话使用 RBR 模式，并且已打开了临时表

如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成RBR 模式。
. 当DML语句更新一个NDB(Mysql cluster 主要采取的存储引擎)表时
. 当函数中包含 UUID() 时
. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
. 行任何 INSERT DELAYED 语句时
. 用 UDF (User Definition Function)时
. 视图中必须要求使用 RBR 时，例如创建视图是使用了UUID() 函数

设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上，再加一个参数：
binlog_format="STATEMENT" 
#binlog_format="ROW"
#binlog_format="MIXED"
当然了，也可以在运行时动态修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

两种模式各自的优缺点：

SBR 的优点：

1．技术比较成熟

2. binlog文件较小，log文件可读

3. binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况

4. binlog可以用于实时的还原，而不仅仅用于复制

5. 主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

1. 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

2. 调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

3. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句，对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响。以为需要记录上下文信息所以存储函数(不是存储过程，mysql内部存储的函数)在被调用的同时也会执行一次 NOW() 函数。

4. 确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源
 

RBR 的优点：

1. 任何情况都可以被复制，这对复制来说是最安全可靠的

2. 如果从服务器上的表如果有主键的话，复制就会快了很多

3. 从服务器上采用多线程来执行复制成为可能

4.  不会出现某些特定情况下的存储过程，或function，以及trigger 的调用和触发无法被正确复制的问题。

RBR 的缺点：

1. 相比SBR的binlog 大了很多，还原的时候可能比较慢

2. 复杂的回滚时 binlog 中会包含大量的数据

3  因为RBR是基于行级的，所以如果有alter table 之类的DDL操作产生的数据量非常大。(GRANT，REVOKE，SET PASSWORD等语句建议使用SBR模式记录).

4.  当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生
5.  采取RBR模式记录的话，log是不可读的。（经过加密）

注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。

MySQL中可以有二类用于生成唯一值性质的工具：UUID()函数和自增序列，那么二者有何区别呢？我们就此对比下各自的特性及异同点：

    1.都可以实现生成唯一值的功能；

    2.UUID是可以生成时间、空间上都独一无二的值；自增序列只能生成基于表内的唯一值，且需要搭配使其为唯一的主键或唯一索引；

    3.实现方式不一样，UUID是随机+规则组合而成的，而自增序列是控制一个值逐步增长的；

    4.UUID产生的是字符串类型值，固定长度为：36个字符，而自增序列产生的是整数类型值，长度由字段定义属性决定；

接下来，详细讲解下UUID()函数产生的值：

root@localhost : (none) 06:09:40> SELECT UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID())\G

从上面的执行结果部分的信息看：

1.同一个SQL语句中，多处调用UUID()函数得到的值不相同；

2.得到的随机值由5个部分组成，且分隔符位为：中划线；

3.多次调用或执行得到的后2组值相同，若把mysqld服务器关闭，重新启动之后，会发现第四组的组与未重启前的值发生变化，然后一直不变化，只要重新启动mysqld服务就会发生变化。另外，对于同一台机器，第五组值始终不会发生变化；

4.字符个数为：36，占字节数为：36（注：系统默认字符集编码：utf8）；

针对UUID产生的值组成部分，作如下解说：

1. 前三组值是时间戳换算过来的；

2. 第四组值是暂时性保持时间戳的唯一性。例如，使用夏令时；

3. 第五组值是一个IEE 802的节点标识值，它是空间上唯一的。若后者不可用，则用一个随机数字替换。假如主机没有网卡，或者我们不知道如何在某系统下获得机器地址，则空间唯一性就不能得到保证，即使这样，出现重复值的机率还是非常小的。

UUID函数对复制的支持：

UUID函数属于不确定性函数，为此不支持MySQL 复制的STATEMENT模式，但是支持MIXED、ROW二种模式，大家可以设置2组测试模式，以5.1.系列版本为例。

测试基于命令行模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = STATEMENT

 

测试基于命令行/混合模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = MIXED  OR ROW

 

在主服务器上执行同一个SQL语句：

INSERT INTO  test_uuid(username) VALUES(UUID());

然后再比对主从服务器上表中存储的值，会发现基于命令行模式的：主从不一致，基于行/混合模式的：主从数据时一致；

 

建议：在复制模式下，需要用到UUID()函数，则一定要使用基于行/混合模式复制方式。

名词解释：

对于输入参数相同，且同一时间执行或一个SQL中多处调用，而得到不同值得函数，我们就称其为：不确定性函数。

备注：

在MySQL 5.1.*及更高版本有一个变种的UUID()函数，名称：UUID_SHORT()，生成一个64位无符号的整数，例如：

root@localhost : (none) 02:46:42> SELECT UUID_SHORT()\G

*************************** 1. row ***************************

UUID_SHORT(): 6218676250261585921

1 row in set (0.00 sec)

 

UUID()函数产生的值，并不适合作为InnoDB引擎表的主键，至于详细的原因，请阅读文章InnoDB引擎表的主键选型。

在mysql中，可以使用uuid 来生成主键，但是用mysql的uuid()函数 ，生成的uuid是36位的，其中包含32个字符以及4个分隔符（-），往往这个分隔符对我们来说是没有用的，可以使用mysql自带的replace函数去掉分隔符

replace(uuid(),'-','')   —->将uuid()中的‘-’，去掉，即替换成空串；

此外

upper(replace(uuid(),'-',''))用于将字符转换为大写

MySQL采用开放可插入式存储引擎架构，提供类似电源插线板的功能，其后接入的存储引擎就类似电器设备，而我们大家常用的存储以MyISAM和InnoDB为主，早期大家主要使用MyISAM引擎支持业务，随MySQL支持业务范围越来越广，存储的数据对企业越来越重要，尤其PC服务器支持的最大内存越来越大，内存的价格也越来越便宜，逐渐采用InnoDB引擎为主.二种风格迥异的存储引擎，各自内部存储算法和数据操纵实现等都竞相不同，另外InnoDB引擎与其他商业数据库产品存储引擎也不太相同，为此我们必须根据使用的存储引擎特点，设计合理的数据存储结构和数据操纵方式。本文将围绕InnoDB存储引擎的主键设计而展开，告诉大家怎样设计表的主键才是合理的做法。

讨论InnoDB引擎表的主键选型的要求之前，我们大家先简单温习下InnoDB引擎表的元数据和索引数据存储结构特点。

InnoDB引擎表的数据和索引都是存储在同一个文件中，InnoDB引擎的页大小默认为16K，且页空间使用率为15/16，为此每页能存储的数据量是有限的。主键和数据的关系是数据存储在簇索引的叶子节点中，接下来我们看下主键和数据的组织结构关系，如图1-1：

对于非簇索引，又是如何存储的呢？通过翻译官方文章，得知：

每个非簇索引的叶子节点存储的数据都包含簇索引的值，然后通过簇索引的值，可以查找到对应的元数据，我们继续看一下非簇索引的存储结构，以及与簇索引之间的关系，如图1-2：

紧接着，我们来理清楚InnoDB引擎表的簇索引为哪三类：

1.  主键也即是我们文章说的簇索引；

2.  若表中无主键，但是存在唯一索引，且字段定义为非空，则作为簇索引；

3.  表无主键，也无非空唯一索引，则内部默认隐含性创建一个长度为6个字节的字段作为簇索引字段；

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

通过上述探讨和分析，弄清楚了数据存储页的空间值和利用率、簇索引和非簇索引的存储结构，以及簇索引和元数据、簇索引和非簇索引的存储关系，以及簇索引为哪三类，那么我们建议大家一定要为InnoDB引擎表创建一个主键，没必要搞一个唯一性索引且字段定义属性非空，而不创建主键的表结构，我们再分析优秀主键具备的素质：值域范围够用且存储长度越短、值的唯一性和易比较性、数值的有序性增加，三个素质的各自优点，单独详细分析。

主键的值域够用且长度越短，将产生三个方面的优势

(1).     相同数据行数的表，数据容量越小，占用磁盘空间越少，为此可以节约物理或逻辑IO和减少内存占用；

(2).     普通索引的长度也将更短，可以减少通过普通索引搜索数据的物理或逻辑IO；

(3).     减少索引数据存储的页块裂变，从而提高页的利用率，以及减少物理IO；

主键的唯一性

(1).     主键的值要求必须唯一，且非空；

(2).     主键值每次插入或修改，都必须判断是否有相同值，为此减少索引值需要比对的长度，可以提高性能，或者间接地让其转换成比对数值大小的方式，提高其比对判断的效率；

主键值插入的有序性，将产生二个方面的优势

(1).     主键存储的块与块之间是有序的，然后块内有也是有序的，主键值的有序插入，可以减少块内的排序，节约磁盘物理IO；

(2).     主键值的有序性，可以提高数据舒顺序取速度，提高服务器的吞吐量，也可以节约物理IO；

上述讨论的数据存储结构及关系，以及主键字段要求的素质等理论知识之外，我们还需要考虑实际生产环境的业务、架构等综合因素，我们实际生产环境可能会使用四类属性作为主键：

(1).     自增序列；

(2).     UUID()函数生成的随机值；

(3).     用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4).     基于一套机制生成类似自增的值，比如序列生成器；

那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：

(1).     自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

(2).     UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

(3).     使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4).     序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

(5).     双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；

通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；

【结束】

使用什么类型的字段属性作为主键，最关键核心的要考虑存储引擎：如何存储元数据、如何检索元数据、如何维护其内部的索引组织结构，以及我们要实现的业务是什么，最后共同决定我们，如何设计一张用于存储数据的表，以及决定操纵数据的SQL语句如何编写，再结合业务特点就决定了我们的索引如何创建，建议大家多关注InnoDB引擎内部实现原理和机制，可以阅读官方提供的一个文档InnoDB引擎内部实现，以及多分析和关注业务特点。