MySQL

1、mysql体系结构

 2、MySQL存储引擎的特点

MySQL在5.5之前默认用的是myISAM ，5.5以后用的是InnoDB

MyISM: 不支持外键、事务、行锁，只支持表锁。文件：xxx.fri表结构 xxx.MYI 表索引 xxx.MYD表数据

InnoDB: 支持外键、事务、行锁、表锁，使用的是b+树索引 文件：xxx.ibd 每张表都对应一个表空间，存储数组、索引、表结构。

Memory: 默认使用Hash索引，引擎的数据存储在内存中，读写快，但是会受到硬件、断电影响，因此只能将这些表做缓存或临时用。

区别和特点：

 引擎的选择：

InnoDB： 是mysql默认引擎，如果对事物的完整性要求比较高，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包含很多的更新删除操作，选择这

MyISAM：如果以读和写操作为主，对数据完整性、并发性要求不高

Memory:访问速度快，用于临时表和缓存。而且对表的大小有限制。

3、索引

索引是一种 帮助mysql快速查找数据的数据结构。mysql的索引是引擎层实现的，因此不同的存储引擎有不同的索引结构。

各种索引：

B+索引：最常见的索引，大部分都支持。

Hash索引：Memory存储引擎支持，InnoDB不支持，但是有自适应hash.。底层是哈希表，只有精确匹配索引列才有效果。

R-tree索引： 空间索引，是myISAM的一种特殊索引类型，用于地理空间数据类型

为什么选择B+树？

索引选择上有二叉树、B-tree、B+tree Hash。

二叉树，如果是顺序插入，会造成单链表，就是我们的单脚，大数据量层级较深，查询效率慢。

红黑树：又名平衡二叉树，会通过左旋右旋维持平衡，单脚效应没有了，但是会只有两叉，大数据量下，层级还是深，查找数据慢。

B-tree：又名多叉路查找树，对比比二叉树，每个节点有多个叉，以一个5阶（度）树为例，每个加点有4个键以及5个指针。但同时，每个节点会存在数据，造成每个节点上的指针存储不多，并且，排序数据的时候会是波浪形，效率慢。

 

 B+树：是B树的变种

 B树与B+树的对比：

1、B+树所有的数据都在叶子节点上

2、叶与叶之间用单项链表相连

3、非叶子节点仅仅起到索引数据的作用，具体的数据都在叶子上。

索引的分类：

1、主键索引： 有且仅有一个，自动创建 针对于主键，关键字 primary;

2、唯一索引：可以有多个，避免列的值重复，关键字unique

3、普通索引： 可以有多个，用于检索

4、全文索引：可以多个，用于查找关键 词，关键字fulltext

聚集索引&二级索引：

在innoDB按索引的存储形式又分聚集和二级。

聚集索引：数据和索引存在一起，索引结构中的叶子节点存储的是数据，表中必须有且只有一个。

二级索引：数据和索引分开存储，叶子节点存储的是主键，表中可以没有。

聚集索引的选取规则：

1、如果存在主键，则主键索引就是聚集索引

2、不存在主键，则第一个唯一索引就是聚集索引

3、不存在唯一索引，则InnoDB会根据隐藏字段ROW_ID生成聚集索引

索引语法：

1、创建索引： create  [unique | fulltext] index index_name on table_name (index_col_name+

2、查看索引：show index from table_name;

3、删除索引： drop index index_name on table_name; 

Hash索引：

结构：hash索引就是将键值采用一定的hash算法得出hash值，再映射到对应的槽位，存储到哈希表中。

 特点：

1、只能用于等值查询（in,=），不能用范围查询 between > <

2、无法利用索引进行排序操作

3、如果是等值查询，不发生hash碰撞的情况下，只需要查询一次即可，比B+树查询效率高。

SQL性能分析：

1、查询SQL执行频率

show global status like "Colum_select/update/insert/delete "可以查询该数据库是以查询还是cud为主。

2、慢查询日志

可以在my.ini配置文件中修改开启慢查询日志，并设置慢查询的时间。

 3、show profiles查看每条sql语句的耗时基本情况

 4、explain分析慢查询sql语句

例子：explain select * from tb_user where id =1; 

 索引使用：

联合索引建立：create index index_usr_profession_age_status on tb_user(profession,age,status)

最左前缀原则：联合索引情况下，最左边的字段必须存在，否则后面的索引不生效，如果中间跳过索引，则后面的索引不生效。

例子：查询语句： select * from tb_user where profession="软件" and status=1;则profession的索引了生效，status的索引不生效。

范围查询：联合索引情况下，如果出现> <则右边的索引失效，>= <=不会出现索引失效的情况。

索引失效：

1、索引列有运算，如subString

2、索引字段类型为varchar,但查询语句没有加“”号，隐式转换

3、模糊查询，"%_"、"%_%"在前索引失效

4、or连接条件，只要有一个字段没有索引，则全部索引不生效。

5、数据分布影响，假设表中status null值很多，非null很少，查询status=null值得情况，则会全文检索，因为MySQL引擎会判断走索引还是全文检索速率快，选择其中快的进行查询。

SQL提示:假设字段建立了单列索引以及联合索引同时存在，msql会选择其中一种，那只时候我们可以给sql提示，让他选择我们指定的一种。

覆盖索引：查询中尽量别使用select *。而使用返回指定字段。覆盖索引就是查询使用了索引，需要返回的内容在索引中可以全部找到

前缀索引：当字段类型为text/varchar/longtext等，有时需要索引很长的字符创，这会让索引变得很大，而查询时浪费大量的磁盘IO,影响查询效率。此时可以将字符串的一部分前缀，建立索引，大大节约索引空间。

前缀长度： 可以根据索引的选择性来决定，而选择性是这不重复的索引和数据表的记录总数的比值。

唯一索引的选择性格最大，为1，也是最好的索引选择，性能最好。

单列索引和联合索引：单列：给一个列建立索引；联合：给多个字段建立索引。

回表查询：第一搜会经二级索引，而二级索引叶子节点存储的是主键值，才是要想拿到返回的数据，会再拿主键值去簇拥索引查找。

避免回表查询：将要返回的字段类型建立联合索引。

SQL优化

插入数据： 多个插入时，可以使用values(值),(值)的方式，而不是每个插入一条insert语句，减少提交语句的开销。

大数据量情况下，比如一百万条，可以使用load data local infile '文件路径'；

插入时：顺序插入，避免页分裂。

主键优化：

1、数据的组织形式：表数据根据索引的主键的顺序组织存放，这叫做索引组织表。

2、叶子中挂的是行数据，而数据是以页16KB的形式加载到内存中的。页与页之间通过链表连接，如果在页之间插入数据，则会触发页分裂

页合并：

order by 优化： 

explain执行计划有Extra字段，通过group by查询 有两个值 using filesort/using index

using filesort: 通过表的索引和全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序，所有不是通过索引直接返回排序结果的排序都是file sort 排序；

using index: 通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作数据高。

group by优化：

在分组操作时，可以利用索引提高效率，同时分组操作，索引也满足最左前缀。

limit优化：

深度分页下，可以使用覆盖索引加子查询提高效率。

count优化：

效率由高到低：

count(*)> count(1)>count(Primary key)> count(普通key)，其中count(*) count(1) 都会统计null值，count(普通key)不会统计null值

锁 

mysql中根据锁的粒度，有以下三类、

1、全局锁 锁住数据库中所有的表

2、表级锁 锁住单张表

3、行级锁 锁住一行

1、全局锁

就是对整个数据库实例进行加锁，然后处于只读的状态，DDM DML操作均不能。

应用场景： 全库的逻辑备份，对所有的表进行备份，获取一致性试图，保证数据的完整性。
2、表级锁：

每次操作锁住整张表，锁定的粒度大，并发低，锁冲突最高

表级锁分以下三类

• 表锁
• 元数据锁
• 意向锁

表锁：分表共享读锁（read lock ）和 表独占写锁(write lock)

语法：加锁：lock tables 表名 、、、read/write

 释放锁： unlock tables /客户端断开。

读锁：阻塞另一客户端的写，不阻塞读。

写锁：阻塞另一客户端的读和写。

元数据锁：锁住的是表结构，防止DML与DDL冲突，避免在执行DML过程中表结构发生改变。系统自动控制，无序显示调用

意向锁：避免行锁和表锁冲突，使表锁不用检查每行数据是否加了行锁，减少表锁的检查；

在没有意向锁的之前：要加表锁需要从第一行遍历到最后一行检查是否存在行锁，而有了元数据锁，在加了行锁之后，表中也会加意向锁，那加表锁的时候就不用检查，如果有意向锁，则不加表锁。

 1、意向读锁，和表读锁兼容和表写锁互斥

2、意向写锁，和表读锁以及表写锁都互斥。

3、行级锁

每次锁住对应操作的行数据，锁定粒度最小，锁冲突最低，并发最高。

InnoDB的数据是根据索引组织的，行锁是对索引上的索引来加锁而不是记录

分以下三类；

行锁、间隙锁、临建锁。

行锁：锁住当个行记录，防止其他事务对其delete update，RC  、RR级别支持

间隙锁：锁住的是数据的前后间隙，防止其他事务在前后间隙insert,造成幻读 RR级别支持

临建锁： 是行锁和间隙锁的结合

行锁：

• 普通的select 语句不会加锁
• select ... lock in share mode 加共享锁，共享锁与共享锁间互斥。
• 共享锁与排它锁互斥
• 排它锁与共享锁、排他锁互斥 
• 查询字段无索引升级为表级锁

6、InnoDB执行引擎

InnoDB的逻辑存储结构：表空间 --段-区--页--行

架构：

 

 内存结构分：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer

Buffer Pool：由于内存处理效率和磁盘io效率不一致，为了弥补两者之间的IO效率差，而存在，

不仅缓存索引页、数据页。还包含undo页、插入缓存、自适应hash。以页为单位加载，底层采用链表连接。

Change Buffer:对于非唯一二级索引，在执行DML会把数据加载到这里修改，在未来读取数据时再将其合并到buffer pool。好处：因为插入的顺序会影响到不同的页，如果每次都操作磁盘，则会降低效率，有了ChangBuffer可以在此进行合并，降低磁盘IO次数

事务的原理

什么是事务：是一组不可分的操作集合，提交事务，会把所有作为一个整体去提交。要么同时成功，要么同时失败。

事务特性：

原子性Atomicity：事务不可分，要么同时成功，要么同时失败。由Redolog日志保证

一致性Consistency:事务从一种正确状态转换为另一种正确状态。如AB互相转账，无论怎么操作，AB账户合起来的金额不变。由undolog和redolog日志保证

隔离性Isolation:指一个事务的操作，不受另一个事务或者说外部并发环境的影响。由MVCC+锁保证。

持久性Durable:事务提交后对数据库的影响是持久性的。由RedoLog保证。

MVCC:多版本并发控制

当前读：指读取到数据的最新状态，读取时，对读取的数据加锁，保证不被修改。

快照读：指读取到数据的可见版本，有可能是历史数据。

• RC级别：每次select都生成快照读
• RR级别：第一次生成快照读，后面的都使用其快照读
• Searlize级别：当前读。

MVCC实现：依赖三个，一是表的三个隐藏字段，最近修改的事务id,回滚指针(指向记录上一版本)，隐藏id（若没有主键才会生成）。二是，undolog版本链。三是，readview视图。