MySQL知识点记录(二)

Django连接MySQL

1. 配置数据库连接，其实就是修改项目下的settings.py文件，具体配置样式可参照下图来：

# Database
# https://docs.djangoproject.com/en/1.8/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'zaihui',
        'USER': 'zaihui',
        'PASSWORD': 'Tdi9LyUMke4PXB',
        'HOST': 'zaihuiprodnew.cbzzv8ulixeg.rds.cn-north-1.amazonaws.com.cn',
        'PORT': '3306',
        'OPTIONS': {'charset': 'utf8mb4'},
    },
    'read': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'zaihui',
        'USER': 'zaihui',
        'PASSWORD': 'Tdi9LyUMke4PXB',
        'HOST': 'prod-read-01.cbzzv8ulixeg.rds.cn-north-1.amazonaws.com.cn',
        'PORT': '3306',
        'OPTIONS': {'charset': 'utf8mb4'},
    },
    'data': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'zaihui',
        'USER': 'zaihui',
        'PASSWORD': 'Tdi9LyUMke4PXB',
        'HOST': 'prod-read-02.cbzzv8ulixeg.rds.cn-north-1.amazonaws.com.cn',
        'PORT': '3306',
        'OPTIONS': {'charset': 'utf8mb4'},
    },
    'dw': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'zaihui',
        'USER': 'zaihui',
        'PASSWORD': 'HeFZqyrv78BDfD',
        'HOST': 'local-redshift.cjrxiomjhv1y.cn-north-1.redshift.amazonaws.com.cn',
        'PORT': '25439',
    },
}

2. 安装数据库驱动
   python2.x：MySQLdb
   python3.x: pymysql
   另外，Django应用程序需要安装mysqlclient
3. 创建表，并同步到mysql（以django orm的方式举例）

# 首先创建一个model
from django.db.models import Model

class Account(Model):
    uid = UUIDField(default=uuid.uuid4, editable=False, db_index=True)
    phone = CharField(max_length=11, unique=True, db_index=True)
    apisecret = UUIDField(default=uuid.uuid4, editable=False)
    qrcode = UUIDField(default=uuid.uuid4, editable=False)
    birthday = DateField(null=True, db_index=True)
    created_at = DateTimeField(default=datetime.datetime.now, db_index=True)
    updated_at = DateTimeField(auto_now=True, db_index=True)
    phone_verified = BooleanField(default=False)

    def __str__(self):
        return '{}'.format(self.phone)

    @property
    def name(self):
        return self.profile.name

    @property
    def sex(self):
        return self.profile.gender

# 生成mi记录的文件
python manage.py makemigrations

# mi表（同步到数据库）
python manage.py migrate

索引

索引的最左前缀匹配原则：在组合索引中会从左到右依次去查询对应索引的列，在使用查询时不能跳过前面的索引，否则索引不会起作用。

1. 创建索引

结合两种场景来说明：

Django下创建索引

可以在创建表结构的时候设置该列field的参数index_db=True

mysql命令行下创建索引

可以在创建表的时候指定或者之后更改表结构

create table <table_name> (字段名 字段类型,
index(指定的索引字段列))
或者
alter table <table_name> add index index_name(指定的索引字段列)

2. 索引优化

数据量比较大的表才建立索引；
最左匹配特性；
选择区分度较高的列做索引，比如布尔属性的字段就不适合建索引；
建索引的列尽量不要去参与计算；
尽量去扩展索引而不去新建索引；
尽量在where、order by经常用到的字段建索引；
对于写特别频繁的列不要建索引；

3. 查询优化

尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描；
最左前缀匹配原则;
在建立索引的列不要进行计算；
尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效；
应尽量避免在where子句中对字段进行null值判断，可以设置默认值为0，确保该列没有null值；
尽量避免使用or操作符，而是采用union all来代替；
避免使用select *；
order by语句的优化，可以考虑在该列新建索引；
group by语句的优化，可以通过将不需要的记录在GROUP BY 之前过滤掉；
用 exists 代替 in；
使用 varchar/nvarchar 代替 char/nchar；
能用DISTINCT的就不用GROUP BY；
能用UNION ALL就不要用UNION，UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源；
在Join表的时候使用相当类型的例，并将其索引；

一个MySQL锁的应用

比如在update表中的某个字段时不想被其他线程或事务影响，可以select ... for update然后再update。