###1.一个查询过程
- 查询缓存
- SQL解析，预处理，优化器生成执行计划
- 解析：对SQL语句进行解析，生成解析树，检查是否有错
- 预处理：检查错误，验证权限
- 优化器：基于成本的优化器：预测一个执行计划的成本，选择最小的。静态优化：优化解析树，如将sql转换成等价形式；运行时优化：重新定义关联表顺序、等价变换
- 调用存储引擎执行查询
###2.事物 ####隔离级别：
- READ UNCOMMITTED:未提交读（脏读），根本就没有什么事物可言好么！
- READ COMMITED：提交读：一个事物从提交到结束都是不可见的，只能读取到已经提交的数据。防止脏读
- REPEATABLE READ：可重复读：在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别，防止脏读和不可重复读，还存在幻象读。因为可重复读会锁住读取的数据，但是阻止不了写操作。这时要是有写入，就有可能会幻读。
- SERILIZABLE：串行化
####几个概念
-
脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时， 另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个 事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
-
不可重复读：在同一事务中，两次读取同一数据，得到内容不同，重点在update和delete。
-
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同,重点在insert
####死锁
- A事物锁定1，想要2；B事物锁定2，想要1，结果谁也不让谁了
- InnoDB解决方案：持有最少行级排他锁的事物回滚，欺负穷人有木有！谁锁的多就归谁啊！
####事物日志
- 修改表的时候修改数据拷贝，并把修改行为记录到日志中，串行追加，等不忙的时候再慢慢执行。
- 能够提高事物效率，因为串行追加，而不需要来回移动磁头。
- 称为预写式日志，一次操作需要两次磁盘读写。
####多版本并发控制（MVCC）
- 其实MYSQL实现的并不是简单的行级锁，而是多版本并发控制(MVCC)
- 因为它能避免很多加锁操作，实现非阻塞的读操作
原理：
- 每个表多了两列，一列存储创建时间，一列存储删除时间
- 其实是骗你的，保存的不是时间，而是一个叫版本号的东西，可以理解为整个数据库的自增主键id
- select的时候，只查找版本号早于当前事务版本号的数据，可以保证读取的数据在这个事务之前就已经存在，这样读操作就不用加锁啦
####事务与锁 innodb将事务分成两个阶段：加锁阶段和解锁阶段
事务 锁
begin;
insert into 加insert锁（排他锁）
select ... 加select锁（共享锁）
commit; 事务提交，同时释放所有锁
保证事务调度串行化
###3.存储引擎
- 每个数据库保存为目录下的一个子目录
- 创建表时会在数据库目录下建一个同名的.frm文件保存表的定义
####MYISAM - 内存中只缓存索引，不缓存数据 - 不支持事务，只支持表级锁 - 可做压缩操作 - 索引文件和数据文件分离，索引文件仅保存数据地址
####innoDB - 索引和数据都可以缓存到内存 - 支持事务和4种隔离级别，支持行级锁 - 数据多版本读取：MVCC - 实现外键 - 表数据文件本身就是一个按B+数组织的索引结构，使用聚簇索引，主键组织成一个B+树，行数据存储在叶子节点上 - 所有的辅助索引都存储主键的值，因此需要检索两遍：先从辅助索引查到主键值，再从主键值查到数据
###4.索引
索引不光用来提升查询速度，还能用于排序，如ORDERBY 和GROUP BY
####1.前缀索引
如果字符太长，索引效率很低，可以索引从头开始的部分字符做索引
-
查询只能从最左端开始，占地小效率高
- 不支持covering index(指的是查的数据就是索引本身)
- 不支持order by
- 适合blob,text,长varchar
- 确定前缀索引长度方法：计算前n个字的区分度，当增加长度区分不明显的时候
-
选择多长的字符做索引的策略：
-
计算每个单词出现的频率
select count(*) as cnt,city
from city group by city order by cnt desc limit 10
-
取n个前缀字母，看它的频率和原单词频率相近的时候就可以了，如从3个前缀开始：
select count(*) as cnt ,left(city,3) as pref
from city group by pref order by cnt desc limit 10
-
- 前缀索引无法做GROUP BY和ORDER BY
####2.聚簇索引（没看懂）
####3.覆盖索引
- 索引保存它包含的列数据
- 条件：where中的字段和所查字段都在索引中
优化方法：有索引index_accountbank_customeremail
bad:
select * from account where account_bank= 'icbc.beijing.fengtai' and customer_email like '%@baidu.com';
good:
select * from account a join (select account_id from account where account_bank= 'icbc.beijing.fengtai' and customer_email like '%@baidu.com')t on a.account_id=t.account_id
这样内循环sql查找id可以使用覆盖索引，过滤出较少id再查询需要的列
####4.联合索引 CREATE TABLE people( last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, birthday DATE NOT NULL, gender ENUM('m','f') NOT NULL, KEY(last_name,first_name,birthday) )
联合索引支持最左前缀，全值匹配
如果不是按照索引最左列开始查询，则无法使用索引：如果查first_name='bill'，无法使用索引
无法跳过某一列，如无法查询last_name和birthday的数据，只会索引第一列
####5.自定义哈希索引
如果数据较长，做查询的时候会很慢，可以自建索引：
-
建表：
CREATE TABLE hashtest( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, url_crc INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(id) )
-
建立触发器存储url_crc或手动添加
-
查找
select * from hashtest where url='www.jiandan.com' and url_crc=CRC32('www.jiandan.com')
SHA1和MD5太长，并不适合 查询的时候url和url_crc都应该包括，因为可能会产生冲突
####主键和唯一限制就是通过索引实现的：
create table test( ID INT NOT NULL PRIMARY KEY, name INT NOT NULL, UNIQUE(ID), INDEX(ID))
这样等于建了三个相同的索引
####冗余索引
如果建了索引INDEX(A,B),就没必要建INDEX(A)，因为索引A就是索引AB的前缀索引
索引INDEX(A,ID)也是冗余的，因为ID主键已经包含在二级索引里了
###6.优化实践
####join join算法只有nested loop join
根据左边表中每一条数据去右边表查
优化：
- 小结果集驱动大结果集，减少循环次数
- 右边的表可以使用index命中
####有三种级别的where用法：
- 在索引中使用where来过滤不匹配记录，最优选择
- 使用索引覆盖扫描，在索引中过滤掉过滤不匹配记录（Extra 中出现using index）
- 从数据表中返回记录，再做过滤，在MYSQL服务器层完成，（Extra中出现using where）最差的情况
获取表中数据两种方式：
- 通过索引获得数据所在行的位置，获取数据
- 优点：快速定位，适合行数不多的情况，索引小容易缓存
- 缺点：获取数据比较多的话，索引顺序存放，数据可能会随机读
- 逐行扫描，过滤出所需的数据
####分解关联查询：将需要关联的数据分多次查出来
好处是因为：
可以高效利用缓存：如果某个表数据很少变化，那么数据在缓存中就会一直存在
####in
- 对大多数数据库，IN()等同于多个OR，时间复杂度O(n)
- 但对于MYSQL,会对IN中的数据进行排序，然后使用二分查找，时间复杂度为O(logn)
####in vs exist
- in 把外表和内表做hash连接，
- exist 对外表做loop循环，每次循环对内表查询
-
exist 里面根据子查询的true or false来判断，通常用来判断子查询数据是否存在
-
查找一个的时候用 exist，如查找小明爸爸的时候：
select * from father where exists( select id from student where name='xiaoming')
-
查找很多爸爸的时候用in
select * from father where student_id in( select id from student)
-
优化器可能成为性能瓶颈，把多条相同结构语句放到一个in（）中一次查询
####count()
- count(name)统计的是name这一列不为null的个数
- count(*)统计结果集的行数，跟有没有null没关系
####group by
group by 后面最好跟带索引的，或者id
-
bad:
select actor.first_name,actor.last_name from film_actor inner join actor using(actor_id) group by actor.first_name,actor.last_name
-
good:
select actor.first_name,actor.last_name from film_actor inner join actor using(actor_id) group by film_actor.actor_id
####limit
尽可能使用索引覆盖扫描
-
bad：
select film_id,description from film order by title limit 50,5
-
good:
select film.film_id,film.description from film inner join( select film_id from film order by title limit 50,5 )as lim using(film_id)
offset会导致扫描很多不用的列再抛弃掉
记录书签：记录最后一条记录的id： 降序读取：
select * from rental where rental_id<1630 order by rental_id desc limit 20
####union优化
带上ALL，可以防止MYSQL给临时表加上DISTINCT选项，导致对整个临时表做唯一性检查
####range（id>100）后的条件无法使用索引，因此最好放到最后
多个range出现只有第一个会使用索引，考虑将range转换为in（）
####insert 插入时想实现如果没有则更新：on duplicate key update
insert into customer (customer_id, customer_phone ) values(111, '111') on duplicate key updatecustomer_phone='111';
将文本文件加载到数据表中，使用load data infile比insert快很多
###6.服务器配置 ####1.配置文件位置：/etc/my.cnf
####2.分配缓存 重要的缓存有这么几个：
-
InnoDB缓冲池（buffer pool）：
- 缓存索引，行数据，锁.....
- 还用来延迟写入
- 需要分配足够的空间
- 过大缓冲池会使预热，关闭过慢
-
InnoDB日志文件
-
MyISAM键缓存（key_buffer_size）
-
线程缓存：为后续连接做准备，也类似于一个线程池
####3.I/O配置
InnoDB使用日志来减少提交事务时的开销：用日志把随机I/O变成顺序I/O
InnoDB日志是环形写方式
两个重要参数：
- 日志文件总大小
- 每个文件的大小
###7.日志
####二进制日志（binlog）
作用：复制和恢复
主从架构复制：由Replication线程实现异步主从同步
- master将改变记录到二进制日志（binary log）中
- slave向master发请求，master传送二进制文件
- slave将二进制文件追加到relay log文件
- slave的sql线程检测到relay log中新增了内容，执行之
格式：
- statement:基于sql语句模式，有些语句可能导致数据不一致
- row：基于行模式，记录行的变化，数据量大
- mixed:混合模式（默认模式）
内容： 由一系列binlog event构成，每个event包括header和data
查看binlog文件：
SHOW MASTER LOGS
查看一个文件中的event:
SHOW BINLOG EVENTS IN 'mysql-bin.000016'
####慢查询日志
设置：
-
位置：/etc/my.cnf
-
配置：
log-slow-queries=/data/mysqldata/slowquery.log long_query_time=2
超过2秒记录
####undo,redo日志
- undo:记录某数据被修改前的值，用于事务失败时回滚
- redo:记录某数据被修改后的值，用来恢复未写入磁盘而已成功的事务
#####example:
事务序号T1，对X进行修改，修改前：5，修改后：15
undo日志：<T1,X,5>
redo日志：<T1,X,15>
#####执行顺序：
- 用户生成一个事务
- undo log buffer记录被修改数据的原始值
- redo log buffer记录被修改的数据更新后的值
- redo,undo日志持久化到磁盘
- 事务将操作写入db buffer(此时内存数据和磁盘不同，内存中的数据定义为脏数据)
- db buffer选择合适的时机持久化到磁盘
先持久化日志的策略叫做预写日志
#####系统恢复策略：
- log file中某个事务没有事务结束标记：说明事务进行一半中断了，数据还没写入buffer,需要进行undo操作
- log file中某个事务有结束标记：说明数据写到buffer中，没有持久化，进行redo操作
####检查点checkpoint 定期将db buffer写入磁盘。写入时，会记录checkpoint发生的时刻，恢复时，只需要redo/undo最近的一次checkpoint之后的操作
###8.范式
一个数据库设计所满足的某种设计标准的级别
范式越高，数据的冗余度越小
####第一范式（1NF） 关系中每个属性都不可再分
缺点：数据冗余：
！
####第二范式（2NF）
记录有唯一标识，就是要有主键id
####第三范式 任何字段不能由其他字段派生出来，即字段没有冗余
####BCNF 符合第三范式，并且只有一个候选码
###9.sharding ####1.主键生成策略：
- UUID：统一的id
- 缺点：Id非常长，建立索引和索引查询时会慢
- 维护一个sequence表，存表名和nextid
- 缺点：每次插入都要查询此表，容易成为瓶颈
####2.实现层面的产品
-
DAO层自行实现
- 优点：不受ORM框架限制
- 缺点：不通用
-
ORM框架层实现
- hibernate shard:表现不好
- mybatis还没有
-
应用服务器与数据库之间通过代理实现：根据路由规则，对sql进行解析后路由到目标shard
- 对应用程序透明，通用性好
- mysql proxy
####3.多数据源事务处理
###10.不知道怎么分类
####（1）varchar(30)含义： 变长，字符最大长度30，单位跟字符集有关，如utf8每个字符占3字节，最大长度90字节
####（2）explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 336 Using filesort
重要的：
-
type:查询方式
- all：全表扫描，最差
- range：使用索引进行范围查询
- index：索引扫描
- ref：两个表join时，被驱动表使用索引查询
-
possible_keys:可能的索引
-
key：被优化器选中的索引
-
ref：join的索引字段
-
rows：估算的结果集
-
extra:细节信息：
- using where：仅通过索引还不能查询出全部所需的数据
- using index：仅通过索引就能查询出全部所需的数据
- using temporary：使用了临时表，order by 或 group by 时会出现
- using filesort：使用Order by时，无法通过索引完成排序，MySQL就需要选择排序算法来排，就会出现这个
####（3）存储过程
编写自定义数据库api，包含一系列sql语句的集合，类似于function
语法：
DELIMITER // CREATE PROCEDURE usp_demo(IN param1 INT) BEGIN sql END // DELIMITER ;
####(4)分区 分区就是把一个表分成好几部分
- 适用于数据量大
- 更容易维护，可对独立分区进行操作
- 可以分布在不同设备上
- 可以理解为一种粗粒度的索引，可以根据一定规则先定位到分区，排除别的分区
例如：将每一年的销售额放在不同分区里：
CREATE TABLE sales( order_date DATETIME, .... )ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))( PARTITION p_2010 VALUES LESS THAN(2010), PARTITION p_2011 VALUES LESS THAN(2011), PARTITION p_catchall VALUES LESS THAN MAXVALUES);