mysql千万数据如何优化(MySQL千万级数据的表如何优化)
mysql千万数据如何优化
MySQL千万级数据的表如何优化MySQL为了提升性能,会将表的索引装载到内存中。但是当表的数据到达一定的量的时候,会导致内存无法存储这些索引,无法存储索引,就只能进行磁盘IO,从而导致性能下降。
实战调优
我这里有张表,数据有1000w,目前只有一个主键索引
- CREATE TABLE `user` (
- `id` int(10) NOT NULL AUTO_INCREMENT,
- `uname` varchar(20) DEFAULT NULL COMMENT '账号',
- `pwd` varchar(20) DEFAULT NULL COMMENT '密码',
- `addr` varchar(80) DEFAULT NULL COMMENT '地址',
- `tel` varchar(20) DEFAULT NULL COMMENT '电话',
- `regtime` char(30) DEFAULT NULL COMMENT '注册时间',
- `age` int(11) DEFAULT NULL COMMENT '年龄',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;
查询所有大概16s。可谓是相当慢了。通常我们一个后台系统,比如这个是一个电商平台,这个是用户表。后台管理系统,一般会查询这些用户信息,做一些操作,比如后台直接新增用户啊,或者删除用户啊这些操作。
所以这里就诞生了两个需求,一个是查询count,一个是分页查询
我们分别来测试一下count用的时间和分页查询所用的时间
- select * from user limit 1, 10 //几乎不用时
- select * from user limit 1000000, 10 //0.35s
- select * from user limit 5000000, 10 //1.7s
- select * from user limit 9000000, 10 //2.8s
- select count(1) from user //1.7s
从上面查询所用时间可以看出来,如果是分页查询的话,查询的数据越往后用时是越长的,查询count也需要1.7s。这显然是不符合我们的要求的。所以,这里我们就需要优化。首先我们在这里进行索引优化试试
首先看一下这是只有主键索引的执行计划:
- alter table `user` add INDEX `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)
看上面的执行计划,虽然type是从all->index,走了sindex索引,但是实际上查询速度并没有发生改变。
其实,创建联合索引,是为了有条件查询的时候速度更快,而不是全表查询
- select * from user where uname='6.445329111484186' //3.5s(无联合索引)
- select * from user where uname='6.445329111484186' //0.003s(有联合索引)
所以这就是有联合索引和无索引的差距
这里基本上可以证明,加了索引和不加索引,进行全表查询的时候,效率就是会很慢
既然索引这个结果已经不好使了,那就只能找其他方案了。根据我之前mysql面试里面讲的,count我们可以单独存储到一个表里面
- CREATE TABLE `attribute` (
- `id` int(11) NOT NULL,
- `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '表名',
- `formcount` int(11) NOT NULL COMMENT '表总数据',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
这里说一下,这种表一般不会查所有,只会查询一条,所以建表的时候,可以建成hash
- select formcount from attribute where formname='user' //几乎不用时
count就进行优化完了。如果上面有选择条件的话,就可以建立索引,通过走索引筛选的形式来查询,这样就可以不用读这个count了。
那么,count是没问题了,分页查询优化要如何优化呢?这里可以使用子查询来优化
- select * from user where
- id>=(select id from user limit 9000000,1) limit 10 //1.7s
其实子查询这种写法,判断id,其实就是通过覆盖索引来查询。效率会大大增加。不过我这里测试是1.7s,以前在公司优化这方面的时候,比这个查询时间要低,大家也可以自己生成数据自己测试
但是如果说数据量太大了,我还是建议走es或者进行一些默认选择,count可以单独列出来
至此,一个千万级的数据分页查询的优化就完成了。
原文链接:https://juejin.cn/post/6989211911384481800
- mysql剩余表空间大小(MySQL 表空间碎片的概念及相关问题解决)
- mysql流式查询(MySQL全面瓦解之查询的正则匹配详解)
- mysql中group_concat
- mysql中修改表的字段名(MySQL 使用SQL语句修改表名的实现)
- mysqlupdate语句用法(MySQL update set 和 and的区别)
- mysql数据备份的几种方式(MySQL数据库备份过程的注意事项)
- mysql索引的机制(Mysql索引选择以及优化详解)
- navicat15激活页面不显示(Navicat for MySQL 15注册激活详细教程)
- mysql新增字段语句(关于Mysql update修改多个字段and的语法问题详析)
- mysql怎么和sqlyog连接(Mysql桌面工具之SQLyog资源及激活使用方法告别黑白命令行)
- mysql几种连接方式(简单谈谈mysql左连接内连接)
- docker部署mysql并且自动启动(Docker 部署 Mysql8.0的方法示例)
- mysql索引原理及调优(mysql高级学习之索引的优劣势及规则使用)
- 创建数据库入门教程mysql(MySQL数据库安装教程一学就会)
- laravel mysql 操作方式(Laravel使用原生sql语句并调用的方法)
- 了解mysql数据自动备份(MySQL数据备份方法的选择与思考)
- 贾怀胤唱《白龙马》 炸场 了 没想到京剧还能这么玩(贾怀胤唱白龙马)
- 白龙马的改编学生版,快来看看(白龙马的改编学生版)
- 萌娃唱《白龙马》走红,那生动的小表情,网友直呼 简直是戏精(萌娃唱白龙马走红)
- 朱鹤松被不断认可,凤凰传奇玲花喊话岳云鹏,索要老朱演出门票(朱鹤松被不断认可)
- 元宵晚会槽点多,芒果台上来就假唱,岳云鹏不说相声改评书了(元宵晚会槽点多)
- 岳云鹏跟凤凰传奇谈心,说出了人生中最重要的三个人,这才成功(岳云鹏跟凤凰传奇谈心)
热门推荐
- tomcat集群如何实现线程安全(如何通过LambdaProbe实现监控Tomcat)
- css中的浮动和定位是啥(CSS的position定位和float浮动详解)
- google 调试vue(Vue实现Google第三方登录的示例代码)
- python使用django搭建简单网页(Python后台开发Django的教程详解启动)
- mysql如何定义外键(MySQL外键设置的方法实例)
- mysql 高级用法(MySQL实现replace函数的几种实用场景)
- navicat配置远程访问mysql(解决Navicat无法连接 VMware中Centos系统中的 MySQL服务器的问题)
- 怎么进入云服务器(云服务器端口怎么打开?)
- JavaScript instanceof 的用法
- django用户权限管理(Django 内置权限扩展案例详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9