day09 数据库

1.必备技能

1.1 数据库设计

在进行数据库的表结构设计时，最重要的是要理清楚表的关系：

单表
一对多（一对一）
多对多

例如，让你开发一个薪资管理平台。

部门表
用户表
级别表
薪资级别记录

接下里你就要根据关系编写相应的SQL语句，例如：

create table ...

但是，实际在项目开发时，设计表结构会使用一些工具，来简化设计过程，例如：

工具：PowerDesigner、Navicat等，示例如下：

ORM框架，示例如下：

import time
  import datetime

  from django.db import models
  from django.contrib.contenttypes.models import ContentType
  from django.contrib.contenttypes.fields import GenericForeignKey, GenericRelation


  class Role(models.Model):
      """ 角色，管理员可以做权限管理并分配角色"""
      title = models.CharField(verbose_name="名称", max_length=32)


  class RolePermission(models.Model):
      """ 角色&权限关系表 """
      role = models.ForeignKey(verbose_name="角色", to="Role", on_delete=models.CASCADE)
      code = models.CharField(verbose_name="权限名称", max_length=32)


  class AdminUser(models.Model):
      """ 平台用户管理 """
      mobile_phone = models.CharField(verbose_name="手机号", max_length=32, db_index=True)
      password = models.CharField(verbose_name="密码", max_length=64)
      real_name = models.CharField(verbose_name="真实姓名", max_length=32)
      role = models.ForeignKey(verbose_name="角色", to="Role", on_delete=models.SET_NULL, null=True, blank=True)


  class HandlerLog(models.Model):
      """ 操作日志，基于自定义信号去实现记录日志。"""
      admin = models.ForeignKey(verbose_name="用户", to='AdminUser', on_delete=models.CASCADE)
      content = models.CharField(verbose_name="日志内容", max_length=255)
      create_datetime = models.DateTimeField(verbose_name="操作时间", auto_now_add=True)

手动写SQL语句

CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NULL,
      `password` varchar(255) NULL,
      `depart_id` int(11) NULL,
      PRIMARY KEY (`id`) 
  );
  CREATE TABLE `depart` (
      `id` int(11) NOT NULL,
      `title` varchar(255) NULL,
      PRIMARY KEY (`id`) 
  );

  -- 其中关键字由`包裹，可以避免和内置关键重复。

案例：废旧物资平台

根据用户认证模块来设计相关表结构。

import time
import datetime

from django.db import models
from django.contrib.contenttypes.models import ContentType
from django.contrib.contenttypes.fields import GenericForeignKey, GenericRelation


class UserInfo(models.Model):
    user_type_choice = (
        (1, "需求方"),  # 需求方
        (2, "供应方"),  # 供应方
        (3, "司机"),  # 司机
    )
    user_type = models.IntegerField(verbose_name="用户类型", choices=user_type_choice)
    mobile_phone = models.CharField(verbose_name="手机号", max_length=32, db_index=True)
    password = models.CharField(verbose_name="密码", max_length=64)

    balance = models.DecimalField(verbose_name="账户余额", max_digits=10, decimal_places=2, default=0)

    auth_status_choice = (
        (0, "未认证"),
        (1, "已认证"),
    )
    auth_status = models.SmallIntegerField(verbose_name="是否已认证", choices=auth_status_choice, default=0)



####################
# 审核基类          #
####################

class BaseAudit(models.Model):
    audit_status_choices = (
        (0, "审核失败"),
        (1, "待审核"),
        (2, "审核中"),
        (3, "审核成功"),
    )
    audit_status = models.SmallIntegerField(verbose_name="状态", choices=audit_status_choices, default=1)
    audit_memo = models.TextField(verbose_name="审核备注", null=True, blank=True)
    apply_datetime = models.DateTimeField(verbose_name="申请时间")
    audit_datetime = models.DateTimeField(verbose_name="审批时间", null=True, blank=True)

    class Meta:
        abstract = True


####################
# 认证相关          #
####################


class AuthenticationPersonal(BaseAudit):
    """ 个人认证（用户和商户均允许个人认证），之后绑定个人银行账户信息 """

    user = models.ForeignKey(verbose_name="用户", to="UserInfo", on_delete=models.CASCADE)
    name = models.CharField(verbose_name="姓名", max_length=16)
    identity_card_no = models.CharField(verbose_name="身份证号码", max_length=19)
    identity_card = models.ImageField(verbose_name="身份证正面照")
    identity_card_reverse = models.ImageField(verbose_name="身份证反面照")
    email = models.EmailField(verbose_name="邮箱", null=True, blank=True)


class AuthenticationBusiness(BaseAudit):
    """ 企业认证（用户和商户均允许企业认证），之后绑定企业银行账户信息"""
    user = models.ForeignKey(verbose_name="用户", to="UserInfo", on_delete=models.CASCADE)
    title = models.CharField(verbose_name="企业名称", max_length=128)
    business_license = models.ImageField(verbose_name="营业执照")
    credit_code = models.CharField(verbose_name="社会统一信用代码", max_length=18)
    email = models.EmailField(verbose_name="邮箱", null=True, blank=True)



class AuthenticationDriver(BaseAudit):
    """ 司机认证 """
    user = models.ForeignKey(verbose_name="用户", to="UserInfo", on_delete=models.CASCADE)
    name = models.CharField(verbose_name="姓名", max_length=16)
    identity_card_no = models.CharField(verbose_name="身份证号码", max_length=19)
    identity_card = models.ImageField(verbose_name="身份证正面照")
    identity_card_reverse = models.ImageField(verbose_name="身份证反面照")
    licence = models.ImageField(verbose_name="驾驶证正面照")
    licence_second = models.ImageField(verbose_name="驾驶证副页正面照")
    licence_second_reverse = models.ImageField(verbose_name="驾驶证副页反面照")
    certificate = models.ImageField(verbose_name="从业资格证", null=True, blank=True)


class Trucks(BaseAudit):
    """ 车辆信息 """
    user = models.ForeignKey(verbose_name="用户", to="UserInfo", on_delete=models.CASCADE)
    image = models.ImageField(verbose_name="车辆照片")
    plate_no = models.CharField(verbose_name="车牌号", max_length=8)
    vehicle_type = models.CharField(verbose_name="车辆类型", max_length=32)
    vehicle_ident_no = models.CharField(verbose_name="车架号", max_length=17)
    band = models.CharField(verbose_name="汽车品牌", max_length=32)

    license = models.ImageField(verbose_name="行驶证正面照")
    license_second = models.ImageField(verbose_name="行驶证副页正面照")
    license_second_reverse = models.ImageField(verbose_name="行驶证副页反面照")
    trailer_license = models.ImageField(verbose_name="挂车行驶证主页", null=True, blank=True)

    drive_license_no = models.CharField(verbose_name="道路运输许可证号", null=True, blank=True)
    drive_license = models.ImageField(verbose_name="道路运输许可证照", null=True, blank=True)

1.2 常见SQL语句

常见的增删改查。

1.3 SQL注入

在设计好项目的表结构后，就应该开始进行编写程序了，但在编写程序时一定要注意SQL注入的问题。

假如，你开发了一个用户认证的系统，应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")



conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='userdb')
cursor = conn.cursor()

# 基于字符串格式化来 拼接SQL语句
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

如果用户在输入user时，输入了： ' or 1=1 -- ，这样即使用户输入的密码不存在，也会可以通过验证。

为什么呢？

因为在SQL拼接时，拼接后的结果是：

select * from users where name='' or 1=1 -- ' and password='123'

注意：在MySQL中 -- 表示注释。

那么，在Python开发中如何来避免SQL注入呢？

切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')

cursor = conn.cursor()

# cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
cursor.execute("select * from users where name=%(name)s and password=%(pwd)s", {"name": user, 'pwd': pwd})

result = cursor.fetchone()
print(result)

cursor.close()
conn.close()

1.4 事务

要成功都成功；要失败都失败，经典案例：

转账，A账户扣钱；B账户涨钱；

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

conn.begin()

try:
    cursor.execute("update tran set age=1 where id=1")
    int('asdf')
    cursor.execute("update tran set age=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

注意：在MySQL中想要使用事务，需要将表设置为innodb引擎。

1.5 锁

MySQL中自带锁，可以帮助我们实现开发过程中遇到的同时处理数据的情况。例如：总共100件商品，每次购买一件需要让商品个数减1 。

关于数据库中的锁，从锁的范围来讲有：

表级锁
行级锁（建议）

MYISAM不支持行锁，而InnoDB支持行锁和表锁。

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

下面基于InnoDB引擎来体验表锁和行锁的不同。

start transaction;
    SQL...
commit;

1.5.1 排它锁

排它锁（ for update），加锁之后，其他事物不可以读写。

1. InnoDB引擎默认的 update, delete, insert都会自动给涉及到的数据加上排他锁（语句结束自动释放锁），select语句默认不会加任何锁类型。

2. select想要添加排它锁，在查询语句的在尾部 for update即可，例如：
    select * from 表 where id=1 for update;   -- 加锁
    select * from 表 where id=1;              -- 不加锁
    注意：select加锁要配合事务。
3. 特点：
    当有人申请锁之后，其他再来申请就会阻塞。

注意：insert在表锁的情况才会上锁。

例如：总共100件商品，每次购买一件需要让商品个数减1 。

A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100

此时 A、B 同时下单，那么他们同时执行SQL：
    update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。


但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1
此时 A、B 同时下单，那么他们同时执行SQL：
    update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？

这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行：
    start transaction;
    select count from goods where id=1 for update;
    -- 获取个数进行判断
    if 个数>0:
        update goods set count=count-1 where id=3;
    else:
        -- 已售罄
    commit;

基于Python代码示例：

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    conn.begin()

    cursor.execute("select age from tran where id=2 for update")
    result = cursor.fetchone()
    current_age = result['age']
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

1.5.2 共享锁

共享锁（ lock in share mode），可以读，但不允许写。

加锁之后，后续其他事物可以可以进行读，但不允许写（update、delete、insert），因为写的默认也会加锁。

应用场景：假如存在两张有关系的表：A和B，使用普通的SELECT语句来查询表 B 并验证数据是否存在，存在后再在B中插入数据，这么写有就有问题：

select * from A where id=1
如果存在，在B中插入数据，其中包括 A 的ID（外键）。
insert into B(...)

如果有其他人在 select 和 insert 之间的某个时间点删除了 A表中的数据，插入操作就会失败（外键约束）。

此时，就可以用共享锁，在 select 和 insert 之间其他人可以读取数据，但不能删除、修改当前数据。

begin;
select * from A where id=1 lock in share mode;
如果存在，在B中插入数据，其中包括 A 的ID（外键）。
insert into B(...) 
commit;

1.6 数据库连接池

在操作数据库时需要使用数据库连接池。

pip3.9 install dbutils

from dbutils.pooled_db import PooledDB
import pymysql

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def run():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor()
    cursor.execute('select * from tran')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()


if __name__ == '__main__':
    run()

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor()
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()


def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

在 dbutils 模块中还支持单独为每个线程都创建一个连接，线程即使调用了close方法，也不会关闭，只是把连接重新放到连接池，供自己线程再次使用。当线程终止时，连接自动关闭。

import threading
import pymysql
from dbutils.pooled_db import PooledDB
from dbutils.persistent_db import PersistentDB

MYSQL_DB_POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    closeable=False,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 将连接交换给连接池
    conn.close()


def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

1.7 数据库帮助类

在以后项目中操作数据库一般分为三种情况：

写脚本，直接pymysql连接就行。
写项目，用原生MySQL，一般都会封装了一个数据库帮助类。
写项目，用ORM，直接操作即可。

import threading
import pymysql
from dbutils.pooled_db import PooledDB


class Connect(object):
    def __init__(self):
        self.conn = conn = db.pool.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()


class DBHelper(object):

    def __init__(self):
        # TODO 此处配置，可以去配置文件中读取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
            mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
            blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )
        self.pool.connection()

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.clse()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(cursor, conn)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = conn.fetch_one()

        self.close_conn_cursor(cursor, conn)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = conn.fetch_all()

        self.close_conn_cursor(cursor, conn)

        return result


db = DBHelper()

if __name__ == '__main__':
    with Connect() as conn_object:
        print(conn_object.conn)
        print(conn_object.cursor)

1.8 ORM框架

SQLAchemy，https://www.cnblogs.com/wupeiqi/articles/8259356.html
Django 框架自带ORM框架

2. 优化

2.1 表结构设计

在表结构设计时对于有几点需要注意：

创建字段时，尽量使用固定长度。例如：用 char代替 varchar。
字段顺序，固定长度的字段在前面。

有些数据可以存在内存中，例如：用户类型。

例如：在Python全局变量中编写
    USER_TYPE = (
        (1, "超级管理员"),
        (2, "管理员"),
        (3, "普通用户"),
    )
在数据库中存储用户类型用tinyint。

注意：一般适用于固定几个值的情况。

允许存在数据冗余（视公司情况而定）

- 用户表 & 部门表
- 直接用户表，将部门名称写在数据库中。

避免查询时连表导致效率低，允许浪费一些硬盘来换取执行速度。

2.2 命中索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

create table big(
    id int not null auto_increment primary key,
    name varchar(32),email varchar(64),
    password varchar(64),
    age int
)engine=innodb default charset=utf8;

import threading
import pymysql
import random
import uuid
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=10,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=3,  # 链接池中最多闲置的链接，0和None不限制
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task(index):
    # 去连接池获取一个连接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor()
    for i in range(300000):
        cursor.execute("insert into big(name,email,password,age) values(%s,%s,%s,%s)",
                       ['wu-{}-{}'.format(index, i), 'w-{}-{}@qq.com'.format(index, i), str(uuid.uuid4()) , random.randint(1, 15)])
        conn.commit()
    cursor.close()
    conn.close()


def run():
    for i in range(10):
        t = threading.Thread(target=task, args=(i + 10,))
        t.start()

if __name__ == '__main__':
    run()

create index big_index_name on big(name);
drop index big_index_name on big;

create unique index big_unique_email  on big(email);

create index ix_name_pwd on big(name,password);

如下情况即使有索引也无法命中索引：

- like '%xx'
    select * from big where name like "%u-12-19999";    -- 未命中
    select * from big where name like "wu-12-1999%";    -- 命中
- 使用函数
    select * from big where reverse(name) = "wupeiqi";  -- 未命中
    select * from big where name = reverse("wupeiqi");  -- 命中
- or，当or条件中有未建立索引的列才失效。
    select * from big where name = "wupeiqi" or password="xx"; -- 未命中
    select * from big where id = 10 or password="xx";          -- 未命中
    特别的：
        select * from big where id = 10 or password="xx" and name="xx"; -- 命中

- 类型不一致 
    select * from big where name = 123; -- 未命中

- 不等于
    select * from big where name != "武沛齐"; -- 未命中
    特别的主键：
        select count(1) from big where id !=1;  -- 命中
- 排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。
    select * from big order by name asc;     -- 未命中
    select * from big order by name desc;    -- 未命中

    特别的主键：
        select * from big order by id desc;  -- 命中
- 组合索引最左前缀，如果组合索引为：(name,password)
    name and password       -- 命中
    name                    -- 命中
    password                -- 未命中
    name or password        -- 未命中

在项目初步开发阶段，还没有很多数据，一般有两种方式来保证你的SQL效率是没问题的：

执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

  mysql> explain select * from tb2;
  +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  |  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  1 row in set (0.00 sec)




  id，查询顺序标识
  如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
  +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
  |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
  +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  特别的：如果使用union连接气值可能为null


  select_type，查询类型
      SIMPLE          简单查询
      PRIMARY         最外层查询
      SUBQUERY        映射为子查询
      DERIVED         子查询
      UNION           联合
      UNION RESULT    使用联合的结果
      ...


  table，正在访问的表名


  type，查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
      ALL，全表扫描，对于数据表从头到尾找一遍
          select * from tb1;
          特别的：如果有limit限制，则找到之后就不在继续向下扫描
          select * from tb1 where email = 'seven@live.com'
          select * from tb1 where email = 'seven@live.com' limit 1;
          虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

      INDEX，全索引扫描，对索引从头到尾找一遍
        select nid from tb1;

      RANGE，对索引列进行范围查找
          select *  from tb1 where name < 'alex';
          PS:
              between and
              in
              >   >=  <   <=  操作
              注意：!= 和 > 符号


      INDEX_MERGE，合并索引，使用多个单列索引搜索
        select *  from tb1 where name = 'alex' or nid in (11,22,33);

      REF，根据索引查找一个或多个值
        select *  from tb1 where name = 'seven';

      EQ_REF，连接时使用primary key 或 unique类型
        select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

      CONST，常量
          表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
          select nid from tb1 where nid = 2 ;

      SYSTEM，系统
          表仅有一行(=系统表)。这是const联接类型的一个特例。
          select * from (select nid from tb1 where nid = 1) as A;


  possible_keys，可能使用的索引

  key，真实使用的

  key_len，MySQL中使用索引字节长度

  rows，mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

  extra
      该列包含MySQL解决查询的详细信息
      “Using index”
      此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
      “Using where”
      这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
      “Using temporary”
      这意味着mysql在对查询结果排序时会使用一个临时表。
      “Using filesort”
      这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
      “Range checked for each record(index map: N)”
      这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

慢日志，MySQL帮你记录所有执行慢的SQL记录。可以在数据库的配置文件中修改如下：

[mysqld]
  port = 3306
  basedir=/usr/local/mysql
  datadir=/usr/local/mysql/data
  sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH"

  # 是否开启慢日志记录 ON 或 OFF
  slow_query_log = ON
  # 时间限制，超过此时间，则记录
  long_query_time = 0.5
  # 日志文件
  slow_query_log_file = /var/log/mysql_slow.log
  # 未使用索引的搜索是否记录
  log_queries_not_using_indexes = ON


  这样一来，MySQL就可以自动将执行慢 或 未命中 索引的SQL全都记录下来。

2.3 查询

查询时候也得注意：

避免使用 select *
使用 count(1) 或 count(列) 代替 count(*)
连表时字段类型要一致

查询特定条数数据时，尽量使用limit ，例如：

select * from big where password = "362def1c-2b5f-4368-b01f-1ff086feab21";          -- 全表扫描
select * from big where password = "362def1c-2b5f-4368-b01f-1ff086feab21" limit 1;  -- 找到第一个就不再继续找

一个经典的分页问题

select * from big limit 10 offset 0;
select * from big limit 10 offset 100;
select * from big limit 10 offset 10000;
select * from big limit 10 offset 1000000;
... 越来越慢

所以，在很多网站只支持让你进行上一页和下一页（或下拉刷新），背后的SQL语句：

上一页（访问当前页时，要记录ID最大值和最小值）

select * from big where id > 最大值 order by id asc limit 10;

select * from ( select * from big where id > 最大值 order by id asc limit 10 ) as T  order by T.id desc;

select * from big where id < 最小值 order by id desc limit 10;

也有网站显示这样页码：

首页

select * from big order by id desc limit 10;

上一页（访问当前页时，要记录ID最大值和最小值）

select * from big where id > 最大值 order by id asc limit 10;

select * from ( select * from big where id > 最大值 order by id asc limit 10 ) as T  order by T.id desc;

select * from big where id < 最小值 order by id desc limit 10;

尾页

select * from (select * from big order by id asc limit 10) as T order by T.id desc;

也有更加复杂一点的页面：

这样就需要根据 当前页 & 目标页面 & 最大ID & 最小ID 共同来计算。

后面的页码

第一步：
  select id from big where id < 最小ID order by id desc limit 每页数据 *【页码-当前页】

第二步：找到目标页最小的ID
  select 
      *
  from 
      (select id from big where id < 最小ID order by id desc limit 每页数据 *【页码-当前页】) as A
  order by A.id asc limit 1

第三步：
  select 
      * 
  from 
      big
  where 
      id >= ( 第二步：找到目标页最小的ID )
  order by id desc limit 每页数据;

前面的页面

第一步：
  select id from big where id > 当前页最大值 order by id asc limit 每页数据 *【当前页-页码】)

第二步：获取目标页最大ID
  select 
      id 
  from 
      (select id from big where id > 最大ID order by id asc limit 每页数据 *【当前页-页码】) A 
  order by A.id asc limit 1

第三步：
  select
      *
  from 
      big
  where 
      nid <= (第二步：获取目标页最大ID)
  order by id desc limit 10;

2.4 缓存 redis

使用缓存来提高页面的响应能力，即：优先去缓存中获取，如果没有的话再去数据库中获取。

但是，在使用缓存时会有特殊的情况：

缓存穿透，例如：访问ID不存在的数据（暴力访问）。

- 尽可能的在代码中进行判定 & 校验
- 即使数据库没有数据也缓存一下，例如：缓存30s
- 布隆过滤

缓存击穿，例如：热点数据同时过期、大量数据同时过期。

- 加锁，多个请求来获取数据，如果不存在别都去数据库，通过加锁只让1个去数据操作，其他的等待 2s 再来获取一遍。
  - 热点不过期，由定时任务去更新数据。

import redis
  import time


  def get_data():
      conn = redis.Redis(host='127.0.0.1', port=6379, password='qwe123', encoding='utf-8')

      # 1. 读取数据
      data = conn.get("wupeiqi666")

      # 当数据不存在时，应该去数据库中获取数据
      if not data:
          # 设置锁，其实就是一个标记
          success = conn.set('wupeiqi666_lock', 9999, nx=True)
          # 设置成功
          if success:
              # 去数据库中读取数据
              data = "数据呀"
              conn.set("wupeiqi666", data)
              conn.delete("wupeiqi666_lock")
          else:
              time.sleep(10)
              get_data()

      return data

缓存雪崩，缓存服务器宕机然后重启，此时如果数据都过期了，压力全给数据库了。

- 预热，先将热点数据在缓存中创建，再启动去接受批量请求。
  - 随机过期，设置的过期时间尽量随机一些，避免出现通知过期。
  - 分布式，让热点数据放在多个服务器上。

注意：redis是单进程&单线程的。

2.5 非关系型数据库

redis、mongodb等。

3. 运维角度【下一节】

binlog日志
主从 & 读写分离

集群 & 高可用

其他命令

mysql>desc 表;
mysql>show create table 表;
mysql>show processlist;
mysql>kill thread_id;
mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

生活杂记，新的开始，新的征程

生活杂记，老运维blog站