存储引擎
1.MyISAM 和 InnoDB区别
1.MyISAM不支持事务,而InnoDB支持,所以InnoDB为默认存储引擎; 2.InnoDB支持外键,MyISAM不支持; 3.InnoDB是聚集索引,MyISAM是非聚集索引; 因此InnoDB必须有主键,数据文件在主键节点的叶子节点;辅助索引则是叶子节点存储主键,需要回表查询;因此主键索引不应该过大,否则其他索引也会跟着增大; MyISAM是非聚集索引,数据文件分离,索引保存的数据文件的指针;主键索引和辅助索引是独立的; 4.InnoDB不保存行数,需要全表扫描;MyISAM使用一个变量存储了行数,所以查询很快; 5.InnoDB最小锁粒度是行锁,MyISAM是表锁;
字段
1.字段类型优先级,特点
整形 > date,time > enum,char > varchar > blob,text;
time: 定长运算快考节省时间,考虑时区,写sql不方便;
enum: 能约束值的目的,内部使用整形储存,但是与char比较时,内部要经历串与值的转化;
char: 定长,考虑字符集和校对集;
varchar: 不定长,要考虑字符集的转换和排序时的校对集,速度慢;
text,blob: 无法使用内存临时表(排序操作只能在磁盘上进行)
注意:
1.能用整形就不用字符串,比如tinyint和char(1)都是占用1字节,但是orderby排序确实tinyint块,因为后者需要考虑字符集和校对集(排序优先集);
2.date,time的选择可以直接选择使用时间戳;enum("男","女")内部转成数字存储,多了转换过程,可以使用tinyint代替;
3.够用就行,比如tinyint使用无符号类型,可以存储255岁;varchar(10)和varchar(300)存储相同内容,查询时后者需要内存更多;
4.尽量不使用NULL:null不利于索引,也不利于查询;
=null和!=null都查不到值,只有使用is null和is not null才可以;
5.char和varchar的选择:
char是固定长度,处理速度更快,但是浪费存储空间;所以对存储空间要求不大,对处理速度要求高的使用char,否则反之;
2.时间类型字段:
时间字段类型:
datetime: 占用8字节,范围('1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999')
timestamp: 占用4字节,范围('1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999')
timestamp翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 datetime 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟用 datetime 还需要函数进行转换，但是 timestamp 直接相减就行。
timestamp 只占 4 个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换。
datetime以 8 个字节储存，不会进行时区的检索.
也就是说，对于timestamp来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于datetime来说，存什么拿到的就是什么。
还有一个区别就是如果存进去的是NULL，timestamp会自动储存当前时间，而 datetime会储存 NULL。
如果在时间上要超过Linux时间的，或者服务器时区不一样的就建议选择 datetime。
如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。
如果只是想表示年、日期、时间的还可以使用 year、 date、 time，它们分别占据 1、3、3 字节，而datetime就是它们的集合。
3.货币存储类型
decimal(numeric ) 同义，用于精确存储数值 。
decimal 数据类型最多可存储 38 个数字，所有数字都能够放到小数点的右边。decimal 数据类型存储了一个准确（精确）的数字表达法；不存储值的近似值。
或者使用long类型,例如将人民币转换为 分,进行存储;
4.varchar(m)最多存储多少数据
最多可以定义65535个字节.m代表该类型最多存储的字符数量,但在实际存储时并不能; mysql对一条记录占用的最大存储空间是有限制的,除了blob和text类型的列,其他所有列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节;
索引
索引类型
主键索引;
唯一索引;
普通索引;
前缀索引; order by和group by无法使用;
表结构
表名t
| id(int) | a(int) | b(int) | c(int) | d(varchar) |
|---|---|---|---|---|
| 1 | 1 | 2 | 1 | 1 |
| 2 | 2 | 3 | 2 | 2 |
| 3 | 3 | 4 | 1 | 3 |
| 4 | 4 | 5 | 2 | 4 |
| 5 | 5 | 6 | 3 | 5 |
联合索引为(a-b-c)
1.mysql一张表最多能存多少数据_mysql B+树可以存多少数据（Innodb存储引擎）
答案是两千多万
1.该图是以名字字段建立索引，树的高度为3为例，叶子节点(第三层)的18 15…是我们存储的数据
2.B+树是以页(作为基本单位)来存储数据或者存储索引和指针
3.页是什么？如上图第一行有三个名字那个方框就是一个页当然，第二行和第三行也是
4.每页存储的数据量是16K mysql默认且可以修改，不信可自查
命令 : show VARIABLES like 'innodb_page_size%’
换算：16384(字节)/1024=16k
5.以bigInt 举例子 它是8个字节 一个指针是6个字节(不要问为什么，你就信了吧)bigInt是mysql 字段类型之一
6.如本文第一个图咱们来计算一下 第一页有多少指向第二层的指针(第一页和第二页只存了索引和指向下一级的指针)
7.16384/(6+8)≈ 1170
8.每一个指针都是一个页，那么第二层有多少页？答案是1170个页，那么每个页是不 也有1170个指针指向第三层，那么第二层有多少指针了？11701170 = 1368900
9.第三层是存数据的，那么他能存多少条数据呢？
10.每一个页咱们之前说了他能存16k大小的数据，而每一条数据大概是 1k 我们就按 1k 算，第二层有1368900个指针指向第三层，也就是 说第三层有1368900个页，每一页可以存16条数据，那么第三层可以存
161368900 =21902400
两千一百九十多万条数据。
注意：B+树只在最下层存储数据
2.最左原则
1.遵循原则与where的条件字段顺序无关,而是要满足包含联合索引从左到右的字段;
3.范围查询时什么时候索引生效
1.(失效) 返回全部字段时,回表效率比全表扫描效率低,全表扫描
select * from t where a>1;
2.(有效) 通过索引回标,数据量较全表要少,效率高;
select * from t where a>1;
3.(有效) 返回索引字段,不涉及回表,查出数据量比聚集索引少了d字段,效率高;
select id,a,b,c from t where a>1;
4.索引下推
select * from t where a=1 and c=1;
1.mysql 5.6之前版本,先通过最左原则查到a=1的一组索引数据,然后通过主键回表查找该组数据,再判断c=1; 2.mysql 5.6之后版本,先通过最左原则查到a=1的一组索引数据,由于c也是联合索引一部分,继续判断c=1,然后使用找到的数据主键回表查询完整数据;
5.order by 导致索引失效
1.(失效) 查询全表后,进行内存排序,性能更高;
select * from t order by a,b,c;
1.(有效) 只需查询索引表,并且无需回表;
select a from t order by a,b,c;
索引失效其他原因
1.优化器行为,比如查询索引的数量是大表的大部分;(可强制使用索引) 2.函数使用在索引字段上,如where (a+1) = 2; 3.like模糊搜索使用左边模糊,如where a like '%1%'; 4.字段存在隐性的类型转换,如a是int类型,d是varchar类型: (1)where a = '1'; 有效索引 (2)where d = 1; 无效索引 具体还是要用explain来分析;
事务
1.事务的基本特性
ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
（1）原子性： 原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
（2）一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
（3）隔离性：隔离性是指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被企图进行修改的事务看到 。
（4）持久性：持久性是指在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。 即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。
2.事务隔离级别及并发问题
事务的并发问题
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
3.ACID靠什么保证
A: 原子性由undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经成功的sql;
C: 一致性由其他三大特性保证,程序代码要保证业务上的一致性;
I: 隔离性由MVCC来保证
D: 持久性由内存+redo log来保证,mysql修改数据同时在内存和redo log记录这次操作,宕机的时候可以从redo log恢复
InnoDB redo log写盘,InnoDB 事务进入prepare状态;
如果前面prepare成功,binlog写盘,再继续将事务日志持久化到binlog,如果持久化成功,那么InnoDB事务则进入commit状态(在redo log里面写一个commit记录)
例子:
update user set age = age + 1 where id = 1;
执行器先到引擎层找到id = 1这一行，由于ID是主键，所以会在主键索引树找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器。否则，需要先从磁盘中读入内存，然后再返回。
执行器拿到存储引擎返回id = 2结果后，给age加上1，原来是25，现在就是26,在调用引擎接口写入这行新数据。
引擎将这行数据先更新到内存中，同时将这个更新操作记录到redo log中，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
接着执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。
两阶段提交:
两阶段提交原理描述:
阶段1：InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态
阶段2：如果前面prepare成功，binlog 写盘，那么再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB
事务 则进入 commit 状态(实际是在redo log里面写上一个commit记录)
备注: 每个事务binlog的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，binlog
最后一个 XID event 之后的内容都应该被 purge。
为什么需要两阶段提交:
保证binlog与redolog的数据一致性。
如果没有两阶段提交：
先写redolog，再写binlog：
redolog写完，还没来得及写binlog，MySQL宕机。重启以后，redolog里有记录，MySQL判断事务提交成功，但binlog里没有记录，binlog与redolog出现数据不一致。由于binlog是追加写入日志，往后的时间里binlog会一直缺失这条数据。如果在以后使用binlog恢复这个时间点的数据，会出现数据丢失的情况。
先写binlog，再写redolog：
binlog写完，还没来得及写redolog，MySQL宕机。重启以后，redolog中没有记录，MySQL判断事务提交失败，但是binlog中有记录，binlog与redolog出现数据不一致。如果以后使用binlog恢复数据，就多出了一个事务操作。
性能优化
1.提升insert性能
1.合并多条为一条插入语句; 2.修改参数bulk_insert_buffer_size,调大批量插入的缓存; 3.设置innodb_flush_log_at_trx_commit = 0,相对1可以十分明显提升插入速度; 4.手动控制事务,多次插入后统一执行commit; redo log的刷盘会在系统空闲时进行;
本文由 GY 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为:
2022/04/07 14:58