数据库与SQL优化笔记(一)
# 数据库存储引擎与SQL优化笔记
[TOC]
## Mysql分层，存储引擎
数据库从上到下
- 连接层 （提供与客户端连接的服务）
- 服务层
- 提供各种用户使用接口 {select...}
- 提供SQL优化器
- 引擎层（提供了各种存储的方式InnoDB MyISAM）
- 存储层（存储数据）
```sql
mysql> show engines; 查看支持那些引擎
```
```sql
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8
```
## SQL优化
原因：原始的sql语句可能性能低，执行时间太长，SQL语句欠佳，索引失效，服务器参数不合理（缓冲区，线程池）
SQL：
编写过程。。。
解析过程 。。。
SQL优化主要是优化索引。索引(B树， hash)
索引：帮助mysql高效获得数据的数据结构
索引弊端：
-索引的存储空间比较大
-索引不是所有情况适合，少量数据，频繁更新不适合
-索引降低增删改的效率
优势：提高查询效率，降低IO，
CPU 使用率
索引分类：
单值索引：单值， age; 一个表可以有多个单值索引
唯一索引：不能重复
符合索引：多个列组合的索引，相当于二级目录。（name, age）
创建索引：
create 索引类型 索引名 on 表（字段）
语法
单值：
create index dept_index on tb(dept);
唯一索引:
create unique name_index on tb(name);
符合索引
create index dept_name__index on tb(dept, name);
alter table 表名 索引类型 索引名 类型
[alter table tb add index dept_index(dept)]
注意：如果一个字段是primary key，默认是主键索引。
删除索引
drop index 索引名 on 表名
查询索引
show index from 表名
---
研究sql性能问题
a, 分析sql的执行计划: explain，模拟SQL优化器执行SQL优化器
b,Mysql优化器干扰我们的优化
查看测试优化结果
```sql
mysql> explain select * from tb \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tb
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
1 row in set (0.00 sec)
```
ps:explain可以查看sql优化的结果，'\G', 或者 ' ; ' 会在选择上不同
id 越大越优先，相等从上到下
type: system>const>eq_ref>ref>range>index>all
效率，越左边越快，优化的前提必须有索引
system:只有一条数据的系统表，只有一条查询;或衍生表只有一条数据的主查询
const:仅仅能查到一条数据的SQL用于primary key或unique
以上部分我们对数据库优化有了一个初步了解
下面的部分知识是基于《高性能Mysql》和《Mysql技术内幕》的
## Schema 与数据类型优化
参考《高性能Mysql》
- 尽可能使用正确的最小数据类型
- 最小够用，但是要避免不足而带来的数据类型范围修改操作，存储空间内存缓存消耗相对少
- 简单就好
- 简单数据类型的规则耗费更小的CPU周期，例如整数比字符串代价更低。字符集涉及校对规则。因此尽可能用Mysql日期类型，而非字符串
- 尽量避免NULL
- NULL对于SQL语句优化更困难。
**Mysql schema设计中的陷阱**
ps: schema是设计，概述，表结构的意思，书籍原标题是这样的
- 太多的列不好
- mysql存储引擎工作的时候在服务层和存储引擎之间通过行缓冲格式拷贝数据，然后在服务层将缓冲内容解码成各个列。从行缓冲中将解码过的列转成行数据结构的代价比较大。
- 太多的关联
- 这里书中批判“实体-属性-值”设计模式很糟糕，在Mysql中会对于优化造成压力。Mysql限制每个关联操作最大只能有61张表。
**范式和反范式**
数据库中我们用第X范式来描述一个数据库表的设计，越高意味着冗余越低。维护代价也更高。但是一定的冗余也并非毫无优点。也许能更有利于SQL优化引擎。
**Alter table**
alter修改表结构，底层的一种策略是使用复制表的方式修改（不是所有alter引起表的重建）。消耗比较大，而且锁表。容易影响服务器的性能，一种方式是用基于插件，或者其他服务，在其他服务器上alter table而后拷贝，同步。
书中介绍了一些非官方的优化技巧，需要承担一点分析这里不提。
## 高性能索引
ps:这不是一篇科普性文章，只是我的学习笔记，所以不会全面解释索引的优点。
- 索引并非总是好的
想起之前的一个例子。对于一个数组，找到一个数字，最好的方法是什么。有些会想起无数数据结构。
但是！！！
如果只需要一次查询遍历即可。
数据结构的应用一定是结合具体需求的
当数据量小的时候，直接拿到内存中遍历效率和不断在索引树上IO到是真不一定那个更好。
下面的sql语句就无法使用索引优化查询。mysql无法解析这个行为
```sql
select actor_id from actor where actor_id + 1 = 5;
```
所以我们尽量把索引列放到一侧。
```sql
select actor_id from actor where actor_id = 4;
```
- 为所有的where后面的内容都加上索引？
如果多个索引做or, and 操作，维护索引会耗费大量内存，早期版本中鼓励用语句的嵌套，而非or, and带索引的列。这样效率不一定更优于直接查。
- select * ???
全部取出来会让优化器无法完成索引覆盖扫描这类优化。除非一些缓存技术的需要，否则不用这样用。
- 优化查询次数
例如用户多次需要查看某个东西各自的URL，每次都单独调用URL是不好的，尽量使用缓存的设计。
。。。
。。。
。。。
总结：sql优化主要集中在如何高效的使用索引上
## sql执行过程
- 客户端 经过客户端与服务器的通信协议到达 Mysql服务器（半双工）
- 服务器先查询缓存，查到返回，否则下一步
- 解析SQL，预处理，由优化器生成对应的执行计划。
- MySQL根据优化器生成执行计划，调用存储引擎API执行查询
- 将结果返回客户端
投递猿辅导等公司8个岗位 >