数据库聚焦与非聚焦索引 事务处理 redis innodb引擎(九)

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()

2 数据库索引

1 索引概述

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

常见的查询算法:

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

索引方式

2 聚焦索引与非聚焦索引

聚焦索引

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

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

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

非聚焦索引即常规索引

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

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

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

非聚焦索引流程

#创建索引
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的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能

创建索引的语句

创建索引

CREATE INDEX name_index
ON Employee (Employee_Name)

联合索引

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

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)

4 MVCC多版本并发控制

概述

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

mysql中innodb实现

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

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