数据库优化:mysql数据库单机数十亿数据查询设计

标签: 编程开发 mysql优化 | 发表时间:2014-06-30 16:13 | 作者:seay
出处:http://www.cnseay.com

    很久没写文章,是不是想着写点什么东西,分享下我的数据库设计思路,主要是针对单机数十亿及以上数据查询优化技巧。


如果只是简单的查询,没有频繁的写入操作,对查询速度不要求在毫秒级别,就不需要什么大型的数据库软件设计复杂的集群关系,也不需要分布式水平分割等太重的优化。

只需要用mysql在本机笔记本搭建一个普通的环境就行。

   那么首先是针对mysql做一些普通常见的优化,比如分表分区、建索引、表字段设计以及mysql的配置优化,比如缓冲区大小等等,这类配置我找了一个文章,详细的可以看 http://www.cnblogs.com/Bozh/archive/2013/01/22/2871545.html
。其实mysql水平分表也是数据水平分割的做法。只需要在入库时针对不同的数据库入到不同的表即可,对于比较大的单个库,比如上两亿的单库,这时候就可以把它进行分表放到两个或者三个表,我的做法是单表不超过一亿。


除了这些优化,我优化的核心设计思想是在建立索引和查询代码上面。


很多人在设计社工库的时候,都是把所有要查询的字段都建立索引,而对于数据库来说,查询的数据库数据量越小,那么查询速度越快,另外索引也比较占空间,所以我们在索引上面做做动作,可以节省大量硬盘空间和优化查询速度。


我的做法是只查可能有我想要的数据的表,肯定没有我想要的数据表直接不查询,我不需要查询的字段就不需要建索引,那这样就可以实现一来我查询的量小了,而来少给很多字段建立索引。


举个简单的例子,比如你现有的数据库info中两个表:


1A表和B表。AB表字段一样。AB表都有明文password字段不为空。
2.A表username字段不为空,B表username字段为空。

3.B表email字段不为空,A表email字段为空。


那在这种情况下,就可以针对这两个表做如下的设计:
1.A表和B表password字段都建立索引。
2.A表username字段建立索引,B表username字段不建立索引且默认为空。

3.B表email字段建立索引,A表email字段不建立索引且默认为空。

OK,那么表设计就完成了。

那么我们就可以利用这种表设计减少我们查询的量来优化查询速度。

对于有索引的字段,在mysql数据库information_schema的statistics表里面保存了所有表的索引信息,那么我们就可以利用这个表来过滤掉我们要查询的字段没有建立索引的表。

比如我要查询info库里面邮箱为root@cnseay.com的信息。那么这个查询中我会丢掉A表,因为A表email字段为空,肯定没有我要查询的root@cnseay.com信息。

那么如何过滤掉A表。一条SQL语句即可:


SELECT TABLE_NAME FROM information_schema.statistics WHERE INDEX_NAME!=’PRIMARY’ and table_schema = ‘info’ and COLUMN_NAME=’email’ GROUP BY TABLE_NAME;


利用这条语句,就可以输出info库email字段存在索引的表。然后利用脚本动态拼接union查询语句查询即可。

我的环境是USB3.0 2TB移动硬盘,笔记本win7 i7-4700,8G内存。20亿数据查询速度在1~3秒。

博主猜你喜欢:

数据优化:解决mysql in 子查询速度慢 优化查询效率

php注入load_file读数据库配置文件

MySql得到webshell或提权

BT5 Metasploit(MSF)连接postgersql数据库

Python学习:python操作MySQL数据库
无觅

相关 [数据库 优化 mysql] 推荐:

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.

MySQL数据库优化实践

- - OurMySQL
   最近一段时间,我们整理了一些关于Percona,Linux,Flashcache,硬件设备的优化经验,分享给大家:.     1.开启BBWC.    RAID卡都有写cache(Battery Backed Write Cache),写cache对IO性能的提升非常明显,因为掉电会丢失数据,所以必须由电池提供支持.

MySQL 数据库性能优化之缓存参数优化

- flychen50 - Sky.Jian 朝阳的天空
在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 这是 MySQL数据库性能优化专题 系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化.

MySQL 数据库性能优化之SQL优化

- - OurMySQL
注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础. IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是. SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段.

MySQL数据库性能优化之表结构优化

- - haohtml's blog
由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了. 反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能.

MySQL优化之数据库结构:数据对象优化

- - CSDN博客数据库推荐文章
使用PROCEDURE ANALYSE函数优化表的数据类型. 表需要使用何种数据类型,是需要根据应用来判断的. 在MySQL中,可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析,该函数可以对数据库中列的数据类型提出优化建议,用户可以根据应用的实际情况斟酌考虑是否实施优化. 以下是函数PROCEDURE ANALYSE()的使用方法:.

mysql数据库性能优化的关键参数及mysql服务器优化

- - CSDN博客数据库推荐文章
MySQL数据库性能优化的关键参数. 关键参数一: back_log. 要求 MySQL 能有的连接数量. 当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程. back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中.

数据库优化:mysql数据库单机数十亿数据查询设计

- - Seay's blog 网络安全博客
    很久没写文章,是不是想着写点什么东西,分享下我的数据库设计思路,主要是针对单机数十亿及以上数据查询优化技巧. 如果只是简单的查询,没有频繁的写入操作,对查询速度不要求在毫秒级别,就不需要什么大型的数据库软件设计复杂的集群关系,也不需要分布式水平分割等太重的优化. 只需要用mysql在本机笔记本搭建一个普通的环境就行.

MySQL 数据库性能优化之表结构

- tangfl - Sky.Jian 朝阳的天空
接着上一篇 MySQL 数据库性能优化之缓存参数优化 ,这是 MySQL数据库性能优化专题 系列的第二篇文章:MySQL 数据库性能优化之表结构. 很多人都将 数据库设计范式 作为数据库表结构设计“圣经”,认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求.