mysql 的优化

最新推荐文章于 2024-11-10 14:18:18 发布

原创最新推荐文章于 2024-11-10 14:18:18 发布 · 219 阅读

0 ·

CC 4.0 BY-SA版权

mysql数据库专栏收录该内容

124 篇文章

订阅专栏

1.概述

Mysql的优化，优化无止境。

笔记见百度网盘：

2.具体策略

2.1 单表的优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候 MySQL 单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

字段：

1.VARCHAR的长度只分配真正需要的空间；

2.使用枚举或整数代替字符串类型；

3.尽量使用TIMESTAMP而非DATETIME；

4.单表不要有太多字段，建议在 20 以内；

5.避免使用 NULL 字段，很难查询优化且占用额外索引空间；

6.用整型来存 IP。

索引：

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描；

1.应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描；

2.值分布很稀少的字段不适合建索引，例如 "性别" 这种只有两三个值的字段

3.使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

查询sql语句：

1.尽量避免在WHERE子句中使用!= 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描；

2.对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；

3.不用SELECT *；

4.OR改写成IN：OR的效率是 n 级别，IN的效率是 log(n) 级别，in 的个数建议控制在 200 以内；

5.可通过开启慢查询日志来找出较慢的SQL；

6.不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边；

7.SQL 语句尽可能简单：一条 SQL 只能在一个 CPU 运算；大语句拆小语句，减少锁时间；一条大 SQL 可以堵死整个库

2.2 数据库存储引擎

1.MyISAM 引擎是 MySQL 5.1 及之前版本的默认引擎，它的特点是：

1.擅长快速的插入和读取数据，在表有读取查询的同时，支持往表中插入新纪录；

2.不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁；

3.不支持事务；

4.对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用。

2.InnoDB 在 MySQL 5.5 后成为默认索引，它的特点是：

1.支持行锁，采用 MVCC 来支持高并发；

2.支持事务；

3.如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），数据的约束比较高，innodb是不错的选择

3.Memory：所有数据都存储在内存中，查询效率比较高，但安全性比较的低，可以选择memory

https://www.cnblogs.com/yuxiuyan/p/6511837.html

2.3 系统调优参数

可以使用下面几个工具来做基准测试：

1.sysbench：一个模块化，跨平台以及多线程的性能测试工具；

2.iibench-mysql：基于Java的MySQL/Percona/MariaDB 索引进行插入性能测试工具；

3.tpcc-mysql：Percona 开发的 TPC-C 测试工具。

这里介绍一些比较重要的参数：

1.thread_concurrency：并发线程数，设为CPU核数的两倍；

2.key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对 MyISAM 表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在 0.1% 以下最好；

2.4 硬件升级

Scale up（按比例增加），这个不多说了，根据 MySQL 是 CPU 密集型还是 I/O 密集型，通过提升 CPU 和内存、使用 SSD，都能显著提升 MySQL 性能。

2.5 架构

1.主从分离，读写分离

一主多从的级联结构，主库写从库读；

缓存

在mysql内部设置缓存参数
数据访问层：比如 MyBatis 针对 SQL 语句做缓存，而 Hibernate 可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object；
Web 层：针对 web 页面做缓存；

表分区

MySQL 在 5.1 版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码。

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对 SQL 层来说是一个完全封装底层的黑盒子。MySQL 实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

分区的好处是：

1.分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作；

2.部分查询能够从查询条件确定只落在少数分区上，速度会很快；

3.分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备

分区的缺点是：

1.一个表最多只能有 1024 个分区；

2.分区表无法使用外键约束；

3.NULL 值会使分区过滤无效；

分区的类型：

1.RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区；

2.LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择；

3.HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式；

4.KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值

拆分表

1.垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。

垂直拆分的优点：

可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起；

可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次数 (每次查询时读取的 Block 就少)；

垂直拆分的缺点：

会引起表连接 JOIN 操作（增加 CPU 开销）可以通过在业务服务器上进行 join 来减少数据库压力；

依然存在单表数据量过大的问题（需要水平拆分）；

事务处理复杂。

2.水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的 MySQL 表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表。

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻 MySQL 服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的 IO、CPU、网络，这个就要通过分库来解决。

实际情况中往往会是垂直拆分和水平拆分的结合

水平拆分的优点：

1.不存在单库大数据和高并发的性能瓶颈；

2.提高了系统的稳定性和负载能力。

3.应用端改造较少；

水平拆分的缺点:

1.分片事务一致性难以解决；

2.数据多次扩展难度跟维护量极大。

分片的原则：

1.能不分就不分，参考单表优化；

2.分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询 SQL 跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量；

3.尽量不要在一个事务中的 SQL 跨越多个分片，分布式事务一直是个不好处理的问题；

4.查询条件尽量优化，尽量避免 Select * 的方式，大量数据结果集下，会消耗大量带宽和 CPU 资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引；

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询 SQL 的条件，因为不带任何 Where 语句的查询 SQL，会遍历所有的分片，性能相对最差，因此这种 SQL 越多，对系统的影响越大，所以我们要尽量避免这种 SQL 的产生。