首页  思维导图  详情

0401 - 集合框架

2021-04-18 09:48:22   2  举报





AI智能生成

数据库存储相关知识体系

架构师的技术栈

java

数据存储

面试

后端开发

作者其他创作

大纲/内容

FastDFS

FastDFS应用背景和原理介绍

FastDFS文件存储项目实战

FastDFS分布式部署实战

MongoDB

应用场景

“三高”需求： High performance - 对数据库高并发读写的需求。 Huge Storage - 对海量数据的高效率存储和访问的需求。 High Scalability && High Availability- 对数据库的高可扩展性和高可用性的需求。

共同特点： （1）数据量大 （2）写入操作频繁 （3）价值较低的数据、对事务性要求不高

具体的应用场景： 1）社交场景，使用 MongoDB 存储存储用户信息，以及用户发表的朋友圈信息，通过地理位置索引实现附近的人、地点等功能。 2）游戏场景，使用 MongoDB 存储游戏用户信息，用户的装备、积分等直接以内嵌文档的形式存储，方便查询、高效率存储和访问。 3）物流场景，使用 MongoDB 存储订单信息，订单状态在运送过程中会不断更新，以 MongoDB 内嵌数组的形式来存储，一次查询就能将 订单所有的变更读取出来。 4）物联网场景，使用 MongoDB 存储所有接入的智能设备信息，以及设备汇报的日志信息，并对这些信息进行多维度的分析。 5）视频直播，使用 MongoDB 存储用户信息、点赞互动信息等。

MongoDB简介

MongoDB是一个开源、高性能、无模式的文档型数据库，当初的设计就是用于简化开发和方便扩展，是NoSQL数据库产品中的一种。 它支持的数据结构非常松散，是一种类似于 JSON 的格式叫BSON，所以它既可以存储比较复杂的数据类型，又相当的灵活。 MongoDB中的记录是一个文档，它是一个由字段和值对（field:value）组成的数据结构。MongoDB文档类似于JSON对象，即一个文档认 为就是一个对象。字段的数据类型是字符型，它的值除了使用基本的一些类型外，还可以包括其他文档、普通数组和文档数组。

数据模型

Bson中，除了基本的JSON类型：string,integer,boolean,double,null,array和object，mongo还使用了特殊的数据类型。 这些类型包括date,object id,binary data,regular expression 和code

MySQL与MongoDB对比

MongoDB的特点

高性能

MongoDB提供高性能的数据持久性。特别是,对嵌入式数据模型的支持减少了数据库系统上的I/O活动。 索引支持更快的查询，并且可以包含来自嵌入式文档和数组的键。（文本索引解决搜索的需求、TTL索引解决历史数据自动过期的需求、地理位置索引可用于构建各种 O2O 应用）mmapv1、wiredtiger、mongorocks（rocksdb）、in-memory 等多引擎支持满足各种场景需求。Gridfs解决文件存储的需求。

高可用性

MongoDB的复制工具称为副本集（replica set），它可提供自动故障转移和数据冗余。

高扩展性

MongoDB提供了水平可扩展性作为其核心功能的一部分。 分片将数据分布在一组集群的机器上。（海量数据存储，服务能力水平扩展）从3.4开始，MongoDB支持基于片键创建数据区域。 在一个平衡的集群中，MongoDB将一个区域所覆盖的读写只定向到该区域内的那些片。

丰富的查询支持

MongoDB支持丰富的查询语言，支持读和写操作(CRUD)，比如数据聚合、文本搜索和地理空间查询等。

其他特点

如无模式（动态模式）、灵活的文档模型

基本常用命令

数据库操作

选择和创建数据库的语法格式

use 数据库名称

如果数据库不存在则自动创建

查看有权限查看的所有数据库命令

show dbs 或 show databases

查看当前正在使用的数据库命令

MongoDB默认的数据库为test，如果没有选择数据库，集合将存放在test数据库中

数据库的删除

db.dropDatabase()

集合操作

集合的显式创建

db.createCollection(集合名称)

集合的隐式创建

当向一个集合中插入一个文档的时候，如果集合不存在，则自动创建集合

查看当前库中的表

show tables 或者 show collections

集合的删除

db.collection.drop() 或 db.集合.drop()

文档基本CRUD

文档的插入

单个文档的插入

db.collection.insert()

批量插入

db.collection.insertMany()

文档的基本查询

db.collection.find(<query>, [projection])

查询所有

db.comment.find() 或 db.comment.find({})

投影查询

>db.comment.find({userid:"1003"},{userid:1,nickname:1})   { "_id" : "4", "userid" : "1003", "nickname" : "凯撒" }   { "_id" : "5", "userid" : "1003", "nickname" : "凯撒" }

文档的更新

db.collection.update(query, update，options)

覆盖修改

db.comment.update({_id:"1"},{likenum:NumberInt(1001)})

局部修改

db.comment.update({_id:"2"},{$set:{likenum:NumberInt(889)}})

批量修改

//默认只修改第一条数据 db.comment.update({userid:"1003"},{$set:{nickname:"凯撒2"}}) //修改所有符合条件的数据 db.comment.update({userid:"1003"},{$set:{nickname:"凯撒大帝"}},{multi:true})

列值增长的修改

db.comment.update({_id:"3"},{$inc:{likenum:NumberInt(1)}})

文档的删除

db.集合名称.remove(条件)

db.comment.remove({})

统计查询

db.collection.count(query, options)

分页列表查询

db.COLLECTION_NAME.find().limit(NUMBER).skip(NUMBER)

//第一页 db.comment.find().skip(0).limit(2) //第二页 db.comment.find().skip(2).limit(2) //第三页 db.comment.find().skip(4).limit(2)

排序查询

db.COLLECTION_NAME.find().sort({KEY:1}) 或 db.集合名称.find().sort(排序方式)

db.comment.find().sort({userid:-1,likenum:1})

正则的复杂条件查询

db.collection.find({field:/正则表达式/}) 或 db.集合.find({字段:/正则表达式/})

db.comment.find({content:/^专家/})

比较查询

db.集合名称.find({ "field" : { $gt: value }}) // 大于: field > value db.集合名称.find({ "field" : { $lt: value }}) // 小于: field < value db.集合名称.find({ "field" : { $gte: value }}) // 大于等于: field >= value db.集合名称.find({ "field" : { $lte: value }}) // 小于等于: field <= value db.集合名称.find({ "field" : { $ne: value }}) // 不等于: field != value

包含查询

db.comment.find({userid:{$in:["1003","1004"]}})

条件连接查询

$and:[ { },{ },{ } ]

db.comment.find({$and:[{likenum:{$gte:NumberInt(700)}},{likenum:{$lt:NumberInt(2000)}}]})

$or:[ { },{ },{ } ]

db.comment.find({$or:[ {userid:"1003"} ,{likenum:{$lt:1000} }]})

常用命令小结

索引-Index

单字段索引

MongoDB支持在文档的单个字段上创建用户定义的升序/降序索引，称为单字段索引（Single Field Index）； 对于单个字段索引和排序操作，索引键的排序顺序（即升序或降序）并不重要，因为MongoDB可以在任何方向上遍历索引。

复合索引

MongoDB还支持多个字段的用户定义索引，即复合索引（Compound Index）。 复合索引中列出的字段顺序具有重要意义。例如，如果复合索引由{ userid: 1, score: -1 } 组成，则索引首先按userid正序排序，然后 在每个userid的值内，再在按score倒序排序。

其他索引

地理空间索引（Geospatial Index）

为了支持对地理空间坐标数据的有效查询，MongoDB提供了两种特殊的索引： 返回结果时使用平面几何的二维索引和返回结果时使用球面几何的二维球面索引。

文本索引（Text Indexes）

MongoDB提供了一种文本索引类型，支持在集合中搜索字符串内容。这些文本索引不存储特定于语言的停止词 （例如“the”、“a”、“or”），而将集合中的词作为词干，只存储根词。

哈希索引（Hashed Indexes）

为了支持基于散列的分片，MongoDB提供了散列索引类型，它对字段值的散列进行索引。 这些索引在其范围内的值分布更加随机，但只支持相等匹配，不支持基于范围的查询。

索引的管理操作

索引的查看

db.collection.getIndexes()

默认_id索引：MongoDB在创建集合的过程中，在_id 字段上创建一个唯一的索引，默认名字为_id_ ，该 索引可防止客户端插入两个具有相同值的文档，您不能在_id字段上删除此索引。 注意：该索引是唯一索引，因此值不能重复，即_id 值不能重复的。在分片集群中，通常使用_id 作为片键。

索引的创建

db.collection.createIndex(keys, options)

索引的移除

db.collection.dropIndex(index)

index:指定要删除的索引。可以通过索引名称或索引规范文档指定索引。若要删除文本索引，请指定索引名称。

db.comment.dropIndexes()

删除spit集合中所有的索引

索引的使用

执行计划

分析查询性能（Analyze Query Performance）通常使用执行计划（解释计划、Explain Plan）来查看查询的情况， 如查询耗费的时间、是否基于索引查询等。

db.collection.find(query,options).explain(options)

涵盖的查询

当查询条件和查询的投影仅包含索引字段时，MongoDB直接从索引返回结果，而不扫描任何文档或将文档带入内存。这些覆盖的查询可以 非常有效。

MongoDB集群和安全

副本集-Replica Sets

简介

MongoDB中的副本集（Replica Set）是一组维护相同数据集的mongod服务。 副本集可提供冗余和高可用性，是所有生产部署的基础。

也可以说，副本集类似于有自动故障恢复功能的主从集群。通俗的讲就是用多台机器进行同一数据的异 步同步，从而使多台机器拥有同一数据的多个副本，并且当主库当掉时在不需要用户干预的情况下自动 切换其他备份服务器做主库。而且还可以利用副本服务器做只读服务器，实现读写分离，提高负载

冗余和数据可用性

复制提供冗余并提高数据可用性。通过在不同数据库服务器上提供多个数据副本，复制可提供一定级别 的容错功能，以防止丢失单个数据库服务器。 在某些情况下，复制可以提供增加的读取性能，因为客户端可以将读取操作发送到不同的服务上，在不 同数据中心维护数据副本可以增加分布式应用程序的数据位置和可用性。您还可以为专用目的维护其他 副本，例如灾难恢复，报告或备份。

MongoDB中的复制

副本集是一组维护相同数据集的mongod实例。副本集包含多个数据承载节点和可选的一个仲裁节点。 在承载数据的节点中，一个且仅一个成员被视为主节点，而其他节点被视为次要（从）节点。 主节点接收所有写操作。副本集只能有一个主要能够确认具有{w：“most”}写入关注的写入; 虽然在某 些情况下，另一个mongod实例可能暂时认为自己也是主要的。主要记录其操作日志中的数据集的所有 更改，即oplog。 辅助(副本)节点复制主节点的oplog并将操作应用于其数据集，以使辅助节点的数据集反映主节点的数据 集。如果主要人员不在，则符合条件的人员中将举行选举以选出新的主要人员。

主从复制和副本集区别

主从集群和副本集最大的区别就是副本集没有固定的“主节点”；整个集群会选出一个“主节点”，当其挂 掉后，又在剩下的从节点中选中其他节点为“主节点”，副本集总有一个活跃点(主、primary)和一个或多 个备份节点(从、secondary)。

副本集的三个角色

两种类型

主节点类型：数据操作的主要连接点，可读写

次要（辅助、从）节点类型：数据冗余备份节点，额可以读或选举

三种角色

主要成员（Primary）：主要接收所有写操作

副本成员（Replicate）：从主节点通过复制操作以维护相同的数据集，即备份数据，不可写操作，但可 以读操作（但需要配置）。是默认的一种从节点类型。

仲裁者（Arbiter）：不保留任何数据的副本，只具有投票选举作用。当然也可以将仲裁服务器维护为副 本集的一部分，即副本成员同时也可以是仲裁者。也是一种从节点类型。

主节点的选举原则

主节点选举的触发条件

1、主节点故障 2、主节点网络不可达（默认心跳信息为10秒） 3、人工干预（rs.stepDown(600)）

选举规则是根据票数来决定谁获胜： 票数最高，且获得了“大多数”成员的投票支持的节点获胜。“大多数”的定义为：假设复制集内投票成员数量为N，则大多数为 N/2 + 1。 例如：3个投票成员，则大多数的值是2。当复制集内存活成员数量不足大多数时，整个复制集将无法选举出Primary，复制集将无法提供写服务，处于只读状态。 若票数相同，且都获得了“大多数”成员的投票支持的，数据新的节点获胜。数据的新旧是通过操作日志oplog来对比的。

分片集群-Sharded Cluster

分片概念

分片（sharding）是一种跨多台机器分布数据的方法， MongoDB使用分片来支持具有非常大的数据集和高吞吐量操作的部署。 换句话说：分片(sharding)是指将数据拆分，将其分散存在不同的机器上的过程。有时也用分区(partitioning)来表示这个概念。 将数据分散到不同的机器上，不需要功能强大的大型计算机就可以储存更多的数据，处理更多的负载。 有两种解决系统增长的方法：垂直扩展和水平扩展。 垂直扩展意味着增加单个服务器的容量，例如使用更强大的CPU，添加更多RAM或增加存储空间量。可用技术的局限性可能会限制单个机器对于给定工作负载而言足够强大。此外，基于云的提供商基于可用的硬件配置具有硬性上限。结果，垂直缩放有实际的最大值。

分片集群包含的组件

分片（存储）：每个分片包含分片数据的子集。每个分片都可以部署为副本集。

mongos（路由）：mongos充当查询路由器，在客户端应用程序和分片集群之间提供接口。

config servers（“调度”的配置）：配置服务器存储群集的元数据和配置设置。 从MongoDB 3.4开始，必须将配置服务器部署为副本集（CSRS）。

分片集群的架构目标

两个分片节点副本集（3+3）+ 一个配置节点副本集（3）+ 两个路由节点（2）

安全认证

默认情况下，MongoDB实例启动运行时是没有启用用户访问权限控制的，也就是说，在实例本机服务 器上都可以随意连接到实例进行各种操作，MongoDB不会对连接客户端进行用户验证，这是非常危险 的。

mongodb官网上说，为了能保障mongodb的安全可以做以下几个步骤： 1）使用新的端口，默认的27017端口如果一旦知道了ip就能连接上，不太安全。 2）设置mongodb的网络环境，最好将mongodb部署到公司服务器内网，这样外网是访问不到的。公司内部访问使用vpn等。 3）开启安全认证。认证要同时设置服务器之间的内部认证方式，同时要设置客户端连接到集群的账号密码认证方式。

为了强制开启用户访问控制(用户验证)，则需要在MongoDB实例启动时使用选项--auth 或在指定启动 配置文件中添加选项auth=true 。

1）启用访问控制： MongoDB使用的是基于角色的访问控制(Role-Based Access Control,RBAC)来管理用户对实例的访问。 通过对用户授予一个或多个角色来控制用户访问数据库资源的权限和数据库操作的权限，在对用户分配 角色之前，用户无法访问实例。 在实例启动时添加选项--auth 或指定启动配置文件中添加选项auth=true 。

2）角色： 在MongoDB中通过角色对用户授予相应数据库资源的操作权限，每个角色当中的权限可以显式指定， 也可以通过继承其他角色的权限，或者两都都存在的权限。

3）权限： 权限由指定的数据库资源(resource)以及允许在指定资源上进行的操作(action)组成。 1. 资源(resource)包括：数据库、集合、部分集合和集群； 2. 操作(action)包括：对资源进行的增、删、改、查(CRUD)操作。 在角色定义时可以包含一个或多个已存在的角色，新创建的角色会继承包含的角色所有的权限。在同一 个数据库中，新创建角色可以继承其他角色的权限，在admin 数据库中创建的角色可以继承在其它任意 数据库中角色的权限。

MongoDB的用户和角色权限

关于角色权限的查看

// 查询所有角色权限(仅用户自定义角色) > db.runCommand({ rolesInfo: 1 }) // 查询所有角色权限(包含内置角色) > db.runCommand({ rolesInfo: 1, showBuiltinRoles: true })

示例： 查看所有内置角色： // 查询当前数据库中的某角色的权限 > db.runCommand({ rolesInfo: "<rolename>" }) // 查询其它数据库中指定的角色权限 > db.runCommand({ rolesInfo: { role: "<rolename>", db: "<database>" } }

常用的内置角色

数据库用户角色：read、readWrite;

read：可以读取指定数据库中任何数据；readWrite：可以读写指定数据库中任何数据，包括创建、重命名、删除集合。

所有数据库用户角色：readAnyDatabase、readWriteAnyDatabase、userAdminAnyDatabase、dbAdminAnyDatabase

readAnyDatabase：可以读取所有数据库中任何数据(除了数据库config和local之外)。 readWriteAnyDatabase：可以读写所有数据库中任何数据(除了数据库config和local之外)。 userAdminAnyDatabase：可以在指定数据库创建和修改用户(除了数据库config和local之外)。 dbAdminAnyDatabase ：可以读取任何数据库以及对数据库进行清理、修改、压缩、获取统计信息、执行检查等操作(除了数据库config和local之外)。

数据库管理角色：dbAdmin、dbOwner、userAdmin；

dbAdmin：可以读取指定数据库以及对数据库进行清理、修改、压缩、获取统计信息、执行检查等操作。 userAdmin：可以在指定数据库创建和修改用户。 clusterAdmin：可以对整个集群或数据库系统进行管理操作。

集群管理角色：clusterAdmin、clusterManager、clusterMonitor、hostManager；

备份恢复角色：backup、restore；

backup：备份MongoDB数据最小的权限。 restore：从备份文件中还原恢复MongoDB数据(除了system.profile集合)的权限。

超级用户角色：root

内部角色：system

集合框架

List

ArrayList

数据结构

动态数组

线程安全

不安全

插入和删除

插入和删除受元素位置影响，如果是add（e），就是添加到尾部，时间复杂度为O(1)，如果是指定位置插入的时间复杂度为O(n-i)。

快速随机访问

支持，通过下标get(index)来迅速获取元素对象

内存空间占用

尾部需要预留一段空间，防止数组越界

LinkedList

数据结构

双向链表

线程安全

不安全

插入和删除

链表存储，所以插入和删除不受元素位置影响，都是近似O(1)

快速随机访问

链表不支持

内存空间占用

空间消耗比ArrayList更大，因为每个元素需要更多的空间前指针和后指针及数据

Vector

线程安全

安全

原理

其实就是方法加了synchronized修饰的ArrayList

问题

Java中遍历ArrayList的过程中删除元素操作会发生并发修改异常？

循环遍历的时候，删除元素，首先会定位到元素位置删除，然后复制数组往前移动一位， 遍历到最后就会出现null，报错了

总结

非并发

写多读少

LinkedList

读多写少

ArrayList

并发

Vector

Set

HashSet

基于HashMap实现，除了clone，writeObject和readObject必须自己实现，无序唯一

HashSet和hashMap区别

前者实现了set接口，后者是Map接口

前者存储对象，后者是键值对

前者用add新增，后者是put

前者是用hashcode和equals来判断对象是否相等，后面只用hashcode

前者效率慢，后者快

hashSet怎么检查重复

首先计算对象的hashcode然后匹配是否有其他的已经存入的hashcode与之匹配，若没有 那么假如，如果有，要么要通过equals来检查hashcode的对象是否真的相同。

LinkedHashSet

链表和hash表组成，由链表保证元素的排序。由hash表保证元素的唯一

TreeSet

有序，唯一，红黑树

Map

HashMap

数据结构

JDK1.7

散列（数组+链表）

JDK1.8

散列（数组+链表）+红黑树

链表长度大于8就会转为红黑树

初始大小是16，扩容因子是0.75，扩容都是2的幂次方，如果设置的初始值不是2的n次方，hashmap会自动扩充为2的n次方

线程安全

不安全

如果需要满足线程安全，可以用 Collections 的synchronizedMap 方法使HashMap 具有线程安全的能力，或者使用ConcurrentHashMap

是否支持NULL键和值

支持

HashMap 最多只允许一条记录的键为null，允许多条记录的值为null

解决hash冲突

拉链法

我们先根据key来获取Hash值,然后通过(数组length-1） 和hash取模获取当前数组存放的下标。若是链表上有值， 那么追加到尾部，然后取值的时候判断key==和equals

并发闭环问题

扩容方法会新建一个数组，复制原数组到新的数组，由于下标挂着链表， 扩容之后会导致环形链表的出现，JDK1.8已经解决了这个问题了。

HashTable

数据结构

初始大小是11，每次扩容都是2n+1

线程安全

安全

内部方法基本上都经过了synchronized修饰

是否支持NULL键和值

不支持

ConcurrentHashMap

数据结构

1.7

ConcurrentHashMap 是一个 Segment 数组，Segment 通过继承ReentrantLock 来进行加锁，所以每次需要加锁的操作锁住的是一个 segment， 这样只要保证每个 Segment 是线程安全的，也就实现了全局的线程安全。

1.8

取消了分段锁，而是采取了cas和synchronized来保证并发安全，synchronized只锁住当前链表或者红黑二叉树的首节点，只要hash不冲突，就不会产生并发，效率很高

原理

put

1）二阶段hash，第一阶段是定位到哪个segment第二阶段是定位到哪个entry

2）entry中，除了value，还有key，hash和next都是final修饰意味着不能从中心或者尾部添加或者删除节点，一律添加到头部

3）通过count来统计段内的数据个数，只有新增和删除会修改这个值，每次都是在上述俩个步骤的最后一步进行修改

get

get不需要加锁，采取volatile修饰共享变量这样每次get的时候都可以获取最新的结构更新由于遍历不需要加锁的原因是因为next是final。 要么是不为空返回，为空的话就加锁重读

remove

由于是final，所以删除节点的时候会删除某个节点然后那个节点之上都复制，然后最后一个节点指向被删节点的下一个节点

resize

扩容只会对段扩容而非整个桶跟HashMap不同的是，段是先判断是否需要扩容再put，而hashmap是先put再判断是否要扩容

size

先尝试不锁住segment的方式来统计segment的大小如果统计过程中，容器的count发生变化则采取加锁的方式

线程安全

安全

ConcurrentHashMap与Hashtable的区别

底层数据结构：1.7分段数组+链表,1.8散列红黑树，而hashtable一直是散列

实现线程安全的方式【重要】

ConcurrentHashMap

1.7

分段锁对整个桶进行切割，增加锁的粒度 如果不是访问同一个segment，就不存在竞争

1.8

Node数组+链表+红黑树，并发控制使用CAS与Synchronized

HashTable

使用synchronized来保证，效率低下，多线程访问同一个同步方法 会导致阻塞或者轮询，竞争会愈发激烈

TreeMap

数据结构

红黑树

一种特殊的AVL树

是否有序

有序

字典顺序来排序【升序】

我们可以通过往对象中构造一个Comparator对象来定制排序

原理

put

1）构建二分搜索树

2）构建红黑树

左旋

右旋

着色

deleteEntry

删除比较特殊，他不是直接刪除，而是找到要刪除节点子节点， 然后用子节点来代替删除节点这样就删除子节点即可，简单的 说就是把要删除节点和他的左孩子的最右和右孩子的最左交换

LinkedHashMap

数据结构

继承于HashMap,同时内部增加了一个链表，用来保证元素的顺序

是否有序

基于元素进入集合的顺序或者被访问的先后顺序

与TreeMap的有序性的区别

1）LinkedHashMap是根据元素增加或者访问的先后顺序进行排序

2）TreeMap是基于元素的固有顺序（借助Comparator或者Comparable确定）

3）如果是自然顺序或者自定义顺序的话treemap好，如果需要输出顺序和输入顺序一致的话用LinkedHashMap好

MySQL

基础知识

SQL语法

数据查询语言DQL：数据查询语言，顾名思义就是从数据库中查询数据。包括select、where、order by、group by、having等动词 数据定义语言DDL：数据定义语言，用于数据库表结构相关操作，如创建表CREATE、删除DROP、添加表索引等 数据操作语言DML：通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问 数据操纵语言DCL：实现对数据库表数据的查询、增加、删除和修改操作

SQL执行顺序

语法顺序：SELECT->DISTINCT->FROM->JOIN IN->WHERE->GROUP BY->HAVING->UNION->ORDER BY 执行顺序：FROM->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->UNION->ORDER BY

数据库设计

三大范式

第一范式1NF：列的原子性 第二范式2NF：满足第一范式，必须要有主键列 第三范式3NF：满足第二范式，主外键引用

完整性约束

1、域（列）完整性：包括取值范围、类型、默认值、非空等 2、实体（行）完整性：主关键字即不能重复，也不能为空值 3、参照完整性：外键约束 4、用户自定义完整性：范围约束

数据库模型

概念模型

E-R图，即Entity Relationship，实体关系

逻辑模型

关系：二维表

物理模型

表结构，包括字段类型、长度、主外键、是否非空等信息

游标

相当于一个指针，用于对数据库中数据进行遍历操作，通常以行为单位进行遍历

函数

存储过程

触发器

类似于存储过程，唯一的区别时触发器不能执行EXECUTE语句调用，而是由用户执行T-SQL语句时自动触发执行

视图

基于SQL语句的结果集构成的表（虚拟表）

存储引擎

MYISAM：不支持外键，默认表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描

INNODB：支持外键，默认行锁，查表总行数时，全表扫描

索引详解

普通索引

最基本的索引类型，没有唯一性之类的限制

唯一索引

不允许其中任何两行具有相同索引值的索引

主键索引

主键索是唯一索引的特定类型，索引字段为主键

全文索引

覆盖索引

按包含的字段数量

单一索引

单个字段作为索引

组合索引

多个字段一起作为索引

按数据结构

B树索引

B+树索引

数据有序,范围查询

哈希索引

通过Ｈash算法计算索引位置，效率高，一次定位。但需要解决Hash冲突，不能排序,不能进行范围查询

数据结构演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

按物理存储结构

聚集索引

数据和索引放在同一个文件中，以主键为索引存储数据

表中行的物理顺序与键值的逻辑（索引）顺序相同，一个表只能包含一个聚集索引

非聚集索引

数据和索引分开单独存放在不同的文件中，索引文件不存储数据，而是存储数据的地址

数据表中记录的物理顺序与索引顺序可以不相同，一个表中可以包含多个非聚集索引

索引失效的场景

全值匹配

最佳左前缀法则

使用多列索引的查询语句：只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

不在索引列上做任何操作（计算、函数、（手动或自动）类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引

MySQL在使用不等于（!=或<>)的时候无法使用索引会导致全表扫描

is null，is not null也无法使用索引

使用LIKE关键字查询的语句：如果匹配字符串的第一个字符为“%”，索引不会起作用。MySQL索引失效会变成全表扫描的操作

字符串不加单引号索引失效（自动类型转换）

or左边有索引、右边没索引也会失效

分页的实现

锁机制与事务隔离级别

MySQL锁

性能

乐观锁

悲观锁

操作

读锁

写锁

粒度

表锁

行锁

死锁以及优化解决方案

ACID四大特性

原子性Atomicity

事务时原子性操作，要么执行，要么都不执行

一致性Consistency

事务在执行前后数据完整性必须保持一致

隔离性Isolation

并发的事务之间时相互隔离的，事务内部的操作对于其他事务而言都是相互不可见的。如果不考虑隔离性，就可能发生脏读、不可重复读、幻读问题

脏读：一个事务读取了另一个事务改写但还未提交的数据，如果这些数据回滚，则读到的数据无效

不可重复读：在同一个事务中，多次读取同一个数据返回的结果不同

幻读：一个事务读取了几行记录后，另一个事务插入了一些记录，后来的查询中前一个事务就会发现有些原来没有的记录

持久性Durability

事务一旦完成提交，引起的数据变化将是永久性的，即使数据库发生故障也不应该对其有任何影响

事务隔离级别

READ_UNCOMMITTED（未提交读）

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

READ_COMMITTED（读已提交）

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

REPEATABLE_READ（可重复读）【默认】

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生

SERIALIZABLE（串行）

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、 不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

并发事务一般有哪些问题？

更新丢失：两次事务读同一行，更新的时候不知道别的数据存在，导致丢失更新问题。

脏读：事务B读取事务A还没有提交的数据

不可重复读：两次事务读的数据不一致

幻读：事务A修改了数据，事务B也修改了数据，这时在事务A看来，明明修改了数据，咋不一样

多版本并发控制MVCC机制详解

常见事务问题的解决方案

版本检查

在数据库中保留“版本”字段，跟随数据同时读写，以此判断数据版本。版本可能是时间戳或状态字段。 UPDATE table SET status = 1 WHERE id=1 AND status = 0; 版本检查能够作为“乐观锁”，解决更新丢失的问题。

锁

共享锁与排他锁

共享锁（Shared locks, S-locks）

加共享锁的对象只允许被当前事务和其他事务读。也称读锁。能给未加锁和添加了S锁的对象添加S锁。对象可以接受添加多把S锁。

排它锁（Exclusive locks, X-locks）

加排它锁的对象只允许被当前事务读和写。也称独占锁，写锁。只能给未加锁的对象添加X锁。对象只能接受一把X锁。加X锁的对象不能再加任何锁。

更新锁（Update locks, U-locks）

引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。 因此增加U锁，U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

临时锁与持续锁

锁的时效性。指明了加锁生效期是到当前语句结束还是当前事务结束。

表级锁与行级锁

锁的粒度。指明了加锁的对象是当前表还是当前行。

悲观锁与乐观锁

悲观锁（Pessimistic Locking）

悲观锁假定当前事务操纵数据资源时，肯定还会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。悲观锁需使用数据库的锁机制实现， 如使用行级排他锁或表级排它锁。 尽管悲观锁能够防止丢失更新和不可重复读这类问题，但是它非常影响并发性能，因此应该谨慎使用。

乐观锁（Optimistic Locking）

乐观锁假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的锁定。乐观锁使用由程序逻辑控制的技术来避免可能出现的并发问题。 唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。 乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。

三级加锁协议

一级加锁协议

事务在修改数据前必须加X锁，直到事务结束（提交或终止）才可释放；如果仅仅是读数据，不需要加锁。

二级加锁协议

满足一级加锁协议，且事务在读取数据之前必须先加S锁，读完后即可释放S锁。

三级加锁协议

满足一级加锁协议，且事务在读取数据之前必须先加S锁，直到事务结束才释放。

两段锁协议（2-phase locking）

加锁阶段：事务在读数据前加S锁，写数据前加X锁，加锁不成功则等待。 解锁阶段：一旦开始释放锁，就不允许再加锁了。

执行计划与优化实践

数据库中设置SQL慢查询日志

查看是否开发慢查询

show variables like 'slow_query%';

slow_query_log = off，表示没有开启慢查询 slow_query_log_file 表示慢查询日志存放的目录

开启慢查询

方式一（临时，重启失效）： ------------------------------------------------------------ mysql>set global slow_query_log=ON mysql>set global long_query_time=3600 mysql>set global log_querise_not_using_indexes=ON

方式二（永久性）： ------------------------------------------------------------- 在/etc/my.cfg文件中的[mysqld]中加入 slow_query_log=ON slow_query_log_file=/var/lib/mysql/localhost-slow.log

查询慢查询次数

show status like 'slow_queries';

慢查询日志分析工具Mysqldumpslow

统计不同慢sql的出现次数(Count)，执行最长时间(Time)，累计总耗费时间(Time)，等待锁的时间(Lock)，发送给客户端的行总数(Rows)，扫描的行总数(Rows)

案例一：取出耗时最长的两条sql -------------->  mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

案例二：取出查询次数最多，且使用了in关键字的1条sql -------------->  mysqldumpslow -s c -t 1 -g 'in' /var/lib/mysql/localhost-slow.log

show profile性能分析方法

查看是否开启profile，mysql默认是不开启的，因为开启很耗性能

show variables like 'profiling%';

开启profile（会话级别的，关闭当前会话就会恢复原来的关闭状态）

set profiling=1; 或者 set profiling=ON;

关闭profile

set profiling=0; 或者 set profiling=OFF;

显示当前执行的语句和时间

显示当前执行的语句和时间

显示当前查询语句执行的时间和系统资源消耗

执行计划explain（慢查询日志分析）

id代表执行select子句或操作表的顺序

select_type

simple：简单的select查询,查询中不包含子查询或union查询 primary：查询中若包含任何复杂的子部分,最外层查询则被标记为primary subquery：在select 或where 列表中包含了子查询 derived：在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结果放在临时表里 union：做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived union result：从union表获取结果的select

table

显示一行的数据时关于哪张表的

type

system：表只有一行记录,这是const类型的特例,平时不会出现 const：表示通过索引一次就找到了,const即常量,它用于比较primary key或unique索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该查询转换为一个常量  eq_ref：唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描 ref：非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体 range：检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。 index：index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取 all：全表扫描,是最差的一种查询类型

possible_keys

显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

key

实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引

key_len

表示索引中使用的字节数,而通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的

ref

显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

Extra

Using filesort：说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序" Using temporary ：使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的 Using where：表明使用了where过滤 Using join buffer：表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表 impossible where：表示where子句的值总是false,不能用来获取任何元祖。如下例：select * from t1 where id='1' and id='2'; select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作，即一旦MySQL找到了与行相联合匹配的行，就不再搜索了

常见慢查询优化

索引优化最佳实践

order by关键字优化

尽量使用index方式排序，避免使用filesort方式

order by满足两种情况会使用index排序：①、order by语句使用索引最左前列，②、使用where子句与order by子句条件列组合满足索引最左前列

双路排序：MySQL4.1之前，两次扫描磁盘

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列进行输出，效率更高一点，但是它会使用更多的空间， 因为它把每一行都保存在内存中了

优化策略: 增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置

group by关键字优化

实质是先排序后进行分组，遵照索引键的最佳左前缀，当无法使用索引列时，增大sort_buffer_size+max_length_for_sort_data参数的设置

优化数据库结构

将字段很多的表拆分成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中， 然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

应用拓展

JDBC

Java DataBase Connectivity,即Java数据库连接，是用Java语言定义的统一的数据库操作规范，具体实现由数据库厂商完成，位于java.sql包中，包括对数据库的各种操作相关接口和类

操作步骤

加载数据库驱动

导入对应的数据的驱动jar包

Class.forName(数据库驱动全名）加载驱动

获取链接对象

DriverManager.getConnection(url, usrename， password)

获取语句对象Statement/PreparedStatement

进行数据库操作（增删查改）

释放资源：关闭结果集ResultSet、语句对象Statement、链接对象Connection

API

DirverManager类

用于获取数据库链接对象

Connection接口

完成数据库操作的主对象，其中包括数据操作相关的方法

事务相关

设置手动提交事务：connection.setAutoCommit(false)；

提交事务：connection.commit()；

Statement接口

执行SQL语句

PreparedStatement接口

Statement的子接口

执行预编译SQL语句，防止SQL注入，及提高SQL执行效率

ResultSet接口

代表查询结果集

CallableStatement接口

PreparedStatement的子接口

处理存储过程的调用

DatabaseMetaData接口

包含对数据源的元数据（比如表结构、字段类型、函数、触发器、存储过程等）信息进行获取的API

通过Connection对象的getMetaData()方法获取

ResultSetMetadata接口

包含对查询结果集中的元数据（比如字段类型、字段值）等信息进行获取的API

通过ResultSet对象的getMetadata()方法获取

数据库连接池

C3P0

DBCP

Druid

德鲁伊，带有监控功能的数据库连接池

HikariCP

“光”，号称时性能最好的数据库连接池

面试总计

MySQL 中有哪几种锁

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

MySQL 中有哪些不同的表格

共有5 种类型的表格： 1、MyISAM 2、Heap 3、Merge 4、INNODB 5、ISAM

简述在MySQL 数据库中MyISAM 和InnoDB 的区别

MyISAM： 不支持事务，但是每次查询都是原子的； 支持表级锁，即每次操作是对整个表加锁； 存储表的总行数； 一个MYISAM 表有三个文件：索引文件、表结构文件、数据文件； 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- InnoDb： 支持ACID 的事务，支持事务的四种隔离级别； 支持行级锁及外键约束：因此可以支持写并发； 不存储总行数： 一个InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制； 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

MySQL 中InnoDB 支持的四种事务隔离级别名称，以及逐 级之间的区别

SQL 标准定义的四个隔离级别为： 1、read uncommited ：读到未提交数据 2、read committed：脏读，不可重复读 3、repeatable read：可重读 4、serializable ：串行事物

CHAR 和VARCHAR 的区别

1、CHAR 和VARCHAR 类型在存储和检索方面有所不同 2、CHAR 列长度固定为创建表时声明的长度，长度值范围是1 到255 当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR 值时需删除尾随空格。

主键和候选键有什么区别

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

myisamchk 是用来做什么的

它用来压缩MyISAM 表，这减少了磁盘或内存使用。 MyISAM Static 和MyISAM Dynamic 有什么区别？ 在MyISAM Static 上的所有字段有固定宽度。动态MyISAM 表将具有像TEXT，BLOB 等字段，以适应不同长度的数据类型。 MyISAM Static 在受损情况下更容易恢复。

如果一个表有一列定义为TIMESTAMP，将发生什么

每当行被更改时，时间戳字段将获取当前时间戳。 列设置为AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？ 它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。 怎样才能找出最后一次插入时分配了哪个自动增量？ LAST_INSERT_ID 将返回由Auto_increment 分配的最后一个值，并且不需要指定表名称。

你怎么看到为表格定义的所有索引

索引是通过以下方式为表格定义的：SHOW INDEX FROM <tablename>;

LIKE 声明中的％和_是什么意思

％对应于0 个或更多字符，_只是LIKE 语句中的一个字符。 如何在Unix 和MySQL 时间戳之间进行转换？ UNIX_TIMESTAMP 是从MySQL 时间戳转换为Unix 时间戳的命令 FROM_UNIXTIME 是从Unix 时间戳转换为MySQL 时间戳的命令

列对比运算符是什么

在SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR 或LIKE 运算符。

BLOB 和TEXT 有什么区别

BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写的BLOB。 BLOB 和TEXT 类型之间的唯一区别在于对BLOB 值进行排序和比较时区分大小写，对TEXT 值不区分大小写。

MySQL_fetch_array 和MySQL_fetch_object 的区别是什么

以下是MySQL_fetch_array 和MySQL_fetch_object 的区别： MySQL_fetch_array（） – 将结果行作为关联数组或来自数据库的常规数组返回。 MySQL_fetch_object – 从数据库返回结果行作为对象。

MyISAM 表格将在哪里存储，并且还提供其存储格式

每个MyISAM 表格以三种格式存储在磁盘上： “.frm”文件存储表定义 数据文件具有“.MYD” （MYData）扩展名 索引文件具有“.MYI” （MYIndex）扩展名

MySQL 如何优化DISTINCT

DISTINCT 在所有列上转换为GROUP BY，并与ORDER BY 子句结合使用。

NOW() 和CURRENT_DATE() 有什么区别

NOW() 命令用于显示当前年份，月份，日期，小时，分钟和秒。 CURRENT_DATE() 仅显示当前年份，月份和日期。

什么是非标准字符串类型

1、TINYTEXT 2、TEXT 3、MEDIUMTEXT 4、LONGTEXT

什么是通用SQL 函数

1、CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。 2、FORMAT(X, D)- 格式化数字X 到D 有效数字。 3、CURRDATE(), CURRTIME()- 返回当前日期或时间。 4、NOW() – 将当前日期和时间作为一个值返回。 5、MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。 6、HOUR（），MINUTE（）， SECOND（） – 从时间值中提取给定数据。 7、DATEDIFF（ A，B） – 确定两个日期之间的差异，通常用于计算年龄 8、SUBTIMES（ A， B） – 确定两次之间的差异。 9、FROMDAYS（ INT） – 将整数天数转换为日期值。

MySQL 支持事务吗

在缺省模式下，MySQL 是autocommit 模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下， MySQL 是不支持事务的。但是如果你的MySQL 表类型是使用InnoDB Tables 或BDB tables 的话，你的MySQL 就可以使用事务处理,使用SETAUTOCOMMIT=0 就可以使MySQL 允许在非autocommit 模式，在非autocommit 模式下，你必须使用COMMIT 来提交你的更改，或者用ROLLBACK来回滚你的更改。

MySQL 里记录货币用什么字段类型好

NUMERIC 和DECIMAL 类型被MySQL 实现为同样的类型，这在SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

MySQL 有关权限的表都有哪几个

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL 数据库里，由MySQL_install_db 脚本初始化。这些权限表分别user，db，table_priv， columns_priv 和host。

列的字符串类型可以是什么

字符串类型是： 1、SET 2、BLOB 3、ENUM 4、CHAR 5、TEXT

MySQL 数据库作发布系统的存储，一天五万条以上的增量， 预计运维三年,怎么优化

1、设计良好的数据库结构，允许部分数据冗余，尽量避免join 查询，提高效率。 2、选择合适的表字段数据类型和存储引擎，适当的添加索引。 3、MySQL 库主从读写分离。 4、找规律分表，减少单表中的数据量提高查询速度。 5、添加缓存机制，比如memcached，apc 等。 6、不经常改动的页面，生成静态页面。 7、书写高效率的SQL。比如SELECT * FROM TABEL 改为SELECT field_1,field_2, field_3 FROM TABLE。

锁的优化策略

1、读写分离 2、分段加锁 3、减少锁持有的时间 4、多个线程尽量以相同的顺序去获取资源 不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

索引的底层实现原理和优化

B+树，经过优化的B+树 主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB 建议为大部分表使用默认自增的主键作为主索引。

什么情况下设置了索引但无法使用

1、以“ %” 开头的LIKE 语句，模糊匹配 2、OR 语句前后没有同时使用索引 3、数据类型出现隐式转化（如varchar 不加单引号的话可能会自动转换为int 型）

实践中如何优化MySQL

最好是按照以下顺序优化： 1、SQL 语句及索引的优化 2、数据库表结构的优化 3、系统配置的优化 4、硬件的优化

MySQL优化概述

MySQL数据库常见的两个瓶颈是：CPU和I/O的瓶颈。 CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上。 我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能。

MySQL优化方案

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

索引优化

索引

一般的应用系统，读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。 索引：简单的说，相当于图书的目录，可以帮助用户快速的找到需要的内容。 在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。 总结：索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子还有：查字典，查地图等。

索引类型

普通索引：是最基本的索引，它没有任何限制。 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

索引优化

1、只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引 2、尽量使用短索引，如果可以，应该制定一个前缀长度 3、对于经常在where子句使用的列，最好设置索引，这样会加快查找速度 4、对于有多个列where或者order by子句的，应该建立复合索引 5、对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引 6、尽量不要在列上进行运算（函数操作和表达式操作） 7、尽量不要使用not in和<>操作

SQL慢查询优化

如何捕获低效sql

1、slow_query_log：这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。 2、ong_query_time：当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。 3、slow_query_log_file：记录日志的文件名。 4、log_queries_not_using_indexes：这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

慢查询优化的基本步骤

1、先运行看看是否真的很慢，注意设置SQL_NO_CACHE 2、where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 3、explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 4、order by limit 形式的sql语句让排序的表优先查 5、了解业务方使用场景 6、加索引时参照建索引的几大原则 7、观察结果，不符合预期继续从1开始分析

优化原则

1、查询时，能不要*就不用*，尽量写全字段名 2、大部分情况连接效率远大于子查询 3、多使用explain和profile分析查询语句 4、查看慢查询日志，找出执行时间长的sql语句优化 5、多表连接时，尽量小表驱动大表，即小表 join 大表 6、在千万级分页时使用limit 7、对于经常使用的查询，可以开启缓存

数据库表优化

1、表的字段尽可能用NOT NULL 2、字段长度固定的表查询会更快 3、把数据库的大表按时间或一些标志分成小表 4、将表拆分 数据表拆分：主要就是垂直拆分和水平拆分。 水平切分：将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。 垂直切分：将表中大字段单独拆分到另外一张表, 形成一对一的关系

优化数据库的方法

1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’ 、’ 性别’最好适用ENUM 2、使用连接(JOIN)来代替子查询 3、适用联合(UNION)来代替手动创建的临时表 4、事务处理 5、锁定表、优化事务处理 6、适用外键，优化锁定表 7、建立索引 8、优化查询语句

简单描述MySQL 中，索引，主键，唯一索引，联合索引 的区别，对数据库的性能有什么影响（从读写两方面）

索引：是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。 普通索引(由关键字KEY 或INDEX 定义的索引)的唯一任务是加快对数据的访问速度。 普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。 索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。 索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

数据库中的事务是什么

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

数据库事务的特性

1、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行； 2、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态； 3、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务； 4、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

事务就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语 句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上 有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作 为事务考虑，就需要通过ACID 测试，即原子性，一致性，隔离性和持久性。

SQL 注入漏洞产生的原因？如何防止

SQL 注入产生的原因：程序开发过程中不注意规范书写sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST 和GET 提交一些sql 语句正常执行。 防止SQL 注入的方式：开启配置文件中的magic_quotes_gpc 和magic_quotes_runtime 设置 执行sql 语句时使用addslashes 进行sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。 过滤掉sql 语句中的一些关键词： update、insert、delete、select、* 。 提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

为表中得字段选择合适得数据类型

字段类型优先级: 整形>date,time>enum,char>varchar>blob,text优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型

存储时期

Datatime：以YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用8 个字节得存储空间，datatime 类型与时区无关 Timestamp：以时间戳格式存储，占用4 个字节，范围小1970-1-1 到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp 列得值 Date:（生日）占用得字节数比使用字符串.datatime.int 储存要少，使用date 只需要3 个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算 Time：存储时间部分得数据 注意：不要使用字符串类型来存储日期时间数据（通常比字符串占用得储存空间小，在进行查找过滤可以利用日期得函数） 使用int 存储日期时间不如使用timestamp 类型

对于关系型数据库而言，索引是相当重要的概念，请回答 有关索引的几个问题

1、索引的目的是什么？ 快速访问数据表中的特定信息，提高检索速度创建唯一性索引，保证数据库表中每一行数据的唯一性。 加速表和表之间的连接使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间 2、索引对数据库系统的负面影响是什么？ 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。 3、为数据表建立索引的原则有哪些？ 在最频繁使用的、用以缩小查询范围的字段上建立索引。在频繁使用的、需要排序的字段上建立索引 4、什么情况下不宜建立索引？ 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。对于一些特殊的数据类型，不宜建立索引，比如文本字段（ text）等

解释MySQL 外连接、内连接与自连接的区别

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。 内连接则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。 外连接其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。 左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL 来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换， MySQL 目前还不支持全外连接。

Myql 中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

SQL 语言包括哪几部分？每部分都有哪些操作关键字

SQL 语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL）四个部分。 数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index 等 数据操纵：Select ,insert,update,delete, 数据控制：grant,revoke 数据查询：select

完整性约束包括哪些

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。 分为以下四类： 1、实体完整性：规定表的每一行在表中是惟一的实体。 2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。 3、参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。 4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。 与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

什么是锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

什么叫视图？游标是什么

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

什么是存储过程？用什么来调用

存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

如何通俗地理解三个范式

第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解； 第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性； 第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。 范式化设计优缺点： 优点：可以尽量得减少数据冗余，使得更新快，体积小 缺点：对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化 反范式化： 优点：可以减少表得关联，可以更好得进行索引优化 缺点：数据冗余以及数据异常，数据得修改需要更多的成本

什么是基本表？什么是视图

基本表是本身独立存在的表，在SQL 中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。

试述视图的优点

(1) 视图能够简化用户的操作(2) 视图使用户能以多种角度看待同一数据；(3) 视图为数据库提供了一定程度的逻辑独立性； (4) 视图能够对机密数据提供安全保护。

NULL 是什么意思

NULL 这个值表示UNKNOWN(未知):它不表示“” (空字符串)。对NULL 这个值的任何比较都会生产一个NULL 值。您不能把任何值与一个NULL 值进行比较，并在逻辑上希望获得一个答案。

你可以用什么来确保表格里的字段只接受特定范围里的值

Check 限制，它在数据库表格里被定义，用来限制输入该列的值。 触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

说说对SQL 语句优化有哪些方法

1、Where 子句中：where 表之间的连接必须写在其他Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在Where 子句的末尾.HAVING 最后 2、用EXISTS 替代IN、用NOT EXISTS 替代NOT IN 3、避免在索引列上使用计算 4、避免在索引列上使用IS NULL 和IS NOT NULL 5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引 6、应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描 7、应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

分库分表

Mycat

ShardingSphere

数据读写分离及分库分表场景详解

常见数据分片算法hash、list、range、tag详解

常见数据库中间件Mycat与ShardingSphere对比

解密Sharding-jdbc核心概念与快速开始

深入Sharding-jdbc特性详解与模块划分

实战订单交易中orders和ordersTtem分库分表开发

深入Sharding-jdbc源码之SQL解析、SQL路由、SQL改写、SQL执行、结构合并