MySQL基础知识
SQL语言
SQL（Structured Query Language），结构化查询语言
非过程语言
每一条SQL语句都是独立执行的，没有先后依赖关系
官方提供数据库标准语言
任何数据库都必须支持SQL语法
由于SQL的非过程性的特点，因此功能不够强大，很多数据库的内部都对SQL语言进行了加强，并且都提供了过程性的特征，可以利用其编程。
SQL分类
SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能：
- DDL （数据定义语言）：定义、修改、删除 数据库内部结构
数据定义语言 - Data Definition Language
用来定义数据库的对象，如数据表、视图、索引等
例如 ： 对数据库、数据表定义、修改、删除 以上操作 - DML （数据操纵语言）：对数据表记录 增加、删除、修改
数据处理语言 - Data Manipulation Language
在数据库表中更新，增加和删除记录
例如 update， insert， delete - DCL （数据控制语言）：用户授权、if 、while、事务管理
数据控制语言 – Data Control Language
指用于设置用户权限和控制事务语句
例如grant，revoke，if…else，while，begin transaction - DQL （数据查询语言）：select查询语句
数据查询语言 – Data Query Language
例如select
MySQL操作
预编译
通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：
- 词法和语义解析
- 优化sql语句，制定执行计划
- 执行并返回结果
具体过程可能很长，如下：
我们把这种普通语句称作Immediate Statements。
但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。
如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。
所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements
预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。
使用**
PREPARE ins FROM 'INSERT INTO test_demo SELECT ?,?,?';SET @a=null,@b='pp',@c='';EXECUTE ins using @a,@b,@c;SELECT * FROM test_demo;
结果
Statement和Prestatement的区别**
1.PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程
2.使用 Statement 对象。在对数据库只执行一次性存取的时侯，用 Statement对象进行处理。PreparedStatement 对象的开销比Statement大，对于一次性操作并不会带来额外的好处。
3.statement每次执行sql语句，相关数据库都要执行sql语句的编译，preparedstatement是预编译得,preparedstatement支持批处理
数据库count(*)和count(1)的区别
sql调优，主要是考虑降低：consistent gets和physical reads的数量。
一般情况下select count() 与select count(1)两者返回的结果一致。
但是针对表中主键的有无，情况会有不同。
无主键：count(1)比count()快
有主键：count(主键)最快
count()与count(1)都包括null统计，而count(column)不包括null统计
count() count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(),sql会完成优化的
使用
：其中num列有一个Null值
SELECT COUNT(*)as a,COUNT(1)as b,COUNT(id)as c,COUNT(num)as d
FROM `test_demo`;
结果
count详解count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)：
count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
distinct 列名,得到的结果将是除去值为null和重复数据后的结果
dstinct 列名使用
SELECT COUNT(*)as a,COUNT(1)as b,COUNT(id)as c,COUNT(DISTINCT num)as d
FROM `test_demo`;
dstinct 列名结果
count(主键) 不一定比count(其余索引) 快：
索引是一种B+树的结构，以块为单位进行存储。假设块大小是1K，主键索引大小为4B，有一个字段A的索引大小为2B。
同样一个块，能存放256个主键索引，但是能存放512个字段A的索引。
假设总数据是2K条，意味着主键索引占用了8个块，而A字段索引占用了4个块，统计时用主键索引需要经历的块多，IO次数多。效率也比A字段索引慢。
MySQL常用日志
errlog 错误日志
作用：Mysql本身启动，停止，运行期间发生的错误信息,记录未经授权的访问。
查看错误日志：show variables like '%log_error%';
**slow query log 慢查询日志
作用：记录执行时间过长的sql，时间阈值可以配置，只记录执行成功- bin log 二进制日志
作用：用于主从复制，实现主从同步
- relay log 中继日志
作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
- general log 普通日志
作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能
- redo 重做日志
作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性
- undo 回滚日志
作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现
DATETIME和TIMESTAMP的区别
5.6.4之前
TIMESTAMP和DATETIME的相同点：
- 两者都可用来表示yyyy-MM-dd HH:mm:ss[.fraction]类型的日期。 ``` yyyy：代表年 M：月份数字。一位数的月份没有前导零 MM：代表月（MM和M一样，区别就是MM表示从零开始，比如四月份，MM显示04，M显示4，后面的如同） dd：代表日 HH：代表24小时制的小时 hh：代表12小时制的小时 mm：代表分钟 ss：代表秒 SSS：代表毫秒
重点（踩坑的点）：oracle和java的表达式不一样 yyyy-MM-dd HH24:mi:ss和 yyyy-MM-dd HH:mi:ss,分别代表oracle中的24小时制和12小时制 原因：oracle和java不同,因为oracle是不区分大小写的,所以java中可以根据大小写来代表24小时和12小时的表达式。 但是在oracle中就会出问题,mysql中将24小时的小时和分钟做了特殊处理.如上所示,在HH后面加上了24,将mm改为了 mi,而一旦不注意取到的时间就会出问题!
```java
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH-mm-ss.sss ");
String time=sdf.format(new Date());
- 从MySQL5.6.4之后这两者都可以包含秒后的小数部分，精度最高为微妙（6位）
- MySQL5.6.4之前，这两个是都表示不了小数的
TIMESTAMP和DATETIME的不同点：
- 存储范围不同
timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
如果我们存储timestamp的时候，存了不在它范围内的时间值时，会直接抛出异常
时区相关
datetime存储与时区无关（准备来说是datetime只支持一个时区，就是存储时当前服务器的时区），而<br /> timestamp存储的是与时区有关 <br /> MySQL在存储TIMESTAMP时，会先将时间从当前服务器的时区转换为UTC（世界协调时）以进行存储，然后 查询时从UTC转换为当前时区以进行返回。也就是说使用timestamp进行存储的时间返回的时候会随着数据库 的时区而发生改变。而datetime的存储则与时区无关，数据是什么就存储什么，也就返回什么存储大小
在5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节；但是在5.6.4之后，由于这两个类型允 许有小数部分，所以占用的存储空间和以前不同；MySQL规范规定，datetime的非小数部分需要5个字节，而 不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节， 具体取决于存储值的小数秒精度<br />**<br />**Mysql存储结构
每个数据页可以组成一个双向列表
- 每个数据页中的记录又可以组成一个单向列表
- 每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
- 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录
所以说，如果我们写select from user where indexname = ‘xxx’这样没有进行任何优化的sql语句，默认会这样做：
1.定位到记录所在的页:需要遍历双向链表，找到所在的页
2.*从所在的页内中查找相应的记录:由于不是根据主键查询，只能遍历所在页的单链表了
很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。
索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：
**
要找到id为8的记录简要步骤：
很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过目录可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）。
其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。
存储引擎
存储引擎之MyISAM
特性：
- 表级锁（修改数据加锁，读取数据也要加共享锁）
- 在磁盘上存储上有三个文件.frm(存储表定义) .myd（存储表数据） .myi（存储表索引）
- 支持数据压缩（命令：myisampack）
- 使用非聚集索引，索引和数据分开，只缓存索引
- 默认表类型不是事物安全的
- 执行大量select，myisam是最好的选择
- 不支持外键
使用场景：
- 非事务性应用
- 只读类应用
- 空间类应用
myIsam非聚集索引 B+树的叶子节点存储数据的内存地址
索引由B+树构成，执行查询操作的时候会先搜索B+树，如果找到对应叶子结点会，根据叶子节点的值（地址），拿出整行数据
存储引擎之Innodb
特性：
- innodb支持行锁（共享锁，排它锁，意向锁），粒度更小，但是在执行不能确定扫描范围的sql语句时，innodb同样会锁全表
- 磁盘上存储的是表空间数据文件和日志文件，innodb表大小只受限于操作系统大小
系统表空间：指的是数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。
独立表空间：每一个表都将会生成以独立的文件方式来进行存储。 共享表空间和独立表空间最大的区别是如果把表放再共享表空间，即使表删除了空间也不会删除，所以表依然很大，而独立表空间如果删除表就会清除空间。
Mysql InnoDB引擎的独立表空间与共享表空间是什么？区别又是什么？ - 使用聚集索引，索引和数据存在一个文件
- 支持事物
- 执行大量的update和insert最好用innodb
- 支持外键
使用场景：
- 大多数OLTP应用
InnoDB 聚集索引 B+树的叶子节点存储数据
上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
InnoDB和MyIsam区别**
1、MyISAM是非事务安全的，而InnoDB是事务安全的
2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁
3、MyISAM支持全文类型索引，而InnoDB不支持全文索引
4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
5、MyISAM表保存成文件形式，跨平台使用更加方便
6、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。
存储引擎之CSV
特性：
- 以文本方式存储在文件中
- .csv存储表内容 .csm 存储元数据 .frm存储表结构信息
- 所有列都不能为null
- 不支持索引
- 可以直接编辑文本内容
使用场景：
- 适合做数据交换的中间表
存储引擎之Archive
特性：
- 只支持insert和select操作
- 只支持在自增ID列上加索引
使用场景：
- 日志和数据采集类应用
存储引擎之Federated
特性：
- 提供访问远程MySQL服务器上表的方法
- 本地不存储数据，只存储表结构和远程服务器的连接信息
使用场景：
- 偶尔的统计分析及手工查询
**
若有收获，就点个赞吧
0 人点赞
