优化规范

基础规范

表存储引擎必须使用InnoDB表字符集默认使用
utf8，必要时候使用utf8mb4
- 通用，无乱码风险，汉字3字节，英文1字节
- utf8mb4是 utf8的超集，有存储4字节例如表情符号时，使用它
禁止使用存储过程，视图，触发器，Event
- 对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层
- 调试，排错，迁移都比较困难，扩展性较差
禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统,数据库中存储路径
禁止在线上环境做数据库压力测试
测试，开发，线上数据库环境必须隔离

命名规范
库名，表名，列名必须用小写，采用下划线分隔tb_book t_book。
- abc, Abc, ABC都是给自己埋坑
库名，表名，列名必须见名知义，长度不要超过32字符
- tmp, wushan谁TM知道这些库是干嘛的
库备份必须以bak为前缀，以日期为后缀
从库必须以-s为后缀
备库必须以-ss为后缀

表设计规范

单实例表个数必须控制在‘2000个以内
单表分表个数必须控制在1024个以内
表必须有主键，推荐使用UNSIGNED整数为主键
- 删除无主键的表，如果是row模式的主从架构，从库会挂住
禁止使用外键，如果要保证完整性，应由应用程式实现
- 外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈
建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

列设计规范
根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
根据业务区分使用char/varchar
- 字段长度固定，或者长度近似的业务场景，适合使用char,能够减少碎片，查询性能高
- 字段长度相差较大，或者更新较少的业务场景，适合使用，varchar，能够减少空间
根据业务区分使用datet. ime/timestamp
- 前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime
必须把字段定义为NOT NULL并设默认值
- NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
- NuLL需要更多的存储空间
- NuLL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑
使用INT UNSIGNED存储IPv4，不要用char(15)
使用varchar(20)存储手机号，不要使用整数
- 牵扯到国家代号，可能出现+/-/()等字符，例如+86
- 手机号不会用来做数学运算
- varchar可以模糊查询，例如like “138%”
使用 TINYINT来代替ENUMD
- ENUM增加新值要进行DDL操作
  索引规范
唯一索引使用uniq_[字段名]3来命名
非唯一索引使用idx_[字段名]来命名
单张表索引数量建议控制在5个以内
- 互联网高并发业务，太多索引会影响写性能
- 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
- 异常复杂的查询需求，可以选择ES等更为适合的方式存储
组合索引字段数不建议超过5个
- 如果5个字段还不能极大缩小row范围，八成是设计有问题
不建议在频繁更新的字段上建立索引
非必要不要进行JOIN查询，如果要进行JJOIN查询，被JOIN的字段必须类型相同，并建立索引
- 踩过因为JOIN :字段类型不一致，而导致全表扫描的坑么?
理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,C),相当于建立了(a),(a,b),(a,b,c)

SQL 规范
禁止使用select ，只获取必要字段。select 会增加
cpu/io/内存/带宽的消耗
- 指定字段能有效利用索引覆盖
- 指定字段查询，在表结构变更时，能保证对应用程序无影响
insert必须指定字段，禁止使用insert iinto T values()
- 指定字段插入在表结构变更时能保证对应用程序无影响
  SQL 优化
  SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径:
  工程优化如何实现:数据库标准、表的结构标准、字段的标准、创建索引（参考优化规范）
  SQL语句的优化:当前SQL语句有没有命中索引。
  mysql内部优化器
  在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。
  当前执行的SQL
```
explain select t from tb_ book where id=1;
show warnings;
```
  内部优化器优化后的效果:
```
/* select#1 */ select "I" As ‘id’, "千锋Java厉害" As ‘name~ from ~db_ mysq1_pro~.~tb_book where true
```

explain

在完成工程结构的优化之后，数据库、表、表中的字段及表的索引，都能够支持海量数据的快速查找。但是查找依然需要通过SQL语句来实现，因此性能优秀的SQL语句是能够走索引，快速查找到数据的。性能不Ok的SQL语句，就不会走索引，导致全表扫描。
阿里云rds提供了慢sql的查询功能。找到慢SQL后，如何做优化，以及如何查询一条SQL语句是否是慢SQL，那就可以通过Explain工具。通过在SQL语句前面加上Explain关键来执行，于是就能看出当前SQL语句的执行性能。
e.g.

EXPLAIN SELECT * FROM teacher

通过在SQL语句前面加.上explain关键字，执行后并不会真正的执行sql语句本身，而是通过explain工具来分析当前这条SQL语句的性能细节:比如是什么样的查询类型、可能用到的索引及实际用到的索引，和一些额外的信息。

id

在多个select中，id越大越先执行，如果id相同，上面的先执行，

select_type

该列描述了查询的类型:

simple:简单查询
primary:外部的主查询
derived:在from后面进行的子查询，会产生衍生表
subquery:在from的前面进行的子查询
runion:进行的联合查询
table
当前正在查询那张表
type
可以直观的判断出当前的sql语句的性能。type里的取值和性能的优劣顺序如下:
null > system > const > eq_ ref > ref >range > index > all
对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。
nul
性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可，因此是性能最好。
system
很少见。直接和一条记录进行匹配。
const
使用主键索引或唯一索引和常量进行比较这种性能非常好
eq_re
在进行连接查询时，连接查询的条件中使用了本表的主键进行关联，因此这种类型的sql就是eq_ref
```
EXPLAIN select * from tb_ book_author left JOIN tb_ book on tb_book_author.book_id = tb_book.id
```
ref
简单查询:使用普通列作为查询条件
```
EXPLAIN select * from tb_ book wherename="book1"
```

在进行连接查询时，连接查询的条件中使用了本表的联合索引列，因此这种类型的sq|就是ref

EXPLAIN select book_id from tb_book lefts join tb_book_author on tb_book.id= tb_book_author.book_id

range
在索引列上使用了范围查找，性能是ok的

index
在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，因此这种sql的查询类型就是index?d

explain select *from tb_book
tb_book中的所有列:id和name都是索引列。

ALL
全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的

explain select * from tb_author

possible_key

这一次的查询可能会用到的索引。也就是说mysql内部优化器会进行判断，如果这一次查询走索引的性能比全表扫描的性能要差，那么内部优化器就让此次查询进行全表扫描一一这样的判断依据我们可以通过trace工具来查看

EXPLAIN select * fromemployees where name like "customet"
这条sql走索引查询的行数是500多万，那么总的数据行数也就500多万，因此直接进行全表扫描性能更快

key

实际该sq|语句使用的索引

rows

该sql语句可能要查询的的数据条数

key_len

这一列的主要作用，通过查看这一列的数值，推断出本sql选择了联合索引中的哪几列。

EXPLAIN select * from employees where name="customer10011" # 74
EXPLAIN select * from employees where name ="customer10011" and age=30 # 74 4 = 78
EXPLAIN select * from employees where name ="customer10011" and age=30 and position="dev" # 74 + 4 + 62 = 140
EXPLAIN select* from employees where name = "customer10011" and position="dev" # 74
name长度是74,也就是当看到key_len是74，表表示使用了联合索引中的name列

key_len 计算规则
EXPLAIN select*from employees where name =”customer10011”and age=30 and position=”dev”
字符串
1.char(n):n字节长度
2. varchar(n):2字节存储字符串长度，如果是utf-8,则长度3n+2
数值类型
1. tinyint: 1字节
2. smallint:2字节
3.int:4字节
4. bigint: 8字节
时间类型
1. date:3字节
2. timestamp:4字节
3. datetime:8字节
如果字段允许为NULL,需要1字节记录是否为NuLL
索引最大长度是768字节,当字符串过长时, mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

extra

这一列展示了这条sql的一些其他信息

using index
使用了覆盖索引
所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接,从索引列中获取数据，而不需要进行查表。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。

EXPLAIN select book_id, author_id :from tb_book_author where book_id=1

using where
where的条件没有使用索引列。这种性能是不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。

EXPLAIN select * from tb_author where name> ”a"

using index condition
查询的列没有完全被索引覆盖，并且where条件中使用普通索引

EXPLAIN select *from tb_ book_author where book_id>1

Using temporary
会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现,

EXPLAIN select DISTINCT namefrom tb_author

这种情况可以通过给列加索引进行优化。s

using filesort
MySQL对数据进行排序，都会使用磁盘来完成，可能会借助内存，涉及到两个概念:单路排序、双路排序

EXPLAIN select *from tb_ author order by name

Select tables optimized away
当直接在索引列上使用聚合函数，意味着不需要操作表

EXPLAIN select min(id) from tb_book

优化

优化规范

基础规范

命名规范

表设计规范

列设计规范

索引规范

SQL 规范

SQL 优化

mysql内部优化器