云掣科技

云掣科技 查看完整档案

杭州编辑  |  填写毕业院校  |  填写所在公司/组织 www.dtsre.com/ 编辑
编辑

专注于云msp服务

个人动态

云掣科技 发布了文章 · 11月25日

MySQL|主从延迟问题排查(二)

二、案例分享二

2.1 问题描述

主库执行insert  select 批量写入操作,主从复制通过row模式下转换为批量的insert大事务操作,导致只读实例CPU资源以及延迟上涨

16:55~17:07

2.2 处理流程

1、接收到只读实例备库延迟告警后,我们观察到只读实例的CPU资源有有明显上涨,同时数据库有大量数据写入操作

image.png

image.png

image.png

2、延迟期间,只读实例的tps的趋势是先下降后上涨,binlog日志量达到12.54G,可以推断出主实例传输过来的批量的写入操作是同一事务中,再加上只读实例配置相对于主实例较低,所以导致这么大的延迟

image.png

2、查看主从延迟期间主实例的情况,可以看到主实例确实执行了大量的数据写入操作,以及主实例审计日志中,我们找到了批量写入操作

image.png

image.png

image.png

3、只读实例延迟趋势17:05后,只读实例tps上涨,同时同步延迟开始下降

image.png

image.png

4、延迟流程描述

  • 16:43 主实例执行insert select批量写入操作,主库执行完毕后,binlog以row的模式将所有的insert操作放在一个事务中传输到只读实例
  • 16:55 只读实例开始应用该大事务中的insert操作,tps跌落,数据库缓存写/日志写上涨
  • 17:05 大事务应用完毕,开始同步延迟期间的binlog操作,正常业务下多个小事务操作,tps上涨明显,延迟开始回落
  • 10:07 主从追平延迟期间的binlog,主从延迟恢复为0
查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月24日

MySQL|主从延迟问题排查(一)

一、案例分享

1.1 问题描述

大查询长时间执行无法释放DML读锁,后续同步主库的DDL操作获取DML写锁资源被阻塞等待,导致后续同步主库的操作堆积,主从延迟增长严重。从同步延迟的监控来看,延迟从17:11开始,17:51:59进行kill大查询操作,直到17:53建议业务方将大查询kill掉后才结束。

1.2 处理流程

1、当接收到只读实例的同步延迟告警后,登录到RDS的管理控制台查看实例当前会话执行情况,判断只读实例当前负载压力。从当前会话截图可以看到,会话并无明显堆积,但是有两个执行时间很久的大查询操作。

2、17:11延迟开始,17:51 kill大查询,17:53主从延迟恢复。我们仍需要排查这个期间主实例和只读实例的运行情况,分析造成主从延迟的具体原因

3、对主实例的排查

1)查看延迟期间主库是否有一些批处理/大事务操作,主库业务业务请求上涨或者有批量的更新操作。对此,我们主要观察主实例的QPS/TPS监控、MySQL_COMDML和日志读写的监控指标。

从以上截图中可以看到,主库TPS在主从延迟期间并没有明显的上涨,说明期间主库业务压力正常;主库MySQL_COMDML和日志读写在主从延迟期间也没有明显的上涨,说明期间主库也没有执行一些批量更新的大事务操作。

2)查看延迟期间主库是否有执行消耗较大的DDL操作。在RDS中若开启了审计日志,我们可以通过时间以及操作类型进行过滤排查

通过对审计日志的搜索,我们搜查到一条对视图定义进行alter的操作,该alter操作仅仅执行了2.32ms,其资源消耗本身并不大。

4、对只读实例的排查

1)查看延迟期间只读实例是否有较大负载压力,从只读实例延迟期间的会话执行情况以及资源消耗可以知道,延迟期间只读实例并无较大负载压力

2)从只读实例的QPS/TPS监控中可以看到,17:51kill掉大查询后只读实例的TPS异常上涨,17:53TPS恢复正常,延迟恢复。说明17:51~17:53期间只读实例在大量应用主库传输过来的binlog日志,恢复主从复制延迟。

5、捕捉延迟期间会话中的异常现象,大查询长时间执行未结束,执行explain操作显示为MDL锁等待,结合我们在主库审计日志中搜索到的alter操作,我们可以推断造成主从延迟的原因可能是只读实例大查询阻塞了从主库传输过来的Alter操作,导致后续延迟一直上涨,并在我们kill掉大查询后恢复。

6、为了印证我们的猜想,我们通过审计日志把相关操作的时间线进行梳理

  • 只读实例view_order_logistics_new相关的大查询执行了3602s还未执行完,一直持有表的DML读锁,不影响表的正常读写操作
  • 17:10 主库执行了ALTER ALGORITHM=UNDEFINED DEFINER=super_sha_prd_db@% SQL SECURITY DEFINER VIEW view_order_logistics_new 的操作
  • 只读实例view_order_logistics_new的大查询仍在执行中,此时主库执行alter操作传输到只读实例,alter操作需要的DML写锁与大查询持有的DML读锁冲突
  • alter操作无法获取到DML写锁从而开始等待锁资源,从主控传输过来的binlog也被阻塞,主从延迟开始上涨
  • 17:51:59 只读实例kill掉了view_order_logistics_new的大查询,只读实例TPS上涨,只读实例开始应用alter操作之后的所有binlog日志
  • 17:53:08 只读实例TPS恢复,应用延迟期间的binlog完毕,主从复制恢复正常

更多技术信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 1 收藏 0 评论 0

云掣科技 发布了文章 · 11月23日

MySQL|空间碎片化问题处理

一、空间碎片化严重案例分享

1.1 问题描述

实例磁盘空间近1个月上涨趋势明显,主要是个别日志表存储较大且部分表存在空间碎片化的现象。

1.2 处理流程

1、通过日常巡检以及监控发现某实例磁盘空间近1月上涨趋势明显

image.png

2、在询问业务方是否为正常的业务增长外,查看该实例top表空间的表,排查是否存在异常较大的表

1)查看数据存储量较大的schema

SELECT table_Schema  , round(SUM((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024), 2) AS Total_Size FROM tables GROUP BY table_schema ORDER BY Total_Size DESC;

image.png

2)查看具体schema下top表的空间使用情况

SELECT table_schema, table_name  , round(DATA_LENGTH / 1024 / 1024, 2) AS Data_Size , round(INDEX_LENGTH / 1024 / 1024, 2) AS Index_Size , round(DATA_FREE / 1024 / 1024, 2) AS Free_Size FROM tables WHERE table_schema = 'db_rim' ORDER BY Data_Size DESC LIMIT 10;

image.png

3、从以上截图中我们可以得到信息:db_rim库库下,rim_user_msg_log表的数据量很大,可查看是否可对该表中无效的数据进行清理?rim_user_msg_analyse这张表的碎片化将尽17G,可考虑对这些碎片化空间进行回收

4、最终采取的处理方法为:rim_user_msg_log表直接清空,rim_user_msg_analyse进行碎片化回收

1)清理前

image.png

2)清理后

image.png

3)实例磁盘空间使用趋势

image.png

二、表数据量过大清理案例

2.1 问题描述

通过对TOP表数据量的监控,我们可以看到sys_rest_server_log的数据量已经达到7000w,除空间占用外,我们更关注的是该表对数据库带来的性能隐患。若业务上或者人为不当的查询该表数据,其操作的资源消耗在一定程度上一定会对正常业务造成影响。

image.png

2.2 问题处理

1、该表当前的表现主要有两点,一点是某瞬时写入量极大,另外一点是表日增可达到300w记录数。对于数据库而言这种表行为或业务设计是不合理的,我们需要搞清楚该表究竟是用来记录哪些信息,业务设计是否合理?

通过观察表数据以及与相关开发人员沟通,我们得知该表会记录应用端所有的接口调用信息,我们所观察到的数据增长尖峰也正是由于业务量上涨而导致该表瞬间并发写入大量日志信息。

image.png

image.png

2、对于这种大数据量的日志记录信息,建议最好使用ELK这种日志分析服务,而不是使用数据库进行存储

3、限于开发人员开发能力有限,无法改造业务架构。针对该表日增长、瞬间增长都极大的情况,我们与开发人员沟通该表记录的数据是否可从源端上进行减少。只记录重要信息、非重要信息的接口日志入库进行屏蔽

4、确定表数据记录可从最根本减产后,我们仍需要对目前7000w的数据进行处理,将历史无效数据进行清理,并制定合适的数据清理策略

5、无效历史数据清理/数据清理策略

1、创建备份表: create table sys_rest_server_log_bak_0228_1 like sys_rest_server_log;  
2、表名替换 将源表sys_rest_server_log重命名为备份表,备份表sys_rest_server_log_bak_0208_1重命名为源表sys_rest_server_log,业务数据会写入空的新表,但是在rename期间这部分业务日志写入会失败【需关注】 rename table sys_rest_server_log to sys_rest_server_log_bak_0228_2,sys_rest_server_log_bak_0228_1 to sys_rest_server_log;  
3、将备份表sys_rest_server_log_bak_0208_2中的仍然需要的数据重新写入到sys_rest_server_log,分批次写入,sys_rest_server_log保留近15天数据。程序上可保证主键fd_id唯一,避免主键冲突。 insert into sys_rest_server_log select * from sys_rest_server_log_bak_0228_2 where fd_start_time between 'xxx' and 'xxx';  
4、调整平台数据清理策略,将服务运行日志备份天数改为15天,清除服务运行日志备份天数改为30天  
5、sys_rest_server_log_bak_0228_2表保留80+天记录数据,暂时可以不做清理,等程序维护的sys_rest_server_log_backup有完备的数据后,将sys_rest_server_log_bak_0228_2表清除;

三、总结归纳

2.1 如何判断表碎片化问题是否严重

表数据的频繁更新容易对表空间造成一定的碎片化,我们可以通过information_schema.table表中的data_free字段判断该表碎片化是否严重。

2.2 如何处理碎片化问题

optimize table tableName; alter table tablName engine=innodb;

2.3 大表历史数据清楚过程中的关注点

1、通过创建bak表与源表进行rename替换,rename期间会造成业务对该表数据写入失败 
2、bak表创建的时需关注primayr key 
1)程序上保证主键唯一可不需要关注 
2)若主键使用自增长,建议在创建bak表创建时提高当前的auto_increment指,空余一部分buffer空间,保证后续将源表历史数据写入rename后的新表时主键不冲突 
3、对于此类经常需要进行数据清理的表,建议使用分区表进行存储,后续对数据的处理仅仅需要删除指定分区

更多技术信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月20日

MySQL|MySQL执行计划

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。

explain执行计划包含的信息

1.jpg

每列的内容

含义

id

执行计划的id标志

select_type

select的类型

table

输出记录的表

partitions

匹配的分区

type

join的类型

possible_keys

优化器可能选择的索引

key

优化器实际选择的索引

key_len

使用索引的字节长度

ref

进行比较的索引列

rows

优化器预估的记录数量额外的显示选项

filtered

根据条件过滤得到的记录的百分比

extra

额外的显示选项

1、执行计划的 id

select 查询的序列号,标识执行的顺序

  • id 相同,执行顺序由上至下
  • id 不同,如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行

2、执行计划的 select_type

查询的类型,主要是用于区分普通查询、联合查询、子查询等。

  • SIMPLE:简单的 select 查询,查询中不包含子查询或者 union
  • PRIMARY:查询中包含子部分,最外层查询则被标记为 primary
  • UNION:表示 union 中的第二个或后面的 select 语句
  • DEPENDENT UNION:union 中的第二个或后面的 select 语句,依赖于外面的查询
  • UNION RESULT:union 的结果
  • SUBQUERY:子查询中的第一个 select
  • DEPENDENT SUBQUERY:子查询中的第一个 select,依赖于外面的查询
  • DERIVED:派生表的 select(from 子句的子查询)
  • MATERIALIZED:物化子查询
  • 产生中间临时表(实体)
  • 临时表自动创建索引并和其他表进行关联,提高性能
  • 和子查询的区别是,优化器将可以进行 MATERIALIZED 的语句自动改写成 join,并自动创建索引
  • UNCACHEABLE SUBQUERY:不会被缓存的并且对于外部查询的每行都要重新计算的子查询
  • UNCACHEABLE UNION:属于不能被缓存的 union 中的第二个或后面的 select 语句

3、执行计划的 table

查询涉及到的表。

  • 通常就是用户操作的用户表
  • <unionM,N>:由 ID 等于 M,N 的语句 union 得到的结果表
  • :派生表,由 ID 等于 N 的语句查询得到的结果表
  • :由子查询物化产生的表,由 ID 等于 N 的语句查询得到的结果表

4、执行计划的 type

访问类型,SQL 查询优化中一个很重要的指标,结果值从好到坏依次是:system > const > eq_ref > ref > range > index > ALL。

  • system:系统表,少量数据,往往不需要进行磁盘IO
  • const:常量连接
  • eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
  • ref:非主键非唯一索引等值扫描
  • range:范围扫描
  • index:索引树扫描
  • ALL:全表扫描(full table scan)

const:

数据准备: CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); 然后执行: explain select * from user where id=1;

2.jpg

const 扫描的条件为:

  1. 命中主键(primary key)或者唯一(unique)索引
  2. 被连接的部分是一个常量(const)值

如上例,id 是 主键索引,连接部分是常量1。

eq_ref

数据准备: CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) NOT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

3.jpg

eq_ref 扫描的条件为,对于前表的每一行(row),后表只有一行被扫描。

再细化一点:

  1. join 查询
  2. 命中主键(primary key)或者非空唯一(unique not null)索引
  3. 等值连接;

如上例,id 是主键,该 join 查询为 eq_ref 扫描。

ref

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

4 .jpg

如果把上例 eq_ref 案例中的主键索引,改为普通非唯一(non unique)索引。就由 eq_ref 降级为了 ref,此时对于前表的每一行(row),后表可能有多于一行的数据被扫描。

select * from user where id=1;

5.jpg

当 id 改为普通非唯一索引后,常量的连接查询,也由 const 降级为了 ref,因为也可能有多于一行的数据被扫描。

ref 扫描,可能出现在 join 里,也可能出现在单表普通索引里,每一次匹配可能有多行数据返回,虽然它比 eq_ref 要慢,但它仍然是一个很快的 join 类型。

range

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'),(2,'zhangsan'),(3,'lisi'),(4,'wangwu'),(5,'zhaoliu'); 然后执行: explain select from user where id between 1 and 4; explain select from user where id in(1,2,3); explain select * from user where id > 3;

6.png

ange 扫描就比较好理解了,它是索引上的范围查询,它会在索引上扫码特定范围内的值。

像上例中的 between,in,> 都是典型的范围(range)查询。

index

explain select count(*) from user;

7.jpg

如上例,id 是主键,该 count 查询需要通过扫描索引上的全部数据来计数,它仅比全表扫描快一点。

ALL

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

8.jpg

如果 id 上不建索引,对于前表的每一行(row),后表都要被全表扫描。

文章中,这个相同的 join 语句出现了三次:

  1. 扫描类型为 eq_ref,此时 id 为主键
  2. 扫描类型为 ref,此时 id 为非唯一普通索引
  3. 扫描类型为 ALL,全表扫描,此时id上无索引

总结

  1. explain 结果中的 type 字段,表示(广义)连接类型,它描述了找到所需数据使用的扫描方式;
  2. 常见的扫描类型有:system>const>eq_ref>ref>range>index>ALL,其扫描速度由快到慢;
  3. 各类扫描类型的要点是:
  4. system 最快:不进行磁盘 IO
  5. const:PK 或者 unique 上的等值查询
  6. eq_ref:PK 或者 unique 上的 join 查询,等值匹配,对于前表的每一行,后表只有一行命中
  7. ref:非唯一索引,等值匹配,可能有多行命中
  8. range:索引上的范围扫描,例如:between、in、>
  9. index:索引上的全集扫描,例如:InnoDB 的 count
  10. ALL 最慢:全表扫描
  11. 建立正确的索引,非常重要;
  12. 使用 explain 了解并优化执行计划,非常重要;

5、执行计划 possible_keys

查询过程中有可能用到的索引。

6、执行计划 key

实际使用的索引,如果为 NULL ,则没有使用索引。

7、执行计划 rows

根据表统计信息或者索引选用情况,大致估算出找到所需的记录所需要读取的行数。

8、执行计划 filtered

表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好。

9、执行计划 Extra

十分重要的额外信息。

  • Using filesort:MySQL 对数据使用一个外部的文件内容进行了排序,而不是按照表内的索引进行排序读取。
  • Using index:表示 SQL 操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率高。
  • Using index condition:表示 SQL 操作命中了索引,但不是所有的列数据都在索引树上,还需要访问实际的行记录。
  • Using index for group by:优化器只需要使用索引就能处理 group by 或 distinct 语句。
  • Using join buffer (Block Nested Loop):表示 SQL 操作使用了关联查询或者子查询,且需要进行嵌套循环计算。
  • Using MRR:优化器使用 MRR 优化
  • Using temporary:使用临时表保存中间结果,也就是说 MySQL 在对查询结果排序时使用了临时表,常见于order by 或 group by。
  • Using where:表示 SQL 操作使用了 where 过滤条件。
  • Select tables optimized away:基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即可完成优化。

数据准备: create table user( id int(11) not null, name varchar(20) default null, sex varchar(5) default null, primary key (id), key name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 用户表:id 主键索引,name 普通索引(非唯一),sex 无索引。 四行记录:其中 name 普通索引存在重复记录 lisi。

Using filesort

执行: explain select * from user order by sex;

9.jpg

Extra 为 Using filesort 说明,得到所需结果集,需要对所有记录进行文件排序。

这类 SQL 语句性能极差,需要进行优化。

典型的,在一个没有建立索引的列上进行了 order by,就会触发 filesort,常见的优化方案是,在 order by 的列上添加索引,避免每次查询都全量排序。

Using temporary

执行: explain select * from user group by name order by sex;

10.jpg

(备注:一开始执行时报错 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 原因是 : 错误1055(42000):选择列表的表达式1不在GROUP BY子句中,并且包含未聚合的列“test.fruits.f_id”,它在功能上不依赖GROUP BY子句中的列;这与SQL_mode=only_full_group_by不兼容) 解决办法: 在mysql中输入 mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ) 重新查询就可以了

Extra 为 Using temporary 说明,需要建立临时表(temporary table)来暂存中间结果。

这类 SQL 语句性能较低,往往也需要进行优化。

典型的 group by 和 order by 同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。

临时表存在两种引擎,一种是 Memory 引擎,一种是 MyISAM 引擎,如果返回的数据在 16M 以内(默认),且没有大字段的情况下,使用 Memory 引擎,否则使用 MyISAM 引擎。

Using index

执行: explain select id from user;

11.png

Extra 为 Using index 说明,SQL 所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录。

这类 SQL 语句往往性能较好。

Using index condition

执行: explain select id, name, sex from user where name='shenjian';

Extra 为 Using index condition 说明,确实命中了索引,但不是所有的列数据都在索引树上,还需要访问实际的行记录。

这类 SQL 语句性能也较高,但不如 Using index。

Using where

explain select * from user where sex='no';

13.jpg

Extra 为 Using where 说明,查询的结果集使用了 where 过滤条件,比如上面的 SQL 使用了 sex = 'no' 的过滤条件

Select tables optimized away

explain select max(id) from user;

14.jpg

比如上面的语句查询 id 的最大值,因为 id 是主键索引,根据 B+Tree 的结构,天然就是有序存放的,所以不需要等到执行阶段再进行计算,查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop)

explain select * from user where id in (select id from user where sex='no');

Extra 为 Using join buffer (Block Nested Loop) 说明,需要进行嵌套循环计算。内层和外层的 type 均为 ALL,rows 均为4,需要循环进行4*4次计算。

这类 SQL 语句性能往往也较低,需要进行优化。

典型的两个关联表 join,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。

更多技术信息可查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月19日

MySQL|mysql-索引

1、索引是什么

1.1索引简介

索引是表的目录,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,以及快速定位查询数据。对于索引,会保存在额外的文件中。

1.2主要注意的地方:

(1).索引可以提高查询速度,会减慢写入速度,索引的缺点时创建和维护索引需要耗费时间。

(2).索引并不是越多越好,索引固然可以提高相应的select的效率,但同时也降低了insert及update的效率,因为insert或update是有可能会重建索引。

1.3什么样的字段适合创建索引

(1).表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询

(2).经常与其他表进行连接的表,在连接字段上应该建立索引

(3).数据量超过300的表应该有索引。

(4).重要的SQL或调用频率高的SQL,比如经常出现在where子句中的字段,order by,group by, distinct的字段都要添加索引。

(5).经常用到排序的列上,因为索引已经排序

(6).经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的。

1.4什么场景不适合创建索引

1.对于那些在查询中很少使用或者参考的列不应该创建索引,这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

2.对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了,所以没有必要。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

4、当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。

5.不会出现在where条件中的字段不该建立索引

6.如果列均匀分布在 1 和 100 之间,却只是查询中where key_part1 > 1 and key_part1 < 90不应该增加索引

1.5索引使用以及设计规范

1、越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂

2、尽量避免null:应该指定列为not null, 含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂,也可能导致复合索引无效

3、主键选择策略

1) 每个表必须显示指定主键;

2) 主键尽量为一个字段,且为数字类型,避免使用字符串;

3) 主键尽量保持增长趋势,建议使用id的生成器;

4)主键尽量杜绝联合索引

4、每个表的索引个数尽量少于5个,避免创建重复冗余索引;每个组合索引尽量避免超过3个字段,索引不是越多越好,谨慎添加索引,综合考虑数据分布和数据更新

5、重要的SQL或调用频率高的SQL

1) update/select/delete的where条件列字段都要添加索引;

2) order by , group by, distinct的字段都要添加索引

6、避免出现index merge(单索引or的查询);合理利用covering index

7、组合索引创建时,把区分度(选择性)高的字段放在前面;根据SQL的特性,调整组合索引的顺序

8、对于varchar字段加索引,建议使用前缀索引,从而减小索引大小

2、索引的创建和删除

2.1创建数据表时创建索引的基本语法结构:

createtable表名(

字段名1 数据类型 [约束条件...]

字段名2 数据类型 [约束条件...]

...

[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (被用作索引的字段名[(长度)] [ASC|DESC])

);

#[]中的内容可加可不加,看具体情况; |:表示'或者' 字段名就是表的列名

2.2直接创建索引的基本语法结构:

create[`UNIQUE|FULLTEXT|SPATIAL]` `INDEX|KEY` `[索引名]` `on` `表名 (被用作索引的字段名[(长度)] [ASC|DESC`])

);

2.3修改表结构的方式添加索引的基本语法:

altertable表名add索引格式;

2.4删除索引:

dropindex索引名on表名;

2.5以上出现的索引属性名的含义:

UNIQUE`:(unique),可选参数,表示索引为唯一索引。`

FULLTEXT`:(fulltext) ,可选参数,表示索引为全文索引。`

SPATIAL`:(spatial) ,可选参数,表示索引为空间索引。`

INDEX|KEY`:(index` `|` `key), 必选参数,用于指定字段为索引的,用户在选择时,只需要二选一即可。`

[索引名]:可选参数,其作用是给创建的索引取新名称。(起到方便使用的目的)

被选定的字段名:必选参数,被用作索引的对应的字段名称,该字段必须被预先定义。

长度:可选参数,其指索引的长度,必须是字符串类型才可以使用。(比如:电话号码)

[ASC | DESC]:(asc | desc),可选参数,ASC 表示升序排列,DESC 表示降序排列。

3、索引类型

索引有很多种类型,可以为不同的场景提供更好的性能。在MySQL中,索引是在存储引擎层而不是服务层。所以,并没有统一的索引标准:不同的存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。

3.1普通索引

创建普通索引,不需要添加 [UNIQUE | FULLTEXT | SPATIAL ] 等任何参数进行约束。

  • 普通索引 (由关键字KEY或INDEX定义的索引) 的唯一任务是加快对数据的访问速度。
  • 只为那些最经常出现在‘查询条件’(WHERE column = …) 或‘排序条件’(ORDER BYcolumn)中的数据列,来创建索引。
  • 只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个int整数类型的数据列)来创建索引。

建表语句:

createtableperson(

idint`(11)notnullauto_increment`,

name varchar(20) default null,

primary key (id),

key (name) using btree

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引:

createindexnameonperson(name);

3.2唯一索引(主键)

创建唯一索引时,使用 UNIQUE 参数对 INDEX | KEY 进行约束。

  • 与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。
  • 如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。
  • 事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。
  • 主索引:在前面已经反复多次强调过!必须为主键字段创建一个索引,这个索引就是所谓的"主索引"。
  • 主索引 与 唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

eg:创建的表名为 address 的数据表,并在该表的 id 字段上建立名称为 address_id 的唯一索引,

建表语句:

create table address(

id int(11)  auto_increment primary key not null,

name varchar(50),

address varchar(200),

UNIQUE INDEX address_id (id ASC) # id 字段设为唯一索引,并赋予新名字address_id ,且ASC升序排列

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

3.3全文索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

  • MySQL从3.23.23版开始支持全文索引和全文检索,fulltext索引仅可用于 MyISAM表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE或CREATE INDEX被添加。
  • 对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
  • 文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE%word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。
  • 这类场合正是全文索引(full-textindex)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用命令添加。
  • 有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法: SELECT * FROM table_name
  • WHERE MATCH(column1, column2)
  • AGAINST(‘word1’, ‘word2’, ‘word3’)
  • 上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

eg:创建的表名为 cards 的数据表,并在该表的 name 字段上建立名称为 cards_number 的全文索引

建表语句:

create table cards(

id int(11) auto_increment primary key not null ,

name varchar(20) default null,

number bigint(11),

info varchar(50),

FULLTEXT KEY cards_number (name) # name字段设为全文索引,并赋予新名字cards_number

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

3.4单列索引

创建单列索引,即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数,用户在建立时只需要指定单列字段名,即可创建单列索引。

  • 多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

eg:创建的表名为 telephone 的数据表,并在该表的 tel 字段上建立名称为 tel_num 的单列索引

create table telephone(

id int(11) primary key auto_increment not null,

name varchar(10) not null,

tel varchar(50) not null,

index tel_num (tel(20)) # tel字段设为单列索引,并赋予新名字tel_num,由于tel字段是字符串类型,因此可以设定'索引长度20'

);

3.5多列索引

创建多列索引,即在数据表的多个字段上创建索引。与上述单列索引类似,创建该类型索引不需要引入约束参数。

  • 多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

eg:创建的表名为 information 的数据表,并在该表的 name 和 sex 字段上建立名称为 info 的多列索引

create table information(

inf_id int(11) auto_increment primary key not null,

name varchar(20) not null,

sex varchar(5) not null,

birthday varchar(50) not null,

index info (name,sex) # name,sex字段设为多列索引,并赋予新名字info

);

  • 注意:在多列索引中,只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段),索引才会被使用。触发多列索引的条件是用户必须使用索引的第一字段,如果没有用到第一字段,则索引不起任何作用,用户想要优化查询速度,可以应用该类索引形式。

3.6空间索引

创建空间索引,需要添加 SPATIAL 参数进行约束。同样,必须说明的是,只有 MyISAM 类型的表支持该类型 ‘ 空间索引 ’。而且,索引字段必须有非空约束。

eg:创建的表名为 list 的数据表,并在该表的 goods 字段上建立名称为 listinfo 的空间索引,这里 goods 字段有非空约束,

create table list(

id int(11) primary key auto_increment not null,

goods geometry not null,

SPATIAL INDEX listinfo (goods) # 这里goods字段有非空约束,符合条件 goods字段设为空间索引,并赋予新名字listinfo

)engine=MyISAM;

注意: goods 字段上已经建立名称为 listinfo 的空间索引,其中 goods 字段必须不能为空,且数据类型是 GEOMETRY,该类型是空间数据类型。

空间类型不能用其他类型代替,否则在生成空间素引时会产生错误且不能正常创建该类型索引。

外键索引:如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

另外:INDEX | KEY:(index | key), 必选参数,用于指定字段为索引的,用户在选择时,只需要二选一即可。

区别:

1). key :是数据库的物理结构,它包含两层意义:一是约束(偏重于约束和规范数据库的结构完整性)、二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;foreign key也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;2). index:是数据库的物理结构,它包含一层意义:它只是索引(辅助查询用的),它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。因此,索引只是索引,它不会去约束索引的字段的行为(那是key要做的事情)。

3.7聚簇索引

1.聚簇索引并不是一种单独索引,而是一种数据储存方式。InnoDB 的聚簇索引实际上在同一结构中保存了B-Tree 索引和数据行。

2.当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值紧凑的储存在一起。

3.对应InnoDB 来说如果表没有定义主键,会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一页面中的记录。

4.聚簇索引的优势:

(1)可以把相关数据保存在一起。(2)数据访问更快。数据和索引保存在同一个 B-Tree 。(3)使用覆盖索引扫描的查询可以直接使用页节点的主键值

5.聚簇索引的缺点:

(1)聚簇索引最大的提高了I/O密集型应用的性能,但如果数据全部都放到内存中,则数据的顺序就没有那么重要了,聚簇索引也就没什么优势了。(2)插入速度严重依赖插入顺序。按照主键插入的方式是InnoDB 速度最快的方式,但如果不是按照主键顺序加载数据,那么在加载后最好使用OPTIMIZE TABLE 命令重新组织一2下表(3)更新聚簇索引列的代价很高。因为会强制InnoDB 将每个被更新的行移动到新的位置

6.二级索引

主键索引的叶子节点存的是整行数据,在InnoDB 里,主键索引也被称为聚簇索引非主键索引的叶子节点内容是主键的值。在InnoDB 里。非主键索引也被称为二级索引。如:select* from order where user_id=3; user_id是普通索引。则会先搜索user_id 的索引树,得到id=5,再到id 索引树搜索一次,这个过程就是 “回表”。也就是说非主键索引需要查询2次

3.8覆盖索引

1.mysql 可以使用索引直接来获取列的数据,这样就可以不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据,那么还有什么必要再回表查询呢?如果一个索引包含(覆盖)所有要查询的字段的值,那么就称为“覆盖索引”

2.覆盖索引可以提高查询的性能,不需要回表,好处是:

(1)索引条目通常小于数据行,如果只需读取索引,那么mysql 就会减少访问量(2)索引是按照列值顺序存储的,索引I/O 密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要少得多(3)一些存储引擎如MyISAM 在内存只缓存索引,数据则依赖操作系统来缓存,因此要访问数据需要一次系统调用,这可能导致严重的性能问题,尤其是那些系统调用占了数据访问中最大开销的场景(4)InnoDB 的聚簇索引,覆盖索引对InnoDB 表的特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。

  1. select id from order where user_id between 1 and 3这时候只需要查ID 的值,而ID 已经在user_id 索引树上,因此可以直接提供查询结果,不需要回表。

select from order where user_id between 1 and 3一旦用了select ,就会有其他列需要读取,这时在读完index以后还需要去读data才会返回结果。

这两种处理方式性能差异非常大,特别是返回行数比较多,并且读数据需要 I/O 的时候,可能会有几十上百倍的差异。因此建议根据需要用select *

4、索引原理

5.1索引实现的数据结构

Mysql对于不同的存储引擎,索引的实现实现方式是不同的。主流的存储引擎:MyISAM和InnoDB,两种存储引擎都使用B+Tree(B-Tree的变种)作为索引结构,但是在实现方式上,却有很大的不同。下面是两种BTree数据结构:

B-tree结构:

B-tree.png

B-Tree无论是叶子结点还是非叶子结点,都含有key和一个指向数据的指针,只要找到某个节点后,就可以根据指针找到磁盘地址从而找到数据。

B+tree结构:

b+tree.jpg

B+Tree所有叶子结点才有指向数据的指针。非叶子结点就是纯索引数据和主键。每个叶子结点都有指向下一个叶子结点的链接。

小结:非叶子结点存放在内存中,也叫内结点,因此,在有限的内存中, B-Tree中每个数据的指针会带来额外的内存占用,减少了放入内存的非叶子结点数;B+Tree则尽可能多地将非叶子结点放入内存中

5.2MySQL中索引实现

由于B+Tree数据结构的优势,目前mysql基本都采用B+Tree方式实现索引,下面列出了两个最常用的存储引擎的索引实现:

1、MyISAM:如下图,叶子结点的data域存放的是数据的地址:

MyLSAM.jpg

上图表中共三列数据,col1为主键,表示MyISAM表的主索引示意图,在MyISAM中,主索引和辅助索引(除主键以外的其它索引)在结构上没有任何区别,只是主索引的key是唯一的,辅助索引的key可以重复。

2、InnoDB:对比MyISAM,InnoDB的主键索引与辅助索引存储方式是不同的:

主键索引:主键索引的叶子结点存放的是key值和数据,叶子结点载入内存时,数据一起载入,找到叶子结点的key,就找到了数据。

InnoDB.jpg

辅助索引:辅助索引的叶子结点存放的是key值和对应的记录的主键值,使用辅助索引查询,首先检索辅助索引获取主键,然后用主键在主索引中检索获取记录。

InnoDB辅助.jpg

小结:MyISAM索引叶子节点存放的是数据的地址,主键索引与辅助索引除了值得唯一性在结构上完全一样。InnoDB索引叶子节点存放的内容因索引类型不同而不同,主键索引叶子节点存放的是数据本身,辅助索引叶子节点上存放的是主键值。
更多技术详情请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月18日

MySQL|MySQL事物以及隔离级别

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如开单,需要添加给订单表增加记录,还需要增加订单的各种相关明细,操作复杂度高,这些操作语句需要构成一个事务。在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

1、事务及其四大特性

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
  • 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
  • 一般来说,事务需要满足4个条件(ACID):原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
  • mysql默认是自动提交事务的

1.1原子性

一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。

1.2一致性

在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

1.3隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

1.4持久性

事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

2、事务的隔离级别

隔离级别

脏读(Dirty Read)

不可重复读(NonRepeatable Read)

幻读(Phantom Read)

未提交读(Read uncommitted)

可能

可能

可能

已提交读(Read committed)

不可能

可能

可能

可重复读(Repeatable read)

不可能

不可能

可能

可串行化(Serializable )

不可能

不可能

不可能

InnoDB默认是可重复读级别的

  • 脏读: 脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
  • 不可重复读:是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。
  • 幻读:第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样,幻读是数据行记录变多了或者少了。

区别:脏读是指读取了未修改完的记录,不可重复读指因为被其它事务修改了记录导致某事务两次读取记录不一致,而幻读是指因为其它事务对表做了增删导致某事务两次读取的表记录数不一致问题。

2.1、第1级别未提交读(Read Uncommitted)

允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

2.2、第2级别提交读(Read Committed)

只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

2.3、第3级别可重复读(Repeated Read)

可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

2.4、第4级别串行读(Serializable)

完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

3、mysql事务相关命令

3.1查看mysql系统的事务隔离级别

mysql> SELECT @@global.tx_isolation;

3.2查看mysql会话的事务隔离级别

mysql> SELECT @@tx_isolation; -- 或 mysql> SELECT @@session.tx_isolation;

3.3设置系统的事务隔离级别

mysql> set global transaction isolation level read committed;

3.4设置会话的事务隔离级别

mysql> set session transaction isolation level read committed; -- 值可以分别为:READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

3.5查看autocommit变量

1:表示自动提交事务,0表示不自动提交事务

mysql> select @@autocommit;

3.6设置mysql不自动提交事务

mysql> set autocommit = 0;

3.7事务回滚

mysql> rollback;

3.8显示的开启一个事务

mysql> start transaction; -- 或 mysql> begin;

3.9创建一个保存点

mysql> savepoint tem1;

3.10显示提交事务

mysql> commit;

更多技术信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月10日

助力高考|为117万艺考学子保驾护航

演员请就位

在最近的一档综艺节目《演员请就位》中,李诚儒和郭敬明的“掰头”赚足了网友们的关注,事件中心的何昶希,因演技受到质疑而引发导演间争议。更是引起了其他选手的不满,提出“what?他以为他是在选秀节目吗?”这样的疑问。image
随着事件在网上的发酵升温,网友们将关注点从节目本身转移到何昶希未来发展上,对他接下来的在节目中的命运,乃至今后演艺生涯都有着不同的猜测,更有甚者认为郭敬明的这张“S卡”断送了何昶希的前程。

在最新的一期节目中,郭敬明竟然第二次为何昶希亮出了“S卡”,让网友们瞋目结舌。意想不到的是,尔冬升导演也亮出了他的“S卡”。何昶希的表现狠狠的打了吃瓜网友一巴掌,“演技上一小步,未来发展的一大步”,这一次演技上的提升,使得大家对他都刮目相看。
image.png

张艺谋导演曾说过:“人的潜力是无限的,一个人就像橡皮筋一样,需要不断地拉,在这个过程中挑战自己的极限,不断扩展自己的能力。”可能正是郭敬明的“S卡”激发了何昶希的潜力,让他有了0→1的过程。何昶希的这个事件,让我们不禁的思考一名优秀演员的标准是什么?演员的专业要求有多高?演员真的这么好当吗?

答案当然是否定的!虽然艺人光鲜靓丽的一面充斥在我们日常生活的各个角落,并且这一职业的高收入现状吸引了越来越多的年轻人踏入艺术行业,但是这条道路上第一道关卡就是“艺考”

“艺考”这座独木桥

2016年参加艺考人数突破100万,就此拉开了“艺考热”的序幕。近几年各大艺术院校的报考人数,更是屡次创下历史新高,截至2020年艺考生高达117万人。

同时114:1的高专业录取比,表演力的高难度考核,使得艺考生日以继夜的努力成为生活常态。从考试报名到考试准备,各个环节都要十分的注意。尤其是报名,如若失误,相当于“不战而败”。image.png

为艺考保驾护航,云掣在行动

互联网的快速发展正在潜移默化地改变着我们的生活,一款为艺考“全程陪护”的线上应用软件应需而生,专注为国内艺术生提供考试报名、院校资讯、考程安排、报考提醒、文化分测算、艺术备考公开课等服务。

为了给考生提供更大的便利,避免在各大院校间辗转奔波,随之又上线了考试模块,新增提交试卷上传监考视频等功能。

这一改变,给该软件的业务系统带来了不小的挑战,为保证考试的顺利进行,需要有丰富高压高并发场景下护航经验的运维团队来保障应用系统平稳运行云掣不胜荣幸担此重任,为艺考出一份力。

1.模拟测压,盘查隐藏风险点

云掣全面梳理系统架构及业务数据流,根据历年考试期间的情况,模拟真实考试场景进行多次多点压测,针对不同环节的业务(如:考试报名、试卷查看、提交试卷业务等)盘查隐藏风险点。发现该软件在实际的高压场景下,极容易发生系统异常的状况,如:闪退,图片乱码,语音错误等。

2.资源扩容,分析问题来源

云掣对系统运行情况进行倒推分析,检测服务器资源使用情况,发现服务器现有资源有限无法承载业务高峰期运行的压力,提出资源扩容方案。

3.性能优化,从源头解决问题

同时根据检测结果,针对应用层软件和数据库遇到的性能瓶颈,提出详尽的优化方案和整改建议,直接从源头上解决问题。
image.png

4.架构优化,分流业务压力

云掣考虑到同一时刻多人提交试卷、上传监考视频的场景,为避免对后端业务造成负担。缩短原本需要经过多层转发的数据流转路径,从前端直接调用存储接口上传答题照片及视频材料文件。利用对象存储海量可扩容和不受硬盘容量限制的特性,实现多地域分流,调高上行带宽的限制,开放QPS限制。

5.全景监测,多级预案保障

云掣全程提供7*24实时监控,设置多级应急预案避免突发风险,顺利保障了数万考生的首次线上艺考交卷,为“艺考梦”保驾护航。考试当天同一时间峰值人数10000+,系统处于稳定状态。

今年的艺考季即将开始,又将有一群可爱的考生们,不畏严寒追逐梦想,成为那天空中美丽的一颗星,云掣也将尽自己的一份力,以一种特殊的方式为考生们加油!

更多业务信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月5日

Elasticsearch|某物流客户Elasticsearch集群性能优化案例

客户背景

客户使用ES来进行数据存储、快速查询业务订单记录,但是经常会出现业务高峰期ES集群的cpu负载、内存使用均较高,查询延迟大,导致前端业务访问出现大量超时的情况,极大影响其客户使用体验。

部分监控如下图:

1) 集群架构如下:

集群节点配置:8数据节点(16C64G);3主节点(8C32G)

2) 集群存在问题分析

  • 业务层面

与客户业务人员沟通,业务处理中有几个聚合查询会占用较多的内存,且这类查询对准确性要求较高,需精确统计所有匹配结果。

  • 架构层面

存在4-5T的单个较大索引,该索引字段多达2000+,分片大小普遍60G+,最高达到130G+,是制约查询性能的一个较大瓶颈,另外集群在业务高峰期还会出现经常的fullgc,这是出现访问超时的直接原因。如图:

Elasticsearch集群优化

与客户开发人员沟通了解集群在业务上存在的问题,结合我们在ES这块的服务经验,从语句参数、索引、架构等多个角度给客户提出调优建议。

1) 语句、参数调优

客户已提供4个慢查询语句,语句中聚合查询使用"execution_hint": "map",该执行策略会把命中的记录都捞回内存中,一旦查询结果较大就会占用大量内存。建议使用terminator_after,此方法可以控制查询结果数量,另外将不参与聚合、排序的字段设置为doc_values:false, 节省磁盘空间提升索引速度。

2) 集群架构优化:

在原有集群基础上添加协调节点或者扩容数据节点:

  • 添加协调节点:优点是可以减轻数据节点压力,变更较为容易,缓解fullgc频繁出现的问题;
  • 扩容数据节点:优点是可以减轻当前数据节点压力,也可以减小分片大小;但是增加索引分片需要重新创建索引,重新导入数据,且当前节点存储压力不大,同时增加数据节点对存储空间有一定的浪费。

结合客户业务特性,我们推荐客户使用添加协调节点的方式对集群架构进行优化。

3) 集群索引优化:

可以对集群进行索引拆分和使用别名两方面进行优化调整。

  • 拆分索引:对索引字段进行拆分并确认大小,可以解决当前索引分片过大的问题,提升查询性能。
  • 使用别名:根据日期定期创建新的索引(建议按月创建索引),根据业务对统一查询的索引创建统一别名,该方法可以彻底解决当前索引分片过大问题,优化查询性能。

集群优化效果

集群优化后整体性能有明显提升:

a. ES集群负载、内存较为平稳,业务高峰期不会有较大波动;

b. ES集群FullGC出现频次极大降低,降低对业务的影响;

c. ES聚合查询延迟减小,业务数据查询性能提升,速度达到百毫秒级别;

写在最后

袋鼠云通过数据集成优化、任务调度优化、代码优化、全链路数据质量保障、故障紧急处理、大数据平台运维,为客户提供大数据系统运维保障服务。

更多解决方案详情查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 11月2日

技本功|Hive优化之监控(三)

Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中是经常涉及的一个点,因此掌握一些Hive调优是必不可少的技能。影响Hive效率的主要有数据倾斜、数据冗余、job的IO以及不同底层引擎配置情况和Hive本身参数和HiveSQL的执行等因素。本文主要是从监控运维的角度对Hive进行整体性能把控,通过对hive元数据监控,提前发现Hive表的不合理处及可优化点,将被动运维转化为主动运维。

1 Hive元数据简介

Hive元数据一般会存储在关系数据库中,mysql是最常见的选择,这里介绍的就是Hive元数据就是存储在myslq中的,本次会介绍几张主要的元数据表,DBS、TBLS、SDS、PARTITIONS

1.1 Hive数据库相关的元数据表(DBS)

image.png

1.2 Hive表和视图相关的元数据表(TBLS)

image.png

1.3 Hive文件存储信息相关的元数据表(SDS)

image.png

1.4 Hive数据库相关的元数据表(PARTITIONS)

image.png

2 收集Hive元数据

在使用Hive元数据做监控时要确保相应表或者分区的元数据信息已经被收集。收集元数据的方式如下

2.1 收集表的元数据

analyze table 表名 compute statistic;

2.2 收集表的字段的元数据

analyze table 表名 compute statistic for columns;

2.3 收集所有分区的元数据

analyze table 表名 partition(分区列) compute statistic;

2.4 指定特定分区进行收集元数据

analyze table 表名 partition(分区列=分区值) compute statistic;

2.5 收集所有分区的列的元数据

analyze table 表名 partition(分区列) compute statistic for columns;

3 Hive元数据监控案例

3.1监控普通表存储的文件的平均大小

对于大的文件块可能导致数据在读取时产生数据倾斜,影响集群任务的运行效率。下面sql是对于大于两倍HDFS文件块大小的表:

​
-- 整体逻辑:通过DBS找到对应库下面的表TBLS,再通过TBLS找到每个表对应的表属性,取得totalSize和numFiles两个属性,前者表示文件大小,后者表示文件数量

**SELECT**

 TBL_NAME,round(avgfilesize,1) **as** 'fileSize(Mb)'

**FROM** (

 **SELECT**

 tp.totalSize/(1024*1024)/numFiles avgfilesize,TBL_NAME

 **FROM** metastore.dbs d

 **INNER** **join** metastore.tbls t **on** d.DB_ID = t.DB_ID

 **left** **join** (

 **SELECT** TBL_ID,

 **MAX**(**case** PARAM_KEY **when** 'numFiles' **then** PARAM_VALUE **ELSE** 0 **END**) numFiles,

 **MAX**(**case** PARAM_KEY **when** 'totalSize' **then** PARAM_VALUE **ELSE** 0 **END** ) totalSize

 **from** metastore.table_params

 **GROUP** **by** TBL_ID

 ) tp **on** t.TBL_ID = tp.TBL_ID

 **where** d.NAME = '要监控的库'

 **and** tp.numFiles **is** **not** **NULL**

 **and** tp.numFiles > 0

 ) a **where** avgfilesize > hdfs的文件块大小*2

 **ORDER** **BY** avgfilesize **desc**;
​

image

3.2监控分区存储的文件平均大小,大于两倍HDFS文件块大小的分区,

​
-- 整体逻辑:先用DBS关联TBLS表,TBLS表关联PARTITIONS表PARTITION表关联PARTITION_PARAMS

**SELECT**

TBL_NAME,part_name,round(avgfilesize,1) **as** 'fileSize(Mb)'

**FROM** (

 **SELECT**

 pp.totalSize/(1024*1024)/numFiles avgfilesize,TBL_NAME,part.PART_NAME

 **FROM** metastore.dbs d

 **INNER** **join** metastore.TBLS t **on** d.DB_ID = t.DB_ID

 **INNER** **join** metastore.PARTITIONS part **on** t.TBL_ID = part.TBL_ID

 **left** **join** (

 **SELECT** PART_ID,

 -- 每个表存储的文件个数

 **MAX**(**case** PARAM_KEY **when** 'numFiles' **then** PARAM_VALUE **ELSE** 0 **END**) numFiles,

 -- 文件存储的大小

 **MAX**(**case** PARAM_KEY **when** 'totalSize' **then** PARAM_VALUE **ELSE** 0 **END** ) totalSize

 **from** metastore.PARTITION_PARAMS

 **GROUP** **by** PART_ID

 ) pp **on** part.PART_ID = pp.PART_ID

 **where** d.NAME = '要监控的库'

 **and** pp.numFiles **is** **not** **NULL**

 **and** pp.numFiles > 0

) a **where** avgfilesize >hdfs的文件块大小*2

**ORDER** **BY** avgfilesize **desc**;

image

3.3监控大表不分区的表

对于大数据量的表,如果不进行分区,意味着程序在读取相同的数据时需要遍历更多的文件块,性能会下降很多。

​

**select** t.TBL_NAME ,round(totalSize/1024/1024,1) **as** 'fileSize(Mb)'

**FROM** metastore.DBS d

**inner** **join** metastore.TBLS t **on** d.`DB_ID` = t.`DB_ID`

**inner** **join** (

 **select** `TBL_ID`,**max**(**case** `PARAM_KEY` **when** 'totalSize' **then** `PARAM_VALUE` **else** 0 **end**) totalSize

 **from** `TABLE_PARAMS`

 **group** **by** `TBL_ID`

) tp **on** t.`TBL_ID` = tp.`TBL_ID`

**left** **join**

(

 **select** **distinct** `TBL_ID` **from** metastore.PARTITIONS p

) part **on** t.`TBL_ID` = part.`TBL_ID`

**where** d.`NAME` = '要监控的库'

**and** part.`TBL_ID` **is** **null**

**and** totalSize/1024/1024/1024 > 30

**ORDER** **BY** totalSize/1024 **desc**;

image

3.4监控表分区的数量

了解表的分区数量,在做全表join时如果一个表数量不大,分区很多,可以考虑分区合并等优化手段

​

**SELECT**

t.TBL_NAME '表名',d.`NAME` '库名', **COUNT**(part.PART_NAME) '分区数'

**FROM**

DBS d

**INNER** **JOIN** TBLS t **on** d.DB_ID = t.DB_ID

**INNER** **join** `PARTITIONS` part **on** part.TBL_ID = t.TBL_ID

**WHERE** d.`NAME` = '要监控的库'

**GROUP** **by** t.TBL_NAME,d.`NAME`

**ORDER** **BY** **COUNT**(part.PART_NAME) **desc**;

image

结语:

Hive元数据的监控主要目的就是对Hive中表情况的整体把控,这里主要介绍了大数据块、不分区表、表分区这几个指标的监控,当然还有很多,比如hive的小文件、表的数据存储格式等等,对这些信息的长期监控,最好可以和grafana这些结合展示,这对整个数仓的稳定运行至关重要。后面我们还会出Hive SQL调优相关的文章,敬请期待。

更多业务信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 10月29日

SQL优化三步曲

有一天开发同学反馈线上业务库中有一条SQL执行很满,每次几乎要跑1分钟才结束,希望我们帮忙优化一下,具体SQL如下:

image

SQL优化第一步 - 查看执行计划

对于一个SQL的优化,我们的第一步也是最重要的一步就是查看SQL执行计划,SQL执行计划一方面告诉我们SQL具体的处理行为,另外一方面也可以体现每个执行步骤下大致的资源消耗点。所以我们拿到问题SQL以及对应数据库环境后,登录该环境只读实例进行SQL分析测试。

image

从以上的SQL执行计划我们可以获取到哪些有效信息呢?

  • SQL先对t表扫描查询生产派生表<derived2>,brand通过索引过滤作为表关联的驱动表,与vender、product、shop_product、spu、<derived2>进行关联查询,表关联均有效利用索引。
  • 从type字段上看,SQL基本上都有效利用到了索引,但是index其实是全索引扫描,该方式的索引扫描执行效率并不会很好
  • 对t表的index全索引扫描数据量高达480w,在当前SQL中执行消耗最大,这也将是我们SQL优化的切入点
  • t表在源SQL中并未出现,再次仔细观察SQL可以发现SQL引用了view_prod_store_sum的视图

SQL优化第二步 -

在MySQL中对于视图使用我们需要知道的是虽然MySQL对视图的查询做了一些优化,但是对于复杂视图查询其优化支持仍然不是很好,所以业务上我们要尽量避免对复杂视图的使用。在本SQL中视图其实是对单表的查询,且目前SQL资源消耗的瓶颈点也在视图查询这部分,所以我们将视图的定义通过子查询代替原视图,整体的来看SQL。

视图定义:

image

完整SQL:

image

SQL优化第三步 - 适当改写

结合我们获取到的SQL执行计划以及恢复出来的完整SQL,我们再次理解当前SQL的处理行为:

1)对t表进行全索引扫描,生产派生表2

2)brand表通过brand_id IN (252)条件进行索引过滤,后续与其他表以及派生表2进行关联查询

  • 资源消耗点分析:

从SQL真正需要查询的数据来看,我们只需要先通过where子句中表过滤条件过滤获取初步满足条件记录,然后对这些记录判断 sum(store) > 0是否满足,满足则返回。但是该SQL实际处理却是先将t表中所有记录的store进行分组计算,将结果保存在派生表中。通过where子句中表过滤条件后的记录再与派生表关联判断sum(store)。

SQL在处理的过程似乎扫描了很多不必要的数据,我们为何不仅仅对已满足where子句条件过滤的记录做sum(store)判断呢?

基于以上的分析,我们尝试使用exists相关子查询进行改写测试。为什么使用相关子查询呢,这是因为exists在处理SQL时的核心思想是先对where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断。 因此我们可以有效的利用exists避免的避免掉优先对t表的派生表产生,保证SQL优先通过where子句中选择性最佳的条件做驱动表,然后对sum(store)通过相关子查询进行判断。

  • 具体改写如下:

image

  • 改写后的执行计划:

image

  • 执行效率对比

优化前:

image

优化后:

image

在一般业务SQL编写中,我们都推荐开发同学使用join而不是exists,这是因为exists本身处理SQL的方式下如果where条件处理后外表记录仍然很大的情况下,再次将外表中每条记录代入exists子查询中判断,其资源消耗代价是很大的。所以我们更偏向使用JOIN,在满足必要的索引情况下MySQL优化器优先选择小表进行驱动。无论具体选择什么方式,其实减少扫描函数才是王道!

更多技术文章,关注公众号“云掣YUNCHE”,还有更多行业咨询哦。

查看原文

赞 0 收藏 0 评论 0

认证与成就

  • 获得 3 次点赞
  • 获得 1 枚徽章 获得 0 枚金徽章, 获得 0 枚银徽章, 获得 1 枚铜徽章

擅长技能
编辑

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2018-11-21
个人主页被 419 人浏览