数据库优化 | Duiliuliu

MariaBD 与 Mysql

两者异同
MariaDB 由开源社区提供支持，与 Mysql 兼容，属于不同的代码分支，因为 oracle 收购 sun 公司，大家害怕 Oracle 闭源

MariaDB 支持 XtraDB 引擎，其是 Innodb 引擎的增强版
支持 Aria 引擎，是 MyIsam 引擎的增强版，Aria 引擎支持动态列，支持 json 格式数据，通过大量缓存的方式由于 MYISAM 引擎

DDL define 定义
DML manipulation 操纵
DCL control 权限控制

性能调优

硬件
表结构
查询

表结构

关系数据库

关系模型 – 范式
越高的范式数据冗余越低

关系数据库中，关系-属性-元组对应数据库中表-列-行

范式：

第一范式属性不可再分，每个属性原子性
第二范式属性都依赖于主关键字
第三范式去掉传递依赖，属性都直接依赖与主关键字，而不是传递函数依赖与主关键字

范式可以消除数据冗余、更新异常、插入异常、删除异常的问题

数据字段类型选择

数值型数据
MySQL 支持的数值数据类型有：
TINYINT 1byte
SMALLINT 2byte
MEDIUMINT 3byte
INT 4byte
BIGINT 8byte
字符型数据
char 定长
varchar 不定长，需要额外的 1-2 字节空间存储长度
日期型数据
datetime 8 字节
timestamp 4 字节

锁机制

表锁 MYISAM 只支持表锁，读的时候，其他事务可读不可写；写的时候，其他事务不可读写
行锁 Innodb 支持，只给索引上的索引项加锁，也就是说：只有通过索引检索数据才会加行锁，否则加表锁
页面锁介于表锁与行锁

当大量的事务因无法立即获得锁而挂起，将会占用资源影响服务性能，为提升性能，可以设置锁超时时间，一般为 100 秒
innodb-lock-wait-timeout=100

死锁两个事务都需要获得对方持有的排他锁才能继续完成事务
Innodb 一般可以自动检测到死锁(图),它会让一个事物释放锁并回滚，另一个事务拿到锁，完成事务

Innodb 与 MYISAM 区别

Innodb 支持行级锁，支持事务回滚 ACID，比较安全，提供崩溃回复功能，多版本并发控制

Myisam 支持全文索引，查询高效，不支持外键与事务，并且每张 MYISAM 表都存储为三个文件 frm 表定义文件、myd 数据文件、myi 索引文件

事务

innodb 事务的实现：日制先行

隔离级别：

读未提交 read uncommited
读已提交 read commited
可重复度 repeatable read
序列化读 serializable

实现原理

慢查询

慢查询即运行较慢的查询

MySQL 开启慢查询：

1
2
3

slow_query_log = 1
slow_query_log_file = mysql.slow
long_query_time = 2

索引

聚集
非聚集

SQL 优化

not in 与子查询 –> join 连接

模式匹配 like “%xxx%”
like “xxx%” 可以命中索引，而 like “%xxx%”不可以
不过我们可以通过覆盖索引的方式优化 SQL

# 原始SQL
select count(*) from artist where name like '%Queen'
# 优化后SQL
select count(*) from artist a
  join (select artist_id from attist where name like '%Queen%') b
  on a.artist_id = b.artist_id

limit 优化
通过索引定位，然后 limit(0,10) 优于直接 limit(10000,10)
count(索引) 优于 count(*)
or
or 条件语句不会使用索引，因此可以选择改为 union all 合并结果
合理使用索引