MySQL

 

 

• 聚簇索引：将数据存储和索引放在一起、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。
• 非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据，这就有点类似一本书的目录，比如要找到第三章第一节，那就现在目录里面查找，找到对应的页码后再去对应的页码看文章。
• InnoDB中一定有主键，主键一定是聚簇索引，不手动设置，则会使用一个unique索引作为主键索引，没有unique索引，则会使用数据库内部的一个隐藏行id来当作主键索引。在聚簇索引之上创建的索引称为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引，前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

 

 

 

 

 1.索引失效/不走索引

（1）索引参与函数，或者运算（2）范围查询；（3）模糊查询如like%；（4）类型不一致导致；（5）使用<>或！=导致 ；（6）or引起的连接的不是一个字段 （7）NOT IN, NOT EXISTS

2.数据库三范式：（1）列的原子性；（2）要求实体的属性完全依赖于主关键字；（3）任何非主属性不依赖于其他非主属性。

3.MylSAM 与InnoDB区别：（1）innodb支持事务，MylSAM不支持；（2）innodb支持外键，MylSAM不支持；（3）innodb是聚集索引，数据文件与索引是一起的，必须有主键；而MylSAM是非聚集索引，索引保存的是数据指针，与数据文件分离；（4）innodb不支持全文索引，MylSAM支持，所以查询效率高（5）innodb不保存表的具体行数，MylSAM保存；（6）MylSAM是表级锁，不支持行锁，innodb都支持，默认为行级。

4.主键不能为空，外键即存在另一个表的主键

5.SQL约束

（1）NOT NULL字段内容不为空；

（2）UNIQUE字段内容不能重复，一个表允许有多个UNIQUE约束；

（3）PRIMARY KEY也是不能重复，但只能出现一个；

（4）FOREIGN KEY预防破坏表之间连接的动作，防止非法数据插入外键列；

（5）CHECK：控制字段范围；

6.varchar是可变长度的，char一般是10字符

如果确定某个字段的长度就使用char，如存储MD5加密后的密码。

 7.DDL数据库定义语言，如创建，删除，修改表，改变表的结构。执行完成自动提交，不回滚

DML数据操纵语言，如查，更，插，删deleate。必须要提交才能被其他会话看到。

 8.MySQL查询过程

name是一个索引：select id from table where name = aaa

由于name索引树的叶子结点上保存有username和id的值，通过name索引树查找到id后 就不需要在主键索引去查找了

事务

1.事务就是逻辑上的一组操作，要么都执行，要么都不执行。

事务特征：隔离性（MVCC实现），原子性（redo log实现），一致性（数据全部写入数据库，undo log实现）；持续性（永久操作，（redo log实现））

MySQL四种隔离基本：读未提交（DML，产生脏读），读已提交（不可重复读，会被打断），可重复读（MySQL默认级别，保证并发读取看到同样数据行，不过会产生幻读），可串行化（一般用在innodb分布式事务）

2.事务隔离机制基于锁机制和并发调度。其中并发调度使用的就是MVVC，通过保存修改的旧版本信息支持并发一致性读和回滚。

3.脏读：就是事务A读取事务B的数据，如果B回滚，读到的就是脏数据；（回滚）

不可重复读：就是事务A读数据，事务B更新数据导致数据不一样；（修改）

幻读：事务A读，事务B插入数据，导致A发现还有一条没读到（增删）

4.事务的实现原理

基于重做日志文件（redo log）和回滚日志（undo log）

每个提交的事务都要将其写入重做日志进行持久化，保证事务原子性和持久性。

当修改事务时产生undo log，要回滚就根据回滚日志反向语句逻辑删除，实现数据库一致性。

undo log是在事务前将需操作的数据备份。redo log是在事务中写入备份。

5.MySQL的binlog

记录数据表结构变更以及表数据修改，不会记录查询操作。

 6.MVCC

多版本并发控制。通过保存数据在某个时间点的快照实现。根据事务开始时间不同，每个事务对同一张表，同一时刻看到的数据可能不一样。

 

innodb每一行都有一个隐藏的回滚指针，指向修改前最后一个版本，这个版本存放在undo log中。如果执行更新操作，会把原数据放到undo log中，其他事务查的时候就查这个。 

MVCC好处：读不加锁，增强并发性。通过MVCC，保证事务隔离性。

而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。