标签归档:MySQL

从Aliyun RDS还原备份数据备忘

从后台下载备份, 可以下载到本地,也可以在远程机器上操作。

安装工具:
https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum list | grep percona
yum install percona-xtrabackup-22

rpm -qa | grep percona
percona-release-0.1-3.noarch
percona-xtrabackup-22-2.2.13-1.el7.x86_64

/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-22-2.2.13
/usr/share/doc/percona-xtrabackup-22-2.2.13/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

需要使用的是/usr/bin/innobackupex工具。

下载解压工具:http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=5176.7741817.2.6.Aw8bl9&file=rds_backup_extract.sh

chmod +x rds_backup_extract.sh

解压实例备份数据(看数据大小,可能时间较长, 另外确保磁盘空间足够):

rds_backup_extract.sh -f hins3426165_data_xxx.tar.gz -C /root/mysql/data

还原数据:

innobackupex —defaults-file=/root/mysql/data/backup-my.cnf --apply-log /root/mysql/data

启动实例:

mysqld_safe --defaults-file=/root/mysql/data/backup-my.cnf --user=mysql --datadir=/root/mysql/data 

启动实例可能会遇到各种问题,主要是配置文件导致的,注释backup-my.cnf中对应的配置即可:
1 [ERROR] /alidata/server/mysql/bin/mysqld: unknown variable ‘innodb_log_checksum_algorithm=innodb’
把innodb_log_checksum_algorithm=innodb注释掉

2 [ERROR] InnoDB: ./ibdata1 can’t be opened in read-write mode
rm -fr ibdata1 ib*

3 [ERROR] –gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires –log-bin and –log-slave-updates
添加gtid_mode = on,enforce_gtid_consistency = 1,log_slave_updates = 1

4 [ERROR] Fatal error: Can’t open and lock privilege tables: Can’t find file: ‘./mysql/user.frm’
新建用户:
delete from mysql.db where user<>‘root’ and char_length(user)>0;
delete from mysql.tables_priv where user<>‘root’ and char_length(user)>0;
flush privileges;

5 其它问题
一般都是配置相关,对应注释掉相对应的指令即可。

MySQL 事务与锁查看

information_schema.innodb_trx

trx_id					事务ID
trx_state				事务状态
trx_started				事务执行开始时间
trx_requested_lock_id			事务等待锁ID号(等待其它事务释放锁)
trx_wait_started			事务等待锁开始时间
trx_weight						
trx_mysql_thread_id			事务线程ID
trx_query				具体的SQL
trx_operation_state			事务当前操作状态
trx_tables_in_use			事务中有多少个表被使用
trx_tables_locked				
trx_lock_structs					
trx_lock_memory_bytes			事务锁住的内存大小(B)
trx_rows_locked				事务锁住的行数
trx_rows_modified			事务更改的行数
trx_concurrency_tickets			事务并发数
trx_isolation_level			事务隔离级别
trx_unique_checks			是否唯一性检查
trx_foreign_key_checks			是否外键检查
trx_last_foreign_key_error		最后的外键错误
trx_adaptive_hash_latched		
trx_adaptive_hash_timeout		

注:trx_started记录了事务开始的时间,如果过去了很长时间,可能是异常事务。trx_wait_started记录了等待时间,如果等待了很长时间,可能是异常事务。对应等待锁的事务,trx_query记录了具体的SQL语句。trx_mysql_thread_id可以定位到具体的线程(回话ID)

information_schema.innodb_locks

lock_id							锁ID
lock_trx_id						拥有锁的事务ID
lock_mode						锁模式
lock_type						锁类型
lock_table						被锁的表
lock_index						被锁的索引(类型)
lock_space						被锁的表空间号
lock_page						被锁的页号
lock_rec						被锁的记录号
lock_data						被锁的数据(对应索引编号,一般是ID号)

事务可以持有多个锁。锁类型有S和X。根据索引类型不同,lock_rec和lock_data可以定位行号。当开启一个事务,对相关行上锁,这个时候的锁不会出现在innodb_locks表中,只有相关的锁被其它事务等待时,产生了锁等待,才会把锁与等待的锁插入此表(换个说法就是此表是用来存放有依赖关系的锁的)

information_schema.innodb_lock_waits

requesting_trx_id					请求锁的事务ID
requested_lock_id					请求锁的锁ID
blocking_trx_id						当前拥有锁的事务ID
blocking_lock_id					当前拥有锁的锁ID
requesting_thd_id					请求锁的线程ID
blocking_thd_id						当前拥有锁的线程ID

记录依赖关系。请求锁等待持有锁。

1 当要查看有哪些线程时,直接运行show full processlist即可,这个命令动态列出当前的线程状态
2 当要查看有哪些事务时(比如检查有哪些事务长时间未结束),可以直接查看innodb_trx表,这个表中的trx_started记录了开始事务的时间。
3 当要查看是否有锁等待时,可以查看innodb_locks,只要有记录,就说明产生了锁等待,具体是哪个依赖哪个,需要查看innodb_lock_waits的关系。

一般来说,产生了锁等待,如果超时,事务会自动释放,但是如果事务开启了,单长时间没有结束,就应该去innodb_trx查看确认(从线程基本无法查看到已经开启了事务)。

获得导致行锁等待和行锁等待超时的会话:

select l.* from ( select 'Blocker' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trx from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union select 'Blockee' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, null from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;

对于复杂的多个会话相互行锁等待情况,建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

对于标识为 Blocker 的会话(持有锁阻塞其他会话的 DML 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。比如,可以通过 Kill 命令来今后会话终止。

Mac 开发环境搭建

进入Mac的默认Shell终端,安装Homebrew工具:

Homebrew是一个包管理器,用于在Mac上安装一些OS X没有的UNIX工具(比如著名的wget)。 Homebrew将这些工具统统安装到了/usr/local/Cellar目录并在/usr/local/bin中创建符号链接。

官方网站:
http://brew.sh/index_zh-cn.html

安装:

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

#检查安装:
brew -v

Homebrew 常用命令

brew install git

brew uninstall git

brew search git

brew list

brew update

#更新某具体软件
brew upgrade git

#查看软件信息
brew [info | home] [FORMULA...]

#和upgrade一样,单个软件删除 和 所有程序删除。清理所有已安装软件包的历史老版本
brew cleanup git 
brew cleanup

#查看哪些已安装的程序需要更新
brew outdated

Homebrew 卸载

    cd `brew --prefix`
    rm -rf Cellar
    brew prune 
    rm `git ls-files` 
    rm -rf Library .git .gitignore bin/brew
    rm -rf README.md share/man/man1/brew
    rm -rf Library/Homebrew Library/Aliases 
    rm -rf Library/Formula Library/Contributions
    rm -rf ~/Library/Caches/Homebrew

一 安装PHP

#搜索,会出现几个分之,比如PHP56 PHP71
brew search php
#过滤,只要71分支,提供了非常多扩展包
brew search php71
#安装(选择需要的扩展包)
brew install homebrew/php/php71 homebrew/php/php71-apcu homebrew/php/php71-redis homebrew/php/php71-mongodb homebrew/php/php71-opcache omebrew/php/php71-swoole

大部分PHP的模块,都包含在了homebrew/php/php71中,是编译到内核的(非动态模块),上面的apcu,redis,mogondb,swoole是动态模块,模块安装位置:/usr/local/opt/,比如:/usr/local/opt/php71-apcu/apcu.so。配置文件自然是/usr/local/etc/php/7.1/php.ini,扩展的配置放在/usr/local/etc/php/7.1/conf.d/*.ini。

php -v
php -m

编译到内核的模块确实是大而全,然后还需要调整一下php.ini的配置(才能符合开发环境要求):

#设置时区
date.timezone = Asia/Shanghai
 
#CGI相关参数,实际上建议修改的是force_redirect,其它均保留默认值
cgi.force_redirect = 0   #默认为1,改为0
cgi.fix_pathinfo = 1     #默认是1,保留
fastcgi.impersonate = 1  #默认是1,保留
cgi.rfc2616_headers = 0  #默认是0,保留

#其它参数调整,根据实际情况调整
upload_max_filesize = 64M
max_execution_time = 1200
max_input_time = 600
max_input_nesting_level = 128
max_input_vars = 2048
memory_limit = 1024M
 
post_max_size = 64M

如果要启动PHPFPM,FPM主配置文件/usr/local/etc/php/7.1/php-fpm.conf,池配置在/usr/local/etc/php/7.1/php-fpm.d中,需要注意的是,池配置中,默认的运行用户是和用户组均为_www,所以需要检查文件的权限,保证对_www具有读和执行(默认是符合的),如果要写入,那么还需要保证对应的文件夹有被写入的权限。

启动PHPFPM,由于php-fpm这个命令放入到了/usr/local/sbin中,默认shell并不搜索这个路径,所以要想添加环境变量:

#设置环境变量
export PATH="/usr/local/sbin:$PATH"  
echo 'export PATH="/usr/local/sbin:$PATH"' >> ~/.bash_profile

#确认命令能找到
which php-fpm
which php71-fpm

#手动启动,PHPFPM可以不使用root身份启动(user和group指令无用),会使用当前用户运行
sudo php71-fpm start
sudo php71-fpm stop

对于开发环境,PHPFPM可以不用启动,直接使用PHP内置的HTTP服务器也可以。

二 安装Nginx

brew install --with-http2 nginx  

如果要绑定到80端口,那么Nginx就必须以root身份运行。默认的server配置位于(可改):/usr/local/etc/nginx/servers。可以往里面方式配置:

server {
    listen 80;
    #listen 443 ssl http2;
    server_name test.app;
    root "/Users/xx/www/test/public";
 
    index index.html index.htm index.php;
 
    charset utf-8;
 
    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }
 
    location = /favicon.ico { access_log off; log_not_found off; }
    location = /robots.txt  { access_log off; log_not_found off; }
 
    access_log off;
 
    sendfile off;
 
    location ~ \.php$ {
        client_max_body_size 64M;
        fastcgi_intercept_errors off;
        fastcgi_connect_timeout 300;
        fastcgi_send_timeout 300;
        fastcgi_read_timeout 300;
        fastcgi_buffer_size 32k;
        fastcgi_buffers 64 32k;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }
 
    location ~ /\.ht {
        deny all;
    }
 
    #ssl_certificate     /etc/nginx/ssl/test.app.crt;
    #ssl_certificate_key /etc/nginx/ssl/test.app.key;
}

Nginx的主配置文件设置的启动user一般应该和PHPFPM相同,或者需要保证Nginx对文件具备读和执行的权限。如果是文件上传,还需要确保Nginx对临时中间文件夹具备写入权限。

启动关闭等:

sudo nginx -t
sudo nginx -s start
sudo nginx -s stop

三 安装MySQL

#安装最新版本(5.7.xx)
brew install mysql

#确定搜索路径:
which mysqld
mysqld —verbose —help | grep -A 1 ‘Default options’

/etc/my.cnf  /etc/mysql/my.cnf  /usr/local/etc/my.cnf  ~/.my.cnf

#
mysql.server start
mysql_secure_installation

# 停止
mysql.server stop

MySQL不需要以root身份启动。

四、安装Tomcat

brew search tomcat
==> Searching local taps...
tomcat ✔            tomcat-native       tomcat@6            tomcat@7            tomcat@8
==> Searching taps on GitHub...
==> Searching blacklisted, migrated and deleted formulae...

# 安装最新版本
brew install tomcat

#安装指定版本
brew install tomcat@8

启动关闭:

catalina --help
Using CATALINA_BASE:   /usr/local/Cellar/tomcat/9.0.6/libexec
Using CATALINA_HOME:   /usr/local/Cellar/tomcat/9.0.6/libexec
Using CATALINA_TMPDIR: /usr/local/Cellar/tomcat/9.0.6/libexec/temp
Using JRE_HOME:        /Library/Java/JavaVirtualMachines/jdk1.8.0_144.jdk/Contents/Home
Using CLASSPATH:       /usr/local/Cellar/tomcat/9.0.6/libexec/bin/bootstrap.jar:/usr/local/Cellar/tomcat/9.0.6/libexec/bin/tomcat-juli.jar
Usage: catalina.sh ( commands ... )
commands:
  debug             Start Catalina in a debugger
  debug -security   Debug Catalina with a security manager
  jpda start        Start Catalina under JPDA debugger
  run               Start Catalina in the current window
  run -security     Start in the current window with security manager
  start             Start Catalina in a separate window
  start -security   Start in a separate window with security manager
  stop              Stop Catalina, waiting up to 5 seconds for the process to end
  stop n            Stop Catalina, waiting up to n seconds for the process to end
  stop -force       Stop Catalina, wait up to 5 seconds and then use kill -KILL if still running
  stop n -force     Stop Catalina, wait up to n seconds and then use kill -KILL if still running
  configtest        Run a basic syntax check on server.xml - check exit code for result
  version           What version of tomcat are you running?
Note: Waiting for the process to end and use of the -force option require that $CATALINA_PID is defined

关于启动问题:
在Mac下,如果要开机启动,可以参考如下配置(一般不需要):

#Nginx
cp /usr/local/opt/nginx/homebrew.mxcl.nginx.plist ~/Library/LaunchAgents/
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.nginx.plist  

# PHP-FPM
cp /usr/local/opt/php70/homebrew.mxcl.php71.plist ~/Library/LaunchAgents/  
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.php71.plist  

# MySQL
cp /usr/local/opt/mysql/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents/  
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

## 卸载
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.nginx.plist  
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.php71.plist  
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist  
rm ~/Library/LaunchAgents/homebrew.mxcl.nginx.plist  
rm ~/Library/LaunchAgents/homebrew.mxcl.php71.plist  
rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

更加实际的方法:进入操作系统后,启动Nginx和PHPFPM(因为要sudo,需要输入密码),MySQL则在需要时启动,比如本地一般链接远程数据库。所以可以这个别名:(往.bash_profile中写入)

alias servers.start='sudo nginx && php-fpm --fpm-config /usr/local/etc/php/7.1/php-fpm.conf -D'
alias servers.stop='sudo bash -c "killall -9 php-fpm && nginx -s stop"'                       
alias nginx.logs='tail -f /usr/local/opt/nginx/access.log'
alias nginx.errors='tail -f /usr/local/opt/nginx/error.log'

遇到问题:
1 Nginx启动提示

nginx: [emerg] getgrnam("

提示大体就是找不到用户组的意思。在Nginx配置中,user如果只指定了用户名,默认会去寻找同名的用户组,在Mac中,用户不一定对应一个同名的用户组,所以出现这种情况就是需要明确指定存在的用户组,可以通过如下方式来确定用户和用户组:

#当前登录的用户名
whoami
www

#确认用户组(可见www的uid是502,对应的组id是20,名称是staff)
id
uid=502(www) gid=20(staff) groups=20(staff),12(everyone)

把www和staff对应填入,错误提示消失。

MySQL 模糊搜索测试

为了解决棘手的字符模糊匹配问题,于是对MySQL进行了一次大数据模糊匹配测试。测试环境:1台Linux虚拟机(CentOS 7.x), 单核,1G内存, MySQL 5.6,未做任何优化(默认安装)。

#key建了一个索引
mysql> show create table indexes \G;
*************************** 1. row ***************************
       Table: indexes
Create Table: CREATE TABLE `indexes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `refto` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `key_idx` (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=91830001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

#总行数,约1亿条
mysql> select count(*) from indexes;
+----------+
| count(*) |
+----------+
| 91830000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(id) from indexes;
+-----------+
| count(id) |
+-----------+
|  91830000 |
+-----------+
1 row in set (0.00 sec)

#前缀like,走索引
mysql> explain select * from indexes where `key` like 'aa%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indexes
         type: range
possible_keys: key_idx
          key: key_idx
      key_len: 98
          ref: NULL
         rows: 44955
        Extra: Using index condition
1 row in set (0.01 sec)

#毫秒级完成
mysql> select * from indexes where `key` like 'bcd%' limit 100;
+----------+----------------------------------+------+-----------+
| id       | key                              | type | refto     |
+----------+----------------------------------+------+-----------+
|  9772263 | bcd0G5dNGJCE3ZLTvX4hTM8uOdvP     |    7 | 398601199 |
| 52244881 | bcd0zoLlD3cIgxoyAKHUQ1           |    1 | 397094852 |
| 50534282 | bcdGOh3euqx34nQUj                |    9 | 314711977 |
+----------+----------------------------------+------+-----------+
100 rows in set (0.00 sec)

#前后模糊(全表扫描) 几十秒完成
mysql> select * from indexes where `key` like '%bcd%' limit 100;
+---------+----------------------------------+------+-----------+
| id      | key                              | type | refto     |
+---------+----------------------------------+------+-----------+
|   22704 | 7jXbcd6Ryp3SUM1nCK9xqeszg2PV4    |    1 | 729091231 |
|   29434 | zlbcdN6dEK1TZW4mKvHjbGrQvD4b04   |    2 | 520555898 |
| 1465369 | g1bcdtfpW1NwcQl8uo               |    8 | 292527767 |
+---------+----------------------------------+------+-----------+
100 rows in set (0.60 sec)

mysql> select * from indexes where `key` like '%bdddcd%' limit 100;
Empty set (38.23 sec)

虽然有大约1亿条数据,但是前缀like是非常快速的,而且对于表有多少行数据并不影响查询效率(扫描索引)。但是前后like模糊搜索就比较不满意了。由于前缀like比较高效率,所以我们可以把类似%abcd%这样的查询,拆分成abcd, bcd, cd和d来存储,这样就可以把前面的%去掉。

不过,对于太长的字符串,这么做就会产生大量数据,查询效率上可能不会遇到问题,不过会占用大量存储空间,看看1亿条数据占用了多少空间:

-rw-rw---- 1 mysql mysql 8.5K May 23 20:02 indexes.frm
-rw-rw---- 1 mysql mysql 3.3G May 24 08:00 indexes.MYD
-rw-rw---- 1 mysql mysql 4.6G May 24 08:00 indexes.MYI

总共用8G空间,大概就是1000万占用800兆,10万占用8兆,1万条记录占用800K,100条占用8K, 1条数据占用0.08K(约82字节)。实际如果表字段多一些,会占用更大空间。

还好,我们可以用空间换时间。一个应用场景,需要从所有订单中搜索包含有指定字符串的所有订单,由于订单是不断增长的,加入1天有1万个订单,一年就有365万,3年就到达1000万,如果要从1000万中(订单号,SKU等)搜索包含有”abc”字符串的订单,那么必定要写成%abc%,虽然全局扫表也是可以出结果的,不过时间就比较难接受了。为了更好的体验,就需要建立“倒排”索引,比如订单号为AS-12345678(对应id为1),变成如下记录进行存储:

1 AS-12345678  1
2  S-12345678  1
3   -12345678  1
4    12345678  1
5     2345678  1
6      345678  1
7       45678  1
8        5678  1
9         678  1

一条记录拆成10条,如果还有其它字段,1000万记录建索引,轻松上亿。不过即使是亿级别的数据,查询依然很轻松。

Node.js 操作MySQL

var mysql = require("mysql");

var cnn = mysql.createConnection({
    host:'127.0.0.1',
	port:3306,
	database:'test',
	user:'root',
	password:''
});

//连接池
var pool = mysql.createPool({
    host:'127.0.0.1',
	port:3306,
	database:'test',
	user:'root',
	password:''
});
var pcnn = pool.getConnection((err, pcnn) => {
	console.log("使用连接池");
	var query = pcnn.query("SELECT * FROM datatables_demo");
	query.on('error', (err) => {
		console.log("数据读取错误");

		// 释放连接回到池
		pcnn.release();
	}).on('fields', (fields) => {
		//console.log(fields);
		fields.forEach((field) => {
			console.log(field.name);
		});
	}).on('result', (row) => {
		pcnn.pause();
		console.log(row.first_name+" "+row.last_name+" "+row.age);
		pcnn.resume();
	}).on('end', () => {
		console.log("读取完毕");
		// 释放连接回到池
		pcnn.release();
	});	
	console.log("使用连接池结束");
});

var pcnn = pool.getConnection((err, pcnn) => {
	console.log("使用连接池");
	var query = pcnn.query("SELECT * FROM datatables_demo");
	query.on('error', (err) => {
		console.log("数据读取错误");

		// 释放连接回到池
		pcnn.release();
	}).on('fields', (fields) => {
		//console.log(fields);
		fields.forEach((field) => {
			console.log(field.name);
		});
	}).on('result', (row) => {
		pcnn.pause();
		console.log(row.first_name+" "+row.last_name+" "+row.age);
		pcnn.resume();
	}).on('end', () => {
		console.log("读取完毕");
		// 释放连接回到池
		pcnn.release();
	});	
	console.log("使用连接池结束");
});

// 关闭整个池
//pool.end((err) => {
//	console.log("关闭连接池");
//});

///////////////////////////////////////////////////////////
cnn.connect(function(err){
	if(err) {
		console.log("数据库连接错误");
	} else {
		console.log("数据库连接成功");
	}	
});

// 连接丢失重连
cnn.on('error', function(err){
	if(err.code === 'PROTOCOL_CONNECTION_LOST') {
		console.log('连接丢失');
		setTimeout(function(){
			cnn.connetct((err)=>{
				console.log("数据库重连");
			});
		}, 10000);
	}
});

//cnn.query("INSERT INTO posts SET ?", {id:1, title:'Hello MySQL'});
//cnn.query("UPDATE posts SET title = :title", {title:'Hello MySQL'});
//cnn.query("SELECT * FROM users WHERE ID = ?", [userId]);

// 以流的方法读取数据,以行为单位
// 正确姿势
var query = cnn.query("SELECT * FROM datatables_demo");
query.on('error', (err) => {
	console.log("数据读取错误");
}).on('fields', (fields) => {
	//console.log(fields);
	fields.forEach((field) => {
		console.log(field.name);
	});
}).on('result', (row) => {
	cnn.pause();
	console.log(row.first_name+" "+row.last_name+" "+row.age);
	cnn.resume();
}).on('end', () => {
	console.log("读取完毕");
});

// 一次读整个结果,然后对结果循环
cnn.query("SELECT * FROM datatables_demo LIMIT 20", function(err, result){
	//console.log(result);
	for(row in result) {
		//console.log(result[row]);
		console.log(result[row].first_name+" "+result[row].last_name+" "+result[row].age);
	}
	console.log("------------------------------------------");
	result.forEach((row) => {
		 console.log(row.first_name+" "+row.last_name+" "+row.age);
	});
});


cnn.end(function(err){
	console.log("数据库关闭。");
});

MySQL 插入数据之replace和ignore

MySQL中处理常规的insert into之外,也提供了replace insert和insert ignore into语句。

当需要插入不重复的值(有唯一索引),常规的插入就必须先判断是否存在,然后再进行插入,这个在需要批量插入数据时,需要循环查询判断,如果使用replace insert和insert ignore into语句就不需要这样做,insert ignore into很好理解,对比唯一索引,如果存在直接跳过,而replace insert是指存在时,对存在的数据行进行更新,准确来说应该是对存在的数据行进行删除,然后插入新的。

所以使用replace insert要特别小心,它是先删除,再插入,比如插入一个已经存在的行,它会返回受影响的行是2,如果新的行没有包含原来的全部数据,那么这部分数据将丢失,如果设置了id为自动增长的,就可以看到,id将不会连续(先删除后插入的缘故)。

以下是一个trait,用来扩展Laravel ORM模型以支持insertReplace和insertIgnore这样的语法:

<?php

namespace Ebt\ModelExtend;

trait InsertReplaceable 
{
    public static function insertReplace(array $attributes = []) 
    {
        return static::executeQuery ( 'replace', $attributes );
    }
    
    public static function insertIgnore(array $attributes = []) 
    {
        return static::executeQuery ( 'insert ignore', $attributes );
    }
    
    protected static function executeQuery($command, array $attributes) 
    {
        $prefix = \DB::connection()->getTablePrefix();
        if (! count ( $attributes )) {
            return true;
        }
        $model = new static ();
        if ($model->fireModelEvent ( 'saving' ) === false) {
            return false;
        }
        $attributes = collect ( $attributes );
        $first = $attributes->first ();
        if (! is_array ( $first )) {
            $attributes = collect ( [ 
                $attributes->toArray () 
            ] );
        }
        $keys = collect ( $attributes->first () )->keys ()->transform ( function ($key) {
            return "`" . $key . "`";
        } );
        $bindings = [ ];
        $query = $command . " into " .  $prefix . $model->getTable () . " (" . $keys->implode ( "," ) . ") values ";
        $inserts = [ ];
        foreach ( $attributes as $data ) {
            $qs = [ ];
            foreach ( $data as $value ) {
                $qs [] = '?';
                $bindings [] = $value;
            }
            $inserts [] = '(' . implode ( ",", $qs ) . ')';
        }
        $query .= implode ( ",", $inserts );
        \DB::connection ( $model->getConnectionName () )->insert ( $query, $bindings );
        $model->fireModelEvent ( 'saved', false );
    }
}

用法:

$data = [
    [
        "name" => 'ifeeline',
        "note" => "xx"
    ],
    [
        "name" => 'ifeeline2',
        "note" => "yy"
    ],
];
//\App\TestTest::insertReplace($data);
\App\TestTest::insertIgnore($data);

MySQL大数据测试

批量插入数据

// Laravel框架代码
        $s = microtime(true);
        
        for($j=0;$j<100;$j++){
            $data = [];
            $ss = microtime(true);
            for($i=0;$i<5000;$i++){
                $d = [
                    'name' => str_random(64)." ".($j+1)*($i+1),
                    'cat1' => str_random(32),
                    'cat2' => str_random(32),
                    'cat3' => str_random(32),
                    'num1' => mt_rand(10000,99999),
                    'num2' => mt_rand(10000,99999),
                    'num3' => mt_rand(10000,99999).".01",
                    'num4' => mt_rand(10000,99999),
                    'num5' => mt_rand(10000,99999)
                ];
                $data[] = $d;
            }
            DB::table('list_test')->insert($data);
            $ee = microtime(true);
            
            Log::info((float)($ee-$ss));
        }
        
        $e = microtime(true);
        Log::info('Total:'.(float)($e - $s));
// 输出
[2015-11-30 13:56:10] local.INFO: 6.0573468208313  
[2015-11-30 13:56:15] local.INFO: 4.7142698764801  
[2015-11-30 13:56:20] local.INFO: 4.7502720355988  
[2015-11-30 13:56:24] local.INFO: 4.6482660770416  
[2015-11-30 13:56:29] local.INFO: 4.8062751293182  
[2015-11-30 13:56:34] local.INFO: 4.8232760429382  
[2015-11-30 13:56:39] local.INFO: 4.5732619762421  
[2015-11-30 13:56:43] local.INFO: 4.7092700004578  
[2015-11-30 13:56:48] local.INFO: 4.9792850017548  
[2015-11-30 13:56:53] local.INFO: 4.8652780056  
[2015-11-30 13:56:58] local.INFO: 4.9532828330994  
[2015-11-30 13:57:03] local.INFO: 4.9182820320129
.................................................
[2015-11-30 14:03:40] local.INFO: 4.8382771015167  
[2015-11-30 14:03:45] local.INFO: 4.7792727947235  
[2015-11-30 14:03:50] local.INFO: 4.9002799987793  
[2015-11-30 14:03:54] local.INFO: 4.7752728462219  
[2015-11-30 14:03:59] local.INFO: 4.8892788887024  
[2015-11-30 14:04:04] local.INFO: 4.7812731266022  
[2015-11-30 14:04:09] local.INFO: 4.9482831954956  
[2015-11-30 14:04:14] local.INFO: 5.0152869224548  
[2015-11-30 14:04:19] local.INFO: 5.0332868099213  
[2015-11-30 14:04:19] local.INFO: Total:494.97931194305  

每次生成5000条数据并批量插入,共插入50万条数。每次插入5000,耗时5秒左右,看起来很不满意,实际是时间应该耗费在产生数据的计算上,真正耗费在插入的事件,应该在毫秒甚至微秒级别。而且可以看到,耗费的时间,不会因为记录量上升而上升,都维持在5秒左右,这个本身就说明插入的时间非常微小。总耗时495秒,大概8分钟,MySQL的批量插入性能还可以可以肯定的。左右对比也不会差到什么地方。

如果以上的程序由于需要产生随机数据耗费了过多时间而无法准确评估,那么可以使用如下SQL来试试:

[SQL]INSERT INTO list_test_copy(name,cat1,cat2,cat3,num1,num2,num3,num4,num5) 
	SELECT name,cat1,cat2,cat3,num1,num2,num3,num4,num5 FROM list_test

受影响的行: 500000
时间: 31.640s

这个结果应该能比较准确的评估当前MySQL的性能,50w条记录批量插入,耗时31.64秒,平均每5000条耗时是0.3164秒,所有以上的5秒时间,有4秒多是用于产生随机数据的。

再来做一个全量查询:

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id

受影响的行: 0
时间: 12.295s

这是一个50万的表,LEFT JOIN一个40万的表,共耗时12.295秒。以下来个大偏移,翻到49万条记录:

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 100000

受影响的行: 0
时间: 2.395s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 200000

受影响的行: 0
时间: 3.592s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 300000

受影响的行: 0
时间: 4.735s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 400000

受影响的行: 0
时间: 7.568s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 490000

受影响的行: 0
时间: 11.576s

看起来不怎么满意?好吧,试试大偏移,但是取少来记录的情况吧:

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 100000,100

受影响的行: 0
时间: 0.475s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 200000,100

受影响的行: 0
时间: 0.645s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 300000,100

受影响的行: 0
时间: 2.706s

[SQL]SELECT l.name, l.cat1, l.cat2, l.cat3,l.num1,l.num2,l.num3,l.num4,l.num5, 
	(l.num1 - ll.num1) AS diff1,
	(l.num2 - ll.num2) AS diff2,
	(l.num3 - ll.num3) AS diff3,
	(l.num4 - ll.num4) AS diff4,
	(l.num5 - ll.num5) AS diff5,
IF(ll.id IS NULL,1,0)
FROM list l LEFT JOIN list_ ll ON l.id = ll.id LIMIT 400000,100

受影响的行: 0
时间: 5.984s

可以看到20万前偏移量,秒级完成。(我这是Windows,同等条件下,在Linux下提升一个数量级不夸张)。偏移量到40w,花费6秒,就是定位这个偏移花费了怎么多时间,这个时间就不太满意了。不过翻页的话,给10万条让你翻,就搞不过来了。

下面试试档表的大偏移:

[SQL]SELECT * FROM list_copy_copy LIMIT 100000,200

受影响的行: 0
时间: 0.449s

[SQL]SELECT * FROM list_copy_copy LIMIT 200000,200

受影响的行: 0
时间: 0.534s

[SQL]SELECT * FROM list_copy_copy LIMIT 300000,200

受影响的行: 0
时间: 0.577s

[SQL]SELECT * FROM list_copy_copy LIMIT 400000,200

受影响的行: 0
时间: 0.680s

[SQL]SELECT * FROM list_copy_copy LIMIT 499000,200

受影响的行: 0
时间: 0.774s
[/sql]
这个单表的大数据偏移,还是比较满意的。

以上数据只是在我个人计算机上做的简单测试,而且是Windows环境。

用到的测试表结构:

CREATE TABLE `list` (
`id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat1`  varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat2`  varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat3`  varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`num1`  int(11) NULL DEFAULT NULL ,
`num2`  int(11) NULL DEFAULT NULL ,
`num3`  decimal(5,2) NULL DEFAULT NULL ,
`num4`  float(64,20) NULL DEFAULT NULL ,
`num5`  float(64,20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;

CREATE TABLE `list_copy` (
`id`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat1`  varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat2`  varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`cat3`  varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL ,
`num1`  int(11) NULL DEFAULT NULL ,
`num2`  int(11) NULL DEFAULT NULL ,
`num3`  decimal(5,2) NULL DEFAULT NULL ,
`num4`  float(64,20) NULL DEFAULT NULL ,
`num5`  float(64,20) NULL DEFAULT NULL ,
`diff1`  int(11) NULL DEFAULT NULL ,
`diff2`  int(11) NULL DEFAULT NULL ,
`diff3`  decimal(10,2) NULL DEFAULT NULL ,
`diff4`  float(64,20) NULL DEFAULT NULL ,
`diff5`  float(64,20) NULL DEFAULT NULL ,
`is_new`  tinyint(2) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;

使用Zend\Db批量插入数据范例

<?php
$rootPath = dirname(__DIR__);
$library = $rootPath."/library";

$includePath = array(
    $library,
    get_include_path()
);
set_include_path(implode(PATH_SEPARATOR, $includePath));

chdir($rootPath);
if(file_exists('vendor/autoload.php')){
    $loader = include '/vendor/autoload.php';
}else{
    exit("Autoload Failed. ");
}
//$loader->setUseIncludePath(true);
$loader->setPsr4("Zend\\",$library.'/zf2_psr4');

$adapter = new Zend\Db\Adapter\Adapter(array(
    'hostname' => 'localhost',
    'database' => 'test',
    'username' => 'root',
    'password' => '',
    
    'driver' => 'mysqli',
    'charset' =>'utf8'
    
    //'driver' => 'pdo_mysql',
    //'driver_options' => array(
    //  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    //),
));

header("Content-type:text/html;charset=utf-8");

$connection = $adapter->getDriver()->getConnection();
$resource = $connection->getResource();

$valuss = [];
for($i = 0; $i < 100000; $i++) {
    $row   = [];
    $row[] = "'".$resource->real_escape_string('\'s vfeelit\\')."'";
    $row[] = "'".$resource->real_escape_string("sku".$i)."'";
    $row[] = "'".$resource->real_escape_string(round(($i+1)/3,2))."'";
    $row[] = "'".$resource->real_escape_string("仓库? 'd".$i)."'";
    $row[] = "'".$resource->real_escape_string("供应:商".$i)."'";
    $row[] = "'".$resource->real_escape_string("链接\\".$i.'\\')."'";
    $row[] = "'".$resource->real_escape_string("采购员".$i.'dd')."'";
    $valuess[] = "(".implode(",",$row).")";
}

$s = microtime(true);
$rs = $adapter->query("INSERT INTO `product`(`name`,`sku`,`purchase_price`,`warehouse_name`,`supplier_name`,`purchase_link`,`who_purchase`) values".implode(",",$valuess),Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
$e = microtime(true);
echo "100000 Record into DB:".(float)($e-$s)."\n";

批量插入数据数据是非常快的。不过可能遇到MySQL server has gone away异常,这种异常要么就是数据库链接异常断了,要么就是发送的包太大,这里就是数据包太大导致无法解析。

show global variables like 'max_allowed_packet';
set global max_allowed_packet=1024*1024*16; #改大

补注:
在新版本的Zend Framework中(2.4.8),Zend\Db\Adapter\Platform\Mysql的quoteValue改为如下:

    public function quoteValue($value)
    {
        if ($this->resource instanceof DriverInterface) {
            $this->resource = $this->resource->getConnection()->getResource();
        }
        if ($this->resource instanceof \mysqli) {
            return '\'' . $this->resource->real_escape_string($value) . '\'';
        }
        if ($this->resource instanceof \PDO) {
            return $this->resource->quote($value);
        }
        return parent::quoteValue($value);
    }

填补了之前的Bug。现在是直接调用Resource的具体方法。

MySQL 技术内幕:InnoDB存储引擎 笔记

第一章 MySQL体系结构和存储引擎
1.1 定义数据库和实例
>数据库:物理操作系统文件或其它形式文件类型的集合。在MySQL中,数据库文件可以是frm、myd、myi、ibd结尾的文件。当使用NDB引擎时,数据库文件可能不是操作系统上的文件,而是存放于内存之中的文件。
>数据库实例:由数据库后台进程/线程以及一个共享内存区组成。共享内存可以被运行的后台进程/线程所共享。数据库实例才是真正用来操作数据库文件的。

在MySQL中,实例和数据库通常关系是一一对应(这里说的数据是指DBMS,不是指具体的库)。但是在集群情况下可能存在一个数据库被多个实例使用的情况。MySQL被设计为一个单进程多线程架构的数据库,也就是说,MySQL数据库实例在系统上的表现就是一个进程。

#启动MySQL数据库实例
./mysqld_safe &

当启动实例时,MySQL数据库会去读取配置文件,根据配置文件的参数来启动数据库实例。在MySQL中,可以没有配置文件,这个情况,MySQL会按照编译时默认参数设置启动实例。用如下命令查看实例启动时,会去哪些位置查找配置文件(Linux下配置文件为cnf,Windows下为ini):

./mysql –help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

MySQL按照如上顺序读取配置文件。如果配置文件中都有同一个参数,MySQL会以读取到的最后一个配置文件中的参数为准。

配置文件中有一个datadir参数,该参数指定了数据库所在的路径。在Linux下,datadir默认为/usr/local/mysql/data(也不一定,决定安装方式)。这个路径可能是一个链接文件,必须保证MySQL的用户和用户组可以操作这个目录(读写执行)。

1.2 MySQL体系结构

MySQL由以下几部分组成:
链接池组件,管理服务和工具组件, SQL接口组件, 查询分析器组件,优化器组件,缓冲(Cache)组件,插件式存储引擎,物理文件

MySQL区别于其他数据库的最重要的特点是其插入式的表存储引擎。MySQL插入式的存储引擎架构提供了一系列标准的管理和服务支持,这些标准与存储引擎本身无关,可能是每个数据库系统本身都必需的,如SQL分析器和优化器等。而存储引擎是底层物理结构的实现。(存储引擎是基于表的,而不是数据库)

1.3 MySQL表存储引擎
由于MySQL数据库的开源特性,用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。

1.3.1 InnoDB存储引擎
InnoDB存储引起支持事务,主要面向在线事务处理方面的应用(OLTP)。其特点是行锁设计、支持外键,并支持类似于Oracle的非锁定读,即默认情况下读取操作不会产生锁。从MySQL5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。InnoDB存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由InnoDB自身进行管理。每个InnoDB存储引擎的表也可以单独存放到一个独立的ibd文件中,还可以使用裸设备(row disk)来建立其表空间(文件系统)。

InnoDB通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4种隔离级别,默认为REPEATABLE级别。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。除此,InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive has index)、预读(read ahead)等高性能和高可用的功能。

对于表中数据库的存储,InnoDB存储引擎采用聚集(clustered)的方式。每张表的存储都按主键的顺序存放,如果没有显示地在表定义时指定主键,InnoDB存储引擎会为每一行生成一个6字节的ROWID,并以此作为主键。

1.3.2 MyISAM存储引擎
不支持事务、表锁和全文索引,主要面向一些OLAP数据库应用。MyISAM存储引擎的缓冲池只缓存索引文件而不缓冲数据文件,这和大多数数据库都非常不同。MyISAM存储引擎表有MYD和MYI组成,MYD用来存放数据文件,MYI用来存放索引文件。

1.3.3 NDB存储引擎
NDB的特点是数据全部放在内存中(从5.1开始,可以将非索引数据库放在磁盘上),因此主键查找速度极快,并且通过添加NDB数据存储节点可以线性地提供数据库性能,是高可用,高性能的集群系统。

1.3.4 Memory存储引擎
默认使用哈希索引。只支持表锁,并发性能较差,不支持TEXT和BLOB列类型。存储变长字段(varchar)时是按照定长字段(char)的方式进行的。MySQL数据库使用Memory存储引擎作为临时表存放查询中间结果集,如果中间结果集大于Memory存储引擎表的容量设置,或中间结果含有TEXT或BLOB列类型字段,则MySQL数据库会把其装换到MyISAM存储引擎表而存放到磁盘(MyISAM不缓存数据库文件,性能下降)。

1.3.5 Archive存储引擎
1.3.6 Federated存储引擎
1.3.7 Maria存储引擎
设计目标主要是用来取代原有的MyISAM存储引擎,可以看作是MyISAM的后续版本。特定:缓存数据和索引文件(MyISAM不缓存数据),行锁设计(MyISAM表锁),提供MVCC功能(MyISAM无),支持事务和非事务安全的选项支持(MyISAM不支持),以及更好的BLOB字符类型的处理性能。
1.3.7 其他存储引擎
1.4 各种存储引擎之间的比较
容量限制(Memory InnoDB 64TB限制) 事务支持 锁的粒度(Memory表锁) MVCC支持 支持的索引(Memory InnoDB都支持哈希和B+树索引) 备份和复制

show engines\G;

1.5 链接MySQL
本质是进程通信。常用进程通信方式有管道、命名管道、命名字、TCP/IP套接字、Unix域名套接字。
1.5.1 TCP/IP
通过TCP/IP连接到MySQL实例时,MySQL数据库会先检查权限视图(mysql库的user表),判断客户端IP是否允许连接。
1.5.2 命名管道
Windows系列….。在MySQL数据库中,需在配置中启用—enable-named-piple选项。MySQL4.1之后,MySQL还提供了共享内存的链接方式,在配置文件中添加—shared-memory。如果想使用共享内存的方式,在链接时,MySQL客户端还不行使用—protocal=memory选项。
1.5.3 Unix域套接字
在配置文件中指定套接字文件的路径,如:–socket=/tmp/mysql.sock。

第2章 InnoDB存储引擎
2.1 InnoDB存储引擎概述
InnoDB存储引擎是第一个完整支持ACID事务的MySQL存储引擎,其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读。
2.2 InnoDB存储引擎的版本
2.3 InnoDB体系架构
InnoDB有多个内存块,这些内存块组成了一个大的内存池,负责:
>为何所有进程/线程需要访问的多个内部数据结构
>缓存磁盘上的数据,方便快速地读取,并且在对磁盘文件的数据进行修改之前在这里缓存
>重做日志缓冲
…….
mysqlinnodb
后台线程的主要作用是负责刷新内存池中的数据,保存缓冲池中的内存缓存的是最近的数据。将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

2.3.1 后台线程
InnoDB存储引擎是多线程的模型,后台有多个不同的后台线程,负责处理不同的任务。
1.Master Thread
Master Thread是一个非常核心的后台线程,主要负责将缓冲池中的数据库一步刷新到磁盘,保证数据的一致性,包括脏页的刷新、合并插入缓冲、UNDO页的回收等。
2 IO Thread
在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求。IO Thread的工作主要是负责这些IO请求的回调处理。从InnoDB1.0.x版本开始,read thread和write thread分别增大到了4个,并且不再使用innodb_file_io_threads参数,而分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置:

show variables like 'innodb_%io_threads'\G

#观察InnoDB的IO Thread
show engine innodb status\G

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

IO Thread 0为insert buffer thread。IO Thread 1为log thread。之后是innodb_read_io_threads和innodb_write_io_threads参数设置的读写线程。
3、Purge Thread
事务被提交后,其所使用的undolog可能不再需要,因此需要PurgeThread来回收已经使用并分配的undo页。在InnoDB1.1版本之前,purge操作仅在InnoDB存储引擎的Master Thread中完成。之后,purge操作可以独立到单独的线程中进行,在配置文件中添加如下命令来启用独立的Purge Thread:

innodb_purge_threads=1

4、Page Cleaner Thread
脏页刷新操作放入到单独的线程中完成

2.3.1 内存
1 缓冲池
InnoDB存储引擎是基于磁盘存储的,并将其中的纪律按照页的方式进行管理。基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。(一般过程就是把数据读入缓冲池,读数据时先从缓冲池读,对于写,首先修改缓冲池中的页,再以一定的频率刷新到磁盘上),注意:页从缓冲池刷新回磁盘的操作并不是每次也发生更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘。

缓冲池的大小直接影响着数据库的整体性能。对于InnoDB,其缓冲池的配置通过参数innodb_buffer_pool_size来设置:

show variables like 'innodb_buffer_pool_size'\G

缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓存(insert bufeer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等。

从InnoDB 1.0.x版本开始,允许有多个缓冲池实例。每个页根据哈希平均分配到不同缓冲池实例中。通过innodb_buffer_pool_instances来进行配置:

show variables like 'innodb_buffer_pool_instances'\G

通过命令show engine innodb status可以观察到每个缓冲池实例对象运行的状态。MySQL5.6开始,还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态:

SELECT POOL_ID, POOL_SIZE, Free_BUFFERS, DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS\G

*************************** 1. row ***************************
       POOL_ID: 0
     POOL_SIZE: 8191
  Free_BUFFERS: 4188
DATABASE_PAGES: 3917
1 row in set (0.00 sec)

2 LRU List、Free List和Flush List
通常,数据库中的缓冲池通过LRU算法来进行管理。InnoDB对LRU算法进行了改进。。。。(这里涉及到InnoDB的几个配置项)

3 重做日志缓冲
InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置很大,因为一般情况下每一秒会将重做日志缓冲刷新到日志文件。由配置参数innodb_log_buffer_size控制,默认为8M(满足绝大部分应用)。

4 额外的内存池

show variables like ‘innodb_buffer_pool_size’\G;
show variables like ‘innodb_log_buffer_size’\G;
show variables like ‘innodb_additional_mem_pool_size’\G

2.4 Checkpoint技术
为了避免发生数据丢失的问题,当前事务数据库系统普遍都采用了Write Ahead Log策略,即当事务提交时,先写重做日志,在修改也(ACID中的D)。
Checkpoint(检查点,保持一个点,点之后的是需要刷新的数据)技术的目的解决以下问题:
缩短数据库的恢复时间;缓冲池不够用时,将脏页刷新到磁盘;重做日志不可用时,刷新脏页。

这里描述的原理设计到几个配置参数。

2.5 Master Thread工作方式
2.5.1 InnodB 1.0.x版本之前
2.5.2 InnodB 1.2.x版本之前
2.5.3 InnodB 1.2.x版本的Master Thread
对于刷新脏页的操作,从Master Thread线程分离到一个单独的Page cleaner Thread。

2.6 InnoDB关键特性
2.6.1 插入缓冲
1 Insert Buffer
2 Change Buffer
3 Insert Buffer的内部实现
4 Merge Insert Buffer
2.6.2 两次写
2.6.3 自适应哈希索引
2.6.4 异步IO
2.6.5 刷新邻接页

2.7 启动、关闭与恢复
在关闭时,参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。

第3章 文件
3.1 参数文件
如果MySQL在默认的数据库目录下找不到mysql架构(必须存在),则启动会失败。
3.1.1 什么是参数
从MySQL 5.1开始,可以通过information_schema架构下的GLOBAL_VARIABLES视图进行查找。
3.1.2 参数类型
MySQL参数文件中的参数可以分为:动态参数和静态参数。可以通过SET命令对动态参数值进行修改。
3.2 日志文件
3.2.1 错误日志
通过show variables lik ‘log_error’来定位该文件。默认情况下错误文件的文件名为服务器的主机名。
3.2.2 慢查询日志
默认情况下,MySQL数据库并不启动慢查询日志。MySQL5.1开始可以将慢查询的日志记录放入一张表中,慢查询表在mysql架构下,名为slow_log。
3.2.3 查询日志
默认文件名为:主机名.log。从MySQL5.1开始,可以将查询日志的记录放入到mysql架构的general_log表。
3.2.4 二进制日志
二进制日志记录了对数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身没有修改。此外,二进制还包括了执行数据库更改操作的时间和执行时间等信息。二进制日志主要有以下两种作用:恢复 和 复制。

通过配置参数log-bin[=name]可以启动二进制日志。如果不指定name,则默认二进制日志文件名为主机名,后缀名为二进制日志的序列号,所在路径为数据库所在目录(datadir)。

以下配置参数影响二进制日志记录的信息和行为:
max_binlog_size
binlog_cache_size
sync_binlog
binlog-do-db
binlog-ingore-db
log-slave-update
binlog_format
参数max-binlog-size指定了单个二进制日志文件的最大值,如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到.index文件。从MySQL5开始默认值是1G。

当使用事务的表存储引擎时,所有未提交的二进制日志会被记录到一个缓存中,等该事务提交时直接将缓存中的二进制日志吸入二进制日志文件,而该缓存的大小有binglog_cache_size决定,默认大小为32KB。此外,binglog_cache_size是基于会话的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小binglog_cache_size的缓存,因此这个值不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设置的太小。通过show global status命令查看binlog_cache_use(使用缓存次数)、binlog_cache_disk_use(使用临时文件次数)的状态,可以判断当前binlog_cache_size的设置是否合适。

默认情况下,二进制日志并不是在每次写的时候同步到磁盘(缓冲写)。因此,当发生突然停机时,可能会有最后一部分数据没有写入二进制日志文件中。这回给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N改为1,表示采用同步写磁盘的方式来写二进制日志(写一次就同步)。该默认值为0。

参数binlog-do-db和binlog-ignore-db表示需要写入或忽略写入那些库日志。默认为空。

如果当前数据库是复制中的slave角色,则他不会将从master取得并执行的二进制日志写入自己的二进制日志文件中,如果需要写入,则需要设置log-slave-update。如需要搭建master=>slave=>slave的复杂,则必须设置该参数。

MySQL 5.1开始引入了binlog_format参数,该参数可设的值有STATEMENT ROW和MIXED。通常,将参数binlog_format设置为ROW,这可以为数据库的恢复和复制带来更好的可靠性。

想要查看二进制日志文件的内容,须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件,使用mysqlbinlog后,看到就是执行的逻辑SQL语句。

3.3 套接字文件
3.4 pid文件
3.5 表结构定义文件
不论采用何种存储引擎,MySQL都有一个以frm为后缀的文件,这个文件记录了该表的表结构定义。frm还用来存放视图的定义。
3.6 InnoDB存储引擎文件
3.6.1 表空间文件
默认配置下,会有一个初始化大小为10MB,名为ibdata1的文件。该文件就是默认的表空间文件。可以通过参数innodb_data_file_path对其进行设置。也可以用多个文件组成一个表空,同时制定文件的属性:

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里讲两个文件来组成表空间。若两个文件位于不同的磁盘上,则可以对性能带来一定程度的提升。两个文件都跟了属性,表示大小,如果用满,该文件可以自动增长(autoextend)。

设置了innodb_data_file_path参数后,之后对于所有基于InnoDB存储引擎的表的数据都会记录到该文件内。而通过设置参数innodb_file_per_table,可以将每个基于InnoDb存储引擎的表单独产生一个表空间,文件名为表名.ibd,这样不用将所有数据都存放在默认的表空间中。

第4章 表
4.1 InnoDB存储引起表类型
4.2 InnoDB逻辑存储结构
所有数据都被咯及地存放在一个空间,称之为表空间,表空间有由段 区 页组成。
4.2.1 表空间
对于启用了innodb_file_per_table的参数选项,每张表的表空间内存放的只是数据集、索引和插入缓冲,其它类的数据,如撤销信息、系统事务信息、二次写缓冲等还是存放在原来的共享表空间内。
4.2.2 段
4.2.3 区
区是由64个连续的也组成的,每个页大小为16KB,即每个区的大小为1MB。对于大数据段,InnoDB存储引擎最大每次可以申请4个区,以此来保证数据的顺序性能。
4.2.4 页
4.2.5 行

4.3 InnoDB物理存储结构
从物理上看,InnoDB表有共享表空间、日志文件组、表结构定义文件组成。若innodb_file_per_table设置为on,则每个表将独立地产生一个表空间文件,以ibd结尾,数据、索引、表的内部数据字典信息都将保持在这个单独的表空间文件中。表结构定义文件以frm结尾,这个与存储引擎无关。

4.4 InnoDB行记录格式
4.5 InnoDB数据页结构
4.6 Named File Formats
4.7 约束
4.8 视图
4.9 分区表
这个内容与另一本书的内容重复。

3.6.2 重做日志文件
默认情况下会有两个文件,名称分别为ib_logfile0和ib_logfile1。重做日志文件对于InnoDB存储引擎至关重要,它们记录了对于InnoDB存储引擎的事务日志。

第5章 索引与算法
5.1 InnoDB存储引擎索引概述
InnoDB存储引擎支持:(B+树索引 全文索引 哈希索引)
InnoDB存储引擎支持的哈希索引是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引。B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页面。然后把页读入到内存,再在内存中进行查找。
5.2 数据结构与算法
5.2.1 二分查找法
5.2.2 二叉查找树和平衡二叉树(AVL树)
二叉树可以任意构建,由此引入了平衡二叉树(任何节点的两个子树的高度最大差为1),平衡二叉树的查询速度很快,但维护一颗平衡二叉树的代价非常大。
5.3 B+树
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按键值的大小顺序放在同一层的叶子节点上,由各叶子节点指针进行连接。
5.3.1 B+树的插入操作
B+树的插入必须保证插入后叶子节点中的记录依然排序。(比如一个颗4层的B+树,最后一层是叶子节点,存放数据,前三层是非叶子节点,存放索引,当存放索引的节点满时,需要增加,当叶子节点满时需要增加叶子节点)
5.3.2 B+树的删除操作
5.4 B+树索引
在数据库中,B+树的高度一般都在2-4层,所以查找某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index),但其内部都是B+树(即高度平衡的,叶子节点存放所有的数据)。聚集索引与辅助索引不同的是,聚集索引存放的是整行的信息。

5.4.1 聚集索引
聚集索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的即为整张表的行记录数据(多行),也将聚集索引的叶子节点称为数据页。由于实际的数据页只能按照一个B+树进行排序,因此每张表只能拥有一个聚集索引。
5.4.2 辅助索引
对于辅助索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引中还包含了一个书签,该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
5.4.3 B+树索引的分裂(B+树的维护方面的问题)
5.4.4 B+树索引的管理
5.5 Cardinality值
5.6 B+树索引的使用
5.6.1 不同应用中B+树索引的使用
5.6.2 联合索引
5.6.3 覆盖索引
要查找的数据直接可以从索引树中取回,不需要二次搜索,比如直接查询ID,或直接查找索引,这些数据查找索引树本身就可以得到,所谓的覆盖索引。
5.6.4 优化器选择不使用索引的情况
通过辅助索引查找的数据是少量的,优化器选择辅助索引。(当扫描辅助索引得到表的很多数据,然后还要根据这个索引去聚集索引中扫描取回数据,这个取回操作是离散的,索引还不如直接全部扫描)
5.6.5 索引提示
MySQL数据库支持索引提示(Index Hint),显式地告诉优化器使用哪个索引。
5.7 哈希算法
5.8 全文检索

第6章 锁
6.1 什么是锁
数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。

InnoDB存储引擎锁提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销,并可以同时得到并发性和一致性。
6.2 lock与latch
6.3 InnoDB存储引擎中的锁
6.3.1 锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁:
共享锁(S Lock),允许事务读一行数据。
排他锁(X Lock),允许事务删除或更新一行数据。

X锁与任何的锁都不兼容,而S锁仅和S锁兼容。

三个表,与事务,锁,锁等待相关。
6.3.2 一致性非锁定读
简单来说就是行被锁时,会保存一个快照(undo),在读这行时不会被堵塞,而是读取快照数据。

在REPEATABLE READ事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。
6.3.3 一致性锁定读
InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读:
SELECT FOR UPDATE
SELECT LOCK IN SHARE MODE(加S锁)
以上语句需要套入一个事务中。
6.3.4 自增长与锁
6.3.5 外键和锁

6.4 锁算法
6.4.1 行锁的3种算法
Record Lock
Gap Lock 锁定一个范围,但不包含记录本身
Next-Key Lock Gap Lock + Record Lock,锁定一个范围,包含记录本身

Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。Gap Lock 的作用是为了阻止多个事务将记录插入到同一范围内。

在InnoDB存储引擎中,对于INSERT的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许插入。

6.4.2 解决Phantom Problem
Phantom Problem是指在同一个事务下,连续执行两次同样的SQL语句可能导致不同的结果。

6.5 锁问题
6.6 堵塞
InnoDB存储引擎中,参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒),innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是Off,代表不回滚)。

当发生超时时,MySQL数据库会抛出一个1205的错误。在默认情况下InnoDB存储引擎不会回滚超时引发的错误一异常。实际上InnoDB存储引擎在大部分情况下都不会对异常进行回滚。(注:检测到死锁时会自动回滚权重的低的事务)
6.7 死锁
6.7.1 死锁概念
死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。

除了超时机制,当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。
6.7.2 死锁概率
6.7.3 死锁的示例

6.8 锁升级
锁升级(Lock Escalation)是指将当前锁的粒度降低。如行锁升级为一个页锁,或将页锁升级为表锁。InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的,其根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常都是一致的。

第7章 事务
InnoDB存储引擎中的事务完全符合ACID的特性:
原子性(atomicity)
一致性(consistency)
隔离性(isolation) – 锁
持久性(durability)

7.1 认识事务
事务严格的定义必须同时满足四个特性(ACID)。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE,完全遵循和满足事务的ACID特性。

7.1.2 分类
从事务理论角度:
扁平事务(Flat Transactions) – 使用最多
带有保存点的扁平事务(Flat Transactions with Savepoints)
链接事务(Chained Transactions)
嵌套事务(Nested Transactions)
分布式事务(Distributed Transactions)

InnoDB存储引擎支持扁平事务,带有保存点的扁平事务,链接事务和分布式事务

7.2 事务的实现
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志,用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
7.2.1 redo
7.2.1 undo
7.2.3 purge
7.2.4 group commit

7.3 事务控制语句
在MySQL命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后马上执行COMMIT操作。要显式开启一个事务需要使用命令BEGIN、START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,禁用当前会话的自动提交。

BEGIN(START TRANSACTION) 在存储过程中只能用START TRANSACTION(BEGIN被存储过程保留)
COMMIT(COMMIT WORK)
ROLLBACK(ROLLBACK WORK)
SAVEPOINT identifier: 创建一个保存点,一个事务中可以有多个SAVEPOINT
RELEASE SAVEPOINT identifier: 删除一个事务的保存点
ROLLBACK TO[SAVEPOINT]identifier: 和语句SAVEPOINT命令一起使用,把事务回滚到标记点。

SET TRANSACTION: 设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ(InnoDB默认), SERIALIZABLE.

7.4 隐式提交的SQL语句
7.5 对于事务操作的统计
7.6 事务的隔离级别
InnoDB存储引擎默认支持的隔离级别是REPEATABLE READ(没有幻读保护),与标准SQL不同的是,InnoDB存储引擎在REPEATABLE READ事务隔离级别下,使用Next-Key Lock锁算法,隐藏避免幻读的产生。

隔离级别越低,事务请求的锁越少或保存锁的时间就越短(大部分数据库默认隔离级别是READ COMMITTED)。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

#配置文件
transaction-isolation = READ COMMITTED

#查看
SELECT @@tx_isolation\G
SELECT @@global.tx_isolation\G

7.7 分布式事务
7.8 不好的事务习惯
7.8.1 在循环中提交
7.8.2 使用自动提交
7.8.3 使用自动回滚

7.9 长事务

第8章 备份与恢复
8.1 备份与恢复概述
Hot Backup
Cold Backup
Warm Backup(加一个全局读写)
8.2 冷备
对应InnoDB存储引擎的冷备,只需要备份MySQL数据库的frm文件,共享表空间文件,独立表空间文件(*。ibd),重做日志文件。另外,定期备份MySQL数据库的配置文件my.cnf。
8.3 逻辑备份
8.4 二进制日志备份与恢复
8.5 热备
8.6 快照备份
8.7 复制

第9章 性能调优
9.1 选择合适的CPU
数据库应用:OLTP(Online Transaction Processing,在线事务处理)和OLAP(Online Analytical Processing, 在线分析处理)。

OLAP是CPU密集型的操作,而OLTP是IO密集型的操作。如果CPU是多核的,可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程。

9.2 内存的重要性
9.3 硬盘对数据库性能的影响
9.4 合理地设置RAID
9.5 操作系统的选择
9.6 不同的文件系统对数据库性能的影响
9.7 选择合适的基准测试工具

MySQL技术内幕:SQL编程 笔记

MySQL技术内幕:SQL编程

第一章 SQL编程
第二章 数据类型
2.1 类型属性
2.1.1 UNSIGNED
UNSIGNED属性就是将数字类型无符号化。INT类型取整范围-2147483648~-2147483647,32位,一个符号位。如果是无符号INT,取值就是0~4294967295。

MySQL中,对于UNSIGNED数的操作,其返回值都是UNSIGNED的。尽量不用UNSIGNED,如果超过INT的表示范围,可以使用BIGINT(64位,包含一符号位)。

2.1.2 ZEROFILL
在创建数字字段时,后面会紧跟一个数字,比如int(5),如果没有ZEROFILL时,这个括号中的数字没有意义。对列添加了ZEROFILL属性后,表示如果长度不够,将使用0进行填充(仅对显示有影响)。比如123,显示为00123。(注意,通过工具为字段设置ZEROFILL后,同时添加了UNSIGNED,说明这个填充行为针对无符号数)

2.2 SQL_MODE设置
SQL_MODE默认为空。生成环境中建议是严格模式。可以避免NULL插入NOT NULL的字段中,非法日期等。SQL_MODE可以在my.cnf和客户端工具中进行。

mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql> select @@session.sql_mode \G
*************************** 1. row ***************************
@@session.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

2.3 日期和时间类型
日期时间应该使用YYYY-MM-DD HH:MM:SS格式输入。MySQL5.5之前(包含),MySQL不能输入微秒级别(被截断)。MySQL提供了MICROSECOND来提供日期中的微秒值:

SELECT MICROSECOND('2015-09-15 00:01:10.123456')\G;

MySQL 5.6.4开始,MySQL增加了对秒的小数部分(fractional second)的支持,语法:type_name(fsp),type_name的类型可以是TIME、DATETIME和TIMESTAMP,fsp表示支持的小数部分的精度,最大为6,表示微秒,默认为0,表示没有小数部分。

TIMESTAMP和DATETIME显示的结果是一样的,都是固定的“YYYY-MM-DD HH:MM:SS”的形式。不同的是,TIMESTAMP占用4个字节,显示范围为“1970-01-01 00:00:00到2038-01-19 03:14:07”UTC。实际存储的内容为1970-01-01 00:00:00到当前时间的毫秒数。

区别:建表时,列为TIMESTAMP的日期类型可以设置一个默认值,而DATETIME不行;更新表时,可以设置TIMESTAMP类型的列自动更新时间为当前时间。

<?php
date_default_timezone_set("UTC"); 	 	//转换为UTC
echo PHP_INT_SIZE."\n";			 	//4,4个字节
echo PHP_INT_MAX."\n";			 	//2147483647,最大整数,31位
echo date("Y-m-d H:i:s",2147483647)."\n";	//2038-01-19 03:14:07,最大整数变成时间
echo strtotime("1970-01-01 00:00:00")."\n";	//0,时间戳开始为0
echo strtotime("2038-01-19 03:14:07");		//2147483647,最大时间,为最大整数

如果对来自MySQL的时间戳进行取整操作,不会产生截断问题,因为整数的大小跟PHP中的一致。

日期和时间相关函数
NOW CURRENT_TIMESTAMP和SYSDATE

DATE_ADD 和 DATE_SUB

DATE_FORMAT

2.4 关于日期的经典SQL编程问题
2.5 数字类型
2.5.1 整型
INT 4字节
SMALLINT 2字节
TINYINT 1字节
MEDIUMINT 3字节
BIGINT 8字节

ZEROFILL可以格式化显示整型,一旦启用ZEROFILL属性,MySQL数据库为列自动添加UNSIGNED属性。

2.5.2 浮点型
2.5.3 高精度类型
DECIMAL和NUMERIC类型在MySQL中被视为相同的类型,用于保存必须为确切精度的值。在标准SQL中,语法DECIMAL(M)等价于DECIMAL(M,0)。在MySQL 5.5中,M的默认值为10。

DECIMAL和NUMERIC的最大位数是65,但具体的DECIMAL和NUMERIC列的实际范围受具体列的精度和标度约束。

2.5.4 位类型
位类型,即BIT数据类型可用来保存字段的值。BIT(M)类型表示运行存储M位数值,M范围为1到64,占用的空间为(M+7)/8字节。如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充。

要指定位值,可以使用b’value’:

INSERT INTO t SELECT b'1000';

直接使用SELECT进程查看会出现空白情况,需要转化:

SELECT HEX(a) FROM t;

2.6 关于数字的经典SQL编程问题
2.7 字符类型
2.7.1 字符集
通过命令SHOW CHARSET可以查看MySQL数据库支持的字符集。MySQL 5.5版本支持39个字符集。

MySQL 5.5数据库共支持ucs2、utf8(utf8mb3)、utf8mb4、utf16以及utf32五种Unicode编码,而5.5之前的版本只支持ucs2和utf8两种Unicode字符集。utf8目前被视为utf8mb3,即最大占用3个字节空间,而utf8mb4可以视为utf8mb3的扩展。对BMP字符的存储,utf8mb3和utf8mb4两者是完全一样的,区别只是utf8mb4对扩展字符的支持。

对于Unicode编码的字符集,建议将CHAR字段设置为VARCHAR字段,因为对于CHAR字段,数据库会报错最大可能的字节数(采用最大可能字节累加)。

MySQL5.5版本开始移除了参数default_character_set,取而代之的是参数character_set_server。

character_set_server=utf8

查看使用的字符集,可以使用STATUS命令。

命令SET NAMES可以用来更改当前会话连接的字符集、当前会话的客户端的字符集,以及当前会话返回结果集的字符集。

MySQL数据库可以细化每个对象字符集的设置。

2.7.2 排序规则
两个不同的字符集不能有相同的排序规则。每个字符集有一个默认的排序规则。有一些常用的命名规则。如果_ci结尾表示大小写不敏感(case insensitive),_cs表示大小写敏感(case sensitive),_bin表示二进制的比较(binary)。在MySQL中,可以通过命令SHOW COLLATION来查看支持的各种排序规则。字符是否大小写敏感,影响到字符比较(建索引时,返回结果,表字段)。

2.7.3 CHAR和VARCHAR
CHAR类型,N的范围为0~255,对于VARCHAR类型,N的范围为0~65535。CHAR(N)和VARCHAR(N)中的N都代表长度,而非字节长度。(这个根int后制定的数字完全两码事)

对于CHAR类型的字符串,MySQL数据库会自动对存储列的右边进行填充操作(取出时做相反操作)

VARCHAR类型存储变长字段的字符类型,与CHAR类型不同的是,其存储时需要在前缀列表加上实际存储的字符数,该字符占用1~2字节的空间。

2.7.4 BINARY和VARBINARY
2.7.5 BLOB和TEXT
BLOB(Binary Large Object)用来存储二进制大数据类型。
TINYBLOB BLOB MEDIUMBLOB LONGBLOB

TEXT类型:
TINYTEXT TEXT MEDIUMTEXT LONGTEXT

在大多数情况下,可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样,也可以将TEXT类型的列视为足够大的VARCHAR类型的列。区别:
BLOB和TEXT类型的列上创建索引时,必须制定索引前缀的长度。
BLOB和TEXT类型的列不能有默认值。
在排序时只使用列的钱max_sort_length个字节(默认1024)。

在数据库中,最小的存储单元是页(也可以称为块)。为了有效存储列类型为BLOB或TEXT的大数据类型,一般将列的值存放在行溢出页,而数据页存储的行数据只包含BLOB或TEXT类型数据列前一部分数据(大体上,就是页存放尽可能多的行,对BLOB或TEXT放入到所谓的溢出页,当然,中间需要有指针链接起来)

2.7.6 ENUM和SET类型
ENUM和SET类型,不同的是ENUM类型最多可枚举65536个元素,而SET类型最多枚举64个元素。由于MySQL不支持传统的CHECK约束,因此通过ENUM和SET类型并结合SQL_MODE可以解决一部分问题。

查询处理
3.1 逻辑查询处理
3.1.1 执行笛卡尔积
3.1.2 应用ON过滤器
下面两种情况下认为两个NUL值的比较是相等的:
GROUP BY子句把所有NULL值分到同一组
ORDER BY子句中把所有NULL值排列在一起
3.1.3 添加外部行
3.1.4 应用WHERE过滤器
3.1.5 分组
3.1.6 应用ROLLUP或CUBE
3.1.7 应用HAVING过滤器
3.1.8 处理SELECT列表
3.1.9 应用DISTINCT子句
3.1.10应用ORDER BY子句
3.1.11LIMIT子句
3.2 物理查询处理

子查询
4.1 子查询概述
4.2 独立子查询
4.3 相关子查询
4.4 EXISTS谓词
4.5 派生表
4.6 子查询可以解决的经典问题
4.7 MariaDB对SEMI JOIN的优化

第五章
联接与集合操作
5.1 联接查询
MySQL数据库支持如下的联接查询:
CROSS JOIN 交叉联接
INNER JOIN 内联接
OUTER JOIN 外链接
其它

5.1.1 新旧查询语法

SELECT * FROM A,B WHERE A.ID = B.ID
SELECT * FROM A INNER JOIN B ON A.ID = B.ID

5.1.2 CROSS JOIN
CROSS JOIN对两个表执行笛卡尔积,返回两个表中所有列的组合。
5.1.3 INNER JOIN
INNERR关键字可省略。INNER JOIN中的WHERE的过滤条件可以写在ON子句中。

注:在MySQL中,如果INNER JOIN后不跟ON子句(不出错),等同于CROSS JOIN,即产生笛卡尔积。

如果ON子句中的列具有相同的名称,可以使用USING子句来进行简化:

SELECT * FROM A INNER JOIN B ON A.ID = B.ID
//等同
SELECT * FROM A INNER JOIN B USING(ID)

就是ON A.ID = B.ID可以用USING(ID)来代替。

5.1.4 OUTER JOIN
MySQL数据库支持LEFT OUTER JOIN 和 RIGHT OUTER JOIN(OUTER可省略)。
通过OUTER JOIN和IS NULL,可以返回没有用户订单的客户:

SELECT c.customer_id FROM customers AS c LEFT JOIN order AS o 
ON c.customer_id = o.customer_id WHERE o.order_id IS NULL

注:INNER JOIN中的过滤条件都可以写在ON子句中,而OUTER JOIN的过滤条件不可以。OUTER JOIN必须给出ON子句,否则抛异常。
5.1.5 NATURAL JOIN
NATURAL JOIN等同于INNER JOIN与USING的组合,它隐含的作用是将两个表中具有相同名称的列进行匹配。同样,NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN于USING的组合。(自动寻找相同列,这个用法可xx)
5.1.6 STRAIGHT_JOIN
STRAIGHT_JOIN不是新的链接类型,而是用户对SQL优化器的控制,其等同于JOIN。通过STRAIGHT_JOIN,MySQL数据库会强制险读取左边的表。

5.2 其它联接分类
5.2.1 SELF JOIN

CREATE TABLE emp (
	emp_no int PRIMARY KEY,
	mgr_no int,
	emp_name varchar(30)
);

SELECT a.emp_name AS employee, b.emp_name AS manager
FROM emp a LEFT JOIN emp b ON a.mgr_no = b.emp_no

对于层次结构问题,通常可以通过SELF JOIN来解决。
5.2.2 NONEQUI JOIN
NONEQUI JOIN的联接包含“等于”运算符之外的运算符。
5.2.3 SEMI JOIN和ANTI SEMI JOIN
5.3 多表联接
5.4 滑动订单问题
5.5 联接算法
5.5.1 Simple Nested-Loops Join算法
5.5.2 Block Nested-Loops Join算法
5.5.3 Batched key Access Join算法
5.5.4 Classic Hash Join算法
5.6 集合操作
5.6.1 集合操作的概述
MySQL数据库支持两种集合操作:UNION ALL和UNION DISTINCT。与联接操作一样,集合操作也是对两个输入进行操作,并生成一个虚拟表。在联接操作中,一般把输入表称为左输入和右输入。集合操作的两个输入必须有相同的列数,若数据类型不同,MySQL数据库会自动将进行隐式转化。同时,结果列的名称由第一个输入决定。

注:在集合操作中,INTO OUTFILE只能存在于最后一个SELECT语句中,否则MySQL数据库会提示语法错误。

5.6.2 UNION DISTINCT和UNION ALL
UNION DISTINCT组合两个输入,并应用DISTINCT过滤重复项。一般省略DISTINCT关键字,直接用UNION。如果确认进行UNION操作的两个集合中没有重复的选项,最有效的办法是使用UNION ALL。UNION ALL组合两个输入中所有项的结果集,并包含重复的选项。

5.6.3 EXCEPT
5.6.4 INTERSECT

聚合和旋转操作
6.1 聚合
6.1.1 聚合函数
MySQL数据库支持聚合(aggregate)操作,按照分组对同一组内的数据聚合进行统计操作。目前MySQL数据库支持的聚合函数有:
avg()
big_and()
bit_or()
bit_xor()
count(distinct)
count()
group_concat()
max()
min()
std()
stddev_pop()
stddev_samp()
stddev()
sub()
var_pop()
var_samp()
variance()

6.1.2 聚合的算法
MySQL数据库仅支持流聚合,而其它数据库可能支持散列聚合。流聚合依赖于获得的存储在GROUP BY列中的数据。如果一个SQL查询中包含的GROUP BY语句多于一行,流聚合会先根据GROUP BY对行进行排序。(若要避免第一次的排序操作,可以在分组的列上添加索引,就是GROUP BY后的列)

6.2 附加属性聚合
6.3 联系聚合

游标
7.1 面向集合与面向过程的开发
7.2 游标的使用
7.3 游标的开销
7.5 使用游标解决问题
7.4.1 游标的性能分析
7.4.2 连续聚合
7.4.3 最大会话数

事务编程
8.1 事务概述
事务的ACID…
8.2 事务的分类
从理论角度:
扁平事务(flat transactions)
带有保存点的扁平事务(flat transactions with savepoints)
链事务(chained transactions)
嵌套事务(nested transactions)
分布式事务(distributed transactions)

扁平事务是最简单的一种,所有操作都处于同一层次,其由BEGIN WORK开始,由COMMIT WORK或ROLLBACK WORK接收。扁平事务的主要显示是不能提交或回滚事务的某一部分,或分几个步骤提交。

带有保存点的扁平事务,除了支持扁平事务支持的操作外,运行在事务执行过程中回滚到同一个事务中较早的一个状态,这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效,放弃整个事务不合乎要求,开销也太大。保存点(savepoint)用来通知系统应该记住事务当前的状态,以便以后发生错误时,事务能回到该状态。

对扁平事务来说,其隐式地设置了一个保存点,但是在整个事务中,只有这一个保存点,回滚只能回滚到事务开始时的状态。保存点用SAVE WORK函数来建立,通知系统记录当前的处理状态。当出现问题时,保存点能用做内部的重启动点,根据应用逻辑,决定是回到最近一个保存点还是其他更早的保存点。

链事务可视为保存点模式的一个变种。在提供一个事务时,释放不需要的数据对象,将必要的处理上下文隐式地传给下一个要开始的事务。链事务中的回滚仅限于当前事务,即只能恢复到最近一个保存点。

嵌套事务是一个层次结构框架。

分布式事务通常是一个在分布式环境下运行的扁平事务,因此需要根据数据所在位置访问网络中的不同节点。
对于MySQL数据库(InnoDB存储引擎)来说,其支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。

8.3 事务控制语句
在MySQL命令行行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务须使用命令BEGIN和START TRANSATION,或者执行命令SET AUTOCOMMIT-0,以禁用当前会话的自动提交。

可用的事务控制语句:
>START TRANSACTIN|BEGIN:显式地开启一个事务。
>COMMIT:COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的。
>ROLLBACK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
>SAVEPOINT identifier:SAVEPOINT运行在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT。
>RELEASE SAVEPOINT identifier:删除一个事务的保存点,当没有一个保存点执行这语句时,会抛出一个异常。
>ROLLBACK TO [SAVEPOINT] identifier:把事务回滚到标记点,而不回滚在此标记点之前的任何工作。
>SET TRANSACTION:这个语句用来设置事务的隔离级别。

START TRANSACTIN和BEGIN语句都可以在MySQL命令行下显式地开启一个事务。但是在存储过程中,MySQL数据库的分析器会自动将BEGIN识别为BEGIN…END,因此在存储过程中只能使用START TRANSACTIN语句来开启一个事务。

COMMIT和COMMIT WORK语句基本上是一致的,都用来提交事务。不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。如果是CHAIN方式,那么事务就变成了链事务。可以通过参数completion_type来进行控制,默认该参数为0,表示没有任何操作。在这种设置下,COMMIT和COMMIT WORK是完全等价的。当completion_type为1时,COMMIT WORK等同于COMMIT AND CHAIN,表示马上自动开启一个相同隔离级别的事务。

8.4 隐式提交的SQL语句
8.5 事务的隔离级别
ANSI SQL标准定义了十个隔离级别:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

隔离级别越低,事务请求的锁越少或保护锁的事件就越短。这也是为何大多数数据库系统默认的事务隔离级别是READ COMMITTED的原因。

在InnoDB存储引擎中,可以使用以下命令来设置当前会话或全局的事务隔离级别:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
	READ UNCOMMITTED
	| READ COMMITTED
	| REPEATABLE READ
	| SERIALIZABLE	
}

如果要在MySQL启动时就设置事务的默认隔离级别,那就需要修改配置,在[mysqld]中添加如下行:

transaction-isolation = READ COMMITTED

查看当前会话或全局的事务隔离级别:

SELECT @@tx_isolation
SELECT @@global.tx_isolation

8.6 分布式事务编程
在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

8.7 不好的事务编程习惯
8.8 长事务

索引
9.1 缓冲池、顺序读取与随机读取
基于磁盘的数据库系统一般都有缓冲池,即一块内存区域,其作用是将从磁盘上读取的指定大小数据(页或块),放入缓冲池。当再次读取是,数据库首先判断该页是否在缓冲池中,如果在则直接读取缓冲池中的页,如果不在则读取磁盘上的页。对于写操作,数据库将页读入缓冲池,然后在缓冲池中对页进行修改,修改完成后的页一般被异步地写入磁盘上。对于缓冲池的维护一般采用最近最少使用算法。由此可见,缓冲池的大小决定了数据库的性能。若数据库中的数据可以完全放入缓冲池中,则可以认为这时数据库的性能是最优的。除了同步/异步的写磁盘操作外,所有其它操作都可以在内存中完成。

对于MySQL数据库系统,由于其有着各种不同的存储引擎,因此其缓冲池是基于存储引擎的,也就是说每个存储引擎都有自己的缓冲池。对于MyISAM存储引擎来说,变量key_buffer_size决定了缓冲池的大小。对于InnoDB存储引擎来说,变量innodb_buffer_pool_size决定了缓冲池的大小。

9.2 数据结构与算法
9.2.1 二分查找法
9.2.2 二叉查找树和平衡二叉树
二叉查找树中,左子树的键值总是小于根的键值,右子树的键值总是大于根的键值,可以通过中序遍历得到键值的排序输出。

若想最大性能地构造一个二叉查找树,需要这个二叉查找树是平衡的,因此引入了平衡二叉树,又称为AVL树。平衡二叉树的定义:首先符合二叉查找树的定义,其次必须满足任何节点的两颗子树的高度最大差为1.
9.3 B+树
B+树和二叉树、平衡二叉树都是经典的数据结构。B+树是为磁盘或其它直接存储辅助设备设置的一种平衡查找树,在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点,各叶子节点通过指针进行链接。
9.3.1 B+树的插入操作
9.3.2 B+树的删除操作

9.4 B+树索引
9.4.1 InnoDB B+树索引
9.4.2 MyISAM B+树索引

9.5 Cardinality
9.5.1 什么是Cardinality
并不是所有在查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般的经验是,在访问表中的很少一部分行时使用B+树索引才有意义。如果某个字段的取值范围很广,几乎没有重复,那么此时使用B+数索引是最适合的。

可以通过SHOW INDEX语句中的Cardinality列来观察索引是否是高选择性。
9.5.2 InnoDB存储引擎怎样统计Cardinality

9.6 B+树索引的使用
9.6.1 不同应用中B+树索引的使用
9.6.2 联合索引
9.6.3 覆盖索引
9.6.4 优化器选择不使用索引的情况
9.6.5 INDEX HINT
9.7 Multi-Range Read
MySQL数据库5.6版本开始支持Multi-Range Read(MRR)优化。MRR优化的目的就是减少磁盘的随机访问,并且将随机访问转化为较为顺序的数据访问。
9.8 Index Condition Pushdown
9.9 T树索引
9.10哈希索引

分区
10.1 分区概述
分区功能并不是在存储引擎层完成的,因此不只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM、NDB等都支持分区。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。从逻辑上,只有一个表或一个索引,但是物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区(行分散),并不支持垂直分区(列分散)。MySQL数据库的分区是局部分区索引,一个分区中既存放数据又存放索引。全局分区是指,数据存放各个分区中,但是所有数据的索引放在一个对象中(索引放一起,局部分区索引,索引放入各分区,和数据放一起)。

当前MySQL数据库支持以下几种类型的分区:
>RANGE分区:行数据基于属于一个给定连续区间的列值放入分区。MySQL5.5开始支持RANGE COLUMNS的分区。
LIST分区:和RANGE分区类型一样,只是LIST分区面向的是离散的值。>MySQL5.5开始支持LIST COLUMNS的分区。
>HASH分区:根据用户自定表达式的返回值来进行分区,返回值不能为负数
>KEY分区:根据MySQL数据库提供的离散函数来进行分区。
不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。(*****)

10.2 分区类型
10.2.1 RANGE分区

CREATE TABLE t (
	id INT
)ENGINE=INNODB
PARTITION BY RANGE (id)(
	PARTITION p0 VALUES LESS THAN (10),
	PARTITION p1 VALUES LESS THAN (20)
);

小于10,插入p0分区,小于20,插入p1分区。查看表在磁盘上的物理文件,启用分区之后,表不再由一个ibd文件组成,而是由建立分区是的各个分区ibd文件组成。

可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息:

SELECT * FROM information_schema.PARTITIONS
WHERE table_schema=database() AND table_name='t'

返回结果中的TABLE_ROWS列反应了每个分区中记录的数量。PARTITION_METHOD表示分区的类型。定义了分区的表,应该严格遵守分区的定义,当插入一个不在分区中定义的值时,MySQL数据库会抛出异常。对于这个问题,可以对分区添加一个MAXVALUE值的分区。MAXVALUE可理解为正无穷:

ALTER TABLE T
ADD PARTITION(
	partitin p2 values less than maxvalue
);

RANGE分区主要用于日期列的分区。例子:

CREATE TABLE sales(
	money INT UNSIGNED NOT NULL,
	date DATETIMES
)ENGINE=INNODB
PARTITION by RANGE (YEAR(date)) (
	PARTITION p2008 VALUE LESS THEN (2009),
	PARTITION p2008 VALUE LESS THEN (2010),
	PARTITION p2008 VALUE LESS THEN (2011),
)

这样创建的好处是便于对sales表的管理,如果要删除2008年的数据,只需要删除2008年数据所在的分区即可:

alter table sales drop partition p2008;

另一个好处是加快某些查询操作,例如值需要查询2008年整年的销售额:

EXPLAIN PARTITIONS 
SELECT * FROM sales WHERE date>='2008-01-01' AND date<='2008-12-31';

EXPLAIN PARTITIONS命令发现,SQL优化器值需要搜索p2008这个分区,而不会搜索所有的分区(称为Partition Pruning分区修剪),故查询的速度得到了大幅提升。

对RANGE分区的查询,优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择。

10.2.2 LIST分区
LIST分区和RANGE分区非常相似,只是分区列的值是离散的,而非连续的:

CREATE TABLE T (
	a INT,
	b INT
)ENGINE=INNODB
PARTITION BY LIST(b) (
	PARTITION p0 VALUES IN (1,3,5,7,9),
	PARTITION P1 VALUES IN (0,2,4,6,8)
)

不同于RANGE分区中定义的VALUES LESS THAN语句,LIST分区使用VALUES IN。因为每个分区的值是离散,因此只能定义值。

10.2.3 HASH分区
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致是一样的。在HASH分区中,用户所要做的是基于将要被散列的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区分割一个表,要在CREATE TABLE语句上添加一个PARTITION BY HASH(expr)子句,其中expr是返回一个整数的表达式。expr可以仅仅是字段类型为MySQL整型的列名。此外,用户需要在后面添加一个PARTITONS num子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。如果没有包含一个PARTITION子句,那么分区的数量将默认为1。

CREATE TABLE t_hash (
	a INT,
	b DATETIME
)ENGINE=INNODB
PARTITION BY HASH(YEAR(b))
PARTITION 4;

MySQL数据库还支持一种称为LINEAR HASH的分区,它使用一个更加复杂的算法来确定插入到已经分区的表中的位置。它的语法和HASH分区的语法相似,只是将关键字HASH改为LINEAR HASH。

10.2.4 KEY分区
KEY分区和HASH分区类似,不同在于HASH分区通过用户自定义的函数进行分区,KEY分区使用MySQL数据库提供的函数进行分区。NDB Cluster引擎使用MD5函数来分区,对于其他存储引擎,MySQL数据库使用其内部的散列函数来分区,这些函数基于与PASSWORD()一样的运算法则。

10.2.5 COLUMNS分区
RANGE、LIST、HASH和KEY这四种分区中,分区的条件必须是整型,如果不是,那么需要通过函数将其转化为整型,如YEAR()、TO_DAYS()、MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区,可视为对RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得到,不需要转化为整型。此外,RANGE COLUMNS分区可以对多个列的值进行分区。

MySQL 5.5开始支持COLUMNS分区,对于之前的RANGE和LIST分区,用户可以用RANGE COLUMNS和LIST COLUMNS分区来很好地代替。

10.3 子分区
子分区(subpartiton)是在分区的基础上在进行分区,有时也称这种分区为符合分区(composite partitioning)。MySQL数据库允许在RANGE和LIST的分区上在进行HASH或KEY的子分区。
10.4 分区中的NULL值
MySQL数据库允许对NULL值进行分区,但是处理方法可能与其它数据库完全不同。MySQL数据库的分区总是把NULL值视为小于任何一个非NULL值,这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。
10.5 分区和性能
分区中存放了数据和索引,对于查询的条件,如果是分区的依据,那么可以确定定位到某个分区,否则就要便利全部分区,如果有索引,进入分区后比较索引。这个原理告诉我们,启用了分区,不见得查询会更快,只有查询条件是分区的依据时,可以明显提高速度。

10.6 在表和分区间交换数据