MySql优化方法

发布时间:2023-12-15 10:05:36

(MySql优化方法)

硬件配置
  • 扩容磁盘
  • 用SSD取代机械硬盘
  • 提高CPU的核数,提高数据库的计算能力
  • 扩大内存,扩大bufffer Pool可以吃更多的数据

成本高,见效快

参数配置
  • 保证从内存读取

  • 数据预热

  • 减少磁盘的写入次数

    • 增加redo log,减少下跌次数

    • 一般查询日志、慢查询日志不能打开,binlog可以打开

    • 写redo log策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

      0:每隔 1 编写日志文件和刷盘操作(编写日志文件) LogBuffer --> OS cache,刷盘 OS cache --> 磁盘文件),最多丢失 1 秒数据

      1:提交事务时,立即写日志文件和刷盘。数据不会丢失,但会频繁发生 IO 操作

      2:提交事务时,立即写日志文件,每隔一段时间 1 刷盘操作在秒钟内进行

  • 系统调整参数

    • back_log
    • wait_timeout
    • max_user_connection
    • thread_concurrency
    • skip_name_resolve
    • key_buffer_size
    • innodb_buffer_pool_size
    • innodb_additional_mem_pool_size
    • innodb_log_buffer_size
    • query_cache_size
    • read_buffer_size
    • sort_buffer_size
    • read_buffer_size
    • read_rndn_buffer_size
    • record_buffer
    • thread_cache_size
    • table_cache
表结构设计
  • 设计聚合表

  • 冗余字段的设计

  • 分表

    分表分为垂直拆分和水平拆分。

    垂直拆分适用于字段过多的大表。例如,如果一个表有100多个字段,则可以拆卸表中经常不使用的字段或存储更多数据的字段。

    例如,一个表有5000万数据,按照一定的策略分为10个表,每个表有500万数据。这样不仅可以解决查询性能问题,还可以解决数据写作操作的热点征用问题。

  • 字段的设计

    • 使用可以存储最小数据类型的数据,适当
    • 尽量使用TINYINTT、SMALLINT、MEDIUM_INT作为一种整数类型,而不是INT,如果非负,则添加UNSIGNED;
    • VARCHAR的长度只分配真正需要的空间;
    • 例如,对某些文本字段,"省份"或者"性别",用枚举或整数代替字符串类型;在MySQL中, ENUM类型被视为数值数据,数值数据比文本类型快得多
    • 尽量使用TIMESTAMP,而不是DATETIME;
    • 单表字段不宜过多,建议在20以内;
    • 尽可能使用 not null 定义字段,null 占用4字节空间,使数据库在未来实施查询时不需要比较NULL值。
    • 用整形手术存储IP。
    • 尽量少用 text 类型,必要时最好考虑拆表。
SQL语句和索引(重要)

注:索引越多越好。根据查询创建有针对性的索引

创建和使用索引的原则
  • 单表查询:哪个列作查询条件在该列中创建索引?

  • 多表查询:left join 当索引添加到右表相关字段时;right join 当索引添加到左表相关字段时,

  • 不要操作索引列(计算、函数、类型转换)

  • 不要在索引列中使用 !=,<> 非等于

  • 前缀索引只构建字符字段,最好不要做主键;

  • 尽量不要使用UNIQUE,由程序保证约束

  • 不使用外键,程序保证约束

  • 索引列不应为空,也不应使用 is null 或 is not null 判断

  • 索引字段为字符串类型,查询条件值应加‘单引号,避免底层类型自动转换

使用EXPLAIN分析SQL

select_type:查询类型

  • SIMPLE 简单查询
  • PRIMARY 最外层查询
  • UNION union后续查询
  • SUBQUERY 子查询

type:查询数据时使用的方法

  • ALL 全表**(性能最差)**
  • index 基于索引的全表
  • range 范围 (< > in)
  • ref 非唯一的索引单值查询
  • const 使用主键或唯一索引等值查询

possible_keys:可使用的索引

key:真正使用的索引

rows:估计扫描多少行记录

key_len:使用索引的字节数

Extra:额外信息

  • Using where 索引回表
  • Using index 索引直接满足条件
  • Using filesort 需要排序
  • Using temprorary 使用临时表

关注type,最直观地反映了SQL的性能

SQL语句尽可能简单

一个sql只能在一个cpu中操作;大句拆小句,减少锁定时间;一个大sql可以堵塞整个库。

对于连续数值,使用BETWEN而不是INSQL 语句中 IN SELECT不应包含太多的值 语句必须指明字段名称

SELECT * 增加大量不必要的消耗(CPU、IO、内存、网络带宽);使用覆盖索引的可能性降低了。

当只需要一个数据时,使用它 limit 1

limit 相当于截断查询。

例如:对于selectt: * from user limit 1; 虽然进行了全表扫描,但limit截断了全表扫描,从0开始取了一个数据。

如果限制条件下其他字段没有索引,尽量少用或尽量使用排序字段加索引 union all 代替 union

union和union alll的区别在于,union将对数据进行distinct,而distanct的速度取决于现有数据的数量,数量越大,时间就越慢。对于几个数据集,确保数据集之间的数据不重复,基本上是o(n)算法的复杂性。

区分 in 和 exists、not in 和 not exists

如果是exists,则以外表为驱动表,先访问。如果是IN,则先执行子查询。因此,IN适用于外观大、内表小的情况;exists适用于外观小、内表大的情况。

采用合理的分页方法,提高分页效率,避免使用 % 模糊查询前缀

例如:like '%name或者like '%name%这种查询会导致索引失效和全表面扫描。但是likee可以使用 'name%这将使用索引。

避免在 where 在句子中表达字段

索引不会使用这种索引:

select user_id,user_project from user_base where age*2=36;

可以改为:

select user_id,user_project from user_base where age=36/2;

任何对列操作都会导致表扫描,包括数据库函数、计算表达式等,查询时尽量将操作移到等号右侧。

避免隐式类型转换

where 出现在句子中 column 与数据库中的字段类型相对应

必要时可使用 force index 在使用联合索引时,强制查询索引的注意范围

对于联合索引,如果存在范围查询,如betwen、>、<当条件发生时,以后的索引字段将失效。

在某些情况下,使用JOIN优化可以使用连接代替子查询

用小表驱动大表,比如用inner join时,优化器会选择小表作为驱动表

小表驱动大表,即小数据集驱动大数据集
#当 B 表的数据集小于 A 表时,用 in 优化 exist;使用 in ,两表的执行顺序是先查 B 表,再查 A 表select * from A where id in (select id from B)  #当 A 表的数据集小于 B 表时,用 exist 优化 in;使用 exists,两表的执行顺序是先查 A 表,再查 B 表格select * from A where exists (select 1 from B where B.id = A.id)
主从

主从相对简单,从运维层面构建从库后,工程师要做的就是制定路由策略。

有两种路由策略:

读写分离模式,所有对实时性要求较高的写作操作和by id查询主库,其余从库,从库使用Round Robin模式。

链路隔离模式:编写与核心操作相对应的SQL走主库,耗时大、非核心操作的SQL走主库。

分库

需要根据业务场景制定分库策略,最常见的有两种:按年月分库和按角色分库。

根据角色库,最经典的是淘宝基于订单的买家库和卖家库。

上一篇 Java socket 270秒必断线
下一篇 rxjava 倒敘排序

文章素材均来源于网络,如有侵权,请联系管理员删除。

标签: Java教程Java基础Java编程技巧面试题Java面试题