一、前言

在前段工作中,曾几次收到超级话题积分漏记的用户反馈。通过源码的阅读分析后,发现问题出在高并发分布式场景下的计数器上。计数器的值会影响用户当前行为所获得积分的大小。比如,当用户在某超级话题下连续第n(n即计数器的值)次进行转发帖子时,将会获得与n相关的分数。然而,在第一次改进后问题依然存在。所以,这次在之前的基础上,通过使用MySQL变量的途径来解决该问题。

出处:

List<Publisher> Publishers = new List<Publisher>();
            Publisher publish1 = new Publisher();
            publish1.Code = "01";
            publish1.Name = "北京出版社1";
            Publishers.Add(publish1);

            Publisher publish2 = new Publisher();
            publish2.Code = "02";
            publish2.Name = "北京出版社2";
            Publishers.Add(publish2);

            List<Book> Books = new List<Book>();
            Book boo1 = new Book();
            boo1.Publisher = new Publisher() { Name = "北京出版社1",Code="01" };
            boo1.Title = "书名1";
            boo1.PublisherCode = "01";
            Books.Add(boo1);

            Book boo2 = new Book();
            boo2.Publisher = new Publisher() { Name = "北京出版社2",Code="02" };
            boo2.Title = "书名2";
            boo2.PublisherCode = "02";
            Books.Add(boo2);

            Book boo3 = new Book();
            boo3.Publisher = new Publisher() { Name = "北京出版社2",Code="02" };
            boo3.PublisherCode = "02";
            boo3.Title = "书名3";
            Books.Add(boo3);


            //使用组连接
            var GroupQuery = (from publisher in Publishers
                            join book in Books on publisher equals book.Publisher 
                            into publisherBooks
                            select new
                            {
                                PublisherName = publisher.Name,
                                Books = publisherBooks
                            }).ToList();

             //使用Group
            var QueryByGroup = (from book in Books
                        group book by book.Publisher 
                        into grouping
                        select new
                        {
                            PublisherName = grouping.Key.Name,
                            Books = grouping.ToList()
                        }).ToList();

            var joinQuery = from publisher in Publishers
                            join book in Books
                                on publisher equals book.Publisher


                            select new
                            {
                                PublisherName = publisher.Name,
                                BookName = book.Title
                            };
            var tem = joinQuery.ToList();

            //left join
            var joinQuery2 = (from publisher in Publishers
                              join book in Books on publisher.Code equals book.PublisherCode 
                              into publisherBooks
                              from publisherBook in publisherBooks.DefaultIfEmpty()
                            select new
                            {
                                PublisherName = publisher.Name,
                                BookName = (publisherBook == default(Book)) ? "no book" : publisherBook.Title
                            }).ToList();

            //cross join
            var crossJoinQuery = (from publisher in Publishers
                                 from book in Books
                                 select new
                                 {
                                     PublisherName = publisher.Name,
                                     BookName = book.Title
                                 }).ToList();

二、到底MySQL的变量分哪几类?

MySQL变量一共分为两大类:用户自定义变量和系统变量。如下:

  • 用户自定义变量
    • 局部变量
    • 会话变量
  • 系统变量
    • 会话变量
    • 全局变量

本文涉及的内容为用户自定义会话变量,若对其他分类无感,请点击这里。

PS:用户定义的会话变量和系统定义的会话变量有什么区别?

https://www.cnblogs.com/easypass/archive/2010/12/

 

局部变量

局部变量一般用于SQL的语句块中,比如存储过程中的begin和end语句块。其作用域仅限于该语句块内。生命周期也仅限于该存储过程的调用期间。

 1 drop procedure if exists add;
 2 create procedure add
 3 (
 4     in a int,
 5     in b int
 6 )
 7 begin
 8     declare c int default 0;
 9     set c = a + b;
10     select c as c;
11 end;

上述存储过程中定义的变量c就是局部变量。

08/1900127.html

 

会话变量

会话变量即为服务器为每个客户端连接维护的变量。在客户端连接时,使用相应全局变量的当前值对客户端的回话变量进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量。其作用域与生命周期均限于当前客户端连接。

会话变量的赋值:

1 set session var_name = value;
2 set @@session.var_name = value;
3 set var_name = value;

会话变量的查询:

1 select @@var_name;
2 select @@session.var_name;
3 show session variables like "%var%";

1.数据库访问优化法则

 

要正确的优化SQL,我们需要快速定位能性的瓶颈点,也就是说快速找到我们SQL主要的开销在哪里?而大多数情况性能最慢的设备会是瓶颈点,如下载时网络速度可能会是瓶颈点,本地复制文件时硬盘可能会是瓶颈点,为什么这些一般的工作我们能快速确认瓶颈点呢,因为我们对这些慢速设备的性能数据有一些基本的认识,如网络带宽是2Mbps,硬盘是每分钟7200转等等。因此,为了快速找到SQL的性能瓶颈点,我们也需要了解我们计算机系统的硬件基本性能指标,下图展示的当前主流计算机性能指标数据。

 

图片 1 

从图上可以看到基本上每种设备都有两个指标:

延时(响应时间):表示硬件的突发处理能力;

带宽(吞吐量):代表硬件持续处理能力。

 

从上图可以看出,计算机系统硬件性能从高到代依次为:

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

由于SSD硬盘还处于快速发展阶段,所以本文的内容不涉及SSD相关应用系统。

根据数据库知识,我们可以列出每种硬件主要的工作内容:

CPU及内存:缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算;

网络:结果数据传输、SQL请求、远程数据库访问(dblink);

硬盘:数据访问、数据写入、日志记录、大数据量排序、大表连接。

 

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容,可以整理出如下图所示的性能基本优化法则:

 

图片 2 

这个优化法则归纳为5个层次:

1、  减少数据访问(减少磁盘访问)

2、  返回更少数据(减少网络传输或磁盘访问)

3、  减少交互次数(减少网络传输)

4、  减少服务器CPU开销(减少CPU及内存开销)

5、  利用更多资源(增加资源)

 

由于每一层优化法则都是解决其对应硬件的性能问题,所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法,因此针对低速设备问题的可优化手段也更多,优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案,而不应该首先想到的是增加资源解决问题。

以下是每个优化法则层级对应优化效果及成本经验参考:

 

优化法则

性能提升效果

优化成本

减少数据访问

1~1000

返回更少数据

1~100

减少交互次数

1~20

减少服务器CPU开销

1~5

利用更多资源

@~10

 

接下来,我们针对5种优化法则列举常用的优化手段并结合实例分析。

 

全局变量

全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量,必须具有SUPER权限。全局变量作用于server的整个生命周期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置文件。

全局变量的设置:

1 set global var_name = value; //注意:此处的global不能省略。根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION
2 set @@global.var_name = value; //同上

全局变量的查询:

1 select @@global.var_name;
2 show global variables like "%var%";

二、Oracle数据库两个基本概念

三、MySQL用户自定义变量详解

你可以利用SQL语句将值存储在用户自定义变量中,然后再利用另一条SQL语句来查询用户自定义变量。这样以来,可以再不同的SQL间传递值。

用户自定义变量的声明方法形如:@var_name,其中变量名称由字母、数字、“.”、“_”和“$”组成。当然,在以字符串或者标识符引用时也可以包含其他字符(例如:@’my-var’,@”my-var”,或者@`my-var`)。

用户自定义变量是会话级别的变量。其变量的作用域仅限于声明其的客户端链接。当这个客户端断开时,其所有的会话变量将会被释放。

用户自定义变量是不区分大小写的。

使用SET语句来声明用户自定义变量:

1 SET @var_name = expr[, @var_name = expr] ...

在使用SET设置变量时,可以使用“=”或者“:=”操作符进行赋值。

当然,除了SET语句还有其他赋值的方式。比如下面这个例子,但是赋值操作符只能使用“:=”。因为“=”操作符将会被认为是比较操作符。

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+

用户变量的类型仅限于:整形、浮点型、二进制与非二进制串和NULL。在赋值浮点数时,系统不会保留精度。其他类型的值将会被转成相应的上述类型。比如:一个包含时间或者空间数据类型(temporal
or spatial data type)的值将会转换成一个二进制串。

如果用户自定义变量的值以结果集形式返回,系统会将其转换成字符串形式。

如果查询一个没有初始化的变量,将会以字符串类型返回NULL。

数据块(Block)

数据块是数据库中数据在磁盘中存储的最小单位,也是一次IO访问的最小单位,一个数据块通常可以存储多条记录,数据块大小是DBA在创建数据库或表空间时指定,可指定为2K、4K、8K、16K或32K字节。下图是一个Oracle数据库典型的物理结构,一个数据库可以包括多个数据文件,一个数据文件内又包含多个数据块;

 

图片 3 

不要在同一个非SET语句中同时赋值并使用同一个用户自定义变量

用户自定义变量可以用于很多上下文中。但是目前并不包括那些显式使用常量的表达式中,比如SELECT中的LIMIT子句,或者LOAD
DATA中的IGNORE N
LINES的字句中。

通常来说,除了在SET语句中,不要再同一个SQL语句中同时赋值并使用同一个用户自定义变量。举个变量自增的例子,下面的是没问题的:

1 SET @a = @a + 1;

对于其他语句,比如SELECT,也许会得到期望的效果,但这真心不靠谱。比如下面的语句,也许你自然地会认为MySQL会先执行@a的值,然后再进行赋值操作:

1 SELECT @a, @a:=@a+1, ...;

然而,用户自定义变量表达式的计算顺序还没有定义呢。

除此之外,还有另一个问题。变量的默认返回类型由语句开始时的类型决定的,正如下面的例子:

1 mysql> SET @a='test';
2 mysql> SELECT @a,(@a:=20) FROM tbl_name;

上述的SELECT语句中,MySQL会报告给客户端第一列的字段类型为字符串,同时将所有对@a变量的使用均转换为字符串处理,尽管在SELECT语句中将@a变量设置为数字类型。在SELECT语句执行后,@a变量才会在下一个语句中识别为数字类型。

为了避免上述问题的发生,要么不在同一个语句中同时赋值并使用变量,要么在使用之前,将变量设置为0,0.0,或者”,以确定它的数据类型。

ROWID

ROWID是每条记录在数据库中的唯一标识,通过ROWID可以直接定位记录到对应的文件号及数据块位置。ROWID内容包括文件号、对像号、数据块号、记录槽号,如下图所示:

 图片 4

变量的值是在SQL发送到客户端后才计算的

在SELECT语句中,在每一个select表达式被发送给客户端后,才会进行计算。这就意味着,在形如HAVING,GROUP
BY和ORDER
BY只句中有使用在当前select表达式定义的变量的情况下,该语句将不会得到如期的效果。

1 mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

上述在HAVING只句中使用了在当前的select列表中定义的别名b,其使用了变量@aa。这条语句并不会得到如期的效果:@aa变量为上一次SQL语句执行的结果集中的ID值,并非当前的。

三、数据库访问优化法则详解

四、MySQL用户自定义变量的实际应用举例

1、减少数据访问

项目

超级话题积分系统

1.1、创建并使用正确的索引

数据库索引的原理非常简单,但在复杂的表中真正能正确使用索引的人很少,即使是专业的DBA也不一定能完全做到最优。

索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销,正确的索引可以让性能提升100,1000倍以上,不合理的索引也可能会让性能下降100倍,因此在一个表中创建什么样的索引需要平衡各种业务需求。

索引常见问题:

索引有哪些种类?

常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:

B-TREE索引也称为平衡树索引(Balance
Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。

叶子节点内容:索引字段内容+表记录ROWID

根节点,分支节点内容:当一个数据块中不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,根节点与分支节点保存了索引树的顺序及各层级间的引用关系。

         一个普通的BTREE索引结构示意图如下所示:

 

 图片 5

如果我们把一个表的内容认为是一本字典,那索引就相当于字典的目录,如下图所示:

 图片 6

图片 7 

 

 

 

图中是一个字典按部首+笔划数的目录,相当于给字典建了一个按部首+笔划的组合索引。

一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。

一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。

SQL什么条件会使用索引?

当字段上建有索引时,通常以下情况会使用索引:

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,…,?)

INDEX_COLUMN like ?||’%’(后导模糊查询)

T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)

 

SQL什么条件不会使用索引?

 

查询条件

不能使用索引原因

INDEX_COLUMN <> ?

INDEX_COLUMN not in (?,?,…,?)

不等于操作不能使用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || ‘a’ = ?

经过普通运算或函数运算后的索引字段不能使用索引

INDEX_COLUMN like ‘%’||?

INDEX_COLUMN like ‘%’||?||’%’

含前导模糊查询的Like语法不能使用索引

INDEX_COLUMN is null

B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引

NUMBER_INDEX_COLUMN=’12345′

CHAR_INDEX_COLUMN=12345

Oracle在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。

a.INDEX_COLUMN=a.COLUMN_1

给索引查询的值应是已知数据,不能是未知字段值。

注:

经过函数运算字段的字段要使用可以使用函数索引,这种需求建议与DBA沟通。

有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引

如:我们company表建了一个id+name的组合索引,以下SQL是不能使用索引的

Select * from company where name=?

Oracle9i后引入了一种index skip
scan的索引方式来解决类似的问题,但是通过index skip
scan提高性能的条件比较特殊,使用不好反而性能会更差。

 

我们一般在什么字段上建索引?

这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

1、字段出现在查询条件中,并且查询条件可以使用索引;

2、语句执行频率高,一天会有几千次以上;

3、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

小表(记录数小于10000行的表):筛选比例<10%;

大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

      单条记录长度≈字段平均内容长度之和+字段数*2

 

以下是一些字段是否需要建B-TREE索引的经验分类:

 

 

字段类型

常见字段名

需要建索引的字段

主键

ID,PK

外键

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

有对像或身份标识意义字段

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

索引慎用字段,需要进行数据分布及使用场景详细评估

日期

GMT_CREATE,GMT_MODIFIED

年月

YEAR,MONTH

状态标志

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

类型

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

区域

COUNTRY,PROVINCE,CITY

操作人员

CREATOR,AUDITOR

数值

LEVEL,AMOUNT,SCORE

长字符

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不适合建索引的字段

描述备注

DESCRIPTION,REMARK,MEMO,DETAIL

大字段

FILE_CONTENT,EMAIL_CONTENT

 

如何知道SQL是否使用了正确的索引?

简单SQL可以根据索引使用语法规则判断,复杂的SQL不好办,判断SQL的响应时间是一种策略,但是这会受到数据量、主机负载及缓存等因素的影响,有时数据全在缓存里,可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用,需要到数据库中查看SQL真实的执行计划,这个话题比较复杂,详见SQL执行计划专题介绍。

 

索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少?

这个没有固定的比例,与每个表记录的大小及索引字段大小密切相关,以下是一个普通表测试数据,仅供参考:

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。

 

术语

积分行为:如转发、评论超级话题下的帖子、签到某超级话题或者帖子被其他人回复等行为。

积分行为次数:产生积分行为的累计次数。

1.2、只通过索引访问数据

有些时候,我们只是访问表中的几个字段,并且字段内容较少,我们可以为这几个字段单独建立一个组合索引,这样就可以直接只通过访问索引就能得到数据,一般索引占用的磁盘空间比表小很多,所以这种方式可以大大减少磁盘IO开销。

如:select id,name from company where type=’2′;

如果这个SQL经常使用,我们可以在type,id,name上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后,SQL就可以直接通过my_comb_index索引返回数据,不需要访问company表。

还是拿字典举例:有一个需求,需要查询一本汉语字典中所有汉字的个数,如果我们的字典没有目录索引,那我们只能从字典内容里一个一个字计数,最后返回结果。如果我们有一个拼音目录,那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页,拼音目录有20页,那我们的数据访问成本相当于全表访问的50分之一。

切记,性能优化是无止境的,当性能可以满足需求时即可,不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里,所以这种只通过索引访问数据的方法一般只用于核心应用,也就是那种对核心表访问量最高且查询字段数据量很少的查询。

业务场景

用户在某超级话题下,第N次产生累计积分的行为,如转发微博,会增加该用户在该超级话题下的积分总数。具体的积分规则见长文章。

1.3、优化SQL执行计划

SQL执行计划是关系型数据库最核心的技术之一,它表示SQL执行时的数据访问算法。由于业务需求越来越复杂,表数据量也越来越大,程序员越来越懒惰,SQL也需要支持非常复杂的业务逻辑,但SQL的性能还需要提高,因此,优秀的关系型数据库除了需要支持复杂的SQL语法及更多函数外,还需要有一套优秀的算法库来提高SQL性能。

目前ORACLE有SQL执行计划的算法约300种,而且一直在增加,所以SQL执行计划是一个非常复杂的课题,一个普通DBA能掌握50种就很不错了,就算是资深DBA也不可能把每个执行计划的算法描述清楚。虽然有这么多种算法,但并不表示我们无法优化执行计划,因为我们常用的SQL执行计划算法也就十几个,如果一个程序员能把这十几个算法搞清楚,那就掌握了80%的SQL执行计划调优知识。

由于篇幅的原因,SQL执行计划需要专题介绍,在这里就不多说了。

 

问题

曾有用户反馈说超级话题积分有漏记的情况:为什么我评论了却没有加分;为什么转发了超级话题帖子没有加分等等。随后,我们立即通过查询后台的积分记录发现,会看到转发行为在第5次时,积分的增加却为0。这显然是不正常的。

首先,排除了根据积分行为的次数来计算积分值的问题。比如第5次转发微博应增加6分。这块的规则,利用二分法写死在程序里面,也做过单元测试,不会有问题。那么,问题就锁定在这个积分行为的次数。

首先来看看积分次数的获取:

1 public static function find($uid, $aid, $status) {  
2     $sql = 'SELECT * FROM '.self::table($aid).' WHERE uid = ? AND aid = ? AND status = ?';
3     return Comm_Db::d(Comm_Db::DB_BASIC)->fetchRow($sql, array($uid, $aid, $status));
4 }

然后,利用上述find()方法来取得该用户在某超级话题下的某积分行为的累计次数。这是有问题的,在于读于从库,但并不保证从库的值是最新的,所以导致当前获取的积分行为次数并不一定是正确的(小于等于实际的值)。

随后,程序会根据当前的次数计算积分值,并分别更新积分值和该行为的积分行为次数值。

所以,这次利用MySQL的用户自定义会话变量的方式,来解决上述问题。

1 public static function incCounter($uid, $aid, $status) {
2     $db = Comm_Db::d(Comm_Db::DB_BASIC);
3     $sql = "UPDATE ". self::table($aid) ." SET `ctn_counter`=@ctn_counter:=`ctn_counter`+1 WHERE `uid` = ? AND `aid` = ? AND `status` = ?";
4     $db->execute($sql, array($uid, $aid, $status));
5     $sql = "SELECT @ctn_counter";
6     $rs = $db->fetchOne($sql, null, true);
7     return $rs;
8 }

改进后,如上述函数,程序将先进行调用incCounter()函数,将当前的积分行为次数自增,并将值存入当前变量中。随后,立即将其读取并返回给PHP进行积分处理。这样一来,就保证了积分行为次数的正确性。

2、返回更少的数据

发表评论

电子邮件地址不会被公开。 必填项已用*标注