MySQL 5.6新特性MRR

一、什么是MRR 
MMR全称是Multi-Range Read,是MYSQL5.6优化器的一个新特性,在MariaDB5.5也有这个特性。优化的功能在使用二级索引做范围扫描的过程中减少磁盘随机IO和减少主键索引的访问次数。是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段。(参考:https://blog.csdn.net/caomiao2006/article/details/52205177

二、MRR和没有MRR的区别 

给出一个简单的例子,在innodb表执行下面的查询:

SELECT non_key_column FROM tbl WHERE key_column=x

在没有MRR的情况下,它是这样得到结果的

1.  select key_column, pk_column from tb where key_column=x order  by key_column —>
假设这个结果集是t

2.  for each row in t ;
select non_key_column from tb where pk_column = pk_column_value。(回表)

在有MRR的情况下,它是这样执行的:
1.  select key_column, pk_column from tb where key_column = x  order by key_column —> 假设这个结果集是t
2.  将结果集t放在buffer里面(直到read_rnd_buffer_size这个buffer满了),然后对结果集t按照pk_column排序      —> 假设排序好的结果集是t_sort
3.  select non_key_column from tb where pk_column in (select pk_column from t_sort)

两者的区别主要是两点:

1. 没有MRR的情况下,随机IO增加,因为从二级索引里面得到的索引元组是有序,但是他们在主键索引里面却是无序的,所以每次去主键索引里面得到non_key_column的时候都是随机IO。(如果索引覆盖,那也就没必要利用MRR的特性了,直接从索引里面得到所有数据)

2. 没有MRR的情况下,访问主键索引的次数增加。没有MRR的情况下,二级索引里面得到多少行,那么就要去访问多少次主键索引(也不能完全这样说,因为mysql实现了BNL),而有了MRR的时候,次数就大约减少为之前次数t/buffer_size。

https://blog.csdn.net/liang_0609/article/details/44040357
https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html
http://www.percona.com/blog/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/

When MRR is used, the Extra column in EXPLAIN output shows Using MRR.

参考阅读:
MYSQL之ICP、MRR、BKA(推荐)

MySQL中select中的for update 的用法

注意: FOR UPDATE 只能用在事务区块(BEGIN/COMMIT)中才有效。

有时候我们会看到一些select语句后面紧跟一句for update,表示手动加锁的意思,这里我们就介绍一下对for update的理解。相对另一种手动加锁方法lock in share mode 的区别见:https://blog.csdn.net/liangzhonglin/article/details/65438777

for update:IX锁(意向排它锁),即在符合条件的rows上都加了排它锁,其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话,那么其他session是无法读取和修改这些记录的,但是innodb有非锁定读(快照读并不需要加锁),for update之后并不会阻塞其他session的快照读取操作,除了select …lock in share mode和select … for update这种显示加锁的查询操作。

lock in share mode:是IS锁(意向共享锁),即在符合条件的rows上都加了共享锁,这样的话,其他session可以读取这些记录,也可以继续添加IS锁,但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

for update是一个意向排它锁,也就是说对于select … for update 这条语句所在的事务中可以进行任何操作(锁定的是记录,这里指主键id=1的这条记录),但其它事务中只能读取(对这条id=1的记录),不能进行update更新操作。

一般用在并发场景下,如双11的时候商品数量的更新,如果不添加for update的话,则会出现商品数量被多减的bug。

为了更加方便理解,我们举例说明(事务隔离级别为 RR,这里表tb的id为主键)

事务A:

start transaction;
select * from tb where id=1 for update;
update tb set product_num=product_num-1 where id=1;

此时另一个事务B执行同样的程序语句:

start transaction;
// 下面此时会被阻塞,直到事务A提交或者回滚
select * from tb where id=1 for update; 
update tb set product_num=product_num-1 where id=1;

对于其它主键值非1的不存在这种情况,只要两个事务操作的不是同一条记录就可以执行成功。

推荐阅读:https://blog.csdn.net/u011957758/article/details/75212222

mysql explain 中key_len的计算方法

建议先阅读这篇文章:http://hidba.org/?p=404

下面我们只对其中提到的

(1).索引字段的附加信息:可以分为变长和定长数据类型讨论,当索引字段为定长数据类型,比如charintdatetime,需要有是否为空的标记,这个标记需要占用1个字节;对于变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;

(备注:当字段定义为非空的时候,是否为空的标记将不占用字节)

(2).同时还需要考虑表所使用的字符集,不同的字符集,gbk编码的为一个字符2个字节,utf8编码的一个字符3个字节, utf8mb4 编码则是4个字节;

做一个验证。

每种MySQL数据类型的定义参考:https://blog.haohtml.com/archives/15222

下面我们以定长数据类型准,变长数据类型请自行测试。

一、数据索引类型允许为null的情况:

表结构:

CREATE TABLE `tb` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sid` smallint(5) DEFAULT NULL,
`gid` smallint(5) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_common` (`sid`,`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

执行分析语句:

mysql> EXPLAIN select * from tb where sid=1 and gid=5;
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 6       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

发现用到了复合索引idx_common,这时复合索引的两个字段全部用到了,而由于 smallint 数据类型占用字节为两个字节, 属于定长类型,且允许为null,所以key_len长度计算公式为 (2 + 1) + (2 + 1) = 6
下面我们将两个字段全部禁止null看一下计算值

二、数据索引类型不允许为null的情况
表结构

CREATE TABLE `tb` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sid` smallint(5) NOT NULL,
`gid` smallint(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_common` (`sid`,`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
mysql> EXPLAIN select * from tb where sid=1 and gid=5;
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 4       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------+------+----------+-------------+

可以看到key_len的长度为4,即2 + 2 = 4

这里同样是复合索引中的字段全部用到,我们可以先测试一下用到一个字段的情况,依据左前缀索引原则

mysql> EXPLAIN select * from tb where sid=1;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb    | NULL       | ref  | idx_common    | idx_common | 2       | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

发现key_len的值为2,就是说明只用到了一个复合索引字段,这里指的是sid字段。

说明:一般情况下如果key的值越大越好,说明了充分利用到了我们创建的索引。

对于char或者varchar类型则可能自行测试!

推荐阅读:http://imysql.com/2017/08/08/quick-deep-into-mysql-index.shtml

10分钟让你明白MySQL是如何利用索引的

一、前言

在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。

譬如:

  1. MySQL 在遇到范围查询条件的时候就停止匹配了,那么到底是哪些范围条件?
  2. MySQL 在LIKE进行模糊匹配的时候又是如何利用索引的呢?
  3. MySQL 到底在怎么样的情况下能够利用索引进行排序?

请见:http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html

PHP连接mysql8.0出错“SQLSTATE[HY000] [2054] The server requested authentication method unknown to”的解决办法

错误信息

SQLSTATE[HY000] [2054] The server requested authentication method unknown to…

这个错可能是mysql默认使用caching_sha2_password作为默认的身份验证插件,而不再是mysql_native_password,但是客户端暂时不支持这个插件导致的。官方文档说明

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. For information about the implications of this change for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

在MySQL 8.0中,caching_sha2_password是默认的身份验证插件,而不是mysql_native_password。有关此更改对服务器操作的影响以及服务器与客户端和连接器的兼容性的信息,请参阅caching_sha2_password作为首选身份验证插件。

解决方法一:修改MySQL全局配置文件

编辑my.cnf文件,更改默认的身份认证插件。

$ vi /etc/my.cnf

[mysqld]中添加下边的代码

default_authentication_plugin=mysql_native_password

然后重启mysql

$ service mysqld restart

解决方法二:修改密码认证方式

ALTER USER 'YOURUSERNAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';

官方文档:https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

MySQL中的查询开销查看方法

MySQL使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

mysql> select * from t_message limit 10;
...省略结果集

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+

示例中的结果表示优化器认为大概需要做6391个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的,这些统计信息包括:每张表或者索引的页面个数索引的基数索引数据行的长度索引的分布情况等等。

有非常多的原因会导致MySQL选择错误的执行计划,比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样(我们希望执行时间尽可能短,但MySQL值选择它认为成本小的,但成本小并不意味着执行时间短)等等。

这里last_query_cost的值是io_costcpu_cost的开销总和,它通常也是我们评价一个查询的执行效率的一个常用指标。

(1)它是作为比较各个查询之间的开销的一个依据。

(2)它只能检测比较简单的查询开销,对于包含子查询和union的查询是测试不出来的。

(3)当我们执行查询的时候,MySQL会自动生成一个执行计划,也就是query  plan,而且通常有很多种不同的实现方式,它会选择最低的那一个,而这个cost值就是开销最低的那一个。

(4)它对于比较我们的开销是非常有用的,特别是我们有好几种查询方式可选的时候。

了解MySQL中的字符集

https://dev.mysql.com/doc/refman/5.7/en/charset.html

平时我们只说了字符集这个概念,另外还有对应的“字符序”。一个字符集(如utf8)对应多个字符序(utf8_general_ci、utf8_german2_ci等),每个字符集都有一个默认“字符序”。

什么是字符集、字符序?简单的来说:

  1. 字符集(character set):定义了字符以及字符的存储编码。
  2. 字符序(collation):定义了字符的比较规则。

可以通过命令查看字符集、字符序信息:

SHOW CHARACTER SET;

在我们开发中,一般要保持服务器端的字符集与客户端的字符集保持一致,不然容易出现乱码的情况。

MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。

参考文章:https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html