数据库设计以及优化

一、数据库三范式
（1）第一范式1NF：保证每列不可再分。

（2）第二范式2NF：保证表中的每个字段都与主键有关，每张表只描述一件事。

（3）第三范式3NF：消除传递依赖，如果某个字段可以通过其他字段推导出来，那么这个字段是没有必要存在的。

二、日志类型
（1）介绍：日志文件中记录这MySQL数据库运行期间发生的变化, 记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。

当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

（2）错误日志：记录MySQL运行过程中较为严重的警告和错误信息，以及MySQL启动和关闭的详细信息。

-- 查看mysql错误日志
show variables like '%log_error%';

（3）通用查询日志：是记录建立的客户端连接和执行的语句。可以通过show variables like '%general%' 查看通用查询日志是否开启，默认是不开启的。

-- 查看mysql通用查询日志
show variables like '%general%';

-- 开启通用查询日志 general_log=1是开启，等于0是关闭
SET GLOBAL general_log=1;

（4）二进制日志：包含所有更新数据或表结构（新增、删除、修改、改表等）SQL 信息的记录。 在搭建MySQL主从时就要求开启二进制日志。

如果要查看二进制日志文件的话，可以使用mysql服务器自带的mysqlbinlog工具！

-- 查看二进制日志
show variables like '%log_bin%';

（5）中继日志：在MySQL主从复制实现中，记录从数据库数据改变。（从机可以理解为备份库）

（6）慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不适用于索引的查询。

-- 查看慢查询日志
show variables like '%quer%';

（7）DDL日志：DDL日志 记录由数据定义语句（如DROP TABLE和ALTER TABLE）生成的元数据操作。 元数据操作的记录被写入MySQL数据目录(datadir)中的文件ddl_log.log中。这是一个二进制文件; 在实际需要记录元数据语句之前，不会创建ddl_log.log，并在mysqld成功启动后被删除。 因此，该文件可能不会以完全正常的方式运行在MySQL服务器上。目的是为了保证MySQL能成功启动。

三、MySQL的执行计划
MySQL通过explain命令输出执行计划，对要执行的查询进行分析。

（1）什么是执行计划：简单来说，就是SQL在数据库中执行时的表现情况，通常用于SQL性能分析、优化等场景。

（2）优化与执行：MySQL会解析查询，并创建内部数据结构（解析树），并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。

（3）执行计划结果介绍：

-- 优化级别，一般要求至少是range级别，最好能达到ref级别。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > range > index > ALL

id: select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行。

select_type：表示查询的类型，常用的值如下：

SIMPLE：表示查询语句中不包括子查询或union

PRIMARY：表示此查询是最外层的查询

UNION：表示此查询是UNION的第二个或后续的查询

UNION RESULT：UNION的结果

SUBQUERY：SELECT子查询语句

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没有用到UNION查x询

table: 表示当前行访问的是哪张表。当有union查询时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的行id。

partitions: 查询将匹配记录的分区。 对于非分区表，该值为 NULL。

type：表示存储引擎查询数据时采用的方式，是比较重要的一个属性。通过它可以判断出查询是全表扫描还是基于索引的扫描。

常见的值如下：

system：逻辑表中限制只返回一行数据，属于const的特例。

explain select * from (select * from student  limit 1) t;

const：表最多有一个匹配行。因为只有一行，只读一次, const查询速度非常快。

一般情况下把主键或唯一索引作为唯一条件的查询都是const。

explain select * from student where sid = 1;

eq_ref: 经常出现在表连接中, 使用了唯一索引或者主键索引进行查找, 另外一张表中只有一条数据与之匹配

但是需要注意，如果另一个表中所有行都被读取到了，就是ALL了。

explain select * from student,teacher where stid=tid;
explain select * from student join teacher  on stid=tid;

ref：查询时，使用非唯一索引或主键索引时, 其他的索引作为查询条件。

explain select * from student where sname='张三';

fulltext: 只要是全文索引使用的就是fulltext类型。

ref_or_null：以外键列作为条件，搜索时包含null值。

注意：只搜索外键列是null为ref

explain select * from student where stid=3 or stid is null;

index_merge 索引合并优化。当使用索引合并优化时的类型。要求只能合并单表的索引查询结果。

alter table student add column (age int(11));
create unique index bbb on student(age);
insert into student values (2,'学生2',2,12);
insert into student values (3,'学生3',2,13);
explain select * from student where stid=1 or age=1;

range：把这个列当作条件只检索其中一个范围。常见where从句中出现between、<、in等。主要应用在具有索引的列中

explain select * from student where sid between 1 and 5;

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，因为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

explain select count(*) from student;

ALL：Full Table Scan，遍历全表以找到匹配的行

explain select * from teacher where tname='张老师';

possible_keys：表示查询时能够使用到的索引，注意并不一定会真正使用，显示的是索引名称

key：表示查询时真正使用到的索引，显示的是索引名称

rows：MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率的高低

key_len：表示索引字段的最大可能长度, 并非实际使用长度, 即key_len是根据表定义计算而得, 并不是通过表内检索出的.

Extra：表示很多额外的信息，各种操作会在Extra提示相关信息，常见的几种如下：

Using where：表示查询未使用索引，作用只是提醒mysql使用了where过滤结果集

Using index：表示查询需要通过索引，索引就可以满足所需数据

Using filesort：表示mysql无法使用索引直接完成排序，用缓冲空间来进行排序, order by 的字段为非索引

Using temporary：表示查询时使用到了临时表，一般出现于去重、分组等操作，多出现于没有索引的字段。

四、MySQL优化 
（1）硬件方面的优化

磁盘寻找（Disk seeks）：磁盘需要一段时间才能找到一段数据。

磁盘读写（Disk reading and writing）：当磁盘处于正确位置时，我们需要读取或写入数据。

CPU（CPU cycles）：当数据在主存储器中时，我们必须处理它以得到结果。

存储带宽（Memory bandwidth）：当CPU需要比CPU缓存中更多的数据时，主存储器带宽成为瓶颈。

（2）软件方面的优化 

表结构是否正确（Are the tables structured properly?）：特别的是列是否具有正确的类型，和表中列的个数是否正确。

正确的设置索引达到查询高效（Are the right indexes in place to make queries efficient?）：需要考虑的是什么SQL会导致索引无效，什么情况会让查询效率更高。

对于不同情况选择不同的存储引擎（Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use? ）选择不同的存储引擎对性能和可伸缩性具有较大的影响。

每张表是否具有适当的行格式（Does each table use an appropriate row format?）：主要取决于适当的存储引擎。压缩表可以占用更低的磁盘空间和更少的I/O操作。压缩表适用于InnoDB和MyISAM存储引擎。

应用程序是否使用适当的锁策略（Does the application use an appropriate locking strategy? ）：在具有高并发、分布式应用程序中，选择适当的锁策略以保证数据的共享性和特定的情况下独占数据。InnoDB存储引擎在不需要我们参与下能处理大部分锁问题，允许数据库实现更好的并发性，减少代码调优量。

所有缓存区域使用的大小是否都正确（Are all memory areas used for caching sized correctly?）：配置的原则是缓存区域大到足以容纳所有频繁访问的数据，但又不能太大，否则导致过量占用物理内存而导致分页。一般情况下需要配置InnoDB的缓冲池、MyISAM密钥缓存和MySQL的查询缓存。

（3）优化可能带来的问题

优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。

优化手段本来就有很大的风险，只不过你没能力意识到和预见到！

任何的技术可以解决一个问题，但必然存在带来一个问题的风险！

对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。

结论：保持现状或出现更差的情况都是失败！

 五、SQL优化
（1）表设计方面

1.NULL值：在老版本中含有NULL的列无法触发索引，但是在MySQL 5.7 以后已经可以触发索引了。但是可能出现“无法预料的结果”。所以在建立表时给列添加not null 约束或default 默认值是非常好的优化手段。

2.字段类型选择：尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。        尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

3.组合索引：在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

4.避免全盘扫描：对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（2）select字段列表

任何地方都不要使用 select * from 表 ，用具体的字段列表代替 “*” ，不要返回用不到的任何字段。

（3）where条件方面

1.避免负向条件：应尽量避免在 where 子句中使用负向条件操作符，否则引擎将放弃使用索引而进行全表扫描。        负向条件有：not like 、not in、not exists、!=、<>、等。

2.避免使用or逻辑：应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。在MySQL5.7 开始or已经可以触发索引了，老版本不可以。

select id from t where num=10 or num=20;
-- 可以这样查询：
select id from t where num=10
union all    
select id from t where num=20;

3.避免查询条件中字段计算：应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

4.避免查询条件中对字段进行函数操作：应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

5.注意模糊查询：模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。当头匹配不确定时索引失效。

（4）数据方面

SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

六、数据库锁
（1）锁的分类：

按照锁的级别分为：共享锁、排它锁。这也是最根本的分类标准。

按照锁的目标分为：行级锁、表级锁。锁影响到整个表叫做表级锁。锁影响到某一行或某几行叫做行级锁。

按照锁是否自动添加分为：自动锁、显示锁。MySQL InnoDB引擎会自动对Insert、Delete、Update添加排它锁，Select操作不会自动添加锁，但可以通过命令添加锁。

共享锁又称读锁，表示在读取数据时不允许其他事务对数据进行修改。

        SQL语句：select .... lock in share mode;

排它锁又称独占锁。在一个事务操作时，其他事务不允许操作数据。