标签归档:sql

MySQL 存储过程实例

drop procedure if exists proc_tmp;

delimiter ;;
create procedure proc_tmp()
begin
    declare done int default 0;
    declare lid bigint;
    declare total int;

    declare row cursor for select lid, total from xx;

    declare continue handler for not FOUND set done = 1;

    open row;

    repeat
       fetch row into lid, total;
       if not done then
           update xxx set total=total where id = lid;
       end if;
    until done end repeat;

    close row;
end
;;

call proc_tmp();

drop procedure if exists proc_tmp;

SQL擅长集合操作。复杂的操作我们需要写一下程序。也可以充分利用数据库的存储过程。

例子是便利一个集合,然后对另一张表进行同步更新,有些情况同时运行多条语句就可以完成,有些情况是需要依赖某些情况的变量,然后才进行的某种操作,单纯的SQL语句就无法胜任,因为这种是代码明显的编程特征,为了补充编程能力,数据库提供了有限的可编程方案。

存储过程定义过程,一般需要用delimiter圈起来,主要是把界定符修改,避免冲突。如果仅仅是完成一个临时任务,存储过程运行后就可以删除。

如果需要循环一个集合,需要游标的帮助。定义游标,打开,取出当前数据,关闭游标。其中取数据使用fetch,这个过程是循环进行的,那么需要一个循环结束条件,当fetch不到任何内容时,就说明结束了。 declare continue handler for not FOUND set done = 1就是当fetch不到任何内容时,done就会被设置为1,就是结束条件。

PostgreSQL 模糊查询

在MySQL中,只要字段对应建立B-Tree索引,那么前缀LIKE就可以走索引。不过在PostgreSQL中的设置就稍微复杂一点。

#建表和建索引
create table test(id int, name varchar(32));
create index idx on test(name);

#对应如下详细SQL语句
CREATE TABLE "public"."test" (
"id" int4,
"name" varchar(32) COLLATE "default"
)
WITH (OIDS=FALSE)
;

ALTER TABLE "public"."test" OWNER TO "postgres";
CREATE INDEX "idx" ON "public"."test" USING btree ("name" "pg_catalog"."text_ops");

注意,字段“name”的字符检验是“default”,表示继承自数据库的设置。创建的索引类型是btree(默认),未指定字符校验集,类型模式是text_ops(默认)。这个情况下(数据库的校验集不是“C”),直接like ‘xxx%’是无法走索引的。

如果需要走索引,有两种方式:
1 建立索引时,明确指定校验集为“C”,查询时也指定校验集为“C”(如果数据校验集是“C”,不需要指定)

#建索引时指定
create index idx on test(name collate "C"); 

#查询时指定
like 'xxx%' collate "C"

这个索引的搜索是二进制搜索,效率高效。

2 建立索引时,明确指定字段类型对应模式,比如varchar对应varchar_pattern_ops,text对应text_pattern_ops,其它类推

create index idx on test(name varchar_pattern_ops);

使用类型对应的 pattern ops 时,索引搜索不仅支持 LIKE 的写法,还支持规则表达式的写法(name ~ ‘^abcd’)。使用pattern ops将使用字符查询而非binary查询的搜索方式。

一般,第二种方式比较常用(因为默认字符校验一般都不会是“C”), 基于btree的后缀模糊搜索(建索引时调用reverse()函数),跟上面的讨论一致。前后模糊搜索,无法应用到btree索引。

在PostgreSQL中,内置了一般数据库都没有的pg_trgm插件,还内置了表达式索引和GIN索引的功能。PostgreSQL 支持使用 pg_trgm 索引来加速同时包含前模糊和后模糊的查询。为使索引过滤有较好的效果,前模糊查询至少需输入 1 个字符,后模糊查询至少需输入 2 个字符。若要高效支持多字节字符(如中文),数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能有较好的效果。

#创建gin索引
create index idx_test on test using gin (c1 gin_trgm_ops);

#查询
select * from test where c1 like 'x%';
select * from test where c1 like '%xx';

注意:对于后默认查询(2个字符以上),并不需要像btree索引那样另外调用翻转函数建立索引。

对于前后模糊搜索,需要至少3个字符:

select * from test where c1 like '%xxx%'

PostgreSQL 入门

2010年9月20日发布了PostgreSQL 9.0
2011年9月12日发布了PostgreSQL 9.1(同步复制)
2012年9月10日发布了PostgreSQL 9.2(级联复制),CentOS 7.x默认YUM源会安装此版本。

大体应该是每年一个大版本:
2013 PostgreSQL 9.3
2014 PostgreSQL 9.4
2015 PostgreSQL 9.5
2016 PostgreSQL 9.6
2017 PostgreSQL 10.0

#CentOS 7中默认安装PostgreSQL 9.2,可以通过安装一个Yum包来获取最新的版本
yum install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

#安装服务端和第三方贡献包和客户端
yum install postgresql96.x86_64 postgresql96-contrib.x86_64 postgresql96-server.x86_64 

#初始化数据库(默认数据目录/var/lib/pgsql/9.6/data/)
/usr/pgsql-9.6/bin/postgresql96-setup initdb

#设置开机启动和启动PostgresSQL
systemctl enable postgresql-9.6.service
systemctl start postgresql-9.6.service

#PostgresSQL安装后就可以启动,安装时自动创建了一个叫postgres的系统用户 和 一个同名的用户名和数据库(postgres)
#设置数据库需要切换到postgres用户,以postgres用户进入PostgreSQL不需要密码
[root@localhost ~]# su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.6.3)
Type "help" for help.

#PostgreSQL默认会创建一个叫postgres的数据库,还有两个模板数据库template0和template1。
#用户新建数据库时默认从模板数据库template1克隆出来(可以定制这个模板库)。
#而template0是一个最简化的模板库,创建数据库时,如果明确指定从此数据库中继承,则创建一个最简化的数据库。
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

############################################################
#使用postgres用户,来生成其他用户和新数据库

\password postgres  #为postgres用户设置密码
CREATE USER dbuser WITH PASSWORD 'password'; #新建立用户和对应的密码
CREATE DATABASE exampledb OWNER dbuser;  # 创建用户的数据库
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;  #为用户授权

############################################################
#一般常用操作
#切换用户
su postgres
#运行psql终端
psql
#查看数据库
\l
#创建数据库
CREATE DATABASE test;
#切换数据库
\c test;
#创建数据库
CREATE TABLE t(id int primry key, name varchar(40));
#查看库中的数据表
\d
#查看具体的数据表结构
\d t
#查看更加详细的表结构
\d+ t

#显示SQL执行的时间
\timing on

postgres=# \q

登录,导入,命令:

#如果当前系统用户是dbuser,则-U dbuser可以省略
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

#如果要导入数据库
psql exampledb < exampledb.sql

一般命令:

\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。

基本配置:
1 监听地址和端口:

cd /var/lib/pgsql/9.6/data/
vi postgresql.conf

#listen_address = 'localhost'
#port = 5432

#############################
#如果修改了远程监听,还需要修改pg_hba.conf,添加一条记录
vi pg_hba.conf
host    all		all		all			trust

注:pg_hba.conf配置文件中的认证METHOD的ident修改为password,可以实现用账户和密码来访问数据库,其中这个认证标示有”trust”, “reject”, “md5”, “password”, “gss”, “sspi”

2 数据库日志

#日志收集
logging_collector = on

#日志目录(默认)
log_directory = 'pg_log'

#日志切换
# 每天生成一个新日志文件
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_ratation = off
log_rotation_age = 1d
log_roration_size = 0

#每当满一定大小则重建
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_ratation = off
log_rotation_age = 0
log_roration_size = 10M

#保留x天日志,循环覆盖
log_filename = 'postgresql-%a.log'
log_truncate_on_ratation = on
log_rotation_age = 1d
log_roration_size = 0

3 内存参数设置
shared_buffers: 共享内存的大小,主要用于共享数据块(缓存)
work_mem: 单个SQL执行时,排序、hash join所使用的内存
根据实际情况,可以适当调大。

————————————————————————————–
数据库逻辑结构
数据库:一个PostgreSQL实例下可以管理多个数据库。
表、索引:一个数据库中有很多表、索引。在PostgreSQL中表的术语为“Relation”(Table)。
数据行:每张表中有很多行数据。在PostgreSQL中行的术语为“Tuple”(Row)。

在PostgreSQL中,一个数据库包含一个或多个模式,模式中有包含了表、函数及操作符等数据库对象(模式可以理解为在数据库和表之间引入了一个命名空间,也即数据库-模式-表)。在PostgreSQL中不能同时访问不同数据库中的对象(需要重新连接到新库,MySQL可以切换库),而模式没有此限制(从这个概念上看,模式类似MySQL中的数据库级别的概念)。

要创建或访问模式中的对象,需要写上模式名(schema_name.table_name),通常创建和访问表时都不用指定模式,实际上这时访问的都是“public”的模式。当登录到该数据库是,如果没有特殊指定,都是以该模式(public)操作各种数据对象的。

默认,用户无法访问模式中不属于他们的对象。如要访问,模式的所有者必须在模式上赋予它们“USAGE”权限。用户也可以在别人的模式里创建对象,则需要被赋予在该模式上的”CREATE”权限。默认情况下每个人在public模式上都有CREATE和USAGE权限。

PostgreSQL支持两类临时表,一种是会话级的临时表,一种是事务级的临时表。

在PostgreSQL中,表空间实际上是为表指定一个存储的目录。在创建数据库时可以为数据库指定默认的表空间。创建表和索引时可以指定表空间,这样表、索引就可以存储到表空间对应的目录。

PostgreSQL使用多进程架构,每个连接会启动一个新的服务进程。不同于多线程方案,多进程架构通常使用共享内存来实现进程间通信,数据共享。PostgreSQL启动后,会生成一块共享内存用做数据库块的缓冲区。

数据目录结构:
使用环境变量PGDATA指向数据目录的根目录。目录的初始化是使用initdb来完成的。完成后这个数据目录下会生成三个配置文件(postgresql.conf实例配置文件,pg_hba.conf认证配置文件,pg_ident.conf认证方式ident的用户映射文件)

服务配置
1 连接配置项
listen_address
port
max_connections
superuser_reserved_connections 为超级用户连接保留的链接数
unix_socket_directory
unix_socket_group
unix_socket_permissions
bonjour
bonjour_name
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count

2 内存配置
shared_buffers 可以是专用内存的25%,比如1G,可以分250M(注意一个单位是8K,32M就是4000个8K,这里设置为4000)。
temp_buffers
work_mem
maintenance_work_mem
max_stack_depth

3 预写式日志配置
4 错误报告和日志项

访问控制配置文件
在PostgreSQL中,运行哪些IP的机器访问数据库服务器是由pg_hba.conf文件控制的。HBA的意思是host-based authentication,也就是基于主机的认证。

格式:

type	database	user	address		method

local	all		all			peer
host	all		all	127.0.0.1/32	ident
host	all		all	all		md5

第一字段是local,这个记录匹配通过UNIX域套接字的链接认证;是host时,这条记录匹配通过TCP/IP进行的连接(包括SSL和非SSL)。

认证方法有:trust、reject、md5和ident。
1 trust
无条件地允许连接。这个方法允许任何可以与PostgreSQL数据库服务器连接的用户以任意PostgresSQL数据库用户身份进行链接,不需要口令或其它任何认证
2 reject
无条件拒绝链接
3 md5
要求客户端提供一个MD5加密的口令进行认证
4 password
要求客户端提供一个未加密的口令进行认证
5 ident
运行客户端上的特定操作系统用户连接到数据库

查看系统信息的常用命令

#查看版本
select version();

#查看数据库启动时间
select pg_postmaster_start_time();

#查看加载配置文时间
select pg_conf_load_time();

#显示当前数据库时区
show timezone;

#查看当前实例有哪些数据库
psql -l #\l

#查看当前用户名
select user;

#查看Session用户
select session_user;

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 技术内幕: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 选择合适的基准测试工具

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的机会。

SQL实例系列 之三

ER图
产品 与 供应商实现多对多关系,但是每个产品有一个默认供应商,这个字段记录在产品-供应商表(正确应该写入产品表);采购员 和 供应商也实现多对多关系,供应商有一个默认的采购员,写入了采购员-供应商(应该写入供应商表才对)。

现在问题是,要根据一个SKU,获取默认的采购员代码(就是要进入采购员表)。流程如下,先根据SKU定位到产品ID,根据这个产品ID到产品-供应商表中获取到供应商代码,用这个代码到采购员-供应商表中获取采购员ID,用这个ID到采购员表中获取采购员代码。

这个过程少说要搞5个查询…..

试着分析一下,产品有默认供应商,供应商有默认采购员,那么不就意味着产品有默认采购员吗?这样的话,就应该把默认供应商和默认采购员写入产品表中,当要获取供应商和采购员时都非常容易(也高效)。但是目前不是这样搞的。最终使用如下SQL实现这个需求:

SELECT u.采购员 FROME 产品表 p LEFT JOIN 产品多属性表 pv ON p.pid=pv.pid JOIN 产品-供应商表 pp ON pp.pid=p.pid AND pp.is_default=1 JOIN
(采购员-供应商 ppu JOIN采购员 u ON u.uid=ppu.uid AND ppu.is_default=1)
ON pp.supplier_code = ppu. supplier_code
WHERE if(p.is_var,pv.sku,p.sku)=SKU

很费劲,有么有?

数据模型设计的重要性在这里充分体现出来。

SQL实例系列 之二

有产品表,产品多属性表,订单表,订单详情表。产品多属性表记录产品的多个属性,比如颜色为红色,尺寸为5寸的产品组成一个新的SKU插入产品多属性表,那么这条记录必定就有它的主SKU,所以产品表中通过一个多属性字段记录该产品是否是多属性产品。产品表中有记录该产品最早上线时间。订单详情表中记录了订单具体的产品,每个订单详情中的产品记录有主SKU字段和是否多属性字段和多属性SKU,它通过主SKU和多属性SKU和产品表以及产品多属性表产品联系。每个订单都有创建时间。

现在要查找一批产品从它的最早上线时间开始,顺延一段时间(比如30天)的这一段时间内产生的销售总额(分货币显示)和有销售的产品和涉及到的SKU数以及涉及到的所有订单并且在每个订单后列出这些产品贡献的销售额(分货币显示)。

主要使用如下SQL获取所有符合的记录:

SELECT p.product_id, p.product_name, p.product_sku, p.product_is_more_var, p.product_is_list, if(p.product_is_more_var,pm.product_more_var_sku,p.product_sku) as display_sku, if(p.product_create_time,from_unixtime('%Y%M%D',p.product_create_time),0) as product_create_time, if(p.product_first_list_time,from_unixtime('%Y%M%D',p.product_first_list_time),0) as product_first_list_time,
o.order_number, o.order_currency_code,
od.order_product_qty, od.order_price
FROM 
(product p LEFT JOIN product_more_vars pm ON p.produc_id = pm.product_id) 
JOIN 
(order o JOIN order_detail ON o.order_id = od.order_id) 
ON od.order_product_sku = if(p.product_is_more_var,pm.product_more_var_sku,p.product_sku)
WHERE ((o.order_create_time > product_first_list_time) AND (o.order_create_time < product_first_list_time+30*24*2600))
AND product_id in(......)

可以很容易的在后面添加GROUP BY语句进行各类汇总,如果添加多次GROUP BY语句发送SQL,看起来效率不高(这里是大数据集)。所以最终决定循环一遍这个结果集,然后顺便做各种汇总,这样这里就使用了一个多重嵌套的数组。

关于使用JOIN还是LEFT JOIN,需要紧记,当需要一个集合全部记录时,LEFT JOIN就很合适,以上提到的产品与产品多属性表就必须使用LEFT JOIN来获取完整的产品集合,这个搞法奇葩地方在于,当使用SKU定位产品信息时,你到产品表中可能无法匹配,因为它可能是多属性的子SKU,同样,你到多属性表中定位这个SKU时,可能也无法定位,因为这个SKU的产品是非多属性的,产品表中SKU就是它的实际SKU。

然后把这个汇总数组传递到视图,在视图中调用JS显示详情(比如涉及到的所有订单),如果一页显示不下还要做翻页处理(JS端)。

SQL实例系列 之一

有产品表,产品上架表,站点表,产品表和产品上架表的关系是1对n关系,站点表和产品上架表也是1对n的关系,产品表和站点表通过产品上架表实现了n对n的关系。通俗说就是,产品上架表中记录了每个产品、产品上架时间、是否在上架以及上架到了哪个站点。

现在要找出所有产品的最早上架时间以及是否已经上架(注,产品上架表中记录了同一个产品的多条记录,都有一个上架时间,以及这个产品是否在上架,只要有一个在上架就说明这个产品在上架的),那么从字面理解就是找出同一产品的所有记录,按照时间从小到大排序,取第一条记录的时间即为最早上架时间,然后对同一产品所有记录进行循环,判断是否有产品在上架,一旦碰到真就返回。

这个搞法面对数据表里面的上10万条数据时(超过1万产品),实在让人发毛。虽然从程序上来看,实现非常简单,也很好理解,但是它是最低效的。更加高效的做法应该是充分利用数据库的特征:

SELECT count(is_list) as is_list, create_time 
FROM product_site 
GROUP BY product_id ORDER BY create_time ASC

通过数据表索引的帮助,这条语句非常快速的返回了结果,虽然也有上万条记录,但是这个做法比最原始的做法至少提升10倍。10倍是什么概念,如果原始做法要10秒,那么后面的这个搞法只要1秒。

实际工作中遇到的问题,是为总结记录。