MySQL性能优化总结_msyl-程序员宅基地

技术标签: mysql  

MySQL性能优化总结

影响性能的相关因素

要知道怎么优化MySQL首先要知道什么东西会影响MySQL的效率

  1. 商业需求
  2. 系统架构
  3. 硬件环境
  4. 数据库的设计
  5. sql语句

在整个系统的性能优化中,如果按照百分比来划分上面几个层面的优化带来的性能收益,可以得出大概如下的数据:

需求和架构及业务实现优化:55%

sql语句的优化:30%

数据库自身的优化:15%

商业需求角度

从商业角度来说 ,如果一个需求本身就很离谱,那么这个性能是不可能高的,所以这点我们基本上是没有优化空间,但不是完全没有,比如可以直接否决掉这种离谱的功能

系统架构角度

第一点:最任意实现也是最基本的一点,就是尽量不要在数据库放一些奇怪的数据,比如二进制多媒体数据流水队列数据超大文本数据,可以使用类似阿里的对象存储来存大文件

第二点:是否合理利用了应用层的缓存机制?

  • 我们可以把不需要经常改动但需要经常查询的数据放进缓存中,比如用户的基本信息等

第三点:不要过度依赖sql语句的查询,能在代码中实现的就不要放在sql中来做

第四点:一些不合理的系统架构

  • 比如缓存的不合理利用导致缓存命中率底下,浪费缓存的同时又浪费数据库的效率
  • 过度依赖面向对象思想也会给系统带来不必要的压力
  • 对可扩展性的过度追求,使系统被拆分的过度离散,需要使用大量的join语句
  • 过度依赖数据库,把大量更适合放入文件系统的文件放入数据库,会造成资源的浪费和整体性能下降
  • 过度理想化系统的用户体验,使大量的非核心业务消耗过多的资源

硬件环境角度

这一点很简单,服务器的cpu和内存直接影响了数据库的操作效率,服务器太差怎么优化都没用,公司有钱的可以直接升级服务器,简单又直接

数据库的设计角度

数据库设计

1.在innodb能满足需求的情况下必须使用innodb,因为innodb支持事务,支持行级锁、更好的恢复性、高并发下性能更好,

2.字符集统一使用UTF8,兼容性更好,避免乱码,如果有存储emoji表情的需要,可以使用UFT8mb4字符集

3.使用comment从句添加表和列的备注,给所有字段和表都加上注释

4.单表数据量大小建议控制在500万以内,500万并不是MySQL的极限,但不建议过大,会造成表结构修改、备份恢复都有问题,可以使用分库分表等

5.谨慎使用分区表,分区表在物理上为多个文件,在逻辑上为一个表

6.尽量冷热分离,减少表的宽度,比如 用户的昵称和账号等信息基本不会改变,但是可能用户的积分等信息会经常改变,就可以把数据进行冷热分离

数据库字段

1.优先选择符合存储需要的最小的数据类型,字段越大单页节点存储的数量就越少,io操作就越多,索引性能就越差

2.避免使用TEXT,BLOB类型数据,因为mysql的内存临时表不支持大数据,如果一定要使用这种类型,建议分出到单独的表中,并且这两个类型只支持前缀索引,

3.避免使用ENUM类型,因为ENUM类型的orderBy操作效率低

4.尽量把所有列定义为NOT NULL,因为索引NULL需要额外空间来保存,并且比较和计算时要对NULL值做特殊处理

5.使用TIMESTAMP(4个字节)或DATETIME(8个字节)存储时间,不要用字符串类型

6.跟金融相关的数据必须用decimal类型

索引设计

1.单表索引建议不超过5个,增加查询效率的同时会减少插入和更新的效率

2.禁止给表中每一列都简历单独的索引

3.innodb的每个表必须有一个主键

4.常见索引列建议:

  • 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
  • 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
  • 并不要将符合 1 和 2 中的字段的列都建立一个索引, 通常将 1、2 中的字段建立联合索引效果更好
  • 多表 join 的关联列

5.如何选择索引列的顺序:

  • 把区分度(列中不同值的数量/列的总行数)最高的放在左侧
  • 字段长度小的放在左侧,因为长度越小一页能存储的数据量就越大,IO性能就越好
  • 频繁使用的放在左侧

6.避免简历冗余索引和重复索引:

  • 重复索引示例:primary key(id)、index(id)、unique index(id)
  • 冗余索引示例:index(a,b,c)、index(a,b)、index(a)

7.对于频繁的查询优先考虑使用覆盖索引(包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引 ),覆盖索引的好处:

  • 避免innodb进行索引的二次查询
  • 可以把随机IO变成顺序IO加快查询效率

8.索引SET规范

  • 不建议使用外键索引,但一定哟啊在表与表之间的关联键上建立索引
  • 外键可用于保证数据的参照完整性,但建议在业务端实现
  • 外键会影响父表和子表的写操作降低性能

sql语句角度

SQL开发规范

1.建议使用预编译语句进行数据库操作

相同语句可以一次解析,多次使用,提高处理效率

2.避免数据类型的隐式转换,因为会导致索引失效

例如

select name,phone from customer where id = '111';
id本不是字符串类型,但本身可以使字符串转换成int类等

3.充分利用表上以及存在的索引:

  • 避免使用双%号的查询条件。如: a like ‘%123%’ ,(如果无前置%,只有后置%,是可以用到列上的 索引的)
  • 一个 SQL 只能利用到复合索引中的一列进行范围查询。如:有 a,b,c 列的联合索引,在查询条件中有 a 列的范围查询,则在 b,c 列上的索引将不会被用到。
  • 在定义联合索引时,如果 a 列要用到范围查找的话,就要把 a 列放到联合索引的右侧,使用 left join 或 not exists 来优化 not in 操作,因为 not in 也通常会使用索引失效。

4.数据库设计时应该要对以后扩展进行考虑

5.连接不同的数据库使用不同的账号,禁止跨库查询

6.禁止使用SELECT*

7.禁止使用不含字段列表的INSERT语句

8.避免使用子查询,可以把子查询优化为join

9.避免使用join关联太多的表

10.减少同数据库的交互次数

11.使用in代替or,如果是连续的值,使用between and代替in

12.禁止使用order by rand将进行随机排序

13.WHERE从句中禁止堆列进行函数转换和计算

14.在明显不会有重复值时使用UNION ALL而不是UNION

15.拆分复杂的大SQL为多个小SQL

数据库行为操作规范

1.超100万行的批量更改操作,要分批多次进行操作,因为可能会造成严重的主从延迟和大事务

2.对于大表使用 pt-online-schema-change 修改表结构 避免大表修改产生的主从延迟 避免在对表字段进行修改时进行锁表

3.禁止为程序使用的账号赋予 super 权限 当达到最大连接数限制时,还运行 1 个有 super 权限的用户连接 super 权限只能留给 DBA 处理问题的账号使用

4.对于程序连接数据库账号,遵循权限最小原则 程序使用数据库账号只能在一个 DB 下使用,不准跨库 程序使用的账号原则上不准有 drop 权限

如果想看msyl的系统架构和原理方面的,可以评论区或者私信我

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_35032539/article/details/123698936

智能推荐

计算机网络(5)体系结构:计算机网络分层结构-程序员宅基地

文章浏览阅读5.8k次,点赞4次,收藏9次。1、计算机网络分层的的原因和概念两个系统间实体的通讯是很复杂的过程,为了降低协议设计和调试过程的复杂性,也为了便于对网络进行研究、实现和维护,促进标准化工作,通常对计算机网络的体系结构以分层的方式进行建模。 我们把计算机网络的各层及其协议的集合称为网络的体系结构。换言之,计算机网络的体系结构就是这个计算机网络及其所完成的功能的精确定义,它是计算机网络中的层次、各层的协议及层口接口的集合..._计算机网络分层结构

计算机如何驱动无线网络,详细教你电脑无线网卡驱动怎么安装-程序员宅基地

文章浏览阅读6k次。新安装的电脑要安装无线网卡驱动才能连上WiFi网络,这样电脑才能正常上网,但是一些电脑新手没有操作过这样的操作程序,不禁在网上发问电脑无线网卡驱动怎么安装,下面,小编就教大家安装电脑无线网卡驱动的操作方法。无线网卡就是既能接受无线信号,又能发射无线信号的网络设备,而无线网卡就需要安装无线网卡驱动。那么无线网卡驱动怎么安装呢?有些网友不知道该怎么操作,其实方法很简单。小编给大家讲解一下关于如何安装无..._怎样安装无线网卡驱动程序

Winform 判断Combobox控件先择值是否为空_imagecomboboxedit 如何判断未空-程序员宅基地

文章浏览阅读6.1k次。前面用.Item试了下,结果是取到绑定在Combobox的数据,不管有没有选择,都会满足不为空的要求,后来就用Text试了下,满足我所需要的要求。(Combobox内我没有设置默认显示某值)if (Combobox.Text.Trim().Length != 0){ MessageBox.Show("Combobox不为空!");}else{ MessageBox.Show("Comb_imagecomboboxedit 如何判断未空

每天掉多少根头发才会秃?你猜!-程序员宅基地

文章浏览阅读975次。最近……遇到了人生中最大的难题我开始疯狂掉头发了一开始觉得只是掉几根头发没什么大事后来发现自己发际线居然后移了!我年纪轻轻的难道要秃了???为什么?难道真如萌萌说的是老天..._一天掉几十根头发会不会掉光

研学-元胞自动机模拟疫情传播_元胞自动机模型解决什么问题-程序员宅基地

文章浏览阅读7.6k次,点赞30次,收藏132次。蒙特卡罗方法模拟疫情传播论文提要2020年伊始,新冠肺炎席卷全球。长时间潜伏期、极高的传染率,使得全人类面对这次疫情显得有些措手不及。为了更好地认识、防控传染病,本文采用蒙特卡罗方法中的一种——元胞自动机,借鉴著名数学家约翰·康威所发明的生命游戏规则,自创规则进行疫情模拟。最终发现严格的防控、警惕的意识与强大的医疗水平均是抗击疫情必不可少的内容。政府越早采取防控措施防控效果越好;并且医疗水平的支持更是防控的关键。主题词蒙特卡罗方法;元胞自动机;疫情传播正文1 研究背景1.1 传染病介绍在人_元胞自动机模型解决什么问题

gpio子系统和pinctrl子系统(中)_pinctrl_add_gpio_range-程序员宅基地

文章浏览阅读797次。转载地址:http://www.cnblogs.com/rongpmcu/p/7662755.htmlpinctrl子系统核心实现分析pinctrl子系统的内容在drivers/pinctrl文件夹下,主要文件有(建议先看看pinctrl内核文档Documentation/pinctrl.txt):core.cdevicetree.cpinconf.cpinmux.cpinctrl-xxx.cco..._pinctrl_add_gpio_range

随便推点

鸿蒙系统有什么特别之处,华为发布鸿蒙系统,鸿蒙系统有什么特别之处在哪里下载开源吗...-程序员宅基地

文章浏览阅读604次。8月9日下午,华为开发者大会正式召开!14:50华为消费者业务CEO余承东称,今年上半年智能手机出货量1.18亿台,同比增长24%。二季度出货量减少约1000万台。华为手机在国内份额占比35%,全球份额占比为17.6%。过去八年,华为消费者业务销售量增长68倍,2018年手机出货量2.06亿台。前瞻:华为开发者大会前瞻一:华为发布面向2025十大趋势2019年,华为基于对交通、零售、金融、制造、航...

install packages & pip 应用 & 虚拟环境_install package和pip install-程序员宅基地

文章浏览阅读683次。参考https://packaging.python.org/tutorials/installing-packages/#id7链接的目录1.确定python版本:命令行:python --version;shell(IPython & Jupyter notebook):# ensure that commands are run in the Python installation matching the currently running notebookIn [1]: ._install package和pip install

给学校计算机室申请计算机报告,小学计算机室工作总结 [小学计算机教师工作总结报告]...-程序员宅基地

文章浏览阅读273次。在计算机教学中,教师是教学的研究者、设计者、开发者,是学生学习实践的指导者、组织者、评价者,是学生课题小组、学习小组学习的组织者和协作者,是学生学习环境的管理者。今天小编给大家整理了小学计算机教师工作总结,希望对大家有所帮助。小学计算机教师工作总结范文一一、开展好常规教学。1、开学初,便结合我校的计算机硬件实际,制定出切实可行的教学计划,并严格按教学计划进行上课。在教学过程中,对具体的教学内容都进...

7-19 计算火车运行时间 (15分)_根据火车的出发时间和达到时间,编写程序c++-程序员宅基地

文章浏览阅读465次。本题要求根据火车的出发时间和达到时间,编写程序计算整个旅途所用的时间。输入格式:输入在一行中给出2个4位正整数,其间以空格分隔,分别表示火车的出发时间和到达时间。每个时间的格式为2位小时数(00-23)和2位分钟数(00-59),假设出发和到达在同一天内。输出格式:在一行输出该旅途所用的时间,格式为“hh:mm”,其中hh为2位小时数、mm为2位分钟数。输入样例:1201 1530输出样例:03:29【源代码】#include <iostream>#include_根据火车的出发时间和达到时间,编写程序c++

【C语言】单链表逆转-程序员宅基地

文章浏览阅读48次。单链表逆转

三分钟使用vue-json-excel插件,将table表格数据导出为Excel文件实践_vue2 vue-json-excel 分sheet-程序员宅基地

文章浏览阅读1.5k次。提示:我这是使用了element-ui样式框架,默认是您已经安装了element-ui安装vue-json-excel插件npm install vue-json-excel --save引入vue-json-excel组件<template> <div> <download-excel :fields = "json_field..._vue2 vue-json-excel 分sheet