搜索 K
Appearance
一、什么是MySQL?
word
MySQL 是一种关系型数据库，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL，因此它的稳定性是有保障的。MySQL是开放源代码的，因此任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306。二、SQL语句的分类
word
1、DDL 数据定义语言 create，alter，drop， truncate
2、DML 数据操控语言 insert， delete ， update
3、DQL 数据查询语言 select
4、DCL 数据控制语言 grant， revoke1、DDL 操作数据库和表
word
1.操作数据库（CRUD）
*创建数据库
格式：create database 数据库名称；
*查询
show databases；——查询所有数据库的名称；
show create database 数据库名称；——查询某个 数据库的创建语句
* 修改数据库的字符集：
alte database 数据库名 charater 新的字符 集
*删除数据库
drop database 数据库名称；
* 查询当前正在使用数据库的名称
select database（）；
*使用数据库
use 数据库名称；
2.操作表结构
*创建表
create table 表名（字段名1，字段类型1，
字段名2，字段类型2，
......
）；
*查询表
查看当前数据库中的所有表
show tables；
查看表结构
desc 表名；
查看创建表的sql语句
show create table 表名；
快速创建一个表结构相同的表
create table 新表名 like 旧表名；
*删除表
drop table 表名；
*修改表
改表名： alter table 旧表名 rename to 新表名
改字符集： alter table 表名 character set 新字符集
添加列： alter table 表名 add 列名 数据类型
修改列: alter table 表名 modify 列名 新类型
删除列： alter table 表名 drop 列名2、DML操作表中数据(增删改)
新增：
insert into 表名 （列1，列2...列n）values（值1，值2...值n）；
insert into 表名values (值1, 值2, 值3…); 默认新增全部
删除：
delete from 表名 [where 条件]
truncate table 表名;（推荐使用）truncate 相当于删除表的结构，再创建一张表。
修改：
update 表名 set 列1=值1, 列名2=值2 ... 列n=值n [where 条件]3、DQL 查询表中的数据
语法：
select 列1，列2，... 列n
from 表名列表
where 条件
group by 分组
having 分组后条件
order by 排序
limit 分页
运算符：
比较运算
>、<、<=、>=、=、<> （<>在SQL中表示不等于）
between...and （在一个范围之内）
in （in里面的每个数据都会作为一次条件，只要满足条件的就会显示）
like '张%'(模糊查询， %匹配任意多个字符串，_匹配一个字符)
is null 查询某一列为NULL的值，注：不能写=NULL
逻辑运算符
and 与
or 或
not 非
查询所有数据：
select * from 表名；
查询指定列：
select 字段名1, 字段名2, 字段名3, ...from 表名;
指定列的别名进行查询：
select 字段名1 as 别名, 字段名2 as 别名... from 表名 as 表别名;
清除重复值：
select distinct字段名 from表名;
where条件查询：
select 字段名 from 表名 where 条件;
order by 排序：
order by 排序字段1 排序方式1 ， 排序字段2 排序方式2... （默认升序）
聚合函数：
count 计算个数
max 最大值
min 最小值
avg 平均值
sum 求和
分组查询：
select 分组的类，聚合函数 from 表 group by 列；
select 分组的类，聚合函数 from 表 where 条件 group by 列 having 条件；
【where 过滤发生在分组之前， 不能用聚合函数；having 过滤发生在分组之后, 可以用聚合函数】
分页查询：
limit【m 开始的索引 n 每页的条数
页号：（page-1)*n = m 】4、多表查询
内连接：
隐式
select 列名 from 表1，表2... where 连接条件
显式
select 列名 from 表1 [inner] join 表2 on 连接条件
外连接：
左外
select 列名 from 表1 left join 表2 on 连接条件
右外
select 列名 from 表1 right join 表2 on 连接条件
子查询:
select * from emp where emp.`dept_id` in (select id from dept where name='市场部' OR NAME ='财务部');三、事务相关
1.什么是事务
word
事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。2.事物的四大特性(ACID)/事务的酸性
word
原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。2.1并发事务带来哪些问题?
word
脏读（Dirty read）:
当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个 数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）:
指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样 第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2 也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）:
指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二 个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）:
幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一 个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。2.2事务隔离级别有哪些?MySQL的默认隔离级别是?
word
READ-UNCOMMITTED(读取未提交)：
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止 脏读、不可重复读以及幻读。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读）
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别四、数据库索引相关
1.为什么要使用索引
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
可以大大加快数据的查询速度（大大减少检索的数据量），这也是创建索引的最大原因；
帮助服务器避免排序和临时表；2.Msql索引主要使用的两种数据结构
word
哈希索引：
对于哈希索引来说，底层的数据结构就是哈希表，因此绝大多数需求单条记录查询的时候，可以选择哈希索引，查询性能最快，其余场景建议 使用BTree索引；
Btree索引：
Mysql的BTree索引使用的B树中的B+Tree。但是对于主要的两种存储引擎（MylSAM和InnoDB）的实现方式不同；3.MyISAM和InnoDB区别
word
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
两者的对比：
是否支持行级锁 :
MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复：
MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事 务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务 安全(transaction-safe (ACID compliant))型表。
是否支持外键：
MyISAM不支持，而InnoDB支持。
是否支持MVCC
仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别 下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL- InnoDB-MVCC多版本并发控制五、乐观锁与悲观锁的区别
1.乐观锁
word
总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。2.悲观锁
word
总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现3.两种锁的使用场景
word
从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。六、大表优化
word
当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下1.限定数据的范围
word
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；2.读/写分离
word
经典的数据库拆分方案，主库负责写，从库负责读；3.垂直分区
word
根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库;
简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
垂直拆分的优点：
可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：
主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；水平分区
word保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。