MySQL进阶

一、MySQL完整架构

1.连接层

        连接层负责进行连接处理，授权认证以及执行相关安全方案

2.服务层

        可以完成mysql的很多核心功能，在服务层进行sql分析,优化,各种逻辑的处理等.

3.引擎层

        引擎层是实际负责数据存储和提取操作. mysql提供了不同的引擎(处理方式),可以根据需要进行选择

4.物理文件存储层

        进行实际的文件存储, 包括存储数据的文件,还有各种日志文件

二、MySQL引擎

        mysql引擎负责进行数据的存储和提取操作，常用的引擎有InnoDB和MyISAM。

1.配置引擎

1.查看数据库支持的引擎：show engines
2.查看表引擎 show table staus like '表名'
3.配置引擎，默认表的引擎是InnoDB，
        在建表时可以手动配置引擎：create table 表名(...)engine = MyISAM;
4.修改引擎，alter table 表名 engine = InnoDB

2.InnoDB:

        InnoDB引擎有以下特点：支持事务（安全可靠）、支持行级锁（锁的粒度更高，效率更高）、支持外检约束、支持全文索引、支持数据缓存（提高数据的查询效率）、不存储数据的行数

3.MyISAM 

        myisam不支持事务,不支持外键,不支持行级锁, 支持表锁(并发量低), 适合写少,查询多的场景.支持全文索引, 存储表的总行数。

三、索引

1.什么是索引

        索引是一种有序的数据结构，可以帮助mysql快速查找数据。数据库在本身存储的数据外，还维护着一个满足特定算法的数据结构，这个数据结构中的元素代表着表中某个字段的数据，为这个字段实现一个快速查找算法，就可以通过该字段查找到该条数据的所有信息。

2.索引的优点/缺点

        优点：索引可以大大提升数据的查询速度，减少硬盘进行IO操作的成本。
        缺点：索引也是会占用硬盘空间的，当数据发生改变时，索引也是要改变的。（空间换时间）

3.索引的创建原则

1.哪些情况需要创建索引

        1.适合作为查询条件的字段，如电话、学号、身份证号等
        2.数据量较大的表
        3.经常用来进行排序和分组的字段

        表的主键自动会建立索引
        尽量使用联合索引，减少单列索引

2.哪些情况不需要创建索引

        1.不适合作为查询条件的索引，如重复量较大的字段（性别、年龄）
        2.数据量较小的表
        3.频繁修改的数据，该字段有数据被修改，那索引也要进行修改

4.索引的分类（如何添加索引）

1.主键索引
        当某列被设置为主键后，就会自动为其生成一个主键索引。
语法：
添加：alter table 表名 add primary key 表名(列名);
删除：alter table 表名 drop primary key

2.唯一索引
        在列上定义 unique约束时，MySQL 会自动为该列创建一个唯一索引。
语法：
添加：create unique index sh on city(name)
删除：drop index 索引名 on 表名

3.单值索引
        即只包含一个字段的索引
语法：
添加：create index 索引名 on 表名（列名）
删除：drop index 索引名 on 表名

4.组合索引(复合索引)
        即包含多个字段的索引，可以相对多个单值索引可以减低索引的开销
语法：
添加：create index 索引名 on 表名（列1，列2...）
删除：drop index 索引名 on 表名

        使用组合索引时，要遵从最左前缀原则，即在进行查询时，查询条件必须包含列1，否则组合索引不生效（即效率大打折扣）

create index zuhe on city (name,pid)
explain select * from city where name = '山西' and pid = '0'
explain select * from city where name = '山西' and id = '2'
explain select * from city where pid = '2' and id = '2'-- 未使用最左列

第三条查询组合索引未生效

5.前缀索引

        有的列中内容较长，直接拿来作为索引会对索引开销较大，可以选取其一部分内容作为索引。如新闻标题、摘要等字段。然后再使用模糊查询，会有不错的效率
语法：
添加：create index 索引名on 表名(列名(长度))
删除：drop index 索引名 on 表名

但是会存在索引失效的问题：

explain select * from city where name like '%中华人%' -- 索引会失效

模糊查询索引失效的情况：
        1.使用%开头的模糊查询
        2.使用%开头且%结尾的模糊查询
        3.使用函数或者表达式也会使索引失效

6.全文索引

        可以用来解决mysql中模糊查询索引失效的问题。

语法：
添加：create fulltext index 索引名 on 表名 (列名) with parser ngram 
查询：select * from 表名 where match(列名）against ('查询内容')

explain SELECT * FROM city WHERE MATCH(name) AGAINST ('中华人民') -- 索引生效

本次操作索引生效（key 等于 qianzhui）

5.索引数据结构

        索引结构使用的是B+树.首先b+树也是有序的,而且一个节点可以存储多个数据,非叶子节点只存储索引数据,所以每个节点可以存储更多的索引数据,数据存储在叶子节点,叶子节点直接还有指针指向,更方便范围查询 例如 id>2

6.聚簇索引和非聚簇索引

1.聚簇索引

        聚簇索引是指使用该索引进行查询时，只要找到内容对应的索引，就是找到了需要的值。mysql中InnoDB引擎中的主键索引就是聚簇索引, 因为数据和索引存储在一个文件中，主键索引是一级索引，直接与数据绑定。

        所谓的一级索引是直接绑定数据的索引，即主键索引，二级索引需要先查找到其对应的主键（一级索引）然后再查询数据。

2.非聚簇索引

        非聚簇索引就是在进行索引查询时，虽然找到了索引，但是并不能直接返回结果，而是需要回表查询，查找该索引对应的主键，然后再通过主键得到想要查找的结果。

        二级索引就是除了主键索引外的其他索引。

        在大多数情况下，主键索引既是聚簇索引也是一级索引，但是管理员也是可以将主键索引设置为非聚簇索引的。

7.回表查询

        回表查询就是一次查询不能查到我们需要的数据，需要再次进行查询。
        例如查询学生信息，id为主键索引，学号为唯一索引，二级索引。在InnoDB引擎下，我们使用学号来查询学生信息，在本次查询中，会先在学号的索引树上查询对应的学号，获取对应的主键id，然后再在主键索引树上查找对应的id，最后得到该学生的信息。

8.索引下推

        索引下推是一种sql优化技术，思想是在非聚簇索引的查询过程中，将有索引的条件下推到索引扫描阶段。例如：使用组合索引name,age来查询学生信息，正常来讲是先查找name，然后在此基础上再查找age，最后根据得到的主键来返回对应的值。使用了索引下推后就是在筛选阶段，同时将name和age作为筛选条件，减少了回表查询的次数，提高了效率。

四、数据库事务

        数据库事务就是对一次数据库操作过程的管理。保证在本次操作中的多条sql语句要么全都执行，要么出现了异常就全都不执行，保证数据的安全性。

1.事务的特征

1.原子性：保证一次任务中的多条sql语句在没有问题时都可以执行，一旦出现错误，数据回滚到执行之前。
2.隔离性：数据库为了提高并发性，提供了四种隔离机制。读，未提交。读，已提交。可重复读。串行化。
3.持久性：一旦数据被提交后，会保证数据会持久存储
4.一致性：是指在进行操作后，数据库中的数据和我们预期的一致，上述三个特征都是为了保证一致性。

2.事务隔离级别

SHOW GLOBAL/SESSION VARIABLES LIKE 'autocommit'; 查看是否自动提交
SET GLOBAL/ SESSION autocommit=0; 禁止自动提交
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务提交

1.读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 设置读未提交
        该隔离级别中，事务A可以读取到另一个事务B中未提交的数据。一旦读取后事物B对数据进行回滚，就会导致事务A读取到脏数据，该隔离模式也会导致不可重复读和幻读问题。
2.读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 设置读已提交
        该隔离级别中，事务A只能读取到事务B已提交的数据，从而避免了读取到脏数据，但是如果在事务B提交前读取一次，事务B提交后读取一次，会导致事务A两次读取到的数据不一致，因此未能解决不可重复读的问题。
3.可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 设置可重复读
        该隔离级别中，可以保证事务A对于每次的读取操作总是能读取到相同的结果，因此可以解决不可重复读的问题，而且可以解决没有for update的查询幻读问题。
4.串行化 
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
        该隔离级别中，可以解决上述所有问题，但是同时也是所有事物隔离级别级别中效率最差的。串行化通过加锁来保证事务的安全，对于读操作会添加共享锁（S锁），写操作会添加排他锁（X锁），即使读操作添加了for update也是可以解决幻读问题的。

3.事务的实现原理

1.持久性
        事务的持久性是通过redolog日志实现的，当事务被提交后会被redolog日志中留下备份，再向数据库中进行持久化，一旦期间服务断开，停电宕机，就会利用redolog中的备份对数据库中的数据进行恢复。
2.原子性
        事务的原子性是通过undolog日志实现的，当事务对数据库中的数据执行变更操作时，redolog日志中就会有一个相反的操作，一旦要对数据进行回滚，就会调用redolog中的相反操作进行回滚。
3.隔离性
MVCC（多版本并发控制）机制，是数据库中一种提高并发的机制，通过为每行数据生成多个版本来提高并发，减少锁的使用。
        每次事务对数据行进行操作后，都会在表中的两个隐式字段中分别记录当前操作者的id和上次数据行的地址。从而形成一个版本链，这个版本链正是基于undolog实现的，版本链中的旧版本的信息正是由undolog提供的。

读视图：读视图是从版本链上获得的一个快照，对于读已提交隔离级别，每次读取数据都会获得最新的读视图，因此每次都可以获得最新数据。对于可重复读，只会在第一次读取时获得读视图，后续都只在这一个读视图上获取数据。而读未提交并不会用到读视图，而是直接读取数据行的当前数据。
4.一致性
        由其他三个特性来保障

五、锁机制

按照锁的粒度来分
1.全局锁
        全局锁一旦添加会对整个数据库进行加锁，通常是在数据进行备份或迁移时，保持数据的不变。此时会禁止一切写、改、删操作，允许读操作。
2.表级锁
        表级锁是对单表加的锁，添加后多个事务不可同时对一张表中的数据进行修改，表级锁可以是共享锁也可以是排他锁，如果是排他锁，则连读操作也不可以多事务同时进行。
3.行级锁
        行级锁会锁定一行的数据，添加后多个事务不可同时对一张表中同一行的数据进行修改，同样的，行级锁也可以是共享锁也可以是排他锁。
4.间隙锁
        间隙锁是锁定行之间的间隙。对与范围操作的  id>1 and id<10  对此区间间隙进行加锁，则其他事务没法在这个(1,10)这个间隙范围里插入任何数。

按照锁的行为分:

排他锁
        新增,修改,删除操作时,默认加的就是排他锁,锁住操作的那行数据。查询语句如果执行时,需要添加排他锁,需要在查询语句末尾添加for update

共享锁
        是用于给查询语句添加的,如果事务1给行1添加了共享锁,那么其他事务只能给行1添加共享锁其他事务不能给行1添加排他锁

六、SQL优化

1.尽量避免select *，只查询自己需要的列。

2.使用varchar代替char
        varchar声明的大小是最大长度，具体长度会随实际数据大小而改变。而char声明的大小是固定的，如果实际数据大小较小，就会用空格补齐，浪费存储空间。

3.使用数值代替字符串类型
        因为数据引擎在比较时，对于字符串类型会将每个字符都进行比较，而整数类型只需要比较一次即可。

4.清空表数据时使用turncate table替代delete语句
        原因在于delete语句进行删除操作时，是对每一行数据逐行删除，每删除一行都会更新索引，释放空间并在在undolog生成日志。而turncate tale是个DDL语句，他会删除这张表，并重新创建一张相同的表，也会重置表中的自增列，因此该语句只会在undolog日志中生成一个日志。

5.避免索引失效
        使用模糊查询和函数以及in，notin，or等语句都会使索引失效

6.order by写法  
        先条件筛选,再分组。切勿先分组,再条件筛选

7.建立索引

8.减少表关联查询的数量

9.避免深度分页问题
        selelct  *  from  table  limit   1000000,10 
该语句查询一百万行后的十组数据，因为跳过太多数据，大大影响了响应速度，造成了深度分页问题。建议优化为select id,name FROM account where id > 100000 order by id limit 10;  先试用条件过滤，再进行取值。

10.使用explain关键字查看sql执行计划
         explain select * from test where a = 10

possible_keys : 查询中可以使用的索引
key：本次查询实际使用到的索引 (查看索引是否有效)