mysql-个人总结

基础知识
1 、Mysql 的数据引擎 以及其区别？
一、innodb
1.支持事务
事务ACID(atomicity原子性、consistency一致性、isolation隔离性、durability持久性)
A：事务要么全执行，要么全不执行
C：事务执行前后，数据完整性一致
I：多用户并发访问数据库时，数据库为每个用户创建的事务间相互隔离
D：事务一旦被提交，对数据库中数据的改变就是持久的
2 、四个隔离级别

1. 脏读:事务A读取了事务B未提交的数据，事务B却回滚了。
2. 不可重复读:是指在一个事务中，前后查询不一致。 事务A的第二次读取了事务B 修改后的数据。
   ps：不可重复读是针对同一条数据
3. 幻读：针对其他事务提交前后，读取的记录的条数不同(记录的增删)
   例子：A:先select 再delete表 最后再select（这里查到有数据） B：在A delete表之前insert了一条记录
   ps：幻读是针对数据总量发生了变化
   1)Read uncommitted(读未提交 --> 最低级别,什么都不能解决)
   2)Read committed (读已提交 --> 可避免脏读)
   3)Repeatable read(可重复读 --> 避免脏读，不可重复读)
   4)Serializable(最高级别，可避免脏读，不可重复读，幻读)
   5)innodb默认隔离级别为:Repeatable read(可重复读)
   3.支持行级锁定：
   仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作
   二、MyISAM
   在5.1之前，MyISAM是默认的引擎，MyISAM有大量的特心态，包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁，而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务，但是很多人的概念中依然是不支持事务的引擎。
   MyISAM并不是无所事处。对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用MyISAM。
   1.管理非事务表，提供高速存储和检索，以及全文搜索能力
   2.支持表级锁定：
   直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。
   如果你是写锁(X锁，排他锁)，则其它进程则读也不允许直接锁定整张表，
   索引
   1、 什么是索引？
   一种帮助mysql 提高查询效率的数据结构。
   ● 是索引数据结果

2、 索引的优点和缺点？
索引的优点
大大的增加了数据的查询效率。
索引的缺点
1、 维护索引需要消耗数据库资源 （如果我们只是表， 那么我们占用的数据只是表的数据，而我们如果一旦使用索引，那么我们需要占用的资源就是表的数据+ 索引的数据了）
2、 索引需要占用磁盘空间
3、 当对数据进行增删改的时候，因为需要重新去对数据进行排序，那么增删改的速度必然会受到影响，所以索引也不是建的越多越好。
一般只能说常用的搜索字段去建立索引
3 、 索引的分类？ （必问）
—InnoDB
1 、 主键索引
— 设定主键后会自动创建索引，innodb 创建的索引叫聚簇索引。主键索引因为是主键所以不能为null
2 、 单值索引 单列索引 普通索引
— 即一个索引只包含单个列，一个表可以有多个单值索引。
3、 唯一索引
— 索引列的值必须唯一，但是可以为null 这就和主键索引有区别了。 但是只能存在一个null， 因为null 也是唯一的
4、 复合索引
— 即一个索引包含多个列。 id name age bir 我们可以基于 id 和name 组合一个复合索引。
比如使用范围， 第二个单值索引，是select * from user where name = 或者 select * from user where age=
但是呢 如果我们要使用select * from user where name = xx and age = 0 就需要使用复合索引了。

— MyISam
fulltext 全文索引
全文索引为fulltext。 在定义索引列上只支持全文查找。 允许在这些列中插入的有重复值，也允许有空值。

4、 索引的基本操作
1 、 主键索引， 这个是自动创建的
— 查看索引
show index FROM stu

2 、普通索引
在建表后创建普通索引
#给 name 字段 添加索引。
CREATE index nameindex on stu(name)
#查询这个表的索引。
show index from stu
在创建表的时候 创建普通索引
CREATE table t_stu(id VARCHAR(20) PRIMARY KEY , stuName VARCHAR(20), KEY(stuName))
3 、 唯一 索引
CREATE table t_stu(id VARCHAR(20) PRIMARY KEY , stuName VARCHAR(20), KEY(stuName))

4 、 复合索引
在建表后创建复合索引,同一个索引名称
CREATE index nameAgeIndex on stu(name,age)

show index FROM stu

在建表前创建复合索引
CREATE table t_stu(id VARCHAR(20) PRIMARY KEY , stuName VARCHAR(20), bir VARCHAR(20), KEY(stuName,bir))
5 、如果有一个聚合索引 name ，age ，bir 问一下哪些查询可以通过索引提高查询效率？
name bir age 可以 调整
name age bir 可以
age bir 不
bir age name 可以
bir age 不
复合索引必须支持
1 、 最左前缀原则
假如对col1、col2、col3创建组合索引，相当于创建了（col1）、（col1，col2）、（col1，col2，col3）3个索引
就是 你name 可以 name bir 可以 name bir age 可以 但是你必须包括 name 所以也叫左包括原则
2 、 mysql 引擎在查询过程中，为了更好的利用索引， 在查询过程中会动态的调整索引的顺序， 比如第一个
name bir age 可以调整为 name age bir

6 、索引的原理

STEP 1 : 我们惊奇的发现， 我们虽然无序的插入了 一些数据，但是我们会过头来看这些数据都被排序了，
这是因为我们的表有主键。 主键就有索引所以被排序了 很正常。 排序过后，这些数据将会以链表的形式存在，一个接着一个的，通过指针连接。
STEP 2 : 为什么要进行排序？
为了更好的查询， 比如现在的数据 就如我们这样是 链表的结构 一个挨着一个的， 然后我们查询5 ，如果是没排序，你只能按照顺序去查询，非常的效率低下， 如果是排序的 ，那么我们只要查询5 次就可以找到。
STEP 3 ：如果我要查询100000 呢， 是不是要比较 100000 次？
不需要这时候我们就把前面的一块 如果是16 kb 的分成一页数据， 然后再产生一个页目录， 页目录上只有 每一个开头， 而且页目录的大小也是16k 但是这样的数据的容量已经很大的！！！
因为 是16K 1024 /36 在乘以 16 k1024 /12 这么多数据 才有一页啊， 所以一般最多3层 ，这就是我们所说的B树。

7、B 树和 B +树的 的区别？

这个是B树。他的目录节点有数据， 而B + 树， 只有叶子节点才有数据，也就是最底层节点。 我们的B 树的问题是就是页节点存储了数据， 那么导致每一页的的数据减少---- 然后导致 树的深度更深了。

8、 聚簇索引和非聚簇索引？
聚簇索引 : 什么是聚簇索引？ 主键的索引就是 聚簇索引。
将数据和索引放到一块的，索引结构的子节点报错了行数据。

非聚簇索引 : 什么是非聚簇索引？
将数据和索引分开，索引的叶子结构指向数据的位置。

在InnoDB中， 非非聚簇索引都是辅助索引，比如前面提到的唯一索引和复合索引。非聚簇索引都存的是id 值 ，为啥不存地址呢，因为在增删中，你的地址值会变，可能会导致其他的地址值改变。所以还是id 好用。 比如 他本来在第一号位置， 我插入了，可能在在第一号位，那么会导致这个值得地址位置会改变。所以非聚簇索引在某些方面也值得用的。

9 、 查询步骤
1 、 InnoDB 中 比如我们查询一个语句 Select * from user where id =14 ， 则按照B+ 树的检索算法即可查找对应的叶节点。之后在获取数据值。
2、 如果我们是 select * from user where name =“ZZP” ， 第一步在辅助索引中检索name 然后找到其对应的页目录 ，然后通过页目录找到对应的子索引，然后再通过子索引找到对应的值。

分库分表

1 、 为什么要分库分表？
因为单张包裹表的数据量实在是太大，并且每次客户预定资源的时候，都会进行增长。刚开始我们只用单机数据库就够了，随后面对越来越多的请求，我们将数据库的写操作和读操作进行分离， 使用多个从库副本（Slaver Replication）负责读，使用主库（Master）负责写， 从库从主库同步更新数据，保持数据一致。架构上就是数据库主从同步。

2、 主要面临的问题 ？
1 、 单库太大
单库的数据量太大了，单个数据库处理能力有限；单库所在服务器上磁盘空间不足；
单库上操作的IO瓶颈 解决方法：切分成更多更小的库。
2、 单表太大
CRUD都成问题；索引膨胀，查询超时（ 这里最主要是索引也很大， 然后索引的字段）
解决方法：切分成多个数据集更小的表。

3、 如何进行数据库的分库分表？
一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。如果是因为单张表的数据量太大，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。 分库分表的顺序应该是先垂直分，后水平分。 因为垂直分更简单，更符合我们处理现实世界问题的方式。

4 、 什么是垂直拆分？

1、 垂直分表也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到“扩展表“ 例如全文啊，摘要啊，还有很多系统性字段。。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

2、垂直分库垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。 切分后，要放在多个服务器上，而不是一个服务器上。为什么？ 我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前， 全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上， 随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。 所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。 数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

5 、 什么是水平拆分？

1. 水平分表针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
2. 水平分库分表将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
3. 水平分库分表切分规则
   a. RANGE从0到10000一个表，10001到20000一个表；
   b. HASH取模一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。
   c. 地理区域比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
   d. 时间按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。
   5、分库分表后面临的问题？
   事务支持
   分库分表后，就成了分布式事务了。
   如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担

跨库join
本来一次查询的问题， 变成了多次的组装了 不过还是得就是我们多数的查询，而不是修改。

语法
假设有两张表 ，一张是 部门表 一张是员工表。 表的构如下：

打卡表

1、 查询出最高工资的员工（可能是多个员工）
实现思路 子查询， 先找到最高工资，然后找到这批人
SELECT
*
FROM
Employee
WHERE
Employee.salary = (SELECT MAX( salary ) FROM Employee )
2 、 查询出 那个员工没有打卡
实现思路1 ： 先使用join 查询 那个员工没有打开 ，拿出这个没打开的员工的id 然后再in 这个集合。
SELECT * FROM
Employee
WHERE
Employee.id NOT IN
(SELECT id FROM Employee JOIN iscome ON Employee.id = iscome.stuid WHEREiscome.ka = 1)
实现思路2 ： 如果我们知道打卡的人员 那么我们用人员去 left join 然后得到全部人 是否打卡 那么没打卡就是空咯
SELECT * from Employee LEFT JOIN iscome on Employee.id= iscome.stuid where ka is NULL

3 、sql连接查询中on和where后条件区别？
SELECT * from Employee LEFT JOIN iscome on Employee.id= iscome.stuid where Employee.id =1

SELECT * from Employee LEFT JOIN iscome on Employee.id= iscome.stuid and Employee.id=1

SELECT * from Employee JOIN iscome on Employee.id= iscome.stuid where Employee.id =1
结论：
● 可以这样理解：on是在生成连接表的起作用的，where是生成连接表之后对连接表再进行过滤。
● 当使用left join时，无论on的条件是否满足，都会返回左表的所有记录
对于满足的条件的记录，两个表对应的记录会连接起来；对于不满足条件的记录，那右表字段全部是null
● 当使用right join时，类似，只不过是全部返回右表的所有记录
● 当使用inner join时，功能与where完全相同。
4 、还是员工表和部门表。找出每个部门工资最高的员工。
SELECT
Employee.id, Employee.name as username , Department.name as deptName
FROM
Employee
JOIN Department ON Employee.departmentId = Department.id
WHERE
( Employee.salary, Employee.departmentId ) IN ( SELECT max( Employee.salary ), Employee.departmentId FROM Employee GROUP BY departmentId )