MySQL-进阶

一、MySQL体系结构
1.连接层：一些客户端和连接服务，完成连接处理、授权认证及相关操作
2.服务层：完成大多数核心服务的功能，比如SQL的分析和优化
3.引擎层：负责MySQL中数据的存储和提取
4.存储层：数据存储层，将数据存储在文件系统上，并完成与存储引擎的交互

二、存储引擎（MySQL的核心）
1.定义：存储数据、建立索引、更新和查询数据的实现方式 。
2.InnoDB：xxx.idb代表表名，最小单元为页（16kB）
（1）支持事务和ACID原则
（2）行级锁，提高并发访问性能、
（3）支持外键foreign key约束，保证数据的完整性和正确性
3.MyISAM
（1）不支持事务和外键
（2）只支持表锁，不支持行级锁
（3）访问速度快
4.Memory
（1）表数据存储在内存中作为缓存表或临时表
（2）hash索引

三、索引
1.定义：索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。无索引会执行全表扫描，表太大时性能会很低。
2.优点和缺点：大大提高了查询效率，但是也占用空间，降低更新表的速度。
3.种类：B+Tree索引、hash索引、R-Tree索引（空间索引）、全文索引
4.B+Tree索引（带有顺序指针的B+Tree）
（1）B Tree：多叉平衡查找树
（2）B+Tree：叶子结点形成有序链表，非叶子节点仅起到索引作用，具体数据都是在叶子节点存放的
5.hash索引
（1）hash碰撞用链表解决
（2）不支持范围查询和排序操作，查询效率很高

四、索引分类
1.主键索引（关键字primary，自动创建，只能有一个）、唯一索引（unique）、常规索引、全文索引（fulltext）
2.根据索引存储形式分为聚集索引、二级索引
（1）聚集索引的选取：默认为主键索引，否则为第一个唯一索引，都没有的话会自动生成一个rowid作为隐藏的聚集索引
（2）聚集索引的叶子节点下挂这这一行的数据；二级索引叶子结点下挂着该字段值对应的主键值
（3）查找过程：先根据字段在二级索引中找到主键值，再根据主键值到聚集索引中找到数据 （回表操作）
（4）语法：create [unique|fulltext] index idx_name ON table_name(index_col_name,...);

五、存储过程
1.定义：事先经过编译存储在数据库中的一段SQL语句的集合，其思想就是SQL语言层面的代码封装与复用
2.特点：封装与复用、可以接收和返回参数、减少网络交互并提高效率
3.语法

--创建
create procedure 存储过程名称（参数列表）
begin
  SQL语句    
end;  
--调用  
call 存储过程名称（参数）

六、锁
1.全局锁：对整个数据库加锁，一般用于做全库的逻辑备份
2.表级锁：每次锁住整张表，锁定力度大，发生锁冲突的概率最大，并发度最低
（1）表锁：表共享锁（读锁）、表排他锁（写锁） （读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞
其他客户端的写。）
加锁：lock tables 表名... read/write；释放锁：unlock tables
（2）元数据锁：为了避免DML和DDL冲突保证读写的正确性 （元数据是指表的结构，比如表名、列名、索引，增删改查时加元数据读锁，改变表的结构的时候加元数据写锁）
（3）意向锁：使在加表锁的时候不用检查每行数据是否加锁，减少表锁的检查。
（4）意向共享锁（IS）：与表锁共享锁兼容，与表锁排他锁互斥；意向排他锁（IX）：与表锁共享锁及排他锁都互斥，意向锁之间不会互斥
3.行级锁：每次锁住对应的行数据，锁定粒度小，锁冲突概率低，并发度高
共享锁（S）、排他锁（X）

七、解决慢查询：慢查询是指执行时间较长的SQL查询语句，可能导致响应时间延迟。
1.使用合适的索引
2.大表尽量避免全表扫描
3.使用覆盖索引（在二级索引的 B+Tree 就能查询到结果的过程就叫作“覆盖索引”，也就是只需要查一个 B+Tree 就能找到数据），少使用select *

八、索引失效的情况
·当我们使用左或者左右模糊匹配的时候，也就是like %xx或者like %xx%这两种方式都会造成索引失效;
·当我们在查询条件中对索引列使用函数，就会导致索引失效。
·当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
·联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
·在WHERE子句中，如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，那么索引会失效。