mysql总结

MyiSAM和innodb

MyiSAM：非聚集索引、B+树、叶子结点保存data地址；

innodb：聚集索引、B+树、聚集索引中叶子结点保存完整data，innodb非聚集索引需要两遍索引，innoDB要求表必须有主键；

innodb为什么要用自增id作为主键：

自增主键：顺序添加，页写满开辟新的页；

非自增主键（学号等）：主键值随机，有碎片、不够紧凑的索引结构；

分库与分表设计、分片：

水平分表；

垂直分表：不常用的加入另一张表、大文本字段单独拆分到另一张表、不经常修改的字段放入另一张表；

聚集索引与非聚集索引：

聚集索引：聚集索引查找完整数据；

非聚集索引：查找对应的主键值，然后根据主键值查找聚集索引，查找到完整数据

事务四大特性（ACID）：

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，在执行过程中发生错误，会发生回滚（rollback）；

一致性：事务开始前和结束后，数据库的完整性没有被破坏；

隔离性：多个事务执行，防止多个事务之间由于交叉执行而导致数据不一致。读未提交、读提交、可重复度、串行化。

持久性：事务提交后，对数据库的修改是永久的。

事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

脏读：一个事务处理过程中读到了另一个事务未提交的数据；

不可重复读：一个事务多次读取一个数据，获得不同的数据结果；

幻读：一个事务读的过程中，另一个事务删除或者增加一条数据，影响这条事务的读的结果。

Mysql级别：可重复读。

事务隔离级别：

读未提交：读取未提交的数据，脏读；

不可重复读：事务A多次读取同一数据，事务B在该过程中对数据进行修改并提交，导致A多次读取数据不一致；

可重复读：同一事务里，多次读操作结果一致，但是存在幻读；

串行化：事务并发，一个个按顺序执行。

MySQL常见的存储引擎InnoDB、MyISAM的区别？

MyISAM：事务×，锁级别：表级锁，存储表总行数，非聚集索引；

（适用于插入不频繁，查询频繁）

InnoDB：事务✓，锁级别：行级锁和外键约束，不存储表总行数，聚集索引；

（可靠性要求比较高，或要求事务，表更新和查询都频繁）

数据库三范式，根据某个场景设计数据表？优缺点

• 所有字段值都是不可分解的原子值。
• 在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• 数据表中的每一列数据都和主键直接相关，而不能间接相关。

第一范式(确保每列保持原子性)：

表中字段值不可再分，提高数据库性能；

第二范式(确保表中的每列都和主键相关)：

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

第三范式(确保每列都和主键列直接相关,而不是间接相关)：

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

优点：可以尽量得减少数据冗余 缺点:对于查询需要多个表进行关联，更难进行索引优化 反范式化: 优点:可以减少表得关联 缺点:数据冗余以及数据异常

Explain关键字：

table：当前语句访问的表；

id：id相同，顺序执行，id越大，执行优先度越高；

select_type：小查询语句扮演的角色，比如SIMPLE、PRIMARY、UNION等。

☆type：表示MySQL在表中找到所需行的方式，又称”访问类型“。

常见类型：NULL，system，const，eq_ref，ref，range，index，ALL。

（性能由好到差）

ALL：遍历全表；

EXPLAIN SELECT * FROM s1;

index：只遍历索引树；

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

range：检索给定范围的行；

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；

eq_ref：类似ref，区别：唯一索引；

const、system：查询优化时，转换为常量；system：只查询一行；

# const
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
# system
INSERT INTO t VALUES(1);

NULL：不需要访问表。

☆rows:预估的需要读取的记录条数，值越小越好。

☆key_len :

实际使用到的索引长度 (即：字节数)

帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

MVCC多版本并发控制(Multiversion Concurrency Control)：

InnoDB中实现MVCC机制；

快照读与当前读：

快照读：不加锁的简单的 SELECT 都属于快照读；

当前读：读取的记录进行加锁；

MVCC：包括：隐藏字段、Undo Log和Read View

隐藏字段：当前事务id，undo指针（指向历史版本的当前记录）；

Undo Log：undo日志，通过undo指针串联历史版本；

ReadView：在查询时创建，

包含：

创建这个Read View的事务ID；

当前活跃的事务id列表；

活跃事务最小事务ID；

系统事务最大ID（并不一定是活跃的）；

Read View规则：判断当前查询事务与Read View中最小事务的关系，

如果小于最小活跃ID，那么说明当前读取的行已提交；

如果大于最大ID，说明当前的行是由一个活跃的事务还未提交，正在处理，需要按照Undo Log往下找，找到不在活跃事务id列表中的最新的提交的数据。

简而言之：每一行存储历史信息，查找时获取当前活跃id，找到历史信息中最新的，并且不在活跃id的事务。

处理读已提交：

每次select时创建一个新的Read View；

处理可重复读：

每次同样的select只在最初的select创建一个Read View；

索引优化：

优化的角度：

• 索引失效、没有充分利用到索引——建立索引
• 关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
• 服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf
• 数据过多——分库分表

索引失效情况：

最左前缀：MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

递增主键：减少页分裂，不用随机字段作为主键。

计算、函数、类型转换(自动或手动)导致索引失效：

函数失效：

# 使用like
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
# 使用函数
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
# 创建索引之后
CREATE INDEX idx_name ON student(NAME);

函数将导致索引失效；

计算失效：

# 创建索引
CREATE INDEX idx_sno ON student(stuno);
# 查询时使用计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

导致索引失效；

类型转换失效：

# 未使用到索引
# name的类型为字符串，下面发生了类型转换，导致失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

范围条件右边的列索引失效：

SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

idx_age_classId_name索引将会失效，因为ID>20作为范围条件，导致右侧的name失效。

除非如下修改，将ID>20放到最后：

create index idx_age_name_classId on student(age,name,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

不等于(!= 或者<>)索引失效;

is null可以使用索引，is not null无法使用索引;

like以通配符%开头索引失效；

OR 前后存在非索引的列，索引失效：

OR前后的两个条件中的列都是索引时，查询中才使用索引。

查询优化：

① 尽可能的使用联合索引而不是索引的组合；

②创建索引尽量让辅助索引进行索引覆盖 而不是回表；

③在可以使用主键id的表中，尽量使用自增主键id，这样可以避免页分裂；

④查询的时候尽量不要使用select * ，这样可以避免大量的回表；

⑤尽量少使用子查询，能使用外连接就使用外连接，这样可以避免产生笛卡尔集；

⑥能使用短索引就使用短索引，这样可以在非叶子节点存储更多的索引列降低树的层高，并且减少空间的开销；