数据库核心概念与优化-优快云博客

幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

二、索引

1、索引分类

引擎	索引类型
innodb	主键索引（聚簇索引）
innodb	单值索引（普通索引）
innodb	唯一索引	唯一、支持NULL值
innodb	联合索引
MylSAM	全文索引 (Full Text)	在此索引列上，支持全文查找

查询索引SQL：

show index from table;

2、mysql两种存储引擎实现

mylSAM

1）、存储结构如下，分了三个文件表结构.frm、索引.MYi、数据.MYD所以只是要三次读取到内存,非聚集索引，即索引和数据放在不同文件

2）、不提供事务的支持，也不支持行级锁和外键。所以更少用。

在写的时候，锁定表、所以性能慢，但是在读的时候，记录的是行数，所以select count（*）from table 会快。

InnoDB（聚集）

1）、Innodb引擎提供了对数据库ACID事务的支持和故障的恢复。并且还提供了行级锁和外键的约束。

两个数据文件 .frm表结构 .ibd:数据和索引文件所以一般来说找到叶子节点就能把数据读取出来，就比较快

按照B+Tree组织所以结构文件:https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

聚集索引：叶节点包括了完整的数据记录里

3、两种引擎所使用的索引的数据结构是什么？

都是B+树，只是mylsam的叶子节点存的是数据的索引、所以是非聚集索引

而innodb叶子节点是存了索引和数据。是聚集索引。

4、索引的两种方法： BTree和Hash

HASH索引。

1）hash(key) hash索引效率很快，

2）但Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。也不能排序，必须全表扫描

3）其次是hash碰撞冲突，会产生链表，如果相同hash值的数据很多，性能也不一定高。

BTree

innodb的Btree索引与主键索引的区别，普通索引查找到叶子节点拿到的是主键索引。

三、数据库SQL规范以及优化

数据库表设计3NF

第一范式(确保每列保持原子性)：即确保每个属性只代表一个含义，不可再分
第二范式(确保表中的每列都和主键相关)；表结构必须有主键，各数据列必须直接依赖于主键列。

第三范式(确保每列都和主键列直接相关,而不是间接相关)；非主键列必须直接依赖于主键列，不能存在传递依赖。

建表规约

1、表明字段名【小写，中间用下划线】：is_get

2、小数类型用decimal,禁用float和double。因为存在精度丢失问题，比较时会问题。

3、varchar类型长度可变，不预先分配空间，长度不要超过5000。如果长度过长，建议用text.并新建一张表存储，用主键关联。防止其他字段索引效率。

4、分库分表：单表数量达到500W或单表容量超过2GB，才推荐分库分表。

如果预计三年到达不了，请不要在在建表是分库分表

5、char(n) 代表字符个数，不是字节数。如果是utf-8 实际占内存应该是 n*3

char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。

varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。

text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。

大于varchar（255）变为 tinytext
大于varchar（500）变为 text
大于varchar（20000）变为 mediumtext

52条优化详细见

阿里大佬总结的52条SQL语句性能优化策略，建议收藏_xmt1139057136的专栏-优快云博客

sql语句优化之一：尽量使用索引避免全表扫描_ywl470812087的博客-优快云博客

平时注意的点：

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。

2、应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。

3、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE。

4、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20。

5、IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN：select id from t where num between 1 and 3。

6、LIKE 下面的查询也将导致全表扫描：select id from t where name like‘%abc%’ 或者select id from t where name like‘%abc’若要提高效率，可以考虑全文检索。而select id from t where name like‘abc%’才用到索引。

7、如果在 WHERE 子句中使用参数，也会导致全表扫描。----没懂

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

13、很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)

22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16、最好不要使用返回所有：select from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

17、使用表的别名（Alias）：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、使用“临时表”暂存中间结果：

简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

20、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。

使用 nolock 有3条原则：

查询的结果用于“插、删、改”的不能加 nolock；
查询的表属于频繁发生页分裂的，慎用 nolock ；
使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。

19、常见的简化规则如下：

不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜

32、选择最有效率的表名顺序（只在基于规则的优化器中有效）：

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

如果有 3 个以上的表连接查询，那就需要选择交叉表（interp table）作为基础表，交叉表是指那个被其他表所引用的表。

33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT'
SELECT JOB, AVG(SAL) FROM EMP HAVING JOB = 'PRESIDENT' GROUP BY JOB

五、innodb的 buffer pool 缓冲池

参考：缓冲池(buffer pool)，这次彻底懂了！！！_自由的天空-优快云博客_buffer pool

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

InnoDB对普通LRU进行了优化：LRU(Least recently used) （与memcache，OS有点不同）

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

1、预读失败-分为 new-sublist 和 old-sublist 将预读页插入old-sublist的头，真正读到的页插入到new-sublist

2、缓存池污染

当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

虽然结果集可能只有少量数据，但这类like不能命中索引，必须全表扫描，就需要访问大量的页：

（1）把页加到缓冲池（插入老生代头部）；

（2）从页里读出相关的row（插入新生代头部）；

（3）row里的name字段和字符串shenjian进行比较，如果符合条件，加入到结果集中；

（4）…直到扫描完所有页中的所有row…

如此一来，所有的数据页都会被加载到新生代的头部，但只会访问一次，真正的热数据被大量换出。

怎么这类扫码大量数据导致的缓冲池污染问题呢？

MySQL缓冲池加入了一个“老生代停留时间窗口”的机制：

（1）假设T=老生代停留时间窗口；

（2）插入老生代头部的页，即使立刻被访问，并不会立刻放入新生代头部；

（3）只有满足“被访问”并且“在老生代停留时间”大于T，才会被放入新生代头部；

参数：innodb_buffer_pool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

参数：innodb_old_blocks_pct

介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

画外音：如果把这个参数设为100，就退化为普通LRU了。

参数：innodb_old_blocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

六、锁

七、Explain解释计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl_benchmark_info		range	PRIMARY	PRIMARY	8		2	100	Using where

group by 和order by 一样都先排序

goup by XX order by null这样可以强制不排序，直接分组。

Using filesort文件排序

底层原理：

单路排序：取出所有字段在sort_buff区（查询所需字段小于Max_length_for_sort_data）

回表排序(双路)：排序字段和可直接定位的行数据的ID，先在sort_buff区排好，排完用ID取回所需数据。sort_mode :<sort_key,rowid>

实战

前提：Mysql由自己的评估判断，不是建了索引就一定会走，很多都不是绝对的。

1、

除了Expain 外，还可以开启：

set session OPTIMIZER_TRACE='enabled=on',end_markers_in_json=on;

然后同时执行查询语句以及下面的SQL；

select * from information_schema.OPTIMIZER_TRACE；

估算全表扫描cost=2054

走索引扫描cost=6074,

所以不走索引，全表扫描比较快！

八、分库分表如何解决跨库查询问题

1、全局表例如字典表，应当每个库建一张

2、字段冗余，比如存了用户ID，存用户姓名，不需要join用户表

3、数据同步定时A库中的tab_a表和B库中tbl_b有关联，可以定时将指定的表做同步。

4、系统层组装，在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。

mysql考点

问题：为什么必须建主键？

一定要自己建主键，如果不建因为B+树会帮你找一个列，不重复列作为主键（如果数据有重复那就默认一个隐藏列），作为建B+树存储

问题：为什么MySQL不建议用uuid? 推荐用自增整型。

整型用来比大小快，占的空间小，将自增勾起来。

自增会使得B+树是一直在末尾添加，性能好

问题：B树与B+树区别？

1、B树叶子节点没有指针？

2、B+树有冗余指针，只有叶节点有存数据，其他层存的是指针。非叶子在同等大小的情况下存储的更多，所以B+树可以控制层高。

问题：非主键索引怎么存储?

树存储非主键索引，重复没关系，他的叶子节点数据存的是主键

问题：联合索引的存储结构？

按照每个字段的大小进行比较

问题：下面3个SQL哪个走索引？

只有第一个，回答出最左前缀、且回答上面的存储结构

问题：Mysql中count(常量)、count(*)、count(列)区别？

表有主键的情况下，

count(*)会统计为null的行， count(列) 不会统计为null的列的行

Select Count (*) 和 Select Count(1) 结果一样

假如表沒有主键, 那么count(1)比count(*)快，

如果有主键的話，count(主键)最快，

问题：sum为null

select if(isnull(sum(col))，0，sum(col)) from t;

问题：null 比较问题

null 和任何比较返回的都是null

null=null 返回时null 而不是true

问题： ORM规约-可以用Ibatis自带的queryForList(String statement,int start,int size)吗？

因为其实现原理是statement查询出所有结果，然后在通过subList截取返回。有可能出现OOM。

问题：<isNotEmpty>是什么意思？<isNotNull>区别

<isNotEmpty>表示不为空且不为null执行

九、NoSql

1、Nosql vs RDBMS

RDBMS

- 高度组织化结构化数据

- 结构化查询语言（SQL） (SQL)

- 数据和关系都存储在单独的表中。

- 数据操纵语言，数据定义语言

- 严格的一致性

- 基础事务

NoSQL

- 代表着不仅仅是SQL

- 没有声明性查询语言

- 没有预定义的模式

-键 - 值对存储，列存储，文档存储，图形数据库

- 最终一致性，而非ACID属性

- 非结构化和不可预知的数据

- CAP定理

- 高性能，高可用性和可伸缩性

2、CAP定理（CAP theorem）

针对分布式系统不可能同时满足以下三个

一致性(Consistency) (所有节点在同一时间具有相同的数据)

可用性(Availability) (保证每个请求不管成功或者失败都有响应)

分隔容忍(Partition tolerance) (系统中任意信息的丢失或失败不会影响系统的继续运作)

3、NoSQL 数据库分类

类型	部分代表	特点
列存储	Hbase Cassandra Hypertable	顾名思义，是按列存储数据的。最大的特点是方便存储结构化和半结构化数据，方便做数据压缩，对针对某一列或者某几列的查询有非常大的IO优势。
文档存储	MongoDB CouchDB	文档存储一般用类似json的格式存储，存储的内容是文档型的。这样也就有机会对某些字段建立索引，实现关系数据库的某些功能。
key-value存储	Tokyo Cabinet / Tyrant Berkeley DB MemcacheDB Redis	可以通过key快速查询到其value。一般来说，存储不管value的格式，照单全收。（Redis包含了其他功能）
图存储	Neo4J FlockDB	图形关系的最佳存储。使用传统关系数据库来解决的话性能低下，而且设计使用不方便。
对象存储	db4o Versant	通过类似面向对象语言的语法操作数据库，通过对象的方式存取数据。
xml数据库	Berkeley DB XML BaseX	高效的存储XML数据，并支持XML的内部查询