1.数据库访问的优化法则

总体概述

1.减少磁盘访问：减少数据库的访问。
2.减少网络传输：返回更少数据，减少交互次数。
3.减少CPU及内存开销：减少服务器CPU开销。
4.增加资源：利用更多资源。

1.减少数据库的访问

创建并使用正确的索引；通过索引访问数据；优化sql执行计划。

2.返回更少的数据，减少交互次数

数据分页处理：客户端分页、服务端分页、数据库sql分页。只返回需要的字段。减少交互次数：对增删改进行批处理，使用存储过程、优化业务逻辑。

3.减少服务器CPU开销

大量复杂运算在客户端处理、使用绑定变量、合理使用排序、减少比较操作。

4.利用等多的资源

客户端多进程访问、数据库并行处理。

2.Mysql 的存储引擎:myisam和innodb的区别

1.二者区别

MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小并发。
innodb是支持事务的存储引擎，合于插入和更新操作比较多的应用，是行锁，适合大并发。

2. innodb引擎的4大特性

插入缓冲（insert buffer)；二次写(double write)；自适应哈希索引(ahi)；预读(read ahead)。

3.2者select count(*)哪个更快

myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

4.2者的索引的实现方式

都是 B+树索引，Innodb 是索引组织表，myisam 是堆表.

3.如何进行SQL优化

（1）选择正确的存储引擎。以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。
（2）优化字段的数据类型。记住一个原则，越小的列会越快。例如使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会比int更经济一些；如果你不需要记录时间， 使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。
（3）为搜索字段添加索引。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。
（4）避免使用Select *从数据库里读出越多的数据。
（5）使用 ENUM 而不是 VARCHAR。
（6）尽可能的使用 NOT NULL。NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。
（7）固定长度的表会更快。如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。

4.索引

1.索引的定义和作用

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度， 因为在执行这些写操作时，还要操作索引文件。

2.索引的底层实现原理

经过优化的B+树。主要是在所有的叶子结点中增加了指向主键索引节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

3.建立索引的要求

建立索引的时候要根据列基数来建立。列基数=列中不同的数据/列总数据。（越接近1，重复数据越少，越适合建立索引） 如果查询出来的数据超过总数的1/3，索引会失效。

4.什么情况下设置了索引但无法使用

① 以“%”开头的LIKE语句，模糊匹配；
② 少用or，用它来连接时索引会失效；
③ 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
④ 不符合联合索引的最左前缀匹配原则；
⑤ 存储引擎不能使用索引范围条件右边的列，联合索引有字段用到范围条件则后面的会失效；
⑥ 查询时，采用is null或is not null条件时，不能利用到索引，只能全表扫描；
⑦ 使用不等于(!= 或 <>)会使索引失效；
⑧ 不适合键值较少的列（重复数据较多的列）。

6.sql优化实际操作

1.explain出来的各种item的意义

id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行；如果 id 值相同，则顺序从上到下。
select_type：查询中每个 select 子句的类型。
table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。
partitions:匹配的分区信息。
type:索引类型。
possible_keys：列出可能会用到的索引。
key:实际用到的索引。
key_len:用到的索引键的平均长度，单位为字节。
ref:表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的。
rows:估计每次需要扫描的行数。
filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
extra:重要的补充信息：比如using file sort;using tempory;using shere等（前面两种应尽量避免）

2.type:索引类型

级别：system>const>eq_ref>ref>range>index>all
system：只有一条数据的系统表（很难达到）;
const：仅能查出一条语句,并且用了unique或者primary key索引;
eq_ref:唯一索引;
ref：普通索引;
range：检索指定范围的行，where后面是一个范围查询;
index：检索全部索引中的数据;
all：全表扫描。

3.profile的意义以及使用场景

Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候，需要用profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql的性能瓶颈。

4.explain 中的索引问题

Explain 结果中，一般来说，要看到尽量用 index(type为eq_ref、ref等，key列有值)，避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引;
被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总的需求;
建立索引的时候要根据列基数来建立。列基数=列中不同的数据/列总数据。（越接近1，重复数据越少，越适合建立索引）;
如果查询出来的数据超过总数的1/3，索引会失效。

7.事务

1.事务的定义

事务：保证一组数据库操作要么全部成功，要么全部失败。在mysql中他是由引擎层实现的，有些引擎支持（如：INNODB），有些引擎不支持（如：MYISAM）。
四大特性（ACID）：原子性、一致性、隔离性、持久性。

2.事务的启动方式

显示启动：begin/start transaction;；提交:commit；回滚：rollback;；关闭事务的自动提交：set autocommit=0;（1为开启，默认开启）

3.MySQL 中 InnoDB 支持的四种事务隔离级别名称

读未提交(read uncommitted)、读已提交(read committed)、可重复读(repeatable read)、串行(serializable)。
配置方式：transaction-isolation='xxx';

4.隔离性和隔离级别

Read Uncommitted:可以读取其他 事务未提交的数据。会出现脏读、不可重复读、幻读；
Read Committed:提交后，其他会话可以看到提交的数据。不会出现脏读，会出现不可重复读、幻读；(ORACLE默认隔离级别)
Repeatable Read: 事务开始时和事务结束前读到的数据是一致的。不会出现脏读、不可重复读、会出现幻读(innodb 独有)。(MYSQL默认隔离级别)
Serializable: 事务只能一个接着一个地执行，读加读锁，写加写锁。事务隔离级别最高。
不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差。

8.悲观锁和乐观锁

1.悲观锁

当数据被外界修改持保守态度，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库层提供的锁机制。当我们使用悲观锁的时候我们首先必须关闭mysql数据库的自动提交属性， 因为MySQL默认使用autocommit模式，关闭命令为：set autocommit=0;，悲观锁可以使用select…for update实现，在执行的时候会锁定数据，防止其他事务更改数据。
查询条件与锁定范围：
1.具体的主键值为查询条件：比如查询条件为主键ID=1等等，如果此条数据存在，则锁定当前行数据，如果不存在，则不锁定。
2.不具体的主键值为查询条件：比如查询条件为主键ID>1等等，此时会锁定整张数据表。
3.查询条件中无主键：会锁定整张数据表。
4.如果查询条件中使用了索引为查询条件：明确指定索引并且查到，则锁定整条数据。如果找不到指定索引数据，则不加锁。 悲观锁的优缺点：悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

2.乐观锁

观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会对数据的冲突与否进行检测，如果发现冲突，则让返回用户错误的信息，让用户决定如何去做。 一般来说，实现乐观锁的方法是在数据表中增加一个version字段，每当数据更新的时候这个字段执行加1（或时间戳）操作。
参考文献:乐观锁和悲观锁

9.全局锁、表锁和行锁

1.全局锁

定义：对整个数据库实例加锁。加锁命令：FLUSH Table With Read Lock;（FTWRL）
结果：使用加锁命令后，整个数据库处于只读状态，阻塞DDL和DDR。这种一般用于全库的逻辑备份中。

2.表锁

分类：表级锁和元数据锁（MDL）。
元数据锁（MySQL5.6后支持）的特点：
1.不需要显示使用，访问表时自动加上；
2.做增删改查时加MDL读锁，表结构变更时加写锁。（给小表加字段的方法：设定等待时间，如果在该时间内能获得锁就改表，不能就不改）
3.读读不互斥，但读写以及写写互斥。

3.行锁

概念：针对表中行记录的锁，比如：两个事物同时修改数据时，要有先后顺序。
两阶段锁协议：行锁时需要才加上的，但不是sql语句执行后就会释放，而是等到事物结束后才会释放。解决办法：如果事物中需要锁多行，则把最可能造成冲突和最影响并发的锁放到最后。

4.死锁和死锁检测

死锁：资源循环依赖，并且互相等待。
解决办法：1.超时等待。（少用，因为时间设置不差当会造成误杀）；2.死锁检测（开启方法：innodb_deadlock_detect=off），出现死锁后回滚某一个事物。
问题：死锁检测需要消耗CPU。
解决热点行更新的方法：1.关闭肯定不会发生死锁的表的死锁检测。（不安全）；2.控制并发度：客户端（不太可行）和服务端（修改MySQL源码，让其进入引擎前排队）。

10.数据库的三大范式

第一范式：每一列属性都是不可再分的属性值，确保每一列的原子性。
第二范式：在第一范式的基础上，每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。（不存在重复列） 第三范式：在第二范式的基础上，数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。（要求主键）

11.MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

1.varchar与char的区别

在单字节字符集下，char(N)在内部存储的时候总是定长，而且没有变长字段长度列表中。 在多字节字符集下面，char(N)如果存储的字节数超过 N，那么 char(N)将和 varchar(N)没有区别。 在多字节字符集下面，如果存储的字节数少于 N，那么存储 N 个字节，后面补空格，补到 N 字节长度。 都存储变长的数据和变长字段长度列表。 varchar(N)无论是什么字节字符集，都是变长的，即都存储 变长数据和变长字段长度列表。

2.varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

3.int（20）中20的涵义

是指显示字符的长度。不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示。

4.mysql为什么这么设计

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样。