分类目录归档:SQL

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万记录建索引,轻松上亿。不过即使是亿级别的数据,查询依然很轻松。

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);

SQL实例之六 Group By Having

SELECT SKU, StartTime, min(StartTime),sum(QuantitySold),count(id) as online FROM ebay_listings
WHERE ListingStatus = 'Active'
GROUP BY SKU HAVING min(StartTime) < "2016-03-01 01:01:01" 
AND (min(StartTime) > "2016-01-01 01:01:01" AND min(StartTime) < "2016-04-01 01:01:01")
AND sum(QuantitySold) < 1
ORDER BY min(StartTime)

首先根据SKU来进行分组,然后通过聚合函数提取一个组中的值,比如min(StartTime),取回一个组中的最小时间。搭配使用聚合函数可以当做一般查询中的Where条件类使用,只不过这里针对的是一个组而已。

以上SQL需要实现的是对一个集合进行分组,然后应用组内的最小时间进行过滤,比如最小时间是否在一个范围内。一条简单的SQL,实现了期望。

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
;

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 在表和分区间交换数据

SQL实例之六

一个关键词对应多个站点,现有实现是:

keyword   US
keyword   UK

同一个词有多个站点,对应了表的多条数据,这个实现在根据站点提取关键词时很方便,但是在编辑和词列表时就比较麻烦(比如列表时同一个词列多条记录,编辑时也只能针对一个站点),期望是列表时相同的词只列出一个,站点信息用一个逗号分隔列出,编辑时可以编辑词的不同站点属性,为了达到这个预期,使用如上的存储结构显然不可行,但是目前程序已经按照这个格式进行读取了,那么我们需要寻找一个能够兼顾两头的方案。

实现方案是使用视图。

把站点这个属性提取出来:

//keyword表
1  keyword1   
2  keyword2

//keyword_site表
id  kid  site
1   1    US
2   1    UK
3   1    DE

这样,关键词keyword1就对应了US UK DE三个站点,而Keyword表还是仅仅保存关键词。那么如果构建最开始说的那个表结构呢(因为程序已经基于这个实现了),经过以上改造后,就可以使用视图组装一个新表了:

CREATE 
VIEW `Keywords`AS 
SELECT ks.id, k.keyword, ks.site from keyword as k LEFT JOIN keyword_site as ks ON k.id = ks.kid;

mysql_view
这个就是最终结果。这个实现兼顾了两头。

以下是具体的SQL:

-- ----------------------------
-- Table structure for keyword
-- ----------------------------
DROP TABLE IF EXISTS `keyword`;
CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyword
-- ----------------------------
INSERT INTO `keyword` VALUES ('1', 'a1');
INSERT INTO `keyword` VALUES ('2', 'a2');
INSERT INTO `keyword` VALUES ('3', 'a3');
INSERT INTO `keyword` VALUES ('4', 'a4');

-- ----------------------------
-- Table structure for keyword_site
-- ----------------------------
DROP TABLE IF EXISTS `keyword_site`;
CREATE TABLE `keyword_site` (
  `id` int(11) NOT NULL,
  `kid` int(11) NOT NULL,
  `site` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kid_index` (`kid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyword_site
-- ----------------------------
INSERT INTO `keyword_site` VALUES ('1', '1', 'US');
INSERT INTO `keyword_site` VALUES ('2', '1', 'UK');
INSERT INTO `keyword_site` VALUES ('3', '1', 'DE');
INSERT INTO `keyword_site` VALUES ('4', '2', 'US');
INSERT INTO `keyword_site` VALUES ('5', '2', 'UK');
INSERT INTO `keyword_site` VALUES ('6', '3', 'US');
INSERT INTO `keyword_site` VALUES ('7', '4', 'UK');

CREATE VIEW `keywords` AS 
SELECT ks.id, k.keyword, ks.site from keyword as k LEFT JOIN keyword_site as ks ON k.id = ks.kid ;

MySQL 分表测试 与 分表模型

例子:

<?php
$server="localhost";
$user="root";
$pwd="";
$db="test";
$port=3306;
$sock='/var/lib/mysql/mysql.sock';
$charset='utf8';

function mysqlidb($server,$user,$pwd,$db,$charset,$port,$sock){
	$link = false;

	$connectionRetry = 10;

	while (!isset($link) || ($link == FALSE && $connectionRetry !=0) ){
		$link = mysqli_connect($server,$user,$pwd,$db,$port,$sock);
		$connectionRetry--;
	}

	if($link) {
		if (@mysqli_select_db($link, $db)) {
			if ((trim($charset) != '') && version_compare(@mysqli_get_server_info(), '4.1.0', '>=')) {
				@mysqli_query($link, "SET NAMES '" . trim($charset) . "'");
				if (function_exists('mysqli_set_charset')) {
					@mysqli_set_charset($link, trim($charset));
				} else {
					@mysqli_query($link, "SET CHARACTER_SET_CLIENT = '" . trim($charset) . "'");
					@mysqli_query($link, "SET CHARACTER_SET_RESULTS = '" . trim($charset) . "'");
				}
			}
		}
	}
	return $link;
}

$db = mysqlidb($server,$user,$pwd,$db,$charset,$port,$sock);

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

function hasTable($table = '') {
	global $db;
	if(empty($table)) {
		return FALSE;
	}
	
	$r = mysqli_query($db,"SELECT table_name FROM information_schema.TABLES WHERE table_name ='$table';");
	$row = mysqli_fetch_assoc($r);
	
	if(!empty($row)) {
		return TRUE;
	}	
	
	return FALSE;
}

function createTable($table = '') {
	global $db;
	
	if(empty($table)) {
		return FALSE;
	}
	
	if(!hasTable($table)) {
		$dd = mysqli_query($db,'
			CREATE TABLE `'.$table.'` (
			`id`  int(11) NOT NULL ,
			`body`  text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL ,
			PRIMARY KEY (`id`)
			)
			ENGINE=InnoDB
			DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
			ROW_FORMAT=COMPACT
			;
		');
		if((int)$dd < 1) {
			$find = FALSE;
		}		
	}
	return TRUE;
}

/**/
for($i = 1; $i < 1000; $i++) {
	//echo $i . " - ". getTableNum($i)."\n";
	$num = getTableNum($i);
	$table = "t_".$num;
	
	if(!hasTable($table)) {
		createTable($table);
	}
	
	$sql = "INSERT INTO `".$table."` (`id`,`body`) values(".$i.",'123456789')";
	//echo $sql;
	$r = mysqli_query($db,$sql);
}


//for($i = 800; $i < 900; $i++) {
	$i = mt_rand(1, 1000);

	$num = getTableNum($i);
	$table = "t_".$num;
	
	if(hasTable($table)) {
		$sql = "SELECT * FROM `".$table."` WHERE id='".$i."' ";
		$r = mysqli_query($db,$sql);
		
		$row = mysqli_fetch_assoc($r);
		echo "From Table(".$table."):" . $row['id']."\n";
		
	} else {
		echo $table."表不存在,记录也就不存在。";
	}
//}

以下试着用一个具体模型来说明:
mysql-table-model

关联用户的订单表是一对多关系,根据订单关联用户这个维度,使用用户的ID,把订单放入各个分表中存放。比如:

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

比如用户的ID从1-200,放入订单表_1,用户的ID从201-400,放入订单表_2等,这样的实现方式最简单高效,做一个除法运算就可以定位到分表号。

情况一:
知道订单ID,要到订单分表获取信息,那么首先要到订单主表去获取这个ID的订单,然后取回用户ID号,然后用这个用户ID号定位到分表,然后到分表中根据订单的ID定位到订单。这个过程说明,分表中需要保持订单ID。这个表现为主表与分表之间的一对一关系。

情况二:
知道用户ID,要定位用户所有的订单,可以根据用户ID定位到分表号,然后到分表中获取所有具有这个用户ID的订单。这个过程说明,分表中需要保存用户ID。这个表现为分表跟用户表之间的一对多关系。

情况三:
要获取一段时间内的所有订单。这个必须是查询订单主表,如果需要到保存在订单详情中的信息,需要循环结果集,分别到订单分表中获取。这个过程说明,主表需要保存尽可能多的元数据,这些元数据也要保存到分表中,分表中保存的是大数据。

这个是典型的一对多模型进行水平扩展的方案,可以满足一定数据存储的需求。不过缺点也明显,主表虽然只保存元数据,容量可能不会太大,但是记录的数量可能很大,比如千万级别的订单。如果一天10w,一个月就是300w,一年就是3600w,不用三年就到达亿级。

所以订单主表还需要进一步进行处理,可以按照时间对其进行归档处理,比如按照年来进行归档,当要查询某个时间段的订单时,可以定位到年归档表查询(如果时间跨越了一年,需要分别进行查询,然后合并结果)。

另外,用户表数量也可能非常大,那么可以继续对用户表进行水平扩展,不过这个扩展跟上面的根据用户ID对应订单进行水平扩展的方式是不一样的。用户表只保存元数据,根据ID定位到分表号(还是一样的函数):

function getTableNum($id) {
	$cap = 200;
	return (int)($id / $cap);
}

一般应该使用一样的分表算法。

以上的两个分表方式,实际是差不多相同,只不过订单的分表是间接根据用户来来分的,而用户表的分表则直接根据用户ID进行拆分,间接分表这种方式是比较常见的,它应该满足一对多的关系模型,在遇到其它需要分表的情况,对照这个模型思考一下就可以。

以上的分表方案可以说是最简单的了,可以看到,由于引入了分表,所有相关的程序也会变得复杂一些。经常可能碰到的概念是分库,一般策略应该是垂直分库(根据业务模型拆分,类似多个子系统,子系统实现水平分表),水平分库(每个库都一样的结构)看起来会比较复杂,需要解决全局ID、事务等问题,当然目前方案是有的….

SQL实例之五

logistics

物流商(logistics)会有多个运输方式(logistics_transport),这是一对多的关系。运输方式(transport)定义了系统使用的运输方式,这些运输方式有些物流商(logistics)并没有对应的运输方式,相反,有些物流商(logistics)提供了对应的运输方式,但系统可能并不使用它,所以在运输方式(transport)、物流商(logistics)和物流商运输方式(logistics_transport)中间必须存在一个对应关系,它记录了某运输方式对应的某个物流商以及物流商的某个运输方式。

这样的关系模型,在关系数据库设计中还是非常常见的。

要获取某物流商对应的系统运输方式 或 获取系统运输方式对应的物流商运输方式,只要进行简单的JOIN操作即可。不过现在系统是针对所有系统运输方式,都那个所有物流商与之匹配,然后选择需要的进行设置,举例:

物流商:Lgstcs_A Lgstcs_B
系统运输方式:Trsprt_01 Trsprt_02

输出:

Trsprt_01		Lgstcs_A		选择Lgstcs_A的对应运输方式
Trsprt_02		Lgstcs_A		选择Lgstcs_A的对应运输方式
Trsprt_01		Lgstcs_B		选择Lgstcs_B的对应运输方式
Trsprt_02		Lgstcs_B		选择Lgstcs_B的对应运输方式

这里的transport和logistics是典型的无条件JOIN关系,SQL如下:

SELECT t.transport_code, t.transport_name, l.logistics_code, l.logistics_name
    FROM transport t JOIN logistics l 
        LEFT JOIN transport_logistics_transport tlt ON (tlt.transport_id = t.id AND tlt.logistics_id = l.id) 

这个查询在transport和logistics没有添加新记录的情况下,返回的记录都是一样的,当设置了对应的物流商运输方式时,会对应一条记录插入到transport_logistics,如果没有设置,对应的记录就要删除。

这里搞这个查询主要是因为transport和logistics都是小集合,比如运输方式只有几种到二三十种左右,物流商应该低于十种,所以把它们全部的可能列出来,反而是很直观的。

SQL实例之四

最近要完成一个自动计算销售价格并更新到销售价格表的需求。具体描述如下:

产品中记录成本价 和 重量,现在要监控这两个变量,一旦这两个变量发生改变,就要按照公式重新计算价格,然后把新的价格写入产品价格表。计算价格的过程需要多个变量,其中产品对应不同类目,会有不同的收费费率,所有需要把所有SKU和类目对应起来,这里需要手动完成这个对应过程(记录到新表)。

首先要解决的是:如何监控成本价 和 重量的变化?
解决方案是使用MySQL的触发器,这个还算简单,因为我只需要监控变化,价格计算过程还是需要调用程序完成的,MySQL的触发器是无法发出调用外部程序的指令的,所以我决定采用最简单的方式:只要数据有变化就写入到一张监控表中(product_trigger),通过计划任务定时扫描这个表执行整个过程。

产品删除时,并不需要去删除对应的价格,所以对于删除产品时,我并不做监控。但是在产品新增和更新时添加了触发器:

DROP TRIGGER IF EXISTS `product_insert`;
CREATE DEFINER=`root`@`localhost` TRIGGER `product_insert` AFTER INSERT ON `product` FOR EACH ROW 
BEGIN 
insert into product_trigger(pid,sku,oprice,oweight,nprice,nweight,type,issync) 
values(NEW.id,0,0,NEW.price,NEW.weight,1,0);
END 

DROP TRIGGER IF EXISTS `product_update`;
CREATE DEFINER=`root`@`localhost` TRIGGER `product_insert` AFTER INSERT ON `product` FOR EACH ROW 
BEGIN 
insert into product_trigger(pid,sku,oprice,oweight,nprice,nweight,type,issync) 
values(NEW.id,OLD.price,OLD.weight,NEW.price,NEW.weight,2,0);
END 

产品添加时,type为1,为1表示是新插入的,那么就直接使用nprice和nweight来计算价格(不需要检测价格和重量是否变化)。当是产品更新时,type为2,为2表示是更新,要检查价格和重量是否变化了,变化了才去更新价格。如果对应的产品价格更新,对应的issync就是1,如果失败了就是2,默认去扫描issync为0的记录。

这个触发表SQL:

CREATE TABLE IF NOT EXISTS `product_trigger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `oprice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `oweight` int(10) NOT NULL DEFAULT '0',
  `nprice` decimal(10,2) NOT NULL DEFAULT '0.00',
  `nweight` int(10) NOT NULL DEFAULT '0',
  `type` int(3) NOT NULL DEFAULT '0',
  `issync` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

然后首先是一段主程序框架:

$allUpdate = $db->fetchAll("SELECT * FROM product_trigger
WHERE issync<1 ORDER BY id");
foreach($allUpdate as $itm){
    if(($itm['type']=1) ||  (($itm['type']==2) && ($itm['oprice'] != $itm['nprice'] || $itm['oweight'] != $itm['nweight']))){
        //需要计算价格
        ......
    }else{
        //不需要操作的记录 可能是更新其它字段导致的写入
        $db->update('product_trigger',array('issync'=>1),"id=".$itm['id']);
    }
    //主要获取产品ID
}

查询中的ORDER BY id是为了解决当有多次更新时,可以确保它是安装顺序进行的。当是更新操作时,只有价格和重量变化了才操作。

计算价格这段程序,有一个变量需要确定,就是产品对应的类名,使用如下SQL查询:

SELECT p.id,p.sku,p.is_var,pv.sku,pr.sku FROM (product p LEFT JOIN product_var pv ON p.id = pv.pid) JOIN product_rate pr ON (pr.sku = if(p.is_var,pv.sku,p.sku)) 
WHERE p.id = PID

这里的PID就是主程序循环获取的产品ID,表product_rate结构:

--
-- 表的结构 `product_rate`
--

CREATE TABLE IF NOT EXISTS `product_rate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `platform` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `site` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `rate` decimal(10,4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

--
-- 转存表中的数据 `product_rate`
--

INSERT INTO `product_rate` (`id`, `sku`, `platform`, `site`, `rate`) VALUES
(1, '00302', 'eBay', 'US', '1.0000');

注意这个表记录的是最小SKU对应的平台站点费率。在可以确定费率之后主程序继续完善如下:

$allUpdate = $db->fetchAll("SELECT * FROM product_trigger
WHERE issync<1 ORDER BY id");
foreach($allUpdate as $itm){
    $del = true;
    if(($itm['type']=1) ||  (($itm['type']==2) && ($itm['oprice'] != $itm['nprice'] || $itm['oweight'] != $itm['nweight']))){
        //需要计算价格
        $reslt = $db->fetchAll("SELECT p.id,if(p.is_var,pv.sku,p.sku) as sku,pr.platform, pr.site, pr.rate FROM (product p LEFT JOIN product_var pv ON p.id = pv.pid) WHERE p.id = ".$itm['pid']);
        if(!empty($reslt)){
             // 循环每个SKU
             foreach($reslt as $rat){
                 // 最小SKU
                 $sku = $rat['sku']; 

                 // 这里初始化所有参数*****

                 $rateTable = $db->fetchAll("SELECT * FROM product_rate WHERE platform='eBay' sku = '".$sku); 
                 if(!$rateTable){
                     ////////////////////////////
                     // 对应费率
                     $rate = $rat['rate'];
                     // 平台
                     $platform = $rat['platform'];
                     // 站点
                     $site = $rat['site'];
                     /////////////////////////////
                     // 开始计算 写入价格表逻辑
                 }else{
                     // 对应费率
                     $rate = 0.05;
                     // 平台
                     $platform = 'SMT';
                     // 站点
                     $site = 'All';
                     // 开始计算 写入价格表逻辑
                 }
                 
             }
        }
    }

    if($del)
        //更新
        $db->update('product_trigger',array('issync'=>1),"id=".$itm['id']);
    }
}

原本想着这段程序会稍微复杂,要搞多次JOIN,实际上根本没有JOIN的机会。