MySQL杂谈

2019年5月写于博客园,现迁移至此

前言

最近翻《高性能Mysql》和 林晓斌的《MySQL实战45讲》，启发良多，故此做一下笔记。

数据类型

1.整数类型

tinyint,smallint,mediunint,int,bigint,分别使用8,16,24,32,64位空间,范围是-2^(n-1)到2(n-1)-1,可选unsigned,表示不允许负值,可以使正数上限提升一倍。 两者具有相同的存储空间，相同性能。另外int（1）和int（10），对存储和计算来说是相同。

2.实数类型

浮点型float,double精确类型decimal可以指定最大位数,这会影响存储空间,decimal最大支持65个数字,例如decimal(18,9),表示9位整数和9位小数,共占用9字节,4字节存9个数字,小数点占1字节.。存储财务数据,建议用bigint代替decimal,可以避免decimal精确计算代价高的问题。

3.字符串类型

varchar类型用于存储可变长字符串,需要额外一个字节或者两个字节存储长度,大多数情况可以节省存储空间.update时可能需要更多的空间,而数据页不够空间时,就需要分裂页,总得来说可能要做额外的工作.

char类型适合存储很短的字符串或者所有值接近一个长度,例如密码的md5值.经常变更的字段也比varchar更好,不容易产生碎片.另外存储一个字节的数据时,char只需1个字节空间,而varchar要2个字节.

varchar(5)和varchar(100),存储相同字符用相同的空间,但是更长的列在执行的时候会消耗更多的内存,例如用内存临时表排序或者操作时。

4.日期

datetime 可以保存1001到9999年的值,精度为秒,使用8字节存储空间封装到格式为YYYYMMDDHHMMSS的整数中.

timetamp 可以保存1970年到2038年,使用4字节的存储空间,可以使用from_unixtime转化为日期,显示的日期和时区有关.

5.位数据

bit类型,Innodb引擎会使用一个最小整数类型来存放,并不能节省空间,平时能不用就不用。

小结一下

1.更小的数据类型更好,占用更少的磁盘,内存和cpu缓存；

2.简单类型更好,整形比字符操作代价更低,用date,datetime而不是字符串存储日期时间,整形储存ip；

3.尽量避免null,null列作为索引需要更多的存储空间,更复杂的索引统计和值比较；

4.字符类型按实际需要长度设置；

5.表避免建立非常多的列；

索引

说到sql性能优化,很多人马上就会想到索引,有慢sql出现时,一般都会看有没有走到索引,如果没有,那么建一个呗.果然马上更快了,那是为什么呢,下面来分析一下.

先来建一个表和初始化一些数据,如

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(1)  DEFAULT NULL,
  `idcard` varchar(18) DEFAULT NULL,
  `brithday` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_idcard` (`idcard`),
  KEY `index` (`city`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
 

INSERT INTO `staff` VALUES (1,'北京', '李一', 21, '女', '443332224440711', '2009-5-20');
INSERT INTO `staff` VALUES (2, '北京', '李二', 22, '男', '443332224440712', '2019-5-7');
INSERT INTO `staff` VALUES (3,'深圳',  '李三', 33, '女', '443332224440713', '2019-5-6');
INSERT INTO `staff` VALUES (4,'广州', '王一', 21, '男', '443332224440714', '2019-4-3');
INSERT INTO `staff` VALUES (6, '北京', '王三', 36, '男', '443332224440716', '2019-3-27');
INSERT INTO `staff` VALUES (7, '北京', '张一', 47, '女', '443332224440717', '2018-2-14');
INSERT INTO `staff` VALUES (8, '上海','张二', 43, '男', '443332224440718', '2019-2-12');
INSERT INTO `staff` VALUES (9, '上海', '张三', 22, '男', '443332224440719', '2019-1-23');
INSERT INTO `staff` VALUES (11,'广州', '赵二', 55, '女', '443332224440721', '2018-9-4');
INSERT INTO `staff` VALUES (12, '广州', '赵三', 33, '女', '443332224440722', '2018-9-5');

InnoDB存储引擎是使用了B+树索引模型,所有数据都是存在在B+树中,每一个索引对应一颗B+树,我在数据表中建了主键索引(id)和联合索引(name,age)，索引模型如下所示:

为什么用了索引，查询会变快。

从图中可以看到主键索引(id)叶子节点存储的是id字段和整行数据,而联合索引(city,name)存储的是city，name字段和主键的值。刚才说到为什么用索引会快，先来看看一条语句

SELECT * from staff where id=9;

如果id没有索引，InnoDB引擎会做全表扫描，而InnoDB是按页存放数据，在 InnoDB 中，每个数据页的大小默认是 16KB。所以按图所示会读取4个Page，才能把符合id=9的数据读取出来。

现在id有索引，InnoDB引擎就可以按通过搜索索引树快速定位数据，因为根节点在内存中，所以只需要读取2个Page就可以把符合id=9的数据读取出来。

磁盘I/O次数是影响查询快慢重要因素,所以这就是用索引为什么会快的原因。

什么叫回表

下面再来看看一条语句

SELECT * from staff where city='上海';

这条语句会用到联合索引(city,name)，它的执行流程是这样的

1.在联合索引上找到（上海，张三）这条记录，取得id=9；

2.再去主键索引上找到id=9的数据，取得row9整行数据

3.在联合索引上找到（上海，张二）这条记录，取得id=8；

4.再去主键索引上找到id=8的数据，取得row8整行数据；

5.在联合索引上找到（北京，张一）这条记录，不符合条件，结束；

这里可以看到非主键索引的查找过程，会多一步到主键索引上搜索的操作，这个操作称为回表。

那么我们可以从这里学到什么，数据量小的表可以不用建索引，因为如果用索引会多一步回表的操作，可能还不够直接全表扫描快，另外还多了维护索引的额外空间和操作。

覆盖索引

刚才说到多了一步回表会带来额外消耗，大多数情况下我们都不会用主键作为查询条件，那么好像回表操作无可避免。如果再细心观察一下上面联合索引图例，我们发现(city,name)已经在索引树上，所以看看下面两条语句

SELECT city,name from staff where city='上海';
SELECT * from staff where city='上海';

语句1执行后，在联合索引（city，name）上可以拿到city,name两个字段的信息，不需要回表了。

语句2执行后，在联合索引（city，name）上可以拿不到所需要的信息，所以要回到主键树上拿数据。

如果用explian解析，也会看到区别，语句1会在Extra栏中多了Using index，表示用了覆盖索引。

像语句1这种直接可以在索引上就拿到所有信息的情况，也就是说索引覆盖了要查询的字段，我们称这种索引为覆盖索引。灵活使用覆盖索引，是提升性能的重要手段。

要不要用业务字段做主键

前面说到用非主键索引查询会产生回表操作，那么如果我们用业务字段作为主键，那不就消除了回表操作了吗。是的，但是也是有代价的。

首先是需要消耗更多储存空间，从上面的示意图中看到，每一个非主键索引都会存储主键的值，所以主键值越大，非主键索引所以占用存储越大。

其次会加大索引维护的代价，业务主键一般很难保证有序递增的，举个例子，如果现在要插入id为5的数据，B+索引树要保证有序性，所以需要在4和6之间的数据页中加入，如果这种时候Page 2刚好满了，那只能分裂成两个页，变成45在一页中，67在另一页中，造成数据页空间降低了。所以一般主键需要是自增主键，这样不产生页分裂，提供空间利用率。

但是用业务主键能提高速度，所以我们可以在表索引少的情况下，用有序递增的业务字段作为主键。

最左前缀原则

上面几个例子都是用city作为查询条件，但是我根本没有建（city）的索引，为什么会用到索引？再来看看上面的图，会发现（city，name）索引树的叶子是按照city字段排序的，这个排序和（city）列的排序一样，所以可以把（city，name）当成（city）用。

这个最左前缀原则可以继续引申为最左N个字段，如（city，name，age）可以当成（city，name）用；还可以引申为字符串索引的最左N的字符，如

SELECT * from staff where city like '上%';

所以，我们看到联合索引的字段顺序很重要，可以节省不必要的索引。

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。看看这个语句

SELECT * from staff where city like '上%' and name='张二';

正常情况下，会在联合索引上拿到两条符合’上%'的记录再去主键索引上查找真正的数据,在 MySQL 5.6后,会在联合索引上就直接将不符合name='张二’过滤掉，节省回表次数。

利用索引排序

我们经常用到order by语法来排序，order by代价是比较高的，首先mysql会开辟sort_buffer_size定义大小的内存空间存放查询记录，如果不够，还会利用磁盘临时文件辅助排序，这样代价就更高了。

其实我们用order by就是为了取到有序的集合，如果集合本来就有序的，那么mysql就不用再做那么多操作了，如

SELECT city,name,sex from staff where city='上海' ORDER BY name;

上面的语句，从上图看到在索引（city，name）上，city=‘上海’记录上，name是有序，所以mysql直接取数就行了，不用再额外排序。

如果用EXPLAIN去解析这条sql，会发现在Extra栏上为空；如果删了索引，再EXPLAIN会在Extra看到Using where; Using filesort，表示执行了排序。

这里我们学到什么，在建索引的时候不只是要考虑到查询条件，还要考虑到排序条件，尽量要用到索引的排序。另外，如果在做jion操作时，只有order by的字段全部为第一个表的字段才能用索引排序。

普通索引和唯一索引选哪个

普通索引和唯一索引可以在两个方面来对比。

一是查询，前面说过InnoDB都是整页存取,所以在查询的时候,普通和唯一索引都是会定位到最下面的数据页里,例如id=8,那么就会找到Page 3的数据页,如果是唯一索引的话,马上就返回了,如果是普通索引的话,会判断下一个记录是否满足id=8,直到不满足为止。这种情况下，普通索引比唯一索引多的代价是一次寻址和一次计算，另外如果刚好下个记录在另外的数据页，还要多一次读取数据页。

二是更新，普通索引的更新可以直接写在内存中的 change buffer，而 change buffer会定时刷到磁盘里。而唯一索引因为要判断唯一性，所以要将整个数据页读到内存里面再去判断是否唯一，这就增加了随机IO访问，随机IO是数据库成本最高的操作之一。

两者互有优劣，但是随机IO的代价更高一点，所以在业务允许且新增数据非常频繁的情况，可以用普通索引;如果要保证业务唯一性,那显然要选择唯一索引的。

什么时候用前缀索引

索引越大占用存储越多，相同的数据页能放下的key就越少，导致搜索一样数量索引的时候，读取的数据页变多，这增加了IO访问的成本。例如身份证号这么长的字段作为索引的话，占用空间就大了，所以可以用前缀索引来减少存储，例如前8位字符作为前缀索引，但是身份证前8位的区分度不高，这样的前缀索引加了和没加没什么区别。不过我们可以倒序身份证作为前缀索引，这样区分度就高了。另外，邮箱字段倒是很适合做前缀索引。话说，用了前缀索引就做不成索引覆盖了，因为索引树中只储存字段的。

索引为什么有时候会不生效

选择索引是Mysql优化器的工作，而优化器的通常是用统计信息去判断成本。其实统计信息主要就是索引的区分度(准确来说是索引基数)，索引中不同的值越多，区分度越高。

再说说有时候索引为什么不生效，那是因为优化器认为它的区分度不高，但是实际上索引大多数区分度都挺高的，是优化器傻了吗，那就要来看看优化器怎么拿到区分度了，最准确的是做法是拿到统计索引的所有值，但这样代价太高了，所以InnoDB选择“采样统计”，采样统计的时候，InnoDB会选择N个数据页，统计平均值。下面结合上面的图例模拟采用统计出现不准的场景。假如，InnoDB在City的索引上采样到Page 7的数据页，统计到里面有3行数据都是广州，所有得到区分度是1/3=0.33，而事实上city索引的区分度可以用下面语句

SELECT COUNT(DISTINCT city)/COUNT(*) from staff;

得到的实际区分度是0.4，当然这只是模拟一下，实际上优化器不会这么简单的，但也说明了采样统计和全量统计的区别，极端情况下优化器拿到错误的区分度。你再想想，如果区分度非常低的话，用索引扫描和全表扫描两种要扫描的行数都差不多，既然索引扫描还有回表的成本，那么干脆就全表扫描就好了，所以，优化器就不用索引了。

话说，如果确定是统计信息导致索引不生效，也可以直接focus（index）强制用索引或者在统计一次信息，如analyze table t；

除了统计信息导致优化器不选择索引外，还有的情况就是导致优化器用不上索引，例如函数计算，如

SELECT * from staff where SUBSTR(city,2,1)='海' ;

上面语句用了截取字符，实际上是破坏了索引值的有序性，既然破坏了值的有序性，自然用不上索引树搜索了，只能走全表扫描。另外要注意的是，就算不破坏有序性只要做了计算就用不上索引，如

SELECT * from staff where city+'1'='上海1' 

还有的就是，隐式转换也会导致函数计算，数字和字符比较时，会将字符转为数字，如

SELECT * from staff where idcard=123;
SELECT * from staff where  CAST(idcard AS signed int)=123;

第一条sql实际上会像第二条sql那样执行，所以不能用索引树搜索了。

还有的是隐式编码转换，例如utf8mb4和utf8比较，会将utf8转成utf8mb4再比较。

杂谈

关于count()

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

mysql对count(*)有专门优化,效果最高。

什么是可重复读

mysql的默认事务隔离级别是可重复读，如下两个事务

事务A：  SELECT age from staff where id=1 ;    result：age=21
事务B：  UPDATE staff set age=age+1 where id=1;    
事务B：  commit；
事务A：  SELECT age from staff where id=1 ;    result：age=21

在事务A中，无论执行多少次，结果都是李一，即使有事务B提交了更改。从这里可以看到可重复读的定义是在同一个事务中多次读取同样的记录得到的结果集是一致的。

可重复读通过多版本并发控制(MVVC)机制来实现的。InnoDB的MVVC机制是这样实现的,每行数据都会有两个隐藏列，分别存储当前版本号，删除版本号，每个事务开始都会申请一个自递增的系统版本号作为transaction id。

select InnoDB只会查找满足两个条件的数据,分别为1.行当前版本号小于transaction id 2.删除列的版本号大于transaction id或者为空
insert InnoDB为新增的行存储transaction id作为当前版本号
delete InnoDB为删除的行存储transaction id作为删除版本号
update InnoDB会新增一条行数据,存储transaction id作为当前版本号,同时更新旧行数据的删除版本号为transaction id
在上面的例子中,事务B的transaction id比事务A的大,所以事务A一般读不到事务B修改后的行数据。

上面说了“一般”的字眼，其实还有其他手段读取到事务B修改后的行数据，那就加锁，如下

SELECT age   from staff where id=1 lock in share MODE;   加共享锁  result：age=22
SELECT age   from staff where id=1 for UPDATE;           加排他锁   result：age=22

既然说到锁了，update语句也是加排他锁,那么执行

UPDATE staff set age=age+1 where id=1;
拿到age也是最新的,即使事务B查询到age=21，这期间有事务C抢先将age=21更新为age=22，事务B update age+1后也将是age=23，这种永远读取最新数据的方式称之为当前读。

为什么sql会偶尔变慢了

那就要从Mysql的WAL技术说起，全称是Write-Ahead Logging，简单来说就是先写日志，再写磁盘。

具体来说，InnoDB会先写redo log和写在内存中的数据页，再写binlog，而redo log是固定大小为4g磁盘空间。另外会InnoDB在合适的时候再写到磁盘，也就是更新到主键树的数据页中，这个写磁盘的过程叫做flush。

这样做可以提高更新速度，如果每次更新都要写磁盘，那要先在磁盘上找出来再更新，这样会增加磁盘IO和查询的成本。

所以为什么sql会偶尔变慢，那可能就是flush。InnoDB在系统空闲的时候会flush，系统没压力不会影响。那什么时候flush会影响呢。

一是 redolog空间满了，所有更新都要阻塞，等把redo log腾出空间来，更新才能继续，所以这个时候你会发现update insert delete语句都有可能被阻塞掉。

二是 内存不够用了，这个时候要将内存中的数据页，淘汰一部分。如果这个数据页是脏页，也就是数据和磁盘不一致的数据页，则开始先执行flush操作。如果这个时候你的select 语句要flush掉大量的脏页，增加磁盘IO压力，导致了查询响应变长。

join不join

在分库分表的背景下,我们现在都是推荐单表查询，然后到应用层处理数据,将计算压力放在应用服务器上,毕竟应用服务器一般比数据库多。但是说到join的话，那就来看看join的执行流程。

select * from t1 join t2 on (t1.a=t2.a);

假设上面语句中,t1有N条记录,t2有M条记录,t2上a字段上没有索引,这个时候会采用BNJ算法，它的执行流程如下

1.全表扫描表t1,读取全部记录放进join_buffer

2.全表扫描表t2，取出每一行和join_buffer的t1数据判断

3.作为结果集返回服务层

这个流程需要扫描的行数是 N+M条，内存判断次数是N*M。

join_buffer默认大小是256k，如果不足以放下t1表的数据时怎么办，那么流程如下

1.扫描表t1，读取K条记录放进join_buffer

2.全表扫描t2，取出每一行和join_buffer的t1数据判断

3.作为结果集返回服务层，清空join_buffer，继续顺序读取K条记录放进join_buffer

4.全表扫描t2，取出每一行和join_buffer的t1数据判断

5.循环上述流程直到结束

那么这个流程需要扫描的行数是N+N/KM条,内存判断依然是NM。

从这里我们可以学到，join_buffer越大，扫描行数就越少，最理想就是N+M条；

如果t2的a字段上有索引，那么会采用NLJ算法，执行流程如下

1.扫描表t1，拿到一条记录

2.通过索引树定位t2的记录，拿到主键，通过主键索引树定位记录，和t1的记录作判断

3.组成结果集返回服务层

4.循环上述流程直到结束

那么这个流程需要扫描的行数是N+M，相比BNJ算法最理想的N+M行多了两次树搜索的时间开销（2*log2M），但是不用占用join_buffer。

两者对比之下，我们看到NLJ算法比BNJ算法扫描行数少（除非在BNJ最理想的情况，但现实基本不可能），join语法要用到NLJ算法才算性能好。所以，结论如下

1.被驱动表的字段要加索引

2.用小表作为驱动表，无论是BNJ算法还是NLJ算法，从上面的公式可以看到N越小，扫描行数和时间复杂度越小。另外需要补充的是，小表是加了where条件后的数据量相对小的表,但是用join并不保证第一个就是驱动表,优化器会自己判断的。

继续说order by

上面说到可以利用索引的有序性来消除排序，提高性能。那么在不能利用索引有序性的情况下，order by是怎么执行的。

EXPLAIN SELECT * from staff  ORDER BY age;

mysql会根据max_length_for_sort_data判断，如果行数据小的话，采用全字段排序，如果行数据大的话采用rowid排序。

全字段排序流程如下

1.扫描staff表

2.在 sort_buffer中创建内存临时表，存放记录然后排序，直接返回结果集

3.如果sort_buffer不够大，那就要放进多个磁盘临时文件，然后归并排序成大文件，读取磁盘，返回结果集

rowid排序流程如下

1.扫描staff表

2. 在 sort_buffer中创建内存临时表，将记录主键放进内存表，然后排序

3.如果sort_buffer不够大，那就要放进多个磁盘临时表，然后归并排序成大表

4.用根据条件排序后的主键，去主键树拿数据，返回结果集

在默认设置中，InnoDB一般都会用全字段排序，因为少了回表的成本。没错，在要排序的记录少的情况很快，但是如果是下面这种分页sql呢

SELECT * from staff  ORDER BY age  LIMIT 10000,2;

上面的sql执行的时候采用全字段排序可能会创建大量的临时表,要优化这条语句,难道要我们手动改小max_length_for_sort_data？这不现实。通常情况下，我们可以用关联改写sql,达到rowid排序的目的,如

SELECT * from staff t1
INNER JOIN (select id from staff ORDER BY age LIMIT 10000,10) t2 where t1.id=t2.id

in ,exists和join的分析和选择

很多人拿这三个语法来比较性能,但是join和前面两个的语义不太一样的。如果一定要比较的话，那要保证join on字段列的值要唯一。

EXPLAIN  SELECT staff.name from staff where EXISTS (SELECT 1 from test_dept where test_dept.type=4 and staff.deptid=test_dept.deptid);
SHOW WARNINGS;
EXPLAIN   SELECT staff.name from staff where deptid in (SELECT deptid from test_dept where test_dept.type=4);
SHOW WARNINGS;
EXPLAIN   SELECT  staff.name from staff JOIN test_dept on staff.deptid=test_dept.deptid where test_dept.type=4 ;
SHOW WARNINGS;

上面三条语句都用EXPLAIN解释执行计划，而SHOW WARNINGS可以拿到实际执行的sql。下面列表中我把deptid或者type有索引的情况列出来

在deptid有索引的情况下，exists用了索引，扫描行数是101=10行，而in和join没有用上索引，扫描行数是1910，在细看一下会发现in的extra列中有FirstMatch，代表采用半连接关联，实际上用SHOW WARNINGS看到的sql，使用了semi join，如下

/* select#1 */ select `mysql`.`staff`.`name` AS `name` from `mysql`.`staff` semi join (`mysql`.`test_dept`) where ((`mysql`.`test_dept`.`deptid` = `mysql`.`staff`.`deptid`) and (`mysql`.`test_dept`.`type` = 4))

所以我们这里可以得出结论，在deptid有索引的情况下，exists》join》in，为什么join优于in，上面说到一个他们可以比较的前提下就是join on字段没有重复值，而semi join在子查询的时候会多做一步判断重复的操作的，这种场景中是多余的。

再看看type有索引的情况，都用上了索引，扫描行数都是1*10，只是看扫描行数，性能貌似是一样。但是还是有其他区别的。join用了BNJ算法；in将子查询的结果物化视图，然后再用NBJ算法。

所以结果还是exists》join》in。话说，in操作在阿里巴巴开发规约里面也不推荐使用，如

【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

小结

一个好的数据库设计要先从表字段做起，所以本文先列了类型。然后性能大多数都是跟索引有关系的，也分几个案例说了索引的原理，另外还补充了一些日常开发相关的问题，最后用exists和in演示了如何分析sql语句和选择。