暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySQL DBA的自我修养(二十六)

记忆乘凉 2017-01-13
243


262:对于表的一些操作包括:ER 图设计、分区、分表分库、水平拆分、垂直拆分、逆规范化.

详看MySQL优化思路。


263:表、索引、SQL、架构、参数优化、硬件层面的优化。

详看MySQL优化思路。


264:13.2.10 Subquery Syntax。

http://dev.mysql.com/doc/refman/5.5/5.6/5.7/en/subqueries.html

子查询语法

子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用。MySQL 支持 SQL 标准要求的所有子查询格式和操作,也扩展了特有的几种特性。

子查询没有固定的语法,一个子查询的例子如下:

SELECT * FROM article WHERE uid IN(SELECT uid FROM userWHERE status=1)

对应的两个数据表如下:

article 文章表:

aid

title

content

uid

1

文章1

文章1正文内容...

1

2

文章2

文章2正文内容...

1

3

文章3

文章3正文内容...

2

4

文章4

文章4正文内容...

3

 

user 用户表:

uid

username

password

status

1

admin

b7e591c246d010bb2ccd77d52490c85e

1

2

小明

a193686a53e4de85ee3f2ff0576adf01

1

3

Jack

0193686a35e4de85ee3f2ff0567adf490

0

查询返回结果如下所示:

aid

title

content

uid

1

文章1

文章1正文内容...

1

2

文章2

文章2正文内容...

1

3

文章3

文章3正文内容...

2

在该例子中,首先通过子查询语句查询出所有status=1 uid,实际的查询类似于:

SELECT * FROM article WHERE uid IN(1,2)

MySQL 子查询分类

上面演示了一个简单的子查询例子,根据子查询的返回数据形式(如上例中返回的 uid 序列),可以分为如下几类:

标量子查询:返回单一值的标量,最简单的形式。

列子查询:返回的结果集是 N 行一列。

行子查询:返回的结果集是一行 N 列。

表子查询:返回的结果集是 N N 列。

MySQL 子查询操作符

在主查询中,可以使用比较操作符以使用操作符来对子查询返回的结果进行比较,从而确定查询的条件。如上面例子使用了 IN 操作符。

可以使用的操作符有:= > <>= <= <> ANY IN SOME ALL EXISTS ,我们将在本节余下的教程中学习这些操作符在子查询中的使用方法。

 


265:表、索引练习作业。

1、怎么根据一个 sql 来判断是否需要建立索引、怎么建立索引、建立索引的依据是什么?

通过执行计划来判断该SQL是否是一个糟糕的SQL并且该SQL是否适合建立索引;如该SQL有适合建立索引的条件,如where中的列,就在该列上建立索引,建立索引前要查看该列的唯一度、数据量等来判断是否是一个合适的列。

2、建立索引以后,如何来评估这个索引的效果,高效还是低效?

通过执行计划来查看该SQL走索引后的状态,过滤的数据,使用的条件等等来进行判断SQL是高效还是低效。

3、索引的几大作用,在哪些场景下需要建立什么样的索引?

1.提高查询效率

2.约束

3.避免多余的资源消耗

根据实际查询情况进行索引的选择。

4、对于 tpcc 测试系统,实现表的分区、分表(水平拆分、垂直拆分)、分库(水平拆分、垂直拆分)、表的逆规范化。

要求自己写例子,包括 SQL、效果演示。

表的拆分

实现对order表的垂直拆分,拆分为订单售后数据和新订单数据,订单售后数据包括列o_carrier_ido_all_local

建表:

create table orders1 (o_id int not null,o_d_idtinyint(4) not null ,o_w_id smallint(6) not null,o_carrier_id tinyint(4),o_all_localtinyint(4),primary key(o_id,o_d_id,o_w_id));

create table orders2 (o_id int not null,o_d_idtinyint(4) not null ,o_w_id smallint(6) not null,o_c_id int,o_entry_ddatetime,o_ol_cnt tinyint(4),primary key(o_id,o_d_id,o_w_id));


创建索引

alter table orders2 add index idx_cid(o_c_id);

alter table orders2 add index idx_ent(o_entry_d);


导入数据:

insert into orders1 selecto_id,o_d_id,o_w_id,o_carrier_id,o_all_local from orders;

insert into orders2 selecto_id,o_d_id,o_w_id,o_c_id,o_entry_d,o_ol_cnt from orders;


实现对order表的水平拆分,拆分规则按照用户编号进行拆分,拆成10个表?

根据数据量建表:create tableorder_1 as select * from orders limit 9000;


如何实现通过订单id查询订单信息,如何实现订单号的分库查询?

通过订单id查询订单信息要进行表关联。

根据订单号进行分库分表,进行查询时会根据主键进行计算。

将拆分后的表分布到2MySQL服务器上,并写出应用如何来快速定位订单所在的服务器?

先将表进行转移,利用导出sql的方式。利用主键求余进行服务器的选择,定位到不同的表上进行查询。

解释水平拆分如何提升TPS

拆分表后,进行SQL查询会根据ROWID进行计算进入不同的小表进行查询,在小表中执行相比大表当然要快了。

解释垂直拆分如何将售后订单查询从新订单中分离出去,提升新订单生成速度,同时提升QPS

业务拆分,根据不同的业务需要对表进行垂直拆分,在订单表中对售后相关的信息进行分离,形成售后表和订单表,在两个表中都保留主键列,根据业务需求进行对应表的查询。

表的分区

如何对history表进行分区,便于历史归档,同时写出归档历史数据的相关语句?
重建history表: 根据天数进行分区

create table newhis(h_c_id int,h_c_d_idtinyint(4),h_c_w_id smallint(6),h_d_id tinyint(4),h_w_id smallint(6),h_datedatetime,h_amount decimal(6,2),h_data varchar(24))partition by  range(DAY(h_date)(par)(partition p0 valuesless than(5),partition p1 values less than (6));

插入数据:

insert into newhis select * from history;

如何对stock进行分区,便于我们查询一个仓库内所有的物品信息,并且展示分区后的执行计划,显示出分区效果?

      select * fromstock where s_w_id=5;

表的逆范式化

如何通过你逆范式化实现下面SQL的优化

select i_name,i_price,s.* from stock s,item i wherei.i_id=s.s_i_id and  s_w_id=1;


建表:

create table stock_item as select i_name,i_price,s.*from stock s,item i where i.i_id=s.s_i_id ;

创建索引:

alter table stock_item add primary key(s_i_id,s_w_id);

alter table stock_item add index idx_wid(s_w_id);

SQLselect i_name,i_price from stock_item where s_w_id=1;




266not in 如何和 left join 进行转换,转换的理论依据是什么,怎么改写。

SQL中用NOT IN会影响性能,导致的主要原因就是索引无效,所以最好是将NOT IN改为其他方式实现。

没修改之前的NOT IN写法:

[sql] view plain copy

SELECT DISTINCT 

    t2.a_id 

FROM 

    temp_b t2 

WHERE 

    t2.a_id NOTIN( 

        SELECTDISTINCT 

           t4.a_id 

        FROM 

            b ASt3, 

            temp_bAS t4 

        WHERE 

           t4.a_id = t3.a_id 

    ) ;

修改为LEFT JOIN写法:

[sql] view plain copy

SELECT DISTINCT 

    t6.a_id 

FROM 

    ( 

       SELECT 

           t2.a_id, 

           t5.a_id AS tempId 

        FROM 

            temp_bt2 

        LEFTJOIN( 

            SELECTDISTINCT 

               t4.a_id 

           FROM 

                bAS t3, 

               temp_b AS t4 

           WHERE 

               t4.a_id = t3.a_id 

        )AS t5 ONt2.a_id = t5.a_id 

    )AS t6 

WHERE 

t6.tempId IS NULL 

 


267not in 如何和 not extsts 进行转换,转换的理论依据是什么,怎么改写。

修改为NOT EXISTS写法:

[sql] view plain copy

SELECT DISTINCT 

    t2.a_id 

FROM 

    temp_b t2 

WHERE 

    NOTEXISTS( 

       SELECT 

            1 

        FROM 

            ( 

               SELECT DISTINCT 

                   t4.a_id 

               FROM 

                   b AS t3, 

                   temp_b AS t4 

               WHERE 

                   t4.a_id = t3.a_id 

            )ASt5 

        WHERE 

           t5.a_id = t2.a_id 

)

 


268not innot existsleft join 他们的执行方式以及各自适合的场景;

A中的数据在表B中存在的效率比较 INNER JOIN = EXISTS = IN

A中的数据在表B中不存在的效率比较 LEFT JOIN > NOT EXISTS > NOT IN

1left join本身更耗资源(需要更多资源来处理产生的中间结果集)

2left join的中间结果集的规模不会比表A

3left join还需要对left join产生的中间结果做is null的条件筛选,而写法一则在两个集合join的同时完成了筛选,这部分开销是额外的

 


269:通过索引来优化 not innot exists

给使用not innot exists的列添加索引,但如果查询数量大于30%优化器会不走索引,使用全表扫描,效率相比之下会更高。

对于in exists的性能区别:

如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists

其实我们区分inexists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了

对于not in not exists的性能区别:

not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not in,并使用anti hash join.

如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null NOT IN 在基于成本的应用中较好。

 


270:如何分析一个需求,写出一个 SQL

我要去奖励一些用户,他们的单笔消费金额,只要大于过去商场 12 个月里面的任何一个月的平均消费金额。

Select username from user,show where s.date>=DateAdd(m,-12,getdate())and u.spend > s.avgspend;


271:如何对>any 进行替换,将上面的 SQL 替换成 min(),假设没有空值。


272:如何对>all 进行替换,使用 max()替换 all,假设没有空值。


273inexistsjoin 如何进行互相转换,参考这个例子:最近一个月有消费记录的用户。


274:如何改写一个<> ALL,使用 not innot existsleft join<> any 不能替换成 not in

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROMt2);


275= ANY in 的互相转换。

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);


276:对于字符串和数字来说,null 都是最小值,因此如果出现 > any (有空值)< all (有空值),这个><就失去了意义,针对上面上面的情况,处理方式。

select * from customer where c_id >= any (select idfrom t1);

select * from customer where c_id >= any (select idfrom t1 where id is not null);


277:如何优化子查询。

1mysql 版本不一样,子查询的优化效果不一样,5.6 开始,CBO 对于子查询已经可以进行部分的自动优化;

2、版本5.5 以前,尽量不要使用子查询;

1.inexistsjoin 互相转换,需要测试效果。

2.not innot existsleft join 互相转换,需要测试效果。

3.将子查询拆开,合并到外边,这个主要针对 from 中有子句的情况。

 


文章转载自记忆乘凉,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论