Mysql

mysql索引类型

btree索引:mysql中普遍使用B+Tree索引,但在实现方式上又分为聚簇索引和非聚簇索引。
hash索引:

聚簇索引和非聚簇索引

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

mysql表类型

1.InnoDB:
InnoDB,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。

支持事务,InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。
不加锁读取
支持外键
支持行锁
不支持FULLTEXT类型的索引
DELETE 表时,是一行一行的删除
InnoDB 把数据和索引存放在表空间里面
跨平台可直接拷贝使用
InnoDB中必须包含AUTO_INCREMENT类型字段的索引
表格很难被压缩
InnoDB的主键范围更大,最大是MyISAM的2倍
innodb属于索引组织表,有两种存储方式,共享表空间存储和多表空间存储,两种存储方式的表结构和myisam一样,以表名开头,扩展名是.frm。如果使用共享表空间,那么所有表的数据文件和索引文件都保存在一个表空间里,一个表空间可以有多个文件,通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字,一般共享表空间的名字叫ibdata1-n。如果使用多表空间,那么每个表都有一个表空间文件用于存储每个表的数据和索引,文件名以表名开头,以.ibd为扩展名。

2.MyISAM:
MyISAM:是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,不支持外键。如果执行大量的select,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时的作用较大。

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

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

mysql中不要用blob等大字段

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

utf8mb4

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

int(11)

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

Mysql调优

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全文索引或是自己做一个索引(比如搜索关键词、标签)。

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

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

全文检索

全文检索是将非结构化数据中的一部分信息提取出来,重新组织,使其变得有一定结构,然后对此有一定结构的数据进行搜索,从而达到搜索相对较快的目的。
这部分从非结构化数据中提取出然后重新组织的信息,我们称之索引。这种先建立索引,再对索引进行搜索的过程就叫全文检索(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中设置复制环境很容易,自动选择过程允许从数据库在主数据库故障的情况下接管。内置分片允许简单的横向扩展。

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内建了多种数据分片的特性,可以很好的适应大数据量的需求。

基于位置的数据查询
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(相对缓慢)完成基础的事务特性,并且通过应用层面的支持,实现双段式提交。

数据库性能优化

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语句进行优化。
组合索引-最左原则
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操作的效率。

垂直水平切分

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对多等需要关联统计的数值添加冗余字段。
例如论坛中每个栏目显示今日发帖数目存储下来,不需要每次查询都再统计。

缓存数据库

Memcached

Memcached是高性能分布式内存缓存服务器,其本质上就是一个内存key-value数据库,但是不支持数据的持久化,服务器关闭之后数据全部丢失。Memcached仅支持简单的key-value结构的数据记录。Memcached使用C语言开发,在大多数像Linux、BSD和Solaris等POSIX系统上,只要安装了libevent即可使用。

http://www.cnblogs.com/Hondsome/p/5962144.html

Redis

Redis:Remote Dictionary Server 远端字典服务,由意大利人antirez(Salvatore Sanfilippo)开发的一款内存高速缓存数据库,该软件使用C语言编写,它的数据模型为key-value。可用作key-value数据库,也可以用作高速缓存和消息队列代理。
Redis是一个开源的key-value存储系统,与其它NoSQL相比,Redis支持丰富复杂的数据类型,包括:String/List/Hash/Set/Sorted Set。Redis单个value的最大限制是1GB,memcached只能保存1MB的数据。因此Redis可以用来实现很多有用的功能,例如用它的List来做FIFO双向链表实现一个轻量级的高性能消息队列服务,用它的Set可以做高性能的tag系统等。
Redis支持内存数据的持久化。提供两种主要的持久化策略:RDB快照和AOF日志。
Redis支持master-slave(主-从)模式应用。
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)类型的应用,读写分离架构并不适合。

https://blog.csdn.net/waeceo/article/details/78701397

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

数据库中间件

传统的应用连接数据库时采用直接对数据进行访问的架构模式,随着目前数据量不断的增大会遇以问题:
单个表数据量太大
单个库数据量太大
单台数据量服务器压力很大
读写速度遇到瓶颈
第一种解决方式:向上扩展(scale up) ,简单来说就是增加硬件性能。这种方式只能暂时解决问题。
第二种解决方式:水平扩展。增加机器,把数据库放到不同服务器上,在应用到数据库之间加proxy进行路由。

中间件与读写分离的区别

读写分离是中间件可以提供的一种功能,而中间件最主要的功能是分库分表。
按照中间件分库分表算法,比如将获取id=1的SQL将发送到DB1节点,由DB1这个MySQL节点负责解析和获取数据,并通过中间件返回给客户端。在读写分离结构中并没有分库分表规则,只能在众多读节点中load balance随机进行分发,它要求各个节点都要存放一份完整的数据。

https://www.jianshu.com/p/f94b10c43d55

分布式与集群的区别

提升效率的点不同:分布式是以缩短单个任务的执行时间来提升效率,集群则是通过提高单位时间内执行的任务数来提升效率。
分布式是指将不同的业务分布在不同的地方。集群指的是将几台服务器集中在一起实现同一业务。
分布式中的每一个节点,都可以做集群。而集群并不一定就是分布式的。
分布式组织比较松散,每一个节点都完成不同的业务,一个节点垮了这个业务就不可访问了。集群有组织性,一台服务器垮了其它的服务器可以顶上来。
访问的人多了可以做一个群集,前面放一个响应服务器,后面几台服务器完成同一业务,响应服务器根据负载均衡规则选择服务器完成。

迁移数据库文件后,有的表报错误

mysql table is marked as crashed and last (automatic?) repair failed
解决办法:

#停止mysql服务
service mysqld stop
#进入数据目录,修复
myisamchk -r -v -f 表名
service mysql start