mysql explain 中key_len的计算方法

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

下面我们只对其中提到的

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

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

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

做一个验证。

每种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的值越大越好,说明了充分利用到了我们创建的索引。

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中的字符集

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

了解MySQL中的驱动表

一、为什么要用小表驱动大表

1、驱动表的定义

当进行多表连接查询时, [驱动表] 的定义为:

1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]

2)未指定联接条件时,行数少的表为[驱动表](重要)

忠告:如果你搞不清楚该让谁做驱动表、谁 join 谁,请让 MySQL 运行时自行判断

既然“未指定联接条件时,行数少的表为[驱动表]”了,而且你也对自己写出的复杂的 Nested Loop Join 不太有把握(如下面的实例所示),就别指定谁 left/right join 谁了,请交给 MySQL优化器 运行时决定吧。

2、mysql关联查询的概念:

MySQL 表关联的算法是 Nest Loop Join(嵌套循环),是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

例: user表10000条数据,class表20条数据

SELECT * FROM user u LEFT JOIN class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来,而如果用class表驱动user表则只需要循环20次就能查询出来

SELECT * FROM class c LEFT JOIN user u c.userid=u.userid

小结果集驱动大结果集

de.cel 在2012年总结说,不管是你,还是 MySQL,优化的目标是尽可能减少JOIN中Nested Loop的循环次数。

以此保证:永远用小结果集驱动大结果集(Important)!

相关算法参考:https://blog.csdn.net/caomiao2006/article/details/52205177

二、优化联表查询

优化第一步之:根据驱动表的字段排序

left join不变,干嘛要根据非驱动表的字段排序呢?我们前面说过“对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序!”的。

explain

SELECT mb.id……

FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid

WHERE 1=1

ORDER BY mb.id DESC

limit 0,10

也满足业务场景,做到了rows最小:

优化第二步:去除所有JOIN,让MySQL自行决定,explain第一张表就是驱动表,数据量比其它两张表都要小!

explain
SELECT mb.id…… 
FROM mb,mbei,u   
WHERE 
    mb.id=mbei.mb_id
    and mb.uid=u.user_id
order by mbei.apply_time desc
limit 0,10

立竿见影,驱动表一样是小表 mbei:

id select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1  SIMPLE        mbei ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 Using filesort
1  SIMPLE        mb      eq_ref  PRIMARY,userid  PRIMARY 4            mbei.mb_id   1
1  SIMPLE        u         eq_ref  PRIMARY            PRIMARY 4            mb.uid           1  Using index

 

三、总结

1、不要过于相信你的运气!

2、不要相信你的开发环境里SQL的执行速度!

3、请拿起 explain 武器,如果你看到以下现象,请优化:

1)出现了Using temporary

2)rows过多,或者几乎是全表的记录数

3)key 是 (NULL)

4)possible_keys 出现过多(待选)索引.

推荐阅读:什么情况下会用到临时表

 

mysql配置变量介绍

key_buffer_size

设置这个变量给键缓冲区(或者说键缓存)分配指定大小的空间。但是操作系统只有在实际用到这些空间的时候才会进行分配。例如,将键缓冲区大小设置为1GB,并不意味着服务器就会真正地给它分配1GB空间。

对一个已有的缓存设置非零值将会冲洗缓存,从技术上来说,这是一个在线操作,但是它会阻止所有访问该缓存的动作,直到缓存冲洗完成。

table_cache_size

设置这个变量是不会立即生效,要等到下一个线程打开表的时候才会生效。当它生效的时候,MYSQL会检查变量的值。如果值大于缓存中表的数量,线程就可以把新打开的表插入到缓存中。如果值小于缓存中表的数量,MySQL就会从缓存中删除掉没有使用的表。

thread_cache_size

设置这个变量不会立即生效,生效被延时到了下一次线程关闭的时候。在那时,MySQL检查缓存中是否有空间存储线程。如果是,它会把线程缓存起来,供另外一个连接使用。如果不是,它会直接结束掉线程。在这种情况下,缓存中线程的数量,以及线程缓存使用的内存数量不会立即就下降。只有当新连接为了使用线程把它从缓存中移走的时候才会看到下降。(MySQL只有在连接关闭的时候才会把线程加入缓存,也只有在创建新连接的时候 才从缓存中移除线程。) Continue reading

mysql 数据表读锁机制详解

为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。
一、概述
MySQL有三种锁的级别:页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下:
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
二、MyISAM表锁
MyISAM存储引擎只支持表锁,是现在用得最多的存储引擎。
1、查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺: Continue reading

MySQL聚簇索引&聚集索引&索引组织表my

MySQL聚簇索引&聚集索引&索引组织表

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

聚簇索引和聚集索引(Clustered Index)

说起索引,不能不说B+树。

引用:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search),二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

MySQL就普遍使用B+Tree实现其索引结构。

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于其实现方式,但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

mysq_index

当表有聚簇索引时,他的数据行实际上存放在索引的叶子页(leaf page)中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起(这并非总成立)。

因为无法同时把数据行存放在两个不同的地方,索引一个表只能有一个聚簇索引。

mysql_index

注:叶子页面包含完整的元组,而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引,但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。

索引组织表(Index Organized Table, IOT)

其实和聚簇索引说的是一个意思。

索引组织表(Index organized table, IOT)就是存储在一个索引结构中的表。与堆组织表无序存储不同的是,IOT中的数据按主键存储和排序。

相比堆组织表,索引组织表能够节省一部分空间,因为使用堆组织表时,我们必须为表和表的主键上的索引分别留出空间。而IOT则可以省去主键索引的开销,因为数据就是按顺序存储的,可以当做索引使。换句话说,如果你只会通过一个表的主键来访问这个表,这个表就适合创建成索引组织表。

举例:

1.一个客户有很多地址信息,客户是一个表,客户地址信息是另外一个表。读取一个客户地址信息的时候,如果这个客户的所有地址信息都存放在相邻的地方,读取速度就会快一些。这个时候,客户地址信息表适合创建成IOT。

2. 经常查看一支股票的最近几天的信息,股票信息一般是千万级别的数据,如果能够把最近几天的信息存放在一起就会快很多。

=======================END=======================

转:http://my.oschina.net/xinxingegeya/blog/474895

从MyISAM转到InnoDB需要注意什么

当前,绝大多数业务场景用InnoDB已经完全能搞定了,越来越多的业务从MyISAM转向InnoDB引擎,那么有哪些注意事项呢? 分析 当了解完两种引擎的不同之处,很轻松的就能知道有哪些关键点了。

总的来说,从MyISAM转向InnoDB的注意事项有:

1、MyISAM的主键索引中,可以在非第一列(非第一个字段)使用自增列,而InnoDB的主键索引中包含自增列时,必须在最前面;这个特性在discuz论坛中,被设计用于“抢楼”功能,因此,若有类似的业务,则无法将该表从MyISAM转成InnoDB,需要自行变通实现(我们则是将其改到Redis中实现);
2、不带条件频繁统计全表总记录数时(SELECT COUNT(*) FROM TAB),InnoDB相对较慢,而MyISAM则飞快;不过,如果是基于索引条件的统计,则二者相差不大;
3、InnoDB在5.6以前不支持全文索引,不过这个相信无所谓,没什么人会在MySQL里直接跑全文索引,尤其是对中文的全文索引(前阵子有开发同学提需求直接被我否了),确实有需要的话,可以采用Sphinx、Lucene等其他方案实现;
4、一次性导入大量数据并且后续还要进行加工处理的,可以先导入到MyISAM引擎表中,经过一通加工处理完后,再导入InnoDB表(我曾经在业务中用此方法提高数据批量导入及处理效率);
5、InnoDB不支持LOAD TABLE FROM MASTER语法(不过应该也很少人使用吧);

从MyISAM转成InnoDB可以享受的好处则有: Continue reading