厚积薄发打卡Day82 ：【MSUP】MySql调优与最佳实践（上）

前言

在看狂神频道的时候偶然发现下图，感触颇深。特别在当今【程序 = 业务 + 框架】思想盛行的开发者中，夯实基础基础显得格外重要，因此开此专栏总结记录。

MySql调优与最佳实践

MySql是互联网行业中使用最为广泛的关系型数据库：

1. MySql 的第一个知识点是要能手写常用的SQL语句，这块没有什么特殊的技巧，根据所列举的语句进行对应练习即可：
   1. 条件查询
   2. 关联查询
   3. 排序查询
   4. 分组查询
   5. 逻辑关键字
   6. 函数
2. 知道MySql 都提供哪些基本的数据类型，不同数据类型占用的空间大小：
   1. 整数型
   2. 浮点数
   3. 字符串
   4. 二进制
   5. 时间
3. MySql 中的主要存储引擎：
   1. MyIASM是MySql 官方提供的存储引擎，其特点是支持全文检索，查询效率比较高，缺点是不支持事务使用表及锁
   2. InnoDB 在5.5版本后成为MySql 中的默认存储引擎，特点是支持ACID事务，支持外键，支持行级锁，提高了并发效率
   3. ToKuDB是第三方开发的开源存储引擎，有非常快的写速度支持数据的压缩存储，可以在线添加索引，而不影响读写操作，但是因为压缩的原因，ToKuDB非常适合访问频率不高的数据，或者历史数据归档不适合大量读取的场景
4. MySql中的锁，
   • 表锁：
     • 刚才也提到了myiasm使用表级锁，innodb使用行级锁表锁开销小，加锁快不会出现死锁；但锁的力度比较大，发生所冲突的概率高，并发访问效率比较低，
   • 行锁：
     • 行级锁开销大加锁慢，有可能会出现死锁，不会因为锁定力度小，发生所冲突的概率比较低，并发访问效率比较高，
   • 共享锁：
     • 共享锁也就是读锁其他事务可以读，但不能写，MySql 可以通过log in share mode 语句来显示的
       使用共享锁
   • 排他锁：
     • 排他锁就是写锁，其他事务不能读取，也不能写，对update delete insert 语句，innodb会自动给涉及的数据集加排他锁或者使用select for update，使用显示的排他锁
5. 索引后续小节讲解
6. MySql 的存储过程与函数
   • 存储过程与函数都可以避免开发人员重复编写相同的SQL语句，并且存储过程和函数都是在MySql 服务器中运行的，可以减少客户端与服务端的数据传输
   • 存储过程实现了更复杂的功能，而函数一般用来实现针对性比较强的功能比如按特殊策略来求和，存储过程可以执行，包括修改表等一系列数据库操作，而用户定义函数不能用于执行修改全局数据库状态的操作
   • 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句中的一个部分来调用sql语句中不能使用存储过程，但可以使用函数
   • 不过存储过程一般与数据库的实现绑定，使用存储过程会降低程序的可移植性，应谨慎使用
7. MySql 8.0的一些新特性
   • 例如默认字符集改为了utf8
   • 增加了隐式索引功能，隐藏后的索引不会被查询优化器使用，可以使用这个特性用于性能调试
   • 支持了通用表表达式，使复杂查询中的嵌入语句表达更清晰
   • 新增了窗口函数的概念，它可以用来实现新的查询方式
   • 窗口函数与suM Count 等集合函数类似，但不会将多行查询结果合并，而是将结果放于多行中
     即窗口函数不需要group by
8. MySql调优，稍后重点讲解

MySql索引

• 索引也可以大幅增加数据库的查询性能
• 在实际业务场景中或多或少都会使用到，但索引也是有代价的：
  • 首先需要额外的磁盘空间来保存索引
  • 其次，对于插入、更新、删除等操作，由于更新索引会增加额外的开销，因此索引比较适合用在读多写少的场景
• 先了解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 的调优也是研发人员需要掌握的一项技能

一般MySql 调优有图中的四个维度：

• 第一个是针对数据库设计表结构设计以及索引设置维度进行优化
• 第二个是对我们业务中使用的SQL语句进行优化，例如调整where 查询条件
• 第三个维度是对MySql 服务的配置进行优化，例如对连接数的管理对索引缓存查询、缓存排序、各种缓存大小进行优化
• 第四个维度是对硬件设备和操作系统设置进行优化，例如调整操作系统参数，禁用swap，增加内存升级固态硬盘等等

这四个维度，从优化成本的角度讲，从左到右，优化成本逐渐升高，从优化效果角度来看，从右到左优化效果更高。

对于研发人员来说，前两个维度与业务息息相关，因此需要重点掌握，后两个维度更适合DBA进行深入学习，简单了解就好

表结构及索引优化：

我们重点来看刚才说的前两个维度，先看到左边的模块：

• 关于表结构和索引的优化应掌握如下原则：
• 第一个要在设计表结构时考虑数据库的水平和垂直扩展能力，提前规划好未来一年的数据量，读写量的增长规划好分库分表方案
  • 比如设计用户信息表时，预计一年后用户数据十一条，写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注入
• 第五个是尽量使用索引扫描来进行排序，也就是尽量在有索引的字段上，进行排序操作