MySQL千万级数据的大表优化解决方案

目录
  • 1.数据库设计和表创建时就要考虑性能
    • 设计表时要注意:
    • 索引
    • 简言之就是使用合适的数据类型,选择合适的索引
    • 引擎
  • 2.sql的编写需要注意优化
  • 3.分区
    • 分区的好处是:
    • 分区的限制和缺点:
    • 分区的类型:
  • 4.分表
  • 5.分库

mysql数据库中的表数据量几千万后,查询速度会很慢,日常各种卡慢,严重影响使用体验。在考虑升级数据库或者换用大数据解决方案前,必须优化现有mysql数据库表设计和sql语句。

1.数据库设计和表创建时就要考虑性能

mysql数据库本身高度灵活,造成性能不足,严重依赖开发人员能力。也就是说开发人员能力高,则mysql性能高。这也是很多关系型数据库的通病。

设计表时要注意:

表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。

尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。

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

尽量使用TIMESTAMP而非DATETIME

单表不要有太多字段,建议在20以内

用整型来存IP

索引

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

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

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

字符字段只建前缀索引

字符字段最好不要做主键

不用外键,由程序保证约束

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

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

简言之就是使用合适的数据类型,选择合适的索引

选择合适的数据类型

(1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob

(2)使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数

(3)使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar

(4)尽可能使用not null定义字段

(5)尽量少用text,非用不可最好分表

选择合适的索引列

(1)查询频繁的列,在where,group by,order by,on从句中出现的列

(2)where条件中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列

(3)长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好

(4)离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count越大,离散程度越高:

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎:

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:

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

不支持事务

不支持外键

不支持崩溃后的安全恢复

在表有读取查询的同时,支持往表中插入新纪录

支持BLOB和TEXT的前500个字符索引,支持全文索引

支持延迟更新索引,极大提升写入性能

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

InnoDB

InnoDB在MySQL 5.5后成为默认索引,它的特点是

支持行锁,采用MVCC来支持高并发

支持事务

支持外键

支持崩溃后的安全恢复

不支持全文索引

总体来讲,MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表。

2.sql的编写需要注意优化

使用limit对查询结果的记录进行限定

避免select *,将需要查找的字段列出来

使用连接(join)来代替子查询

拆分大的delete或insert语句

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

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

sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库

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

不用函数和触发器,在应用程序实现

避免%xxx式查询

少用JOIN

使用同类型进行比较,比如用'123'和'123'比,123和123比

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

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

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

3.分区

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

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

用户的SQL语句是需要针对分区表做优化,SQL条件中要带上分区条件的列,从而使查询定位到少量的分区上,否则就会扫描全部分区,可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上,从而进行SQL优化,我测试,查询时不带分区条件的列,也会提高速度,故该措施值得一试。

分区的好处是:

可以让单表存储更多的数据

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

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

分区表的数据还可以分布在不同的物理设备上,从而搞笑利用多个硬件设备

可以使用分区表赖避免某些特殊瓶颈,例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争

可以备份和恢复单个分区

分区的限制和缺点:

一个表最多只能有1024个分区

如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

NULL值会使分区过滤无效

所有分区必须使用相同的存储引擎

分区的类型:

我首先根据月份把上网记录表RANGE分区了12份,查询效率提高6倍左右,效果不明显,故:换id为HASH分区,分了64个分区,查询速度提升显著。问题解决!

结果如下:PARTITION BY HASH (id)PARTITIONS 64

select count() from readroom_website; --11901336行记录

/ 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. /

select * from readroom_website where month(accesstime) =11 limit 10;

/ 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */

4.分表

分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次查询分成多次查询,然后把结果组合返回给用户。

分表分为垂直拆分和水平拆分,通常以某个字段做拆分项。比如以id字段拆分为100张表: 表名为 tableName_id%100

但:分表需要修改源程序代码,会给开发带来大量工作,极大的增加了开发成本,故:只适合在开发初期就考虑到了大量数据存在,做好了分表处理,不适合应用上线了再做修改,成本太高!!!

5.分库

把一个数据库分成多个,建议做个读写分离就行了,真正的做分库也会带来大量的开发成本,得不偿失!不推荐使用。

到此这篇关于MySQL千万级数据的大表优化解决方案的文章就介绍到这了,更多相关MySQL大表优化方案内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2022-11-17

MySQL性能优化技巧分享

MySQL性能优化 在互联网公司MySQL的使用非常广泛,大家经常会有MySQL性能优化方面的需求.整理了一些在MySQL优化方面的实用技巧. Schema与数据类型优化 整数通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT 完全"随机"的字符串(如:MD5().SHA1()或者UUID()等产生的字符串)会任意分布在很大的空间内,会导致INSERT以及一些SELECT语句变的很慢 如果希望查询执行得快速且并发性好,单个查询最好不要做太多的关联查询(互联网公

MYSQL数据库表结构优化方法详解

本文实例讲述了MYSQL数据库表结构优化方法.分享给大家供大家参考,具体如下: 选择合适的数据类型 1.使用可以存下你的数据的最小的数据类型 2.使用简单的数据类型.Int要比varchar类型在mysql处理上简单 3.尽可能的使用not null定义字段 4.尽量少用text类型,非用不可时最好考虑分表 使用int来存储日期时间,利用FROM_UNIXTIME()[将int类型时间戳转换成日期时间格式],UNIX_TIMESTAMP()[将日期时间格式转换成int类型]两个函数进行转换 使用

MySQL优化常用的19种有效方法(推荐!)

目录 1.EXPLAIN 2.SQL语句中IN包含的值不应过多 3.SELECT语句务必指明字段名称 4.当只需要一条数据的时候,使用limit1 5.如果排序字段没有用到索引,就尽量少排序 6.如果限制条件中其他字段没有索引,尽量少用or 7.尽量用unionall代替union 8.不使用ORDERBYRAND() 9.区分in和exists.notin和notexists 10.使用合理的分页方式以提高分页的效率 11.分段查询 12.避免在where子句中对字段进行null值判断 13.

9种 MySQL数据库优化的技巧

目录 1.选择最合适的字段属性 2.尽量把字段设置为NOT NULL 3.使用连接(JOIN)来代替子查询(Sub-Queries) 4.使用联合(UNION)来代替手动创建的临时表 5.事务 6.使用外键 7.锁定表 8.使用索引 9.优化de的查询语句 9.1 不使用子查询 9.2 避免函数索引 9.3 用IN来替换OR 9.4 LIKE双百分号无法使用到索引 9.5 读取适当的记录LIMIT M,N 9.6 避免数据类型不一致 9.7 分组统计可以禁止排序 9.8 避免随机取记录 9.9

mysql大数据查询优化经验分享(推荐)

正儿八经mysql优化! mysql数据量少,优化没必要,数据量大,优化少不了,不优化一个查询10秒,优化得当,同样查询10毫秒. 这是多么痛的领悟! mysql优化,说程序员的话就是:索引优化和where条件优化. 实验环境:MacBook Pro MJLQ2CH/A,mysql5.7,数据量:212万+ ONE: select * from article INNER JOIN ( SELECT id FROM article WHERE length(content_url) > 0 an

mysql千万级数据大表该如何优化?

1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节: 2.数据项:是否有大字段,那些字段的值是否经常被更新: 3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE.GROUP BY.ORDER BY子句中等: 4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中: 5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少? 6.预计大表及相关联的SQL,每天总的执行量在何数量级? 7.表中的数据:更新为主的

MySQL千万级数据表的优化实战记录

前言 这里先说明一下,网上很多人说阿里规定500w数据就要分库分表.实际上,这个500w并不是定义死的,而是与MySQL的配置以及机器的硬件有关.MySQL为了提升性能,会将表的索引装载到内存中.但是当表的数据到达一定的量的时候,会导致内存无法存储这些索引,无法存储索引,就只能进行磁盘IO,从而导致性能下降. 实战调优 我这里有张表,数据有1000w,目前只有一个主键索引 CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `u

MySQL 千万级数据量如何快速分页

前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned NOT NULL AUTO_I

MySQL百万级数据大分页查询优化的实现

目录 一.MySQL分页起点越大查询速度越慢 二. limit大分页问题的性能优化方法 (1)利用表的覆盖索引来加速分页查询 (2)用上次分页的最大id优化 三.MySQL百万数据快速生成 3.1.创建内存表及普通表 3.2.创建函数 3.3.创建插入内存表数据的存储过程 3.4.创建内存表数据插入普通表的存储过程 3.5.运行存储过程插入数据 参考链接: 前言:在数据库开发过程中我们经常会使用分页,核心技术是使用用limit start, count分页语句进行数据的读取. 一.MySQL分页

mysql千万级数据量根据索引优化查询速度的实现

(一)索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了. 能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低.代价小且行之有效的解决方法就是合理的加索引.索引使用得当,能使查询速度提升上千倍,效果惊人. (二)mysql的索引类型: mysql的索引有5种:主键索引.普通索引.唯一索引.全文索引.聚合索引(多列索引).

MySQL 大表的count()优化实现

以下是基于我结合B+树的数据结构和对实验结果的推测作出的判断,如有错误,恳请指正! 今天实验了一下MySQL的count()操作优化, 以下讨论基于mysql5.7 InnoDB存储引擎. x86 windows操作系统. 创建的表的结构如下(数据量为100万): 首先是关于mysql的count(*),count(PK), count(1)哪个快的问题. 实现结果如下: 并没有什么区别!加上了WHERE子句之后3个查询的时间也是相同的,我就不贴图片了. 之前在公司的时候就写过一个select

如何批量生成MySQL不重复手机号大表实例代码

前言 在MySQL很多测试场景,需要人工生成一些测试数据来测试.本文提供一个构造MySQL大表存储过程,可以生成包含用户名,手机号码,出生日期等字段.也可以通过滤重来使得手机号码不重复,模拟现实场景. 一.生成脚本 生成说明: 以下使用存储过程批量生成包含用户名,手机号,出生日期等字段大表. 该存储过程使用基于uid作为主键,因此会生成少量重复手机号码,后面有滤重SQL脚本. 如果想一次性生成不重复手机号码,可以考虑修改以下脚本,去掉uid,基于mobile列作为主键 DROP TABLE IF

mysql千万级数据分页查询性能优化

mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

详解MySQL数据库千万级数据查询和存储

百万级数据处理方案 数据存储结构设计 表字段设计 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0. 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1.因为这样可以加上 UNSIGNED,数值容量就会扩大一倍. 可以的话用 TINYINT.SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小. 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实

MySQL深度分页(千万级数据量如何快速分页)

目录 前言 案例 优化 小结 前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned