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

值	含义
0	0表示每隔1秒将数据写入日志,并将日志写入磁盘
1(默认)	1表示每次提交事务的时,都把数据写入日志,并把日志写入磁盘
2	2表示每次提交事务的时候都将数据写入日志,但是日志每间隔 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_tc	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync	YES	YES
wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue	YES	YES

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

duration	sql_text	EVENT_ID
137.2529	select count(*) from demo.trans	17
137.2420	select count(*) from demo.trans	907

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 总结

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

3.4思考题

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

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

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