MySQL分表优化实例 - Chinaxxren - ITeye博客
首页
资讯
精华
论坛
问答
博客
专栏
群组
下载
资源
搜索
您还未登录!
登录
jiangm520
浏览:
1278712 次
性别:
来自:
成都
最近访客
更多访客>>
pcdlrzxx
ringphone
304113037
zfl110
博主相关
博客
微博
相册
收藏
留言
关于我
文章分类
全部博客 (471)
原创文章 (4)
Database (84)
J2SE (63)
Web (26)
Javascript (30)
Lucene (11)
os (13)
算法 (8)
Webservice (1)
Open projects (18)
Hibernate (18)
Spring (15)
Css (2)
J2ee (2)
综合技术 (18)
安全管理 (13)
PatternsInJava (27)
NIO (5)
Ibatis (2)
书籍收藏 (1)
quartz (7)
并发编程 (15)
oracle问题 (2)
ios (60)
coco2d-iphone (3)
C++ (6)
Zookeeper (2)
golang (4)
animation (2)
android (1)
社区版块
我的资讯 (
0)
我的论坛 (
5)
我的问答 (
0)
存档分类
2013-08
1)
2013-02
1)
2012-12
13)
更多存档...
最新评论
dandingge123：
【引用】限制UITextField输入长度的方法
qja：
...
对List顺序，逆序，随机排列实例代码
安静听歌：
现在在搞这个，，，，，哎~头都大了，，，又freemarker ...
通用大型网站页面静态化解决方案（一）
springdata-jpa：
java quartz定时任务demo教程源代码下载，地址：h ...
Quartz 配置参考
马清天：
[b][/b][list][*]引用[u][/u][/list ...
通用大型网站页面静态化解决方案（一）
jiangm520
MySQL分表优化实例
博客分类： Database
MySQL
阅读更多
我们的项目中有好多不等于的情况。今天写这篇文章简单的分析一下怎么个优化法。这里的分表逻辑是根据t_group表的user_name组的个数来分的。因为这种情况单独user_name字段上的索引就属于烂索引。起不了啥名明显的效果。
1、试验PROCEDURE.DELIMITER $$DROP PROCEDURE `t_girl`.`sp_split_table`$$CREATE PROCEDURE `t_girl`.`sp_split_table`()BEGIN declare done int default 0; declare v_user_name varchar(20) default ''; declare v_table_name varchar(64) default ''; -- Get all users' name. declare cur1 cursor for select user_name from t_group group by user_name; -- Deal with error or warnings. declare continue handler for 1329 set done = 1; -- Open cursor. open cur1; while done <> 1 do fetch cur1 into v_user_name; if not done then -- Get table name. set v_table_name = concat('t_group_',v_user_name); -- Create new extra table. set @stmt = concat('create table ',v_table_name,' like t_group'); prepare s1 from @stmt; execute s1; drop prepare s1; -- Load data into it. set @stmt = concat('insert into ',v_table_name,' select * from t_group where user_name = ''',v_user_name,''''); prepare s1 from @stmt; execute s1; drop prepare s1; end if; end while; -- Close cursor. close cur1; -- Free variable from memory. set @stmt = NULL;END$$
DELIMITER ;
2、试验表。我们用一个有一千万条记录的表来做测试。
mysql> select count(*) from t_group;+----------+| count(*) |+----------+| 10388608 |+----------+1 row in set (0.00 sec)
表结构。mysql> desc t_group;+-------------+------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------+------+-----+-------------------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || money | decimal(10,2) | NO | | | || user_name | varchar(20) | NO | MUL | | || create_time | timestamp | NO | | CURRENT_TIMESTAMP | |+-------------+------------------+------+-----+-------------------+----------------+4 rows in set (0.00 sec)
索引情况。
mysql> show index from t_group;+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+|Table | Non_unique | Key_name | Seq_in_index | Column_name |Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+|t_group | 0 | PRIMARY | 1 | id |A | 10388608 | NULL | NULL | | BTREE | || t_group | 1 | idx_user_name | 1 | user_name | A | 8 | NULL | NULL | |BTREE | || t_group | 1 | idx_combination1| 1 | user_name | A | 8 | NULL |NULL | | BTREE | || t_group | 1 |idx_combination1 | 2 | money | A | 3776| NULL | NULL | | BTREE | |+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+4 rows in set (0.00 sec)
PS:idx_combination1 这个索引是必须的，因为要对user_name来GROUP BY。此时属于松散索引扫描！当然完了后你可以干掉她。idx_user_name 这个索引是为了加快单独执行constant这种类型的查询。我们要根据用户名来分表。
mysql> select user_name from t_group where 1 group by user_name;+-----------+| user_name |+-----------+| david || leo || livia || lucy || sarah || simon || sony || sunny |+-----------+8 rows in set (0.00 sec)
所以结果表应该是这样的。mysql> show tables like 't_group_%';+------------------------------+| Tables_in_t_girl (t_group_%) |+------------------------------+| t_group_david || t_group_leo || t_group_livia || t_group_lucy || t_group_sarah || t_group_simon || t_group_sony || t_group_sunny |+------------------------------+8 rows in set (0.00 sec)
3、对比结果。
mysql> select count(*) from t_group where user_name = 'david';+----------+| count(*) |+----------+| 1298576 |+----------+1 row in set (1.71 sec)
执行了将近2秒。
mysql> select count(*) from t_group_david;+----------+| count(*) |+----------+| 1298576 |+----------+1 row in set (0.00 sec)几乎是瞬间的。
mysql> select count(*) from t_group where user_name <> 'david';+----------+| count(*) |+----------+| 9090032 |+----------+1 row in set (9.26 sec)执行了将近10秒，可以想象，这个是实际的项目中是不能忍受的。mysql> select (select count(*) from t_group) - (select count(*) from t_group_david) as total;+---------+| total |+---------+| 9090032 |+---------+1 row in set (0.00 sec)几乎是瞬间的。
我们来看看聚集函数。对于原表的操作。
mysql> select min(money),max(money) from t_group where user_name = 'david';+------------+------------+| min(money) | max(money) |+------------+------------+| -6.41 | 500.59 |+------------+------------+1 row in set (0.00 sec)最小，最大值都是FULL INDEX SCAN。所以是瞬间的。mysql> select sum(money),avg(money) from t_group where user_name = 'david';+--------------+------------+| sum(money) | avg(money) |+--------------+------------+| 319992383.84 | 246.417910 |+--------------+------------+1 row in set (2.15 sec)其他聚集函数的结果就不是FULL INDEX SCAN了。耗时2.15秒。
对于小表的操作。mysql> select min(money),max(money) from t_group_david;+------------+------------+| min(money) | max(money) |+------------+------------+| -6.41 | 500.59 |+------------+------------+1 row in set (1.50 sec)最大最小值完全是FULL TABLE SCAN,耗时1.50秒，不划算。以此看来。mysql> select sum(money),avg(money) from t_group_david;+--------------+------------+| sum(money) | avg(money) |+--------------+------------+| 319992383.84 | 246.417910 |+--------------+------------+1 row in set (1.68 sec)
取得这两个结果也是花了快2秒，快了一点。
我们来看看这个小表的结构。mysql> desc t_group_david;+-------------+------------------+------+-----+-------------------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------+------+-----+-------------------+----------------+| id | int(10) unsigned | NO | PRI | NULL | auto_increment || money | decimal(10,2) | NO | | | || user_name | varchar(20) | NO | MUL | | || create_time | timestamp | NO | | CURRENT_TIMESTAMP | |+-------------+------------------+------+-----+-------------------+----------------+4 rows in set (0.00 sec)
明显的user_name属性是多余的。那么就干掉它。mysql> alter table t_group_david drop user_name;Query OK, 1298576 rows affected (7.58 sec)Records: 1298576 Duplicates: 0 Warnings: 0
现在来重新对小表运行查询
mysql> select min(money),max(money) from t_group_david;+------------+------------+| min(money) | max(money) |+------------+------------+| -6.41 | 500.59 |+------------+------------+1 row in set (0.00 sec)
此时是瞬间的。mysql> select sum(money),avg(money) from t_group_david;+--------------+------------+| sum(money) | avg(money) |+--------------+------------+| 319992383.84 | 246.417910 |+--------------+------------+1 row in set (0.94 sec)
这次算是控制在一秒以内了。
mysql> Aborted
小总结一下：分出的小表的属性尽量越少越好。大胆的去干吧。
分享到：
Mysql limit优化，百万至千万级快速分页， ...
定时每天备份mysql并定时删除上月记录（脚 ...
2010-10-25 22:17
浏览 2374
评论(0)
分类:数据库
查看更多
评论
发表评论
您还没有登录,请您登录后再发表评论
相关推荐
mysql 分表
关于mysql的分表技术实现方法。以及分表设计， 及简单的代码实例。
php mysql分库分表实例
php分库分表
MySQL数据库优化之分表分库操作实例详解
主要介绍了MySQL数据库优化之分表分库操作,结合实例形式详细分析了mysql数据库分表分库垂直拆分、水平拆分相关原理以及应用案例,需要的朋友可以参考下
shards-jdbc分库分表实例
shards-jdbc分库分表实例 dataSources: ds0: !!org.apache.commons.dbcp.BasicDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ds0 username: root password: ...
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
│ │ 5_MySQL索引优化原则.mp4 │ │ 6_MySQL运维常见错误part1.mp4 │ │ 7_MySQL运维常见错误part2.mp4 │ │ ERROR1040_1917970.1.pdf │ │ ERROR1062_1593526.1.pdf │ │ ERROR1205_1911871.1.pdf │ │ ...
MyCat测试报告(单机MySQL与通过MyCat分表后的对比情况)1
测试方法：简单分表测试，16张表分别放在单台MySQL对比两台机器四个实例每个实例4张表的MySQL（使用mycat作为中间件），3600秒的时间分别在16、
分库分表demo项目.zip
通过springboot+shardingsphere+mybatis-plus+mysql 模块进行整合的demo。
MySQL数据库实战例子（存储引擎、视图、锁机制、分表）
本例子主要以存储引擎、视图、存储函数、锁机制、分表为主，并且配以对应的博客全面解析。 这个例子的博客系列在这里：http://blog.csdn.net/Jack__Frost/article/category/6998564
MySQL运维与管理相关文档.rar
资源来自互联网，包括 1、MySQL 5.7 多实例单配置 & 多实例多配置文件安装 2、MySQL分区实战以及分区的管理 3、MySQL 数据库基本命令汇总整理，需要的赶紧学起来
MYSQL数据库表结构优化方法详解
本文实例讲述了MYSQL数据库表结构优化方法。分享给大家供大家参考，具体如下： 选择合适的数据类型 1、使用可以存下你的数据的最小的数据类型 2、使用简单的数据类型。Int要比varchar类型在mysql处理上简单 ...
通过实例学习MySQL分区表原理及常用操作
我们试着想一想, 在生产环境中什么最重要？ 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？只要看完这篇大家应该就能对MySQL中数据备份有一定了解
mysql分区功能详解,以及实例分析
前段时间写过一篇关于mysql分表的 的文章，下面来说一下什么是数据库分区，以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面 （可以通过my.cnf中的datadir来查看），一张表...
Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能
主要介绍了Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
实例分析python3实现并发访问水平切分表
假设有一个mysql表被水平切分，分散到多个host中，每个host拥有n个切分表。 如果需要并发去访问这些表，快速得到查询结果， 应该怎么做呢？ 这里提供一种方案，利用python3的asyncio异步io库及aiomysql异步库去...
PHP框架Laravel中使用UUID实现数据分表操作示例
本文实例讲述了PHP框架Laravel中使用UUID实现数据分表操作。分享给大家供大家参考，具体如下： UUID UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。 说的简单点，它就是通过一个规则...
MySQL复制(四)—多源(主)复制
通常用于数据仓库整合数据，比如OLTP系统为了分散业务压力，对业务进行分库分表，当要对数据进行分析的时候，可以使用多主复制将数据整合到同一个数据库实例上，便于统一分析。MySQL从5.7版本开始支持多主复制。 ...
incepiton-mysql：专为mysql初始化而设计的Web平台
Incepiton MySQL :red_apple: 专为MySQL Inception设计...支持分表操作 自动审核 发起SQL上线，由自动审核，自动审核成功后，提交至Audit。 审核人操作 SQL执行进度实时获取 只有多的表走pt-osc，可以通过Inception来
TiDB-DM架构设计与实现原理
特别地，对于有多个MySQL/MariaDB实例的分库分表需要合并后同步到同一个TiDB集群的场景，DM提供了良好的支持。DM是集群模式的，其主要由DM-master、DM-worker与DM-ctl三个组件组成，能够以多对多的方式将多个上游...
在OneProxy的基础上实行MySQL读写分离与负载均衡
OneProxy平民软件完全自主开发的分布式数据访问层，帮助用户在MySQL/PostgreSQL集群上快速搭建支持分库分表的分布式数据库中间件，也是一款具有SQL白名单（防SQL注入）及IP白名单功能的SQL防火墙软件。采用与MySQL...
mysql主从热备配置(含innodb)终极版
2012-12-25 13:10
2481
转自 http://blogread.cn/it/articl ...
sqlite3中的数据类型
2012-12-10 21:37
1134
(转)http://www.cnblogs.com/kfqco ...
Mac下MySql卸载方法
2012-09-10 23:57
944
Mac下MySql卸载方法
mac下mysql的D ...
mac os x mysql数据库utf8配置
2012-09-10 23:29
2120
进入mysql console:
输入
mysql& ...
ON DUPLICATE KEY UPDATE
2012-08-07 01:47
967
(转自)http://blog.csdn.net/kesaih ...
MySQL单列索引和组合索引的区别介绍
2012-08-07 01:31
983
（转自）http://blog.cs ...
centos下MySQL主从同步配置
2012-08-03 13:14
1447
（转自）http://apps.hi.baidu.com ...
sql语句中left join、inner join中的on与where的区别
2012-06-13 13:24
1146
table a(id, type):
id t ...
分组取前N记录
2012-05-31 16:24
1038
（转）http://blog.csdn.net/ac ...
如何一条sql语句取出分组数据中值最大的
2012-05-30 23:59
2123
--按某一字段分组取最大(小)值所在行的数据(2007-10- ...
存储过程与函数的区别
2011-09-28 19:35
1047
...
RMAN备份方案
2011-07-21 10:29
1947
RMAN备份方案 RMAN也可以进行联机备份，而且备份与恢 ...
oracle
2011-04-06 17:46
963
学习笔记
PLSQL Developer + Oracle客户端简易安装方法
2011-03-17 00:10
14179
Oracle 10g绿色客户端 pl ...
SELECT INTO FROM与INSERT INTO SELECT
2011-01-26 15:52
3298
1.INSERT INTO SELECT语句
...
浅谈unique列上插入重复值的MySQL解决方案
2011-01-25 11:33
1680
本文的unique列上插入重复值解决方案，主要基于MySQL平 ...
:Oracle 中对Merge语句的增强 和去重复新增
2011-01-25 11:11
2173
在Oracle 10g之前，merge语句支持匹配更新和不匹配 ...
单行函数（仅对单个表达式或一行进行处理）
2011-01-23 18:49
1155
例如：select sum(qty) from sales
...
oracle中的事务控制命令
2011-01-23 18:45
1939
事务提交有显示提交：使用commit命令来提交所有未提交的更新 ...
oracle全文检索
2011-01-23 18:18
6041
1.全文检索和普通检索的区别
不使用Oracle text功 ...
Global site tag (gtag.js) - Google Analytics