Innodb中Page结构

  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 (F*1) or (F*2) 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

更多参考:http://www.2cto.com/database/201412/365376.html

http://hedengcheng.com/?p=118

聚簇索引(clustered index )和非聚簇索引(secondary index)的区别

这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键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作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

x

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

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

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

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

转:http://www.cnblogs.com/shijingxiang/articles/4743324.html

如何查询mysql事务未提交

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

到information_schema库下面,查看下面这个表:
innodb_trx         ## 当前运行的所有事务
innodb_locks       ## 当前出现的锁
innodb_lock_waits  ## 锁等待的对应关系

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

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

Continue reading

mysql中的handler_read_%

 

  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.

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of
table scans. Generally this suggests that your tables are not properly indexed or that your queries are
not written to take advantage of the indexes you have.

这里很重要的一点要理解:索引项之间都是有顺序的,所以才有first, last, next, prev等等,所以前面的5个都是对索引读情况
的计数器,而后面的2个是对数据文件的读情况的计数器。

很显然的一点:
后面的2个 Handler_read_rnd 和 Handler_read_rnd_next 是越低越好,如果很高,应该进行索引相关的调优。而Handler_read_key的数值
肯定是越高越好,越高代表使用索引读很高。其他的计数器,要具体情况具体分析

http://blog.chinaunix.net/uid-25909722-id-4378355.html

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by的解决办法

线上用的MySQL版本为5.7.11,线下用的5.6版本,发现将程序上线后,有些地方报这个错误

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ONLY_FULL_GROUP_BY:
对于GROUP BY聚合操作,若select中的列没有在group by中出现,那么这句SQL是不合法的。

解决办法下my.cnf中添加以下几行

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

在sql_mode 中去掉only_full_group_by

然后重启MySQL Server即可。

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

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Alternatively, disable ONLY_FULL_GROUP_BY.

对于以前的语句,可以使用ANY_VALUE() 函数来解决。
对于SQL_MODE各值的用法见:http://blog.itpub.net/29773961/viewspace-1813501/

Linux下安装MySQL多实例

环境说明:
Centos 6.6 64位
mysql 使用最新版本5.7.16版本

这里安装两个MySQL实例,分别使用3306/3307端口号

目录结构:
/data/mysql/mysql3306
/data/mysql/mysql3306/data
/data/mysql/mysql3307/log
/data/mysql/mysql3306/tmp

执行命令:

mkdir -p /data/mysql/mysql3306/{data,tmp,log}
mkdir -p /data/mysql/mysql3307/{data,tmp,log}

为了方便我们先配置mysql3306实例,配置成功后,再复制一份到3307即可。

tar zxvf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz
cp -rf mysql-5.7.16-linux-glibc2.5-x86_64/* /data/mysql/mysql3306/

权限修改

chown -R mysql:mysql /data/mysql/mysql3306

配置my.cnf Continue reading

Percona XtraBackup备份mysql数据库 技术手册

https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

下载地址:https://www.percona.com/downloads/XtraBackup/LATEST/
一、安装两个必需库

sudo yum -y install libdv perl-DBD-MySQL

如果libev库在yum源找不到的话,需要在rpmfind.net网站下载自行安装。

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/libev-4.15-1.el6.rf.x86_64.rpm
rpm libev-4.15-1.el6.rf.x86_64.rpm

二、安装percona-xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

https://yq.aliyun.com/articles/59272

https://my.oschina.net/lionel45/blog/691406

http://blog.itpub.net/29418060/viewspace-1676617/