MySQL使用规范
explain:
这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
|
列
|
列值
|
说明
|
|
|
|
|
|
| |
SIMPLE
|
简单SELECT，不使用UNION或子查询等
|
|
|
|
|
|
| |
PRIMARY
|
子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY
|
|
|
|
|
|
| |
UNION
|
UNION中的第二个或后面的SELECT语句
|
|
|
|
|
|
|
select_type
|
DEPENDENT UNION
|
UNION中的第二个或后面的SELECT语句，取决于外面的查询
|
|
|
|
|
|
| |
UNION RESULT
|
UNION的结果，union语句中第二个select开始后面所有select
|
|
|
|
|
|
| |
SUBQUERY
|
子查询中的第一个SELECT，结果不依赖于外部查询
|
|
|
|
|
|
| |
DEPENDENT SUBQUERY
|
子查询中的第一个SELECT，依赖于外部查询
|
|
|
|
|
|
| |
DERIVED
|
派生表的SELECT, FROM子句的子查询
|
|
|
|
|
|
| |
UNCACHEABLE SUBQUERY
|
一个子查询的结果不能被缓存，必须重新评估外链接的第一行
|
|
|
|
|
|
| |
all
|
Full Table Scan， MySQL将遍历全表以找到匹配的行
|
|
|
|
|
|
| |
index
|
Full Index Scan，index与ALL区别为index类型只遍历索引树
|
|
|
|
|
|
|
type
|
range
|
只检索给定范围的行，使用一个索引来选择行
|
依次从最优到最差分别为: system > const > eq_ref > ref > range > index > ALL
|
|
|
|
|
| |
ref
|
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
|
|
|
|
|
|
| |
eq_ref
|
类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
|
|
|
|
|
|
| |
const、system
|
当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
|
|
|
|
|
|
| |
NULL
|
MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
|
|
|
|
|
|
|
rows
|
|
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
Using where
|
查询的列未被索引覆盖，where筛选条件非索引的前导列
|
|
|
|
|
|
|
Extra
|
Using temporary
|
|
|
|
|
|
|
| |
Using filesort
|
|
|
|
|
|
|
| |
Using index
|
查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高
|
能够触发覆盖索引
|
|
|
|
|
| |
Using index condition
|
表示使用的索引方式为二级检索(非聚簇索引树)
|
|
|
|
|
|
降低磁盘IO和CPU计算
一张student表，表设计结构，索引，内容如下图
1.尽量不用join，必须要保证字符集属性类型和长度相同，并建立索引
字符集不同会触发全表扫描
2.禁止%开头查询，禁止 != , not in等负向查询，会全表扫描
第一条语句%开头,type类型为ALL,key没有命中索引, rows为6全表扫描，性能会很差。
第二条语句没有%开头, type类型为range, key命中索引， rows为1命中数据，性能会很好。
!=同理
3.字段类型, 与查询字段赋值类型必须相同
name类型为varchar，name=1类型不同，会有强制类型转换;name='1'类型相同。尽管命中了索引，但是第一条语句rows=6，全表扫描。
4.字段必须定义为not null,并提供默认值
表中6条数据，通过sql查询age!=18的数据，可以预想会有5条数据，然而结果只有四条，漏掉的一条age允许为null
因此得对sql进行改造:
select * from student where age!=18 or age is null;
这样才能查询出默认值为null的数据，此为大坑。
5.禁止在列上进行函数或表达式计算
6.联合索引，区分度最高的放在最左边
过滤大量数据
7.联合索引，列个数不超过5个
降低数据库CPU计算
8.禁止使用外键约束，有服务端保证完整性
9.禁止使用存储过程，视图、触发器、Event
10. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
消耗更多的 CPU 和 IO 以网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响
11.orderby, group by ,dictinct要加索引
12. 垂直拆分，将字段短、访问频率高的字段放在一张表内
1)数据库本身有自己的内存缓冲池，以row为单位缓存数据，短row能缓存更多数据
2)高频率访问row，能直接访问缓存池数据，减少访问磁盘
举例:
13.分页查询优化
1) select返回列较少或列宽较小的时候，我们可以通过建立复合索引的方式优化分页查
覆盖索引(covering index): 将查询的字段建立到联合索引里面。
此时只有name建立索引，explain结果如下, type为all全表扫描
优化: 通过name,age建立索引，explain结果如下, type为index, extra为Using index
2)select返回列较多或列宽较大的时候，可以先查询非聚簇索引树，在查询聚簇索引树，防止频繁回表操作
必须使用innodb(高并发数据量大)
必须要有注释
必须使用utf8或utf8mb4
数据库和表的字符集统一
禁止select *
不具备扩展性, 表结构会变更, 全部查出来传输字段也过长，需要多少列具体协商查询多少列
insert into 必须制定列
拒绝复杂sql,将大sql拆分多条简单sql
关于innodb和mysiam选型最关键两点:
事务：对一致性帮助很大
行锁: 对提高并发帮助很大
不命中索引,innodb不能用行锁，会走表锁，因为行锁是实现在索引上，而非锁在物理行记录上。
降低数据库磁盘IO
读多写少用缓存
前台后台分离架构
良好SQL
存储和索引
其余规范(线上环境)：
禁止在服务器上私自安装mysql客户端来访问数据库
禁止在业务高峰期批量操作
禁止跳过工单跳过审批私自操作线上数据库