MySQL整理资料

什么是MySQL?

MySQL是一个关系型数据库管理系统.

什么是SQL？

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言

数据库三大范式

1NF:保证所有列都是不可拆分,保证每一列具有原子性

2NF:满足1NF基础上，非码属性必须完全依赖于候选码

3NF:满足2NF基础上，消除非主属性传递性依赖

SQL连接查询方式

1.内连接(inner join)：查询交集数据

2.外连接(LEFT/RIGHT JOIN):

        RIGHT JOIN：以右表为主表，左边为副表

LEFT JOIN：以左表为主表，右边为副表

3.全连接(FULL JOIN): 两表所有数据展示，mysql不支持，使用UNION实现

SELECT a.*,b.* FROM A a LEFT JOIN B b ON a.id=b.id 
UNION 
SELECT a.*,b.* FROM A a RIGHT JOIN B b ON a.id=b.id

4.sql执行顺序

1).对from子句中的表执行一个笛卡尔乘积，此时生成虚拟表 v1

2).on筛选器，筛选满足on表达式的条件，生成虚拟表v2

3).根据join添加外部行，生成新的虚拟表v3

4.where 条件筛选

5). group by分组

6).计算函数

7）.having

8).select

9).distiny

10).order by

11).limit

mysql InnoDB和MyISAM

INNODB:支持事务，支持行级锁和表级锁，支持外键，索引为聚簇索引，每张表保存同一个数据文件中

Myisam:不支持事务，表级锁，不支持外键，索引为非聚簇索引，每张表保存在3个数据文件中

索引

索引的实现通常使用B树及其变种B+树。主要用来提高查询效率，默认是使用主键作为索引，主键没有创建，则会寻找unique的字段作为索引，如果没有unique字段，则会寻找index修饰字段，如果都没有，自动生成索引。

索引类型

1.主键索引：primery key

2.唯一索引：unique

3.普通索引：index

4.组合索引

5.全文索引fulltext index（myisam支持全文索引）     

索引创建删除

索引创建

1）在表中添加索引

CREATE INDEX/UNIQUE index_name ON table_name(colum_name,....)

ALTER TABLE table_name ADD 索引类型[索引名](colum_name,...)

ALTER TABLE table_name MODIFY colum_name INT  UNIQUE

2）创建表时添加

CREATE TABLE table_name( colum_name colum_type PRIMERY KEY/UNIQUE)

删除

DROP INDEX index_name ON table_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMERY KEY

索引的数据结构

通常为b+树，b+树是二叉排序树发展而来，实现的是完全平衡，叶子节点保存的是索引对应的数据，并且每个叶子节点的数据都是顺序排列的线性结构。根节点和子树保存的是索引。

为什么索引快

数据的存放位置一般是在硬盘中，如果数据量大，不使用索引，查询是按行遍历查找，每操作一行数据就对硬盘执行一次io操作，硬盘中执行io操作是耗时的，必须减少硬盘io操作。索引采用b+树数据结构，能减少对硬盘的io操作，一般b+树的高度会在2-4，所以执行2-4次硬盘操作，节约时间。硬盘的io要比内存耗时，所以需要将硬盘数据放到内存中处理。mysql在硬盘上一般只会查找到索引对应的叶子节点。mysql会将叶子节点的数据放到内存中进行读写，减少耗时。并且内存中的叶子节点的数据也会变成b树结构。

索引类型可以分为聚簇索引和非聚簇索引，聚簇索引一般就是主键，使用主键查询时，所有叶子节点存放的是数据 。使用非聚簇索引，叶子节点保存的是主键地址。先查询到地址，然后根据主键地址查询出数据。

索引的原则

创建索引能够提高查询效率，但是索引也不是也多越好，索引多了，对于update操作来说，会影响性能，一般一张表创建索引5个左右，所以创建索引的字段最好符合要求

1.频繁查询的数据使用索引，频繁修改的数据不建议使用索引

2.定义外键约束时，外键的列需要索引

3.不能有效区分的列数据没必要使用索引

数据慢

1.索引失效

创建了索引，但是没有提高查询效率，可能是由于索引失效造成，引起索引失效需要注意

1.）where条件字句中对null值进行了判断，例如：is null not null等

2）.使用模糊查询时使用了左模糊，例如：like ‘%s’

3）.使用or条件 ，如果条件两边出现不是索引的列，索引失效，可使用union代替

4）.使用in条件，例如：in（2，5），可使用bewteen 2 and 5 代替

5）.where条件的=左侧使用表达式操作，例如：WHERE num/5=5,可以使用 WHERE num=5*5

6）.不能使用<> 或者！=，

7）.order by 要和where 条件一致才能使用索引，eg：SELECT *FROM user WHERE age>0 ORDER BY age DESC

2.索引没问题->慢

1).限定查询范围，例如：更改查询内容细粒度，添加查询的限制条件（一个月内）

2).读写分离，主从复制：数据库拆成只读数据库和只写数据库，主库用来写，从库用来读，使用缓存来查询，可以用很多中间件实现 

3).将表拆成多份，经常查询的可以放在一张表中，不经常使用放在其他表中

4）.对表进行优化，添加中间表，或者表中故意添加冗余字段

3.对于删除慢

1）.删除索引后对数据删除，然后在添加上索引

2）.减少io操作，可以批量删除数据

事务

事务是操作数据的最小执行单元，事务是一组操作，要么都执行，要么都不执行

事务具有四大特性

原子性:执行事务的最小单位，不可分割，原子性确保动作要么全部完成，要么完全不起作用

一致性：操作一组事务时，事务提交前和提交后的数据总量保持一致

隔离性：事务之间的操作都是互不干扰的，独立完成

持久性：事务提交后，数据库的改变是永久生效的

数据库存在不同的隔离级别，不同隔离级别解决不同的隔离问题

隔离级别从低到高：read-uncommited，reda-commited，repeatable-read，serilizable

隔离问题有：脏读，不可重复读，幻读

脏读：读取到了未提交的数据，read-uncommited隔离级别产生该问题，提高隔离级别可解决

不可重复读：第一次读取的数据和第二次读取的数据，主要原因是 eg：事务A先查询出数据，事务B修改了数据并提交，事务A再次查询，发现数据不一致。一般该问题出现在reda-commited上，提高隔离级别可解决

幻读：事务A第一次查询的数据数量和第二次查询的数据数量不同，主要原因 eg:事务A先查询出数据，事务B添加或者删除了数据并提交，事务A再次查询，发现数据不一致。一般该问题出现在repeatable-read上，提高隔离级别可解决。

隔离级别越高，数据越安全，相对的，并发效率就降低。隔离级别的选择需要考虑安全和并发效率。mysql默认隔离级别为repeatable-read

锁

添加隔离级别实际上是对数据进行上锁操作。

数据管理类型划分

1）共享锁：事务可以对数据进行并发读取操作，任何事务不能对数据进行修改（事务可以继续追加共享锁，但不能添加排他锁）

2）排他锁：事务A对数据进行修改和读取，其他事务不能对该数据进行操作

锁的细粒度划分为

1）表锁：对整个表上锁

2）行锁：对操作的数据上锁

3）页锁：INNODB没有

根据锁的角度划分

1）乐观锁：认为数据安全，不使用数据库的锁，基于版本进行控制

2）悲观锁：认为数据不安全，使用数据库的锁

3) 死锁：多个事务在同一资源上相互占用，并请求锁定对方的资源

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在加锁阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得共享锁，进行写操作之前要申请并获得排他锁，加锁不成功，则事务进入等待状态，直到加锁成功才继续执行

解锁阶段：当事务释放了一个锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作

Read uncommitte不会加锁

read-commited：读取数据不上锁，update数据上排他锁

repeatable-read：读取时使用了行共享锁，update数据上排他锁

serilizable：读取时给数据表上表共享锁，update数据上排他锁

以上是使用锁处理隔离问题，

但对于mysql来说，使用rr隔离级别，不会出现幻读，主要是mysql中是使用基于乐观锁的MVCC（多版本并发控制）机制，该机制通过版本进行控制，能够提高性能。当事务A执行查询时，会提供一个版本号给事务A，事务B对数据进行操作，给事务B另一个版本，每个版本的数据都是独立的快照。事务A查询的数据是基于版本号的，即使事务B提交了数据，查询的结果还是以事务A为主的版本数据.

视图

所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。

视图的建立和删除不影响基本表，方便查询,一般是连接多表时使用

为避免多表字段名字冲突，自定义视图字段

CREATE VIEW view_name(colum1.colum2...) AS SELECT....

SHOW VIEW

DROP VIEW view_name

SQL 5大约束

not null

primery key

foreign key 

check

unique

SQL的生命周期

1）客户端连接server层

2）查询缓存是否开启，判读缓存是否存在数据，存在就直接使用，不存在就执行下一步

3）分析器进行语法分析和词法分析，首先进行词法分析：分析字段类型，检查字段是否存在，然后进行语法分析，分析语法是否正确

4）优化器：出现多个索引时，决定使用哪一个索引，或者连接查询时，判断连接顺序

5）执行器：调用存储引擎，执行sql

6）数据返回客户端

7）销毁

在对数据进行update操作时，数据最终是保存在磁盘上，需要在磁盘上查找对应的数据，并修改，整个过程比较耗时，mysql采用了wal技术，将数据写到redo log中，INNODB会在空闲时候，将redo log日志写到硬盘上，即使数据库异常，记录也不会消失。redo log 是innodb的，记录的是物理日志，binlog是server层的，记录的是逻辑日志