MySQL 进阶02

-----转自 【王者-YCZ】的笔记

三、SQL开发的规范和建议

1.SQL语句分类

DDL : 数据定义语言
DCL : 数据控制语言
DML : 数据操纵语言
DQL：数据操纵语言（查询）

2.DDL规范

库

操作：
  CREATE 
  DROP 
  ALTER 

规范: 
  1. 库名不要使用大写字母,数字开头 
  2. 库名要和业务有关
  3. 不要使用内置关键字
  4. 创建数据库要加字符集(UTF8MB4)
  5. 生产中禁用DROP操作
  6. 字符集修改：从小到大，比如: utf8 ---> utf8mb4

表:

操作：
  CREATE 
  DROP 
  ALTER
  TRUNCATE

规范: 
  1. 表名不要使用大写字母,数字开头
  2. 表名要和业务有关
  3. 选择合适的数据类型(完整的,简短的,符合数据特点)
  4. 建表要有主键。
  5. 每个列要加非空约束,并设定默认值0。
  6. 每个列要有注释,comment，每个表要有注释。
  7. 每个表要设置存储引擎(innoDB)和字符集(utf8mb4).
  8. 生产禁用DROP操作
  9. alter做表结构变更时,大表不能在业务繁忙期间变更；
  如果一定需要，交给DBA来做，通过PT-OSC，在线做DDL,对业务影响小。
  在线DDL：在8.0版本以前需要的操作，8.0版本以后就不需要了，因为源数据库只有一份，而不是多份。
  10.TRUNCATE，历史归档数据应用,使用truncate配合分区表进行实现,避免使用Delete。

3.DCL规范

操作：
  grant 
  revoke 
规范：
  1.遵循权限最小化。

4.DML规范

操作：
  insert
  update
  delete

规范: 
	1.insert，尽量批量,尽量精准录入<将字段名写全>
		insert into t1(id,name) values(...,...),(...,...),(...,...);
	2.大的事务,进行拆分,避开业务繁忙期。
	3.update/delete,必须加where条件。
	4.delete操作尽量替换为update。(使用状态列,逻辑删除)
	5.整表删除,不用delete，用truncate.

5.DQL规范

操作：
	select：MySQL Select与其他数据库的不同
1.查询设定的参数
mysql> select @@port;
mysql> select @@datadir;
mysql> select @@socket;
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables;
mysql> show variables like '%trx%';

select @@sql_mode; 					查看SQL_MODE规范
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

2.计算 
mysql> select 1+1;

3.调用函数
mysql> select now();

6.关键字逻辑执行顺序

    from
    where
    group by
    having
    select
    distinct 
    order by
    limit
解析：
1.找到表:from
2.拿着where指定的约束条件，去表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.distinct去重
7.将结果按条件排序：order by
8.限制结果的显示条数

了解：
	其中，group by中做了三件事：排序，去重，分组。

7.关于删表的面试题

drop table t1    : 表结构 + 表段全部物理删除
trucate table t1 : 保留表结构,清空表段的数据页,属于物理删除
delete from t1   : 保留表结构,逐行删除表的每行数据,属于逻辑删除,会产生大量日志和碎片.

8.group by

1.原因：在5.7的版本中，group by加入了 ONLY_FULL_GROUP_BY 的SQL_MODE限制。
2.示例：
select a , b ,count(c) 
from t1 
group by a;

报ONLY_FULL_GROUP_BY错：
	在select后的列表,例如b，没有在函数里包裹,也没再group by后出现就报错。

不报错的情况：
	如果分组列，即a列，可以唯一确认一个唯一对象时（比如，主键）,就不会违反SQL_MODE。

四、索引原理和优化

索引作用：提供了类似于书中目录的作用,目的是为了优化查询。

1.MySQL的索引类型

B树索引
Hash索引
R树
Full text
GIS 

2.B树查找算法

BTREE 
B+TREE：叶子节点之间有双向指针。
B*TREE：叶子节点之间和枝节点之间都有双向指针。

1.B树对等值查询有一定的优势，而对范围的查询则需要采用B+树与B*树。
2.mysql中innodb引擎用的是B*树；myisam引擎用的是普通B树。

B*树图：

3.MySQL中innodb引擎的索引

可分为聚集索引、辅助索引（也称为二级索引或者普通索引）两大类。

聚集索引：
	1.如果建表时有主键,自动选择主键列作为聚集索引列
	2.如果没有主键,则选择唯一键作为聚集索引列
	3.如果都没有,则自动生成隐藏聚集索引	
	4.聚集索引只能有一个,非空唯一,一般是主键
	5.聚集索引叶子节点,就是磁盘的数据行存储的数据页
	6.MySQL是根据聚集索引,组织存储数据的,数据存储时就是按照聚集索引的顺序进行存储数据

辅助索引：
  1.辅助索引,可以有多个,是配合聚集索引使用的
  2.辅助索引,只会提取索引键值,进行自动排序生成B树结构

辅助索引细分：
  1.普通的单列辅助索引
	2.联合索引：多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
	3.唯一索引：索引列的值都是唯一的

索引相关命令：
  创建单列索引：alter table 表名 add index 索引名称(索引字段);
  创建联合索引：alter table 表名 add index 索引名称（索引字段1，索引字段2);
  创建前缀索引：alter table 表名 add index 索引名称(district(前缀字符数));
  删除索引：alter table 表名 drop index 索引名称;
同一个表中，索引名不能同名。

4.聚集索引构建B树结构

解析：

聚集索引的树如同上图构成，此时，只能加速条件为id字段的查询。

5.辅助索引构建B树结构

解析：

1.先将索引列name字段进行排序；
2.排完序后，生成叶子节点，枝节点，根节点；
3.由于是要查找整行数据，因此在构建树时，还会存储pk的值；
4.首先根据name字段条件查询到pk的值，然后通过pk值，回到聚集索引的表，获取整行数据；
5.辅助索引列的值中，如果重复值过多，会造成回表的次数增多，回表的次数多，IO的次数就多，而且还是随机IO，因为辅助索引中的列重新排序后，回表后，所在行就不是顺序的了；
6.因此，设计辅助索引除了为增强当前索引列查询的速度，还需要尽肯能地减少回表的次数
（可以通过选择重复值较少的列以及使用联合索引来减少回表的次数）。

6.聚集索引和辅助索引最大的区别（重要）

在叶子节点处，
	聚集索引存储的是整行数据；
	辅助索引存储的是索引列键的值，以及对应的pk值。
	在查询时，辅助索引是配合聚集索引进行查询的，而不是单独使用。

7.索引树高度的影响因素

影响因素：
	1.表的数据行
	2.索引列值过长
	3.数据类型
对应的解决策略：
	1.分表，分库，数据库缓存
	2.使用前缀索引
	3.选择合理，简短的数据类型

8.索引执行计划获取

1.命令：
	explain SQL查询语句；		或者		desc SQL查询语句；
	
2.通过命令获取到的是优化器选择完成的,它认为代价最小的执行计划。

3.作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题，
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

9.索引执行计划分析（重要）

​ 1.type：查询类型

共分为：ALl，index，range，ref，eq_ref，const(system)六种查询类型。

各种查询类型出现的情况：
1.ALl：全表扫描，不走索引。
	a.查询条件列,没有索引 
	b.查询条件出现以下语句(辅助索引列)：
		<>、not in()、like"%aa%"、隐式转换、where中条件进行计算、索引失效(统计信息过旧)
  c.注意:对于聚集索引列,使用以上语句,依然会走索引

2.index：全索引扫描

3.range：索引范围扫描
	a.查询条件出现以下语句（辅助索引列）：
		<、>、>=、<=、between and、like "a%"、or、in
	b.查询语句中出现or、in，是不能享受到B+树算法优势的；
		因为or，in里面的值有可能不是顺序的，不能利用到双向指针，只能重新全索引查询；
		此时，可以使用 union all 来替代or或者in。
		示例：
			 select * from t1 where num in ("110","119");
			 可以替换为：
			 select * from t1 where num="110"
			 union all
			 select * from t1 where num="119";
		注意，用 union all 替换后，查询类型将不再显示range，而是显示两条ref的查询类型。

4.ref：非唯一性索引，等值查询。

5.eq_ref：多表连接查询，右表的关联列是主键或者唯一键。

6.const(system)：主键或者唯一键的等值查询。

​ 2.key_len：应用索引的长度

解析：

按预留的最大长度来计算：
	tinyint：最大长度为1个字节，如果没有设置not null，则需要预留1个字节的长度来存储是否为null；
	int：最大长度为4个字节，如果没有设置not null，则需要预留1个字节的长度来存储是否为null；
	char(5)：根据字符编码集，由于utf8mb4编码1个字符最大为4个字节，因此为5*4，如果没有设置not null，则需要预留1个字节的长度来存储是否为null；
	varchar(5)：varchar数据类型中的"abcde"占据的是6个字节长度，还有一个字节需要用来存储一共为5个字节，且varchar在超过255字节长度时，需要用两个字节来存放长度，因此，根据按预留的最大长度计算，varchar(5)的key_len长度为5*4+2，如果没有设置not null，则需要预留1个字节的长度来存储是否为null。
	

联合索引应用：
idx_a_b_c(a,b,c)  ==>  查询条件为：a, ab, abc时，可以应用到索引。
	1.where a=? and b=? and c=?
		where条件的等值查询，当三个索引列都在时，无关摆放顺序，全都可以走联合索引。
	2.where a=？ and c=？
		只会走a的索引，c的索引不会走。
	3.where a=？ and b>? and c=？
		只会走a和b的索引，c的索引不会走；如果联合索引建立改成index(a,c,b)，则a，b，c的三个索引都会走
	4.建立联合索引时，在多子句的情况下，应按照子句的顺序建立。
	5.建立联合索引时有顺序讲究，查询时则没有；在建立联合索引时，重复值少的列应该放在最左侧。

​ 3.extra：额外信息

filesort：额外的文件排序
出现原因：在创建的索引没有应用到时，才会进行额外的排序。
出现情况：group by、order by、distinct、union
解决：建立联合索引，将group by和where的列一起建立联合索引。

10.面试题

题目意思:我们公司业务慢,请你从数据库的角度分析原因。
mysql出现性能问题,总结有两种情况:
	1.应急性地慢：突然夯住
		处理过程:
			a.show processlist;  	获取到导致数据库hang的语句
			b.explain 						分析SQL的执行计划,有没有走索引,索引的类型情况
			c.建索引,改语句
	2.一段时间慢(持续性的):
			a.记录慢日志slowlog,分析slowlog
			b.explain   分析SQL的执行计划,有没有走索引,索引的类型情况
			c.建索引,改语句

11.建立索引的场景

1.必须要有主键,如果没有,可以创建无关列作为主键条件的列
2.索引列要是经常作为where order by  group by  join on, distinct 的条件的列
3.最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
4.列值长度较长的索引列,建议使用前缀索引
5.降低索引条目,不要创建没用索引,不常使用的索引要清理
6.索引维护要避开业务繁忙期
7.构建索引后，查询类型至少要到range及以上才好。