背景：
目前作为一个开发人员，无论是什么语言出身，和数据库打交道是我们必备的功课。所以我们需要对其深入的了解
下面是我对数据库的一些整理笔记。
索引数据结构
二叉树 ：
弊端：出现单边极端情况下，索引形同虚设
红黑树 ：
又叫平衡二叉树，是进化版的二叉树。如果存储大数据的情况下，树的高度无法控制。
Hash表 ：
对数据进行一次hash运算，得到哈希值。查询的时候只需要去找到值对应的哈希值就可以一次查询出来。效率极高。
但是无法进行范围查询。
B-Tree ：
又叫多路平衡二叉树，为了吧高度降低。
叶节点具有相同的深度
叶节点的指针为空
叶点中的数据索引从左到右递增排列
B树也只能查询单个值，并不能解决范围查询。
mysql使用的是B+Tree：
类似于链表中的跳表查询索引
一个节点默认是16K
非叶子节点不存储data，只存储索引，可以放更多索引
叶子节点不存储指针
顺序访问指针，提高区间访问的性能
MyLSAM存储引擎图
InnoDB存储引擎（默认该引擎）
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引：将数据元数和索引存储在同一篇聚集索引里面，索引的逻辑顺序=数据物理顺序。
InnDB表必须有主键，并且推荐使用整形的自增主键。如果建表的过程中没有建立主键，MYsql会默认设置一个。
联合索引的底层存储结构
Mysql分层，存储引擎
连接层：提供与客户端连接的服务
服务层:提供各种用户使用的接口，提供SQL优化器
引擎层：InnDB:事务优先（适合高并发操作；行锁），MyISAM：性能优先（表锁）
存储层：存储数据
SQL优化
原因：性能低、执行时间太长、等待时间太长、sql语句欠佳（链表查询）、索引失效，服务器参数设置不合理（缓冲区，线程数）。
sql编写过程：
select dinstinct ..from ..join..on..where...group by ....having ..order ....
解析过程：
from ...on ...join ...where ...group by ...javing ...select dinstinct...order...
sql优化，主要就是在优化索引，索引：相当于书的目录；
索引：index是帮助mysql高效获取数据的数据结构。索引是数据结构（树：B树(默认)，Hash树....）
B树：小放左，大放右
索引的弊端：
索引本身很大，可以存放在内存/硬盘（通常为硬盘）；
索引不是所有情况均使用：少量数据，频繁更新的字段、很少使用的字段
索引确实可以提高查询的效率，但是会降低增删改的效率。
索引优势：
提高查询效率（降低IO使用率）
降低CPU使用率（因为B树索引，本身就是一个排好序的结构，索引查询的时候不需要排序）
3层Btee可以存放 上百万条数据
Btree:一般都是指B+，数据全部存放在叶节点中。
索引
分类：
单值索引：单列，age；一个表可以有多个单值索引，name。
唯一索引：唯一重复。id
复合索引:多个列构成的索引（相当于 二级目录： z:zhao）
创建索引：
方式一：create 索引类型 索引名 on 表（字段）
单值索引：create index dept_index on tb(dept):
唯一：create unique index name_index on tb(name);
复合索引：create index dept_name_index on tb(dept,name);
方式二：alter tablle 表名 索引类型 索引名（字段）
单值索引：alter tablle tb add index dept_index(dept):
唯一：alter tablle tb add index name_index(name);
复合索引：alter tablle tb add index dept_name_index (dept,name);
注意：如果一个字段是primary key,则改字段默认就是 主键索引。
SQL性能问题
1、分析sql的执行计划
explain:可以模拟sql优化器执行sql语句，从而让开发人员，知道自己执行sql的状况。
查询执行计划 explain+sql语句 会出现一张执行计划表
id:编号
di值有相同，又有不同，id值越大越优先；id值相同，从上往下 顺序执行
select_type:查询类型
PRIMAPY 包含子查询sql中 主查询 ；
SUBQUEPY包含子查询SQL中的子查询;
SIMPLE 简单查询（不包含子查询、union）
derived衍生查询（使用到了临时表）
type:索引类型、类型
system>const>eq_ref>ref>range>inndex>all ,要对type进行优化的前提：有索引。
system,const只是理想情况；实际只能达到 ref>range
const:仅仅能查到一条数据的sql,用于Primary key 或unique索引 （类型与索引类型有关）
eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多、不能0）
ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
range :检索指定范围的行，where后面是一个范围查询（between, > < >=,特殊：in有时候会失效）
index :查询全部数据索引中数据
all:查询全部数据表中数据
system/const:结果只有一条数据
eq_ref:结果多条，但是每条数据唯一的
ref:结果多条，但是每条数据是0或多条。
table:表
查询过程中用到的表明，如果sql有别名，显示别名
possible keys: 预测用到的索引
可能用到的索引，是一种预测，不准确
key:实际使用的索引
实际用到的索引，准确
如果possible keys和key是null说明没有索引
key_len:实际使用索引的长度
作用：用于判断复合索引是否被完全使用。
ref:表之间的引用
注意与type中的ref值区分。不是一个东西。
作用：指明当前表所参照的字段。 要么是const常量，要么是用到是另外一个表的字段。
rows:通过索引查询到的数据量
被索引优化查询的数据个数，实际通过索引而查询到的数据个数
Extra:额外的信息
using filesort:性能消耗大；需要“额外” 的一次排序（查询）常见于 order by排序语句中
排序：先查询 查询字段中
对于单索引，如果排序和查找是同一个字段，则不会出现 using filesort；如果排序和查找不是同一个字段，则会出现 using filesort。
避免：where哪些字段，就order by哪些字段
复合索引：不能跨列（最佳左前缀）
避免：where和order by按照顺序使用，不要跨列，或者无序使用。
using temporary：性能损耗大，用到了临时表。一般出现在group by分组语句中
避免：查询哪些列，就根据哪些列分组。
using index ：性能提升；索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）
只要使用到的列，全在索引中，就是索引覆盖using index
如果用到了索引覆盖（using index时），会对possible_keys和key照成影响。
using where(需要回表查询) ：
假设age是索引列
但是查询语句 select name age from age=... 查询条件age不包括name，所以要回表查询name.
impossible where : where 子句永远为false
示例： explain select * from test where a='x' and a='y'
usering filesort (多了一次额外的查找。) 跨列使用造成的 where 和order by
复合索引小结
如果（a,b,c,d）复合索引 和使用的孙旭全部一致（且不跨列使用），则复合索引全部使用。如果部分一致，则使用部分索引。
单表优化
下面用一条sql举例：
-- 单表优化举例，下面sql -- 原sql
SELECT bid from book where typeid in(2,3) and authorid=1 order by typeid desc;
-- 加索引优化
alter table book add index idx_bta(bid,typeid，authorid);
-- 索引一旦进行升级优化，需要将之前废弃的索引删除，防止干扰。
drop index idx_bta on book
-- 根据实际sql解析执行顺序，调整索引顺序
alter table book add index idx_bta(typeid,authorid,bid);
-- 再次优化（之前是index级别） -- 思路，因为范围查询in优势会失效，因此交换索引顺序。将typeid放后面
drop index idx_bta on book -- 删除原索引
alter table book add index idx_bta(authorid,typeid,bid);
SELECT bid from book where authorid=1 and typeid in(2,3) order by typeid desc; 单表优化小结： a.最佳左前缀，保持索引的定义和使用的顺序一致性 b.索引需要逐步优化。c.含有in条件的sql将条件放在最后面。
多表查询优化：
小表驱动大表
索引建立经常使用的字段上 （一般情况对于左外连接，给左表加索引；右外连接，给右表加索引）
当编写sql时，将数据量小的表放左边。
Using join buffer:extra中的一个选项，作用：Mysql引擎使用了连接缓存
避免索引失效
复合索引，不要跨列或无序使用（最佳左前缀）
复合索引 ，尽量使用全索引匹配
不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效。
对于复合索引，左边失效，右侧全部失效。
复合索引不能使用不等于（！= <>）或 is null (is not null) , 否则自身以及右边全部失效。
sql优化，是一种概率层面的优化。至于实际是否使用了我们的优化，需要用expplain进行推测
在服务层中会对sql进行优化。可能会影响我们的优化
补救，尽量使用索引覆盖（using index）
like尽量以“常量” 开头，不要以‘%’ 开头，否则索引失效; 如果必须进行模糊查询，可以使用索引覆盖挽救。
尽量不要使用类型转换（显示、隐式），否则索引失效
示例： select * from teacher where tname=123 ,这里面tanme是varchar类型并且有建立索引。但是和数字匹配造成索引失效
尽量不要使用or，否则索引失效
一些其他的有优化方法
exist和in
如果主查询数据集大，则使用IN ，效率高。
如果子查询的数据集大，则使用exist ， 效率高
order by 优化
using filesort有两种算法，双路排序、单路排序（根据IO的次数）
MYsql4.1之前默认使用 双路排序 （从磁盘读取，第一次只扫描排序字段后进行排序（在buffer缓冲区）。第二次扫描其他字段）
-- IO比较消耗性能
Mysqk4.1之后你让使用 单路排序 （只读取一次，全部的字段。在buffer中进行排序，但此钟单路排序会有一定的隐患，如果数据量特别大，无法将数据一次性读取完毕。会进行分片读取，不一定是真的一次IO，有可能多次IO）
使用单路排序时，如果数据量大，可以考虑调大buffer的容量大小： set max_length_for_sort_data=1024 单位字节。
如果max_length_for_sort_data值太低，则mysql会自动从单路切换回双路排序。
提高order by 查询的策略：
选择使用单路、双路； 调整buffer的容量大小；
避免select * ...
复合索引 不要跨列使用，避免using filesort
保证全部的排序字段 排序的一致性（都是升序或降序）
SQL排查--慢查询日志
MySQL提供的一种日志记录，用于MySQL记录响应时间查过阈值的SQL语句
慢查询日志默认是关闭的：建议：开发调优是打开，而最终部署时关闭
检查是否开启命令： show variables like '%low_query_log%'
临时开启命令：set globak slow_query_log=1
永久开启：
打开/etc/my.cnf文件
在[mysqlD]下追加
slow_query_log=1
slow_query_log_file=日志文件.log
重启
慢查询阈值查询命令：show variables like '%long_query_time%'
临时设置阀值 ： set globle long_query_time=5 ; 设置完毕后，重新登录生效
永久设置阀值：
打开/etc/my.cnf文件
在[mysqlD]下追加set globle long_query_time=3
查询超过阀值的SQl : show global status like '%slow_queries%';
慢查询SQL被记录在日志中，打开日志后可以查看具体 是SQL。
通过mysqldunoslow 工具查看，可以通过一些过滤条件，快速查找需要定位的慢SQL
-s 排序方式 -r 逆序 -I 锁定时间 -g 正则匹配模式
锁机制
解决因为资源共享，而造成的并发问题。
分类：
操作类型：
读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。
写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读锁，写操作。
MDL锁（元数据锁）：如果开启一个查询事务后。无法对表结构进行修改。锁定表结构。
操作范围：
表锁 ：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁，但锁的范围大，容易发送锁冲突，并发度低。
行锁： 一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁。锁的范围较小，不易发生锁冲突，并发度高（很少发送高并发问题：脏读，幻读、不可重复读，丢失更新）。
页锁
MyLSAM表级锁
在执行查询语句select前，会自动给涉及的所有表加读锁，
在执行跟下操作DML前，会自动给涉及的表加写锁。
增加表锁SQL：locak table 表名;
查看表锁SQL：show open table;
如果某一个会话，对A表加了read锁，则该会话可以对A表进行读操作，不可以增删改。不能对其他表进行操作。
既如果给A表加了读锁，则当前会话只能对A表进行读操作
其他会话的操作：可以对A表以外的其他表，增删改查。对A表只能读，增删改需要等待释放锁。
如果一个会话，对表加了write锁 可以对加了锁的表， 进行任何（增删改查）操作。对其他表无法操作。
其他回合：对加了锁的表，可以进行增删改查，但是必须等待锁释放。对其他表可以任意操作。
InniDB表级锁
如果回合对某条数据A进行 DML操作(关闭自动commit情况下)。则其他回合必须等他commit才能对这条数据A进行操作。
表锁是通过unlock tables; 行锁是通过事务解锁。
行锁，不同会话操作不同数据；相互不影响。
行锁注意事项：如果没有索引或者索引在SQL失效，则行锁会转为表锁。
行锁的一种特殊情况：间隙锁：值在范围内，但却不存在。
行锁：如果有where,则实际加锁的范围 就是where后面的范围（不是实际的值）
行锁： InnoDB默认采用行锁;优点：比表锁性能损耗大。优点：并发能力强，效率高、因此，高并发用InnoDB。
如何给仅仅是查询语句的SQL加锁,在查询语句后面增加for update;
自动关闭commit命令:set autocommit=0;
