MySQL调优

调优的目的？让吞吐量更大，响应速度更快。

关于数据库优化，我们从以下5个维度进行。

一.优化表结构

表结构尽量遵循三范式的原则，在进行多表查询的时候，必要时可以采用反范式化进行优化。

什么叫范式？

• 在关系型数据库中，关于数据表设计的基本原则，规则就称为范式。

一共有6大范式，知道前5个就行。满足了高级范式,就一定满足低级范式。比如满足第三范式,就一定满足第1,2范式。

• 第一范式：确保每一个字段保证"原子性",不能被拆分。比如有一个字段叫"个人信息",它就可以拆分为地址,年龄,姓名等。就不满足第一范式

• 第二范式：确保表中的每一条记录,都有唯一的标识(主键)。所有的非主键字段,必须完全依赖主键。不能部分依赖。
  注意：这里的主键是联合主键。比如下图：姓名，年龄依赖球员编号；比赛时间，比赛场地依赖比赛编号；只有得分全部依赖

• 第三范式：确保非主键之间是相互独立的,不能产生依赖。下图就不满足

• 巴斯范式(BCNF)：3NF的增强版，在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖的关系。相当于主键中也产生了依赖关系,就不满足巴斯范式。比如下图

• 第四范式：一个表中只有一对1:多的关系。如果一个表中有多个1：多的关系就不满足第四范式。

•  第五范式：也叫完美范式，了解有这么个东西就行

范式的优缺点？

• 优点：消除数据冗余
• 缺点：降低查询效率，因为范式越高，设计出来的数据表就越多，就需要很多的关联查询。

反范式化？

• 是一种空间换时间的优化手段。因为我们遵循业务优先原则，可以通过在表中增加冗余字段来提高数据库的读性能。
• 当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采用反范式化进行优化。

 

数据库的设计原则？三少一多

• 数据表的个数越少越好
• 数据表中的字段个数越少越好
• 数据表中联合主键的字段个数越少越好
• 使用主键和外键越多越好。这里是指外键关系越多，就可以重复的利用数据，而不是指在表中建立好多外键。

数据库表建模的工具?

• PowerDesigner

二.优化逻辑查询

关联查询优化：最好"被驱动表加索引"

• 外连接：一般驱动表是全表查询(就算添加索引也是index),被驱动表是索引查询。(也就是说最好给被驱动表添加索引，驱动表加不加都行)
  • 如：student是驱动表，book是被驱动表   ：EXPLAIN SELECT * FROM student LEFT JOIN book ON student.card = book.card；
• 内连接：谁是驱动表谁是被驱动表由优化器决定,优化器满足"小表驱动大表"。(2个都加索引,让优化器自己决定;如果只加 一个索引,优化器肯定选择加索引的作为被驱动表)

优化器中join 的原理？

• 不加索引
  • Simple Nested-Loop Join(简单嵌套循环连接) 

• • Block Nested-Loop Join(块嵌套循环连接)：不再逐条获取驱动表的数据，而是一块一块的获取，引入join buffer缓存区

　　

• • Hash Join：MySQL8.0之后默认使用hash join。可以做大数据集连接。
• 加索引
  • Index Nested-Loop Join(索引嵌套循环连接)

 

 子查询优化：

• 子查询执行效率不高，使用关联查询(join)代替子查询。
• 效率不高的原因：
  • 查询的过程中需要建立一个临时表，查询完毕，再撤销临时表。消耗性能
  • 临时表都不会使用索引

 排序优化：

• 前提知识：MySQL支持2中排序方式，所以优化也是从这俩方面考虑
  • index排序：b+树的叶子节点就是按照排序进行的，使用索引直接就可以保证有序性
  • FileSort排序：将需要排序的数据加载到内存中，然后进行排序。
• 尽量使用索引完成order by排序。如果where 和order by 后面的列相同就用单索引，不同就用联合索引。
• 对FileSort进行调优
  • 前提知识：FileSort有2种算法
    • 双路排序(慢)：进行俩次磁盘扫描，第一次只加载需要排序的列到sort_buffer，进行排序。然后根据排序好的列，第二次从磁盘读取其他的列。
    • 单路排序(快)：一次性将所有列的数据加载到sort_buffer中，进行排序。
  • 提高sort_buffer_size：不管哪种算法，提高这个内存值肯定加大效率。
  • 提高max_length_for_sort_data：这个参数就是一个界限，需要返回的列总长度大于这个值就使用双路，小于这个值就使用单路。

group by优化：

• group by 优化的方法和order by一样。

分页查询优化：

• 如果像下图查询的情况极端，尽量的使用表中其他字段的索引。

 

其他的优化

• exists和in：小表驱动大表

• count(*)和count(1)和count(具体字段)的效率
  • count(*)和count(1)没有本质区别，执行时间基本一样
  • count(具体字段)的时候，尽量使用占用空间少的二级索引。因为二级索引存储的信息相比聚簇索引要少很多。count(*)和count(1) 系统会自动选择占用空间少的二级索引进行统计。　　
  • innodb的count()是O(n)级别的，MyISAM是O(1)级别的。
• 关于select *
  • 尽量使用什么字段就指出来，不要使用select *。因为会加载很多没用的列。
  • 无法使用覆盖索引
• 关于Limit 1：
  • 如果是全表扫描，加上Limit 1。找到数据就不会再继续查找了，加快查找效率
  • 如果是唯一索引，找到数据也不会继续查找了，Limit 1 就不管用了。

 

 三.优化物理查询(索引)

• 选择适合做索引的字段(MySQL索引讲)
• 哪些情况导致索引失效(MySQL索引讲)
• 使用覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引。(也就是需要的列恰好都在索引的叶子节点上存储，不需要回表)
  • 好处：无需回表；可以把随机IO变成顺序IO加快查询效率(利用到索引都是顺序IO，因为索引就是有顺序的)。
• 使用索引下推：如图

• • 索引下推就用在一些and查询语句中，本来通过非聚集索引zipcode查询出来数据，要进行回表，但是如果查出来100条，分别对这100条进行回表就很浪费性能，icp就是先不进行回表，使用后面的条件进行过滤，过滤完毕之后比如剩下10条，对这10条进行回表就行了。
  • set optimizer_switch = 'index_condition_pushdown=on'     //开启索引下推
• 使用隐藏索引：之前索引要是删除后,发现效果不好或者出错，只能重新添加索引。一删一加浪费性能,现在就可以先隐藏索引，让索引不起作用。发现效果不错,再删除索引。
  • create index 索引名 on 表名(列名) invisible；

四.使用缓存

对于热点数据可以使用redis或者Memcached作为缓存，减少数据库的压力

五.库级优化

从以下3个方面进行调优。

1.优化MySQL服务器：在my.cnf或者my.ini文件的[mysqld]组中配置。

• innodb_buffer_pool_size：表示表和索引的最大缓存。这个值越大，查询速度越快。
• key_buffer_size：表示索引缓存区的大小，索引缓存区是所有线程共享，对于4GB左右的服务器该参数可设置为256M或384M。
• table_cache：表示同时打开的表的个数。默认为2402，调到512-1024最佳，这个值不是越大越好，因为同时打开好多表影响操作系统的性能。
• query_cache_size：表示查询缓存区的大小。这个就是MySQL架构中的查询缓冲器。在8.0废除，需要配合query_cache_type才能使用。
• query_cache_type：0代表关闭，1代表开启，2代表自定义。
  • 关于自定义：eg：select SQL_CACHE * from test where id=5  使用SQL_CACHE可以自定义的在sql声明使用查询缓存
• sort_buffer_size：表示每一个需要进行排序的线程分配的缓存区的大小。提高这个参数的值可以提高order by和group by的速度。对于4GB左右的服务器建议设置为6-8M。如果有100个连接，实际分配的总共排序缓存区的大小为6 * 100 = 600MB。
• join_buffer_size = 8M：表示联合查询操作时所能使用的缓存区大小，每一线程独享。
• read_buffer_size：表示每个线程连续扫描时为扫描的每一个表分配的缓冲区的大小。默认为64K，可设置为4M。
• innodb_flush_log_at_trx_commit：表示何时将缓存区的数据写入日志文件。
  • 0：表示每秒1次，将数据写入日志文件并将日志文件写入磁盘。
  • 1：表示每次提交事务，将数据写入日志文件并将日志文件写入磁盘。
  • 2：表示每次提交事务，将数据写入日志文件，每隔1s将日志文件写入磁盘。
• innodb_log_buffer_size：事务日志所使用的缓存区，log buffer的值。
• max_connections：表示允许连接到MySQL数据库的最大数量。
• back_log：用于控制MySQL监听TCP端口时设置的挤压请求栈大小，比如：max_connections为500，其他来的请求就可以放入这个栈中。5.0之前为50，现在一般为50+(max_connections / 5)。
• thread_cache_size：线程缓冲池的大小。
• wait_timeout：一个请求的最大连接时间，4GB的服务器一般设置为5-10。
• interactive_timeout：表示服务器在关闭连接前等待行动的秒数。

 2.优化数据库结构：

• 拆分表：冷热数据分离，将一些查询频率高的列放在一个表中，查询频率不高的列放在另一个表中。
• 添加中间表：对于联合查询，使用一个中间表记录它们之间的关系，将原来的联合查询改为对中间表的查询，加快效率。
• 优化数据类型：优先选择符合存储需要的最小的数据类型。
  • 存储整数一般使用int型，非负型数据(自增id，整型ip)可以使用unsigned型，因为无符号相对于有符号，同样的字节数，存储量更大(不用给负数留空间)。
  • 避免使用TEXT,BLOB类型。
  • 使用TimeStamp存储时间
  • 使用Declmal代替float和double存储精准浮点数，因为Declmal为精准浮点型，在计算时不会丢失精度，占用空间由定义的宽度决定。
• 使用分析表，检查表，优化表的语句
  • 分析表：分析关键字的分布，analyze table ...
  • 检查表：检查表中是否存在错误，check table ...
  • 优化表：消除删除/更新造成的空间浪费，optimize table...，只能优化varchar，blob，text类型的字段，整理文件的碎片，重新规划空间。
• 创建全局通用表空间：create tablespace...，相对于独享表空间，可以节约元数据方面的内存。

3.大表优化：

• Memcached（高速缓存系统）+ MySQL(垂直拆分:拆分表结构) (拆分表)
• mysql主从复制，读写分离
• 分表分库 + 水平拆分 + Mysql 集群 (水平拆分:把数据库中的每个表中的数据,拆分在不同的数据库中)(拆分表中的数据)
  
  • 分库：不同的模块拥有自己的不同的数据库
  • 分表：水平拆分，垂直拆分
  • 出现的问题：分布式id；分布式事务；排序，分页的问题（二次排序，先在自己数据库排序，后结合在一起进行排序）
  • 参考资料：分库分表你要怎么分？

 

今天是除夕，祝大家新年快乐！

 寄语：放弃可以找到一万个理由,坚持只需要一个信念!