mysql构成
客户端发送查询请求，检测到开启了缓存，查询是否命中缓存，命中从缓存中获取数据返回，否则解析查询语句，优化器生成执行计划，执行计划，获取结果
ps:
1、缓存存放在一个类似HashMap的引用表中，hash值通过查询本身、数据库本身、客户端协议版本号等生成
2、查询中包含任何自定义函数、存储函数、用户变量、临时表、系统表查询结果都不会被缓存
mysql存储引擎
myisam和innerdb的主要区别 1、myisam只能支持表级锁，innerdb支持行级锁 2、myisam不支持事务，innerdb支持事务 3、myisam不支持外键，innerdb支持 4、myisam的索引的数据结构为索引文件与数据文件分离，索引的叶子节点保存数据文件的指针 innerdb的表必定存在一个主键索引（聚集索引），如果有主键的话，以该主键创建索引，若没有主键，选择一个不为空的唯一索引代替，若也没有，会隐式创建一个6个字节的字段作为主键创建聚集索引。
锁
乐观锁：假设不会发生冲突，提前不上锁，使用版本号，在提交时检查版本号，若版本号发生变化，认为数据失效，不去提交
悲观锁：假设会发生冲突，提前上锁，例如共享锁 排他锁
共享锁（读锁）：其他事务只可以读，不可以写
排他锁（写锁）：其他事务既不能读，也不能写
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最小,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最高,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
死锁：多个事务相互等待锁的释放，比如事务c1分别修改h1,h2两行数据，c2分别修改h2,h1两行数据，c1对h1添加排他锁，c2对h2添加排他锁，此时c1等待h2锁的释放，c2等待h1锁的释放，出现死锁
SHOW STATUS LIKE 'Table%'：查看表锁争用情况， 若Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况
myisam在执行select操作时会自动给表加读锁，执行delete、update、insert自动给表加写锁，在自动加锁的情况下，myisam会一次性的获得当前事务所需要的全部锁，所以不会出现死锁的现象
innerdb行级锁是添加到索引项上的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
死锁恢复：innerdb将持有最少行级排他锁的事务进行回滚,只需要重新执行因死锁回滚的事务即可 发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
innserdb避免死锁：
1、多个事务更新相同数据的顺序尽量保持一致
2、事务开始提前显示的获取全部的锁，select for update
3、选择合适的事务大小，不要太大
4、合理设计索引
事务
事务：
一组sql构成一个单元，要不全部执行要不一条都不执行\
特性：
原子性:一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
一致性:在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
脏读:m2修改了表t1的数据尚未提交事务，此时m1读取了t1表中的数据，然后m2在执行后边sql的时候出现异常，回滚了全部操作，此时m1读取的数据即为脏数据，即为脏读
不可重复读：一个事务中，同一条查询语句执行多次出现了不同的结果。例如：事务c1查询表t1两次，查询完第一次后，事务c2修改了表t1的内容并提交成功,此时c1对t1的第二次查询跟第一次查询的结果存在差异，即为不可重复读。（注意区别幻读，幻读的多次查询结果是一样的）
幻读：一个事务中，多次查询结果相同，但是根据执行结果进行操作时会出现与查询结果不同的效果。例如：事务c1查询t1中id=1的记录是否存在，发现不存在，然后执行insert语句插入id=1的数据，此时提示主键重复插入失败，再次执行select语句依然查不到id=1的数据，好像出现了幻觉，是因为有另一个事务c2在c1执行insert之前插入了id=1的数据并成功提交了。
事务隔离级别：
读未提交RU：一个事务m1可以读取到另一个事务m2尚未提交的数据。会产生脏读，不可重复读，幻读
读已提交RC：读已提交，一个事务只能读取到其他事务提交的数据。解决脏读，可重复读，幻读
可重复读RR：一个事务中同一条查询语句的多条查询结果总是相同的。解决脏读、可重复度，无法解决幻读；PS:可重复读的实现原理MVCC：给每行数据添加版本号，修改时版本号+1，事务开始时记录版本号，该事务中查询的数据都是固定版本的内容，所以即使其他事务成功修改提交了数据也不会影响当前事务。 可以通过给查询手动添加锁（共享锁、排他锁）的方式解决幻读（串行读的实现）
select ... lock in share mode //共享锁
select ... for update //排他锁
串行读serializable：无论读写都添加锁，读读可以共享，其他均不可，即最后为串行执行，解决脏读、不可重复读、幻读
mysql索引
数据结构：B+Tree 叶子节点存储数据（真实的数据、主键、地址），非叶子节点存储索引key，每一个页子节点有一个指向相邻节点的指针。
优点：
1、非叶子节点只存储key值，可以提高每个存储块（页）的存储数量，减少数的高度，减少磁盘IO
2、有序且相邻的叶子节点存在指针可以提高范围查询的性能
索引的查找流程：
1.索引精确查找: 确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中。（select * from user_info where id = 23）\
Create table table_x(int id primary key, varchar(64) name,key sec_index(name) )
• Select * from table_x where name = “d”;
通过二级索引查出对应主键，拿主键回表查主键索引得到数据， 二级索引可筛选掉大量无效记录，提高效率
聚集索引：
索引键值的顺序决定了表中数据的物理顺序，所以一个表中只有一个聚集索引。
非聚集索引：
索引项的顺序与数据的实际顺序可能不同
联合索引的数据结构：
覆盖索引：查询内容和查询条件全部包含在索引中，不需要回表操作
mysql 带索引和不带索引 select语句的执行顺序（个人理解）
例如：
t: id-主键，name:姓名，age年龄
SELECT * FROM T WHERE NAME = 'LDF' AND AGE = 20
1)没有任何索引
全表扫描，按NAME='LDF'筛选获得一个临时表t1，在t1的基础上满足筛选AGE=20的数据
2)Name有索引,AGE没有索引
搜索Name的索引文件，筛选满足Name='LDF'的索引key值，从而得到主键索引的key值，根据得到的值回表去主键索引中定位到具体的数据得到临时表t1,以t1为基础筛选AGE=20的数据
3)AGE有索引,Name没有索引 同二，先搜索AGE的索引文件，筛选满足AGE的索引key值，从而得到主键索引的key值，根据得到的值回表去主键索引定位到具体的数据得到临时表t2,以t2为基础筛选NAME='LDF'的数据
4)NAME和AGE两个单独索引 MySQL 会选择那个它认为似乎是最有效率的单列索引，另外的作为普通条件过滤
5)NAME和AGE创建联合索引 按顺序先检索name 在检索结果中检索age，最后得到主键索引的key值回表获取数据
sql优化
1、表的数据类型遵循小而简单的原则，越简单的数据类型通常会越快，占用越小的磁盘、内存，处理时需要的cpu也更少
2、通常没有太大必要使用DECIMAL数据类型，可以将原先的数据乘以10的n次方来存储，避免浮点数计算不精确和decimal计算代价高的问题
3、TIMESTAMP使用四个字节，只能表示1970-2038年，datetime使用八个字节范围要大得多
4、shcma的列不要太多，原因是存储引擎的API在工作时需要在服务层和存储层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，转换过程的代价非常高
5、将能筛选掉大量数据的条件放在where条件的最左侧
6、select * 改为具体的字段 *意味着mysql要额外查一次元数据表，而且多余的字段返回的数据过大影响性能
7、主键尽量不要参与业务
8、创建合理的索引
1）为经常作为检索条件或者排序的字段建立索引
2）不要建立过多的索引，减少磁盘占用
3）建立索引的字段数据量不宜过大，会导致每页的存储量减少，从而提高了树的高度，增加了磁盘的IO次数
4)最左前缀匹配原则，创建联合索引时，最常用到的字段放在左边
5）尽量选择区分度高的字段建立索引，否则对性能提交没有太大意义
6) 索引列不能参与计算，否则会导致不走索引
7)否定的一些查询，例如 !=. not like, is not null 会导致不走索引
ps: index 普通索引；unique 唯一索引；primary key 主键索引；
有的时候对应字段有索引但是查询却并没有使用索引，这个可能原因是因为优化器在做代价评估时认为使用索引的代价不如不用索引所以会放弃使用。
表分区
数据文件按分区条件拆分为小文件，就相当于大表拆成了小表，提高查询性能。
range范围查询：按一定范围分区，数字或者日期，例如partition p0 less than(10)
问题：若插入的数据不在任何范围内会报错。
解决：尽量选择可以覆盖整个范围的字段作为分区字段；使用存储过程或者其他方式及时增加分区；less than maxValue
list分区：按列表范围分区,例如 partition p1 values in (1,2,3)
问题：插入的数据不在任何分区范围内会报错
解决：使用过程或其他方式及时增加分区
hash分区：分散热点数据，使数据在各个分区尽量分布均匀，常规hash（取模）线性hash；hash分区只能支持整数，可以自定义分区函数
常规hash:partition by hash(store_id) partitions 4;在分区变动时，整个数据要重新分区，代价比较大\
线性hash:分区数据不大均衡
key分区: 类似hash分区，不支持自定义函数只能使用默认的函数，支持除blob和text之外的其他类型 partition by key(exp) partitions 4;
分区表的索引：如果定义的索引列和分区列不匹配，则会导致查询无法进行分区过滤。例如在列a上定义分区，在列b上定义索引，因为每个分区都有独立的索引，所以扫描索引时需要扫描每个分区。 应该避免建立和分区列不匹配的索引，除非查询中包含了可以过滤分区的条件。
mysql高可用
主从复制： mysql从库通过读取主库产生的sql操作日志,将在主库上执行过的sql在从库上执行一遍
