E-R模型
- 關系型數(shù)據(jù)庫建議在E-R模型基礎上缤剧。我們需要根據(jù)產品經(jīng)理的設計策劃馅袁，抽取出來模型與關系，制定出表的結構荒辕。項目開始的第一步
- 設計數(shù)據(jù)庫軟件
power designer汗销、db designer
1、E 表示entry抵窒，實體弛针。設計實體就像是定義一個類一樣，指定從哪些方面描述對象李皇，一個實體轉化為數(shù)據(jù)庫中的一個表削茁。
2、R 表示relationship疙赠，關系付材。關系描述兩個實體之間的對應規(guī)則，關系的類型包括一對一圃阳、一對多厌衔、多對多
3、關系也是一種數(shù)據(jù)捍岳，需要通過一個字段來存儲在表中
數(shù)據(jù)庫
1. 數(shù)據(jù)庫設計
-
概念：
為了建立冗余較小數(shù)據(jù)富寿、結構合理的數(shù)據(jù)庫睬隶，設計數(shù)據(jù)庫時必須遵循一定的規(guī)則。在關系數(shù)據(jù)庫中這種規(guī)則就是稱為范式页徐。滿足第三范式的數(shù)據(jù)庫設計苏潜，往往不是最好的設計。為了提高數(shù)據(jù)庫的運行效率变勇，常常需要降低范式標準：適當增加冗余恤左，達到以空間換時間。
的目的搀绣。
-
一范式
基本范式飞袋，數(shù)據(jù)庫表的所有的字段值都是不可分解的原子值（確保每列保持原子性）
-
二范式
需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關，而不能只與主鍵的莫一部分相關（主要針對聯(lián)合主鍵而言）链患。
也就是一個數(shù)據(jù)庫表中只能保存一種數(shù)據(jù)巧鸭，不可以把多種數(shù)據(jù)保存在同一數(shù)據(jù)庫表中。
-
三范式
需要確保數(shù)據(jù)庫表中的每一列數(shù)據(jù)都和主鍵直接相關麻捻，而不是間接相關纲仍。
2. 一對多、多對一
- 例子：員工與部門的關系
- 設計原則：首先不要管對象之間的關系贸毕，看某個對象都有什么基本的屬性郑叠。在多的一方添加外鍵描述數(shù)據(jù)之間的關系。在定義模型類的時候明棍，一的一方添加關系關聯(lián)鍵锻拘，不是實體的數(shù)據(jù)表中的字段，只用于查詢數(shù)據(jù)击蹲。
3. 多對多
例子：老師和學生關系
設計原則：首先不要管對象之間的關系，看某個對象都有什么基本的屬性婉宰。設計一個表來保存對象的基本數(shù)據(jù)歌豺。兩個表之間需要一個中間表來描述數(shù)據(jù)關系。
-
多對多對象的表：可以降低范式心包，增加數(shù)據(jù)的冗余类咧，少用觸發(fā)器，多用儲存過程蟹腾。
出現(xiàn)冗余的數(shù)據(jù)痕惋，但是查詢性能是好的，不需要多表查詢娃殖。
4. 一對一
- 例子：身份證管理系統(tǒng)
- 設計原則：首先不要管對象之間的關系值戳，看某個對象都有什么基本的屬性。設計一個表來保存對象的基本數(shù)據(jù)炉爆。一對一的對象堕虹，有一個主從關系卧晓，主是人，從是身份證赴捞。
自連接表
- 家族管理表系統(tǒng)
- 注意： 自連接逼裆，外鍵列不能加非空的約束
5. 面試：無限極分類的數(shù)據(jù)表設計
- 商品分類表
6. 數(shù)據(jù)庫設計提高運行效率
在數(shù)據(jù)庫物理設計時，降低范式赦政、增加冗余胜宇、少用觸發(fā)器，多用存儲過程恢着。
當計算復雜桐愉、而記錄條數(shù)巨大，復雜的計算要在數(shù)據(jù)庫外邊然评。處理完成以后仅财，最后才追加到表中。電信的計費系統(tǒng)設計碗淌。
-
發(fā)現(xiàn)表的記錄太多盏求，則對該表進行水平分割。
水平分割的做法：將該表的主鍵PK的某個值為界線亿眠，將該表的記錄水平分割為兩個表碎罚。
垂直分割的做法：若發(fā)現(xiàn)某個表的字段太多，例如超過八十個纳像，則垂直分割該表荆烈，將原來的一個表分解為兩個表。
7. 解決數(shù)據(jù)庫高并發(fā)訪問瓶頸問題
- 優(yōu)點：高可用性竟趾、自動失效切換憔购、分布式結構、高吞吐量岔帽、低延遲玫鸟、擴展性強
- 缺點：不支持外鍵、占用內存大犀勒、備份和恢復不方便屎飘、重啟數(shù)據(jù)節(jié)點load到內存時間長。
一贾费、緩存式的WEB應用程序架構
在Web層和db層之間加一層cache層钦购，主要目的：減少數(shù)據(jù)庫讀取負擔，提高數(shù)據(jù)讀取速度褂萧。cache存取的媒介是內存押桃，可以考慮采用分布式的cache層，這樣更容易破除內存容量的限制箱玷，同時增加了靈活性怨规。
-
在使用ORM模型類查詢的時候陌宿，查詢集QuerySet
從SQL的角度講，查詢集與select語句等價波丰，過濾器像where壳坪、limit、order by子句
-
惰性執(zhí)行：創(chuàng)建查詢集不會反問數(shù)據(jù)庫掰烟，直到調用數(shù)據(jù)時爽蝴，才會訪問數(shù)據(jù)庫。調用數(shù)據(jù)的情況：
迭代纫骑、序列化蝎亚、if語句的便利
緩存：使用同一個查詢集，第一次使用時會發(fā)生數(shù)據(jù)庫的查詢先馆，然后Django會把結果緩存下來发框，再次使用這個查詢集時會使用緩存的數(shù)據(jù)，減少了數(shù)據(jù)庫的查詢次數(shù)煤墙。
-
-
redis數(shù)據(jù)庫緩存分布式搭建
3.1 在配置中配置搭建的redis數(shù)據(jù)庫信息
# 配置緩存redis,(不同數(shù)據(jù)配置多個緩存庫)使用django-redis # 搭建多個緩存庫梅惯，存出不同的數(shù)據(jù)類型 CACHES = { "default": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://127.0.0.1:6379/0", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", } }, "session": { "BACKEND": "django_redis.cache.RedisCache", "LOCATION": "redis://127.0.0.1:6379/1", "OPTIONS": { "CLIENT_CLASS": "django_redis.client.DefaultClient", } }, }3.2 建立連接信息（基本鏈接redis數(shù)據(jù)庫，操作數(shù)據(jù)庫）
from django_redis import get_redis_connection # 獲取鏈接數(shù)據(jù)庫的游標對象 conn = get_redis_connection('verify_codes') # 根據(jù)游標對象仿野，操作數(shù)據(jù)庫铣减，CURD3.3 使用緩存—-Django框架
# 使用擴展 pip install drf-extensions # 1. 直接添加裝飾器 from rest_framework_extensions.cache.decorators import cache_response class AreaView(ListCreateAPIView): @cache_response(timeout=60 * 60, cache='default', key_func='calculate_cache_key') def get(self, request, *args, **kwargs): """對請求數(shù)據(jù)庫的查詢繼承重寫,裝飾器,緩存數(shù)據(jù)庫""" return super(AreaView, self).get(request, *args, **kwargs) def calculate_cache_key(self, view_instance, view_method, request, args, kwargs): id = self.kwargs['pk'] return '.'.join([ str(len(args)), id ]) ''' cache_response裝飾器接收兩個參數(shù)： timeout：緩存時間 cache： 緩存使用的是Django配置的redis指定數(shù)據(jù)庫名。（即CACHES配置中的鍵的名稱） ''' ### 緩存數(shù)據(jù)保存位置與有效期的設置 """想把緩存數(shù)據(jù)保存在redis中脚作，且設置有效期葫哗，可以通過配置文件定義實現(xiàn)""" # DRF擴展 REST_FRAMEWORK_EXTENSIONS = { # 緩存時間 'DEFAULT_CACHE_RESPONSE_TIMEOUT': 60 * 60, # 緩存存儲 'DEFAULT_USE_CACHE': 'default', } -
MySQL數(shù)據(jù)庫配置鏈接---Django框架使用
4.1 使用MySQL數(shù)據(jù)庫首先安裝驅動程序
pip install PyMySQL
4.2 在Django的工程目錄下的____init____.py文件中執(zhí)行驅動
import pymysql pymysql.install_as_MySQLdb() # 作用 # 讓Django的ORM能以mysqldb的方式來調用PyMySQL. 在程序運行以后就開始執(zhí)行此驅動4.3 修改DATABASE配置信息
DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'HOST': '127.0.0.1', # 數(shù)據(jù)庫主機 'PORT': 3306, # 數(shù)據(jù)庫端口 'USER': 'root', # 數(shù)據(jù)庫用戶名 'PASSWORD': 'mysql', # 數(shù)據(jù)庫用戶密碼 'NAME': 'django_demo' # 數(shù)據(jù)庫名字 } }
二、實現(xiàn)MySQL數(shù)據(jù)庫異步查詢實現(xiàn)——主要使用在Spider
- 使用wisted是用Python實現(xiàn)的基于事件驅動的網(wǎng)絡引擎框架球涛。一種異步框架
- Twisted 提供了 twisted.enterprise.adbapi, 遵循DB-API 2.0協(xié)議的一個異步封裝劣针。
- adbapi 在單獨的線程里面進行阻塞數(shù)據(jù)庫操作, 當操作完成的時候仍然通過這個線程來進行回調。同時亿扁，原始線程能繼續(xù)進行正常的工作酿秸，服務其他請求。
import pymysql
import pymysql.cursors
from twisted.enterprise import adbapi
from twisted.internet import reactor
class MysqlTwistedPipeline(object):
def __init__(self, dbpool):
self.dbpool = dbpool
@classmethod
def from_settings(cls, settings):
# 需要在setting中設置數(shù)據(jù)庫配置參數(shù)
dbparms = dict(
host=settings['MYSQL_HOST'],
db=settings['MYSQL_DBNAME'],
user=settings['MYSQL_USER'],
passwd=settings['MYSQL_PASSWORD'],
charset='utf8',
cursorclass=pymysql.cursors.DictCursor,
use_unicode=True,
)
# 連接ConnectionPool（使用MySQLdb連接魏烫，或者pymysql）
dbpool = adbapi.ConnectionPool("pymysql", **dbparms) # **讓參數(shù)變成可變化參數(shù)
return cls(dbpool) # 返回實例化對象
def process_item(self, item, spider):
# 使用twisted將MySQL插入變成異步執(zhí)行
query = self.dbpool.runInteraction(self.do_insert, item)
# 添加異常處理
query.addCallback(self.handle_error)
def handle_error(self, failure):
# 處理異步插入時的異常
print(failure)
def do_insert(self, cursor, item):
# 執(zhí)行具體的插入
insert_sql = """
insert into jobbole_artitle(name, base_url, date, comment)
VALUES (%s, %s, %s, %s)
"""
cursor.execute(insert_sql, (item['name'], item['base_url'], item['date'], item['coment'],))
三、MySQL主從讀寫分離
- cache層（如Memcached）只能緩解數(shù)據(jù)庫的讀取壓力肝箱。
- 使用主從復制技術（master-slave模式）來達到讀寫分離哄褒，以提高讀寫性能和讀庫的可擴展性。復制是異步進行的煌张，所以從服務器不需要一直連接主服務器呐赡。
1. 主從同步的機制
MySQL服務器之間的主從同步基于二進制日志機制，主服務器使用二進制日志來記錄數(shù)據(jù)庫的變動情況骏融。從服務器通過讀取和執(zhí)行該日志文件來保持和主服務器的數(shù)據(jù)一致链嘀。
在使用二進制日志時萌狂，主服務器的所有操作都會被記錄下來，然后從服務器會接收到該日志的一個副本怀泊。從服務器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)）茫藏，默認會執(zhí)行日志中的所有語句。
主服務器和每一個從服務器都必須配置一個唯一的ID號霹琼，
2.配置主從同步的基本步驟
- 在主服務器上务傲，必須開啟二進制日志機制和配置一個獨立的ID
- 在每一個從服務器上，配置一個唯一的ID枣申，創(chuàng)建一個用來專門復制主服務器數(shù)據(jù)的賬號
- 在開始復制進程前售葡，在主服務器上記錄二進制文件的位置信息
- 如果在開始復制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)忠藤，就必須先創(chuàng)建一個數(shù)據(jù)快照（可以使用mysqldump導出數(shù)據(jù)庫挟伙，或者直接復制數(shù)據(jù)文件）
- 配置從服務器要連接的主服務器的IP地址和登陸授權，二進制日志文件名和位置
3. 詳細配置主從同步的方法
-
備份主服務器原有數(shù)據(jù)到從服務器
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql # --lock-all-tables ：執(zhí)行操作時鎖住所有表模孩，防止操作時有數(shù)據(jù)修改 # ~/master_db.sql :導出的備份數(shù)據(jù)（sql文件）位置尖阔，可自己指定 -
在從服務器上進行數(shù)據(jù)還原
1. 將備份好的數(shù)據(jù)庫包master_db.sql復制到從服務器 2. 執(zhí)行恢復數(shù)據(jù)包的命令 mysql -uroot -pmysql < master_db.sql -
配置主服務器master（編輯設置mysqld的配置文件，設置log_bin和server_id)
sudo vim /etc/mysql/mysql.conf.d/mysql.cnf # 在配置文件中 server—id = 1 log_bin = /var/log/mysql/mysql -bin.log -
重啟Mysql服務
sudo service mysql restart -
登入主服務器的mysql瓜贾，創(chuàng)建用于從服務器的同步數(shù)據(jù)的賬號（主創(chuàng)建賬號）
mysql -uroot -pmysql GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave'; FLUSH PRIVILEGES; # 命令直接執(zhí)行 -
獲取主服務器的二進制日志信息
SHOW MASTER STATUS; # 來獲取 主服務器的 日志File名诺祸、日志的位置Position -
配置從服務器salve（找到從服務器的發(fā)MySQL的配置文件）
# 1. 保存退出 server—id = 2 # 2. 重新啟動 sudo service mysql restart -
進入從服務器的mysql，設置連接到master主服務器
change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590; # master_host:主服務器的IP地址 # master_log_file：前面查詢到主服務器的日志文件名 # master_log_pos：前面查詢到主服務器日志文件的位置 -
開啟從服務器同步祭芦，查看同步狀態(tài)
start slave # Qusery OK, show slave statues \G; # \G:數(shù)據(jù)庫的源文件位置 ''' # 表示同步已經(jīng)執(zhí)行運行 Slave_IO_Runnning:YES Slave_SQL_Running:YES '''
四筷笨、分表分庫
-
在cache層的高速緩存，MySQL的主從復制龟劲，讀寫分離的基礎上胃夏，數(shù)量的增加，高并發(fā)會出現(xiàn)嚴重鎖的問題昌跌。
大量的高并發(fā)MySQL應用開始使用InnoDB引擎代替MyISAM仰禀。
采用Master-Slave復制模式的MySQL架構，只能對數(shù)據(jù)庫的讀進行擴展蚕愤，對數(shù)據(jù)的寫的操作集中在Master上
1. 分表【水平拆分】
- 對于訪問極為頻繁且數(shù)據(jù)量巨大的單表來說答恶，首先要做的是減少單表的記錄條數(shù)，以便減少數(shù)據(jù)查詢所需的時間萍诱，提高數(shù)據(jù)庫的吞吐悬嗓。
- 在分表之前，首先需要選擇適當?shù)?strong>分表策略裕坊，使得數(shù)據(jù)能夠較為均衡地分布到多張表中包竹，并且不影響正常的查詢。
2. 分庫【垂直拆分】
- 分表能夠解決單表數(shù)據(jù)量過大帶來的查詢效率下降的問題，但是卻無法給數(shù)據(jù)庫的并發(fā)處理能力帶來質的提升周瞎。
- 面對高并發(fā)的讀寫訪問苗缩，當數(shù)據(jù)庫master服務器無法承載寫操作壓力時，不管如何擴展Slave服務器都是沒有意義的声诸，對數(shù)據(jù)庫進行拆分酱讶，從而提高數(shù)據(jù)庫寫入能力
3.分庫分表策略
1、中間變量=user_id % ( 庫數(shù)量 * 每個庫的表數(shù)量 )
2双絮、庫=取整（中間變量 / 每個庫的表數(shù)量）
3浴麻、表=中間變量 % 每個庫的表數(shù)量
8. MySQL數(shù)據(jù)庫存儲原理
- 存儲過程是一個可編程的函數(shù)，它在數(shù)據(jù)庫中創(chuàng)建并保存囤攀。他可以有SQL語句和一些特殊的控制結構組成软免。
- 在不同的程序和平臺執(zhí)行相同的函數(shù)，封裝特定的功能時焚挠，存儲過程是相同的膏萧。可以看作對編程中面向對象的方法的模擬蝌衔。
9. 數(shù)據(jù)庫的優(yōu)化
1榛泛、對語句的優(yōu)化
- 減少反問次數(shù)、較少表的訪問行數(shù)噩斟。
- 分開操作處理數(shù)據(jù)庫操作曹锨。
- 查詢不要使用通配符、模糊查詢
- 在可能的情況下盡量限制盡量結果集行數(shù)
2剃允、 避免使用不兼容的數(shù)據(jù)類型
-
數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進行的優(yōu)化操作（將整型轉化成為錢幣型 ）
SELECT name FROM employee WHERE salary > 60000
這里的數(shù)據(jù)60000為整型沛简，轉換為錢幣型，不要在查詢的時候強制轉換
3斥废、語句優(yōu)化
- 避免在WHERE語句對字段的函數(shù)式表達椒楣、表達式操作。
- 避免使用 !=牡肉、<> 捧灰、IS NULL 、IN统锤、NOT IN等毛俏。盡量使用數(shù)字型字段。
- 使用視圖加速查詢饲窿、能使用Between 的就不要使用 IN拧抖。能使用DIstinct 就不使用Group by
- 部分使用索引、不寫沒有意義的查詢
