MySQL优化

MySQL优化

一、概述

  1. 为什么要优化

    • 一个应用吞吐量瓶颈往往出现在数据库的处理速度上
    • 随着应用程序的使用，数据库数据逐渐增多，数据库处理的压力逐渐增大
    • 关系型数据库的数据是存放在磁盘上的，读写速度较慢（与内存中的数据相比）

  2. 如何优化

    • 表、字段的设计阶段、考量更优的存储和计算
    • 数据库自身提供的优化功能，如索引
    • 横向扩展、主从复制、读写分离、负载均衡和高可用
    • 典型SQL语句优化（收益甚微）

二、字段设计

  1. 典型方案

    • 对精度有要求
      • decimal
      • 小数转整数
    • 尽量使用整数表示字符串
      • IP
        • inet_aton('ip')
        • inet_ntoa(num)
    • 尽可能的使用not null
      • null数值的计算逻辑比较复杂
    • 定长和非定长的选择
      • 较长的数字数据可以使用decimal
      • char为定长（超过长度的内容将被截掉），varchar为非定长，text对内容长度的保存额外保存而varchar对长度的保存占用数据空间
    • 字段数不要过多，字段注释是必要的，字段命名见名知意，可以预留字段以备扩展

  2. 范式

    • 第一范式：字段原子性（关系数据库有列的概念，默认就符合了）
    • 第二范式：消除对主键的部分依赖（因为主键可能不止一个）
    • 第三范式：消除对主键的传递依赖，高内聚，如商品表可分为商品简略信息表和商品详情表两张表

三、存储引擎的选择

  1. 功能差异

    • Innodb支持事务、行级锁定、外键

  2. 存储差异

    • 存储方式
      • MyISAM的数据和索引时分开存储的（.MYI，.MYD).而Innodb是存在一起的（.frm）
    • 表可移动性
      • 可以通过移动对应的MYI和MYD能够实现表的移动，而Innodb还有额外的关联文件
    • 碎片空间
      • MyISAM删除数据时会产生碎片空间（占用表空间）需要定期通过optimize table-name 手动优化，而Innodb不会
    • 有序存储
      • Innodb插入数据时按照主键有序来插入，因此表中数据默认按照主键有序（耗费写入时间，因为需要在b+tree中查找插入点，但查找效率高）

  3. 选择依据

    • 读多写少用MyISAM(新闻、博客网站)
    • 读多页也写多用Innodb
      • 支持事务、外键，保证数据一致性，完整性
      • 开发能力强（行锁）

四、索引

  1. 什么是索引

    • 从数据中提取的具有标志性的关键字，并且有到对应数据的映射关系

  2. 类型

    • 主键索引 primary key：要求关键字唯一且不为null
    • 普通索引 key ：符合索引仅按照 第一字段有序
    • 唯一索引 unique：要求关键字唯一
    • 全文索引 fulltext key：（不支持中文）

  3. 索引管理语法

    • 查看索引

      -- 第一种
show create table student
-- 第二种
desc student

    • 建立索引

      --创建是指定
first_name varchar(16),last_name(16),key name (first_name,last_name)
--更改表结构
alert table student add key/unique key/primary key/fulltext key key_name(first_name,last_name)

    • 删除索引

      alert table student drop key key_name
--如果删除的是主键索引，并且主键自增长，则需要alert modify 先取消自增长再删除

  4. 执行计划 explain

    • 分析SQL执行是否用到了索引，用到了什么索引

  5. 索引的使用场景

    • where
      • 如果查找字段都建立了索引，则会索引覆盖
    • order by
      • 如果排序字段建立了索引，而索引又是有序排列的，直接根据索引拿对应数据即可，与读取查询出来的所有数据再排序相比效率很高
    • join
      • 如果join on的条件字段建立了索引，查找会变得高效
    • 覆盖索引：直接对索引做查找，而不去读取数据

  6. 语法细节

    • 即使建立了索引，有些场景也不一定使用
      • where id+1 = ?建议写成where id = ?-1 即保证索引字段的独立出现
      • like语句不要在关键字前模糊匹配，即%keyword不会使用索引，而‘keyword%’会使用索引
      • or关键两边条件字段都建立索引时才会使用索引，只要有一边不是就会做全表扫描
      • 状态值，像性别这样的状态值，一个关键字对应很多条数据，会认为使用索引比全表扫描效率还低

  7. 索引的存储结构

    • Btree
      • 搜索多叉树：节点内关键字有序排列，关键字之间有一个指针，查找效率log（nodesize,N)，其中nodeSize指一个节点内关键字数量（这取决于关键字长度和节点大小）
    • B+Tree
      • 又BTree升级而来，数据和关键字存在一块空间，省去了由关键字到数据映射的找数据存放地的时间

五、查询缓存

  1. 将select查询结果缓存起来，key为SQL语句，value为查询结果

    • 如果SQL功能一样，但只是多个空格或略微改动都会导致key的不匹配

  2. 客户端开启

    • query_cache_type
      • 0-不开启
      • 1-开启：默认缓存每条select，针对某个sql不缓存，“select sql-no-cache
      • 2-不开启：默认都不缓存，通过select sql-cache指定缓存哪一条

  3. 客户端设置缓存大小

    • query_cache_size

  4. 重置缓存

    • reset query cache

  5. 缓存失效

    • 对数据表的改动会导致基于该数据表的所有缓存失效（表层面的管理）

六、分区

默认情况下一张表对应一组存储文件，但当数据两较大时（通常千万条级别）需要将数据分到多组存储文件，保证单个文件的处理效率

  1. partition by 分区函数（分区字段）（分区逻辑）

    • hash-分区字段为整数
    • key-分区字段为字符串
    • range-基于比较，只支持less than
    • list-基数状态值

  2. 分区管理

    • 创建时分区

      create table article() partition by key(title) partition 10

    • 修改表结构

      alert table article add partition(分区逻辑)

  3. 分区字段应选择常用的检索字段，否则分区意义不大

七、水平分割和垂直分割

  1. 水平

    • 多张结构相同的表存储一类型数据
      • 单独一张表保证id唯一性

  2. 垂直

    • 分割字段到多张表，这些表记录是一一对应关系

八、集群

  1. 主从复制

    • 首先手动将slave和master同步一下
      • stop slave
      • master导出数据到slave执行一遍
      • show master status with read lock 记录file和positioin
      • 到slave change master to
    • start slave 查看Slave_IO_Running和Slave_SQL_Running必须都为YES
    • master可读可写，但slave只能读，否则主从复制会失效需要重新手动同步
    • mysqlreplicate快速配置主从复制

  2. 读写分离（基于主从复制）

    • 使用原生Java.sql.Connect
      • WriteDatebase提供写连接
      • ReadDatebase提供读连接
    • 借助Spring AOP和Aspect实现数据源动态切换

  3. 负载均衡

    • 算法
      • 轮询
      • 加权轮询
      • 依据负载情况

  4. 高可用

    • 为单机提供一个冗余机
      • 心跳检测
      • 虚IP
      • 主从复制

九、典型SQL

  1. 线上DDL

    • 为了避免长时间表级锁定
      • copy策略，逐行复制，记录复制期间旧表SQL日志重新执行
      • mysql5.6 online ddl，大大缩短锁定时间

  2. 批量导入

    • 先禁用索引和约束，导入之后统一建立
    • 避免逐条事务
      • Innodb为了保证一致性，默认为每条SQL加事务（也是要消耗时间的），批量导入前应手动建立事务，导入完毕后手动提交事务

  3. limit offset,rows

    • 避免较大的offset(较大的页码数)
      • offset用来跳过数据，完全可以用过滤筛选数据，而不是查出来之后再通过offset跳过

  4. select *

    • 尽量查询所需字段，减少网络传输延时（影响不大）

  5. order bu rand()

    • 会为每条数据生成一个随机

  6. limit 1

    • 如果确定了仅仅检索一条数据，建议都加上limit 1

十、慢查询日志

定位查询效率较低的SQL，针对性地做优化

  1. 配置项

    • 开启slow_query_log
    • 临界时间long_query_time

慢查询日志会自己记录超过临界时间的SQL，并保存在datadir下的XXX-slow.log中

十一、profile

  • 自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费时间

  • 配置项

    • 开启profile

  • 查看日志信息show profiles

  • 查看具体SQL的详细步骤花费的时间

    show profiles for query Query_ID

十二、典型 的服务器配置

  • max_connections，最大客户端连接数
  • table_open_cache,表文件索引缓存句柄数，加快表文件的读写
  • key_buffer_size,索引缓存大小
  • innodb_buffer_pool_size,innodb的缓冲池大小，实现innodb各种功能的前提
  • innodb_file_per_table,每个表一个ibd文件，否则innodb共享空表空间

十三、压测工具musql slap

  1. 自动生成sql并执行来测试性能

    • mysqlslap --auto-generate-sql -uroot -proot

  2. 并发测试

    • mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot ,模拟100个客户端执行sql

  3. 多轮测试，反应平均情况

    • mysqlslap --auto-generate-sql --concurrency=100 --interations=3 -uroot -proot,模拟100个客户端执行sql,执行3轮

  4. 存储引擎测试

    • -- engine=innodb
      • mysqlslap --auto-generate-sql --concurrency=100 --interations=3 --engine-innodb -uroot -proot,模拟100个客户端用户执行sql,执行3轮，innodb的处理性能
    • --engine=myisam
      • mysqlslap --auto-generate-sql --concurrency=100 --interations=3 --engine-myisam -uroot -proot,模拟100个客户端用户执行sql,执行3轮，innodb的处理性能


作者：关小涛
学习和分享是博客最大的乐趣，欢迎大家取之所需。
努力是自己努力的原因，每周天写博客总结工作中的新技能和出现的问题
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/XtsLife/p/11545616.html