mysql知识简单总结
mysql存储引擎
MyISAM
mysql5.5默认的存储引擎时MyISam，它通常用于只读或以读为主的工作。
特点如下：
1、它是基于表的锁（插入和更新会锁表），不支持事务。
2、他的插入（insert）和查询（select）速度效率较高
3、存储了表的行数（count的速度更快）
向表中插入100万条数据？可以先用myIsam插入数据之后，然后改存储引擎职位innoDB的
InnoDB
mysql5.7的默认存储引擎，它是一个事务安全（ACID）的mysql存储引擎。它具有提交回滚和崩溃恢复功能来保护用户数据的。他是行级锁。innoDb将用户数据存储在聚集索引中，以减少主键常见的I/O.为了保证数据完整性，它还支持外键约束。
特点：
支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别和表级别的锁。
支持读写并发，写不阻塞读（mvcc）
特殊的索引存放方式，可以减少io，提升查询效率。
适合：经常更新的表，存在并发读写或有事务处理的业务系统中
redo log
/var/lib/mysql目录下的ib_logfile0和ib_logfile1,每个48M.这些文件就是磁盘的redo log，是用于崩溃恢复的。磁盘的数据文件，数据来自buffer。redo log写入磁盘，不是写入数据文件。 默认参数是1，也就是实时会把事务提交的mysql log buffer 的数据写入log file 并刷到磁盘去。
1、redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。
2、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。
3、redo log的大小是固定的，前面的内容会被覆盖掉
innodb_log_file_size 可以设置默认大小 默认值是48M
随机i/o是要找到某个城市的地址，某街某个房间里的杯子。（比喻）
顺序i/o 就是 挨着往下走
如果正在写入页的时候宕机了，这个redo 就会失效，这个时候需要一个页的副本 ，用这个副本页还员redo log。这个页的副本就是double write，innoDb的双写技术就是通过它实现了数据页的可靠性。
double write由俩部分组成。一部分是内存的double write，一部分是磁盘上的。因为这个是顺序写入，不会带来很大的开销。
在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，他的空间不会收缩。
show variables like ‘innodb_file_per_table’;
开启之后每张表会开辟一个新得空间。这个文件就是数据目录下的ibd文件，存放表的索引和数据。但是其他的数据，如回滚信息，插入缓冲引页、系统十五信息，二次缓冲等还是存放原来的共享表空渐渐内。
binlog
binlog以时间的形式记录了所有的DDL和DML语句（因为记录的是操作而不是数据值，属于逻辑日志），可以用来做主从赋值和数据恢复。
它没有固定大小限制，他的文件内容是可以追加的。在开启了这个功能下。可以把binlog导出成sql语句，把所有的操作重放一边，来实现数据的恢复。
mysql索引
索引是数据库管理系统中的一个排序的数据结构，以协助快速查询、更新数据库表中的数据。
比如查找500条数据中的某一条，没有索引就会全表扫描。如果有索引，是直接到索引文件里查找索引的地址，直到这个地址上找这条数据。 和书籍的目录一样，标记了某项内容在几页。
索引类型
1、唯一索引
主键索引是特殊的唯一索引。唯一索引要求键值不能重复
2、全文索引
能加全文索引的只有char 、varchar、 text 这三种类型，是针对比较大的数据，比如说存放的是消息内容，有几kB的数据。要解决like查询效率的问题。可以创建全文索引。
3、普通索引
也叫非唯一索引，最普通的索引没有任何限制。
MyISAM和InnoDB支持全文索引。
InnoDB
在innodb里，它是以主键为索引来组织数据存储的，所以索引文件和数据文件都在同一个文件里，都在.idb文件里。
innodb的实现是基于B+Tree.数据都存在叶子节点上。
聚集索引（聚簇索引）：就是索引的键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排的，内容也是按拼音排的，按拼音排序的这种目录就叫聚集索引）。
在innoDB中，它组织的数据的方式叫做聚集索引组织表，所以主键索引是聚集索引，非主键都是非聚集索引。
InnoDB中的B+Tree的特点：
1)它是BTree的变种，BTree能解决的问题，它都能解决。BTree解决的两大问题
是什么？（每个节点存储更多关键字；路数更多）
2)扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以
了，不需要遍历整棵B+Tree拿到所有的数据）
3)B+Tree的磁盘读写能力相对于BTree来说更强（根节点和枝节点不保存数据区，
所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4)排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5)效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定的）
如果在主键之外的索引，在name字段上建一个普通索引，又是怎么存储和检索数据的呢？
在innodb中主键索引和辅助索引是有一个主次之分的。
辅助索引存的是辅助索引和主键值（也就是name，和主键id的值（不是主键数据的地址））。如股票使用辅助索引查询，会更具主键值在主键索引中查询，最终取得数据。
比如查找name=‘我’，他会在叶子节点找到主键值也就是id=1，然后再到主键索引的叶子节点拿到数据。
为什么存的是主键值而不是主键值的地址呢？
是因为B Tree有分叉和合并的操作，这个时候键值的地址会发生改变，所以在辅助索引里面不能存储地址。
要保持平衡，就要调整各个节点的位置，就会有分叉和合并。
如果一张表没有主键怎么办？
1、我们定义了主键，innodb就hi选择主键作为聚集索引
2、没有显示定义主键，则innodb会选择一个不包含有null值得唯一索引作为主键。
3、如果也没有这样的唯一索引，则innodb会选择内置得6字节rowID作为隐藏得聚集索引，它会随着记录得写入而递增。如果数据量太大超出了这个范围会报一个主键冲突得异常。一般不会超出这个数据，这个范围大约数40亿条（也就是2^23）.
重复的数据行越多离散度越低，在这样得字段上建索引优化器可能不会走索引，因为重复字段太多和全表扫描差不多。
联合索引最左匹配
如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。就不需要单独再键索引了。
索引的创建
1、在用于where判断order排序和join的（on）字段上创建索引
2、索引的个数不要过多。
——浪费空间，更新变慢。
3、区分度低的字段，例如性别，不要建索引。
——离散度太低，导致扫描行数过多。
4、频繁更新的值，不要作为主键或者索引。
——页分裂
5、组合索引把散列性高（区分度高）的值放在前面。
6、创建复合索引，而不是修改单列索引。
7、过长的字段，怎么建立索引？
8、为什么不建议用无序的值（例如身份证、UUID ）作为索引？
1、无序字段作为索引会导致page的分裂与合并
2、UUID等数据占用字节数太多，而自增的int类型只需要占用4个字节，所有索引最终都会在叶子节点存储主键的id，会导致占用更多的磁盘空间以及降低io性能
什么时候用不到索引？
1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、
计算（+ - * /）：
2、字符串不加引号，出现隐式转换
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEXcomidx_name_phone(name,phone);3、like条件中前面带%
where条件中 like abc%，like %2673%，like %888都用不到索引吗？为什么？
过滤得开销太大，所以无法使用索引。可以使用全文索引
4、负向查询
NOT LIKE 不能：
explain select*from employees where last_name not like'wang'
!= （<>）和NOT IN 在某些情况下可以：
explain select*from employees where emp_nonotin(1)
explain select*from employees where emp_no<>1事务隔离级别
一、事务的基本要素（ACID）
1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。*
二、事务的并发问题
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。这个是针对一行数据得内容
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。这个是针对数据得条数
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
三、MySQL事务隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交（read-uncommitted） | 是 | 是 | 是 |
读已提交（read-committed） | 否 | 是 | 是 |
可重复读（repeatable-read） | 否 | 否 | 是 |
串行化（serializable） | 否 | 否 | 否 |
1、事务隔离级别为读提交时，写数据只会锁住相应的行
2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
3、事务隔离级别为串行化时，读写数据都会锁住整张表
4 、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。1234 效率一次降低
锁
排他锁：
锁住的是索引记录
1、不使用索引
没有创建索引，会使得全表扫描所以不使用索引会进行全表锁住
2、主键索引
只会锁住索引得那一列
3、唯一索引
会锁住二级索引
锁住二级索引，为什么主键索引也会被锁住？
这就是涉及到辅助索引查询数据得方式决定的
在innodb中可以避免，可重复读导致的幻读就是使用了间隙锁机制。
锁得算法（什么时候锁住什么范围）
记录锁(record)：锁住索引的记录 精准匹配id=4
间隙锁(gap)：锁住索引之间的间隙，n+1段，开区间。（7,10）目的是阻塞插入，例如id>5and id<9 id=6也会被锁住。
临建锁（next-key）：前一个到后一个得区间(1,4] , 例如id>5and id<9,
