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 LogRead 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 * ,这样可以避免大量的回表;

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

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

热门相关:首席的独宠新娘   照见星星的她   巡狩万界   寂静王冠   大神你人设崩了