MySQL数据库设计、优化

2018-08-21  本文已影响0人  梦醒家先生

E-R模型

1、E 表示entry，实体。设计实体就像是定义一个类一样，指定从哪些方面描述对象，一个实体转化为数据库中的一个表。

2、R 表示relationship，关系。关系描述两个实体之间的对应规则，关系的类型包括一对一、一对多、多对多

3、关系也是一种数据，需要通过一个字段来存储在表中

数据库

1. 数据库设计

2. 一对多、多对一

3. 多对多

4. 一对一

自连接表

5. 面试：无限极分类的数据表设计

6. 数据库设计提高运行效率

  1. 在数据库物理设计时，降低范式、增加冗余、少用触发器，多用存储过程。

  2. 当计算复杂、而记录条数巨大，复杂的计算要在数据库外边。处理完成以后，最后才追加到表中。电信的计费系统设计。

  3. 发现表的记录太多，则对该表进行水平分割。

    水平分割的做法：将该表的主键PK的某个值为界线，将该表的记录水平分割为两个表。

    垂直分割的做法：若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。

7. 解决数据库高并发访问瓶颈问题

一、缓存式的WEB应用程序架构

  1. 在Web层和db层之间加一层cache层，主要目的：减少数据库读取负担，提高数据读取速度。cache存取的媒介是内存，可以考虑采用分布式的cache层，这样更容易破除内存容量的限制，同时增加了灵活性。

  2. 在使用ORM模型类查询的时候，查询集QuerySet

    从SQL的角度讲，查询集与select语句等价，过滤器像where、limit、order by子句

    • 惰性执行：创建查询集不会反问数据库，直到调用数据时，才会访问数据库。调用数据的情况：

      迭代、序列化、if语句的便利

    • 缓存：使用同一个查询集，第一次使用时会发生数据库的查询，然后Django会把结果缓存下来，再次使用这个查询集时会使用缓存的数据，减少了数据库的查询次数。

  3. redis数据库缓存分布式搭建

    3.1 在配置中配置搭建的redis数据库信息

    # 配置缓存redis,(不同数据配置多个缓存库)使用django-redis
# 搭建多个缓存库，存出不同的数据类型
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数据库，操作数据库）

    from django_redis import get_redis_connection

# 获取链接数据库的游标对象
conn = get_redis_connection('verify_codes')
# 根据游标对象，操作数据库，CURD

    3.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):
        """对请求数据库的查询继承重写,装饰器,缓存数据库"""
        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装饰器接收两个参数：
timeout：缓存时间
cache： 缓存使用的是Django配置的redis指定数据库名。（即CACHES配置中的键的名称）
'''
### 缓存数据保存位置与有效期的设置
"""想把缓存数据保存在redis中，且设置有效期，可以通过配置文件定义实现"""
# DRF扩展
REST_FRAMEWORK_EXTENSIONS = {
    # 缓存时间
    'DEFAULT_CACHE_RESPONSE_TIMEOUT': 60 * 60,
    # 缓存存储
    'DEFAULT_USE_CACHE': 'default',
}

  4. MySQL数据库配置链接---Django框架使用

    4.1 使用MySQL数据库首先安装驱动程序

    pip install PyMySQL

    4.2 在Django的工程目录下的____init____.py文件中执行驱动

    import pymysql
pymysql.install_as_MySQLdb()
# 作用
# 让Django的ORM能以mysqldb的方式来调用PyMySQL. 在程序运行以后就开始执行此驱动

    4.3 修改DATABASE配置信息

    DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'HOST': '127.0.0.1',  # 数据库主机
        'PORT': 3306,  # 数据库端口
        'USER': 'root',  # 数据库用户名
        'PASSWORD': 'mysql',  # 数据库用户密码
        'NAME': 'django_demo'  # 数据库名字
    }
}
二、实现MySQL数据库异步查询实现——主要使用在Spider
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中设置数据库配置参数
        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)  # **让参数变成可变化参数
        return cls(dbpool)   # 返回实例化对象
 
    def process_item(self, item, spider):
        # 使用twisted将MySQL插入变成异步执行
        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):
        # 执行具体的插入
        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主从读写分离
1. 主从同步的机制

MySQL服务器之间的主从同步基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况。从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。

主服务器和每一个从服务器都必须配置一个唯一的ID号，

2.配置主从同步的基本步骤
  1. 在主服务器上，必须开启二进制日志机制和配置一个独立的ID
  2. 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
  3. 在开始复制进程前，在主服务器上记录二进制文件的位置信息
  4. 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
  5. 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置
3. 详细配置主从同步的方法

  1. 备份主服务器原有数据到从服务器

    mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
# --lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
# ~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定

  2. 在从服务器上进行数据还原

    1. 将备份好的数据库包master_db.sql复制到从服务器
2. 执行恢复数据包的命令
mysql -uroot -pmysql < master_db.sql

  3. 配置主服务器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

  4. 重启Mysql服务

    sudo service mysql restart

  5. 登入主服务器的mysql，创建用于从服务器的同步数据的账号（主创建账号）

    mysql -uroot -pmysql
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
FLUSH PRIVILEGES;
# 命令直接执行

  6. 获取主服务器的二进制日志信息

    SHOW MASTER STATUS;
# 来获取 主服务器的 日志File名、日志的位置Position

  7. 配置从服务器salve（找到从服务器的发MySQL的配置文件）

    # 1. 保存退出
server—id    = 2 
# 2. 重新启动
sudo service mysql restart

  8. 进入从服务器的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：前面查询到主服务器日志文件的位置

  9. 开启从服务器同步，查看同步状态

    start slave # Qusery OK,
show slave statues \G; # \G:数据库的源文件位置
'''
# 表示同步已经执行运行
Slave_IO_Runnning:YES
Slave_SQL_Running:YES
'''
四、分表分库
1. 分表【水平拆分】
2. 分库【垂直拆分】
3.分库分表策略

1、中间变量=user_id % ( 库数量 * 每个库的表数量 )

2、库=取整（中间变量 / 每个库的表数量）

3、表=中间变量 % 每个库的表数量

8. MySQL数据库存储原理

9. 数据库的优化

1、对语句的优化
2、 避免使用不兼容的数据类型
3、语句优化
上一篇 下一篇

猜你喜欢

热点阅读