文档章节

知识整理-数据库(转载)

流鱼不乖
 流鱼不乖
发布于 2017/01/13 16:05
字数 18310
阅读 67
收藏 0

分类

 

Oracle

1.       show parameter;查看配置参数

 

1.Clob :视屏,动画,影像,图  4G

  blob

2.存储过程

3.定时任务

4.序列

5.rownum

   会根据返回记录生成一个序列化的数字

   不能对ROWNUM使用>(大于1的数值)、>=(大于或等于1的数值)、=(大于或等于1的数值),否则无结果

当查询到第一条记录时,该记录的ROWNUM为1,但条件要求ROWNUM>1,因此不符合,继续查询下一条;因为前面没有符合要求的记录,因此下一条记录过来后,其ROWNUM还是为1,如此循环,就不会产生结果。上述查询可以通过子查询来替代:

6. Sql有四种隔离级别:
         Read uncommited  读未提交,脏读,幻读,不可重复读都有可能发生,级别最低
         Read commited    读已提交,不允许脏读,
         Repeatable read   可重复读,不允许不可重复读,脏读
         Serializable       串行化  不允许出现任何并发问题,级别最高

脏读: 脏读意味着一个事务读取了另一个事务未提交的数据,而这个数据是有可能回滚

不可重复读: 不可重复读意味着,在数据库访问中,一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

幻读: 是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样.

7. 存储过程:存储过程就是编译好了的一些sql语句。
  1.存储过程因为SQL语句已经预编绎过了,因此运行的速度比较快。
  2. 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
  3.可以降低网络的通信量。存储过程主要是在服务器上运行,减少对客户机的压力。
  4:存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因
  5:存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

8. 触发器: 当满足触发器条件,则系统自动执行触发器的触发体。
  触发时间:有before,after.触发事件:有insert,update,delete三种。触发类型:有行触发、语句触发

9.例子

   select (case when a>b then a else b end ),
  (case when b>c then b esle c end)
  from table_name

如何只显示重复数据,或不显示重复数据:

显示重复:select * from tablename groupby id having count(*)>1
  不显示重复:select * from tablename groupby id having count(*)=1

数据库的映射:

就是将数据库的表与字段对应到模型层类名与属性的过程

Java Bean是被容器(tomcat)所创建,ava Bean还要实现Serializable接口用于实现Bean的持久性;

EJB可以被远程访问(跨进程、跨计算机)。但EJB必须被布署在诸如Webspere、WebLogic这样的容器中,EJB客户从不直接访问真正的EJB组件,而是通过其容器访问

数据库类型中 VARCHAR 和 CHAR 的区别: char 的长度是固定的,而 varchar2 的长度是可以变。CHAR效率比vachar高。

基本

1.      to_number  转换为数字

to_char  转换为字符串

to_date  转换为年月日

trunt 截取多少位  左右都需要有

2.      oracle 支持海量数据存储,支持分布式部署,支持多用户,跨平台,大型公司。

3.       

进阶

数据局优化:

   数据结构设计;应用程序设计;sql语句;服务器内存分配;数据库内存分配;

 数据迁移:导出脚本 执行

序列

   提供逐渐,提高访问效率; create sequence

   NEXTVAL:下一个;CURRVAL:当前;

  通过创建Oracle序列和触发器实现表的主键自增

  Sys_guid;

   自定义序列,并发问题:加表级锁

是序列号的话,就不会产生你说的情况,但是如果你用的是当前最大值加1的话就不行,也可以用 for update 实现不冲突

 

 存储过程:
  
create or replace procedure 存储过程名(param1 in type,param2 out type)

as

变量1 类型(值范围);

变量2 类型(值范围);

Begin

End;

调用’exe/  "{call getsum(?)}

 触发器:

   进行insert、update、delete操作的时候,这些操作会相应的触发一些事件的执行,通常要执行的事件被写成PL/SQL程序,那么这些数据库对象上的事件相关的程序就是触发器(trigger)

  Create or replace trigger **  before  ****begin …end;

索引:通mysql

rowid 用于定位数据表中某条数据的位置,是唯一的、也不会改变

rownum 表示查询某条记录在整个结果集中的位置, 同一条记录查询条件不同对应的 rownum 是不同的而 rowid 是不会变的

 

主键:

定时任务:
 
定时器就是定时来完成某些操作,oracle中的定时器job也是这种用法,在oracle中可以通过定义job来定期执行某个程序。

   Declare jobno  begin  存储过程;

   运行:begin  存储过.run(jobno);

疑问

案例

MySQL 

语法:

1.    不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。效率上truncate比delete快,但truncate删除后不记录mysql日志,不可以恢复数据。

索引

 普通索引:create index;ailter table add index;drop index;

唯一索引:值唯一,可以为空。 组合索引 列必须唯一

全文索引  生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

ALTER TABLE article ADD FULLTEXT index_content(content)

 

 

13

CREATE FULLTEXT INDEX index_content ON article(content)

 

单列索引,多列索引:MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

组合索引

 

索引优化:

1.      过多食用造成滥用;提高查询速度,境地更新表速度;占用磁盘空间;

2.      索引不要包含null;

3.      where子句中已经使用了索引的话,那么order by中的列是不会使用索引的

4.      少用Like,%aaa%不会使用索引,aaa%可以使用。

5.      不要在列上进行运算:where YEAR(adddate)<2007 索引失效;where adddate<’2007-01-01′ 使用索引;

6.      一个单引号引发mysql性能损失:explain

   Vachar如果用Int查询,不会使用索引,全表扫描;用’aa’会使用索引

 总结:MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多索引对于查询快,更新慢

  主键一定是唯一性索引

一个表中可以有多个唯一性索引,但只能有一个主键。 

主键列不允许空值,而唯一性索引列允许空值。 

索引可以提高查询的速度。

主键一般都要建,主要是用来避免一张表中有相同的记录,

主键是为了标识数据库记录唯一性,不允许记录重复,且键值不能为空,主键也是一个特殊索引。 
数据表中只允许有一个主键,但是可以有多个索引。 
使用主键会数据库会自动创建主索引,也可以在非主键上创建索引,方便查询效率。 
索引可以提高查询速度,它就相当于字典的目录,可以通过它很快查询到想要的结果,而不需要进行全表扫描。 

引擎

 Innodb:

    支持ACID,行级锁

Mylsam:

不支持事务,不支持行级锁;增删改锁住整张表。

两种存储引擎的大致区别表现在:
1)InnoDB支持事务,MyISAM不支持,这一点是非常之重要。事务是一种高级的处理方式,如在一些列增删改中只要哪个出错还可以回滚还原,而MyISAM就不可以了。
2MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及涉及到安全性较高的应用
3)InnoDB支持外键,MyISAM不支持
4)从MySQL5.5.5以后,InnoDB是默认引擎
5)InnoDB不支持FULLTEXT类型的索引
6)InnoDB中不保存表的行数,如select count(*) from table时,InnoDB需要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含where条件时MyISAM也需要扫描整个表
7)对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引
8)清空整个表时,InnoDB是一行一行的删除,效率非常慢。MyISAM则会重建表
9)InnoDB支持行锁(某些情况下还是锁整表,如 update table set a=1 where user like '%lee%'

关于MyISAM与InnoDB选择使用:
MYISAM和INNODB是Mysql数据库提供的两种存储引擎。两者的优劣可谓是各有千秋。INNODB会支持一些关系数据库的高级功能,如事务功能和行级锁,MYISAM不支持。MYISAM的性能更优,占用的存储空间少。所以,选择何种存储引擎,视具体应用而定:
1)如果你的应用程序一定要使用事务,毫无疑问你要选择INNODB引擎。但要注意,INNODB的行级锁是有条件的。where条件没有使用主键时,照样会锁全表。比如DELETE FROM mytable这样的删除语句。
2)如果你的应用程序对查询性能要求较高,就要使用MYISAM了。MYISAM索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能,这可以极大地优化LIKE查询的效率。

 

Oracle  vs   MySQL

分页比较

1.      jdbc访问数据库:mysql  limit;oracle rownum

   oracle: SELECT * FROM 
SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

2.      数量多

  如果你查询的记录比较大,并且数据传输量比较大,比如包含了text类型的field,则可以通过建立子查询。

3.      Limit分页优化

  子查询优化法 

             倒排表优化法 :类似建立索引,用一张表来维护页数,然后通过高效的连接得到数据

缺点:只适合数据数固定的情况,数据不能删除,维护页表困难

4.      Mysql 自增;oracle序列。

5.       

 

 

SQL中 exists和in的区别是什么

1.exist,not exist一般都是与子查询一起使用. In可以与子查询一起使用,也可以直接in (a,b.....)。
2.exist会针对子查询的表使用索引. not exist会对主子查询都会使用索引. in与子查询一起使用的时候,只能针对主查询使用索引. not in则不会使用任何索引. 注意,一直以来认为exists比in效率高的说法是不准确的。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。

数据库索引

1.       本质:索引就是数据结构,协助快速查询、更新数据库表中数据

2.       索引的实现通常使用B树及其变种B+

3.       索引:根据 特定查找算法;已某种方式指向数据

4.    

5.       最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

6.       数据库查找的速度 与查找算法有关:

  最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的

  有了 二分查找,二叉树查找;

7.  B_TREE:

  B-Tree中按key检索数据的算法;,key是物理地址。

  顺序访问指针

锁引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理,然后再结合这些原理分析B-/+Tree作为索引的效率。

  在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引

 

Mysql索引

1. 索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构,包含字段数值以及指向相关记录的指针,然后对这个索引结构进行排序,允许在该数据上进行二分法排序。

副作用是索引需要额外的磁盘空间,对于MyISAM引擎而言,这些索引是被统一保存在一张表中的,这个文件将很快到达底层文件系统所能够支持的大小限制,如果很多字段都建立了索引的话。

乐观所悲观锁

理解:

1.      数据库都支持并发,但是为解决数据冲突,都用了锁。

2.      悲观锁:假设数据肯定会冲突,所以在数据开始读取的时候就把数据锁定住。

3.      乐观锁:乐观锁就是认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让用户返回错误的信息,让用户决定如何去做

4.      并发量不大且不允许脏读,可以使用悲观锁解决并发问题;但如果系统的并发非常大的话,悲观锁定会带来非常大的性能问题,所以我们就要选择乐观锁定的方法.

Oracle

1.    行级锁,但是更占内存

2.      select * from test where id = 10 for update   锁定数据库,不能执行了(查询可执行),知道session commit/rollback

select * from test where id = 10 不锁定

select * from test where id = 10 for update nowait  可以采用nowait方式迅速判断当前数据是否被锁定中,如果锁定中的话,就要采取相应的业务措施进行处理

3.    Oracle中的悲观锁就是利用Oracle的Connection对数据进行锁定。在Oracle中,用这种行级锁带来的性能损失是很小的,提交时必须有一条数据库连接。

4.      乐观锁三种方式:时间戳,版本号,全部数据(不是主键,主键索引不变)

[1]第一种就是在数据取得的时候把整个数据都copy到应用中,在进行提交的时候比对当前数据库中的数据和开始的时候更新前取得的数据。当发现两个数据一模一样以后,就表示没有冲突可以提交,否则则是并发冲突,需要去用业务逻辑进行解决

[2]第二种乐观锁的做法就是采用版本戳,这个在hibernate中得到了使用。采用版本戳的话,首先需要在你有乐观锁的数据库table上建立一个新的column,比如为number型,当你数据每更新一次的时候,版本数就会往上增加1。比如同样有2个session同样对某条数据进行操作。两者都取到当前的数据的版本号为1,当第一个session进行数据更新后,在提交的时候查看到当前数据的版本还为1,和自己一开始取到的版本相同。就正式提交,然后把版本号增加1,这个时候当前数据的版本为

[3]新增一个Table的Column,不过这次这个column是采用timestamp型,存储数据最后更新的时间。timestamp with time zone类型来做时间戳。这种Timestamp的数据精度在Oracle的时间类型中是最高的,精确到微秒(还没与到纳秒的级别),一般来说,和刚才的版本戳类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则OK,否则就是版本冲突

 

MySQL

 

1.    不同的存储引擎支持不同的锁机制:

MyISAM和MEMORY存储引擎采用的是表级锁(table-level-locking);BDB存储引擎采用的是页面锁(page-level-locking),同时也支持表级锁;InnoDB存储引擎既支持行级锁,也支持表级锁,默认情况下是采用行级锁

2.    1) 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
2) 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
3) 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

3.    对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞 对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作

4.    行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

5.     InnoDB锁与MyISAM锁的最大不同在于:一是支持事务(TRANCSACTION),二是采用了行级锁

6.    InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!

 

其他数据库

1.      页级锁

2.      对一张表数据操作是串行插入或者更新,同时只能有一条数据插入,。带来后果,并发量大的时候,性能很低,数据库经常处于假死的状态。

索引

有索引时,插入慢:插入的数据要按索引排序

联合索引(explain)

1.  A:等值查询中,查询条件a返回的条目比较多,查询条件b返回的条目比较多,而同时查询a、b返回的条目比较少,那么适合建立联合索引;

   B:对于有等值查询的列和范围查询的列,等值查询的列建在前、范围查询的列建在后比较实用;

2. 触法索引的情况,例如:联合索引 A-B-C 查询 AB,AC,BC其中哪些会触发索引。

  索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效 ----最左原则,只有匹配上最左边的才行。

只有 ab ac 用了索引。

 

基本sql

去重:

Oracle:

查询及删除重复记录的SQL语句

1、查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(Id)来判断,只留有rowid最小的记录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录(多个字段)

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

Mysql:

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

数据库中间件

Cobar, MyCAT, TDDL, DRDS, DDB

 

1. 分布式数据库分表分库

2. 数据增量订阅与消费

3. 数据库同步(全量,增量,跨机房,复制)

4,跨数据库(数据源)迁移

 

 

中间层介于应用程序与数据库间,需要做一次转发,而基于JDBC协议并无额外转发,直接由应用程序连接数据库,性能上有些许优势。这里并非说明中间层一定不如客户端直连,除了性能,需要考虑的因素还有很多,中间层更便于实现监控、数据迁移、连接管理等功能。

都会分为分片规则配置、SQL解析、SQL改写、SQL路由、SQL执行以及结果归并等模块。

 

用户id分库,订单id分表;年分库,月+用户id 分表

SQL解析:fdb/jsqlparser和Druid。

SQL路由:找到数据源;单表路由,Binding表路由和笛卡尔积路由。

 

Cobar

1.Cobar必须将同一张表拆分的表放入不同的库中,必须是分布式

 

2.不支持跨库的 分页 排序 子查询

 

3.再 schema.xml  中配置  各数据库连接,数据库结构,数据节点,按照  规则(rule1)--再rule.xml中配置

 

 

4.rule.xml  中配置 rule:配置分库分表策略的类。

 

 

 

 

 

 

 

 

1.      server.xml 配置本地服务cobar的用户名和密码,cobar属于服务,需要启动,其他的只需要调用jar包

 

Cobar集群:

 

 

2.      cobar采用了主流的Reactor设计模式来处理请求,并使用NIO进行底层的数据交换,这大大提升系统的负载能力。其中,NIOAcceptor用于处理前端请求,NIOConnector则用于管理后端的连接,NIOProcessor用于管理多线程事件处理,NIOReactor则用于完成底层的事件驱动机制

3.      Cobar去除了JDBC。与后端数据库交互,Cobar直接面向协议,目前实现了基于MySQL协议的后端交互。

Sharding-JDBC

1.      当当的,目前只支持mysql ,以jar包形式提供,无proxy代理层

Mycat

1.      支持各种数据库和mongleDb  NOSQL数据库

2.      支持分库分表,读写分离,容在管理,多租户应用并发,云平台基础设施。

3.      后端:cobar 为bio,mycat是nio,实现并发,增加order by ,group by,limit 聚合功能。

4.     一个彻底开源的,面向企业应用开发的“大数据库集群” 支持事务、ACID、可以替代Mysql的加强版数据库

5.    一个可以视为“Mysql”集群的企业级数据库,用来替代昂贵的Oracle集群 

6.    一个融合内存缓存技术、Nosql技术、HDFS大数据的新型SQL Server 

7.    结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 

8.    一个新颖的数据库中间件产品

9.    

配置

Xml:

 

这里是配置的我们的myCat后台连接的真实的 1主2从服务器以及它们的连接信息。

启动:/mycat start  

打开mycat 客户端

 

24.分片

什么是分片


一般的replication具有一个限制,即一旦数据库过于庞大,尤其是当写入过于频繁,很难由一台主机支撑的时候,我们还是会面临到扩展瓶颈。
所谓数据分片(sharding)即是通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面,以达到分散单台设备负载的效果。。数据的切分同时还可以提高系统的总体可用性,因为单台设备Crash之后,只有总体数据的某部分不可用,而不是所有的数据。

 

数据的切分(Sharding)模式

一种是按照不同的表(或者Schema)来切分到不同的数据库(主机)之上,这种切可以称之为数据的垂直(纵向)切分;另外一种则是根据表中的数据的逻辑关系,将同一个表中的数据按照某种条件拆分到多台数据库(主机)上面,这种切分称之为数据的水平(横向)切分。

垂直切分


一个架构设计较好的应用系统,其总体功能肯定是由很多个功能模块所组成的,而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中,各个功能模块相互之间的交互点越统一越少,系统的耦合度就越低,系统各个模块的维护性以及扩展性也就越好。这样的系统,实现数据的垂直切分也就越容易。
一般来说,如果是一个负载相对不是很大的系统,而且表关联又非常的频繁,那可能数据库让步,将几个相关模块合并在一起减少应用程序的工作的方案可以减少较多的工作量,这是一个可行的方案。一个垂直拆分的例子:


1.用户模块表:user,user_profile,user_group,user_photo_album
2.群组讨论表:groups,group_message,group_message_content,top_message
3.相册相关表:photo,photo_album,photo_album_relation,photo_comment
4.事件信息表:event

群组讨论模块和用户模块之间主要存在通过用户或者是群组关系来进行关联。一般关联的时候都会是通过用户的id或者nick_name以及group的id来进行关联,通过模块之间的接口实现不会带来太多麻烦;
相册模块仅仅与用户模块存在通过用户的关联。这两个模块之间的关联基本就有通过用户id关联的内容,简单清晰,接口明确;
事件模块与各个模块可能都有关联,但是都只关注其各个模块中对象的ID信息,同样可以做到很容易分拆。

 

垂直切分的优点

数据库的拆分简单明了,拆分规则明确;

应用程序模块清晰明确,整合容易;

数据维护方便易行,容易定位;

垂直切分的缺点

 

部分表关联无法在数据库级别完成,需要在程序中完成;

对于访问极其频繁且数据量超大的表仍然存在性能瓶颈,不一定能满足要求;

事务处理相对更为复杂;

切分达到一定程度之后,扩展性会遇到限制;

过度切分可能会带来系统过渡复杂而难以维护。

水平切分

 

将某个访问极其频繁的表再按照某个字段的某种规则来分散到多个表之中,每个表中包含一部分数据。
对于上面的例子:所有数据都是和用户关联的,那么我们就可以根据用户来进行水平拆分,将不同用户的数据切分到不同的数据库中。
现在互联网非常火爆的Web2.0类型的网站,基本上大部分数据都能够通过会员用户信息关联上,可能很多核心表都非常适合通过会员ID来进行数据的水平切分。而像论坛社区讨论系统,就更容易切分了,非常容易按照论坛编号来进行数据的水平切分。切分之后基本上不会出现各个库之间的交互。

水平切分的优点


表关联基本能够在数据库端全部完成;
不会存在某些超大型数据量和高负载的表遇到瓶颈的问题;
应用程序端整体架构改动相对较少;
事务处理相对简单;
只要切分规则能够定义好,基本上较难遇到扩展性限制;


水平切分的缺点


切分规则相对更为复杂,很难抽象出一个能够满足整个数据库的切分规则;
后期数据的维护难度有所增加,人为手工定位数据更困难;
应用系统各模块耦合度较高,可能会对后面数据的迁移拆分造成一定的困难。

 

 

两种切分结合用

 

一般来说,我们数据库中的所有表很难通过某一个(或少数几个)字段全部关联起来,所以很难简单的仅仅通过数据的水平切分来解决所有问题。而垂直切分也只能解决部分问题,对于那些负载非常高的系统,即使仅仅只是单个表都无法通过单台数据库主机来承担其负载。我们必须结合“垂直”和“水平”两种切分方式同时使用
每一个应用系统的负载都是一步一步增长上来的,在开始遇到性能瓶颈的时候,大多数架构师和DBA都会选择先进行数据的垂直拆分,因为这样的成本最先,最符合这个时期所追求的最大投入产出比。然而,随着业务的不断扩张,系统负载的持续增长,在系统稳定一段时期之后,经过了垂直拆分之后的数据库集群可能又再一次不堪重负,遇到了性能瓶颈。
如果我们再一次像最开始那样继续细分模块,进行数据的垂直切分,那我们可能在不久的将来,又会遇到现在所面对的同样的问题。而且随着模块的不断的细化,应用系统的架构也会越来越复杂,整个系统很可能会出现失控的局面。
这时候我们就必须要通过数据的水平切分的优势,来解决这里所遇到的问题。而且,我们完全不必要在使用数据水平切分的时候,推倒之前进行数据垂直切分的成果,而是在其基础上利用水平切分的优势来避开垂直切分的弊端,解决系统复杂性不断扩大的问题。而水平拆分的弊端(规则难以统一)也已经被之前的垂直切分解决掉了,让水平拆分可以进行的得心应手。

 

优化

MySQL优化

1.      存储引擎

Mylsam:并发不大,多读少写,索引

Innodb:并发达,写频繁,有外键,事务,系统内存大。

2.      字段类型

尽可能小。,占用内存少,占用磁盘空间少,占用磁盘io少,占用带宽少。

3.      编码

Utf-8

4.      主键(innodb)

自动生成最好,不用可变字段

5.      NULL 不用

难优化,占用太多磁盘空间

6.      索引 :explain

加快查询速度,但是降低性能,占用磁盘空间。不是越多越好

唯一性索引;

经常排序。分组,联合操作的列

经常作为查询条件

数据量少的作为索引

7.      读写分离,主从复制,负载均衡,读负载到多台服务器上,

8.      利用分区功能进行大数据拆分。

 

10.Sql优化

   不要在数据库做运算;

   避免%为前缀的查询

   避免索引列做运算

   查询使用Limit减少返回行数

   联合查询;小结果集放前面

   Explain优化sql

   Innodb避免全表扫描,会使用表锁

   尽可能缓存所有的索引和数据,提高响应速度

避免主键更新。

9.      数据库性能优化:

   读写分离  主从复制 负载均衡  分库分表

   存储引擎

   表结构设计

   索引  主键   字段  字段大小、类型

   参数设置

 

Mysql

1. 导致索引失效的情况:

A、如果条件中有or,即使其中有条件带索引也不会使用到。

B、对于多列索引,不是使用的第一部分,则不会使用索引。

C、like查询是以%开头,而不是以%结尾的。

D、如果索引列类型是字符串,一定要在条件中将数据使用引号引用起来,否则不使用索引。

E、如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

2. InnoDB要求表必须有主键(MyISAM可以没有),

 

1.    海量数据处理

①纵向扩展:基于业务的高度隔离性和数据的安全性,对业务和数据进行合理的切分,进行主-备机分离,主-主同步,主-从同步(对于MySQL数据库是单向异步同步机制),主-从热备等操作。

②横向扩展:对数据表进行横向切分,按一定的规则(hash取模分、user_id尾数、自定义算法等)对数据表进行横向切分。

 

Sql中exists 和 in的区别是什么啊?

1.exist,not exist一般都是与子查询一起使用. In可以与子查询一起使用,也可以直接in (a,b.....)。
2.exist会针对子查询的表使用索引. not exist会对主子查询都会使用索引. in与子查询一起使用的时候,只能针对主查询使用索引. not in则不会使用任何索引. 注意,一直以来认为exists比in效率高的说法是不准确的。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。

1、MySQL的复制原理以及流程

基本原理流程,3个线程以及之间的关联;

1. 主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;

2. 从:io线程——在使用start slave 之后,负责从master上拉取 binlog 内容,放进 自己的relay log中;

3. 从:sql执行线程——执行relay log中的语句;


 

2、MySQL中myisam与innodb的区别,至少5点

(1)、问5点不同;

1>.InnoDB支持事物,而MyISAM不支持事物

2>.InnoDB支持行级锁,而MyISAM支持表级锁

3>.InnoDB支持MVCC, 而MyISAM不支持

4>.InnoDB支持外键,而MyISAM不支持

5>.InnoDB不支持全文索引,而MyISAM支持。


 

(2)、innodb引擎的4大特性

插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

(3)、2者selectcount(*)哪个更快,为什么

myisam更快,因为myisam内部维护了一个计数器,可以直接调取。


 

3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义
(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型
(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)
(3)、int(20)中20的涵义
是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001 ~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;
(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

4、问了innodb的事务与日志的实现方式
(1)、有多少种日志;
错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
二进制日志:记录对数据库执行更改的所有操作。
中继日志:
事务日志:

(2)、事物的4种隔离级别
隔离级别
读未提交(RU)
读已提交(RC)
可重复读(RR)
串行

(3)、事务是如何通过日志来实现的,说得越深入越好。
事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence number)号; 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”

5、问了MySQL binlog的几种日志录入格式以及区别
(1)、binlog的日志格式的种类和分别
(2)、适用场景;
(3)、结合第一个问题,每一种日志格式在复制中的优劣。
Statement:每一条会修改数据的sql都会记录在binlog中。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能 与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条 件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所 产生的日志量会增加多少,以及带来的IO性能问题。)
缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的 一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).
使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁
2.Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。
优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题
缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比 如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。
3.Mixedlevel: 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的 变更。

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理?
(1)、没有经验的,可以不问;
(2)、有经验的,问他们的处理思路。
列出所有进程  show processlist  观察所有进程  多秒没有状态变化的(干掉)
查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半,比如where子句或分页子句没有发送,,当然的一次被坑经历)

7、sql优化
(1)、explain出来的各种item的意义;
select_type 
表示查询中每个select子句的类型
type
表示MySQL在表中找到所需行的方式,又称“访问类型”
possible_keys 
指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
key
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值 
Extra
包含不适合在其他列中显示但十分重要的额外信息

(2)、profile的意义以及使用场景;
查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等

8、备份计划,mysqldump以及xtranbackup的实现原理
(1)、备份计划;
这里每个公司都不一样,您别说那种1小时1全备什么的就行
(2)、备份恢复时间;
这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考
20G的2分钟(mysqldump)
80G的30分钟(mysqldump)
111G的30分钟(mysqldump)
288G的3小时(xtra)
3T的4小时(xtra)
逻辑导入时间一般是备份时间的5倍以上

(3)、xtrabackup实现原理
在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

9、mysqldump中备份出来的sql,如果我想sql文件中,一行只有一个insert....value()的话,怎么办?如果备份需要带上master的复制点信息怎么办?
--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert
Enter password:
  KEY `idx_c1` (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `helei`
--

LOCK TABLES `helei` WRITE;
/*!40000 ALTER TABLE `helei` DISABLE KEYS */;
INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');
INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');
INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

10、500台db,在最快时间之内重启
puppet,dsh

11、innodb的读写参数优化
(1)、读取参数
global buffer pool以及 local buffer;

(2)、写入参数;
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

(3)、与IO相关的参数;
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

(4)、缓存参数以及缓存的适用场景。
query cache/query_cache_type
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

·         第一个:读操作多的话看看比例,简单来说,如果是用户清单表,或者说是数据比例比较固定,比如说商品列表,是可以打开的,前提是这些库比较集中,数据库中的实务比较小。

·         第二个:我们“行骗”的时候,比如说我们竞标的时候压测,把query cache打开,还是能收到qps激增的效果,当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多,访问量并不多,那么就不要打开,例如社交网站的,10%的人产生内容,其余的90%都在消费,打开还是效果很好的,但是你如果是qq消息,或者聊天,那就很要命。

·         第三个:小网站或者没有高并发的无所谓,高并发下,会看到 很多 qcache 锁 等待,所以一般高并发下,不建议打开query cache


 


12、你是如何监控你们的数据库的?你们的慢日志都是怎么查询的?
监控的工具有很多,例如zabbix,lepus,我这里用的是lepus

13、你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

14、你们数据库是否支持emoji表情,如果不支持,如何操作?
如果是utf8字符集的话,需要升级至utf8_mb4方可支持

15、你是如何维护数据库的数据字典的?
这个大家维护的方法都不同,我一般是直接在生产库进行注释,利用工具导出成excel方便流通。

16、你们是否有开发规范,如果有,如何执行的
有,开发规范网上有很多了,可以自己看看总结下

17、表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问
(1)、您是选择拆成子表,还是继续放一起;
(2)、写出您这样选择的理由。
答:拆带来的问题:连接消耗 + 存储拆分空间;不拆可能带来的问题:查询性能;
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?
答:InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

.

19、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表?
 

答案见:http://suifu.blog.51cto.com/9167728/1830651

开放性问题:据说是腾讯的
一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

 

30mysql优化sql语句查询的方法
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
  2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
  3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
  select id from t where num is null
  可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
  select id from t where num=0
  4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
  select id from t where num=10 or num=20
  可以这样查询:
  select id from t where num=10
  union all
  select id from t where num=20
  5.下面的查询也将导致全表扫描:
  select id from t where name like '%abc%'
  若要提高效率,可以考虑全文检索。
  6.in 和 not in 也要慎用,否则会导致全表扫描,如:
  select id from t where num in(1,2,3)
  对于连续的数值,能用 between 就不要用 in 了:
  select id from t where num between 1 and 3
  7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
  select id from t where num=@num
  可以改为强制查询使用索引:
  select id from t with(index(索引名)) where num=@num
  8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
  select id from t where num/2=100
  应改为:
  select id from t where num=100*2
  9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
  select id from t where substring(name,1,3)='abc'--name以abc开头的id
  select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
  应改为:
  select id from t where name like 'abc%'
  select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'
  10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
  11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
  12.不要写一些没有意义的查询,如需要生成一个空表结构:
  select col1,col2 into #t from t where 1=0
  这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
  create table #t(...)
 
  13.很多时候用 exists 代替 in 是一个好的选择:
  select num from a where num in(select num from b)
  用下面的语句替换:
  select num from a where exists(select 1 from b where num=a.num)
  14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
  15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
  17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
  18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
  19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
  20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
  21.避免频繁创建和删除临时表,以减少系统表资源的消耗。
  22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
  23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
  24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
  25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
  26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
  27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
  28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
  29.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
  30.尽量避免大事务操作,提高系统并发能力。

 

20.一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 。

如果表的类型是MyISAM,那么是18。 
因为MyISAM表会把自增主键的最大ID记录到数据文件里,重启MySQL自增主键的最大ID也不会丢失。 
如果表的类型是InnoDB,那么是15。 
InnoDB表只是把自增主键的最大ID记录到内存中,所以重启数据库或者是对表进行OPTIMIZE操作,都会导致最大ID丢失。

1.       查看执行时间

2.       查看执行计划

Explain+sql

3.       参数

mySQL的进程使用情况:show global status like 'thread%';

工具:sqlyog

 

Oracle@@

索引

MySQL如何实现索引的:

在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,比如MyISAM和InnoDB存储引擎。

MyISAM索引实现:

MyISAM存储引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现:

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

第一个重大区别是:InnoDB的数据文件本身就是索引文件。

第二个与MyISAM索引的不同是:InnoDB的辅助索引data域存储相应记录主键的值而不是地址。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

其实,数据库索引的实现可以采用红黑树,B-Tree树数据结构。

但是为什么实际上采用的B+Tree呢?

这要从计算机存储原理和操作系统相关知识说起。因为数据表的索引比较大,不能常驻内存,所以以文件形式存储在磁盘中。所以当查询数据的时候就需要I/O操作。高效率查询的目标是较少I/O次数。一次I/O一般读取一页(一般为4k)大小的数据(局部性原理)。如此,在B-树中,每当申请一个新结点时,就以页的大小来申请。也就是说一次I/o可以读取一个一个结点(包含很多key)的数据;而在红黑树结构结构中,逻辑相邻的结点物理上不一定相邻,就是说,读取同等的数据需要多次I/O。所以选择B-树效率更好。

那为何最终选了B+树呢?

因为B+树内节点去掉了data域,因此可以拥有更大的出度,就是说一个结点可以存储更多的内结点,那么I/O效率更高。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

聚集索引与非聚集索引之分:

InnoDB是聚集索引,因为它的B+树的叶结点包含了完整的数据记录。而MyISAM方式B+树的叶结点只是存储了数据的地址,故称为非聚集索引。

索引使用策略及优化

MySQL的优化主要分为结构优化(Scheme optimization)和查询优化(Query optimization)。

一、索引的概念
        索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。
二、索引的特点
    1.索引可以加快数据库的检索速度
    2.索引降低了数据库插入、修改、删除等维护任务的速度
    3.索引创建在表上,不能创建在视图上
    4.索引既可以直接创建,也可以间接创建
    5.可以在优化隐藏中,使用索引
    6.使用查询处理器执行SQL语句,在一个表上,一次只能使用一个索引
    7.其他
三、索引的优点
    1.创建唯一性索引,保证数据库表中每一行数据的唯一性
    2.大大加快数据的检索速度,这也是创建索引的最主要的原因
    3.加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
    4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
    5.通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能。
四、索引的缺点
    1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
    2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大
    3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度
五、索引分类
    1.直接创建索引和间接创建索引
    直接创建索引: CREATE INDEX mycolumn_index ON mytable (myclumn)
    间接创建索引:定义主键约束或者唯一性键约束,可以间接创建索引
    2.普通索引和唯一性索引
    普通索引:CREATE INDEX mycolumn_index ON mytable (myclumn)
    唯一性索引:保证在索引列中的全部数据是唯一的,对聚簇索引和非聚簇索引都可以使用
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.单个索引和复合索引
    单个索引:即非复合索引
    复合索引:又叫组合索引,在索引建立语句中同时包含多个字段名,最多16个字段
    CREATE INDEX name_index ON username(firstname,lastname)
    4.聚簇索引和非聚簇索引(聚集索引,群集索引)
   聚簇索引:物理索引,与基表的物理顺序相同,数据值的顺序总是按照顺序排列
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
    ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
   非聚簇索引:CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)
六、索引的使用
    1.当字段数据更新频率较低,查询使用频率较高并且存在大量重复值是建议使用聚簇索引
    2.经常同时存取多列,且每列都含有重复值可考虑建立组合索引
    3.复合索引的前导列一定好控制好,否则无法起到索引的效果。如果查询时前导列不在查询条件中则该复合索引不会被使用。前导列一定是使用最频繁的列
    4.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案
    5.where子句中对列的任何操作结果都是在sql运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被sql优化器优化,使用索引,避免表搜索(例:select * from record where substring(card_no,1,4)=’5378’
&& select * from record where card_no like ’5378%’)任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边
    6.where条件中的’in’在逻辑上相当于’or’,所以语法分析器会将in ('0','1')转化为column='0' or column='1'来执行。我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用column上的索引;但实际上它却采用了"or策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用column上索引,并且完成时间还要受tempdb数据库性能的影响。in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引
    7.要善于使用存储过程,它使sql变得更加灵活和高效

 

分库分表

1.       切分

先垂直再水平;讲关系联系紧密的同一个库。

 

2.        只有两个字段数据有上亿,如果分表 分表后如何查询。

a. 分表,不分库:用union建立视图

   create view   view_nam  as select * from news1 union select * from news2 union ..

      select * from view_name where title like '%阿斯达%' order by 字段名

 

分表是分散数据库压力的好方法。
分表,最直白的意思,就是将一个表结构分为多个表,然后,可以再同一个库里,也可以放到不同的库。
当然,首先要知道什么情况下,才需要分表。个人觉得单表记录条数达到百万到千万级别时就要使用分表了。
1,分表的分类
1>纵向分表
将本来可以在同一个表的内容,人为划分为多个表。(所谓的本来,是指按照关系型数据库的第三范式要求,是应该在同一个表的。)
分表理由:根据数据的活跃度进行分离,(因为不同活跃的数据,处理方式是不同的)
案例:
对于一个博客系统,文章标题,作者,分类,创建时间等,是变化频率慢,查询次数多,而且最好有很好的实时性的数据,我们把它叫做冷数据。而博客的浏览量,回复数等,类似的统计信息,或者别的变化频率比较高的数据,我们把它叫做活跃数据。所以,在进行数据库结构设计的时候,就应该考虑分表,首先是纵向分表的处理。
这样纵向分表后:
首先存储引擎的使用不同,冷数据使用MyIsam 可以有更好的查询数据。活跃数据,可以使用Innodb ,可以有更好的更新速度。
其次,对冷数据进行更多的从库配置,因为更多的操作时查询,这样来加快查询速度。对热数据,可以相对有更多的主库的横向分表处理。
其实,对于一些特殊的活跃数据,也可以考虑使用memcache ,Redis
之类的缓存,等累计到一定量再去更新数据库。或者MongoDB 一类的nosql 数据库,这里只是举例,就先不说这个。
2>横向分表
字面意思,就可以看出来,是把大的表结构,横向切割为同样结构的不同表,如,用户信息表,user_1,user_2 等。表结构是完全一样,但是,根据某些特定的规则来划分的表,如根据用户ID来取模划分。

分表理由:根据数据量的规模来划分,保证单表的容量不会太大,从而来保证单表的查询等处理能力。
案例:同上面的例子,博客系统。当博客的量达到很大时候,就应该采取横向分割来降低每个单表的压力,来提升性能。例如博客的冷数据表,假如分为100个表,当同时有100万个用户在浏览时,如果是单表的话,会进行100万次请求,而现在分表后,就可能是每个表进行1万个数据的请求(因为,不可能绝对的平均,只是假设),这样压力就降低了很多很多。

106.分表后进行分页

要是整体的分页显示那就更简单了
1、每个表的记录数是已知的,应在每次发生变化时记录到目录表中
2、无论是否排序(如果排序只是表的次序不同)至多会 union 两个分表
如假定共3个分表,记录数分别为 90,120,80 总记录数为 290
设分页是每页显示40条,则 
第1页 表一的 1 到 40
第2页 表一的 41 到 80
第3页 表一的 81 到 90 + 表二的 1 到 30
第4页 表二的 31 到 70
第5页 表二的 71 到 110
第6页 表二的 111 到 120 + 表三的 1 到 30
计算起来非常简单

 

1. 订单:分库分表

按照系统分库;按照订单(城市)分表。主键自定义

   

本文转载自:

流鱼不乖
粉丝 1
博文 22
码字总数 2491
作品 0
南京
私信 提问
Oracle存储过程实现返回多个结果集 在构造函数方法中使用 dataset

原文 Oracle存储过程实现返回多个结果集 在构造函数方法中使用 dataset DataSet相当你用的数据库; DataTable相当于你的表。一个 DataSet 可以包含多个 DataTable。如果用datatable只适合返回...

橘子红了呐
2017/11/20
0
0
Oracle :一次数据库连接,返回多个结果集

1. 一次数据库连接,返回多个结果集 1.1 建立包规范 create or replace package QX_GDJTJ is-- Author : xxx -- Created : 2012-1-1 -- Purpose : 统计主设备缺陷 TYPE TCURSOR IS REF CURS......

橘子红了呐
2017/11/22
0
0
常见面试题整理--计算机网络篇(每位开发者必备)

作者:路人甲 链接:https://zhuanlan.zhihu.com/p/24001696 来源:知乎 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。 计算机网络相关的知识点是在面试过程中开发...

陈雨曦
2016/12/01
323
0
程序员如何打造属于自己的云笔记服务

摘要: 目录 前言 介绍 安装 启动服务 使用配置 APP访问 前言 在这个互联网知识呈爆炸增长的时代,作为一个程序员要掌握的知识越来越多,然再好的记性也不如烂笔头,有了笔记我们就是可以时常...

姬子玉
2017/11/28
0
0
阿里云双十二优惠活动 爆款产品

大家期待已久的阿里云双十二活动,此次活动力度是比较大的,可以免费送服务器,还有iPhone x,大疆无人机,iPad pro,等奖品 2018双十二阿里云放大招了!送你免费云服务器器! 双十二即将来临...

kafei001
2018/12/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

DDD(五)

1、引言 之前学习了解了DDD中实体这一概念,那么接下来需要了解的就是值对象、唯一标识。值对象,值就是数字1、2、3,字符串“1”,“2”,“3”,值时对象的特征,对象是一个事物的具体描述...

MrYuZixian
今天
3
0
数据库中间件MyCat

什么是MyCat? 查看官网的介绍是这样说的 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵...

沉浮_
今天
4
0
解决Mac下VSCode打开zsh乱码

1.乱码问题 iTerm2终端使用Zsh,并且配置Zsh主题,该主题主题需要安装字体来支持箭头效果,在iTerm2中设置这个字体,但是VSCode里这个箭头还是显示乱码。 iTerm2展示如下: VSCode展示如下: 2...

HelloDeveloper
今天
6
0
常用物流快递单号查询接口种类及对接方法

目前快递查询接口有两种方式可以对接,一是和顺丰、圆通、中通、天天、韵达、德邦这些快递公司一一对接接口,二是和快递鸟这样第三方集成接口一次性对接多家常用快递。第一种耗费时间长,但是...

程序的小猿
今天
6
0
Python机器学习之数据探索可视化库yellowbrick

背景介绍 从学sklearn时,除了算法的坎要过,还得学习matplotlib可视化,对我的实践应用而言,可视化更重要一些,然而matplotlib的易用性和美观性确实不敢恭维。陆续使用过plotly、seaborn,...

yeayee
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部