數據庫

• 數據庫設計

• SQLAlchemy

• 數據庫理論

• 分佈式ID

• Redis

數據庫設計

1 需求

根據黑馬頭條前臺產品原型圖中使用者端的部分，進行數據庫設計。

• 表結構
• 欄位型別、是否允許爲null、是否有預設值
• 索引設計
• 數據庫引擎的選擇

2 注意事項

• 爲了查詢效率，可以做冗餘欄位設計（空間換時間的思想，屬於一種反範式設計）

• 欄位型別的選擇

  • 整型的儲存大小與顯示大小

    mysql的欄位，unsigned int(3), 和unsinged int(6), 能儲存的數值範圍是否相同。如果不同，分別是多大？

    我們建立下面 下麪這張表：

    CREATE TABLE `test` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `i1` int(3) unsigned zerofill DEFAULT NULL,
        `i2` int(6) unsigned zerofill DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    

    插入一些數據後

• • 發現，無論是int(3), int(6), 都可以顯示6位以上的整數。但是，當數位不足3位或6位時，前面會用0補齊。

    手冊解釋是這樣的：

    MySQL還支援選擇在該型別關鍵字後面的括號內指定整數值的顯示寬度(例如，INT(4))。該可選顯示寬度規定用於顯示寬度小於指定的列寬度的值時從左側填滿寬度。顯示寬度並不限制可以在列內儲存的值的範圍，也不限制超過列的指定寬度的值的顯示。

    也就是說，int的長度並不影響數據的儲存精度，長度只和顯示有關，爲了讓大家看的更清楚，我們在上面例子的建表語句中，使用了zerofill。

    最終答案：儲存範圍相同。

  • char 與 varchar 的選擇

    • char 不可變，查詢效率高，可能造成儲存浪費
    • varchar 可變，查詢效率不如char，節省空間

常見MySQL數據型別

類 型	大 小	描 述
CAHR(Length)	Length位元組	定長欄位，長度爲0~255個字元
VARCHAR(Length)	String長度+1位元組或String長度+2位元組	變長欄位，長度爲0~65 535個字元
TINYTEXT	String長度+1位元組	字串，最大長度爲255個字元
TEXT	String長度+2位元組	字串，最大長度爲65 535個字元
MEDIUMINT	String長度+3位元組	字串，最大長度爲16 777 215個字元
LONGTEXT	String長度+4位元組	字串，最大長度爲4 294 967 295個字元
TINYINT(Length)	1位元組	範圍：-128~127，或者0~255（無符號）
SMALLINT(Length)	2位元組	範圍：-32 768~32 767，或者0~65 535（無符號）
MEDIUMINT(Length)	3位元組	範圍：-8 388 608~8 388 607，或者0~16 777 215（無符號）
INT(Length)	4位元組	範圍：-2 147 483 648~2 147 483 647，或者0~4 294 967 295（無符號）
BIGINT(Length)	8位元組	範圍：-9 223 372 036 854 775 808~9 223 372 036 854 775 807，或者0~18 446 744 073 709 551 615（無符號）
FLOAT(Length, Decimals)	4位元組	具有浮動小數點的較小的數
DOUBLE(Length, Decimals)	8位元組	具有浮動小數點的較大的數
DECIMAL(Length, Decimals)	Length+1位元組或Length+2位元組	儲存爲字串的DOUBLE，允許固定的小數點
DATE	3位元組	採用YYYY-MM-DD格式
DATETIME	8位元組	採用YYYY-MM-DD HH:MM:SS格式
TIMESTAMP	4位元組	採用YYYYMMDDHHMMSS格式；可接受的範圍終止於2037年
TIME	3位元組	採用HH:MM:SS格式
ENUM	1或2位元組	Enumeration(列舉)的簡寫，這意味着每一列都可以具有多個可能的值之一
SET	1、2、3、4或8位元組	與ENUM一樣，只不過每一列都可以具有多個可能的值

• 索引

  • 主鍵 Primary Key

  • 外來鍵 Foreign Key

    • 保持數據完整性

    ALTER TABLE tbl_name
        ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name,...)
        [ON DELETE reference_option]
        [ON UPDATE reference_option]
    

    例如：

    ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`)
    

    CASCADE

    在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄

    • ON DELETE：刪除主表時自動刪除從表。刪除從表，主表不變
    • ON UPDATE：更新主表時自動更新從表。更新從表，主表不變

    SET NULL

    在父表上update/delete記錄時，將子表上匹配記錄的列設爲null (要注意子表的外來鍵列不能爲not null)

    • ON DELETE：刪除主表時自動更新從表值爲NULL。刪除從表，主表不變
    • ON UPDATE：更新主表時自動更新從表值爲NULL。更新從表，主表不變

    NO ACTION

    如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作

    • ON DELETE：從表記錄不存在時，主表纔可以刪除。刪除從表，主表不變
    • ON UPDATE：從表記錄不存在時，主表纔可以更新。更新從表，主表不變

    RESTRICT

    同no action, 都是立即檢查外來鍵約束

    SET DEFAULT

    父表有變更時,子表將外來鍵列設定成一個預設的值 但Innodb目前不支援

  • 索引 Key / Index

    • 提升查詢效率，減慢增刪改速度

  • 唯一約束 Unique

    • 保證數據不重複

• MySQL數據庫引擎

  數據庫儲存引擎是數據庫底層軟體組織，數據庫管理系統（DBMS）使用數據引擎進行建立、查詢、更新和刪除數據。不同的儲存引擎提供不同的儲存機制 機製、索引技巧、鎖定水平等功能，使用不同的儲存引擎，還可以 獲得特定的功能。現在許多不同的數據庫管理系統都支援多種不同的數據引擎。MySQL的核心就是儲存引擎。

  SHOW ENGINES  # 命令來檢視MySQL提供的引擎
  
  SHOW VARIABLES LIKE 'storage_engine'; # 檢視數據庫預設使用哪個引擎
  

  InnoDB儲存引擎

  InnoDB是事務型數據庫的首選引擎，支援事務安全表（ACID），支援行鎖定和外來鍵，InnoDB是預設的MySQL引擎。InnoDB主要特性有：

  1、InnoDB給MySQL提供了具有提交、回滾和崩潰恢復能力的事物安全（ACID相容）儲存引擎。

  InnoDB鎖定在行級並且也在SELECT語句中提供一個類似Oracle的非鎖定讀。這些功能增加了多使用者部署和效能。在SQL查詢中，可以自由地將InnoDB型別的表和其他MySQL的表型別混合起來，甚至在同一個查詢中也可以混合

  2、InnoDB是爲處理巨大數據量的最大效能設計。它的CPU效率可能是任何其他基於磁碟的關係型數據庫引擎鎖不能匹敵的

  3、InnoDB儲存引擎完全與MySQL伺服器整合，InnoDB儲存引擎爲在主記憶體中快取數據和索引而維持它自己的緩衝池。InnoDB將它的表和索引在一個邏輯表空間中，表空間可以包含數個檔案（或原始磁碟檔案）。這與MyISAM表不同，比如在MyISAM表中每個表被存放在分離的檔案中。InnoDB表可以是任何尺寸，即使在檔案尺寸被限製爲2GB的操作系統上

  4、InnoDB支援外來鍵完整性約束

  5、儲存表中的數據時，每張表的儲存都按主鍵順序存放，如果沒有顯示在表定義時指定主鍵，InnoDB會爲每一行生成一個6位元組的ROWID，並以此作爲主鍵

  6、InnoDB被用在衆多需要高效能的大型數據庫站點上

  InnoDB不建立目錄，使用InnoDB時，MySQL將在MySQL數據目錄下建立一個名爲ibdata1的10MB大小的自動擴充套件數據檔案，以及兩個名爲ib_logfile0和ib_logfile1的5MB大小的日誌檔案

  MyISAM儲存引擎

  MyISAM基於ISAM儲存引擎，並對其進行擴充套件。它是在Web、數據倉儲和其他應用環境下最常使用的儲存引擎之一。MyISAM擁有較高的插入、查詢速度，但不支援事物。MyISAM主要特性有：

  1、大檔案（達到63位檔案長度）在支援大檔案的檔案系統和操作系統上被支援

  2、當把刪除和更新及插入操作混合使用的時候，動態尺寸的行產生更少碎片。這要通過合併相鄰被刪除的塊，以及若下一個塊被刪除，就擴充套件到下一塊自動完成

  3、每個MyISAM表最大索引數是64，這可以通過重新編譯來改變。每個索引最大的列數是16

  4、最大的鍵長度是1000位元組，這也可以通過編譯來改變，對於鍵長度超過250位元組的情況，一個超過1024位元組的鍵將被用上

  5、BLOB和TEXT列可以被索引

  6、NULL被允許在索引的列中，這個值佔每個鍵的0~1個位元組

  7、所有數位鍵值以高位元組優先被儲存以允許一個更高的索引壓縮

  8、每個MyISAM型別的表都有一個AUTO_INCREMENT的內部列，當INSERT和UPDATE操作的時候該列被更新，同時AUTO_INCREMENT列將被重新整理。所以說，MyISAM型別表的AUTO_INCREMENT列更新比InnoDB型別的AUTO_INCREMENT更快

  9、可以把數據檔案和索引檔案放在不同目錄

  10、每個字元列可以有不同的字元集

  11、有VARCHAR的表可以固定或動態記錄長度

  12、VARCHAR和CHAR列可以多達64KB

  使用MyISAM引擎建立數據庫，將產生3個檔案。檔案的名字以表名字開始，擴充套件名之處檔案型別：frm檔案儲存表定義、數據檔案的擴充套件名爲.MYD（MYData）、索引檔案的擴充套件名時.MYI（MYIndex）

  MEMORY儲存引擎

  MEMORY儲存引擎將表中的數據儲存到記憶體中，未查詢和參照其他表數據提供快速存取。MEMORY主要特性有：

  1、MEMORY表的每個表可以有多達32個索引，每個索引16列，以及500位元組的最大鍵長度

  2、MEMORY儲存引擎執行HASH和BTREE縮影

  3、可以在一個MEMORY表中有非唯一鍵值

  4、MEMORY表使用一個固定的記錄長度格式

  5、MEMORY不支援BLOB或TEXT列

  6、MEMORY支援AUTO_INCREMENT列和對可包含NULL值的列的索引

  7、MEMORY表在所由用戶端之間共用（就像其他任何非TEMPORARY表）

  8、MEMORY表記憶體被儲存在記憶體中，記憶體是MEMORY表和伺服器在查詢處理時的空閒中，建立的內部表共用

  9、當不再需要MEMORY表的內容時，要釋放被MEMORY表使用的記憶體，應該執行DELETE FROM或TRUNCATE TABLE，或者刪除整個表（使用DROP TABLE）

  儲存引擎的選擇

  不同的儲存引擎都有各自的特點，以適應不同的需求，如下表所示：

如果要提供提交、回滾、崩潰恢復能力的事物安全（ACID相容）能力，並要求實現併發控制，InnoDB是一個好的選擇

如果數據表主要用來插入和查詢記錄，則MyISAM引擎能提供較高的處理效率

如果只是臨時存放數據，數據量不大，並且不需要較高的數據安全性，可以選擇將數據儲存在記憶體中的Memory引擎，MySQL中使用該引擎作爲臨時表，存放查詢的中間結果

如果只有INSERT和SELECT操作，可以選擇Archive，Archive支援高併發的插入操作，但是本身不是事務安全的。Archive非常適合儲存歸檔數據，如記錄日誌資訊可以使用Archive

使用哪一種引擎需要靈活選擇，一個數據庫中多個表可以使用不同引擎以滿足各種效能和實際需求，使用合適的儲存引擎，將會提高整個數據庫的效能

3 頭條專案數據庫

加入Gitlab專案組，克隆toutiao-backend程式碼，理解數據庫sql檔案。

==========================================

理解ORM

作用

• 省去自己拼寫SQL，保證SQL語法的正確性

• 一次編寫可以適配多個數據庫

• 防止注入攻擊

• 在數據庫表名或欄位名發生變化時，只需修改模型類的對映，無需修改數據庫操作的程式碼

  (相比SQL的話，可能需要同步修改涉及到的每一個SQL語句)

思考：

可否在已經存在數據庫表的情況下，使用模型類進行操作？

使用ORM的方式選擇

1. 先建立模型類，再遷移到數據庫中
   • 優點：簡單快捷，定義一次模型類即可，不用寫sql
   • 缺點：不能盡善盡美的控制建立表的所有細節問題，表結構發生變化的時候，也會難免發生遷移錯誤
2. 先用原生SQL建立數據庫表，再編寫模型類作對映
   • 優點：可以很好的控制數據庫表結構的任何細節，避免發生遷移錯誤
   • 缺點：可能編寫工作多（編寫sql與模型類，似乎有些牽強）

頭條專案採用編寫原生SQL建立表，之後再編寫模型類進行對映的方式。

===================================

SQLAlchemy對映構建

1 簡介

SQLAlchemy是Python程式語言下的一款開源軟體。提供了SQL工具包及物件關係對映（ORM）工具，使用MIT許可證發行。

SQLAlchemy「採用簡單的Python語言，爲高效和高效能的數據庫存取設計，實現了完整的企業級持久模型」。

SQLAlchemy首次發行於2006年2月，並迅速地在Python社羣中最廣泛使用的ORM工具之一，不亞於Django的ORM框架。

Flask-SQLAlchemy是在Flask框架的一個擴充套件，其對SQLAlchemy進行了封裝，目的於簡化在 Flask 中 SQLAlchemy 的 使用，提供了有用的預設值和額外的助手來更簡單地完成日常任務。

2 安裝

安裝Flask-SQLAlchemy

pip install flask-sqlalchemy

如果使用的是MySQL數據庫，還需要安裝MySQL的Python用戶端庫

pip install mysqlclient

3 數據庫連線設定

在Flask中使用Flask-SQLAlchemy需要進行設定，主要設定以下幾項：

• SQLALCHEMY_DATABASE_URI 數據庫的連線資訊

  • Postgres:

    postgresql://user:password@localhost/mydatabase
    

  • MySQL:

    mysql://user:password@localhost/mydatabase
    

  • Oracle:

    oracle://user:[email protected]:1521/sidname
    

  • SQLite （注意開頭的四個斜線）:

    sqlite:////absolute/path/to/foo.db
    

• SQLALCHEMY_TRACK_MODIFICATIONS 在Flask中是否追蹤數據修改

• SQLALCHEMY_ECHO 顯示生成的SQL語句，可用於偵錯

這些設定參數需要放在Flask的應用設定（app.config）中。

from flask import Flask

app = Flask(__name__)

class Config(object):
    SQLALCHEMY_DATABASE_URI = 'mysql://root:[email protected]:3306/toutiao'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)

其他設定參考如下：

名字	備註
SQLALCHEMY_DATABASE_URI	用於連線的數據庫 URI 。例如:sqlite:////tmp/test.dbmysql://username:password@server/db
SQLALCHEMY_BINDS	一個對映 binds 到連線 URI 的字典。更多 binds 的資訊見用 Binds 操作多個數據庫。
SQLALCHEMY_ECHO	如果設定爲Ture， SQLAlchemy 會記錄所有 發給 stderr 的語句，這對偵錯有用。(列印sql語句)
SQLALCHEMY_RECORD_QUERIES	可以用於顯式地禁用或啓用查詢記錄。查詢記錄 在偵錯或測試模式自動啓用。更多資訊見get_debug_queries()。
SQLALCHEMY_NATIVE_UNICODE	可以用於顯式禁用原生 unicode 支援。當使用 不合適的指定無編碼的數據庫預設值時，這對於 一些數據庫適配器是必須的（比如 Ubuntu 上 某些版本的 PostgreSQL ）。
SQLALCHEMY_POOL_SIZE	數據庫連線池的大小。預設是引擎預設值（通常 是 5 ）
SQLALCHEMY_POOL_TIMEOUT	設定連線池的連線超時時間。預設是 10 。
SQLALCHEMY_POOL_RECYCLE	多少秒後自動回收連線。這對 MySQL 是必要的， 它預設移除閒置多於 8 小時的連線。注意如果 使用了 MySQL ， Flask-SQLALchemy 自動設定 這個值爲 2 小時。

4 模型類欄位與選項

欄位型別

型別名	python中型別	說明
Integer	int	普通整數，一般是32位元
SmallInteger	int	取值範圍小的整數，一般是16位元
BigInteger	int或long	不限制精度的整數
Float	float	浮點數
Numeric	decimal.Decimal	普通整數，一般是32位元
String	str	變長字串
Text	str	變長字串，對較長或不限長度的字串做了優化
Unicode	unicode	變長Unicode字串
UnicodeText	unicode	變長Unicode字串，對較長或不限長度的字串做了優化
Boolean	bool	布爾值
Date	datetime.date	時間
Time	datetime.datetime	日期和時間
LargeBinary	str	二進制檔案

列選項

選項名	說明
primary_key	如果爲True，代表表的主鍵
unique	如果爲True，代表這列不允許出現重複的值
index	如果爲True，爲這列建立索引，提高查詢效率
nullable	如果爲True，允許有空值，如果爲False，不允許有空值
default	爲這列定義預設值

關係選項

選項名	說明
backref	在關係的另一模型中新增反向參照
primary join	明確指定兩個模型之間使用的聯結條件
uselist	如果爲False，不使用列表，而使用標量值
order_by	指定關係中記錄的排序方式
secondary	指定多對多關係中關係表的名字
secondary join	在SQLAlchemy中無法自行決定時，指定多對多關係中的二級聯結條件

5 構建模型類對映

例用虛擬機器中已有的頭條數據庫，構建模型類對映，以下面 下麪三張表爲例

CREATE TABLE `user_basic` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '使用者ID',
  `account` varchar(20) COMMENT '賬號',
  `email` varchar(20) COMMENT '郵箱',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '狀態，是否可用，0-不可用，1-可用',
  `mobile` char(11) NOT NULL COMMENT '手機號',
  `password` varchar(93) NULL COMMENT '密碼',
  `user_name` varchar(32) NOT NULL COMMENT '暱稱',
  `profile_photo` varchar(128) NULL COMMENT '頭像',
  `last_login` datetime NULL COMMENT '最後登錄時間',
  `is_media` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是自媒體，0-不是，1-是',
  `is_verified` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否實名認證，0-不是，1-是',
  `introduction` varchar(50) NULL COMMENT '簡介',
  `certificate` varchar(30) NULL COMMENT '認證',
  `article_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '發文章數',
  `following_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '關注的人數',
  `fans_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '被關注的人數',
  `like_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累計點贊人數',
  `read_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累計閱讀人數',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `mobile` (`mobile`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者基本資訊表';

CREATE TABLE `user_profile` (
  `user_id` bigint(20) unsigned NOT NULL COMMENT '使用者ID',
  `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性別，0-男，1-女',
  `birthday` date NULL COMMENT '生日',
  `real_name` varchar(32) NULL COMMENT '真實姓名',
  `id_number` varchar(20) NULL COMMENT '身份證號',
  `id_card_front` varchar(128) NULL COMMENT '身份證正面',
  `id_card_back` varchar(128) NULL COMMENT '身份證背面',
  `id_card_handheld` varchar(128) NULL COMMENT '手持身份證',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `register_media_time` datetime NULL COMMENT '註冊自媒體時間',
  `area` varchar(20) COMMENT '地區',
  `company` varchar(20) COMMENT '公司',
  `career` varchar(20) COMMENT '職業',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資料表';

CREATE TABLE `user_relation` (
  `relation_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '使用者ID',
  `target_user_id` bigint(20) unsigned NOT NULL COMMENT '目標使用者ID',
  `relation` tinyint(1) NOT NULL DEFAULT '0' COMMENT '關係，0-取消，1-關注，2-拉黑',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`relation_id`),
  UNIQUE KEY `user_target` (`user_id`, `target_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者關係表';

首先需要建立SQLAlchemy物件:

• 方式一：

    db = SQLAlchemy(app)
  

• 方式二：

    db = SQLAlchemy()
    db.init_app(app)
  

  注意此方式在單獨執行偵錯時，對數據庫操作需要在Flask的應用上下文中進行，即

    with app.app_context():
        User.query.all()
  

定義模型類

class User(db.Model):
    """
    使用者基本資訊
    """
    __tablename__ = 'user_basic'

    class STATUS:
        ENABLE = 1
        DISABLE = 0

    id = db.Column('user_id', db.Integer, primary_key=True, doc='使用者ID')
    mobile = db.Column(db.String, doc='手機號')
    password = db.Column(db.String, doc='密碼')
    name = db.Column('user_name', db.String, doc='暱稱')
    profile_photo = db.Column(db.String, doc='頭像')
    last_login = db.Column(db.DateTime, doc='最後登錄時間')
    is_media = db.Column(db.Boolean, default=False, doc='是否是自媒體')
    is_verified = db.Column(db.Boolean, default=False, doc='是否實名認證')
    introduction = db.Column(db.String, doc='簡介')
    certificate = db.Column(db.String, doc='認證')
    article_count = db.Column(db.Integer, default=0, doc='發帖數')
    following_count = db.Column(db.Integer, default=0, doc='關注的人數')
    fans_count = db.Column(db.Integer, default=0, doc='被關注的人數（粉絲數）')
    like_count = db.Column(db.Integer, default=0, doc='累計點贊人數')
    read_count = db.Column(db.Integer, default=0, doc='累計閱讀人數')

    account = db.Column(db.String, doc='賬號')
    email = db.Column(db.String, doc='郵箱')
    status = db.Column(db.Integer, default=1, doc='狀態，是否可用')

class UserProfile(db.Model):
    """
    使用者資料表
    """
    __tablename__ = 'user_profile'

    class GENDER:
        MALE = 0
        FEMALE = 1

    id = db.Column('user_id', db.Integer, primary_key=True, doc='使用者ID')
    gender = db.Column(db.Integer, default=0, doc='性別')
    birthday = db.Column(db.Date, doc='生日')
    real_name = db.Column(db.String, doc='真實姓名')
    id_number = db.Column(db.String, doc='身份證號')
    id_card_front = db.Column(db.String, doc='身份證正面')
    id_card_back = db.Column(db.String, doc='身份證背面')
    id_card_handheld = db.Column(db.String, doc='手持身份證')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='建立時間')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新時間')
    register_media_time = db.Column(db.DateTime, doc='註冊自媒體時間')

    area = db.Column(db.String, doc='地區')
    company = db.Column(db.String, doc='公司')
    career = db.Column(db.String, doc='職業')


class Relation(db.Model):
    """
    使用者關係表
    """
    __tablename__ = 'user_relation'

    class RELATION:
        DELETE = 0
        FOLLOW = 1
        BLACKLIST = 2

    id = db.Column('relation_id', db.Integer, primary_key=True, doc='主鍵ID')
    user_id = db.Column(db.Integer, doc='使用者ID')
    target_user_id = db.Column(db.Integer, doc='目標使用者ID')
    relation = db.Column(db.Integer, doc='關係')
    ctime = db.Column('create_time', db.DateTime, default=datetime.now, doc='建立時間')
    utime = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新時間')

============================================

SQLAlchemy操作

1 新增

user = User(mobile='15612345678', name='itcast')
db.session.add(user)
db.session.commit()
profile = Profile(id=user.id)
db.session.add(profile)
db.session.commit()

對於批次新增也可使用如下語法

db.session.add_all([user1, user2, user3])
db.session.commit()

2 查詢

all()

查詢所有，返回列表

User.query.all()

first()

查詢第一個，返回物件

User.query.first()

get()

根據主鍵ID獲取物件，若主鍵不存在返回None

User.query.get(2)

另一種查詢方式

db.session.query(User).all()
db.session.query(User).first()
db.session.query(User).get(2)

filter_by

進行過慮

User.query.filter_by(mobile='13911111111').first()
User.query.filter_by(mobile='13911111111', id=1).first()  # and關係

filter

進行過慮

User.query.filter(User.mobile=='13911111111').first()

邏輯或

from sqlalchemy import or_
User.query.filter(or_(User.mobile=='13911111111', User.name.endswith('號'))).all()

邏輯與

from sqlalchemy import and_
User.query.filter(and_(User.name != '13911111111', User.mobile.startswith('185'))).all()

邏輯非

from sqlalchemy import not_
User.query.filter(not_(User.mobile == '13911111111')).all()

offset

偏移，起始位置

User.query.offset(2).all()

limit

獲取限制數據

User.query.limit(3).all()

order_by

排序

User.query.order_by(User.id).all()  # 正序
User.query.order_by(User.id.desc()).all()  # 倒序

複合查詢

User.query.filter(User.name.startswith('13')).order_by(User.id.desc()).offset(2).limit(5).all()

query = User.query.filter(User.name.startswith('13'))
query = query.order_by(User.id.desc())
query = query.offset(2).limit(5)
ret = query.all()

優化查詢

user = User.query.filter_by(id=1).first()  # 查詢所有欄位
select user_id, mobile......

select * from   # 程式不要使用
select user_id, mobile,.... # 查詢指定欄位

from sqlalchemy.orm import load_only
User.query.options(load_only(User.name, User.mobile)).filter_by(id=1).first() # 查詢特定欄位

聚合查詢

from sqlalchemy import func

db.session.query(Relation.user_id, func.count(Relation.target_user_id)).filter(Relation.relation == Relation.RELATION.FOLLOW).group_by(Relation.user_id).all()

關聯查詢

1. 使用ForeignKey

class User(db.Model):
    ...
    profile = db.relationship('UserProfile', uselist=False)
    followings = db.relationship('Relation')

class UserProfile(db.Model):
    id = db.Column('user_id', db.Integer, db.ForeignKey('user_basic.user_id'), primary_key=True,  doc='使用者ID')
    ...

class Relation(db.Model):
    user_id = db.Column(db.Integer, db.ForeignKey('user_basic.user_id'), doc='使用者ID')
    ...

# 測試   
user = User.query.get(1)
user.profile.gender
user.followings

2. 使用primaryjoin

class User(db.Model):
    ...

    profile = db.relationship('UserProfile', primaryjoin='User.id==foreign(UserProfile.id)', uselist=False)
    followings = db.relationship('Relation', primaryjoin='User.id==foreign(Relation.user_id)')

# 測試
user = User.query.get(1)
user.profile.gender
user.followings

3. 指定欄位關聯查詢

class Relation(db.Model):
    ...
    target_user = db.relationship('User', primaryjoin='Relation.target_user_id==foreign(User.id)', uselist=False)

from sqlalchemy.orm import load_only, contains_eager

Relation.query.join(Relation.target_user).options(load_only(Relation.target_user_id), contains_eager(Relation.target_user).load_only(User.name)).all()

3 更新

• 方式一

    user = User.query.get(1)
    user.name = 'Python'
    db.session.add(user)
    db.session.commit()
  

• 方式二

    User.query.filter_by(id=1).update({'name':'python'})
    db.session.commit()
  

4 刪除

• 方式一

    user = User.query.order_by(User.id.desc()).first()
    db.session.delete(user)
    db.session.commit()
  

• 方式二

    User.query.filter(User.mobile='18512345678').delete()
    db.session.commit()
  

5 事務

environ = {'wsgi.version':(1,0), 'wsgi.input': '', 'REQUEST_METHOD': 'GET', 'PATH_INFO': '/', 'SERVER_NAME': 'itcast server', 'wsgi.url_scheme': 'http', 'SERVER_PORT': '80'}

with app.request_context(environ):
    try:
        user = User(mobile='18911111111', name='itheima')
        db.session.add(user)
        db.session.flush() # 將db.session記錄的sql傳到數據庫中執行
        profile = UserProfile(id=user.id)
        db.session.add(profile)
        db.session.commit()
    except:
        db.session.rollback()

========================================

數據庫理論

1. 複製集與分佈式

• 複製集（Replication）
  • 數據庫中數據相同，起到備份作用
  • 高可用 High Available HA
• 分佈式（Distribution）
  • 數據庫中數據不同，共同組成完整的數據集合
  • 通常每個節點被稱爲一個分片（shard)
  • 高吞吐 High Throughput
• 複製集與分佈式可以單獨使用，也可以組合使用（即每個分片都組建一個複製集）
• 關於主（Master）從（Slave）
  • 這個概念是從使用的角度來闡述問題的
  • 主節點 -> 表示程式在這個節點上最先更新數據
  • 從節點 -> 表示這個節點的數據是要通過複製主節點而來
  • 複製集 可選 主從、主主、主主從從
  • 分佈式 每個分片都是主，組合使用複製集的時候，複製集的是從

2. MySQL

1） 主從複製

複製分成三步：

1. master將改變記錄到二進制日誌(binary log)中（這些記錄叫做二進制日誌事件，binary log events）；
2. slave將master的binary log events拷貝到它的中繼日誌(relay log)；
3. slave重做中繼日誌中的事件，將改變反映它自己的數據。

下圖描述了這一過程：

該過程的第一部分就是master記錄二進制日誌。在每個事務更新數據完成之前，master在二日誌記錄這些改變。MySQL將事務序列的寫入二進制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進制日誌完成後，master通知儲存引擎提交事務。

下一步就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作執行緒——I/O執行緒。I/O執行緒在master上開啓一個普通的連線，然後開始binlog dump process。Binlog dump process從master的二進制日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O執行緒將這些事件寫入中繼日誌。

SQL slave thread處理該過程的最後一步。SQL執行緒從中繼日誌讀取事件，更新slave的數據，使其與master中的數據一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。

此外，在master中也有一個工作執行緒：和其它MySQL的連線一樣，slave在master中開啓一個連線也會使得master開始一個執行緒。

利用主從在達到高可用的同時，也可以通過讀寫分離提供吞吐量。

思考：讀寫分離對事務是否有影響？

對於寫操作包括開啓事務和提交或回滾要在一臺機器上執行，分散到多臺master執行後數據庫原生的單機事務就失效了。

對於事務中同時包含讀寫操作，與事務隔離級別設定有關，如果事務隔離級別爲read-uncommitted 或者 read-committed，讀寫分離沒影響，如果隔離級別爲repeatable-read、serializable，讀寫分離就有影響，因爲在slave上會看到新數據，而正在事務中的master看不到新數據。

2）分庫分表（sharding）

分庫分表前的問題

任何問題都是太大或者太小的問題，我們這裏面對的數據量太大的問題。

• 使用者請求量太大

  因爲單伺服器TPS，記憶體，IO都是有限的。 解決方法：分散請求到多個伺服器上； 其實使用者請求和執行一個sql查詢是本質是一樣的，都是請求一個資源，只是使用者請求還會經過閘道器，路由，http伺服器等。

• 單庫太大

  單個數據庫處理能力有限；單庫所在伺服器上磁碟空間不足；單庫上操作的IO瓶頸 解決方法：切分成更多更小的庫

• 單表太大

  CRUD都成問題；索引膨脹，查詢超時 解決方法：切分成多個數據集更小的表。

分庫分表的方式方法

一般就是垂直切分和水平切分，這是一種結果集描述的切分方式，是物理空間上的切分。 我們從面臨的問題，開始解決，闡述： 首先是使用者請求量太大，我們就堆機器搞定（這不是本文重點）。

然後是單個庫太大，這時我們要看是因爲表多而導致數據多，還是因爲單張表裏面的數據多。 如果是因爲表多而數據多，使用垂直切分，根據業務切分成不同的庫。

如果是因爲單張表的數據量太大，這時要用水平切分，即把表的數據按某種規則切分成多張表，甚至多個庫上的多張表。 分庫分表的順序應該是先垂直分，後水平分。 因爲垂直分更簡單，更符合我們處理現實世界問題的方式。

垂直拆分

1. 垂直分表

   也就是「大表拆小表」，基於列欄位進行的。一般是表中的欄位較多，將不常用的， 數據較大，長度較長（比如text型別欄位）的拆分到「擴充套件表「。 一般是針對那種幾百列的大表，也避免查詢時，數據量太大造成的「跨頁」問題。

2. 垂直分庫

   垂直分庫針對的是一個系統中的不同業務進行拆分，比如使用者User一個庫，商品Producet一個庫，訂單Order一個庫。 切分後，要放在多個伺服器上，而不是一個伺服器上。爲什麼？ 我們想象一下，一個購物網站對外提供服務，會有使用者，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數據庫的單庫處理能力成爲瓶頸。按垂直分庫後，如果還是放在一個數據庫伺服器上， 隨着使用者量增大，這會讓單個數據庫的處理能力成爲瓶頸，還有單個伺服器的磁碟空間，記憶體，tps等非常吃緊。 所以我們要拆分到多個伺服器上，這樣上面的問題都解決了，以後也不會面對單機資源問題。

   數據庫業務層面的拆分，和服務的「治理」，「降級」機制 機製類似，也能對不同業務的數據分別的進行管理，維護，監控，擴充套件等。 數據庫往往最容易成爲應用系統的瓶頸，而數據庫本身屬於「有狀態」的，相對於Web和應用伺服器來講，是比較難實現「橫向擴充套件」的。 數據庫的連線資源比較寶貴且單機處理能力也有限，在高併發場景下，垂直分庫一定程度上能夠突破IO、連線數及單機硬體資源的瓶頸。

水平拆分

1. 水平分表

   針對數據量巨大的單張表（比如訂單表），按照某種規則（RANGE,HASH取模等），切分到多張表裏面去。 但是這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。不建議採用。

2. 水平分庫分表

   將單張表的數據切分到多個伺服器上去，每個伺服器具有相應的庫與表，只是表中數據集合不同。 水平分庫分表能夠有效的緩解單機和單庫的效能瓶頸和壓力，突破IO、連線數、硬體資源等的瓶頸。

3. 水平分庫分表切分規則

4. 1. RANGE

      從0到10000一個表，10001到20000一個表；

   2. HASH取模 離散化

      一個商場系統，一般都是將使用者，訂單作爲主表，然後將和它們相關的作爲附表，這樣不會造成跨庫事務之類的問題。 取使用者id，然後hash取模，分配到不同的數據庫上。

   3. 地理區域

      比如按照華東，華南，華北這樣來區分業務，七牛雲應該就是如此。

   4. 時間

      按照時間切分，就是將6個月前，甚至一年前的數據切出去放到另外的一張表，因爲隨着時間流逝，這些表的數據 被查詢的概率變小，所以沒必要和「熱數據」放在一起，這個也是「冷熱數據分離」。

分庫分表後面臨的問題

• 事務支援

  分庫分表後，就成了分佈式事務了。如果依賴數據庫本身的分佈式事務管理功能去執行事務，將付出高昂的效能代價； 如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成程式設計方面的負擔。

• 多庫結果集合併（group by，order by）

• 跨庫join

  分庫分表後表之間的關聯操作將受到限制，我們無法join位於不同分庫的表，也無法join分表粒度不同的表， 結果原本一次查詢能夠完成的業務，可能需要多次查詢才能 纔能完成。 粗略的解決方法： 全域性表：基礎數據，所有庫都拷貝一份。 欄位冗餘：這樣有些欄位就不用join去查詢了。 系統層組裝：分別查詢出所有，然後組裝起來，較複雜。

分庫分表方案產品

目前市面上的分庫分表中介軟體相對較多，其中基於代理方式的有MySQL Proxy和Amoeba， 基於Hibernate框架的是Hibernate Shards，基於jdbc的有當當sharding-jdbc， 基於mybatis的類似maven外掛式的有蘑菇街的蘑菇街TSharding， 通過重寫spring的ibatis template類的Cobar Client。

還有一些大公司的開源產品：

3 黑馬頭條專案應用

• 主從

• 垂直分表

  CREATE TABLE `user_basic` (
    `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '使用者ID',
    `account` varchar(20) COMMENT '賬號',
    `email` varchar(20) COMMENT '郵箱',
    `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '狀態，是否可用，0-不可用，1-可用',
    `mobile` char(11) NOT NULL COMMENT '手機號',
    `password` varchar(93) NULL COMMENT '密碼',
    `user_name` varchar(32) NOT NULL COMMENT '暱稱',
    `profile_photo` varchar(128) NULL COMMENT '頭像',
    `last_login` datetime NULL COMMENT '最後登錄時間',
    `is_media` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是自媒體，0-不是，1-是',
    `is_verified` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否實名認證，0-不是，1-是',
    `introduction` varchar(50) NULL COMMENT '簡介',
    `certificate` varchar(30) NULL COMMENT '認證',
    `article_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '發文章數',
    `following_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '關注的人數',
    `fans_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '被關注的人數',
    `like_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累計點贊人數',
    `read_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累計閱讀人數',
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `mobile` (`mobile`),
    UNIQUE KEY `user_name` (`user_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者基本資訊表';
  
  CREATE TABLE `user_profile` (
    `user_id` bigint(20) unsigned NOT NULL COMMENT '使用者ID',
    `gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性別，0-男，1-女',
    `birthday` date NULL COMMENT '生日',
    `real_name` varchar(32) NULL COMMENT '真實姓名',
    `id_number` varchar(20) NULL COMMENT '身份證號',
    `id_card_front` varchar(128) NULL COMMENT '身份證正面',
    `id_card_back` varchar(128) NULL COMMENT '身份證背面',
    `id_card_handheld` varchar(128) NULL COMMENT '手持身份證',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    `register_media_time` datetime NULL COMMENT '註冊自媒體時間',
    `area` varchar(20) COMMENT '地區',
    `company` varchar(20) COMMENT '公司',
    `career` varchar(20) COMMENT '職業',
    PRIMARY KEY (`user_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資料表';
  

  CREATE TABLE `news_article_basic` (
    `article_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '文章ID',
    `user_id` bigint(20) unsigned NOT NULL COMMENT '使用者ID',
    `channel_id` int(11) unsigned NOT NULL COMMENT '頻道ID',
    `title` varchar(128) NOT NULL COMMENT '標題',
    `cover` json NOT NULL COMMENT '封面',
    `is_advertising` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否投放廣告，0-不投放，1-投放',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '貼文狀態，0-草稿，1-待審覈，2-審覈通過，3-審覈失敗，4-已刪除',
    `reviewer_id` int(11) NULL COMMENT '審覈人員ID',
    `review_time` datetime NULL COMMENT '審覈時間',
    `delete_time` datetime NULL COMMENT '刪除時間',
    `reject_reason` varchar(200) COMMENT '駁回原因',
    `comment_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '累計評論數',
    `allow_comment` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否允許評論，0-不允許，1-允許',
    PRIMARY KEY (`article_id`),
    KEY `user_id` (`user_id`),
    KEY `article_status` (`status`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章基本資訊表';
  
  CREATE TABLE `news_article_content` (
    `article_id` bigint(20) unsigned NOT NULL COMMENT '文章ID',
    `content` longtext NOT NULL COMMENT '文章內容',
    PRIMARY KEY (`article_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='文章內容表';

======================================

分佈式ID

1 方案選擇

• UUID

  UUID是通用唯一識別碼（Universally Unique Identifier)的縮寫，開放軟體基金會(OSF)規範定義了包括網絡卡MAC地址、時間戳、命名空間（Namespace）、隨機或僞亂數、時序等元素。利用這些元素來生成UUID。

  UUID是由128位元二進制組成，一般轉換成十六進制，然後用String表示。

  550e8400-e29b-41d4-a716-446655440000

  UUID的優點:

  • 通過本地生成，沒有經過網路I/O，效能較快
  • 無序，無法預測他的生成順序。(當然這個也是他的缺點之一)

  UUID的缺點:

  • 128位元二進制一般轉換成36位元的16進位制，太長了只能用String儲存，空間佔用較多。
  • 不能生成遞增有序的數位

• 數據庫主鍵自增

  大家對於唯一標識最容易想到的就是主鍵自增，這個也是我們最常用的方法。例如我們有個訂單服務，那麼把訂單id設定爲主鍵自增即可。

  • 單獨數據庫 記錄主鍵值

  • 業務數據庫分別設定不同的自增起始值和固定步長，如

    第一臺 start 1  step 9 
    第二臺 start 2  step 9 
    第三臺 start 3  step 9
    

  優點:

  • 簡單方便，有序遞增，方便排序和分頁

  缺點:

  • 分庫分表會帶來問題，需要進行改造。
  • 併發效能不高，受限於數據庫的效能。
  • 簡單遞增容易被其他人猜測利用，比如你有一個使用者服務用的遞增，那麼其他人可以根據分析註冊的使用者ID來得到當天你的服務有多少人註冊，從而就能猜測出你這個服務當前的一個大概狀況。
  • 數據庫宕機服務不可用。

• Redis

  熟悉Redis的同學，應該知道在Redis中有兩個命令Incr，IncrBy,因爲Redis是單執行緒的所以能保證原子性。

  優點：

  • 效能比數據庫好，能滿足有序遞增。

  缺點：

  • 由於redis是記憶體的KV數據庫，即使有AOF和RDB，但是依然會存在數據丟失，有可能會造成ID重複。
  • 依賴於redis，redis要是不穩定，會影響ID生成。

• 雪花演算法-Snowflake

  Snowflake是Twitter提出來的一個演算法，其目的是生成一個64bit的整數:

• 1bit:一般是符號位，不做處理
• 41bit:用來記錄時間戳，這裏可以記錄69年，如果設定好起始時間比如今年是2018年，那麼可以用到2089年，到時候怎麼辦？要是這個系統能用69年，我相信這個系統早都重構了好多次了。
• 10bit:10bit用來記錄機器ID，總共可以記錄1024臺機器，一般用前5位代表數據中心，後面5位是某個數據中心的機器ID
• 12bit:回圈位，用來對同一個毫秒之內產生不同的ID，12位元可以最多記錄4095個，也就是在同一個機器同一毫秒最多記錄4095個，多餘的需要進行等待下毫秒。

上面只是一個將64bit劃分的標準，當然也不一定這麼做，可以根據不同業務的具體場景來劃分，比如下面 下麪給出一個業務場景：

• 服務目前QPS10萬，預計幾年之內會發展到百萬。
• 當前機器三地部署，上海，北京，深圳都有。
• 當前機器10台左右，預計未來會增加至百台。

這個時候我們根據上面的場景可以再次合理的劃分62bit,QPS幾年之內會發展到百萬，那麼每毫秒就是千級的請求，目前10臺機器那麼每臺機器承擔百級的請求，爲了保證擴充套件，後面的回圈位可以限制到1024，也就是2^10，那麼回圈位10位就足夠了。

機器三地部署我們可以用3bit總共8來表示機房位置，當前的機器10台，爲了保證擴充套件到百台那麼可以用7bit 128來表示，時間位依然是41bit,那麼還剩下64-10-3-7-41-1 = 2bit,還剩下2bit可以用來進行擴充套件。

時鐘回撥

因爲機器的原因會發生時間回撥，我們的雪花演算法是強依賴我們的時間的，如果時間發生回撥，有可能會生成重複的ID，在我們上面的nextId中我們用當前時間和上一次的時間進行判斷，如果當前時間小於上一次的時間那麼肯定是發生了回撥，演算法會直接拋出異常.

2 黑馬頭條

使用雪花演算法 （程式碼 toutiao-backend/common/utils/snowflake)

# Twitter's Snowflake algorithm implementation which is used to generate distributed IDs.
# https://github.com/twitter-archive/snowflake/blob/snowflake-2010/src/main/scala/com/twitter/service/snowflake/IdWorker.scala

import time
import logging

class InvalidSystemClock(Exception):
    """
    時鐘回撥異常
    """
    pass

# 64位元ID的劃分
WORKER_ID_BITS = 5
DATACENTER_ID_BITS = 5
SEQUENCE_BITS = 12

# 最大取值計算
MAX_WORKER_ID = -1 ^ (-1 << WORKER_ID_BITS)  # 2**5-1 0b11111
MAX_DATACENTER_ID = -1 ^ (-1 << DATACENTER_ID_BITS)

# 移位偏移計算
WOKER_ID_SHIFT = SEQUENCE_BITS
DATACENTER_ID_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS
TIMESTAMP_LEFT_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS + DATACENTER_ID_BITS

# 序號回圈掩碼
SEQUENCE_MASK = -1 ^ (-1 << SEQUENCE_BITS)

# Twitter元年時間戳
TWEPOCH = 1288834974657


logger = logging.getLogger('flask.app')


class IdWorker(object):
    """
    用於生成IDs
    """

    def __init__(self, datacenter_id, worker_id, sequence=0):
        """
        初始化
        :param datacenter_id: 數據中心（機器區域）ID
        :param worker_id: 機器ID
        :param sequence: 其實序號
        """
        # sanity check
        if worker_id > MAX_WORKER_ID or worker_id < 0:
            raise ValueError('worker_id值越界')

        if datacenter_id > MAX_DATACENTER_ID or datacenter_id < 0:
            raise ValueError('datacenter_id值越界')

        self.worker_id = worker_id
        self.datacenter_id = datacenter_id
        self.sequence = sequence

        self.last_timestamp = -1  # 上次計算的時間戳

    def _gen_timestamp(self):
        """
        生成整數時間戳
        :return:int timestamp
        """
        return int(time.time() * 1000)

    def get_id(self):
        """
        獲取新ID
        :return:
        """
        timestamp = self._gen_timestamp()

        # 時鐘回撥
        if timestamp < self.last_timestamp:
            logging.error('clock is moving backwards. Rejecting requests until {}'.format(self.last_timestamp))
            raise InvalidSystemClock

        if timestamp == self.last_timestamp:
            self.sequence = (self.sequence + 1) & SEQUENCE_MASK
            if self.sequence == 0:
                timestamp = self._til_next_millis(self.last_timestamp)
        else:
            self.sequence = 0

        self.last_timestamp = timestamp

        new_id = ((timestamp - TWEPOCH) << TIMESTAMP_LEFT_SHIFT) | (self.datacenter_id << DATACENTER_ID_SHIFT) | \
                 (self.worker_id << WOKER_ID_SHIFT) | self.sequence
        return new_id

    def _til_next_millis(self, last_timestamp):
        """
        等到下一毫秒
        """
        timestamp = self._gen_timestamp()
        while timestamp <= last_timestamp:
            timestamp = self._gen_timestamp()
        return timestamp


if __name__ == '__main__':
    worker = IdWorker(1, 2, 0)
    print(worker.get_id())

===============================

數據庫優化

數據庫是Web應用至關重要的一個環節，其效能的優劣會影響整合Web應用，所以需要對數據庫進化優化以提高使用效能。以下提供幾點方法作爲參考。

1 理解索引

2 SQL查詢優化

• 避免全表掃描，應考慮在 where 及 order by 涉及的列上建立索引；

• 查詢時使用select明確指明所要查詢的欄位，避免使用select *的操作；

• SQL語句儘量大寫，如

    SELECT name FROM t WHERE id=1
  

  對於小寫的sql語句，通常數據庫在解析sql語句時，通常會先轉換成大寫再執行。

• 儘量避免在 where 子句中使用!=或<>操作符， MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE；

    SELECT id FROM t WHERE name LIKE ‘abc%’
  

• 對於模糊查詢，如：

    SELECT id FROM t WHERE name LIKE ‘%abc%’
  

  或者

    SELECT id FROM t WHERE name LIKE ‘%abc’
  

  將導致全表掃描，應避免使用，若要提高效率，可以考慮全文檢索；

• 遵循最左原則，在where子句中寫查詢條件時把索引欄位放在前面，如

    mobile爲索引欄位，name爲非索引欄位
    推薦
    SELECT ... FROM t WHERE mobile='13911111111' AND name='python'
    不推薦
    SELECT ... FROM t WHERE name='python' AND mobile='13911111111' 
  
    建立了複合索引 key(a, b, c)
    推薦
    SELECT ... FROM t WHERE a=... AND b=... AND c= ...
    SELECT ... FROM t WHERE a=... AND b=...
    SELECT ... FROM t WHERE a=...
    不推薦 (欄位出現順序不符合索引建立的順序)
    SELECT ... FROM t WHERE b=... AND c=...
    SELECT ... FROM t WHERE b=... AND a=... AND c=...
    ...
  

• 能使用關聯查詢解決的儘量不要使用子查詢，如

    子查詢
    SELECT article_id, title FROM t_article WHERE user_id IN (SELECT user_id FROM t_user  WHERE user_name IN ('itcast', 'itheima', 'python'))
  
    關聯查詢(推薦)
    SELECT b.article_id, b.title From t_user AS a INNER JOIN t_article AS b ON a.user_id=b.user_id WHERE a.user_name IN ('itcast', 'itheima', 'python');
  

  能不使用關聯查詢的儘量不要使用關聯查詢；

• 不需要獲取全表數據的時候，不要查詢全表數據，使用LIMIT來限制數據。

3 數據庫優化

• 在進行表設計時，可適度增加冗餘欄位(反範式設計)，減少JOIN操作；
• 多欄位表可以進行垂直分表優化，多數據表可以進行水平分表優化；
• 選擇恰當的數據型別，如整型的選擇；
• 對於強調快速讀取的操作，可以考慮使用MyISAM數據庫引擎；
• 對較頻繁的作爲查詢條件的欄位建立索引；唯一性太差的欄位不適合單獨建立索引，即使頻繁作爲查詢條件；更新非常頻繁的欄位不適合建立索引；
• 編寫SQL時使用上面的方式對SQL語句進行優化；
• 使用慢查詢工具找出效率低下的SQL語句進行優化；
• 構建快取，減少數據庫磁碟操作；
• 可以考慮結合使用內在型數據庫，如Redis，進行混合儲存。

===================================

Redis

1 Redis事務

基本事務指令

Redis提供了一定的事務支援，可以保證一組操作原子執行不被打斷，但是如果執行中出現錯誤，事務不能回滾，Redis未提供回滾支援。

• multi 開啓事務
• exec 執行事務

127.0.0.1:6379> multi
OK
127.0.0.1:6379> set a 100
QUEUED
127.0.0.1:6379> set b 200
QUEUED
127.0.0.1:6379> get a
QUEUED
127.0.0.1:6379> get b
QUEUED
127.0.0.1:6379> exec
1) OK
2) OK
3) "100"
4) "200"

使用multi開啓事務後，操作的指令並未立即執行，而是被redis記錄在佇列中，等待一起執行。當執行exec命令後，開始執行事務指令，最終得到每條指令的結果。

127.0.0.1:6379> multi
OK
127.0.0.1:6379> set c 300
QUEUED
127.0.0.1:6379> hgetall a
QUEUED
127.0.0.1:6379> set d 400
QUEUED
127.0.0.1:6379> get d
QUEUED
127.0.0.1:6379> exec
1) OK
2) (error) WRONGTYPE Operation against a key holding the wrong kind of value
3) OK
4) "400"
127.0.0.1:6379>

如果事務中出現了錯誤，事務並不會終止執行，而是隻會記錄下這條錯誤的資訊，並繼續執行後面的指令。所以事務中出錯不會影響後續指令的執行。

Python用戶端操作

在Redis的Python 用戶端庫redis-py中，提供了pipeline (稱爲流水線 或 管道)，該工具的作用是：

• 在用戶端統一收集操作指令
• 補充上multi和exec指令，當作一個事務發送到redis伺服器執行

from redis import StrictRedis
r = StrictRedis.from_url('redis://127.0.0.1:6381/0')
pl = r.pipeline()
pl.set('a', 100)
pl.set('b', 200)
pl.get('a')
pl.get('b')
ret = pl.execute()
print(ret) #  [True, True, b'100', b'200']

watch監視

若在構建的redis事務在執行時依賴某些值，可以使用watch對數據值進行監視。

127.0.0.1:6379> set stock 100
OK
127.0.0.1:6379> watch stock
OK
127.0.0.1:6379> multi
OK
127.0.0.1:6379> incrby stock -1
QUEUED
127.0.0.1:6379> incr sales
QUEUED
127.0.0.1:6379> exec
1) (integer) 99
2) (integer) 1

事務exec執行前被監視的stock值未變化，事務正確執行。

127.0.0.1:6379> set stock 100
OK
127.0.0.1:6379> watch stock
OK
127.0.0.1:6379> multi
OK
127.0.0.1:6379> incrby stock -1
QUEUED
127.0.0.1:6379> incr sales
QUEUED

此時在另一個用戶端修改stock的值，執行

127.0.0.1:6379> incrby stock -2
(integer) 98

當第一個用戶端再執行exec時

127.0.0.1:6379> exec
(nil)

表明事務需要監視的stock值發生了變化，事務不能執行了。

注意：Redis Cluster 叢集不支援事務

2 Redis持久化

redis可以將數據寫入到磁碟中，在停機或宕機後，再次啓動redis時，將磁碟中的備份數據載入到記憶體中恢復使用。這是redis的持久化。持久化有如下兩種機制 機製。

RDB 快照持久化

redis可以將記憶體中的數據寫入磁碟進行持久化。在進行持久化時，redis會建立子進程來執行。

redis預設開啓了快照持久化機制 機製。

進行快照持久化的時機如下

• 定期觸發

  redis的組態檔

    #   save  
    #
    #   Will save the DB if both the given number of seconds and the given
    #   number of write operations against the DB occurred.
    #
    #   In the example below the behaviour will be to save:
    #   after 900 sec (15 min) if at least 1 key changed
    #   after 300 sec (5 min) if at least 10 keys changed
    #   after 60 sec if at least 10000 keys changed
    #
    #   Note: you can disable saving completely by commenting out all "save" lines.
    #
    #   It is also possible to remove all the previously configured save
    #   points by adding a save directive with a single empty string argument
    #   like in the following example:
    #
    #   save ""
  
    save 900 1
    save 300 10
    save 60 10000
  

• BGSAVE

  執行BGSAVE命令，手動觸發RDB持久化

• SHUTDOWN

  關閉redis時觸發

AOF 追加檔案持久化

redis可以將執行的所有指令追加記錄到檔案中持久化儲存，這是redis的另一種持久化機制 機製。

redis預設未開啓AOF機制 機製。

redis可以通過設定如下項開啓AOF機制 機製

appendonly yes  # 是否開啓AOF
appendfilename "appendonly.aof"  # AOF檔案

AOF機制 機製記錄操作的時機

# appendfsync always  # 每個操作都寫到磁碟中
appendfsync everysec  # 每秒寫一次磁碟，預設
# appendfsync no  # 由操作系統決定寫入磁碟的時機

使用AOF機制 機製的缺點是隨着時間的流逝，AOF檔案會變得很大。但redis可以壓縮AOF檔案。

結合使用

redis允許我們同時使用兩種機制 機製，通常情況下我們會設定AOF機制 機製爲everysec 每秒寫入，則最壞僅會丟失一秒內的數據。

3 Redis高可用

爲了保證redis最大程度上能夠使用，redis提供了主從同步+Sentinel哨兵機制 機製。

Sentinel 哨兵

https://redis.io/topics/sentinel

redis提供的哨兵是用來看護redis範例進程的，可以自動進行故障轉移，其功能如下：

• Monitoring. Sentinel constantly checks if your master and slave instances are working as expected.
• Notification. Sentinel can notify the system administrator, another computer programs, via an API, that something is wrong with one of the monitored Redis instances.
• Automatic failover. If a master is not working as expected, Sentinel can start a failover process where a slave is promoted to master, the other additional slaves are reconfigured to use the new master, and the applications using the Redis server informed about the new address to use when connecting.
• Configuration provider. Sentinel acts as a source of authority for clients service discovery: clients connect to Sentinels in order to ask for the address of the current Redis master responsible for a given service. If a failover occurs, Sentinels will report the new address

在redis安裝後，會自帶sentinel哨兵程式，修改sentinel.conf組態檔

bind 127.0.0.1
port 26380
daemonize yes
logfile /var/log/redis-sentinel.log
sentinel monitor mymaster 127.0.0.1 6380 2
sentinel down-after-milliseconds mymaster 30000
sentinel parallel-syncs mymaster 1
sentinel failover-timeout mymaster 180000

• sentinel monitor mymaster 127.0.0.1 6380 2 說明
  • mymaster 爲sentinel監護的redis主從叢集起名
  • 127.0.0.1 6300 爲主從中任一臺機器地址
  • 2 表示有兩臺以的sentinel認爲某一臺redis宕機後，纔會進行自動故障轉移。

啓動方式：

redis-sentinel sentinel.conf

高可用方案注意事項

• 至少三個sentinel以上
• sentinel要分散執行在不同的機器上

Python用戶端使用

# redis 哨兵
REDIS_SENTINELS = [
    ('127.0.0.1', '26380'),
    ('127.0.0.1', '26381'),
    ('127.0.0.1', '26382'),
]
REDIS_SENTINEL_SERVICE_NAME = 'mymaster'

from redis.sentinel import Sentinel
_sentinel = Sentinel(REDIS_SENTINELS)
redis_master = _sentinel.master_for(REDIS_SENTINEL_SERVICE_NAME)
redis_slave = _sentinel.slave_for(REDIS_SENTINEL_SERVICE_NAME)

使用範例

# 讀數據，master讀不到去slave讀
try:
    real_code = redis_master.get(key)
except ConnectionError as e:
    real_code = redis_slave.get(key)

# 寫數據，只能在master裡寫
try:
    current_app.redis_master.delete(key)
except ConnectionError as e:
    logger.error(e)

4 Redis叢集

https://redis.io/topics/partitioning

Reids Cluster叢集方案，內部已經整合了sentinel機制 機製來做到高可用。

Python用戶端

# redis 叢集
REDIS_CLUSTER = [
    {'host': '127.0.0.1', 'port': '7000'},
    {'host': '127.0.0.1', 'port': '7001'},
    {'host': '127.0.0.1', 'port': '7002'},
]

from rediscluster import StrictRedisCluster
redis_cluster = StrictRedisCluster(startup_nodes=REDIS_CLUSTER)

# 可以將redis_cluster就當作普通的redis用戶端使用
redis_master.delete(key)

注意：

• redis cluster 不支援事務
• redis cluster 不支援多鍵操作，如mset

5 用途

• 快取
• 持久儲存
  • 數據庫的統計冗餘欄位 放到 redis中儲存

6 相關補充閱讀

• https://redis.io/documentation
• 《Redis實踐》 （Redis in action)