MySQL大数据量优化

最新推荐文章于 2024-06-17 10:04:37 发布

安心撸码

最新推荐文章于 2024-06-17 10:04:37 发布

阅读量688

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/lyf1548603225/article/details/97047070

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

当 MySQL 单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化。

字段类型优化

1、除非单表数据未来会一直不断上涨(例如网络爬虫)，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度

2、一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候 MySQL 单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量。

索引优化

1、索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于更快地进检索，mysql 有四种不同的索引类型：

主键索此 ( PRIMARY )

唯一索引 ( UNIQUE )

普通索引 ( INDEX )

全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）

2、索引并不是越多越好，要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 explain（解释/说明）来查看是否用了索引还是全表扫描

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

查看表中的索引字段

1、使用 show index from 表名可以查看整张表的索引（如下所示 id 为int类型的自增主键，name 为 varchar 类型，有唯一约束）：

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | name     |            1 | name        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2、当然也可以使用 show create table 表名，从表的创建信息中也可以看出来：

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

explain 显示查询SQL索引使用情况

1、"explain +查询语句" 可以查看索引的使用情况：

mysql> explain select * from user where id =1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

id：查询语句的序列号

select_type：表示查询类型

table：查询的表、结果集

type：连接类型。可选值有（性能从上到下依次降低。）：

system：系统表，表中只有一行数据。
const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
fulltext：进行全文索引检索。
ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据。
unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束。
index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
rang：索引范围扫描。
index：全索引扫描。
all：全表扫描。
possible_keys：可能用到的索引

key：使用的索引

rows：显示MySQL认为它执行查询时必须检查的行数。

Extra：额外的信息

SQL 索引失效情况

1、like 模糊查询时，like "%xxx" 索引失效、like "%xxx%" 索引失效、like "xxx%" 索引有效（如下所示 id 为int类型自增主键，name 为 varchar ，加唯一约束，age 为 int类型，无约束）：

mysql> select * from user where name like "%si";
+----+------+------+---------------------+
| id | name | age  | birthday            |
+----+------+------+---------------------+
|  2 | lisi |   25 | 2019-02-17 13:32:52 |
+----+------+------+---------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where name like "%si";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where name like "si%";
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | name          | name | 153     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where name like "%si%";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql>

2、字符串使用 != 时，索引失效（如下所示 id 为int类型自增主键，name 为 varchar ，加唯一约束，age 为 int类型，无约束）：

mysql> explain select * from user where name != "zhangsan";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | name          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where id != 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where age != 22;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
3、or 关键字导致索引失效（如下所示 id 为int类型自增主键，name 为 varchar ，加唯一约束，age 为 int类型 ，无约束）：

mysql> explain select * from user where name = "lisi" ;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | name          | name | 153     | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where name = "lisi" or id =1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | PRIMARY,name  | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from user where name = "lisi" or age = 25;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | name          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

查询 SQL

1、可通过开启慢查询日志来找出较慢的 SQL

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

3、sql 语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大 sql 可以堵死整个库

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

5、少用函数和触发器，在应用程序实现

6、避免 %xxx 式查询，会让索引失效，导致全表扫描

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

8、对于连续数值，使用 BETWEEN 不用 IN， SELECT id FROM t WHERE num BETWEEN 1 AND 5

9、列表数据不要拿全表，要使用 LIMIT来分页，每页数量也不要太大

10、排序尽量使用升序 ;

11、or 的查询尽量用 union 代替（Innodb）

12、复合索引高选择性的字段排在前面；

13、尽量规避大事务的 SQL，大事务的 SQL 会影响数据库的并发性能及主从同步；

14、删除表所有记录请用 truncate，不要用 delete；

15、Innodb上用 select count(*)，因为 Innodb 会存储统计信息；

16、只有一条查询结果时，推荐使用 “LIMIT 1”，“LIMIT 1” 可以避免全表扫描，找到对应结果就不会再继续扫描了。

17、为获得相同结果集的多次执行，保持 SQL 语句前后一致，这样做的目的是为了充分利用查询缓冲。

18、WHERE 子句、JOIN 子句里面的列尽量被索引，因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

19、ORDER BY 的列尽量被索引，ORDER BY的列如果被索引，性能也会更好。

存储引擎

1、mysql 5.1 中，引入了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的 mysql 服务器中。使用 mysql 插件式存储引擎体系结构，允许数据库专业人员或者设计库表的软件开发人员为特定的应用需求选择专门的存储引擎，完全不需要管理任何特殊的应用编码要求，也无需考虑所有的底层实施细节。

2、mysql 常用的存储引擎包括 MYISAM、Innodb 和 Memory，其中各自的特点如下：

myisam: 全表锁，拥有较高的执行速度，一个写请求请阻塞另外相同表格的所有读写请求，并发性能差，占用空间相对较小，mysql 5.5 及以下仅 MYISAM 支持全文索引，myisam 不支持事务，也不支持外键。

Innodb：行级锁（SQL 都走索引查询），并发能力相对强，占用空间是 MYISAM 的 2.5 倍，不支持全文索引（5.6 开始支持），支持事务与外键。常用！

Memory : 全表锁，存储在内存当中，速度快，但会占用和数据量成正比的内存空间且数据在 mysql 重启时会丢失。

3、基于以上特性，建议绝大部份都设置为 innodb 引擎，特殊的业务再考虑选用 MYISAM 或 Memory ，如全文索引支持或极高的执行效率等。

4、关于 Mysql 引擎可以参考《MySQL 存储引擎详解》

升级硬件

1、根据 MySQL 是 CPU 密集型还是I/O密集型，通过提升 CPU 和内存、使用 SSD，都能显著提升MySQL性能

读写分离

1、读写分离也是目前常用的优化方式，从库读主库写

2、一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

数据缓存

1、缓存可以发生在以下这些层次：

1）MySQL内部：做内部参数调优

2）数据访问层：比如 MyBatis 针对 SQL 语句做缓存，而 Hibernate 可以精确到单个记录，这里缓存的对象主要是持久化对象 PersistenceObject

3）应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象 DataTransferObject

4）Web层：针对web页面做缓存

5）浏览器客户端：用户端的缓存

2、可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

直写式（Write Through）：在数据写入数据库后，同时更新缓存(如 Redis)，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如 Spring Cache 的工作方式。这种实现非常简单，同步好，但效率一般。

回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

垂直分表

1、垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。

2、垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。

3、比如原始的用户表是：

4、垂直拆分后是：

优点

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

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

数据维护简单

缺点

主键出现冗余，需要管理冗余列

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

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

事务处理复杂

水平分表

1、水平拆分是通过某种策略将数据分片来存储，分为库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。

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

3、前面垂直拆分的用户表如果进行水平拆分，结果是：

4、实际情况中往往会是垂直拆分和水平拆分的结合，即将 Users_A_M和 Users_N_Z再拆成 Users和 UserExtras，这样一共四张表。

优点

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

应用端改造较少

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

缺点

分片事务一致性难以解决

跨节点 Join 性能差，逻辑复杂

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

安心撸码

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL大数据量优化

当 MySQL 单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化。字段类型优化1、除非单表数据未来会一直不断上涨(例如网络爬虫)，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度2、一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候 MySQL 单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据...
复制链接

扫一扫

专栏目录