mysql基础原理笔记

mysql查询的流程

数据类型

整数	INT，（TINY/SMALL/MEDIUM/BIG）INT，BOOL
浮点数	FLOAT，DOUBLE，DECIMAL
字符串	CHAR，VARCHAR，TEXT
日期类型	Date，DateTime，TimeStamp
其他数据类型	ENUM，Point，SET，BINARY

1.  CHAR和VARCHAR的区别
    

   	char(n)	varchar(n)
   区别	固定长度	可变长度
   存储空间	不管实际存储数据的长度，按char规定的长度分配存储空间，存储上限255字节	根据实际存储的数据分配最终的存储空间，还会占用1/2个字节来记录长度
   n代表	n代表字符个数，不代表字节个数。超过最大长度n的限制后就会被截断
   尾部空格	截断尾部的空格	不截断尾部的空格

2. BLOB和TEXT的区别
   BLOB保存二进制数据，TEXT保存字符数据

索引

--创建唯一索引
create unique index on tablename (username(length))

--在原来的表增加索引
alter table tablename add index indexname(columnName) 

--删除索引
drop index indexname on tablename 

--查看索引
show index from tablename

索引的本质是数据结构，索引一般以索引文件存在磁盘上，大部分都是B+树。

使用索引的目的是提高查询效率。在存储引擎上实现的。

索引的优点：

1. 唯一索引能保证每一行都是唯一的
2. 加快搜索速度
3. 加速表连接
4. 减少分组和排序的时间

索引的缺点：

1. 索引本身也是一个表，需要占用内存
2. 提高了搜索速度，但是降低更新表的速度，每次更新表都要保存更新的数据和索引

按数据结构划分索引：①B+树索引  ②Hash树索引   ③Full-Text全文索引   ④R-Tree索引  

MongoDB是使用B树索引

B+树是B-树的优化，B-树的每个磁盘块还包含了data，而磁盘块的大小有限，如果data过大会导致B-树变深

下图是B+树的结构

B+树是双指针的，一个指针指向根节点，另一个指针指向关键字最小的叶子节点。而且叶子节点之间是链式环结构

B+树可以进行两种查找方式：①根据主键范围查找和分页查找，②从根节点开始随机查找

InnodDB有Page的概念，Page是磁盘管理的最小单位，但是一个磁盘块的大小没有这么多，所以申请的Page的存储空间的时候往往是申请多个连续的磁盘块

聚簇索引和非聚簇索引

InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚集索引和非聚集索引都是B+树结构。

聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据（回表查询）。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

设计表结构没有主键的话，mysql会选择不重复的列做索引，没有符合条件的会使用隐含字段的整型做主键

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

主键、外键、唯一键

	主键	外键	唯一键
定义	可以唯一的标识一条记录，不能重复，不能为空	是其他表的主键，可以重复，也可以是空	唯一的标识一条数据，可以为空
作用	保证数据完整性	和其他表建立连接	提高查询速度
在表中的个数	一张表只有一个主键	可以有多个外键	可以有多个唯一键

 

存储引擎

-- 查看存储引擎
SHOW ENGINES

-- 查看某表的引擎
SHOW TABLE STATUS LIKE 'tablename'

InnoDB特点

第一个完整支持ACID事务的mysql引擎

1. 具有行锁
2. 支持并发
3. 非锁定读
4. 支持外键
5. 有效利用内存和cpu

InnoDB和MyISAM的对比

	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
锁定	行锁	表锁
索引类型	聚簇索引	非聚簇索引
关注点	事务	性能
缓存	缓存索引和数据，对内存要求高	只缓存索引

1. 一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？
   
   MyISAM的最大ID记录到数据文件中，重启也不会丢失，所以插入后id = 18
   InnoDB的最大ID记录到内存中，重启会丢失，所以插入后id = 15。如果不重启，则id = 18
    
2. 哪个存储引擎执行select count（*）更快？
   
   MyISAM将表的总行数存储在磁盘上，执行该sql时候直接返回结果
   InnoDB会先读数据，然后累加得出（因为事务的特性，由于多版本并发控制，不确定返回多少行）

Mysql的事务

mysql的事务主要用于处理操作量大，复杂度高的数据。

例如删除一个人员，需要在多个表上都删除对应的信息，这些操作的数据库语句就是一个事务

事务的四个条件（ACID）：

1. 原子性：成功就完成，失败就回滚。
2. 一致性：一次事务中，多次读取一个值都是一致的
3. 隔离性：允许并发事务同时对数据进行读写和修改。防止并发执行导致数据不一致。
   隔离等级：①读未提交②读提交③可重复读④串行化
4. 持久性：事务处理结束后，对数据的修改时持久的。

用begin，rollback，commit来实现：事务处理的两种方法：

1. ①begin 开始一个事务   ②rollback 事务回滚   ③commit 事务提交
2. 直接用set来改变mysql的自动提交模式：
   ①SET AUTOCOMMIT=0 禁止自动提交  ②SET AUTOCOMMIT=1开启自动提交

并发事务带来的问题

更新丢失，脏读，不可重复读，幻读

防止更新丢失除了事务控制器，还要对数据加锁控制。
脏读，不可重复读，幻读都是一致性的问题。由数据库提供的事务隔离机制解决：加锁，数据库多版本并发控制

事务隔离的级别

1. READ-UNCOMMIT读未提交：一个事务可以读取另一个事务未提交的数据，会出现脏读
2. READ-COMMIT读已提交：一个事务要等另一个事务提交数据后才可以读，可以避免脏读，
3. REPEATABLE-READ可重复读：一个事务正在进行事务，不允许其他事务修改，针对update操作
4. SERIALIZABLE可串行化：完全遵从ACID逐一处理，针对insert操作

 InnoDB的MVCC在读已提交和可重复读的两层工作

事务日志

InnoDB 用事务日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。
事务的隔离性是由锁控制的，原子性，一致性，持久性都是由事务日志支持的。

mysql的查询

1. in和exists的区别？
   in 相当于多个or条件的叠加
   select * from a where a.id in （select id from b）
   select * from a where a.id = 1 or a.id = 2 or ...
   
   exists对外表用loop逐条查询，当条件里的语句能够返回记录行，就返回当前行，不能就丢弃
   select * from a where exists （select * from b where b.idi = a.id）
2. UNION和UNION ALL
   UNION连接后会删掉重复的记录（低效率），UNION ALL不会删掉重复的数据
   UNION会按照字段的顺序排序，UNION ALL只是简单的合并返回
3. between and 和not between and
   between and 是包括边界值的
   not between and 是不包括边界值的
4. 分页查找
   select * from user Limit(1000,10)，效率低下，一共要查1010行
   select * from user where id>=990 Limit 10 ，效率高
5. 内连接和外连接的区别
   内连接是求交集
   外连接是求并集