当前位置: 澳门新濠3559 > 数据库 > 正文

传统意义上的索引,将存活的对象复制到另外一

时间:2019-10-07 08:53来源:数据库

1 数据库事务处理

一个数据库事务通常包含对数据库进行读或写的一个操作序列 . 当一个事务被提交给了DBMS(数据库管理系统),则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要被回滚.

1 为数据库提供了一个从失败恢复到正常状态的方法 , 同时提供了数据库在异常状态下仍然能保持一致性方法

2 当多个应用程序并发访问数据库时,可以在这些应用程序之间提供隔离方法,以防止彼此的操作互相干扰

事务具有的特性:

原子性(Atomicity):事务作为一个整体被执行,对数据库的操作要么全部被执行,要么都不执行。
一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
持久性(Durability):一个事务一旦提交,他对数据库的修改应该永久保存在数据库中。

爬虫数据库操作封装

import pymysql
'''爬虫数据库存储'''
class Sql(object):
    def __init__(self):
        #创建连接
        self.conn = pymysql.connect(host='xxx', port=3306, user= 'root', passwd = 'xxx', database = 'douban',charset = 'utf8')
        #创建游标
        self.cursor = self.conn.cursor()
        #执行sql清空Movie
        self.cursor.execute("truncate table Movie")
        self.conn.commit()

    def process_item(self, item, spider):
        try:
            #执行sql插入语句
            self.cursor.execute("insert into Movie (name,movieInfo,star,quote) VALUES (%s,%s,%s,%s)",(item['name'], item['movieInfo'], item['star'], item['quote']))
            #提交数据
            self.conn.commit()

        except pymysql.Error:
            print("Error%s,%s,%s,%s" % (item['name'], item['movieInfo'], item['star'], item['quote']))
        return item

    def close_spider(self, spider):
        #关闭
        self.cursor.close()
        self.conn.close()
Mysql页锁:

页级:引擎 BDB。
表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行
行级:引擎 INNODB , 单独的一行记录加锁
表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许
行级,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。
页级,表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。

网络

1.HTTP状态码?301 302区别
2.tcp三次握手,为什么三次握手
3.tcp四次挥手,为什么四次挥手?断开之后马上重新连接吗?不是的话,等多久?
4.为什么需要长连接?怎样实现?
5.cookie原理,如果服务器是分布式的,怎样应对Session一致性问题?
6.osi七层模型,
7.浏览器访问一个网址的过程,
8.tcp拥塞控制,滑动窗口 回退N指针协议?
9.Ping过程,icmp报文?
10.redict 和 forward区别?

2 数据库索引

1 索引概述

索引(Index)是帮助MySQL高效获取数据的数据结构, 数据库查询是最重要,最基本功能之一.

常见的查询算法:

>1 顺序查找 , 数据量大时,肯定不行
>
>2 二分查找, 但要求数据有序
>
>3 二叉树查找,只能应用在二叉树上
>
>4 为了适应各种复杂的数据结构, 数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引

索引方式

澳门新濠3559 1

2 聚焦索引与非聚焦索引

聚焦索引

目前大部分的数据库系统及文件系统都是采用B-Tree与 B Tree实现的即平衡树的数据结构.

我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置 , 这样原本大量查询的数据查询次数计算 , 查找次数是以树的分叉数为底,记录总数的对数,大大降低次数数量级.

澳门新濠3559 2

​ 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

非聚焦索引即常规索引

非聚焦索引即 每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

澳门新濠3559 3

不管以任何形式查询表,绝大部分都要通过聚焦索引来进行定位, 聚集索引(主键)是通往真实数据所在的主要路径。

非聚焦索引流程

#创建索引
create index_age name on user_info(age);
#查询年龄为20的用户名
select name from user_info where index_age = 20;

首先,通过非聚集索引index_age查找age等于20的所有记录的主键ID值

然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

最后, 从得到的真实数据中取得naem字段的值返回, 也就是取得最终的结果

复合索引即多字段查询

#创建复合索引
create index index_birthday_and_user_name on user_info(birthday, user_name);
#查询生日为1993-11-1的用户名
select user_name from user_info where birthday = '1993-11-1'

通过非聚集索引index_birthday_and_user_name查找birthday等于1993-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能

澳门新濠3559 4

物理存储角度

数据库以页为存储单位,一个页有8K(8192byte),一页存放N条记录

在B 树中分为数据页和索引页
B 树的高一般为2-4层,因为查找某一键值的记录只需要2-4次I/O,效率较高

1.聚集索引(也叫聚簇索引)
2.非聚集索引
不管是聚集索引还是非聚集索引他们的结构都是B 树,他们的唯一区别是
聚集索引的数据页存放的是完整的记录,也就是说,聚集索引决定了表的物理存储顺序
非聚集索引的数据页中存放的是指向记录的地址信息,他真正的数据已经在聚集索引中存储了
 1、聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
 2、聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续

在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同,一个表只能包含一个聚集索引,聚集索引比非聚集索引有更快的访问速度
索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。
术语“聚簇”表示数据行和相邻的键值紧密地存储在一起,InnoDB 的聚簇索引的数据行存放在 B-Tree 的叶子页中。
因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
优点

  1. 可以把相关数据保存在一起,减少 I/O 操作;
  2. 因为数据保存在 B-Tree 中,因此数据访问更快。
    缺点
  3. 聚簇索引最大限度提高了 I/O 密集型应用的性能,但是如果数据全部放在内存,就没必要用聚簇索引。
  4. 插入速度严重依赖于插入顺序,按主键的顺序插入是最快的。
  5. 更新操作代价很高,因为每个被更新的行都会移动到新的位置。
  6. 当插入到某个已满的页中,存储引擎会将该页分裂成两个页面来容纳该行,页分裂会导致表占用更多的磁盘空间。
  7. 如果行比较稀疏,或者由于页分裂导致数据存储不连续时,聚簇索引可能导致全表扫描速度变慢。

然而, 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

数据结构

1.链表手写 CLH队列为例子说了下双向链表的队列怎么写?
2.Hash冲突如何解决
3.什么是平衡二叉树,红黑树,什么地方用到红黑树,怎样实现红黑树?
4.迪杰特斯拉算法,就是最短路径的那个,图在内存中怎么存储,
5.一个大文件中数据排序,内存一次放不下,怎么实现?
(哈希 排序 归并 最小堆)
6.判断32位整数是不是4的幂?先判断是不是2的幂?然后在判断4的幂,将树与0X5555555与,非0则是。
7.一个文本文件每一行都有一个url,有一万行,统计每一个url出现的次数,(hash就可以了)

4 MVCC多版本并发控制

概述

全称是Multi-Version Concurrent Control,即多版本并发控制,在MVCC协议下,每个读操作会看到一个一致性的snapshot,并且可以实现非阻塞的读。MVCC允许数据具有多个版本,这个版本可以是时间戳或者是全局递增的事务ID,在同一个时间点,不同的事务看到的数据是不同的。

mysql中innodb实现

innodb会为每一行添加两个字段,分别表示该行创建的版本删除的版本,填入的是事务的版本号,这个版本号随着事务的创建不断递增。在repeated read的隔离级别(事务的隔离级别请看这篇文章)下,具体各种数据库操作的实现:select,insert,delete,update

MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好.

从数据结构角度分

1.B 索引:
传统意义上的索引,最常用最普遍的索引
2.hash索引:
hash索引是一种自适应的索引,数据库会根据表的使用情况自动生成hash索引,人为无法干预
3.全文索引:
用于实现关键词搜索,但它只能根据空格分词,因此不支持中文,可以使用lucene实现搜索功能
4.RTree索引:
在mysql很少使用,仅支持geometry数据类型;相对于BTREE,RTREE的优势在于范围

先看下面这个SQL语句

操作系统

1.操作系统自旋锁怎么实现?
2.kill进程杀不掉的原因? kill -9 -9是什么意思?
3.linux文件权限(rwx 421)
4.linux内存使用情况 进行调度 常用命令
5.死锁条件 预防

3 Redis原理

概述

  1. 是一个完全开源免费的key-value内存数据库
  2. 通常被认为是一个数据结构服务器,主要是因为其有着丰富的数据结构 strings、map、 list、sets、 sorted sets

Redis数据库

Redis也以消息队列的形式存在,作为内嵌的List存在,满足实时的高并发需求。在使用缓存的时候,redis比memcached具有更多的优势,并且支持更多的数据类型,把redis当作一个中间存储系统,用来处理高并发的数据库操作.

Redis存储的优点:

  • 速度快:使用标准C写,所有数据都在内存中完成,读写速度分别达到10万/20万
  • 持久化:对数据的更新采用Copy-on-write技术,可以异步地保存到磁盘上,主要有两种策略,一是根据时间,更新次数的快照(save 300 10 )二是基于语句追加方式(Append-only file,aof)
  • 自动操作:对不同数据类型的操作都是自动的,很安全
  • 快速的主--从复制,官方提供了一个数据,Slave在21秒即完成了对Amazon网站10G key set的复制。
  • Sharding技术: 很容易将数据分布到多个Redis实例中,数据库的扩展是个永恒的话题,在关系型数据库中,主要是以添加硬件、以分区为主要技术形式的纵向扩展解决了很多的应用场景,但随着web2.0、移动互联网、云计算等应用的兴起,这种扩展模式已经不太适合了,所以近年来,像采用主从配置、数据库复制形式的,Sharding这种技术把负载分布到多个特理节点上去的横向扩展方式用处越来越多。

Redis缺点

  • 是数据库容量受到物理内存的限制,不能用作海量数据的高性能读写,因此Redis适合的场景主要局限在较小数据量的高性能操作和运算上。
  • Redis较难支持在线扩容,在集群容量达到上限时在线扩容会变得很复杂。为避免这一问题,运维人员在系统上线时必须确保有足够的空间,这对资源造成了很大的浪费。

Redis的常见应用场景

一:缓存——热数据


热点数据(经常会被查询,但是不经常被修改或者删除的数据),首选是使用redis缓存

  • Select 数据库前查询redis,有的话使用redis数据,放弃select 数据库,没有的话,select 数据库,然后将数据插入redis
  • update或者delete数据库钱,查询redis是否存在该数据,存在的话先删除redis中数据,然后再update或者delete数据库中的数据

二:计数器

诸如统计点击数等应用。由于单线程,可以避免并发问题,保证不会出错,而且100%毫秒级性能! redis只是存了内存,记住要持久化,命令用 INCRBY

INCR user:<id> EXPIRE  

三:队列

  • 由于redis把数据添加到队列是返回添加元素在队列的第几位,所以可以判断用户是第几个访问这种业务
  • 队列不仅可以把并发请求变成串行,并且还可以做队列或者栈使用

四:位操作(大数据处理)

用于数据量上亿的场景下,例如几亿用户系统的签到,去重登录次数统计,某用户是否在线状态等等。

原理是:

redis内构建一个足够长的数组,每个数组元素只能是0和1两个值,然后这个数组的下标index用来表示我们上面例子里面的用户id(必须是数字哈),那么很显然,这个几亿长的大数组就能通过下标和元素值(0和1)来构建一个记忆系统,上面我说的几个场景也就能够实现。用到的命令是:setbit、getbit、bitcount

五:分布式锁与单线程机制

验证前端的重复请求(可以自由扩展类似情况),可以通过redis进行过滤:每次请求将request Ip、参数、接口等hash作为key存储redis,设置多长时间有效期,然后下次请求过来的时候先在redis中检索有没有这个key,进而验证是不是一定时间内过来的重复提交

六:最新列表

例如新闻列表页面最新的新闻列表,如果总数量很大的情况下,尽量不要使用select a from A limit 10,尝试redis的 LPUSH命令构建List,一个个顺序都塞进去就可以啦。用mysql查询并且初始化一个List到redis中。

七:排行榜

这个需求与上面需求的不同之处在于,取最新N个数据的操作以时间为权重,这个是以某个条件为权重,比如按顶的次数排序,这时候就需要我们的sorted set出马了,将你要排序的值设置成sorted set的score,将具体的数据设置成相应的value,每次只需要执行一条ZADD命令即可。

//将登录次数和用户统一存储在一个sorted set里
zadd login:login_times 5 1
zadd login:login_times 1 2
zadd login:login_times 2 3
//当用户登录时,对该用户的登录次数自增1
ret = r.zincrby("login:login_times", 1, uid)
//那么如何获得登录次数最多的用户呢,逆序排列取得排名前N的用户
ret = r.zrevrange("login:login_times", 0, N-1)
逻辑角度

1.普通索引:索引值不唯一
2.唯一索引:唯一索引是不允许任意两行具有相同索引值的索引,当现有数据库中存在重复键值的时候,大多数数据库不允许将唯一索引和数据库表相关联,当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。
3.主键索引:数据库中经常有一列或几列的组合,其值能唯一标识表中的每一行,该列称为主键。在数据库关系图中表为主键自动创建主键索引,主键索引是唯一索引的特定类型,该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。
4.空间索引

主键和唯一索引的区别:
1.一个表可以有多个唯一索引,而主键只能有一个
2.主键可以作为其它表的外键
3.主键不可以为null,而唯一索引可以为null
主键就是聚焦索引”这是极端错误的,是对聚焦索引的一种浪费。
主键并不一定是聚集索引,只是在SQL SERVER中,未明确指出的情况下,默认将主键定义为聚集,而ORACLE中则默认是非聚集

不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。

Spring

1.Spring框架特征,IOC控制反转,注入和单例的差别
2.Spring AOP 场景和底层实现
3.注入方式
4.Spring支持事物,如何管理?(源码)
5.Spring如何实现ioc
6.Spring Dao那种设计模式用的最多?
7.Spring mvc用过哪些注解
8.spring bean加载过程
9.redis 和 memcached 区别
10.分布式锁

创建索引的语句

创建索引

CREATE INDEX name_index
ON Employee (Employee_Name)

联合索引

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)
MySQL配置文件优化

mysql的配置文件名为my.cnf(window为my.ini),不同情况下配置文件参数设置也不相同,应该根据具体场景调优。
InnoDB中最重要的选项是:
innodb_buffer_pool_size:缓存用户表(实际数据row)及索引数据的最主要缓存空间,对 Innodb 整体性能影响也最大,默认为8MB,建议设为内存的70%传统意义上的索引,将存活的对象复制到另外一块。~80%(MyISAM只缓存索引)
innodb_log_file_size:日志文件大小,默认为48MB,应该调大,至少有几百MB

非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 如下图

数据库

1.数据库索引实现原理
数据库的索引是在存储引擎层实现的,不同的存储引擎有不同的实现方法,索引一般指的是B-Tree这种数据结构,InnoDB使用的是B 树,B树指的是多路自平衡搜索树,所有键值分布在树的所有节点中,搜索有可能在非叶子节点结束,性能逼近二分搜索。B 树是B-树的变体,所有的键值都存储在叶子节点中,为所有节点增加了链指针。
为什么会使用B/B 树:
红黑树等数据结构也可以用来实现索引,但是文件系统及数据库系统普遍采用B-/ Tree作为索引结构。MySQL 是基于磁盘的数据库系统,索引往往以索引文件的形式存储的磁盘上,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/ Tree,还跟磁盘存取原理有关。
InnoDB存储引擎,B 树索引 自适应哈希索引。
B 树和B树的优缺点
B树每个索引节点都含有信息,B 数除了叶子节点外,其余的都是存储的索引,因此一次性读入内存的数据更多了,降低io次数,而且所有数据都存储在叶子节点上,查询效率更加稳定,因为叶子节点之间用过指针相连,所以范围查找更加快速,B树要实现范围查找只能进行中序遍历。

2.什么数据库索引
索引就是用来提高数据库表的访问速度的,和我们查字典的过程很象,
A)索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页,而不是遍历所有数据页。
B)对于非聚集索引,有些查询甚至可以不访问数据页。
C)聚集索引可以避免数据插入操作集中于表的最后一个数据页。
D)一些情况下,索引还可用于避免排序操作。

3.索引的存储
一个索引包含索引项 指针,就是你指定的那个索引记录和指向数据的指针,键值 逻辑指针。
当你为一张空表创建索引时,数据库系统将为你分配一个索引页,该索引页在你插入数据前一直是空的。此页此时既是根结点,也是叶结点。每当你往表中插入一行数据,数据库系统即向此根结点中插入一行索引记录。当根结点满时,数据库系统大抵按以下步骤进行分裂:
A)创建两个儿子结点
B)将原根结点中的数据近似地拆成两半,分别写入新的两个儿子结点
C)根结点中加上指向两个儿子结点的指针
通常状况下,由于索引记录仅包含索引字段值(以及4-9字节的指针),索引实体比真实的数据行要小许多,索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录,这意味着在索引中查找时在I/O上占很大的优势。

4.索引的类型
http://www.cnblogs.com/aspnethot/articles/1504082.html
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
1.聚集索引:在聚集索引中,叶结点也即数据结点,所有数据行的存储顺序与索引的存储顺序一致。
2.非聚集索引:聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。
3.覆盖索引:索引覆盖是这样一种索引策略:当某一查询中包含的所需字段皆包含于一个索引中,此时索引将大大提高查询性能。
包含多个字段的索引,称为复合索引。索引最多可以包含31个字段,索引记录最大长度为600B。如果你在若干个字段上创建了一个复合的非聚集索引,且你的查询中所需Select字段及Where,Order By,Group By,Having子句中所涉及的字段都包含在索引中,则只搜索索引页即可满足查询,而不需要访问数据页。由于非聚集索引的叶结点包含所有数据行中的索引列值,使用这些结点即可返回真正的数据,这种情况称之为“索引覆盖”。

5.索引的优缺点
索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度
唯一索引可以确保每一行数据的唯一性
通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能
索引需要占物理和数据空间

6.数据库连接
连接就是将数据库表中的其他列添加进来,进行添加列的集合运算,内连接和外连接。
inner join:只能选取出同时存在两张表中的数据。
outer join:只要数据存储在某一张表中,就能读取出来。选取出单张表中的全部信息。
outer join:还涉及一个概念,就是以哪个表作为连接的主表,
left outer join, right outer join. 外连接中使用left outer join, right outer join 得到的结果完全相同。

7.数据库左连接,右连接
就是在外连接中,可以指定哪个表作为主表。left outer join right outer join.

8.澳门新濠3559,什么是事务
事务(Transaction)是并发控制的基本单位。所谓的事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。
事务的四个特性:ACID
8.1.原子性:是指事务是不可分割的最小单元,整个事务的所有操作要么执行成功,要么全部失败回滚。
8.2.一致性:是指事务总是从一个一致性的状态转移到另外一个一致性的状态。
8.3.隔离性:一个事务在没有提交之前,对于其他事务是不可见的,事务的隔离有四个级别。务允许多个用户对同一个数据进行并发访问,而不破坏数据的正确性和完整性。同时,并行事务的修改必须与其他并行事务的修改相互独立。
a.未提交读:事务中的修改,即使没提交也是可以读到的,这种称为脏读。
b.提交读:一个事务从开始到提交之前,对其他事务是不可见的,这个级别有时候也称为不可重复读,因为两次执行同样的查询可能会出现不同的结果。
c.可重复读:该级别保证了多次读取同样的记录时是同一个值,但是这个级别无法解决幻读的问题,幻读是指,当一个事务在某个范围内读取值时,然后其他事务又在这个范围内插入新值,然后再次读取这个值的时候,就会出现新的记录,产生幻行,InooDB通过多版本并发控制MVCC解决了幻读问题。MySql的默认隔离级别
d.可串行化:通过每一行数据加锁,然后强制事务串行执行,最高的隔离级别。
8.4.持久性:一旦事务提交,则其所做的所有修改就会永久的保持在数据库中。

9.mysql存储引擎

http://www.jianshu.com/p/a957b18ba40d
InnoDB:支持事物处理。

MyISAM:在mysql 5.1之前,MyISAM是默认的存储引擎,但是MyISAM不支持事务和行级锁,崩溃后无法安全恢复,

10.mysql中的锁策略
当多个查询需要在同一时刻修改数据,就会产生并发控制问题,数据库管理系统中的并发控制是指当多个事务同时存取同一数据时,不破坏事务的隔离性和统一性及数据库的完整性。共享锁(读锁),排它锁(写锁)。
表锁:是mysql中最基本的锁策略,并且是开销最小的所策略,就是对整张表进行锁定。
行级锁:锁定表中的某一行,最大程度的支持并发处理,但是也带来最大的锁开销,InnoDB支持行级锁
多版本并发控制MVCC:MySQL中的存储引擎不仅仅是简单的行级锁,为了提高并发访问的性能,一般都实现了多版本并发控制,不同的存储引擎实现MVCC的方式是不同的,典型的有乐观的并发控制和悲观的并发控制。
MVCC是行级锁的一个变种,但是在很多情况下避免了加锁操作,因此开销更低,但是大多数都实现了非阻塞的读操作,写操作也是锁定必要的行。
InnoDB中MVCC的实现:InnoDB是通过在每行记录的后边添加两个隐藏列来实现的,一个是保存了行的创建时间,一个保存了行的过期时间,存储的不是时间值,而是系统的版本号,没开始一个新的事务,系统版本号都会自动递增。
可重复读隔离级别下,MVCC是具体如何操作的
SELECT
InnoDB会根据以下两个条件检查每行记录:
a.只查找版本号早于当前事务的版本号的行,这样可以保证事务读取的行,要么是事务开始之前存在的,要么是事务自身插入或修改过的。
b.行的删除版本号要么未定义,要么大于当前的事务版本号,这可以确保读取到事务的行,在读取之前不会被删除。
INSERT:
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
保存这两个版本号,使得读操作都可以不用加锁,不足之处需要额外的存储空间,MVCC只在提交读和可重复读的隔离级别下工作。

11.数据库范式
第一范式:字段是最小的单元,不可再分。
第二范式:满足第一范式,而且表中的其他字段必须依赖于主键。
第三范式:满足第二范式,非主键外的所有字段必须互补依赖。

12.范式和反范式的优缺点
范式化的优点
a.范式化的更新操作比反范式要快。
b.当数据较好的范式化时,没有重复数据。
c.范式化的表通常更小,更容易放在内存中,所以执行操作会更快。
d.很少有多余的数据意味着检索列表时更少需要DISTINCT和GROUP BY语句。
范式化的缺点
范式化的数据库表,在查询时通常需要关联。
反范式化的优点
a.避免关系表查询。
反范式化的缺点
数据冗余。

13.连接池原理,乐观锁,悲观锁
http://www.open-open.com/lib/view/open1452046967245.html
a.数据库连接池和java线程池原理相同,都是预定义一些线程,等待连接,通过设置参数控制线程数量,这样避免频繁的启动和关闭线程和无限制的创建线程所带来的系统开销,实现原理,使用阻塞队列来实现。设置corePoolSize maxPoolSize。

b.乐观锁(乐观并发控制)
它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚。
乐观锁( Optimistic Locking ) 相对悲观锁而言,乐观锁假设认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让返回用户错误的信息,让用户决定如何去做。

c.悲观锁(悲观并发控制)
如果一个事务执行的操作都某行数据应用了锁,那只有当这个事务把锁释放,其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
悲观锁操作流程:先加锁,在操作,然后释放锁。
悲观锁的优缺点
悲观并发控制实际上是“先取锁再访问”的保守策略,为数据处理的安全提供了保证。但是在效率方面,处理加锁的机制会让数据库产生额外的开销,还有增加产生死锁的机会;另外,在只读型事务处理中由于不会产生冲突,也没必要使用锁,这样做只能增加系统负载;还有会降低了并行性,一个事务如果锁定了某行数据,其他事务就必须等待该事务处理完才可以处理那行数。

14.sql注入 如何预防

15.sql性能优化

16.redis存储结构
17.慢查询
18.间隙锁 **
19.
存储过程**
存储过程就是预编译的sql语句,
1.存储过程就是预编译的sql代码块,执行效率高,给存储过程取个名字,在别处直接调用就好了
2.一个存储过程替代大量T_SQL语句 ,可以降低网络通信量,提高通信速率
3.可以一定程度上确保数据安全

索引的实现

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,而B Tree的高度低(多叉树),可以减少I/O次数
InnoDB(聚簇索引)的数据文件本身就是索引文件(索引和数据存放在一个文件idb)。从上文知道,MyISAM(非聚簇索引)索引文件(MYI)和数据文件(MYD)是分离的,索引文件仅保存数据记录的地址。
mysql中每个表都有一个聚簇索引(clustered index ),除此之外的表上的每个非聚簇索引都是二级索引(普通索引、唯一索引),又叫辅助索引(secondary indexes)。

澳门新濠3559 5

java相关

1.Java内存模型(JSR133 happen-before 内存重排序)

2.垃圾回收算法,弱引用和软引用的区别
标记-清除:第一步标记可以被回收的对象,然后第二步进行清除。效率和空间碎片问题。
复制:把将可用的内存分配为两块空间,分配内存时只使用其中的一块,当回收垃圾时,将存活的对象复制到另外一块,这块内存直接全部清除,目前新生代回收都是使用这种算法,HotSpot虚拟机目前将内存分为一块Eden两块Survivor,8:1:1 每次分配内存时都是使用一块Eden和一块Survivor,因为90%以上的对象都是朝生夕死,但是当surviror存不下存活的对象,回去老年代分配担保一块空间。
标记-整理:第一步标记,第二步,整理,就是让存活的对象向一端移动,然后直接清理掉内存以外的东西。
分代回收:就是对堆空间分代,把它分为新生代和老年代,对于不用的代采用不同的算法,新生代采用复制算法,老年代采用标记整理或者标记清除。

3.ConcurrenHashMap CopyOnWriteArrayList

4.volatile及原理 synchronized lock区别
synchronized 是利用操作系统内核态和用户态转换实现的同步,jvm根据这个原语在线程和临界区的各种情况作了优化,也就是偏向级锁,轻量级锁,锁膨胀,重量级锁,竞争失败一方做自旋,自旋期间如果没能获得锁,直接挂起线程。
Lock是利用AQS实现的同步功能,更多的使用CAS操作做同步,利用操作系统调用硬件指令集完成。
Lock不能代替synchronized,因为CAS有局限性,相比之下,就是Lock会灵活些。

5.死锁发生条件,举例说明
6.Java内存分区,堆分类,FullGC过程
7.泛型擦除
8.线程状态及线程状态切换(InterruptException相关的)
9.基本类型和引用类型的区别
10.深刻隆和浅克隆的区别
11.AQS 和 ReentrantLock的源码
12.HashMap的源码,jdk1.8引入红黑树
13.垃圾回收机制,gc时什么情况下会发生迁移
14.finalize会不会发生立即出发GC,finalize对象复活,System.gc()
15.java 动态代理 cglib
16.jdk cglib区别是什么?java如何实现动态代理
17.java序列化(NIO )
18.java集合类源码ArrayList LinkedList
19.LinkedHashSet TreeSet HashSet
20.并发编程过程中需要遵守的原则(原子 可见 有序)
21.fianl关键字 (同步语义)
22.Object类的方法
23.sleep和wait方法区别
24.解释下time_waiting状态。
25.hashcode和equals 为什么要有equals
26.java类加载机制(双亲委派,破坏双亲委派)
27.NIO是什么?NIO除了让开发者使用本地内存外还有什么优势?
28.线程池有哪些类型,应用场景,实现原理,线程池核心数如何设置?
29.为什么用线程池
30.对象锁,类锁的区别?
31.servlet是线程安全的嘛?(servlet的config 等等有安全有不安全)
32.java IO设计模式(装饰者)
33.内存泄露如何检测?
34.jvm优化 gc分析
35.锁的等级:方法锁,对象锁,类锁
36.手写生产者、消费者模式(wait notify)
37.泛型擦除 jvm
38.Overload和override区别
39.构造器可被override吗?
40.String stringbuilder stringbuffer
41.Collection 和Collections区别(一个接口,一个工具类)
42.servlet生命周期及各个方法
43.servlet如何自定义filter
44.jsp原理及和servlet的区别
45.java io nio aio bio

事务的四大特性

1、原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行。
2、一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致。事务必须是使数据库从一个一致性状态变到另一个一致性状态。多个账户钱的总和不变
3、隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。(即事务隔离级别)(转钱时,从其他用户角度看)
4、持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障。

首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

其他xx鬼知道的框架

1.Netty
2.kafka
3.分布式。

为什么MyISAM会比Innodb 的查询速度快

INNODB在做SELECT的时候,要维护的东西比MYISAM引擎多很多;
1)数据块,INNODB要缓存,MYISAM只缓存索引块,这中间还有换进换出的减少;
2)innodb寻址要映射到块,再到行,MYISAM 记录的直接是文件的OFFSET,定位比INNODB要快
3)INNODB还需要维护MVCC一致;虽然你的场景没有,但他还是需要去检查和维护MVCC ( Multi-Version Concurrency Control )多版本并发控制(读不加锁,读写不冲突。)
(具体看下面Mysql中InnoDB的MVCC)

//建立索引

设计模式

1.单例模式,工厂模式

索引

数据库中的查询操作非常普遍,索引就是提升查找速度的一种手段

数据库索引的大致工作原理就是像文中所述, 然而细节方面可能会略有偏差,这但并不会对概念阐述的结果产生影响 。

MySQL数据库的四种隔离级别(从低到高、为解决并发事务问题):

①Read uncommitted (读未提交):最低级别,任何情况都无法保证。
②Read committed (读已提交):只有在事务提交后,其更新结果才会被其他事务看见。可避免脏读的发生。

③Repeatable read (可重复读):在一个事务中,对于同一份数据的读取结果总是相同的,无论是否有其他事务对这份数据进行操作,以及这个事务是否提交。可避免脏读、不可重复读的发生。
④Serializable (串行化):事务串行化执行,隔离级别最高,牺牲了系统的并发性。可避免脏读、不可重复读、幻读的发生。
  在MySQL数据库中,支持上面四种隔离级别,默认的为Repeatable read (可重复读);而在Oracle数据库中,只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,其中默认的为Read committed级别。

然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

索引的类型

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

B树和红黑树

B树主要是保证只有少数的磁盘访问(io次数少),解决数据结构不在主存中的数据存储问题。高度低。某一个节点可以看做一个磁盘块,里面含有指向下一个磁盘块的指针。

澳门新濠3559 6

image.png

关键吗就是上图磁盘块中的数字

B树从最后一层开始插入,涉及到节点的分裂
一棵含n个结点的B树的高度也为O(logn),但可能比一棵红黑树的高度小许多,应为它的分支因子比较大。所以,B树可以在O(logn)时间内,实现各种如插入(insert),删除(delete)等动态集合操作。
“阶”定义为一个节点的子节点数目的最大值(非根节点关键字个数m/2向上取整-m-1个)

create index index_birthday_and_user_name on user_info(birthday, user_name);

数据库引擎

每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

数据库表结构优化

选择合适的数据类型
数据类型越小越好:尽量使用可以正确存储数据的最小数据类型(tinyint,占用更少的磁盘、内存、cpu缓存);tinyint占1字节,int占4字节
简单就好:如整型比字符操作代价更低(用整型存储ip地址)
避免NULL:最好指定列为NOT NULL,因为NULL更难优化,使用的索引更复杂
不要设计太多列:减少不必要的列
适度冗余,减少join关联查询
适当拆分,水平拆分、垂直拆分
选择合适的字符编码:如果我们可以确定不需要存放多种语言,就没必要非得使用UTF8或者其他UNICODE字符类型,这回造成大量的存储空间浪费

首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节, 但是从上图能看出,树一共有三层, 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

数据库的悲观锁和乐观锁

一、悲观锁
1、排它锁,当事务在操作数据时把这部分数据进行锁定,直到操作完毕后再解锁,其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据。
2、实现:大多数情况下依靠数据库的锁机制实现
二、乐观锁
1、如果有人在你之前更新了,你的更新应当是被拒绝的,可以让用户重新操作。
2、实现:大多数基于数据版本(Version)记录机制实现
具体可通过给表加一个版本号或时间戳字段实现,当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。当我们提交更新的时候,判断当前版本信息与第一次取出来的版本值大小,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数据,拒绝更新,让用户重新操作。
并发量很高的话,建议使用悲观锁,否则的话就使用乐观锁

select user_name from user_info where birthday = '1991-11-1'

Mysql其它引擎

Blackhole引擎,它会丢弃所有插入的数据,不做任何保存。但会记录日志。
CSV引擎,可以将CSV文件作为MySQL表来处理,可以作为一种数据交换机制。
Memory引擎,数据存在内存中,访问速度快,重启后数据会丢失。
Merge引擎,是MyISAM的变种,由多个MyISAM表合并而来的虚拟表。

非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

实现区别

MyISAM引擎使用B Tree作为索引结构,叶结点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的。
MyISAM左图为主索引,右图为辅助索引(二级索引),两者在结构上没什么区别,都是B 树。

澳门新濠3559 7

image.png

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

澳门新濠3559 8

image.png

InnoDB左图为主索引、右图为辅助索引,辅助索引结构也是B 树
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B Tree组织的一个索引结构,这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。

这里以英文字符的ASCII码作为比较准则(排序)。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
由于实际的数据页只能按照一颗B 树进行排序,因此每张表只能有一个聚集索引。

select * from table where id = 1256;

索引的代价

建立索引需要付出的代价 建立索引的目的是加快对表中记录的查找或排序。但是为表设置索引是要付出代价的:这个代价有几个个方面
a) 索引需要占物理空间 除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
b) 创建索引和维护索引要耗费时间 这种时间随着数据量的增加而增加。
c) 降低维护速度 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度,同样降低了效率。

判断是否应该建索引的条件

1、较频繁的作为查询条件的字段应该创建索引

2、唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

3、增、删、改操作较多的数据库字段不适合建索引(加了索引后update会增加耗时)

4、表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。

5、性别字段(只有男、女)不适合建索引,性别字段的选择性低,使用索引查找还不如遍历表的效率高。最多只能少检索一半,但索引的额外开销更大,得不偿失

这句SQL语句的执行过程就会变为

MySQL中InnoDB的MVCC

MVCC的原理与copyonwrite类似,全称是Multi-Version Concurrent Control,即多版本并发控制。MVCC允许数据具有多个版本,这个版本可以是时间戳或者是全局递增的事务ID,在同一个时间点,不同的事务看到的数据是不同的。
由于在update操作提交之前,不能影响已有数据的一致性,所以不会改变旧的数据,update操作会被拆分成insert delete。需要标记删除旧的数据,insert新的数据。只有update提交之后,才会影响后续的读操作。而对于读操作而且,只能读到在其之前的所有的写操作,正在执行中的写操作对其是不可见的。(读不加锁,读写不冲突)
innodb会为每一行添加两个字段,分别表示该行创建的版本和删除的版本,填入的是事务的版本号,这个版本号随着事务的创建不断递增。在repeated read的隔离级别下,具体各种数据库操作的实现:
select:满足以下两个条件能够查询成功:(1)该行的创建版本号小于等于当前版本号,用于保证在select操作之前所有的操作已经执行落地。(2)该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
insert:将新插入的行的创建版本号设置为当前系统的版本号。
delete:将要删除的行的删除版本号设置为当前系统的版本号。
update:不执行原地update(相当于写操作),而是转换成insert delete。将旧行的删除版本号设置为当前版本号,并将新行insert同时设置创建版本号为当前版本号。
其中,写操作(insert、delete和update)执行时,需要将系统版本号递增。
由于旧数据并不真正的删除,所以必须对这些数据进行清理,innodb会开启一个后台线程执行清理工作,具体的规则是将删除版本号小于当前系统版本的行删除,这个过程叫做purge。
通过MVCC很好的实现了事务的隔离性,可以达到repeated read级别,要实现serializable还必须加锁。

接下来就讲解一下上面提出的几个问题。

数据库完整性约束

数据库完整性约束
数据的完整性
约束是用来确保数据的准确性和一致性。数据的完整性就是对数据的准确性和一致性的一种保证。
数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。
分为以下四类:

  1. 实体完整性:规定表的每一行在表中是惟一的实体。
  2. 域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
  3. 参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
  4. 用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。
    与表有关的约束
    主键约束(PK)primary key constraint 唯一且不为空
    唯一约束(UQ)unique constraint唯一,允许为空,即可以再其中出现null值,但只能出现一次
    默认约束(DF)default constraint默认值
    检查约束(CK)check constraint范围以及格式限制
    外键约束(FK)foreign key constraint表关系

 

两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁

1.存储结构
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义;数据文件的扩展名为.MYD (MYData);索引文件的扩展名是.MYI (MYIndex)。
InnoDB:.ibd的文件,存储与该表相关的数据、索引、表的内部数据字典(表缓存)信息;.frm表结构文件。
2.数据导出:
MyISAM只要发给他们对应那表的frm.MYD,MYI的文件,让他们自己在对应版本的数据库启动就行,而Innodb就需要导出xxx.sql了,因为光给别人文件,受字典数据文件的影响,对方是无法使用的。
3.、MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少,能加载更多索引。而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb占用空间较大
4.MyISAM存储空间几乎没有限制,最多可到64PB,InnoDB最多64TB
5.InnoDB支持事务(每条sql都是事务,默认配置事务自动提交)和外键,MyISAM不支持
6.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
7.MyISAM:只支持表级锁,select,update,delete,insert语句都会给表自动加锁
InnoDB:支持行级锁,但是InnoDB的行锁,只是在WHERE的主键(唯一索引)是有效的,非主键的WHERE都会锁全表的。对索引加锁,而不是对数据行加锁,只有当查询条件能使用索引的时候才会使用行级锁
两者锁读的时候共享锁、写的时候排它锁(可以一起读,不能一起写或读写)
8.全文索引(通过关键字匹配来查询)
MyISAM:支持 FULLTEXT类型的全文索引
InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。(从5.6开始支持,但不支持中文索引)
9.表的具体行数
MyISAM:保存有表的总行数,如果select count() from table;会直接取出出该值。
InnoDB:没有保存表的总行数,如果使用select count(
) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。
10.CURD操作
MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。
InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。DELETE 从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除

澳门新濠3559 9

image.png

澳门新濠3559 10

数据库调优:

MySQL调优分析explain;show status查看服务器状态信息

上图就是带有主键的表(聚集索引)的结构图。图画的不是很好, 将就着看。其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

其它索引

1.联合索引:又叫复合索引,Mysql从左到右使用索引中的字段,一个查询可以只使用索引的一部分,但必须是最左侧的部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。符合最左原则
联合索引实现:每个节点含有多个关键字,排序时按照多个关键字的顺序进行排序。而这个顺序就是你创建索引时候的顺序
如果你经常要用到多个字段的多条件查询,可以考虑建立联合索引,建立了一个联合索引就相当于建立了多个索引
联合索引sql会先过滤出last_name符合条件的记录,在其基础上再过滤first_name符合条件的记录。那如果我们分别在last_name和first_name上创建两个列索引,mysql的处理方式就不一样了,它会选择一个最严格的索引来进行检索,可以理解为检索能力最强的那个索引来检索,另外一个利用不上了,这样效果就不如多列索引了。虽然此时有了两个单列索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。如果经常使用单独一列作为查询条件,那么应该使用单列索引。(如有两个单列索引a、b,查询的时候只用a或只用b)。
多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高
2.覆盖索引:只需要通过辅助索引就可以获取查询的信心,而无需再通过聚集索引查询具体的记录信息
由于覆盖索引不包含整行的记录,因此它的大小远小于聚集索引
比较适合做一些统计操作

最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

SQL语句的执行顺寻

from--where--group by--having--select--order by

澳门新濠3559 11

JDBC的参数

澳门新濠3559 12

image.png

我们把birthday字段上的索引改成双字段的覆盖索引

SQL语句优化:

分析:确认程序是否存在查询不需要的记录;mysql是否在扫描额外记录
1、查询不需要的记录:使用select语句查询大量结果,然后再获取前N行(如新闻网站,取100条记录,只显示前面的10条),这时可以使用limit(limit 1,10;从1开始10行)
2、总是使用SELECT ,对I/O、内存消耗较大,不必要时不要这样。
3、子查询的性能又比外连接性能慢,尽量用外连接来替换子查询。
Select
from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
一种简单的优化就是用innerjoin的方法来代替子查询,查询语句改为:
Select* from A inner join B using(uuid) where b.uuid>=3000;
4、尽量少排序,排序操作会消耗较多的CPU资源(可以使用索引)
5、对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
6、切分查询,将大查询切分成小查询,每个查询功能一样,只完成一小部分,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、耗尽系统资源、阻塞很多小的但重要的查询。

澳门新濠3559 13

Mysql中查看索引

使用“执行过程”EXPLAIN,查看索引使用情况,查询的覆盖行数等
EXPLAIN SELECT * FROM user WHERE id = 10

澳门新濠3559 14

image.png

用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

事务的隔离级别

1.脏读:脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
当一个事务正在多次修改某个数据,而在这个事务中这多次的修改都还未提交,这时一个并发的事务来访问该数据,就会造成两个事务得到的数据不一致。例如:用户A向用户B转账100元
  当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。
2.不可重复读:不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。不可重复读出现的原因就是事务并发修改记录,要避免这种情况,最简单的方法就是对要修改的记录加锁,这回导致锁竞争加剧,影响性能。另一种方法是通过MVCC可以在无锁的情况下,避免不可重复读。
例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发送了不可重复读。
  不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
3,虚读(幻读)
在同一个事务中,同一个查询多次返回的结果不一致。事务A新增了一条记录,事务B在事务A提交前后各执行了一次查询操作,发现后一次比前一次多了一条记录。就好像产生幻觉一样,这就是发生了幻读。幻读是由于并发事务增加记录导致的,这个不能像不可重复读通过记录加锁解决,因为对于新增的记录根本无法加锁。需要将事务串行化,才能避免幻读。
  幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)。

我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置,即主键的目的为将数据库表里面的记录变换为索引结构的树状形式。

InnoDB和MyISAM区别

Oracle收购sun(sun之前收购了mySQL),发布的首个版本5.5,默认使用了InnoDB作为存储引擎,而之前的版本使用MyISAM作为默认。
MyISAM 和 InnoDB的适用场景
MyISAM适合:读多写少(1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。
InnoDB适合:写多读少(1)可靠性要求比较高,或者要求事务;(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。

假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是

Mysql意向锁:

①在MySQL中有表锁,
LOCK TABLE my_tabl_name READ; 用读锁锁表,会阻塞其他事务修改表数据。
LOCK TABLE my_table_name WRITe; 用写锁锁表,会阻塞其他事务读和写。
②Innodb引擎又支持行锁,行锁分为
共享锁(读锁),一个事务对一行的共享只读锁。
排它锁,一个事务对一行的排他读写锁。
③这两中类型的锁共存的问题
考虑这个例子:
事务A锁住了表中的一行,让这一行只能读,不能写。
之后,事务B申请整个表的写锁。
如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。
数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。
数据库要怎么判断这个冲突呢?
step1:判断表是否已被其他事务用表锁锁表
step2:判断表中的每一行是否已被行锁锁住。
注意step2,这样的判断方法效率实在不高,因为需要遍历整个表。
于是就有了意向锁。
在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。
在意向锁存在的情况下,上面的判断可以改成
step1:不变
step2:发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。
意向锁是为了提高封锁子系统的效率。
注意:申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。

然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

事务的一致性

1.强一致性:读操作可以立即读到提交的更新操作。
2.弱一致性:提交的更新操作,不一定立即会被读操作读到,此种情况会存在一个不一致窗口,指的是读操作可以读到最新值的一段时间。
3.最终一致性:事务更新一份数据,最终一致性保证在没有其他事务更新同样的值的话,最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生,不一致窗口的大小依赖于:通信延迟,系统负载等。
其他一致性变体还有:
单调一致性:如果一个进程已经读到一个值,那么后续不会读到更早的值。
会话一致性:保证客户端和服务器交互的会话过程中,读操作可以读到更新操作后的最新值。

  • 为什么要给表加上主键?

  • 为什么加索引后会使查询变快?

  • 为什么加索引后会使写入、修改、删除变慢?

  • 什么情况下要同时在两个字段上建索引?

范式

第一范式1NF的定义为:符合1NF的关系中的每个属性都不可再分,1NF是所有关系型数据库的最基本要。
2NF在1NF的基础之上,消除了非主属性对于码的部分函数依赖。
码:设 K 为某表中的一个属性或属性组,若除 K 之外的所有属性都完全函数依赖于 K(这个“完全”不要漏了),那么我们称 K 为候选码,简称为码。在实际中我们通常可以理解为:假如当 K 确定的情况下,该表除 K 之外的所有属性的值也就随之确定,那么 K 就是码。一张表中可以有超过一个码。
部分函数依赖:依赖于码的一部分(其中的某几个)
非主属性:包含在任何一个码中的属性成为主属性。
3NF在2NF的基础之上,消除了非主属性对于码的传递函数依赖。
BCNF范式:在 3NF 的基础上消除主属性对于码的部分与传递函数依赖
(https://www.zhihu.com/question/24696366)

反范式是通过增加冗余数据或数据分组来提高数据库读性能的过程。在某些情况下, 反范式有助于掩盖关系型数据库软件的低效。关系型的范式数据库即使做过优化, 也常常会带来沉重的访问负载。

澳门新濠3559 15

Mysql5.7的新特性

MySQL 5.7版本提供了更为简单SSL安全访问配置,并且默认连接就采用SSL的加密方式
MySQL数据库从5.7.8版本开始,也提供了对JSON的支持。
generated column是MySQL 5.7引入的新特性,所谓generated column,就是数据库中这一列由其他列计算而得。
不支持子查询中排序

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

为什么Mysql能保证失败回滚

进行事务处理的时候,MySQL 在开始事务时会切换到一个延缓操作的状态,这个状态下操作并不都是立即执行的(通常情况下语句是立即执行的)。而在 commit 时,会将延缓执行的操作都执行进去,并将状态回归到及时写入状态。同样的, rollback 时会把延缓写入的操作抛弃掉,此间申请的锁释放掉,并将状态回归到及时写入状态。
执行 rollback 的关键在于释放 申请的锁 和 回归及时写入状态,而并不是放弃未写入的操作(你关心的点在未写入的操作,然而执行与不执行 rollback 都没有操作写进去,所有你感觉执行或不执行都没什么区别)。
或者是显示地使用savepoint,rollback到之前设置的savepoint

这句SQL语句的执行过程如下

索引的创建

在表上创建一个简单的索引,允许使用重复的值

CREATE INDEX index_name
ON table_name (column_name)

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

假如您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

创建索引还可以用alter实现
InnoDB按照主键进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键(6个字节)然后在上面进行聚集。mysql不能手动创建聚集索引。
主键索引是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引

//查询生日在1991年11月1日出生用户的用户名

B 树

澳门新濠3559 16

image.png

澳门新濠3559 17

image.png

只有叶节点存数据,非叶节点都只是下层节点最大值的复写。叶子节点间多了指针,使得范围查找变得高效(如上图查找20到65)

create index index_birthday on user_info(birthday);

索引优化

1、建立索引加快查询性能,优先在经常搜索的字段上建立索引(where);WHERE子句的查询条件里使用了比较操作符LIKE前置通配符%(如:LIKE "

编辑:数据库 本文来源:传统意义上的索引,将存活的对象复制到另外一

关键词: 澳门新濠3559