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_id、o_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查询订单信息要进行表关联。
根据订单号进行分库分表,进行查询时会根据主键进行计算。
将拆分后的表分布到2台MySQL服务器上,并写出应用如何来快速定位订单所在的服务器?
先将表进行转移,利用导出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);
SQL:select i_name,i_price from stock_item where s_w_id=1;
266:not 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 ;
267:not 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
);
268:not in、not exists、left join 他们的执行方式以及各自适合的场景;
表A中的数据在表B中存在的效率比较 INNER JOIN = EXISTS = IN
表A中的数据在表B中不存在的效率比较 LEFT JOIN > NOT EXISTS > NOT IN
(1)left join本身更耗资源(需要更多资源来处理产生的中间结果集)
(2)left join的中间结果集的规模不会比表A小
(3)left join还需要对left join产生的中间结果做is null的条件筛选,而写法一则在两个集合join的同时完成了筛选,这部分开销是额外的
269:通过索引来优化 not in、not exists;
给使用not in和not exists的列添加索引,但如果查询数量大于30%优化器会不走索引,使用全表扫描,效率相比之下会更高。
对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是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,假设没有空值。
273:in、exists、join 如何进行互相转换,参考这个例子:最近一个月有消费记录的用户。
274:如何改写一个<> ALL,使用 not in、not exists、left 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:如何优化子查询。
1、mysql 版本不一样,子查询的优化效果不一样,5.6 开始,CBO 对于子查询已经可以进行部分的自动优化;
2、版本5.5 以前,尽量不要使用子查询;
1.in、exists、join 互相转换,需要测试效果。
2.not in、not exists、left join 互相转换,需要测试效果。
3.将子查询拆开,合并到外边,这个主要针对 from 中有子句的情况。