mysql进阶
2.存储引擎简介
a.存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型
3.存储引擎的特点
a.InnoDB:是一种兼顾高可靠性能的通用存储引擎，在mysql5.5之后就是mysql的默认存储引擎。
b.InnoDB的特点：
- DML（curd)操作遵循ACID模型，支持事务。
- 行级锁，提高并发访问效率
- 支持外键foreign key 外键约束，保证数据的完整性和正确性。
- 文件：xxx.ibd xxx代表表名，innoDB引擎的每张表都对应一个表空间文件，存储该表的表结构，数据，索引
- 逻辑存储结构如下
c.MyISAM:是mysql早期默认存储引擎
特点： - 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
文件 xxx.sdi ：存储表结构信息
xxx.MYD :存储数据
xxx.MYI :存储索引
d. Memory:Memory 引擎表数据存储在内存中，只能作为临时表或者缓存使用。
特点： - 内存存放
- hash索引（默认）
文件 xxx.sdi:存储表结构信息
e.三张表的比较
f . 存储引擎的选择
- InnoDB：是mysql的默认存储引擎，支持事务，外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了查询和插入外，还有很多的更新，删除操作，那么InnoDB就是比较适合的选择
- MyISAM：如果应用是以读取和插入为主的，只有少量的更新和删除操作，并且对事务的完整性，并发性要求不是很高，那么这个选择MyISAM就很合适。
- MEMORY: 将所有数据保存在内存中，访问速度快，通常用于临时表的以及缓存。MEMORY的缺点就是表不能太大，太大的表不能缓存在内存里，而且数据的安全性无法保障。
2.索引
B+Tree:以一颗最大度数为4阶的B+Tree为例
Hash索引：
哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表。如果两个以上的键值映射到相同的槽位上，就会产生Hash冲突，也称为Hash碰撞，可以通过链表解决。
Hash索引的特点：
a. Hash索引只能用于对等比较（=，in） ，不支持范围查询，（<>）
b .Hash索引无法利用索引进行排序。
c.查询效率高，通常一次检索就可以（不出现Hash碰撞），效率通常高于B+Tree索引
存储引擎支持
在mysql中，支持哈希索引的是Memory引擎，而InnoDB具有自适应hash功能，hash索引是存储引擎根据B+Tree在指定条件下自动构建的。
面试题：为什么InnoDB选择B+Tree作为索引结构？
答：相对于二叉树，层级更少，搜索效率高；对于B-Tree ,无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值对减少，指针减少，同样保存大量的数据，只能增加树的高度，导致性能降低；相对于Hash索引，它只支持等值匹配不支持范围匹配，还不支持排序。
3.索引分类
1.主键索引：针对表中主键创建的索引 ，自动创建，只能有一个，关键字primary
2.唯一索引：避免同一个表中数据列的值重复，可以多个，关键字，unique
3. 常规索引： 快速定位特定数据，可以多个
4.全文索引： 全文索引查找的是文本的关键字，而不是比较索引中的 值 ，可以多个 ,关键字，fulltext
5.根据索引的存储形式，又分为下面两种：
a.聚簇索引：将数据存储与索引放到一块，索引机构的叶子结点存储了行数据，必须有且只有一个
b.二级索引：将数据与索引分开，索引结构的叶子结点关联的是对应的主键，可以存在多个
6.聚簇索引的选取规则：
a. 如果存在主键，主键索引就是聚簇索引
b.如果不存在主键，那么将使用第一个unique索引作为聚簇索引
c. 如果不存在主键，或者没有合适的唯一索引，则InnoDB会自动生成一个揉我的作为隐藏的聚簇索引
面试题：INnoDB主键索引的B+Tree的高度为多少？以及存储的数据量大概是多少？
答：假设一行数据大小为1k,一页中可以存储16行。InnoDB的指针占用6个字节的空间，主键即使为bigint,占用8个字节。高度为2时，n*8 +(n+1) = 16* 1024 ，算出n约为1170,1171* 16 = 18736，
高度为3时，则多一层，1171*1171*16 = 21939856 ~ 两千万条数据
4.索引的操作语法
1.创建索引：create【unique|fulltext|】index index_name on table_name(col_name1,col_name2...);
2.查看索引：show index from table_name;
3.删除索引： drop index index_name on table_name;
5.sql性能分析
a.sql执行频率
通过如下指令，可以查看当前数据库的Insert，update,delete,select的访问频率：show global status like 'Com_______';
b.慢查询日志：
慢查询日志记录了所有执行时间超过指定参数的所有sql语句的日志。mysql默认慢查询日志没有开启，需要在mysql的配置文件中配置一下信息：
#开启mysql慢查询开关：
slow_query_log=1
#设置慢查询的阈值为5秒，超过该值视为慢查询，记录日志。
long_query_time=5
配置之后，慢查询日志文件位置：/var/lib/mysql/
c.profile详情
执行一些列的sql业务操作，然后通过以下指令查看sql的执行耗时：
#查看每一条sql的耗时基本情况
show profiles;
#查看指定query_id的sql各个阶段的耗时
show profile for query query_id;
#查看指定query_id 的CPU使用情况：
show profile cpu for query query_id;
d.explain 执行计划
explain 或者desc 获取mysql如何执行select 语句的信息，包括select语句执行过程中表如何连接和连接的顺序。
explain执行各个字段的意义：
a.id: select 查询的序列号，表示查询中执行select字句或者是操作表的顺序，id相同，执行顺序丛上到下，id不同，值越大，越先执行。·
b.select_type: 表示查询的类型，常见的有simple，简单查询，primary主查询，union union中第二或者之后的查询，subquery 子查询等
c. type: 表示连接的类型，性能由好到坏的连接类型为null,system.const,eq_ref,ref,range,index,all
d. possible_key: 可能用到的索引，一个或多个。
e.key :实际用到的索引
f.key_length:索引使用的长度，该值表示索引字段最大的可能长度，并非实际使用长度，在不损失精度的前提下，长度越短越好。
g.rows：mysql认为必须要执行查询的行数，预估值并非实际值。
h.filtered :表示返回结果的行数占读取行数的百分比，filtered的值越大越好。
6.索引的使用：(索引失效的情况）
1.最左前缀法则：如果索引了多列，联合索引，要遵循最左前缀法则，即查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效，后面的字段索引失效。要求是索引字段必须存在，和位置无关。例：where a= 1 b =2 and c = 3 ,index_c_b_a 。同样走索引。
2.范围查询：联合索引中，出现范围查询，（<>） 范围查询右侧的列索引失效。如何解决呢？尽量使用>= 或者<=。
3.不要在索引列上进行运算操作，索引将失效。
4.字符串类型字段使用时，不加引号，索引将失效。
5.模糊匹配：如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。
6.or连接的条件：只有or两侧都有索引才会走索引，不然索引都会失效
7.数据分布影响：如果mysql评估使用索引比全表更慢，则不适用索引。is null (is not null)同理，如果表中某个字段绝大多数都是null，那么is null条件就不会走索引，同理is not null
8.sql提示：是优化数据库的一个重要手段，就是在sql中加入一些提示来达到优化的目的
use index: explain select * from t_user use index(idx_user_pro) where profession = '软件工程';
ignore index: explain select * from t_user ignore index(idx_user_pro) where profession = '软件工程';
force index: explain select * from t_user force index(idx_user_pro) where profession = '软件工程';
9.覆盖索引：尽量使用覆盖索引，意思是查询使用了索引，并且需要返回的列，在该索引中已经全部找到，减少select *。
注意：explain中的Extra中的信息如果出现using index condition 表示使用了索引，但是需要回表查数据，如果出现using where using index 则表示需要的数据都在索引中找到，不需要回表
10.前缀索引：当字段是字符串时，有时候需要索引很长的字符串，这会让索引变的很大，查询的时候会浪费大量IO，影响查询效率，此时只讲字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高效率。
- 语法： create index idx_XXXX on table_name(col(n)); n代表长度，可以根据索引的选择性来决定，而选择性是指不重复的索引值。
10.单列索引和联合索引
- 单列索引： 一个索引只包含单个列
- 联合索引： 一个索引包含多个列
业务场景中：如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单个索引，单个索引可能会导致只走前一个索引，后一个失效。从而导致回表。
7.索引的设计原则
1.针对数据量大，且查询比较频繁 的表建立索引
2.针对于常作为查询条件，排序，分组的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率也越高
4.如果是字符串类型的字段，字段的长度越长，可以针对字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价也越大，会影响增删改的效率。
7.如果索引不能存储null值，请在创建时候用not null 约束它，当优化器知道每列时候会包含null值时，可以更好的确定那个索引的效率更高
8.SQ语句的优化
1.insert 优化
a.批量插入
b.手动提交事务
c.主键顺序插入
2.主键优化
a.数据组织方式：在INnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种方式称为索引组织表。
b.叶分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含2~n行数据，如果一行数据过多会溢出，根据主键排列。如果主键乱序插入会出现叶分裂现象导致性能下降。
c.页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录标记为删除并且它的空间允许被其他记录声明使用。当页中删除的记录达到Merge_Threshold（50%），InnoDB会开始寻找最靠近的页，看看前后是否可以将两个页合并以优化空间使用
d.主键设计原则：
- 满足业务需求的情况下，尽量降低主键长度
- 插入数据时，尽量选择顺序插入，选择使用Auto_increment自增主键
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
- 业务操作时，避免对主键的修改
3.order by 优化
a.Using filesort:通过表的索引或者全表扫描，读取满足的条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引值接返回排序结果的排序都叫FileSort排序。
b. Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index， 不需要额外排队，操作效率高、
c.order by优化：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
- 必须使用覆盖索引，回表的操作会导致排序使用filesort，效率较低
- 多字段排序，一个升序，一个降序，此时需要注意联合索引创建时的规则。
- 如果不可避免的出现filesort，大数据量排序时候，可以适当增加排序缓冲区大小sort_buffer_size(默认256k)
4.group by 优化
- 在分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也满足最左前缀法则
5.limit 分页查询的优化
问题：一个常见的场景就是limit 200000000,10,此时需要mysql排序200000000条记录，而仅仅只返回200000000 -200000010的记录，其他的全部丢弃，查询排序代价十分大。
优化思路：一般分页查询时，通过创建覆盖索引能够比较好的提升性能，可以通过覆盖索引加子查询的方式进行优化。
例：explain select * from tb_sku t,(select id from _tb_sku order by id limit 200000000,10) a where t.id = a.id
6.count优化
a.MyisAM 引擎把一个表的总数据量记录在了磁盘里，因此执行count（*）的时候直接返回这个数，效率高，但是不能包含where条件
b.InnoDB 执行count（*） 的时候需要把数据一条一条加载出来再累计计数。
优化思路，自己计数，比如缓存中维护一个key:value ，新增+1，删除-1
count的几种用法：
-count(id):InnoDB会遍历整张表，把每一行的主键id值都取出来，返回服务层。服务层拿到主键后，直接按照行进行累加。
- count(字段）：
没有not null 约束：InnoDB会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是不是为null，不为null则累加。
有 not null约束：直接累加。
- count(1) InnoDB 遍历整张表，但不取值，服务层对返回的每一行，放一个数字1进去，直接进行累加
- count(*) InnoDB不会把全部字段取出来，而是做了专门优化，不取值，服务层直接进行累加。
总结排序效率：count(字段）<count(id)<count(1)<count(*)，所以尽量使用count(*)
7.update优化
注意：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，大大降低并发性能；
9.锁
1.介绍：锁是计算机协调多个进程并发访问某一资源的机制，在数据库中，除传统的计算机资源的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的重要因素。从这个角度来说，锁对数据库而言显得尤为重要，也更加复杂。
2.锁的分类：
a.全局锁：锁定数据库的所有表
1.全局锁就是 对整个数据库进行加锁，加锁后整个实例处于只读状态，后续的dml的写语句，DDL语句，已经更新操作的事务提交语句都将会被阻塞。
2. 典型的应用场景就是做全库的备份，对所有表进行锁定，从而获得一致性的视图，保证数据库的完整性
3.操作实例：
- flush tables with read lock;(加全局锁）
- mysqldump -uroot -p123456 itcast > itcast.sql
- unlock tables;(释放锁）
b.表级锁： 每次操作锁住整张表
1.表级锁，每次操作锁住整张表。锁的力度大，发生锁冲突的概率最高，并发度最低。应用在MYIsAM，InnoDB，BDB等存储引擎中。
2.表级锁的分类：
- 表锁
1.表共享读锁（读锁：read lock）
读锁不会阻塞其他客户端的读操作，但是会阻塞写操作。
2.表独占写锁（写锁：write lock）
写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。
3.加锁：lock tables 表名。。。 read/write
4.释放锁： unlock tables /客户端断开连接
- 元数据锁（meta datalock,简称MDL）
MDL加锁的过程是系统自动控制的，无需显示加锁，在访问任何一张表的时候会自动加上。MDL锁的主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
在MYsql5.5中引入了MDL。当对一张表进行CRUD时，加MDL读锁，当对表结构修改时，加MDL写锁
- 意向锁
为了避免DML在执行的时候，加的行锁和表锁冲突，在INnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
a.意向共享锁（IS） ：由语句select ... lock in share mode添加；与表锁共享锁read兼容，与表锁排它锁write互斥
b.意向排他锁（IX）: 由insert，update,delete, select ... for update添加。与表锁共享锁read,以及排它锁都互斥。意向锁之间不会互斥
c.行级锁： 每次操作锁住对应的行数据
1.行级锁，每次操作锁住对应的行数据。锁粒度最小，发生锁冲突概率最低。并发度最高。应用在InnoDB引擎中。
2.InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对行记录加锁。对于行级锁，主要分为一下三类
- 行锁（record lock）：锁定单个行的数据，防止其他事务对此行进行update和delete，在RC，RR隔离级别下都支持。
a.共享锁（S) ：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
b.排他锁（X）:允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
DML语句的加锁情况
注意事项：
- 默认情况下，InnoDB在 RR隔离级别运行，InnoDB使用Next-key锁进行搜索和扫描，防止幻读。
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化成行锁。
- InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将会对表中的所有数据加锁，此时，行锁就会升级为表锁；
- 间隙锁（Gap lock）:锁定索引记录间隙，确保索引记录间的间隙不变，防止其他事务在这个间隙进行insert,产生幻读。 在RR隔离级别下都支持。
a.索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
b.索引上的等值匹配（普通索引）, 向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
c. 索引上范围查询（唯一索引）,会访问到不满足条件的第一个值为止
- 临键锁（Next-key lock）:行锁和间隙锁的组合。同时锁住数据和数据前面的间隙，在RR下支持。
10 . InnoDB引擎
1.逻辑存储结构
2.架构：InnoDB架构分为两个部分，一个为内存区，一个为磁盘结构
架构-后台线程
1.Master Thread :核心后台线程，负责调度其他线程，还负责将缓冲池中得数据异步刷新到磁盘，保持数据的一致性，还包括脏页的刷新，合并插入缓存，undo页的回收。
2.IO Thread ：在InnoDB引擎中大量使用了AIO来处理IO请求，这样可以极大的提高数据库的性能，而IO THread 主要负责这些IO请求的回调
3.Purge THread: 主要用于回收事务已经提交了的undo log,在事务提交之后，undo log 可能不用了，就用它来回收。
4.Page Cleaner Thread: 协助Master Thread 刷新脏页到磁盘的线程，它可以减轻MasterTHread 的工作压力，减少阻塞
3.事务原理
事务：事务是一组操作的集合，它是不可分割的工作单位，事务会吧所有的操作作为一个整体一起向系统提交或者撤销操作，这些操作要么同时成功，要么同时失败。
事务的特性：ACID
- 原子性： 事务是不可分割的最小操作单元，要么一起成功，要么一起失败
- 一致性： 事务执行后，数据库状态与其它业务规则保持一致。其他特性都是为了给一致性服务的. 例如买东西,张三买李四的东西, 买卖前和买卖后张三和李四的所有钱数之和是保持不变的.
- 隔离性： 事务和事务之间是隔离开的. 一个事务看不到另一个事务正在操作的数据(正在进行中的状态)
- 持久性： 一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制将数据恢复到提交后的状态。
redo log（保持事务的持久性）
- 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
- 该日志文件分为两个部分，重做日志缓冲和重做日志文件，前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。
undo log（保持事务的原子性）
- 回滚日志，用于记录数据被修改前的信息，作用有两个，提供回滚和MVCC（多版本并发控制）
- undo log 和 redo log记录的物理日志不一样，他是逻辑日志。可以认为当执行一条delete语句时，undo log中就会对应记录一条insert语句，反之亦然，当update一条记录时，他就记录一条相反的update语句，当执行rollback 时，就可以从undo log中的逻辑记录中读到相应的内容并回滚。
- undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undolog,因为这些日志可能还用于MVCC
- undo log 存储：undo log采用段的方式进行管理和记录，存在在前面说的rollback segment回滚段中，内部包含1024个undo log segment.
4.MVCC
1.当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select 。。lock in share mode,select ..for update,update..,insert,delete都是一种当前读
2.快照读：简单的select就是快照读，快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
- Read Committed :每次select ，都生成了一个快照读
- Repeatable Read: 开启 事务后第一个select语句才是快照读的地方
- Serializable:快照读会退化成当前读
3.MVCC：（Multi-Version Concurrency Control） :多版本并发控制。指维护一个数据的多个版本，使得读写的操作没有冲突，快照读为mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，依赖于数据库的三个隐式字段，undo log,readView
a.记录中的隐式字段
4.undo log:
-回滚日志，在insert，update，delete的时候产生的便于回滚数据的日志。
- 当insert的时候，产生的undo log,只在回滚时需要，在事务提交后可以立即删掉
- 当update,delete 的时候，产生的undo log日志不仅在回滚时需要，在快照读的时候也需要，不会被立即删除
5.undo log 版本链
-不同事务或者相同事务对同一条记录进行修改，会导致该记录的undo log 生成一条记录版本链，链表的头部是最新的额记录，链表尾部是最旧的记录。
6.readview:是快照读sql执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务未提交的id.readview包含四个核心字段：
- 版本链数据访问规则：