Mysql_Note9

1.如何设计才能提高性能

1.1总结

步骤补充
1.修改数据类型以节省存储空间既可以使用文本类型也可以使用整数类型的字段,要使用整数类型,而不要用文本类型
2.在利大于弊的情况下增加冗余字段增加冗余字段的时候,不要忘了确保数据一致性
3.把大表中查询频率高的字段和查询频率低的字段拆分成不同的表
4.尽量使用非空约束

1.2思考题

假设我们有一个这样的订单表,如下所示:

在这里插入图片描述

经过统计发现,商品信息,订货数量和交易时间这 3 个字段使用得很频繁,地址和电话这 2 个字段使用得相对比较少。针对这样的表格,你会怎么优化呢?

可以拆分成2个表:

表1:包含订单编号、商品编号、订货数量和交易时间
表2:包含订单编号、地址、电话

2.如何充分利用系统资源

3.1优化系统资源配置

系统参数控制着资源的配置,调整系统参数的值,可以帮助我们提升资源的利用效率

InnoDB_flush_log_at_trx_commit

前提:存储引擎为innodb

含义
00表示每隔1秒将数据写入日志,并将日志写入磁盘
1(默认)1表示每次提交事务的时,都把数据写入日志,并把日志写入磁盘
22表示每次提交事务的时候都将数据写入日志,但是日志每间隔 1 秒写入磁盘

InnoDB_buffer_pool_size

InnoDB 存储引擎使用缓存来存储索引和数据, 这个值越大,可以加载到缓存区的索引和数据量就越多,需要的磁盘读写就越少, 我们的计算机是 64 位机,内存也有128G,于是我们把这个参数的值调整为 64G

InnoDB_buffer_pool_instances

这个参数的意思是,将 InnoDB 的缓存区分成几个部分, 这样一来,就可以提高系统的并行处理能力,因为可以允许多个进程同时处理不同部分的缓存区

注意

修改完 MySQL 的配置文件 my.ini 之后,保存的时候,记得用 ANSI 的格式

3.2利用系统资源诊断问题

Performance Schema:性能监控数据

系统数据库performance_schema中的表setup_instruments 和 setup_consumers 中的数据.是启用监控的关键

setup_instruments 保存的数据,表示哪些对象发生的事件可以被系统捕获

setup_consumers 保存的数据用来控制保存哪些事件的信息

select name,enabled,timed from prfromance_schema.setup_instruments
NAME:事件名称ENABLED:是否启用了对事件的监控TIMED:表示是否收集事件的时间信息
wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tcYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commitYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queueYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_doneYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queueYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_indexYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_logYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_posYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_syncYESYES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queueYESYES

performance_schema.events_statements_history

这个表中记录了系统中所有进程中最近发生的查询事件,表中可以为每个进程保存的最大记录数由系统变量决定

show variable like '%performance_schema_events_statements_history_size%'

performace_schema.events_statements_history_long

这个表中记录了系统中所有进程中最近发生的查询事件

show variable like '%performance_schema_events_statements_history_size_long%'

示例

select truncate(timer_wait /1000000000000, 6) as duration ,
sql_text,event_id from perfromace_schema.events_statements_history_long
where truncate(time_wait /1000000000000, 6)<>0
and sql_text is not null
order by truncate(time_wait/10000000000,6) desc
limit 1,2
durationsql_textEVENT_ID
137.2529select count(*) from demo.trans17
137.2420select count(*) from demo.trans907

1.字段 TIMER_WAIT:表示这个查询消耗了多少时间,单位是微微秒,也就是万亿分之一秒

2.TRUNCATE(X,D) 函数:表示给 X 保留 D 位小数,注意这个函数是直接截取,没有四舍五入

3.字段 sql_text:表示执行的 SQL 语句的内容

4.EVENT_ID:表示事件编号

3.3思考题

前面提到,我把 InnoDB_flush_log_at_trx_commit 的值改成了 2,因为 0 虽然效率更高一些,但是在数据安全性方面不如 2。你知道为什么 0 的效率更高一些,但是数据安全性却不如 2 吗?

0表示每隔1秒写日志并且落盘,这样进程提交事务之后可以马上执行下一个任务,效率最高,但是如果出问题,会损失1秒钟的数据。2表示提交事务后需要写入日志,不能马上执行下一个任务,效率不如0,但是数据被写入了日志,所以安全性好于0

3.Mysql8的新特征

3.1 窗口函数

窗口函数的作用类似于在查询中对数据进行分组,不同的是,分组操作会把分组的结果聚合成一条记录,而窗口函数是将结果置于每一条数据记录中

语法结构

函数 over( [ partition by 字段 ] )

或者

函数 over 窗口 ... window 窗口名 as ( [ partition  by 字段名] )

示例

网站在每个城市的销售总额,在全国的销售总额,每个区的销售额占所在城市销售额中的比率,以及占总销售额中的比率

普通方法

第一步,计算总销售金额,并存入临时表 demo.a:

CREATE TEMPORARY TABLE demo.a -- 创建临时表 
SELECT SUM(salesvalue) AS salesvalue -- 计算总计金额
FROM demo.test1;

第二步,计算每个城市的销售总额并存入临时表 demo.b

CREATE TEMPORARY TABLE demo.b -- 创建临时表 
SELECT city,SUM(salesvalue) AS salesvalue -- 计算城市销售合计 
FROM demo.test1 
GROUP BY city;

第三步,计算各区的销售占所在城市的总计金额的比例,和占全部销售总计金额的比例

SELECT a.city AS 城市,a.county AS,a.salesvalue AS 区销售额,
       b.salesvalue AS 市销售额,a.salesvalue/b.salesvalue AS 市比率, 
       c.salesvalue AS 总销售额,a.salesvalue/c.salesvalue AS 总比率 
       FROM demo.test1 AS a 
JOIN demo.b AS b ON (a.city=b.city) -- 连接市统计结果临时表 
JOIN demo.a AS c -- 连接总计金额临时表 
ORDER BY a.city,a.county;

窗口函数

select city as 城市,county as,salesvalue as 区销售额,
sum(salesvalue) over(partition by city) as 市销售额,
salesvalue/sum(salesvalue) over(partition by city) as 市比率,
sum(salesvalue) over() as 总销售额,
salesvalue/sum(salesvalue) over() as 总比率
from demo.test1
oder by city,country;

窗口函数独有函数

函数名函数含义
rank()函数把并列计算在内,并且并列影响排位
dense_rank()函数也计算并列,但是并列不影响排位
row_number()函数不计算并列,只是简单排序
select student,points,
rank() over w as 排序2,
dense_rank() over w as 排序2
row_number() over w as 排序3
from demo.test2
window w as (oder by points desc)

3.2 公用表达式

语法结构

with 
cte名称 as (子查询)
select |delete|update 语句;

示例

假设我们有一个商品信息表(demo.goodsmaster)它保存的是商品信息,还有一个每日销售统计表 (demo.dailystatistics),保存的是每日的销售统计信息.现在超市经营者想要查出都卖了什么商品,我们就可以先通过子查询查出所有销售过的商品的商品编号,然后查出这些商品的商品信息

SELECT * FROM demo.goodsmaster WHERE itemnumber IN
(SELECT DISTINCT itemnumber FROM demo.dailystatistics); -- 子查询,查出所有销售过的商品的商品编号

普通公用表达式

with cte as (
select distinct itemnumber from demo.dailystatistics
)

selet * form demo.goodsmaster a join cte
on (a.itemnumber =cte.itemnumber)

注意

公用表表达式有一个优点,就是定义过公用表表达式之后的查询,可以像一个表一样多次引用公用表表达式,而子查询则不能

递归公用表达式

with recursive
cte名称 as (子查询)
select | delete|update 语句;

示例

尝试用查询语句列出所有具有徒孙身份的人员信息

with recursive
cte as (
select id,name,teacherid,1 as n from demo.teach where id =101 -- 种子查询
    union all
    select a.id,a.fname,a.teacher,n+1 From demo.teach as a join cte
    on (a.teaherid =cte.id) -- 递归查询,找出以递归公用表表达式的人为老师的人
    )
    select id,fname from cte where n>=3;
)

3.3 总结

12
窗口函数可以分组,而且可以在分组内排序,且可以在在原表数据的基础上进行统计和排序非常有用
公用表达式可以替代子查询,且可以被多次使用,适合查询有一个公共根节点的树形结构

3.4思考题

1.假设我有一个会员销售统计表(demo.memtrans), 请使用窗口函数查询会员名称,商品名称,销售金额,总计金额和销售占比

在这里插入图片描述

请使用窗口函数查询会员名称,商品名称,销售金额,总计金额和销售占比

select membername as 会员名称,
goodsname as 商品名称,
actualvalue as 销售金额,
sum(actualvalue) over() as 总金额,
actualvalue/(sum(actualvalue) over()) as 销售占比
from demo.memtrans
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值