数据库基础

不同的数据库中字符串连接符不同:
1、oracle数据库中的连接符为||,例如连接字符串AB、CD可以使用“AB”||“CD”;
2、SQLSERVER 数据库连接符为+,例如连接字符串AB、CD可以使用“AB”+“CD”;
3、MYSQL数据库中连接符为+,例如连接字符串AB、CD可以使用“AB”+“CD”;

SQL聚集函数是以一个集合(集或者多重集)为输入,返回单个值的函数。SQL提供了五个聚集函数:

  • 平均值:avg
  • 最小值:min
  • 最大值:max
  • 总和:sum
  • 计数:count

而Having是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作,在Having中可以使用聚合函数。

范式

  • 第一范式(1NF)
    指数据库表的每一列数据项都不可分割,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。简而言之,第一范式就是无重复的列。
  • 第二范式(2NF)
    要求数据库表中的每个实例或行必须可以被唯一地区分。要求实体的属性完全依赖于主关键字
    所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。简而言之,第二范式就是非主属性依赖于主关键字。
  • 第三范式(3NF)
    在满足第二范式的基础上,不存在传递函数依赖,那么就是第三范式。简而言之,第三范式就是属性不依赖于其它非主属性。

常用的关系数据库

  • Oracle
  • MySql
  • MariaDB
  • PostgreSQL

NoSQL

  • redis
  • mongodb
  • hbase
  • Cassandrs
  • pika

NewSQL

  • TiDB
  • OceanBase

数据库事务特性

  • A 原子性
  • C 一致性
  • I 隔离性
  • D 持久性

本地事务数据库断电的这种情况,它是怎么保证数据一致性的呢?使用SQL Server来举例,在执行事务的时候数据库首先会记录下这个事务的redo操作日志,然后才开始真正操作数据库,在操作之前首先会把日志文件写入磁盘,那么当突然断电的时候,即使操作没有完成,在重新启动数据库时候,数据库会根据当前数据的情况进行undo回滚或者是redo前滚,这样就保证了数据的强一致性。

Mysql

Mysql存储引擎

MyISAM

MyISAM:官方提供的存储引擎,基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,不支持外键。

  • 查询效率高,执行大量的查询MyISAM比较适合。
  • 不支持事务,回滚将造成不完全回滚,不具有原子性。
  • 不支持外键
  • 只支持表级锁,不支持行锁定,这样同一个表上的读锁和写锁是互斥的,MyISAM并发读写时如果等待队列中既有读请求又有写请求,默认写请求的优先级高,即使读请求先到,所以MyISAM不适合于有大量查询和修改并存的情况,那样查询进程会长时间阻塞。因为MyISAM是锁表,所以某项读操作比较耗时会使其他写进程饿死。在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。表锁开销小,加锁快,不会出现死锁,粒度大,锁冲突概率高,并发效率低。
  • 支持全文搜索,全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用,因为它不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉。

DELETE 表时,先drop表,然后重建表
MyISAM 表被存放在三个文件:frm 文件存放表格定义,数据文件是MYD (MYData) ,索引文件是MYI (MYIndex)引伸。
跨平台很难直接拷贝
MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格可以被压缩。
MyISAM支持GIS数据,InnoDB不支持。GIS数据是指空间数据对象:Point,Line,Polygon,Surface等。
MyISAM保存表的具体行数,在查询不带where时,直接返回保存的行数。所以没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读;而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。这里特别强调“主键以外”,是因为InnoDB中primary index是和raw data存放在一起的,而secondary index则是单独存放,然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快,而primary key则主要在扫描索引同时要返回raw data时的作用较大。

InnoDB

InnoDB,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。5.5版本后成为默认存储引擎。

  • 支持ACID事务,InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。
  • 支持外键
  • 支持行级锁

update、insert、delete自动加隐式排它锁,或者select for update显示排它锁。不加锁读取

在Mysql5.6版本开始支持FullText类型的索引,5.7.6版本支持了中文索引。
DELETE 表时,是一行一行的删除
InnoDB 把数据和索引存放在表空间里面
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩
InnoDB的主键范围更大,最大是MyISAM的2倍
innodb属于索引组织表,有两种存储方式,共享表空间存储和多表空间存储,两种存储方式的表结构和myisam一样,以表名开头,扩展名是.frm。如果使用共享表空间,那么所有表的数据文件和索引文件都保存在一个表空间里,一个表空间可以有多个文件,通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字,一般共享表空间的名字叫ibdata1-n。如果使用多表空间,那么每个表都有一个表空间文件用于存储每个表的数据和索引,文件名以表名开头,以.ibd为扩展名。

TokuDB

第三方开发的存储引擎,写速度快,

支持数据压缩存储,适合访问频率低的数据,历史数据归档。不适合大量读取

支持在线添加索引不影响读写操作,

3.对比选择:
MyISAM相对简单,在效率上要优于InnoDB。从效率上来看,如果系统读多写少,对原子性要求低,那么MyISAM最好的选择,且MyISAM恢复速度快,可直接用备份覆盖恢复。如果系统读少写多,尤其是高并发写入,InnoDB就是首选了。

MyISAM与InnoDB两者之间区别与选择,详细总结,性能对比
https://www.cnblogs.com/y-rong/p/8110596.html

数据库锁级别

  • 表级锁:开销小,加锁快,不会出现死锁,粒度大,锁冲突概率高,并发效率低。
  • 行级锁:开锁大,加锁慢,可能出现死锁,粒度小,锁冲突概率低,并发效率高。
  • 共享锁:读锁。其它事务可读不可写。
  • 排它锁:写锁。其它事务不可读不可写。

存储过程与函数

在Mysql服务器中运行,减少网络数据传输。

  • 存储过程实现复杂数据库操作,是独立的sql语句调用。与数据库实现绑定,降低了程序的可移植性。
  • 函数实现针对性强的功能,例如按特殊策略求和。用户定义函数不能修改数据库全局状态。

Mysql8.0新特性

  • 默认字符集修改为了utf-8
  • 增加了隐式索引,隐藏后的索引不会被查询优化器使用,这个特性可用了性能调试。
  • 通用表表达式,复杂查询中的嵌入语句表达更清晰。
  • 窗口函数,与集合函数类似,可实现新的查询方式。不会将多行查询结果合并,不需要group by

Mysql索引

需要额外磁盘空间来保存索引,插入更新删除会增加额外的开销,索引适合读多写少的场景。

索引类型:

  • 唯一索引:值可为NULL。
  • 主键索引:不允许出现空值。
  • 普通索引
  • 联合索引:最左原则即查询条件中的字段必须符合在索引字段中从左开始连续
  • 全文索引:只能在char、varchar、text类型字段上使用。

索引实现:

  • B-Tree:mysql中普遍使用B+Tree索引,但在实现方式上又分为聚簇索引和非聚簇索引。
  • R-Tree
  • Hash
  • FullText:倒排索引,关键字与文档关系。

聚簇索引和非聚簇索引

  • 聚簇索引:指主索引文件和数据文件为同一份文件,聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身,key为主键。
  • 非聚簇索引:指B+Tree的叶子节点上的data并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中。非聚簇索引比聚簇索引多了一次读取数据的IO操作,所以查找性能上会差。

utf8mb4

MySQL在5.5.3之后增加了utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。
utf8mb4是utf8的超集,为了节省空间,一般情况下使用utf8就够了。

int(11)

int(n)中,n表示查询显示时的最小宽度,即最小的字符数。若不够最小长度,则前面补0。

自定义函数

转换三点分版本号为纯数字,以便于比较版本大小:

CREATE DEFINER=`root`@`%` FUNCTION `formatVersion3`(version varchar(11)) RETURNS varchar(9) CHARSET latin1
BEGIN
	declare result VARCHAR(9);
	set result = CONCAT(LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 1), '.', -1), 3, '0'),
			    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1), 3, '0'),
			    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 3), '.', -1), 3, '0'));
	RETURN result;
END

Mysql调优

优化的维度

  • 表结构与索引
  • SQL语句
  • Mysql参数
  • 硬件和系统配置

数据库的水平和垂直扩展能力,提前规则未来读写和数据量的增长,分库分表方案。按UID维度散列,分为4个库,每个库32张表,保证单表数据量控制在千万级别。

字段选择合适的最小空间的数据类型,如年龄使用tinyint

多字段表拆分为多个表,必要时使用中间表关联。

范式与反范式、反范式即保持一定的冗余。

分析慢查询日志,寻找需要优化的语句

利用分析工具,如explain、profile。

查询语句避免使用select *,要指定需要的列。可以避免查询列字段的元信息。

1.使用内置缓存
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次,这些查询结果会被缓存。
对于像 NOW() 、RAND()、CURDATE()等SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以需要用一个变量来代替MySQL的函数,从而开启缓存。

2.使用 EXPLAIN 执行SELECT查询
使用 EXPLAIN 关键字可以打印出MySQL是如何处理SQL语句的,可以帮助分析查询语句或者表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉索引主键是被如何利用的、数据表是如何被搜索和排序的等等。
在SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面,可以使用phpmyadmin来执行。然后会看到一张表格。

3. 追加 LIMIT 1
查询表有时已经知道结果只会有一条结果,此时加上 LIMIT 1 可以增加性能。MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
但是在用explain查询分析,显示limit 1加与不加,性能相同。是因为可能是使用了缓存吗?

4. 搜索字段建索引
索引并不一定是给主键或是唯一的字段,如果表中有某个字段经常用来做搜索,那么请为其建立索引。
有些搜索是不能使用正常的索引的。例如需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引是没有意义的。这可能需要使用MySQL全文索引或是自己做一个索引(比如搜索关键词、标签)。
查询语句中使用use index可以指定索引。

5.使用 ENUM 代替 VARCHAR
ENUM 类型是非常快和紧凑的,实际上其保存的是 TINYINT,但其外表上显示为字符串。用这个字段类型来做选项列表变得相当的完美。比如“性别”,“国家”,“民族”,“状态”或“部门”字段,这些字段的取值是有限而且固定的,那么应该使用 ENUM 而不是 VARCHAR。

6.PROCEDURE ANALYSE() 结构建设
PROCEDURE ANALYSE() 会让 MySQL 帮助分析字段和其实际的数据,给出一些有用的建议。只有表中有实际的数据这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。
例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。但这些建议可能因为数据不够多而决策做得就不够准确。
在phpmyadmin里,你可以在查看表结构时,点击 “Propose table structure” 或“规则表结构”来查看这些建议,建议结果呈现在Optimal_fieldtype字段中。

7.字段尽量设置默认值
除非有特殊原因使用 NULL 值,否则应该总是让字段保持 NOT NULL。NULL需要额外的空间,并且在进行比较的时候,程序会更复杂,难以查询优化。
下面摘自MySQL自己的文档:
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

MySQL 性能优化…经验分享
https://www.cnblogs.com/pengyunjing/p/6591660.html

mysql中不要用blob等大字段

在开发规范中,我们一般都是要求字段不要设置成blob这种大字段。原因是查询的时候效率问题。另外一个就是在插入量大的时候,生成的binlog太多,容易导致一些问题。线上就遇到了,1k多的插入,binlog狂写,磁盘干满了。

1.多表先约束再连接
由全表连接,变为先约束为小表再连接。

2.索引
随着集合的增长,需要针对查询条件中数据量大的排序字段做索引。
如果没有对索引的键排序,数据库需要将所有数据提取到内存并排序。因此在做无索引排序时,如果数据量过大以致无法在内存中进行排序,数据库将会报错。初步可以对出现查询慢的表建立索引、组合索引。
在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在索引组合的最左边。
mysql 组合索引“最左前缀”规则,简单的理解就是只从最左面的开始组合。查询条件中没有使用组合索引最左列列的语句,不会使用到索引。
MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。在以通配符%和_开头作查询时,MySQL不会使用索引。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
explain sql1 可以分析sql语句的执行情况,进而对sql语句进行优化。
组合索引-最左优先原则(最左原则)

MySQL有一个复合索引:INDEX(`a`, `b`, `c`),以下查询能用上索引。
A、select * from users where a = 1 and b = 2
B、select * from users where b = 2 and a = 1
C、select * from users where a = 2 and c = 1
第二项会由Mysql查询器优化。

https://blog.csdn.net/u014590757/article/details/79590561

3.数据库拆分:分库、分表、分区

单表的数据量限制,当单表数据量到一定条数之后数据库性能会显著下降。分库减少单台数据库的压力。有些数据表适合通过主键进行散列分库分表。

数据库的垂直切分和水平切分
数据切分可以是物理上的,对数据通过一系列的切分规则将数据分布到不同的DB服务器上,通过路由规则访问特定的数据库,降低单台机器的负载压力。

数据切分也可以是数据库内的,对数据通过一系列的切分规则,将数据分布到一个数据库的不同表中,比如将article表分为article_001,article_002等子表,若干个子表水平拼合有组成了逻辑上一个完整的article表。比如article表中有5000w条数据,在这个表中增加(insert)一条新的数据,insert后数据库会针对这张表重新建立索引,5000w行数据建立索引的系统开销还是不容忽视的。如果将这个表分成100 个子表,每个子表里边就只有50万行数据,向一张只有50w行数据的table中insert数据后建立索引的时间就会呈数量级的下降,提高了DB的并发量。分表的好处还有诸如写操作的锁操作等。

分库降低了单点机器的负载;分表提高了数据操作的效率,尤其是Write操作的效率。

垂直水平切分

分库分表后id主键处理的方法:
A、单库生成自增 id
B、设置数据库 sequence 或者表自增字段步长
C、UUID
D、最佳方法是snowflake 算法

snowflake 算法是 twitter 开源的分布式id生成算法,采用 Scala 语言实现,是把一个 64 位的 long 型的 id,1 个 bit 是不用的,用其中的 41 bit 作为毫秒数,用 10 bit 作为工作机器 id,12 bit 作为序列号。
1 bit 不用,是因为二进制里第一个 bit 为如果是 1,那么都是负数,但是我们生成的 id 都是正数,所以第一个 bit 统一都是 0。 41 bit:表示的是时间戳,单位是毫秒。41 bit 可以表示的数字多达 2^41 – 1,也就是可以标识 2^41 – 1 个毫秒值,换算成年就是表示69年的时间。 10 bit:记录工作机器 id,代表的是这个服务最多可以部署在 2^10台机器上哪,也就是1024台机器。但是 10 bit 里 5 个 bit 代表机房 id,5 个 bit 代表机器 id。意思就是最多代表 2^5个机房(32个机房),每个机房里可以代表 2^5 个机器(32台机器)。 12 bit:这个是用来记录同一个毫秒内产生的不同 id,12 bit 可以代表的最大正整数是 2^12 – 1 = 4096,也就是说可以用这个 12 bit 代表的数字来区分同一个毫秒内的 4096 个不同的 id。

4.读写分离
读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

例如用户信息,每个用户都有系统内部的一个userid,与userid对应的还有用户名。那么如果分库分表的时候单纯通过userid进行散列分库,那么根据登录名来获取用户的信息,就无法知道该用户处于哪个数据库中。或许可以维护一个username—-userid的映射关系,先查询到userid再根据userid的分库分表规则到对应库的对应表来获取用户的记录信息。但这个映射关系的条数本身也是个瓶颈,原则上是没有减少单表内数据的条数,算是一个单点。并且也要维护这个映射关系和用户信息的一致性。最大一个原因,其实用户信息实际是一个读大于写的库,所以对用户信息拆分还是有一定局限性的。

对于这类读大于写并且数据量增加不明显的数据库,推荐采用读写分离+缓存的模式,用户注册、修改信息、记录登录时间、登录IP、修改密码,这些是写操作,但是这些操作次数都是很小的,所以整个数据库的写压力是很小的。读写分离首要解决的就是将经常变化的数据的拆分,比如:用户登录时间、记录用户登录IP,这类信息可以单独独立出来,记录在持久化类的缓存中(可靠性要求并不高,登陆时间、IP丢了就丢了,下次来了就又来了)。

主库负责写数据、读数据。读库仅负责读数据。每次有写库操作,同步更新cache,每次读取先读cache再读DB。写库就一个,读库可以有多个,例如Oracle采用dataguard来负责主库和多个读库的数据同步。

参考
https://www.cnblogs.com/panxuejun/p/5887118.html
https://www.jianshu.com/p/65e8a31fbab8
https://www.cnblogs.com/alvin_xp/p/4162249.html

MySQL数据库则可以利用MySQL主从配置,实现读写分离,既起到备份作用又可以减轻数据库的读写的压力。读写分离的基本原理是让“主”数据库处理事务性增、改、删操作,“从”数据库处理查询操作。利用数据库复制把事务性操作导致的变更从主数据库同步到从数据库。写库有一个,读库可以有多个,采用日志同步的方式实现主库和多个读库的数据同步。

Mysql 的 Replication 是一个异步的复制过程,从一个MySQL节点(称为Master)复制到另一个MySQL节点(称Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(SQL 线程和 I/O 线程)在 Slave 端,另外一个线程(I/O 线程)在 Master 端。
要实现 MySQL 的 Replication ,首先要打开 Master 端的 Binary Log,整个复制过程实际上就是 Slave 从 Master 端获取日志,然后在自己身上完全顺序的执行日志中所记录的各种操作。
MySQL的Replication原理有以下结论和推论:
* 每个从仅可以设置一个主。
* 主在执行sql之后,记录二进制log文件(bin-log)。
* 从连接主,并从主获取bin-log,存于本地relay-log,并从上次记住的位置起执行sql,一旦遇到错误则停止同步。
* 主从间的数据库不是实时同步。
* 如果主从的网络断开,从会在网络正常后,批量同步。
* 如果对从进行修改数据,那么很可能从在执行主的bin-log时出现错误而停止同步,这个是很危险的操作。所以一般情况下,非常小心的修改从上的数据。
* 一个衍生的配置是双主,互为主从配置,只要双方的修改不冲突,可以工作良好。
* 如果需要多主的话,可以用环形配置,这样任意一个节点的修改都可以同步到所有节点。

4.数据缓存
大型网站为了应对大量的并发访问,除了在业务端实现分布式负载均衡,还要减少数据库的连接,例如采用优秀的代码框架进行代码优化,采用优秀的数据缓存技术如memcached,如果资金丰厚还可以架设服务器群来分担主数据库的压力。

建表原则

1、定长与变长分离
如id int占4个字节,char(4)占4个字节,即每一个单元值占的字节都是固定的。而varchar,text,blob 变长字段适合单放另一张表,在其中使用主键与核心表关联。
2、常用字段和不常用字段向分离
结合具体的业务分析字段的查询场景,把查询频度低的字段单拆出来。
3、把1对多等需要关联统计的数值添加冗余字段。
例如论坛中每个栏目显示今日发帖数目存储下来,不需要每次查询都再统计。

sql语句优化

计算机存储空间分为:寄存器、高速缓存、内存、交换区(外部存储虚拟化)、硬盘以及其他的外部存储。从寄存器开始到硬盘读写速度是从快到慢依次递减。对于批量记录如果一条一条的连接数据库进行操作,耗费的时间非常恐怖。调优的环节之一,则是减少与数据库交互的次数,将多条查询、插入、更新合并为交互一次,也就是批操作。多次处理的操作交给主程序在内存中进行处理,内存中处理的速度要快上很多。

批量插入语句
将插入语句进行拼接,多条插入语句拼接成一条插入语句,与数据库交互一次执行一次。

//如果字符串太长,则需要配置下MYSQL,在mysql 命令行中运行 :
set global max_allowed_packet=2*1024*1024*10
//批量插入语句
insert into 表名(字段名1,字段名2)values(值a1,值b1), (值a2,值b2)

批量更新语句

UPDATE categories SET 
display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
END, 
title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3);

批量查询和删除

select * from tableName where id in (1,2,3,4) order by id
delete from tableName where id in(1,2,3,4,5,6)

sql语句取3000条记录

#Oracle :
select * from table where rownum between 1 and 3000
select * from table rownum<3001
#MySql:
select * from table limit 1 to 3000

插入记录隐藏列名时注意表结构中字段顺序

例如以下插入,是按字段顺序对应,不是按字段名对应。
insert into b_subuser_deleted_table SELECT * from b_subuser_table WHERE id=1

全文检索

全文检索是将非结构化数据中的一部分信息提取出来,重新组织,使其变得有一定结构,然后对此有一定结构的数据进行搜索,从而达到搜索相对较快的目的。
这部分从非结构化数据中提取出然后重新组织的信息,我们称之索引。这种先建立索引,再对索引进行搜索的过程就叫全文检索(Full-text Search)。

Lucene是apache下的一个开源的全文检索引擎工具包(类库)。它的目的是为软件开发人员提供一个简单易用的工具包,以方便的在目标系统中实现全文检索的功能。Lucene提供了完整的查询引擎和索引引擎,部分文本分析引擎。

更新依据的字段只能是字符串类型,StringField。

Document doc=new Document();
doc.add(new StringField("id","abcd",Field.Store.YES));
indexWriter.updateDocument(new Term("id","abcd"),doc);

Field.Store.YES或者NO是用来决定分词前的原内容是否存储。

lucene一个field多个条件查询
https://blog.csdn.net/guiyecheng/article/details/56484049

用lucene实现在一个(或者多个)字段中查找多个关键字
https://www.cnblogs.com/xudong-bupt/archive/2013/05/08/3065297.html

Elastic Search 概述(一)
https://blog.csdn.net/yezonggang/article/details/80064394

Elasticsearch的不足
http://dockone.io/article/3655

MongoDB

MongoDB将信息存储在BSON(Binary JSON)中,对海量数据存储有着很明显的优势。并且当需要一个无模式或模式灵活的数据结构时,MongoDB是一个不错的选择。MongoDB对数据结构的更改相对轻松和宽容,这是NoSQL解决方案的卖点。

只创建记录而不定义结构增加了MongoDB的灵活性。

MongoDB缺点:主要是无事物机制!

mongodb命令行group分组和java代码中group分组
https://www.2cto.com/database/201607/528258.html
MongoDB Counting and Grouping
http://wiki.summercode.com/mongodb_aggregation_functions_and_ruby_counting_and_grouping
mongodb和spring集成中MongoTemplate的总结是使用方法
http://blog.csdn.net/ruishenh/article/details/12842331
MongoTemplate:增加数据。根据id查询,条件查询,聚合group 和 distinct
http://147175882.iteye.com/blog/1565378
Spring Data JPA – Reference Documentation
https://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/#repositories.query-methods.query-creation
Spring Data for Mongo 介绍
http://colobu.com/2014/12/03/Spring-Data-for-Mongo-Introduction/

MongoDB文档

https://docs.mongodb.com/
https://docs.mongodb.com/manual/
https://docs.mongodb.com/manual/crud/
MongoDB教程
http://www.yiibai.com/mongodb/mongodb_indexing.html
MongoDB 基础知识
https://segmentfault.com/a/1190000002694268?_ea=184402
Mongoose学习参考文档——基础篇
http://ourjs.com/detail/53ad24edb984bb4659000013
Spring Boot中使用MongoDB数据库
http://www.tuicool.com/articles/QjmIFnf
MongoDB学习笔记—Linux下搭建MongoDB环境
http://www.cnblogs.com/hanyinglong/archive/2016/07/21/5690611.html
Install MongoDB Community Edition on OS X
https://docs.mongodb.com/manual/tutorial/install-mongodb-on-os-x/
MongoDB Tutorial
https://www.tutorialspoint.com/mongodb/index.htm

MySQL与MongoDB的操作对比
http://blog.csdn.net/suxinpingtao51/article/details/40981621
对比MySQL,你究竟在什么时候更需要MongoDB
http://www.csdn.net/article/2014-03-06/2818652-when-use-mongodb-rather-mysql

安装mongodb

https://docs.mongodb.com/manual/tutorial/install-mongodb-on-ubuntu/
https://docs.mongodb.com/manual/tutorial/install-mongodb-enterprise-on-ubuntu/

MongoDB是免费的,有Enterprise和Community两个版本,在文档中可以看到差异主要体现在安全认证、系统认证等方面。

## Enterprise-Only Options:
#auditLog:
#snmp:

http://www.178linux.com/13492

修改端口等配置

vi /etc/mongod.conf
#修改端口和监听IP
# network interfaces
net:
  port: 27017
  #bindIp: 127.0.0.1
  #允许本机所有ip
  bindIp: 0.0.0.0

在ubuntu16.04中设置开机启动mongodb服务
sudo systemctl enable mongod
https://www.cnblogs.com/weschen/p/7395667.html

常用查询

./mongod -version
mongo #默认参数为 localhost:27017
mongo localhost:14000
help
show dbs
use dbname1
db.auth('username','password');
show collections
db.collectionName1.find()
#查看索引
db.collectionName1.getIndexes()
#查看版本
db.version()
#创建索引
db.collectionName1.ensureIndex({"_id":1})

常用修改

db.collectionName1.update({"_id" : ObjectId("58d0f91a7e113c661b0fc997")},{$set:{"code":"11112222"}})
db.collectionName1.insert({"name" : "abc","alias" : "123"})
#修改字段名
db.collectionName1.update({}, {$rename : {"oldColumnName" : "newColumnName"}}, false, true)

mongo3.0备份还原数据

#备份
~/mongodb/bin/mongodump -h localhost:27017 -d my_db -o ./
#还原
~/mongodb/bin/mongorestore -h localhost:27017 -d my_db ~/mongo-db/20170705/my_db --drop

mongoDB 2.0 文件目录

配置文件   /etc/mongod.conf 
数据库日志 /var/log/mongodb
数据库文件 /var/lib/mongodb
#关闭和启动数据库服务
sudo service mongod stop
sudo service mongod start

日志中:”nscanned” 表示查询了多少个文档。

MongoDB学习笔记(索引)
http://www.cnblogs.com/stephen-liu74/archive/2012/08/01/2561557.html

手动启动Mongo

cd  ~/mongodb/bin/
./mongod -dbpath=../data -logpath=../log/mongodb.log --logappend&
~/mongodb/bin/mongod -config  ~/mongodb/etc/mongodb.conf
./mongod -config ../etc/mongodb.conf --fork
#mongodb config file
dbpath=../data/db
logpath=../mongodb.log
logappend = true
port = 14000
fork = false
httpinterface=true

关闭数据库

# mongo localhost:27017
> use admin
> db.shutdownServer()

查询示例:

操作语句
修改多行
db.getCollection('operateRecord').update({"operateType":"门禁控制"},{"$set":{"operateType":"door"}},{multi: true})
删除列
db.User.update({},{$unset:{'operateTypel':''}},false, true)

常用查询 
db.getCollection('devices').find({name:/^mic/}).sort({display_name:1})
db.getCollection('devices').find({name:/^mic/,display_name:/^圆桌/},{_id:1}).sort({mic_router_id:1,name:1})
db.getCollection('devices').find({name:/^mic/,display_name:/^二层/},{_id:1}).sort({mic_router_id:1,name:1})
db.getCollection('alarmRecord').find({stateTime:{'$lt':new Date('2017/03/15')}}).count()
db.getCollection('alarmRecord').remove({stateTime:{'$lt':new Date('2017/03/16')}})
db.getCollection('devicegroups').find({tags:[‘aaaa’]})  数组中查找
db.getCollection('devices').find({name:/^decoder/}) 所有网络音频编解码器
db.getCollection('devices').find({device_type:ObjectId("585601376868180438aea058")})   所有门禁
db.getCollection('devices').find({device_type:ObjectId("59015c66dece7169709a02f4"))}) 所有安防摄像头
db.getCollection('devices').find({name:{$in:["door_2_40","door_2_41"]}})
db.Alert.find({collectorId:{$exists:true}})
#查询字段存在
db.getCollection('Unit').find({parametervalue:{$exists:true}},{}).sort({_id:-1})

添加
db.UnitType.insert({"name" : "CarelPJ0003","alias" : "qhtf-PJ0003"})

修改
db.UnitType.update({"_id" : ObjectId("583cca533b5e105d024c79f7")},{$set:{"name":"YIDE-HP 020"}})
db.UnitType.update({"_id" : ObjectId("599a3e7e80577cc54e0f4eb4")},{$unset:{"template":"lsqk-1"}})
db.Alert.update({collectorId:{$exists:true}},{$set:{"unitId":ObjectId("000000000000000000000000")}},false,true)

统计分路器个数
db.getCollection('devices').group({
    key: {mic_router_id: 1},
    cond: {name:/^mic/},
    reduce: function (curr, result) {
        result.count++;
    },
    initial: {
        count: 0
    }
});

按类型列出设备 //http://www.xker.com/page/e2015/07/205772.html
db.getCollection('devices').group({
    key: {device_type: 1},
    reduce: function (curr, result) {
        result.count++;
        result.ids.push(curr._id);
    },
    initial: {
        count: 0,
        ids:[]
    }
});

Lookup多表关联处理
db.getCollection('devicetypes').aggregate([{$lookup:{from: "devices", localField:"_id", foreignField: "device_type", as: "devices"}}
,{$project:{_id:1,name:1,devices:1}}
,{$group: {_id: "$_id", deviceTypeName: {$first:"$name"}, deviceIds:{$push:"$devices._id"}, count:{$sum: 1} }}
])
或者
db.getCollection('devices').aggregate([{$lookup:{from: "devicetypes", localField:"device_type", foreignField: "_id", as: "deviceType"}}
,{$group: {_id:{_id:"$deviceType._id", display_name:"$deviceType.display_name", name:"$deviceType.name"}, deviceIds:{$push:"$_id"}, count:{$sum: 1} }}
])
或者
db.getCollection('devices').aggregate([{$lookup:{from: "devicetypes", localField:"device_type", foreignField: "_id", as: "deviceType"}}
,{$group: {_id:"$deviceType._id", display_name:{$first:"$deviceType.display_name"}, name:{$first:"$deviceType.name"}, deviceIds:{$push:"$_id"}, count:{$sum: 1} }}
])

各个机组的最新的监测记录 https://docs.mongodb.com/manual/reference/operator/aggregation/max/
db.getCollection('RealtimeLog').aggregate(
   [
        {$match: {"unitId": {"$in":[ObjectId("596dac89c13a10339936610b")]}}},
     {
       $group:
         {
           _id: "$unitId",
          maxId: { $max: "$_id" }
         }
     }
   ]
)

统计各个unitId的条数
db.getCollection('Alert').aggregate([{$group : {_id : "$unitId", num_tutorial : {$sum : 1}}}])

forEach示例
db.CollectorGarbage.find({address:/中间的文字/}).forEach(function(doc) {
  doc._id=doc.code;
  doc.code=null;
  db.Collector.insert(doc);
});

db.devices.find({name:/^micRouter/}).forEach(function(doc) {
  var singles=[2,5,12,14,16,18,20,25,28,31,34];
  var number=doc.name.replace("micRouter","");
  var single=false;
  var n=parseInt(doc.number);
  if(singles.indexOf(n)>=0){
    single=true;
  }
  db.devices.update({"_id" : doc._id},{$set:{"single":single,"number": NumberInt(number)}})
});

db.devices.find({name:/^micRouter/}).sort({number:1}).forEach(function(doc) {
  var micCode= parseInt(doc.number * 4 - 3 + 10000);
  var newdoc={
      "device_type" : ObjectId("583cf97345fa750587159ce7"),
      "mic_router_id" : ""+doc.number,
      "name" : "mic"+micCode,
      "remote_id" : ""+micCode
  };
  db.devices.insert(newdoc);

  if(!doc.single){
  micCode+=2;
  var newdoc={
    "device_type" : ObjectId("583cf97345fa750587159ce7"),
    "mic_router_id" : ""+doc.number,
    "name" : "mic"+micCode,
    "remote_id" : ""+micCode
  };
  db.devices.insert(newdoc);
  }
});

db.devices.find({device_type:ObjectId("583cf97345fa750587159ce7")}).forEach(
   function(item){                 
       db.devices.update({"_id":item._id},{"$set":{"display_name":"话筒"+item.display_name.replace("mic","")}},false,true) 
    }
)

数据库加密码

use admin
db.auth("userForAdmin","passForAdmin");
#use 即可新建数据库
use newdb1
db.createUser({user: "userForNewDB1", pwd: "123456", roles: [{ role: "dbOwner", db: "newdb1" }]})

distinct

distinct 以数组的形式返回集合中指定字段的不重复值
db.collection_name.distinct(field,query,options)
field -----指定要返回的字段(string)
query-----条件查询(document)
options-----其他的选项(document)

引用

在user表中引用role表中的记录

{
"role":{"$ref":"role","$id":{"$oid":"59528c49b320c1ff2a34cd5a"}}
}

Mongodb适用的场景

高写入负载
默认情况下,Mongodb更侧重高数据写入性能,而非事务安全。Mongodb很适合业务系统中有大量“低价值”数据的场景,应当避免在高事务安全性的系统中使用Mongodb,除非能从架构设计上保证事务安全。

高可用性
Mongodb的复副集(Master-Slave)配置非常方便,此外Mongodb可以快速响应的处理单节点故障,自动安全的完成故障转移。这些特性使得Mongodb能在一个相对不稳定(如云主机)的环境中保持高可用性。

数据量很大或者未来很大
MySQL依赖数据库自身的特性,完成数据的扩展是较困难的事。在MySQL中当一个单达表到5-10GB时会出现明显的性能降级,此时需要通过数据的水平和垂直拆分以及库的拆分完成扩展,使用MySQL通常需要借助驱动层或代理层完成这类需求。而MongoDB内建了多种数据分片(Sharding)的特性,可以很好的适应大数据量的需求。选择MongoDB的另一个原因是它具有设置复制环境,内置分片和自动选择方面的功能。在MongoDB中设置复制环境很容易,自动选择过程允许从数据库在主数据库故障的情况下接管。内置分片允许简单的横向扩展。

基于位置的数据查询
MongoDB支持二维空间索引,因此可以快速及精确的从指定位置获取数据。

表结构不明确,且数据不断变大
在传统RDBMS中,增加一个字段会锁住整个数据库/表,或者在执行一个重负载的请求时会明显造成其它请求的性能降级。通常发生在数据表大于1G的时候(当大于1TB时更甚)。因MongoDB是文档型数据库,为非结构化的文档增加一个新字段是很快速的操作,并且不会影响到已有数据。另外一个好处当业务数据发生变化时,将不再需要由DBA修改表结构。

没有DBA支持
如果没有专职的DBA,并且准备不使用标准的关系型思想(结构化、连接等)来处理数据,那么MongoDB将会是首选。MongoDB对于对像数据的存储非常方便,类可以直接序列化成JSON存储到MongoDB中。 但是需要先了解一些最佳实践,避免当数据变大后,由于文档设计问题而造成的性能缺陷。

BillRun – 基于MongoDB的帐单系统
BillRun是由Ofer Cohen推出开源账单系统,采用MongoDB做为数据存储。这套账单系统被以色列一家增速最快的电信运营商采用,每月处理5亿条通信记录,Ofer在Slideshare上说明了具体利到了MongoDB的哪些特性:
弱数据结构的特点,使得BillRun能很快的支持新的CDR(通讯记录)类型。这个特性使文档型数据库很适用于快速发展、业务需求不确定的系统中。
BillRun仅使用了一个Collection,已经管理了数TB的文档数据,并且没有遇到由结构变更、数据爆发式增长的带来的限制和问题。
replicaSet副本集特性使建立更多的数据中心DRP变得更轻松。
内建的Sharding分片特性避免系统在数据增长的过程中遇到性能瓶颈。
每秒钟2000条通信记录的插入,MongoDB在架构设计上很好的支持了高负载的数据写入。并且可以使用findAndModify(相对缓慢)完成基础的事务特性,并且通过应用层面的支持,实现双段式提交。

缓存数据库

缓存的类型

缓存是使用内存保存数据。

  • 本地缓存:本地缓存是进程内存中的缓存,可以用LRU Map、Ehcache实现,单机容量较小,不能扩展。
  • 分布式缓存:用来应付较大数据量,水平扩展方便。需要远程请求,性能相对低。
  • 多级缓存:实际业务中采用多级缓存。本地缓存保存访问频率高的热点数据,其它热点数据放于分布式缓存。

淘汰策略

对超过内存容量的缓存数据踢除。

  • FIFO:淘汰最早
  • LRU:淘汰最近最少使用
  • LFU:淘汰最近最低频使用

Memcached

Memcached,可以简称为MC,是高性能分布式内存缓存服务器。

  • 多线程异步IO模式处理请求
  • 简单key-value结构,key不超过250B,value不超过1MB
  • 可以设计数据失效期,延迟失效策略,即再次使用数据时检查。key最长失效周期是30天
  • 只支持内存,不支持持久化。
  • 不支持主从同步

通过 Slab机制管理内存,主要解决了频繁malloc、free产生碎片的问题。MC的问题有钙化问题,预热以防止雪崩问题等。

Redis

Redis:Remote Dictionary Server 远端字典服务,可用作高速缓存、key-value数据库和消息队列代理

  • 单线程模式处理请求,非阻塞异步事件处理机制,缓存数据都是内存操作,IO时间不会太长,单线程可以避免上下文切换开销。
  • 支持持久化,所以也可以用作NoSQL数据库。提供两种持久化策略:RDB快照和AOF日志。
  • 支持多种数据类型,String/List/Hash/Set/Sorted Set
  • 提供主从同步(master-slave)机制、cluster集群部署能力,能提供高可用服务。

Redis的功能

  • bitmap
  • hyperLogLog
  • geospatial
  • pub/sub
  • pipeline
  • lua脚本
  • 非完全事务

Redis单个value的最大限制是1GB,可以用来实现很多功能,例如用它的List来做FIFO双向链表实现一个轻量级的高性能消息队列服务,用它的Set可以做高性能的tag系统等。
Redis把整个数据库统统加载在内存当中进行操作,定期通过异步操作把数据flush到硬盘上进行保存。因为是纯内存操作,Redis的性能非常出色,每秒可以处理超过10万次读写操作,是已知性能最快的Key-Value DB。
Redis可以对存入的Key-Value设置expire时间。
Redis的主要缺点是数据库容量受到物理内存的限制,不能用作海量数据的高性能读写,因此Redis适合的场景主要在较小数据量的高性能操作和运算上。
Redis为了达到最快的读写速度将数据都读到内存中,并通过异步的方式将数据写入磁盘。所以redis具有快速和数据持久化的特征。如果不将数据放在内存中,磁盘I/O速度为严重影响redis的性能。在内存越来越便宜的今天,redis将会越来越受欢迎。如果设置了最大使用的内存,则数据已有记录数达到内存限值后不能继续插入新值。
Redis是单进程单线程的,redis利用队列技术将并发访问变为串行访问,消除了传统数据库串行控制的开销。
Redis支持主从的模式。原则上Master会将数据单向同步到Slave,Slave启动时会连接Master来同步数据,这是一个典型的分布式读写分离模型。我们可以利用Master来插入数据,Slave提供检索服务,这样可以有效减少单个机器的并发访问数据。通过增加Slave DB的数量,读的性能可以线性增长。为了避免Master DB的单点故障,集群一般都会采用两台Master DB做双机热备,所以整个集群的读和写的可用性都非常高。而读写分离架构的缺陷在于,不管是Master还是Slave,每个节点都必须保存完整的数据,这样在数据量很大的情况下,集群的扩展能力还是受限于单个节点的存储能力,对于写密集(Write-intensive)类型的应用,读写分离架构并不适合。

缓存常见问题

  • 缓存更新
  • 缓存不一致
  • 缓存穿透:典型场景是恶意请求实际不存在的数据,缓存中不存在,继而访问数据库。解决方式有缓存空对象、bloomfilter过滤器检测数据存在性。
  • 缓存击穿:主要场景是热点key失效,此时伴随大量请求。解决方式有互斥更新、随机退避、差异失效时间。
  • 缓存雪崩:典型原因是缓存crash。解决方式有:快速失败熔断、主从模式、集群模式。

Redis中存储对象

根据数据量级别,如果是存储百万级的大数据对象,建议采用存储序列化对象方式。如果是少量的数据级对象,或者是数据对象字段不多,建议采用JSON转换成String方式。毕竟redis对存储字符类型优化的非常好。

Redis命令

docker exec -it 容器ID /bin/sh
redis-cli
AUTH 密码
HGETAll EQP:INFO:EQPID_4886
HGET EQP:INFO:EQPID_4886 "\xac\xed\x00\x05t\x00\bphoneDad"
HSET EQP:INFO:EQPID_4886 "\xac\xed\x00\x05t\x00\bphoneDad" "\xac\xed\x00\x05t\x00\x0b16695010385-1234"

redis 配置文件 https://blog.csdn.net/zhywbp/article/details/76528500

Mysql语句

删除重复记录

删除重复记录个数是2的:

delete list_table from list_table,(select max(id) as id from list_table group by href having count(*)>1) as t2 where list_table.id=t2.id

去掉字段中的换行,制表符

\n也可以是用char(10)代替,\r可以用char(13)代替

update list_table set briefDesc = replace(briefDesc ,"\r\n","")
update list_table set briefDesc = replace(briefDesc ,"\t","")
UPDATE list_table SET briefDesc = REPLACE(REPLACE(briefDesc, CHAR(10), ''), CHAR(13),'')

重置auto_increment字段的起始值

有些表只用来统计,其id不会作为外键,所以可以自由修改它的id。
通过命令ALTER TABLE share_table AUTO_INCREMENT=605 或通过phpMyAdmin操作表选项修改,都不成功。
后来先去掉id字段的auto_increment属性,再添加上它,就会自动从最大的id开始了。

用选择的数据创建临时表

创建临时表可以简化一些复杂的sql语句

create table temp_table SELECT articleId,count(*) as count FROM `share_table` group by articleId
insert into temp_table SELECT articleId,count(*) as count FROM `share_table` group by articleId

联合update

UPDATE list_table A, temp_table B
SET A.shareTimes=B.count
WHERE A.id=B.articleId

Could not connect: Access denied for user ‘root’@’localhost’ (using password: YES)

修改root密码

update user set password=password('abc') where user='root'

重启mysql

查询index

 show index from list_table
 alter table drop index xxx;

表 is marked as crashed and should be repaired

在phpmyadmin中选中库再在表的列表中选择对应表名,再选择选中项中的修复表,执行修复。可能会遇到需要修改表文件的组和拥有者。

修改数据库密码

进入mysql库

UPDATE user SET password=PASSWORD('123456') WHERE user='root'

Linux环境下修改MySQL端口

vi /alidata/server/mysql/my.cnf
修改port=3306
service mysql restart

授权访问者

#命令行登录
本地:mysql -uroot -p 
本地:mysql -u root -p, 回车后输入密码; 也可以p后不加空格,直接加密码,回车就登录了。
远程:mysql -hxx.xx.xx.xx -P 3306 -u -pxxx

#授权允许从ip为192.168.1.6的主机以root用户和password1作为密码,连接到mysql服务器。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.6' IDENTIFIED BY 'password1' WITH GRANT OPTION;
#修改生效并退出
FLUSH PRIVILEGES;
EXIT;

授权ip段,可以使用%,例如:192.168.1.%。

修改字段部分内容

update `list_table_test` set `thumbnailUrl`=concat('http://pic.cnblogs.com',substr(`thumbnailUrl`,18)) WHERE `thumbnailUrl` like 'http://www.cnblogs.com/pic.cnblogs.com/face/%'

SELECT replace(replace(href,'http://so.brogrammer.org/view_',''),'.aspx','') FROM `poetry_table` WHERE href like 'http://so.brogrammer.org/view_%'

工具

Navicat