MySQL和MySQL高级

MySQL

1.数据源

1.Druid(德鲁伊)

1.创建配置文件(xxx.properties)

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/数据库名
username=root
password=root
initialSize=5
maxActive=10
maxWait=1000

2.使用

1.导入jar包：druid-1.1.10.jar
2.方法：
    //集合
    Properties properties = new Properties();
	/**
		Class.getResourcesAsSteam("xxx.properties")
		默认读取的是包内资源，使用"/"则读取classpath内文件
		Class.getlassLoder().getResourcesAsSteam("xxx.proeperties")
		默认读取的是classpath内文件
	*/
    InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("配置文件");
   	//将配置文件内容读取到集合种
	properties.load(in);
	//获取数据源,createDataSource("properties集合")
	dataSource = DruidDataSourceFactory.createDataSource(properties);
	//获取连接，自动连接不需要参数
    dataSource.getConnection()
    

2.DButils

1.导入jar包（commons-dbutils-1.6.jar）

2.使用

1.构造方法
	new QueryRunner()->无参构造
    new QueryRunner(DataSources)->使用数据源，自动管理
2.方法
    //有参构造使用，参数列表1.String sql，2.查询结果为一个BeanHandler()<>,3.占位符Object...param
    								//	查询结果为多个BeanListHandler()<>
    //无参构造使用，参数列表1.Connection connection,2...和有参一致
    query()
    //有参构造使用，参数列表1.String sql,2.占位符Object...param
    //无参构造使用，参数列表1.Connection connection,2.String sql,3.占位符Object...param
    update()
    

3.常用Sql语句

1.增加数据

1.添加一条
	insert into 表名 values(值);
2.添加多条
	insert into 表名 values(值),(值);

2.删除数据

	delete from 表名 where 条件

3.修改数据

	update 表名 set 字段名=新值 where 条件

4.查询数据

1.常规查询
	select * from 表名
2.排序查询
	select * from 表名 order by 排序字段 ASC|DESC (ASC为升序，DESC为降序)
3.分组查询
	select * from 表名 group by 分组列 having 条件
4.分页查询
	select * from 表名 limit m,n (m从那条数据开始，起始为0，n为显示几条数据)
5.内连接查询
	select * from 表A，表B where 双表联系条件
	select * from 表A inner join 表B on 条件
6.外连接查询
	select * from 表A left join 表B on 条件
	select * from 表A right join 表B on 条件
7.子查询
	select * from 表A where 条件(=,!=,>=,<=) (表B查询结果)

MySQL高级

1.MySQL执行流程

核心部件:连接层、SQL接口、解析器、查询优化器
客户端：SQLyog、JDBC等

执行流程：客户端使用TCP(3次握手)进行连接，连接成功，则使用线程池，选择一个线程，然后SQL接口接受客户端SQL命令，解析器对SQL进行分析，词法分析、语法分析、语义分析，并为其创建语法树，最后使用查询优化器进行对不改变执行结果同时对SQL进行优化，最后进行查询执行引擎。
注：如果MySQL8之前会有一个缓存，在第一次查询时候先去缓存进行查询，如果命中则返回，未命中则和上述一致，最后查询结果进行缓存，缓存只有两次进行相同的查询生效，两次之间有一次增删改就会失效

2.MySQL存储引擎

InnoDB:默认的存储引擎，支持事物，行级锁定，支持聚簇索引，善于并发写、事务、更大更复杂的资源操作
MyISAM:不支持事物、外键，表锁，常用于并发量少，后台用户查询
BLACkHOLE：任何写入到此引擎的数据均会被丢弃掉，不做实际存储，但是开启了二进制日志，常用于集群的从集，获取执行的SQL语句
CSV：会把数据每行逗号隔开，每条回车分开，可以使用文本、表格工具进行打开，方便大量数据写入

3.索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）
索引可以高效的进行查询，但是索引存储是在磁盘上面，当数据量过于庞大，也占用相当大的空间，索引只是提高了查询效率，因为每次添加或修改数据后，会重新进行排序，影响效率

索引结构：
B树:平衡多路树，每个节点可以存放多个值
Hash索引：Hash索引使用哈希函数将索引列的值映射为一个固定长度的哈希码，并将哈希码作为索引的键值。Hash索引适用于等值查询，可以快速定位到目标数据
R树：R树是一种用于处理多维数据的索引结构，常用于地理信息系统（GIS）和空间数据库中
Full-text全文索引：文索引也是MyISAM的一个特殊索引类型，主要用于全文索引

创建索引的原因：
1.数据量很大
2.尽量创建符合索引，查询时候可以命中多个效率高
3.如果一个字段较长可以使用前缀索引
注：不能添加过多索引，会影响查询效率

1.B树和B+树

BTree又叫多路平衡搜索树，存放着 数据本身，元素索引、下一条记录如果数据过于庞大，则每个节点都存放这数据特别消耗空间，当数据过多，树的深度会很高，这时候IO次数就多，查询效率就会比较低。

MySQL使用的是B+Tree  存放着记录的类型，吓一条记录，各列的值，数据只存放在叶子节点上面
数据会找到最大元素和最小元素，进行排序，然后进行分页，分页是最小元素key和页码，然后双向链表进行连接，这样深度就会变低，存放数据更多，查询效率就会变高。
目录也成为索引
记录的值：普通0，目录1，最小2，最大3

B+树和B树的差异：

1、B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中， 非叶子节点既保存索引，也保存数据记录 。
2、B+树中所有关键字主键都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。
3、B+树中非叶子节点的关键字也会同时存在于子节点中，并且是在子节点中所有关键字的最小值。

2.MySQL索引类型

聚簇索引：
	聚簇索引对于主键的排序查找和范围查找速度非常快 ，插入速度严重依赖于插入顺序，更新主键代价非常高
1、只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
2、由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
3、如果没有为表定义主键，InnoDB会选择非空且唯一索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
4、为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

非聚簇索引：
	二级索引、辅助索引聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。
非聚簇索引叶子存放的不是完整的用户数据
如果查询一个值在非聚簇没有，然后需要再从聚簇索引查询这样叫做回表，所以我们查询数据时候不建议用 select * 。
联合索引：
	我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层c字段
覆盖索引：
	如果能通过读取索引就可以得到想要的数据，那就不需要读取用户记录，或者不用再做回表操作了。一个索引包含了满足查询结果的所有数据就叫做覆盖索引。例如，如果为前面例子中的数据库表创建c2列索引，则如下SQL会使用覆盖索引

3.索引操作

创建索引
CREATE TABLE customer (
    
  id INT UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
    
  PRIMARY KEY(id), -- 主键索引：列设定为主键后会自动建立索引，唯一且不能为空。
  UNIQUE INDEX uk_no (customer_no), -- 唯一索引：索引列值必须唯一，允许有NULL值，且NULL可能会出现多次。
  KEY idx_name (customer_name), -- 普通索引：既不是主键，列值也不需要唯一，单纯的为了提高查询速度而创建。
  KEY idx_no_name (customer_no,customer_name) -- 复合索引：即一个索引包含多个列。
);

-- 建表后创建索引
ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主键索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
CREATE INDEX idx_name ON customer1(customer_name);  -- 普通索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

ALTER TABLE customer1 MODIFY id INT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY customer1(id); --创建自增的主键索引

查看索引
SHOW INDEX FROM customer;
删除索引
DROP INDEX idx_name ON customer; -- 删除单值、唯一、复合索引

ALTER TABLE customer MODIFY id INT UNSIGNED, DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
ALTER TABLE customer1 DROP PRIMARY KEY;  -- 删除主键索引(没有主键自增)

4.索引失效

1.使用%like
2.使用函数
3.需要按照最左选择，不能跳跃索引
4.范围比较后面是不能使用索引的
5.类型转换，如‘123’转换123

4.MySQL优化

1.如何定位慢SQL
可以用流量监控软件skywaking，上面现实每条SQL的执行时间，会从慢到快自动排序，或者可以使用MySQL系统配置文件里面的日志，开启慢日志功能，可以查看超过多少秒的的SQL语句
2.MySQL超大分页如何处理
如：select * from 表 limit 100000,10
原因：MySQL会自动排序，然后我们只要十条数据，效率太低了，而且还会回表查询
解决：select * from 表 where id = (
select id from 表 order by id limit 100000,10
)
先排序好id，然后再根据这个id进行查询
3.如何做SQL优化
可以在建表时候使用索引，SQL语句编写，主从复制，读写分离，如果数据量较大也可以考虑分库分表
具体优化：
1.可以在创建表的时候选择适合存储类型的数据类型
2.进行避免使用select * 容易产生回表
3.尽量避免索引失效的写法
4.复合查询适合尽量用union all代替union，因为union会多一次去重，浪费时间
5.可以使用inner join最好使用inner join，因为inner join会自动选择小表为驱动表，如果必须使用left join或者right join最好可以将小表在前面。

5.日志

1.事物

ACID
原子性：要么都成功，要么都失败
一致性：操作数据库前和操作数据库后要完整，比如转账A扣了1000，转账给B，B就必须要加1000
隔离性：多线程存在,一个事物对另一个事物的影响
持久性：事物提交后就永久保存下来
隔离级别存在的问题
 脏读：一个事物读取到另一个并行的未提交的事物
 不可重复读：一个事物读取之前操作的事物，发现被另外一个事物做了修改，在同一个事物中两次查询结果不一致
 幻读：在前后两个时间段内执⾏对同⼀个数据项的读取，可能出现不⼀致的结果
解决方案：
  读未提交(READ_UNCOMMITTED)： 以上三个问题，一般不用
  读已提交(READ_COMMITTED)：只能读取到已提交的数据，避免了脏读
  可重复读(REPEATABLE_READ)：一个事物内两次查询保障结果一致，避免了脏读和不可重复读
  串行化(SERIALIZABLE)：完全禁止了并发，只允许一个事务执行完毕之后才能执行另一个事务
  
注意：
1、事务的隔离性由锁机制实现。
2、而事务的原子性、一致性和持久性由事务的 redo日志和undo日志来保证。
redo log 持久性，原子性、一致性 undo log

2.日志

慢查询日志：
	用于定位慢SQL，可以设置超越多少时间的SQL进行记录到日志，以便于做优化。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log的存储：
	undo log采用段的方式进行管理和记录，存放在rollback segment回滚段中，内部包含1024个undo log segment。
undo log的删除:
	1、针对于insert undo log 因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge(清理)操作。 
	2、针对于update undo log 该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。
undo log记录：
	插入：记录主键值，回滚删除这个主键
	删除时候：记录内容，回滚时候插入这个内容
	修改：旧值前后都记录下来
	
注：
redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以 一定策略将redo log buffer刷入到真正的redo log file 中。这里的策略这就是我们要说的刷盘策略

设置为0 ：表示每次事务提交时不进行刷盘操作【不往文件系统中写】。（系统默认master thread每隔1s进行一次重做日志的同步）。性能最佳、数据风险较高
设置为1：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）。数据安全性较高、性能稍差
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。性能较高数据安全性较高。在发生数据库故障时，可能会丢失最近提交的事务的数据，因为尚未刷新到磁盘上的日志文件中

6.锁

用来实现事物的隔离性
锁的分类：
	世界观：乐观锁和悲观锁
	范围：行锁和表锁
	操作：读锁和写锁
并发产生的问题的情况：
	1.读读，并发读取相同的记录，允许此操作
	2.写写，并发时候一个一个事物未提交，另一个事物进行操作，会产生脏写，覆盖等,可以加锁
	3.读写，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读、不可重复读、幻读的问题，可以使用MVCC
注意：InnDB在增删改时候索引未命中会自动由行锁升级为表锁，InnDB,读不会加锁
	读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写

7.MVCC

MVCC：多版本并发控制
	读操作利用多版本并发控制，写操作进行加锁
结构：
	1.隐藏字段:trx_id + roll_pointer
	在MySQL中给每个表都设置了隐藏字段，有一个是trx_id(事务id)，记录每一次操作的事务id，是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
	2.undo log日志
	undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
	3.ReadView主要来帮我们解决可见性的问题的
	ReadView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是读已提交隔离级别，每一次执行快照读时生成ReadView，如果是可重复读隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用
ReadView结构：
	1.creator_trx_id,创建这个 ReadView的事务ID  
	2.m_ids,生成ReadView时有哪些事务在执行但是还没提交的（称为 ”活跃事务“），这些活跃事务的id就存在这个字段里
	3.min_trx_id:m_ids里最小的值
	4.max_trx_id:生成 ReadView时InnoDB将分配给下一个事务的ID的值（事务 ID 是递增分配的，越后面申请的事务 ID 越大）max_trx_id是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID

8.分库分表

1.MySQL主从同步原理

1.主机会在变更时候把变更操作记录到二进制日志Binlog
2.从机会开启一个线程IO用来读取主机的Binlog，写道从机RelayLog
3.从机会开启一个线程SQL，然后用于将日志里数据写入到从机里面
注：主从复制同步是从搭建开始同步，并不是主机所有内容都同步

2.分库分表

1.垂直分片：
	微服务项目开发常用拆分策略，将每个服务对应操作分成对应的数据库进行操作
2.水平分片：
	将一个数据库按照规则进行水平拆分，如分个3个库，可以选择对应的差异化较大字段如用户id，或者订单id，分组id等进行对3取模，或者哈希运算，均匀分散到表里
	如果存在多个表关联操作，关联表需要在每个分片的记录关联的表
作用：
    水平分库，将一个库的数据拆分到多个库中，拒绝海量数据的存储个高并发的问题
    水平分表，解决单表存储和性能的问题
    垂直分库，根据业务逻辑进行拆分，高并发下提高磁盘IO和网络连接数
    垂直分表，冷热数据分离，多表互不影响，一般将表中较大字段，如text等，单独分出来一个表
使用ShardingSphere-JDBC进行分库分表，配置文件描述进行合理的规则匹配，然后在操作时候会自动根据规则进行操作，
Application.yml配置
# 配置 DataSource Driver
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
# 指定 YAML 配置文件
spring.datasource.url=jdbc:shardingsphere:classpath:shardingsphere.yaml
shardingsphere.yaml配置可以根据相应分库分表进行配置