mysql

基础知识

三大范式：

​ 第一范式：每个列都不可以再拆分

原子件，要求每一列的值不能再拆分了

​ 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖主键的一部分

一张表只描述一个实体（若列中有冗余数据，则不满足）

​ 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

所有列与主键值直接相关

mysql有关权限的表

	**user权限表**：记录允许连接到服务器的用户账号信息（里面的权限是全局级的）

​	**db权限表**：记录各个账号在各个数据库上的操作权限

​	**table_priv权限表**：记录数据表级的操作权限

​	**columns_priv权限表**:记录数据级别的操作权限

​	**host权限表**:配合db权限表对给定主机上的数据库操作权限作更细致的控制，这个权限表不受grant和revoke语句影响

索引类型

​	**主键索引**：数据列不允许重复，不允许为NULL，一个表只能有一个主键

​	**唯一索引**：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引

​	**普通索引**：基本的引用类型，     允许为NULL值， 没有唯一性的限制

​	**全文索引**：是目前搜索引擎使用的一种关键技术

索引创建

​	**alter table table_name add unique(column)** :创建唯一索引

​	**alter table table_name add unique(column1,column2)**:创建唯一组合索引

​	**alter table table_name add index index_name(column)**:创建普通索引

​	**alter table table_name add index index_name(column1,column2)**:创建组合索引

​	**alter table table_name add fulltext(column)**:创建全文索引

事务

​ 一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，结果必须使数据库从一种一致性状态变到另一个一致性状态

​ 事务是逻辑上的一组操作，要么都执行，要么都不执行

事务四大特性

​	**原子性**：事务最小的执行单位，不允许分割（动作要么全部完成，要么完全不起作用）

​	**一致性**：执行事务前后，数据保持一致，多个事务对同一个数据读取结果是相同的

​	**隔离性**：并发访问数据库时，一个用户事务不被其他事务所干扰，各个并发事务之间数据库时独立的

​	**持久性**：一个事务被提交之后，它对数据库中的数据的改变是持久的（发生故障也不会对其有影响

事务隔离级别

​ read uncommitted:读未提交

	最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读，幻读，和不可重复读

​ read committed：读已提交

允许读取并发事务已经提交的数据，可以阻止脏读，幻读和不可重复仍有可能发生

​ repeatable read：可重复读

对同一字段多次读取结果是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但**幻读**仍有可能发生

​ serializable：可串行化

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

注：MySQL采用的是 repeatable_read 隔离级别
	orable默认采用的是 read_committed 隔离级别

存储过程

​ 优点：

​ 1 存储过程是预编译过的，执行效率高

​ 2，存储过程的代码直接放在数据库中，通过存储过程名直接调用，减少网络通讯

​ 3，安全性高，执行存储过程需要有一定权限的用户

​ 4，存储过程可以重复使用，减少数据库开发人员的工作量

触发器

​**before insert** 	
**after insert**	
**before update**	
**after update**	
**before delete**	
**after delete**

常用sql语言

​	数据定义语言  (DDL)：逻辑结构等操作的，其中包括表结构，视图和索引。
	**create** 
	**drop**
	**alter**

​	数据查询语言（DQL): 
	**select**

​	数据操纵语言（DML):对数据进行操作的
	**insert**
	**update**	
	**delete**
	
​	数据控制功能（DCL):对数据库安全性完整性等有操作的，可以简单的理解为权限控制等
	**grant**	
	**revoke**	
	**commit**	
	**rollback**​		

sql约束

​	**not null**	:控制字段不能为空

​	**unique**	:控制字段内容不能重复，一个表允许有多个unique约束

​	**primary key**	:控制字段内容不能重复，一个表允许出现一个

​	**foreign key**	:用于预防破坏表之间连接的动作，防止非法数据插入外键列，因为它必须指向的那个表中的值之一

​	**check**	：用于控制字段的值范围

六种关联查询

​	交叉连接：**cross join**

​	内连接：	**inner join**

​	外连接：	**left join/right join**

​	联合查询：**union**(去重)**/union all**（不去重）

​	全连接（MySQL不支持全连接）：	**full join**

​	交叉连接：**cross join**

sql优化：

​原因：
	性能低，
	执行时间长，
	等待时间长，
	sql语句欠佳（连接查询），
	索引失效，
	服务器参数设置不合理

​ sql关键字：

​	select 查询

​	dinstinct:去重

​	from :要查询的对象

​	join：连接

​	on:范围

​	where：条件

​	group by:分组

​	having：去重

​	order by :排序

​	limit:分页		

编写顺序：

select ..dinstinct ..from ..join ..on ..where ..group by ..having ..order by ..limit ..

解析顺序

from ..on ..join ..where ..group by ..having ..select..dinstinct ..order by ..limit ..

sql优化主要是优化索引
索引（index）：是帮助数据库高效获取数据的数据结构（b树（mysql），Hash树）–提升查询效率

​ 如下：索引已经是排好序的，当需要对age排序时，直接使用索引即可，不需要再在数据库中对数据排序

使用索引的弊端：

​	1，索引本身很大，通过存放在磁盘（可以存放在内存，磁盘）

​	2，不适用场景：少量数据	频繁更新的字段	很少使用的字段

​	3，会降低增删改的效率

索引分类

​	**单列索引**（index）：如age列做索引（一个表可以多个单值索引）

​	**主键索引**：该列字段值不能重复，不能为null

​	**唯一索引**（unique）：该列的字段值不能重复（如使用id），可以为null

​	**复合索引**（index ）：多个列构成的索引，如（name,age)先根据name查找，再根据age查找

创建索引

​ 方式一：

​ create 索引类型 索引名 on 表（字段）

​	单值：create **index** dept_index  on  tb(dept);  为tb表的dept字段创建单值索引

​	唯一索引：create **unique** index_name on tb(name); 为tb表name创建唯一索引

​	复合索引：create index dept_name_index on tb(dept,name); 为tb表创建复合索引（字段dept,name)

​ 方式二：

​ alter table 表名 add 索引类型 索引名（字段）

​	单值：alter table tb add  index  dept_index(dept);

​	唯一：alter table tb add unique index_name(name);

​	复合：alter table tb add dept_name_index(dept,name)

​	全文索引： alter table t_dept add fulltext index_name(name);

	注：如果一个字段是primary key  则该字段默认就是 主键索引

删除索引

​**drop index 索引名 on  表名**
​		drop index name_index on tb;

查询索引

​	**show index fom 表名**
​    **show index fom 表名 \G **   以一列一列的形式显示

查询执行计划 ： explain + sql语句

id	编号
select_type	查询类型
table	表
possible_keys	预测用到的索引
key	实际用到的索引
key_len	实际使用索引的长度
ref	表之间的引用
rows	通过索引查询到的数据量
extra	额外的信息

不操作索引，sql优化的一些方法

​	1，**尽量避免全表扫描**，首先考虑在where及order by 设计的列上建索引

​	2，**尽量避免在where字句中对字段进行null值判断**（将导致引擎放弃索引进行全表扫描）

​	3，**尽量避免在where字句中使用 != 或者 < > 操作符**（将导致引擎放弃索引进行全表扫描）

​	4，**尽量避免在where字句中使用or来连接条件**（将导致引擎放弃索引进行全表扫描）

​	5，**尽量避免使用 in 和not in**（将导致引擎放弃索引进行全表扫描）

​	6,**尽量避免使用模糊查询**（将导致引擎放弃索引进行全表扫描）

​	7,**where 子句中使用参数**  （将导致引擎放弃索引进行全表扫描）

​	8,**where 子句中对字段进行表达式操作**（将导致引擎放弃索引进行全表扫描）

​	9,**where子句中对字段进行函数操作**（将导致引擎放弃索引进行全表扫描）

​	10,不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

存储引擎

​ MyISAM

​不支持事务，

​不支持外键

​支持全文索引，查询效率上MyISAM要高

​采用表级锁(table-level locking)

​是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和	辅助索引是独立的

​用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速	度很快；

​ InnoDB

​支持事务，

​支持外键

​不支持全文索引

​支持行级锁(row-level locking)和表级锁, 默认为行级锁

​是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效	率很高。

​不保存表的具体行数，执行select count(*) from table时需要全表扫描

存储引擎	事务	外键	全文索引	锁
MyISAM	不支持事务	不支持外键	支持全文索引（查询效率高)	表级锁
InnoDB	支持事务	支持外键	不支持全文索引	行级锁（默认）+表级锁

开启缓存

​ 开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果

​ （1）my.cnf加入如下配置，重启MySQL开启查询缓存

query_cache_type=1
query_cacher_size=600000

​ (2)执行以下命令也可开启查询缓存

set global query_cache_type=1
set global query_cache_size=600000

​