如何设计一个能够高效查询的千万级MySQL数据库

首先要确定你的目标，所谓千万级是每秒千万次查询还是千万条记录的数据库，前者是一个极其复杂的，这个不是光告mysql能解决的，我想不是前者，而后者却是很简单的一件事，前提是定义高效，定义两个指标：

1，每秒查询的次数是多少

2，每次查询时长

确定好以后再考虑以下几个因素的优化

1，存储的类型，SSD比普通磁盘的随机读写能力可以提高不少，一般2到3个数量级，还要看索引和数据块的大小，比较复杂

2，先择RAID类型，如果选raid0和raid10可以提升近似1倍的速度

3，使用高带宽的网速，可以减少网络传输延迟，用10g的光纤比1g的电缆理论上可以提升1个数量级的吞吐量，尤其对大数据据量的结果集特别有效

4，合理的索引，带条件的检索字段加上索引

5，用大宽表，尽可能减少多表关联查询，用空间换时间吧

6，_用主从的集群，基本上查询的并发量和服务器的数量成正比的

7，使用缓存，如memcached，尤其对静态数据提升尤其明显

8，合理选择数据库字段的类型，用定长字字，不要用变长的，如定长的int,char,decimal类型，别用varchar,text等

9，给数据库配置更大的内存

10，检查下瓶颈在不在CPU，如果查询复杂，换个更高配置的服务器

总的原刚就是，尽可能用内存替代碰盘提升IO速度，提高网络和CPU的配置以减少查询时间；尽可能提升网络速度，内存和主机的数量以提高并发

我们先探讨非高并发量的实现。

对于查询频次较高的字段，加上索引。

加索引注意事项：

1对那些字符内容较长的最好不要加索引

2按照官方文档，单表加的索引不要超过16个，索引的长度不要超过256个字节。

随意加索引，会给数据维护增加负担

其实，可以引入分区。

分区注意事项：

1常见的分区类型有range，list，hash，key等。用的比较多的就是range分区。

2对于初始建立索引的时候，我们往往会忽视一个前提条件，导致添加失败报错。

这里的前提是，如果表是有主键的，分区的键和主键不是同一个，那么分区的键也必须是主键。

引入分区后，数据写入时，数据库会自动判断写入哪个分区

对于并发量较高的，我们除了做上面的 *** 作外，就要考虑分库分表或者采用一主多从的方式。

未来我相信这类问题需要采用NewSQl这类数据库来解决，如TiDb等，此时，我们将不必考虑数据分区的问题，而且可以做到数据水平无限扩展，和热点数据的动态分布。

oracle的逻辑结构包括表空间(tablespace)，段(segment),区（extent）,数据块(data block)\x0d\oracle数据库在逻辑上是由多个表间组成的，表空间中存储的对象叫段，比如数据段，索引段，和回退段。段由区组成，区是磁盘分配的最小单位。段的增大是通过增加区的个数来实现的。每个区的大小是数据块大小的整数倍，区的大小可以不相同；数据块是数据库中最小的I/O单位，同时也是内存数据缓冲区的单位，及数据文件存储空间单位。块的大小由参数DB_BLOCK_SIZE设置，其值应设置为 *** 作系统块大小的整数倍。\x0d\表空间\x0d\表空间是Oracle数据库最大的逻辑结构，一个Oracle数据库在逻辑上由多个表空间组成，一个表空间只隶属于一个数据库。Oracle中有一个称为SYSTEM的表空间，这个表空间是在创建或安装数据库时自动创建的。主要用于存储系统的数据字典，过程，函数，触发器等；也可以存储用户的表，索引等。一个表空间可以有多数据文件，但是一个数据文件只能属于一个表空间。\x0d\一个表空间就是一片磁盘区域,他由一个或者多个磁盘文件组成,一个表空间可以容纳许多表、索引或者簇等。每个表空间有一个预制的磁盘区域称为初始区间（initial extent）用完这个区间后再用下一个，直到用完表空间，这时候需要对表空间进行扩展，增加数据文件或者扩大已经存在的数据文件\x0d\段\x0d\Oracle中的段可以分成4种类型：数据段、索引段、回滚段、临时段。\x0d\数据段用来存储用户的数据，每个表都有一个对应的回滚段，其名称和数据表的名字相同。索引段用来存储系统、用户的索引信息。回滚段用来存储用户数据修改前的值，回退段与事务是一对多的关系，一个事务只能使用一个回退段，而一个回退段可存放一个或多个事务的回退数据。临时段用于order by语句的排序以及一些汇总。\x0d\区\x0d\区是磁盘空间分配的最小单位。磁盘按区划分，每次至少分配一个区。区存储于段中，它由连续的数据块组成。区的分配过程中，每次至分配5个区。如果所剩的空闲空间不够5个区，就会出现错误：ORA-01653。可以通过字典dba_tablespaces查询表空间中区的信息。可以通过字典user_tables查询段中区的信息。可以通过字典user_extents查询区的分配状况。我们可以通过以下SQL语句分别查询表空间、段、区中区的分配信息\x0d\SQL>select from dba_tablespaces;\x0d\SQL>select table_name, tablespace_name, min_extents, max_extents from user_tables; \x0d\SQL>select from user_extents;\x0d\数据块\x0d\数据块是数据中中最小的数据组织单位与管理单位，是数据文件磁盘存储空间单位，也是数据库I/O 的最小单位，数据块大小由DB_BLOCK_SIZE参数决定，不同的oracle版本DB_BLOCK_SIZE的默认值是不同的。

package mavendemotest;

import javautilArrayList;

import javautilList;

import commongodbBasicDBObject;

import commongodbDB;

import commongodbDBCollection;

import commongodbDBObject;

import commongodbMongo;

import commongodbQueryOperators;

public class MongoDB {

private static void print(String str){

Systemoutprintln(str);

}

public static void main(String[] args) {

try {

//创建连接

Mongo m=new Mongo("127001", 27017);

//得到数据库

DB db=mgetDB("test");

//得到所有数据库

// List<String> colls=mgetDatabaseNames();

// for(String str:colls){

// Systemoutprintln(str);

// }

// //得到所有的集合（表）

// for(String collection:dbgetCollectionNames()){

// Systemoutprintln(collection);

// }

//删除一个数据库

//mdropDatabase("sun");

//得到sun表

DBCollection coll=dbgetCollection("things");

//查看一个表的索引

// for(DBObject index:collgetIndexInfo()){

// Systemoutprintln(index);

// }

// DBObject myDoc=collfindOne();

// Systemoutprintln(myDoc);

//添加

// BasicDBObject doc=new BasicDBObject();

// docput("name", "sunshan");

// docput("sex", "男");

// docput("age", 22);

//collinsert(doc);

//删除

//collremove(doc);

// BasicDBObject doc1=new BasicDBObject();

// doc1put("i", 0);

// doc1put("j", "foo");

// BasicDBObject doc2=new BasicDBObject();

// doc2put("hello", "world");

// doc1put("doc2", doc2);

// collinsert(doc1);

//修改

// BasicDBObject doc3=new BasicDBObject();

// doc3put("x", 6);

// BasicDBObject doc4=new BasicDBObject();

// doc4put("x", 1);

// collupdate(doc3, doc4,true,false);

//如果数据库不存在就添加 |多条修改 false只修改第一天，true如果有多条就不修改

//条件查询

//Systemoutprintln(collfind(doc4));

//collfindAndRemove(doc4);

// //批量插入

// List<DBObject> datas=new ArrayList<DBObject>();

// for(int i=0;i<10;i++){

// BasicDBObject bd=new BasicDBObject();

// bdput("name", "data");

// bdappend("age", i);

// datasadd(bd);

// }

// collinsert(datas);

//添加

// BasicDBObjectBuilder documentBuilder = BasicDBObjectBuilderstart();

// documentBuilderadd("database", "mkyongDB");

// documentBuilderadd("table", "hosting");

// BasicDBObjectBuilder documentBuilderDetail = BasicDBObjectBuilderstart();

// documentBuilderDetailadd("records", "99");

// documentBuilderDetailadd("index", "vps_index1");

// documentBuilderDetailadd("active", "true");

// documentBuilderadd("detail", documentBuilderDetailget());

// collinsert(documentBuilderget());

//添加

// Map<Object,Object> map=new HashMap<Object,Object>();

// mapput("a", 1);

// mapput("b", "b");

// collinsert(new BasicDBObject(map));

//添加

// String json ="{'1' : '1','2' : '2',"+"'11' : {'1' : 1, '2' : '2', '3' : '3'}}";

// DBObject dbobject=(DBObject)JSONparse(json);

// collinsert(dbobject);

//更新

// BasicDBObject bdo=new BasicDBObject();

// bdoput("x", 11);

// collupdate(new BasicDBObject()append("x", 0), bdo);

//更新

// BasicDBObject bdo=new BasicDBObject()append("$inc", new BasicDBObject()append("x", 12));

// collupdate(new BasicDBObject()append("x", 11), bdo);

//更新

//如果不使用$set 直接是 age则所有的都会更新

//根据age为9条件把name：data修改为 name：sun

// BasicDBObject bdo=new BasicDBObject()append("$set", new BasicDBObject()append("name", "sunshan"));

// collupdate(new BasicDBObject()append("age", 9), bdo);

//更新

//根据name为data条件把age：批量修改为 age:age

// BasicDBObject bdo=new BasicDBObject()append("$set", new BasicDBObject()append("age", "age"));

// collupdate(new BasicDBObject()append("name", "data"), bdo,false, true);

//查询age=1

// print("find："+collfind(new BasicDBObject("age", 1))toArray());

//查询age<=1

// print("find: "+collfind(new BasicDBObject("age", new BasicDBObject("$lte", 1)))toArray());

//查询age>=1

// print("fint: "+collfind(new BasicDBObject("age", new BasicDBObject("$gte", 1)))toArray());

//查询age！=1

// print("fint: "+collfind(new BasicDBObject("age", new BasicDBObject("$ne", 1)))toArray());

//查询age=1,2,3

// print("fint: "+collfind(new BasicDBObject("age", new BasicDBObject(QueryOperatorsIN ,new int[]{1,2,3})))toArray());

//查询age！=1,2,3

// print("find: "+collfind(new BasicDBObject("age" ,new BasicDBObject(QueryOperatorsNIN ,new int[]{1,2,3})))toArray());

// print("find: "+collfind(new BasicDBObject("age" ,new BasicDBObject(QueryOperatorsEXISTS ,true)))toArray());

//查询age属性

// print("find: "+collfind(null ,new BasicDBObject("age" ,true))toArray());

// List<DBObject> list=collfind()toArray();

// for(Object obj:list){

// Systemoutprintln(obj);

// }

//true查询出来存在的 /false 查询出来不存在的

//print(""+collfind(new BasicDBObject("y",new BasicDBObject(QueryOperatorsEXISTS,false)))toArray());

// DBObject dbc=new BasicDBObject();

// dbcput("name", 1111);

// List<DBObject> list=new ArrayList<DBObject>();

// listadd(dbc);

// Systemoutprintln(collinsert(list)getN());

// //查询部分数据块

// DBCursor cursor=collfind()skip(1);

// while(cursorhasNext()){

// Systemoutprintln(cursornext());

// }

// DBCursor cur=collfind(); //DBCursor cur=collfind()limit(2);

// while(curhasNext()){

// Systemoutprintln(curnext());

// }

//Systemoutprintln(curgetCursorId()+" "+curcount()+" "+JSONserialize(cur));

//条件查询

BasicDBObject doc5=new BasicDBObject();

doc5put("$gt", 1);

doc5put("$lt", 3);

print("find 21<y<23："+collfind(new BasicDBObject("y", doc5))toArray());

// BasicDBObject doc5=new BasicDBObject();

// doc5put("$gt", 1);

// doc5put("$lt", 3);

// BasicDBObject doc6=new BasicDBObject();

// doc6put("x", doc5);

// Systemoutprintln(collfind(doc6));

} catch (Exception e) {

eprintStackTrace();

}

}

}

数据块是一组按顺序连续排列在一起的几组记录，是主存储器与输入、输出设备或外存储器之间进行传输的一个数据单位。是数据的物理记录，与数据的逻辑记录（逻辑上有联系，在存储器上占有一组邻接单元的数据单位）之间的对应关系有3种方式：①一个块即为一个记录；②一个块包含若干个逻辑记录；③一个逻辑记录占有几个块。数据块的大小可以是固定的或是可变的，块与块之间有间隙。设计数据块大小，受到多方面因素的影响，包括输入、输出效率，存储空间代价以及计算机应用特点等。

在 text 、ntext 和 image 数据中，数据块是应用程序和 SQL Server 2000 实例之间一次传输的数据单元。该术语还适用于这些数据类型的存储单元。在磁带备份文件中，数据块是物理 I/O 的单元。

一 ROWID的概念

存储了row在数据文件中的具 置 位编码的数据 A Z a z + 和 /

row在数据块中的存储方式

SELECT ROWID last_name FROM hr employees WHERE department_id = ;

比如 OOOOOOFFFBBBBBBRRR

OOOOOO data object number 对应dba_objects data_object_id

FFF file# 对应v$datafile file#

BBBBBB block#

RRR row#

Dbms_rowid包

SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from dual;

具体到特定的物理文件

二 索引的概念

类似书的目录结构

Oracle 的 索引 对象 与表关联的可选对象 提高SQL查询语句的速度

索引直接指向包含所查询值的行的位置 减少磁盘I/O

与所索引的表是相互独立的物理结构

Oracle 自动使用并维护索引 插入 删除 更新表后 自动更新索引

语法 CREATE INDEX index ON table (column[ column] );

B tree结构(非bitmap)

[一]了解索引的工作原理

表 emp

目标 查询Frank的工资salary

建立索引 create index emp_name_idx on emp(name);

[试验]测试索引的作用

运行/rdbms/admin/utlxplan 脚本

建立测试表

create table t as select from dba_objects;

insert into t select from t;

create table indextable

as select rownum id owner object_name subobject_name

object_id data_object_id object_type created

from t;

set autotrace trace explain

set timing on

分析表 可以得到cost

查询 object_name= DBA_INDEXES

在object_name列上建立索引

再查询

[思考]索引的代价

插入 更新

三 唯一索引

何时创建 当某列任意两行的值都不相同

当建立Primary Key(主键)或者Unique constraint(唯一约束)时 唯一索引将被自动建立

语法 CREATE UNIQUE INDEX index ON table (column);

演示

四 组合索引

何时创建 当两个或多个列经常一起出现在where条件中时 则在这些列上同时创建组合索引

组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在列表的最前面

演示(组合列 单独列)

五 位图索引

何时创建

列中有非常多的重复的值时候 例如某列保存了 性别 信息

Where 条件中包含了很多OR *** 作符

较少的update *** 作 因为要相应的跟新所有的bitmap

结构 位图索引使用位图作为键值 对于表中的每一数据行位图包含了TRUE( ) FALSE( ) 或NULL值

优点 位图以一种压缩格式存放 因此占用的磁盘空间比标准索引要小得多

语法 CREATE BITMAP INDEX index ON table (column[ column] );

掩饰

create table bitmaptable as select from indextable where owner in( SYS PUBLIC );

分析 查找 建立索引 查找

六 基于函数的索引

何时创建 在WHERE条件语句中包含函数或者表达式时

函数包括 算数表达式 PL/SQL函数 程序包函数 SQL函数 用户自定义函数

语法 CREATE INDEX index ON table (FUNCTION(column));

演示

必须要分析表 并且query_rewrite_enabled=TRUE

或者使用提示/+ INDEX(ic_index)/

七 反向键索引

目的 比如索引值是一个自动增长的列

多个用户对集中在少数块上的索引行进行修改 容易引起资源的争用 比如对数据块的等待 此时建立反向索引

性能问题

语法

重建为标准索引 反之不行

八 键压缩索引

比如表landscp的数据如下

site feature job

Britten Park Rose Bed Prune

Britten Park Rose Bed Mulch

Britten Park Rose Bed Spray

Britten Park Shrub Bed Mulch

Britten Park Shrub Bed Weed

Britten Park Shrub Bed Hoe

……

查询时 以上 列均在where条件中同时出现 所以建立基于以上 列的组合索引 但是发现重复值很多 所以考虑压缩特性

Create index zip_idx

on landscp(site feature job)

press ;

将索引项分成前缀(prefix)和后缀(postfix)两部分 前两项被放置到前缀部分

Prefix : Britten Park Rose Bed

Prefix : Britten Park Shrub Bed

实际所以的结构为

Prune

Mulch

Spray

Mulch

Weed

Hoe

特点 组合索引的前缀部分具有非选择性时 考虑使用压缩 减少I/O 增加性能

九 索引组织表(IOT)

将表中的数据按照索引的结构存储在索引中 提高查询速度

牺牲插入更新的性能 换取查询性能 通常用于数据仓库 提供大量的查询 极少的插入修改工作

必须指定主键 插入数据时 会根据主键列进行B树索引排序 写入磁盘

十 分区索引

簇:

A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together

lishixinzhi/Article/program/Oracle/201311/17769

以上就是关于如何设计一个能够高效查询的千万级MySQL数据库全部的内容，包括:如何设计一个能够高效查询的千万级MySQL数据库、oracle数据库中表、段、区、块是什么意思、高手帮帮忙，我刚接触mongodb，怎么用java对mongodb数据库进行多条件查询，先谢谢了等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！