本文主要是介绍厚积薄发打卡Day82 :【MSUP】MySQL调优与最佳实践(上)先行概念,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
厚积薄发打卡Day82 :【MSUP】MySql调优与最佳实践(上)
前言
在看狂神频道的时候偶然发现下图,感触颇深。特别在当今【程序 = 业务 + 框架】思想盛行的开发者中,夯实基础基础显得格外重要,因此开此专栏总结记录。
![在这里插入图片描述](/upload/202108/01/202108010206141757.png)
MySql调优与最佳实践
MySql是互联网行业中使用最为广泛的关系型数据库:
![在这里插入图片描述](/upload/202108/01/202108010206144585.png)
- MySql 的第一个知识点是要能手写常用的SQL语句,这块没有什么特殊的技巧,根据所列举的语句进行对应练习即可:
- 条件查询
- 关联查询
- 排序查询
- 分组查询
- 逻辑关键字
- 函数
- 知道MySql 都提供哪些基本的数据类型,不同数据类型占用的空间大小:
- 整数型
- 浮点数
- 字符串
- 二进制
- 时间
- MySql 中的主要存储引擎:
- MyIASM是MySql 官方提供的存储引擎,其特点是支持全文检索,查询效率比较高,缺点是不支持事务使用表及锁
- InnoDB 在5.5版本后成为MySql 中的默认存储引擎,特点是支持ACID事务,支持外键,支持行级锁,提高了并发效率
- ToKuDB是第三方开发的开源存储引擎,有非常快的写速度支持数据的压缩存储,可以在线添加索引,而不影响读写操作,但是因为压缩的原因,ToKuDB非常适合访问频率不高的数据,或者历史数据归档不适合大量读取的场景
- MySql中的锁,
- 表锁:
- 刚才也提到了myiasm使用表级锁,innodb使用行级锁表锁开销小,加锁快不会出现死锁;但锁的力度比较大,发生所冲突的概率高,并发访问效率比较低,
- 行锁:
- 行级锁开销大加锁慢,有可能会出现死锁,不会因为锁定力度小,发生所冲突的概率比较低,并发访问效率比较高,
- 共享锁:
- 共享锁也就是读锁其他事务可以读,但不能写,MySql 可以通过log in share mode 语句来显示的
使用共享锁
- 排他锁:
- 排他锁就是写锁,其他事务不能读取,也不能写,对update delete insert 语句,innodb会自动给涉及的数据集加排他锁或者使用select for update,使用显示的排他锁
- 索引后续小节讲解
- MySql 的存储过程与函数
- 存储过程与函数都可以避免开发人员重复编写相同的SQL语句,并且存储过程和函数都是在MySql 服务器中运行的,可以减少客户端与服务端的数据传输
- 存储过程实现了更复杂的功能,而函数一般用来实现针对性比较强的功能比如按特殊策略来求和,存储过程可以执行,包括修改表等一系列数据库操作,而用户定义函数不能用于执行修改全局数据库状态的操作
- 存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句中的一个部分来调用sql语句中不能使用存储过程,但可以使用函数
- 不过存储过程一般与数据库的实现绑定,使用存储过程会降低程序的可移植性,应谨慎使用
- MySql 8.0的一些新特性
- 例如默认字符集改为了utf8
- 增加了隐式索引功能,隐藏后的索引不会被查询优化器使用,可以使用这个特性用于性能调试
- 支持了通用表表达式,使复杂查询中的嵌入语句表达更清晰
- 新增了窗口函数的概念,它可以用来实现新的查询方式
- 窗口函数与suM Count 等集合函数类似,但不会将多行查询结果合并,而是将结果放于多行中
即窗口函数不需要group by
- MySql调优,稍后重点讲解
MySql索引
![在这里插入图片描述](/upload/202108/01/202108010206146787.png)
- 索引也可以大幅增加数据库的查询性能
- 在实际业务场景中或多或少都会使用到,但索引也是有代价的:
- 首先需要额外的磁盘空间来保存索引
- 其次,对于插入、更新、删除等操作,由于更新索引会增加额外的开销,因此索引比较适合用在读多写少的场景
- 先了解MySql 的索引类型:
- 唯一索引(UNIQUE ):
- MySql 的索引按类型有唯一索引,就是索引列中的值必须是唯一的,但是允许出现空值,这种索引一般用来保证数据的唯一性
- 比如保存账户信息的表,每个账户的id必须要保证唯一,如果重复插入相同的账户,id这个时候会返回异常
- 主键索引(PRIMARY ):
- 主键索引是一种特殊的唯一索引,但它不允许出现空值,第三种是普通索引与唯一索引不同,它允许索引列中存在相同的值,例如学生的成绩表,各个学科的分数是允许重复的,就可以使用普通索引
- 普通索引(INDEX)
- 这是最基本的索引类型,而且它没有唯一性之类的限制。
- 联合索引
- 联合索引就是由多个列共同组成的索引,一个表中含有多个单列的索引并不是联合索引
- 联合索引是对多个列字段按顺序共同组成一个索引,应用联合索引时需要注意最左原则,就是where查询条件中的字段
- 所以字段从左到右进行匹配:比如一个用户信息表,用姓名和年龄组成了联合索引,如果查询条件是姓名等于张三,那么满足最左原则,如果查询条件是年龄大于二十,由于索引中最左的字段是姓名,不是年龄,所以呢就不能使用这个索引
- 全文索引(FULLTEXT ):
- 前面提到了myisam引擎中实现了这个索引,在5.6版本innoDB引擎也支持了全文索引,并且在5.6.7版本后支持了中文索引,
- 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
- 索引实现:
- MySql 的索引按时间来分
- B-Tree
- B+Tree比较适合用作大于或者小于这样的范围查询,
- B+Tree是MySql 中最常使用的一种索引实现
- R-Tree
- R-Tree是一种用于处理多维数据的数据结构,可以对地理数据进行空间索引,不过实际业务场景中呢使用的比较少
- Hash
- Hash是使用散列表来对数据进行索引,Hash方式不需要像B Tree那样需要查询多次才能定位到记录,因此Hash索引的效率比B-Tree高,但是不支持范围查找,排序等功能实际使用的也比较少
- FullText
- 最后full text就是我们前面提到的全文索引,是一种记录关键字与对于文档关系的一种倒排索引
MySql调优
MySql 的调优也是研发人员需要掌握的一项技能
![在这里插入图片描述](/upload/202108/01/202108010206148847.png)
一般MySql 调优有图中的四个维度:
- 第一个是针对数据库设计表结构设计以及索引设置维度进行优化
- 第二个是对我们业务中使用的SQL语句进行优化,例如调整where 查询条件
- 第三个维度是对MySql 服务的配置进行优化,例如对连接数的管理对索引缓存查询、缓存排序、各种缓存大小进行优化
- 第四个维度是对硬件设备和操作系统设置进行优化,例如调整操作系统参数,禁用swap,增加内存升级固态硬盘等等
这四个维度,从优化成本的角度讲,从左到右,优化成本逐渐升高,从优化效果角度来看,从右到左优化效果更高。
对于研发人员来说,前两个维度与业务息息相关,因此需要重点掌握,后两个维度更适合DBA进行深入学习,简单了解就好
![在这里插入图片描述](/upload/202108/01/202108010206151211.png)
表结构及索引优化:
我们重点来看刚才说的前两个维度,先看到左边的模块:
- 关于表结构和索引的优化应掌握如下原则:
- 第一个要在设计表结构时考虑数据库的水平和垂直扩展能力,提前规划好未来一年的数据量,读写量的增长规划好分库分表方案
- 比如设计用户信息表时,预计一年后用户数据十一条,写QPS约五千,读QPS三万可以设计按UID维度进行散列分为四个库,每个库三十二张表,这样可以保证单表数据量控制在千万级别
- 第二个要为字段选择合适的数据类型,在保留扩展能力的前提下,优先选用较小的数据结构,
- 例如保存年龄的字段要使用tiny int,而不要使用int
- 第三个,可以将字段多的表分解成多个表,必要时增加中间表进行关联
- 第四个一般来说我们设计关系数据库时需要满足第三范式,
- 但为了满足第三范式,我们可能会拆分出多张表,而在查询时又需要对多张表进行关联查询,有时为了提高查询效率,会降低范式要求在表中保存一定的冗余信息,也叫做反范式,但要注意反范式一定要适度
- 第五个要善用索引
- 比如未经常作为查询条件的字段创建索引,创建联合索引时要根据最左原则,考虑索引的复用能力不要重复创建索引,要为保证数据不能重复的字段创建唯一索引等等
- 不过要注意索引对插入,更新等写操作是有代价的,不要滥用索引,比如像性别这样唯一性很差的字段,就不适合建立索引
- 第六个列字段尽量设置not null, MySql 难以对使用null的列进行查询优化
- 允许null会使索引索引统计和值更加复杂,允许null 值的列需要更多的存储空间,还需要MySql 内部进行特殊处理
SQL语句优化
再看到右面的模块对社会语句进行优化的原则:
- 第一个要找到最需要优化的sql语句:
- 要么是使用频率最高的,要么是优化后提高最明显的语句,可以通过查询MySql 的慢查询日志来发现需要进行优化的SQL语句
- 第二个,要学会利用MySql 提供的分析工具,
- 例如使用explain 来分析语句的执行计划,看看是否使用了索引,使用了哪个索引,扫描了多少记录,是否使用了文件排序等等,
- 或者使用profile 命令来分析某个语句执行过程中的各分布耗时
- 第三个要注意使用查询语句时,要避免使用select *,而应当指定需要获取的字段,
- 原因一是可以避免查询出不需要使用的字段,二是可以避免查询列字段的源信息
- 第四个是尽量使用prepared statements一是性能更好,另一个是可以防止sql注入
耗时 - 第三个要注意使用查询语句时,要避免使用select *,而应当指定需要获取的字段,
- 原因一是可以避免查询出不需要使用的字段,二是可以避免查询列字段的源信息
- 第四个是尽量使用prepared statements一是性能更好,另一个是可以防止sql注入
- 第五个是尽量使用索引扫描来进行排序,也就是尽量在有索引的字段上,进行排序操作
这篇关于厚积薄发打卡Day82 :【MSUP】MySQL调优与最佳实践(上)先行概念的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!