[大数据]Mysql使用及底层实现与一些使用问题

1、安装数据库（ubuntu）

直接apt-get安装数据：http://www.linuxboy.net/ubuntujc/140519.html

2、修改密码或操作sql文件

#修改密码
mysqladmin -u root -p password 123456789
#导出数据库中的sql文件
mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql
mysqldump -uroot -p123456789 --databases DIAN_TI >> DIAN_TI.sql
#将sql文件导入数据库中
source sql文件

3、中文乱码

Ubuntu20版本的mysql5.7的配置文件：my.conf这个文件应该是在/etc/mysql/mysql.conf.d/mysqld.cnf这个文件

• 在创建数据库时指定默认的编码格式为utf8

create database 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci;

• 修改已创建的数据库的编码

alter database 数据库表名 CHARACTER SET utf8 COLLATE utf8_general_ci;

• 修改表的默认编码格式，将表和字段的编码都更改为utf8编码格式

alter table 表名 character set utf8 COLLATE utf8_general_ci;

4、8版本导入5版本数据问题

Mysql8的sql文件导入到Mysql5中出现的问题

utf8mb4替换为utf8

utf8mb4_0900_ai_ci替换为utf8_general_ci

utf8_croatian_ci替换为utf8_general_ci

utf8mb4_general_ci替换为utf8_general_ci

5、JDBC连接

参考网址：https://www.runoob.com/java/java-mysql-connect.html

jdbc:mysql://127.0.0.1:3306/book?useSSL=true

package test;

import java.sql.*;

public class Main {

	public static void main(String[] args) throws Exception{
				Class.forName("com.mysql.jdbc.Driver");
				try(
					Connection conn = DriverManager.getConnection(
						"jdbc:mysql://114.55.26.230:3306/book?autoReconnect=true&useUnicode=true&useSSL=false"
						, "root" , "1314520ok");
					Statement stmt = conn.createStatement();
					ResultSet rs = stmt.executeQuery("select * from book_system"))
				{
					while(rs.next())
					{
						System.out.println(rs.getInt(1) + "\t"
							+ rs.getInt(2) + "\t"
							+ rs.getString(3) + "\t"
							+ rs.getString(4) + "\t"
							+ rs.getString(5));
					}
				}

	}

}

6、内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

参考：https://blog.csdn.net/plg17/article/details/78758593

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

内连接

#关键字
inner join on
#语句
select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

左连接（左外连接）

#关键字
left join on / left outer join on
#语句
select * from a_table a left join b_table b on a.a_id = b.b_id;

说明：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

如果要获取a_table中的阴影部分且不包括与b_table中的数据则可以使用

select * from a_table a left join b_table b on a.a_id = b.b_id WHERE a.id NOT IN (SELECT b_table.id FROM b_table);

右连接（右外连接）

#关键字
right join on / right outer join on
#语句
select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

全外连接

【注意】：Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ UNION+右外连接实现

#关键字
UNION
#语句
select * from a_table a left join b_table b on a.a_id = b.b_id
UNION
select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

7、索引

参考：https://www.bilibili.com/video/BV19y4y127h4?p=3&spm_id_from=pageDriver

素引的优点

• 大大加快数据查询速度

素引的缺点

• 维护素引需要耗费数据库资源
• 素引需要占用磁盘空间
• 当对表的数据进行增删改的时候,因为要维护索引,速度会受到影响

索引的分类

InnoDB

• 主键索引：设定为主键后数据库会自动連立素引，innodb为聚族索引，索引列值不能为空
• 单值（单例/普通）索引：一个索引只包含单个列，一个表可以有多个单列素引
• 唯一索引：索引列的值必须唯一，但允许有空值，允许为null但是只能有一个null
• 复合索引：一个索引包含多个列

MyISAM

• Full Text（全文索引）：MySQL5.7版本之前，只能用于MyISAM引擎，MySQL8也支持全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR、TEXT类型列上创建。

索引的基本操作

主键索引

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建表时自动进行创建。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

普通索引

两种方式进行创建：

给name列创建索引

1. 建表时创建（KEY关键字）（不可以指定索引的名称，名称默认为列的名称）

   CREATE TABLE t_user (
   	id INTEGER PRIMARY KEY,
   	name VARCHAR(20),
       KEY(name)
   );
   

2. 建表后创建（name_index索引名称，t_user为表名，name为哪个列创建索引）

   CREATE INDEX name_index ON t_user(name);
   

唯一索引

1. 建表时创建（UNIQUE关键字）（不可以指定索引的名称，名称默认为列的名称）

   CREATE TABLE t_user (
   	id INTEGER PRIMARY KEY,
   	name VARCHAR(20),
       UNIQUE(name)
   );
   

2. 建表后创建

   CREATE UNIQUE INDEX name_index ON t_user(name);
   

复合索引

1. 建表时创建

   CREATE TABLE t_user (
   	id INTEGER PRIMARY KEY,
   	name VARCHAR(20),
       age INTEGER
       KEY(name,age)
   );
   

2. 建表后创建

   CREATE INDEX name_index ON t_user(name,age);
   

左前缀原则

索引index1:(a,b,c)有三个字段，我们在使用sql语句来查询的时候，会发现很多情况下不按照我们想象的来走索引。

• 会走索引

  select * from table where a = '1'  
  select * from table where a = '1' and b = ‘2’ 
  select * from table where a = '1' and b = ‘2’  and c='3'
  

  select * from table where a = '1' and c= ‘2’
  -- a,c也走，但是只走a字段索引，不会走c字段
  

索引底层数据结构

B树（B-Tree）

每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B加树（B+Tree）

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针

1、B+Tree非叶子节点不存储data，只存储key；
2、所有的关键字全部存储在叶子节点上；
3、每个叶子节点含有一个指向相邻叶子节点的指针，带顺序访问指针的B+树提高了区间查找能力；
4、非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字；

为什么Mysql索引要用B+树不是B树？

? 用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部 加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

8、InnoDB与MyISAM

• 聚簇索引：将数据存储与索引放到了一块，素引结构的叶子节点保存了行数据（聚簇索引不一定是主键索引，主键索引一定是聚簇索引 ），一个表只能有一个聚簇索引，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

参考：https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html

MYISAM索引实现（非聚集）

所以MYISAM这个存储引擎他的查找的一个大致过程就是，先看条件字段有没有用到索引，是索引字段就先去到索引文件去查找这个索引所在的那一行的磁盘文件地址，就借助B+Tree的特点从根节点顺藤摸瓜找到磁盘文件地址指针，然后从MYD文件一次性定位到所找的数据，也就是说MYISAM会垮两个文件。

InnoDB索引实现（聚集）默认数据页大小为1 6kb

它也是一个B+Tree，但是它的叶子节点和MYISAM有点区别，它存储的是索引所在行的所有字段。

9、关系型与非关系型数据库区别

参考：https://blog.csdn.net/qq_33472765/article/details/81515251

非关系型数据库与关系型数据库各自的优势

非关系型数据库的优势：

1. 性能NOSQL是基于键值对的，nosql的存储格式是key,value形式、文档形式、图片形式等等，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。

2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：

1. 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。

2. 事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

关系型数据库的优势和劣势

关系型数据库把所有的数据都通过行和列的二元表现形式表示出来。

关系型数据库的优势：

1. 保持数据的一致性（事务处理）
2. 由于以标准化为前提，数据更新的开销很小（相同的字段基本上都只有一处）
3. 可以进行Join等复杂查询

关系型数据库的不足：

1. 大量数据的写入处理

2. 为有数据更新的表做索引或表结构（schema）变更

3. 字段不固定时应用

4. 对简单查询需要快速返回结果的处理

10、Mysql order by与limit混用陷阱

参考：https://blog.csdn.net/qiubabin/article/details/70135556

? 在Mysql中我们常常用order by来进行排序，使用limit来进行分页，当需要先排序后分页时我们往往使用类似的写法select * from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题。

比如现在有一张user表，表结构及数据如下：

现在想根据创建时间升序查询user表，并且分页查询，每页2条

select * from user order by create_time limit pageNo,2;

1、查询第一页数据时：

2、查询第四页数据时：

user表共有8条数据，有4页数据，但是实际查询过程中第一页与第四页竟然出现了相同的数据。

? 如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

基于这个我们就基本知道为什么分页会不准了，因为我们排序的字段是create_time，正好又有几个相同的值的行，在实际执行时返回结果对应的行的顺序是不确定的。对应上面的情况，第一页返回的name为8的数据行，可能正好排在前面，而第四页查询时name为8的数据行正好排在后面，所以第四页又出现了。

? 如果想在Limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

所以上面的情况下可以在SQL再添加个排序字段，比如fund_flow的id字段，这样分页的问题就解决了。

SELECT * FROM user ORDER BY create_time,id LIMIT 6,2;

11、SELECT 语句的执行过程

客户端

? 客户端的作用是访问Mysql 的server层,相信大家第一次接触到Mysql, 然后…就啥都没了. 客户端就是用界面来与Mysql做交互的.
常用链接Mysql server层的方式有两种 :

• 图形化界面工具:
  • NAVICAT 界面好看 功能强大, 但是收费
  • SQLYOG 刚开始学Mysql 的时候用的, 满足日常使用,收费!
  • DBEAVER 挺棒的, 公司就是用的好这个, 因为免费
• 命令行 (Linux/ Windows 都有)

连接器

在 JDBC 中获取的 Connection 连接对象。连接器的作用是通过TCP握手获取登录信息后验证权限, 以及连接的管理。

缓存

你执行一条SQL(查询)，如果你没有手动禁用缓存的话，Mysql会先到缓存中查询之前是否执行过这条SQL，有则视为命中缓存并且返回数据，没有命中则交由，分析器处理。
需要注意的是增 删 改 都会刷新Mysql的缓存，所以经常修改的的数据，不建议使用缓存。比如城市交通的线路，站名，城市名，省名，这些长期不变的数据 则建议使用缓存，提高效率。

MySQL 8.0 的版本中，已经不在支持查询缓存。

分析器

分析器的作用是对你执行的SQL的解析, 检查你的语法是否能正常被Mysql的执行器执行.
分析器又分为: 词法分析和语法分析

优化器

经历了分析器，那么 MySQL 就知道你要干啥了，那么怎么做？优化器完成了这一步，判断需要用哪个索引去查询、先执行哪个条件、或者决定 join 表的连接顺序等等，把前面的解析出来的换成执行的计划，进行最优评估。

执行器

执行器首先要做的是检查是否有权限访问这张表，然后再根据建表时的选用的储存引擎去调用该储存引擎的读写(IO)接口。

查询：有索引走索引, 无索引走全表 .命中一条就返回一条到结果集中. 返回的同时也会放到缓存中方便下一次使用。

12、Mysql四种存储引擎

InnoDB存储引擎：是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

MyISAM存储引擎：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

MEMORY存储引擎：MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

如何选择存储引擎？

• 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
• 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎。
• MySQL中使用该引擎作为临时表，存放查询的中间结果如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。