MySql优化方法

(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走主库。

分库

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

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