首页 >

精选归纳15个Mysql优化问题

数据库|mysql教程精选归纳15个Mysql优化问题
mysql
数据库-mysql教程
网咯棋牌源码,vscode哪种字体好看,ubuntu调用终端,tomcat俄罗斯,sqlite3放在哪,类似八爪鱼爬虫 手机APP,php二维变一维,番禺seo推广运营,dede网站地图地睛,chrome 网页源码,自考报名模板lzw
mysql视频教学
cms java系统源码,vsCode 中编码出错,ubuntu挖chia,tomcat的用户配置,爬虫 pycharm,php 检查字符编码,大同seo网络营销,网站聊天程序源码,wap微网站模板lzw
开发过程如何排查SQL?排查思路
狗扑源码网,vscode前端可视化插件,ubuntu硬盘不够,nginx需要打tomcat吗,sqlite很快么,国内创意网页设计,wordpress服务器内部错误,dz插件vip,前端技术框架说明,爬虫店内部,php换行输出,温州seo排名,跨域注解springboot,php 微网站源码,网页设计 按钮样式,传奇易模板,dedecms 后台模板修改,评论输入框页面实现,ligerrm权限管理系统,综合商城网站程序lzw
对于大部分程序员来说,在开发过程中排查SQL基本是空白。但随着行业的内卷,对一开发过程越来越重视和专业,其中一项就是开发过程中尽可能解决掉SQL问题,避免生产才暴露SQL问题。那么在开发过程中如何方便的进行程序的SQL排查呢?

其思路还是使用Mysql的慢日志来实现:

首先在开发过程中也需要开启数据库Mysql的慢查询

SET GLOBAL slow_query_log='on';

其次设置慢SQL的最小时间

SET GLOBAL long_query_time=0.02;

为方便操作可以把慢SQL记录到表中而不是文件中

SET GLOBAL log_output='TABLE';

最后通过mysql.slow_log表就可以查询到记录的慢SQL

精选归纳15个Mysql优化问题

使用工具

在勇哥给大家开发的软件中,也提供了图形化的界面来一键帮助大家快速实现上述功能。

精选归纳15个Mysql优化问题

生产环境SQL问题如何排查?排查思路

生成SQL问题的排查就相对复杂一点点,但是整体的思路还是通过慢SQL来排查,具体思路如下:

首先开启数据库Mysql的慢查询

SET GLOBAL slow_query_log='on';

其次设置慢SQL的最小时间

SET GLOBAL long_query_time=0.02;

一般生成时把慢SQL放到文件

SET GLOBAL log_output='FILE';

下载慢SQL日志文件到本地

最后关闭数据库Mysql的慢查询

SET GLOBAL slow_query_log='off';

SQL怎么调优?表结构优化

1、合理的使用字段类及长度

举个例子来理解:就一个性别字段,用tinyint(1)存储占用1字节,用int(1)存储占用4个字节,如果有100W条记录,那么用int存储的表就比tinyint存储的表文件大小多2.8M左右,因此在读取int类型存储的表时文件大,读速度相比读tinyint的慢。这其实就是为什么说要合理使用字段类型长度的本质:就是减少存储的文件大小,以提供读性能

当然有的朋友就可能说2.8M并不影响大局,因此可以忽略。对于此想法勇哥要补充一嘴:一个表假设有10个字段,你的系统一共有30个表,那么再看一下多出的文件大小是多少?(2.8Mx10x30=840M,840M你用迅雷超级下载也要花好几秒,这个时间在计算机里面算是很慢了…)

2、合理的使用冗余设计

2.1、冗余设计背景——临时表

Mysql内部存在一种特殊且轻量级的临时表,它是被Mysql自动创建和删除的。主要在SQL的执行过程中使用临时表来存储某些操作的中间结果,该过程由 MySQL 自动完成,用户无法手工干预,且这种内部表对用户来说是不可见的。

内部临时表在 SQL 语句的优化过程中非常重要,MySQL 中的很多操作都要依赖于内部临时表来进行优化操作。但是使用内部临时表需要创建表以及中间数据的存取代价,所以在写 SQL 语句的时候应该尽量去避免使用临时表

那么场景的那些场景Mysql内部会使用临时表呢?

多表关联查询(JOIN)中,order by 或group by使用的列不是第一个表的列

group by 的列不是索引列时

distinct和group by 联合使用

order by 语句中使用了distinct关键字

group by 的列时索引列,但数据量过大时

2.2、如何查看是否使用内部临时表?

通过Explain关键字或者工具的功能按钮,查看SQL的执行过程,在结果中的Extra列中如果出现Using temporary关键字,则说明你的SQL语句在执行时使用了临时表。

如下图,角色Role表和角色组Role_Group是多对1的关系,在关联查询的时候,排序使用role_group的id排序则会使用临时表(见下图1),如果排序使用role的id则不会使用临时表(见图2)。

精选归纳15个Mysql优化问题

精选归纳15个Mysql优化问题

2.3、如何解决不使用内部临时表?

这个问题解决有两个方案,一是调整SQL语句避免使用临时表,另外一个方案就是在表中冗余存储。比如2.2中的图一例子如果一定要按照role_group的id排序,则可以按照role表中的group_id排序,而这列正是冗余存储的role_group表中id列值。

精选归纳15个Mysql优化问题

3、合理的使用分库分表

分库分表不仅用于大数量情况下的优化,其中垂直分表还可以使用到SQL调优下。(这里我就不去解释垂直和水平分表了,感兴趣的私信我)

例如:一个文章表一般设计不会包括文章内容这个大字段。

精选归纳15个Mysql优化问题

文章内容这个大字段是单独放置到一张表中

精选归纳15个Mysql优化问题

为什么文章表要采用以上设计而不把字段合并到一表中呢?

我们先来计算一道数学题,假设一篇文章总共1M大小,其中文章内容,824KB,其余字段200KB,这样的文章一共有100W条,则:

方案一,如果用一个表存储,则这个表大小是100W*1M=100WM

方案二,如果用垂直分表存储,则基本表时200KBx100W,内容表824KBx100W

我们在前端有文章列表和文章详情两个页面,分别要直接从数据库中查询相关内容,则:

方案一,文章列表和文章详情的查询都会从100WM数据中查询

方案二,文章列表会从200KBx100W中查询,文章详情会从824KBx100W中查询(当前也可能还需要从200KBx100W中查询)

说到这里,相信大家心中应该有一个清晰的答案了吧!垂直拆表可以让不同业务场景的查询的数据量不同,常常这个数据量往往小于总表数据量,这就比从固定很大小的量中查询更灵活和高效率。

表索引优化

1、合理的添加索引列

大多数人对应索引的理解层次都在“索引可以加快查询的速度”,然而这句话勇哥要补充下半句“索引可以加快查询的速度,也可以减慢数据插入或修改的速度”

如果一个表有5个索引,那么可以简单的把一个索引当成一个表,则这就会有1张表+6张索引表=相当于有6张表,那么这6张表在什么时候会操作呢?我们来计算一下:

insert操作,数据插入后,需要去对5张索引表插入索引数据

delete操作,数据删除后,需要去把5张索引表中的索引删除

update操作

如果修改了索引列的数据,则先修改数据,还需要修改索引表中的索引

如果没有修改索引列的数据,则只修改数据表

select操作

如果命中查询索引,则先查询索引,再查数据表

如果没命中查询索引,则直接查数据表

通过以上的计算,你会神奇的发现,索引个数越多,对于insert、delete、update操作是有影响的,而且是负影响。所以对于索引竟可能评估其带来的影响小于查询的收益,才去添加,而不是盲目的添加

2、合理的调配复合索引列个数和顺序

复合索引指的是包括有多个列的索引,它能有效的减少表的索引个数,平衡了多个字段需要多个索引直接的性能平衡,但是再使用复合索引的时候,需要注意索引列个数和顺序的问题。

先说列个数的问题,指的是一个复合索引中包括的列字段太多影响性能的问题,主要是对update操作的性能影响,如下红字:

如果修改了索引列的数据,则先修改数据,还需要修改索引表中的索引,如果索引列个数越多则修改该索引的概率越大

如果没有修改索引列的数据,则只修改数据表

再说复合索引中列顺序的问题,是指索引的最左匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,这个比较容易理解,就不多做阐述。

那些情况索引会失效?

索引无法存储null值,当使用is null或is not nulli时会全表扫描

like查询以”%”开头

对于复合索引,查询条件中没有给出索引中第一列的值时

mysql内部评估全表扫描比索引快时

or、!=、、in、not in等查询也可能引起索引失效

表设计有那些规范?建表规约

表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型为 unsigned tinyint说明:任何字段如果为非负数,则必须是 unsigned。

字段允许适当冗余,以提高查询性能,但必须考虑数据一致。e.g. 商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,

避免关联查询

。冗余字段遵循:

不是频繁修改的字段;

不是 varchar 超长字段,更不能是 text 字段。

索引规约

在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。

页面搜索严禁左模糊或者全模糊,如果需要请通过搜索引擎来解决。 说明:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

如果有 order by 的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。

正例:where a=? and b=? order by c; 索引: a_b_c。

反例:索引中有范围查找,那么索引有序性无法利用,如 WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

利用延迟关联或者子查询优化超多分页场景。 说明:MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 的行,返回 N 行。当 offset 特别大的时候,效率会非常的低下,要么控制返回的总页数,要么对超过阈值的页数进行 SQL 改写。

建组合索引的时候,区分度最高的在最左边。

SQL 性能优化的目标,至少要达到 range 级别,要求是 ref 级别,最好是 consts。

SQL 语句

不要使用 count(列名) 或 count(常量) 来替代 count(),count() 是 SQL92 定义的标准统计行数的语句,跟数据库无关,跟 NULL 和非 NULL 无关。 说明:count(*) 会统计值为 NULL 的行,而 count(列名) 不会统计此列为 NULL 值的行。

count(distinct column) 计算该列除 NULL 外的不重复行数。注意,count(distinct column1,column2) 如果其中一列全为 NULL,那么即使另一列用不同的值,也返回为 0。

当某一列的值全为 NULL 时,count(column) 的返回结果为 0,但 sum(column) 的返回结果为 NULL,因此使用 sum() 时需注意 NPE 问题。 可以使用如下方式来避免 sum 的 NPE 问题。

SELECT IF(ISNULL(SUM(g), 0, SUM(g))) FROM table;

使用 ISNULL() 来判断是否为 NULL 值。 说明:NULL 与任何值的直接比较都为 NULL。

不得使用外键与级联,一切外键概念必须在应用层解决。 说明:以学生和成绩的关系为例,学生表的 student_id 是主键,成绩表的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

禁止使用存储过程。存储过程难以调试和扩展,更没有移植性。

in 操作能避免则避免。若实在避免不了,需要仔细评估 in 后面的集合元素数量,控制在 1000 个之内。

ORM 映射

POJO 类的布尔属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性的映射。

sql.xml 配置参数使用:#{}, #param#,不要使用 ${},此种方式容易出现 SQL 注入。

@Transactional 事务不要滥用。事务会影响数据库的 QPS。另外,使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

mysql视频教学


  • 暂无相关文章
  • Posted in 未分类