Mysql高级篇-笔记
一、Mysql字符集
- mysql 5.7与mysql 8.0
mysql8.0开始，字符集默认使用utf8bm4字符集，可插入中文数据。
mysql5.7及以前默认字符集使用latin1 无法插入中文字符数据。
对此，我们需要修改mysql字符集：
- windows系统
修改my.ini文件 - linux系统
修改my.cnf文件
在配置文件中添加以下内容即可(请在修改完成后重启mysql服务)
character-set-server=utf8mb4
注意：在修改字符集前已创建库的字符集是不改变的
请使用 alter database 库名 character set 'utf8'或 alter table 表名 convert to character set 'utf8'进行修改
二、Mysql表在文件系统中的表示
- mysql
存储了Mysql的用户信息和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
- information_schema
这个数据库保存这mysql服务器维护的
其他所有数据库的信息。比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息并不是真实的用户数据，而是一些描述性信息，有时也称为元数据。在information_schema中提供了一些以innodb_sys开头的表用于表示内部系统表。
- performance_schema
保存了Mysql服务器在运行过程中的一些状态信息。可以用于
监控Mysql的各类性能指标。
包括统计了最近执行的sql语句以及在执行过程的每个阶段所花费的时间和内存使用情况。
- sys
该库主要通过
视图的形式，将information_schema和performance_schema结合起来，方便监控
所有表数据存放在 var/lib/mysql 下，在该目录下可以看到系统自带数据库目录和自定义创建的数据库目录。
随机点开一个数据库目录可以看到三种文件类型
① innodb引擎
- db.opt ：该文件主要记录了当前数据库使用的字符集、比较规则等配置信息。（在mysql8.0以后该文件被合并到xx.ibd文件中）
- xx.frm ：该文件记录了xx表的表结构信息（在mysql8.0以后该文件被合并到xx.ibd文件中）
- xx.ibd ：在mysql5.7下，表数据默认存放在对应的xx.ibd文件中（也可以存放在数据库目录同级目录 var/lib/mysql中的ibdata1文件中）
- ibdata1 系统表空间
系统表空间在mysql服务中只存在一份，在mysql5.5.7到mysql5.6.6中我们的表数据都会默认存放在系统表空间中 - xx.idb 独立表空间
在mysql5.6.6以后的版本中，每个表对应一个独立表空间（即对应一份xx.ibd文件）
[server]
innodb_file_per_table=0
0表示使用系统表空间 1表示使用独立表空间
② myisam引擎下文件结构又发生了变化
- 存储表结构：8.0以前：xx.frm，8.0以后：xx.sdi。
- 存储表数据：xx.MYD
- 存储表索引：xx.MYI
可以看出在myisam引擎下，索引数据和表数据是分开存储的。视图也会独立创建一个xx.frm文件。
三、用户与权限管理
1. 用户账号管理
- 创建用户
create user ‘用户名’@‘访问权限’ identified by ‘密码’;
- 删除用户
drop user ‘用户名’@‘访问权限’;
- 修改用户密码
alter user ‘用户名’@‘访问权限’ identified by ‘新密码’
tips: @'访问权限’可省略，默认访问权限是‘%’
2. 用户权限管理
- 给用户分配权限
示例① 给用户分配所有权限（与root用户差别在于该用户不可分配权限给其他用户
GRANT ALL PRIVILEGES ON *.* to ‘用户名’@‘访问权限’;
示例② 给用户分配数据库db1下所有表的增删改查权限
GRANT INSERT,DELETE,UPDATE,SELECT ON db1.* to ‘用户名’@‘访问权限’;
示例③ 给用户分配数据库db2下table1的查询权限 并可以将该用户拥有的权限分配给其他用户
GRANT SELECT ON db2.table1 to ‘用户名’@‘访问权限’ WITH GRANT OPTION;
示例④ 移除用户查询数据库db1下所有表的权限
REVOKE SELECT ON . FROM ‘用户名’@‘访问权限’;
- 查看用户权限
show grants [for ‘用户名’@‘访问权限’];
用户权限表的部分介绍：
- user：记录了所有用户信息以及用户对所有库所有表的操作权限，即全局权限。
- db：记录了仅对某些数据库有操作权限的用户权限信息（不存在拥有全局权限的用户）
- tables_priv：在db表的基础上对用户的权限进行细分到表上
- columns_priv：在tables_priv表的基础上对用户的权限进行细分到列上
四、角色
权限的集合，在数据库user表中还是以用户的形式存储。
角色的概念在mysql8.0后出现，之前的版本中无法使用
1. 角色的操作
- 创建角色
create role ‘角色名’@‘访问权限’;
-
给角色赋予权限，查看角色权限等 均和用户权限操作相同
-
删除角色
drop role ‘角色名’@‘访问权限’;
2. 角色的使用
- 将角色赋予用户
grant ‘角色名’@‘访问权限’ to ‘用户名’@‘访问权限’;
- 激活角色
在mysql中，给用户分配角色后默认该角色是未激活状态，即用户此时还未拥有该角色所拥有的所有权限
查看当前用户的所有角色
select current_role();
① 使用set default role激活角色
激活当前用户的所有角色：set default role all to ‘用户名’@‘访问权限’;
激活当前用户的某个角色：set default ‘角色名’@‘访问权限’ all to ‘用户名’@‘访问权限’;
需要退出用户重新登陆才生效
② 设置全局变量使角色赋予用户时自动激活
set global activate_all_roles_on_login=ON;
- 撤销用户下的角色
revoke ‘角色名’@‘访问权限’ from ‘用户名’@‘访问权限’;
五、mysql架构
1. sql执行流程
词法分析：分析SQL语句每一个字符串都代表什么意思
语法分析：分析SQL语句语法是否有错误，如FROM写为FORM等
解析树结构例如：
2. sql执行原理
- 开启查询缓存（mysql8.0后移除了查询缓存 - 命中低下）
在配置文件 my.ini/my.cnf中添加
query_cache_type=1 或 query_cache_type=2（按需缓存）
- 开启profile记录sql消耗资源信息（仅本次会话有效）
set @@profiling=1;
- 执行两次命令
select * from user;
- 查看执行的SQL列表
查看所有执行的SQL列表：show profiles;
- 查看某一条执行SQL的执行情况 配合5使用
show profile for query 序号;
直接查看最后一条执行的SQL情况：show profile;
请注意：若执行的两条SQL有一个字符之差（包括空格，大小写等）均不会命中缓存，且只要缓存对应的表数据或结构被修改，则该表的所有缓存将被清除；若使用now()这种函数，每次查询的结果集均不相同，则也相应不会缓存。
3. 数据库缓冲池
Innodb引擎是以页（数据页：16kb）作为单位来管理存储空间的。增删改查操作其实本质上都是在访问页面。为了让效率提升，DBMS在操作时会向内存申请一块空间作为“数据缓冲池”，所有页面操作都在内存中进行，并定期将数据进行刷盘。
首先需要明白，数据缓冲池和查询缓存并不是一个东西。
-
缓存原则：位置 * 频次 ，由于内存有限，我们会将访问高频的数据存储进内存，而低频数据会被移除。
-
同时缓冲池有预读特性：在我们使用一些数据时，大概率会访问这些数据周围的数据，所以采用“预读”特性会将这些数据同时加载进内存中。
-
缓冲池读取数据：在我们进行访问页面时，首先会去查询缓冲池中是否存在这些数据，如果存在则返回，不存在则访问磁盘读取数据并缓存在缓冲池中，然后返回数据。
4. 查看/设置buffer大小
MyISAM引擎只缓存索引，不缓存数据，可使用命令key_buffer_size查看。
InnoDB命令为：innodb_buffer_pool_size
5. 多个buffer实例
★：在多线程环境下，访问buffer数据都会经过加锁处理，这样会导致性能下降，所以我们可以设置多个buffer实例（即将buffer分为多块区域互相独立），这样线程访问互不干扰即可提升性能。
我们可以根据innodb_buffer_pool_instances查看buffer实例个数（默认是1），当缓冲池大小大于1G时，鼓励设置多个buffer实例来提升并发性能（小于1G设置无效）。
① 问：如果我们执行了数据更新操作，这些数据差异会立刻同步到磁盘上吗？
答：不会。数据库会以一定的频率刷新数据到磁盘上（俗称刷盘），缓冲池采用
checkpoint机制将数据回写到磁盘上。
② 问：如果数据在缓冲池中还没刷新到磁盘时，数据库服务发生宕机怎么办？
答：数据库会根据事务日志Redo Log和Undo Log来进行数据恢复操作。
六、存储引擎
1. Innodb引擎和MyISAM引擎
主流使用：Innodb和MyISAM引擎
| Innodb | MyISAM | |
|---|---|---|
| 外键 | √ | × |
| 事务 | √ | × |
| 行表锁 | 行锁(适合高并发) | 表锁(不适合高并发) |
| 缓存 | 缓存数据和索引 | 只缓存索引 |
| 自带系统表使用 | × | √ |
| 关注点 | 并发写、支持事务、处理大量数据 | 节省资源、消耗小、简单业务 |
| 默认使用 | mysql5.5以上 | mysql5.5以下 |
| *如果没有特殊要求，优先选择Innodb引擎。 | ||
| MyISAM引擎使用场景：以读为主，且不需要事务控制的场景使用。* |
创建表时指定引擎
create table 表名(id int) engine = Innodb/MyISAM;
修改表引擎命令：
alter table 表名 ENGINE = Innodb/MyISAM;
tips: 为什么读多或者写多时可以选择MyISAM
1.myisam没有事务支持，它的连续的插入和查询速度都比Innodb快很多，但是如果需要插入和查询穿插着来，那么myisam是表锁，innodb是行锁，innodb的并发性好，并且innodb是支持事务的
2.innodb在插入数据的时候需要维护表级缓存，myisam只需要维护索引(文件级offset定位数据行，不需要缓存表)
3.innodb在插入和查询的时候需要维护mvcc
4.innodb在插入时维护主外键关系等innodb因为提供了事务支持和表级锁的支持，维护成本比myisam高很多
其他部分常用引擎：
2. Archive引擎
用于数据存档，仅支持插入(不能修改)和查询，数据文件扩展名为.ARZ。
- mysql5.5后支持索引
- 使用
zlib压缩库，拥有很好的压缩机制，所以占用空间极小 - 采用行级锁，支持AUTO_INCREMENT列属性（自增长）。除了自增长列，其他列不允许创建索引。
- 适合存储大量的独立的作为历史记录的数据，常用于日志和数据采集。
- 对插入效率很高，对查询支持较差。
3. Memory引擎
使用内存存储数据，响应速度快。但如果MYSQL服务重启或宕机，数据会丢失。且仅支持表字段类型为定长格式，即不支持Blob或Text类型。
- 支持哈希索引（默认）和B+树索引
- 表大小受到限制。主要取决于两个参数：
max_rows:最大行数，可在创建表时指定
max_heap_table_size:最大表大小，默认16MB，可按需扩大（主要用于限制表大小防止内存溢出）。
使用场景：
- 目标数据比较小且频繁访问，数据量太大会造成内存溢出
- 临时数据
- 不在乎数据丢失问题
4. CSV引擎
存储数据以逗号分隔各个数据项，以.CSV扩展名结尾存储数据，可使用文本或excel打开。
- 以上特性得出，csv引擎可以将普通csv文件作为mysql的表进行处理，但不支持索引。
- 对于数据的快速导入，导出有明显优势。
- 对于CSV引擎的表字段，必须不能为空。
七、索引
1. 什么是索引？
索引是一种数据结构。
对于以上左边的数据，未加索引前如果我们需要找到Col2=89的数据，需要进行6次IO操作才能得到6-89的数据。
当我们对Col2加了索引以后，就相当于在硬盘上为Col2维护了一个数据结构，即右边的二叉搜索树。二叉搜索树每个节点存储的都是<K, V>结构。key是Col2的值，value就是该key所在行的地址值（指针）。
★：当我们需要获取到6-89这行数据时，就会先去搜索该二叉树。
- 读34节点到内存，34<89，继续右侧搜索
- 读89节点到内存，发现89=89，则可根据该节点key对应的value找到对应行的数据。
以此可以发现，从原来6次IO的操作缩短到2次IO次数，加快了查询效率。这就是索引， 目的就是减少磁盘IO的次数。
1.1 优点
- 降低数据库IO成本
- 通过唯一索引，可保证数据库表中每一行数据的唯一性
- 加速表与表之间的连接
- 减少查询中分组和排序的时间
1.2 缺点
- 创建索引和维护索引很耗费时间
- 需要占用磁盘空间
- 降低更新表的速度
当我们需要频繁更新表数据时，可以先删除索引，待操作完成后再创建索引，这样可以减少维护索引的时间。
2. B+ Tree
在数据库中，数据默认设置为Compact格式。即一条数据包含额外信息和真实数据两部分。
那么数据库中的数据在磁盘上就会是以下形式展现：
那么如果我们想快速找到我们想要的数据项，我们就需要建立一个“目录”。
建立目录的前提条件：
- 下一页中用户记录的主键需要大于上一页中用户记录的主键
- 给所有页都建立一个目录项
建立的目录：
至此，一个简单的索引案例就出来了
在查询的时候先查询目录页，根据key值找到数据项所在页，再加载数据页进行查询。
同时，如果数据量比较大，还可以再根据以上步骤建立目录的目录页，以此类推，这样一个B+Tree就出现了。
一般情况下，我们所用到的B+Tree都不会超过4层
假设所有用户记录数据页可以存放100条数据，所有目录页一共可以存放1000条目录项记录（目录项比用户数据项小）
- 1层：100条数据
- 2层：最多可以存放1000*100=10000条记录
- 3层：最多可以存放10001000100=100000000条记录
- 4层：最多可以存放100010001000*100=10000000000条记录
由此可以得出，通过建立索引，我们一般最多和磁盘发生IO的次数仅4次，大大提高了查询效率。
3. 常见索引概念
按物理实现方式，索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引，也称为二级索引或辅助索引）
① 聚簇索引
不是一种单独的索引类型，而是一种存储方式，即所有用户数据都存储在叶子节点（也就是上述图片中的样子）
特点：
- 页内记录是按照主键的大小顺序排成一个单向链表
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排序成一个双向链表
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
限制：
- 只有Innodb数据引擎支持聚簇索引
- 由于数据物理存储排序方式只有一种，所以每个表只能有一个聚簇索引，一般为主键
- 如果没有定义主键，innodb会选择非空的唯一索引代替，否则innodb会隐式的定义一个主键来作为聚簇索引
- 请选择有序的顺序ID作为主键
MyISAM引擎中，没有聚簇索引的概念，因为她的索引和数据是分开存放的（.MYD/.MYI），所以可以称为二级索引。
而在Innodb中，索引和数据一起存放（.idb），数据即索引，索引即数据。
② 二级索引
二级索引也和上述图片类似，只是他的叶子节点中的用户记录项仅包含record_type、next_record和二级索引所在列信息及数据页号，不包含所有数据。
排序顺序遵循聚簇索引的特点。
示例：
如现在有张表data包含4个字段，id（主键）,c1（二级索引）,c2，c3
select c1, c2, c3 from data where c1 = 1;
数据库首先会搜索二级索引得到c1为1的数据，并根据该数据项的数据页号去查询聚簇索引得到c2，c3的值（这个动作称为回表）
问：为什么不把所有数据都存储在二级索引中，这样就不需要回表了？
答：聚簇索引已经包含了所有数据内容，二级索引可以创建多个，如果每个都包含了所有数据信息，假设有3个二级索引，表中共有1000W条数据，那么磁盘中将存在4个表数据副本，太浪费磁盘空间。
③ 联合索引
同时以多个列的大小作为排序规则，也就是同时为多个列建立一个索引，比如为c1列和c2列建立一个联合索引。
- 先把各个记录和页按照c1进行排序
- 在c1相同的情况下再按c2进行排序
联合索引本质上也是一个二级索引。
④ 覆盖索引
概念：
- 一个索引包含了满足查询结果的数据就叫做覆盖索引（或不需要回表操作）
- 索引列+id 包含了select 到 from之间使用到的列叫做覆盖索引
优点：
- 避免对主表的二次查询（回表），减少IO操作
- 随机IO变为顺序IO加快查询效率
缺点：
- 索引字段维护需要成本，因此，在建立冗余索引来支持覆盖索引时需要权衡利弊
⑤ 索引下推（ICP）
现有一张表 tb1，在tb1中建立一个联合索引 idx_c1_c2_c3
执行以下SQL语句（注意：c3列未使用到）
select * from tb1 where c1=1 and c2 like '%a' and c4 like '%123%';
在这条sql执行计划中，extra显示 using index condition，表示使用了索引下推
执行过程：
- 先找到c1=1的所有列
- 此时执行引擎会选择继续根据c2列过滤查询到的所有c1=1的列（这样可能会过滤掉非常多的数据 减少回表次数），因为如果此时回表，再查找c2 like ‘%a’ and c4 like ‘%123%’;显然回表次数增加，IO次数增加，耗费CPU资源。
- 根据第2步过滤的结果集再回表进行1次IO找出符合c4条件的数据即可。
第2步中，虽然c2列使用了前置%导致索引失效，但是执行引擎还是过滤了结果集，这种操作就是索引下推。
- 如果没有ICP，在筛选完c1=1的数据后就回表定位基表中的行，并返回给MYSQL，由MYSQL服务器来过滤WHERE后面的条件。
- 启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则mysql服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并只有在满足这一条件时才从表中读取行。
- 虽然ICP可以减少存储引擎必须访问基表的次数和MYSQL服务器必须访问存储引擎的次数，但是ICP的加速效果还是取决于在存储引擎内通过ICP筛选掉的数据比例。
索引下推的开启/关闭：set optimizer_switch = on/off
索引下推使用条件：
- 如果表访问的类型为range、ref、eq_ref和ref_of_null可以使用ICP
- 可用于InnoDB和MyISAM表
- ICP仅用于二级索引，目标是减少全行读取次数，从而减少IO操作
- 使用覆盖索引时不支持ICP
- 相关子查询的条件不能使用ICP
小结
- 不使用过长的字段作为主键，否则会令二级索引变得过大。
- 使用单调递增或单调递减的字段作为主键，否则在更新表时，可能会因为维护B+Tree的特性而频繁的分裂调整索引，十分低效。
4. 索引的代价
- 空间代价
每个索引都要为它创建一个B+Tree，而每一颗B+Tree的每个节点都是一个数据页，一页默认占用16KB存储空间，一棵树由许多数据页组成，是一块很大的存储空间。
- 时间代价
每次更新表数据，都可能需要去修改索引。增删改这些操作可能会对索引的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂，页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+Tree都要进行相应的维护操作，会给性能拖后腿。
一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的性能就越差。
八、索引设计
按功能分类，索引可以分为普通索引，唯一索引，主键索引以及全文索引。
- 普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。
- 唯一索引：与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。
- 主键索引：是特殊的唯一索引，不允许有空值，全表仅一个。
- 组合索引：将几个列作为一条索引进行检索，使用最左匹配原则，只有where条件中使用了字段id才会被使用。
- 全文索引：全文索引（FULLTEXT）作用于CHAR，VARCHAR、TEXT数据类型的列，mysql5.6.4之前仅MyISAM支持，mysql5.6.4之后MyISAM、Innodb都支持。
1. 哪些情况适合创建索引？
- 字段值有唯一性的限制
如果某一字段是唯一性的，就可以对其创建唯一索引或主键索引。
如学号字段，呈唯一性，为该字段创建唯一索引后则可通过学号快速定位到学生的信息。但如果使用姓名的话，可能出现同名情况，会降低查询效率。
业务上具有唯一特性的字段，即使是组合字段，也必须创建唯一索引（来自：Alibaba）
不要以为唯一索引影响了insert速度，这个速度损耗是可以忽略的，但提高查找速度效果明显。
-
频繁作为where条件的字段
某个字段在SELECT语句的WHERE条件中经常被使用到，那么就可以对该字段建立索引（普通索引）了。尤其在数据量大的情况下，查询速度提升效果明显， -
经常GROUP BY 和ORDER BY的列
索引建立顺序为GROUP BY列在前，ORDER BY列在后建立组合索引效率最高。
因为执行顺序是先GROUP BY 后ORDER BY -
UPDATE、DELETE的WHERE列
-
DISTINCT列
-
多表JOIN连接操作时，创建索引注意事项
① 连接表数量尽量不超过3张
② 其次，对WHERE条件创建索引
③ 对用于连接的字段创建索引，并该字段在多张表中的类型必须完全一致，如不能一个为int另一个为varchar。 -
使用列的类型小的创建索引
这里说的类型大小指的是该类型表示的数据范围大小
以整型类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有二级索引的节点也会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。 -
使用字符串前缀创建索引（前缀索引）
对字符串类型列创建索引时，我们可以通过截取字段的前一部分内容建立索引，这就叫前缀索引。这样可以节约空间，又减少字符串比较时间。
截取多少呢？如何 计算不同长度的选择性呢？
公式：
select count(distinct left(列名, 索引长度)) / count() from 表名
如：
select count(distinct left(address, 10)) / count() as 10, --截取前10个字符的选择度
count(distinct left(address, 15)) / count() as 15, --截取前15个字符的选择度
select count(distinct left(address, 20)) / count() as 20 --截取前20个字符的选择度
from shop;
得出截取长度的选择度哪个接近于1就说明匹配度越高，但随之索引长度也会越长
但这会引申出另一个问题：
如果使用了前缀索引，将无法使用索引排序，只能使用文件排序。因为索引不包含完整的address列信息，无法对前缀后面的字符进行排序。
Alibaba开发手册：
在varchar字段上建立索引必须指明索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度
说明：
索引长度和区分度是一对矛盾体，对于字符串类型数据，一般长度为20的索引区分度就会高达90%以上。
-
区分度高（散列性高）的列适合作为索引
列的基数指的是某一列中不重复数据的个数，如某列包含值2,5,8,2,5,8,2,5,8，虽然有9条记录，但该列基数是3（2,5,8）。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散（不容易重复），列基数越小，该列中的值越集中。列的基数指标很重要，直接影响我们能否有效利用索引。
最好为基数大的列创建索引，对基数太小的列创建索引效果可能不好。
可以使用公式select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了
tips:联合索引把区分度高的列放最前面 -
使用最频繁的列放在联合索引左侧
遵循“最左前缀原则” -
在多个字段都要创建索引情况下，联合索引由于单值索引
比如创建联合索引（id, name, create_time）,单使用id查询时也可以用得上该索引
2. 限制索引数目
一般建议单表索引不超过6个。
原因：
- 每个索引都要占用磁盘空间
- 索引会影响Insert、delete、update等语句的性能，因为表中数据修改时，同时也需要调整索引。
- 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成一个最好的执行计划，如果有多个索引都可以用于查询时，会增加优化器生成执行计划的时间，降低查询性能。
3. 哪些情况不适合创建索引？
- 在where中使用不到的字段不设置索引
- 数据量小的表最好不要使用索引
表记录太小，效率提升并不大，并且可能还需要回表查询，浪费时间空间。数据量小于1000行时最好不要创建索引 - 有大量重复数据的列上不要建立索引
- 避免对经常更新的表创建过多索引
- 不建议用无序的值作为索引
- 删除不再使用或很少使用的索引
- 不要定义冗余或重复的索引
例如联合索引和单列索引，可能会发生重复冗余
4. 哪些情况索引会失效？
例子：假设我们现在有两列col1，col2，并有单列索引index_col1和组合索引index_col1_col2。
- 全值匹配我最爱
这种情况主要是针对组合索引的。
在进行查询时如果where中同时使用了col1和col2进行查询时，执行计划中会使用索引index_col1_col2来进行查询，从而index_col1失效。 - 最佳左前缀原则
这种情况针对组合索引。
在进行查询时，如果我们需要使用组合索引index_col1_col2，则在where条件中需要按顺序进行查询。如where col1 = xx and col2 = xx;否则索引失效。
过滤条件要使用索引必须按照索引建立时的顺序依次满足，一旦跳过某个字段，则索引后面的字段将无法使用。
- 对索引列计算、函数、类型转换（自动或手动）
以下场景会使索引失效：
计算：select * from tb1 where age+1=18;
函数：select * from tb1 where left(name, 3) = ‘abc’;
类型转换：select * from tb1 where name = 123; - 范围条件右边的列索引失效
select * from tb1 where col1 > 0 and col2 = ‘a’;
对于col1和col2来说，它们都存在索引。但是col1过滤条件使用了范围条件，则col2索引失效。
对于组合索引的创建，最好将范围条件列放在索引最后，即可避免范围条件失效问题。
- 不等于(<> / !=)索引失效
在覆盖索引情况下，此条规则可能不成立 - is null可使用索引 is not null不能使用索引
同理，not like也无法使用索引（索引使用的是等值匹配）
最好在设计数据表的时候将字段设置为NOT NULL约束。或者给定默认值，如int类型默认值为0，字符类型默认值为空字符串‘’
- like以通配符%开头索引失效
开头无法确定，索引无法使用。
Alibaba开发手册：
禁止左模糊查询或全模糊查询，如有需要请使用搜索引擎。
- OR前后存在非索引列，索引失效
理解：有一列需要全表扫描时，另一列使用索引查询毫无意义。 - 字符集不同
不同的字符集进行比较前需要进行转换操作，这会使索引失效。
建议：数据库和表的字符集统一使用utf8
九、主键的设计
建议尽量不要用跟业务有关的字段做主键。无法预测哪些业务字段会因为项目的业务需求而有重复或重用之类的情况出现。如：会员卡号会在回收后再次分配其他人而导致重用情况。
通过淘宝订单猜测淘宝的订单表主键ID设计如下：
时间 + 去重字段 + 用户ID后6位
这样的设计能做到全局唯一，且对分布式系统查询友好
推荐：
- 非核心业务，如日志等，主键设置自增ID
- 核心业务：主键设计至少应该是全局唯一且单调递增
十、数据库设计
1. 范式
Normal Form。
共六种常见范式，从低到高分别是：第一范式，第二范式，第三范式，巴斯-科德范式，第四范式，第五范式。
范式设计越高阶，冗余度就越低。同时高阶范式一定满足低阶范式要求，类似于高中生学历一定满足初中生学历。
1.1 第一范式（1NF）
第一范式确保表中每个字段值必须具有原子性，就是说每个字段的值是不可再拆分的。
但属性的原子性是主观的。例如”收货地址“这个字段，如果应用程序中需要分别处理收货地址的省市区部分（如：用于搜索），则有必要将其分开，否则不必。
1.2 第二范式
满足数据表里的每条数据记录，都是可唯一标识的。且所有非主键字段，都必须完全依赖主键，不能部分依赖。
举例1：
成绩表（学号，课程号，成绩）
在该表中，主键是学号+课程号。
对于以上信息，成绩表满足第二范式。
因为对于非主键字段“成绩“来说，单单一个学号信息并不能决定成绩，单单一个课程号也不能。只有学号+课程号才能决定成绩，所以(学号+课程号)->成绩是完全依赖关系，满足第二范式。
举例2：
比赛表(比赛编号，球员编号，姓名，年龄，比赛时间，比赛场地，球员得分)
主键是比赛编号+球员编号
对于以上信息，不满足第二范式。
因为球员编号就可以决定姓名和年龄 与比赛编号无关，而比赛编号可以决定比赛时间和比赛场地
对于违反第二范式可能带来的问题:
- 数据冗余：如果一个球员参加多场比赛，则姓名年龄就重复了n-1次；一场球赛也可以多个球员参加，则比赛场地，得分重复n-1次。
- 插入异常：如果我们需要添加一场新的比赛，但是暂时没有确定球员信息则无法插入。
- 删除异常：如果我们需要删除一个球员编号，也同时会把比赛信息删掉。
- 更新异常：如果我们需要更新一场比赛时间，则需要更新大批信息，否则数据无法同步。
为了避免以上问题需要重新设计表：
| 表名 | 字段 |
|---|---|
| 球员表 | 球员编号 姓名 年龄 |
| 比赛表 | 比赛编号 比赛时间 比赛场地 |
| 球员比赛关系表 | 比赛编号 球员编号 球员得分 |
1.3 第三范式（3NF）
要求数据表中所有非主键字段不能依赖于其他非主键字段。
举例1：
部门信息表（部门编号，部门名称，部门简介等）
员工信息表（员工编号，姓名，部门编号）
在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门信息有关的加入员工表了。
否则将违反第三范式。因为部门名称仅依赖于部门编号而不依赖于员工编号。
1.4 小结
优点：消除数据冗余。第三范式通常被认为在性能、拓展性和数据完整性方面达到最好的平衡
缺点：表太多太细，增加表连接，降低查询效率，连接查询也可能使索引失效。
范式仅是一个标准，实际开发中并不一定要符合这些标准。可能会因为性能和读取效率而违反范式化原则。
范式本无优劣之分，只有适用场景不同。
2. 反范式化
概念：违反范式规则
目的：提升读取效率和性能
举例：
- 商品流水表
| transid流水号 | itemno 商品编号 | num数量 | price价格 | balance金额 |
|---|
- 商品信息表
| itemno 商品编号 | name名称 | sep规格 |
|---|---|---|
| 两张表是符合第三范式要求的。但是现在我们经常查询商品流水表，同时需要获取商品名称，此时我们就需要在商品流水表中增加一个商品名称的冗余字段，以此减少连接JOIN查询提升读取性能。 |
缺点：
- 反范式化典型就是空间换时间
- 一个表中字段做了修改，另一个表中的冗余字段也需要同步修改，否则数据不一致
- 数据量小的情况下，反范式化不能体现性能优势，可能还会让数据库设计更复杂
- 若采用存储过程来支持数据更新删除等额外操作时，如果更新频繁会非常消耗系统资源
适用场景：
当冗余信息有价值或能大幅度提升查询效率时，才采用反范式化。
- 设置冗余字段的建议
① 这个冗余字段不需要经常修改
② 这个冗余字段每次查询不可或缺
- 历史快照的需要
比如在商城下单正常流程是用户获取到用户的收货地址表中的信息，选择收货地址再进行下单。若此时订单表设计的收货地址信息是关联收货地址表的话，若用户更新了收货地址，则发生了信息异常。所以我们在订单表中设置冗余字段来存储收货地址信息是非常有必要的，而每次发生的订单收货信息都属于历史快照。
十一、事务
1. 数据并发问题
按问题严重性从大到小排序：
脏写 > 脏读 > 不可重复读 > 幻读
1.1 脏写
概念：事务A修改了另一个未提交事务B修改过的数据，则发生脏写。
条件：当前有张用户表，存在一条数据id为1，name为张三。
| 事务A | 事务B | |
|---|---|---|
| ① | BEGIN | |
| ② | BEGIN | |
| ③ | update user set name = ‘李四’ where id = 1 | |
| ④ | update user set name = ‘王五’ where id = 1 | |
| ⑤ | COMMIT | |
| ⑥ | ROLLBACK |
A和B各开启一个事务，B先把数据更新为“李四”，A再把数据更新为“王五”并提交，此时事务B进行了回滚，那么事务A更新的数据也将不复存在，此时查看数据库，会发现虽然经过了两次update，但是数据库中的数据还是“张三”，这种现象就是脏写。
1.2 脏读
事务A读取到已被事务B更新但还未提交的数据，若此时事务B回滚数据，那么事务A读取到的数据就是临时且无效的。
| 事务A | 事务B | |
|---|---|---|
| ① | BEGIN | |
| ② | BEGIN | |
| ③ | update user set name = ‘李四’ where id = 1 | |
| ④ | select name from user where id = 1 | |
| ⑤ | COMMIT | |
| ⑥ | ROLLBACK | |
| 在事务A读取到事务B更新但是未提交的数据后，事务B回滚数据导致事务A相当于读取到了一个不存在的数据，这种现象就是脏读。 |
1.3 不可重复读
概念：对于两个事务A和B，A读取了一个数据后，B将该数据更新并提交，导致A第二次去读取该数据时发现与第一次读取到的数据不一致，这就发生了不可重复读。
| 事务A | 事务B | |
|---|---|---|
| ① | BEGIN | |
| ② | select name from user where id = 1（此时name为张三） | |
| ③ | update user set name = ‘李四’ where id = 1 | |
| ④ | select name from user where id = 1（如果此时name为王五，则发生不可重复读） |
我们在B中提交了几个隐式事务（隐式事务：语句结束，事务就提交），每次事务提交之后，如果事务A都能读到最新的值，这种现象就称为不可重复读
1.4 幻读
对于事务A和B，A从表中读取一个字段，此时事务B在该表中插入一些数据，之后如果事务A再次读取同一个表，就会多出几行数据，这种现象称为幻读。
| 事务A | 事务B | |
|---|---|---|
| ① | BEGIN | |
| ② | select * from user where age > 18;（读到【张三 19】的数据） | |
| ③ | insert into user(name, age) value(‘赵六’, 20) | |
| ④ | select * from user where age > 18;(若此时读到【张三 19，赵六 20】的数据，则发生幻读) |
事务A先根据age>18的条件读取user表中数据，得到name为张三 age为19的数据；之后事务B提交了一个隐式事务插入一条数据name为赵六，age为20；之后事务A再次以同样的条件age>18查询user表，得到了两条记录【张三-19，赵六-20】，多出几行数据，这种现象称为幻读。
2. 四种隔离级别
- READ UNCOMMITTED 读未提交：在该级别中，所有事务都可以看到其他事务未提交的数据。不可避免脏读、不可重复读、幻读
- READ COMMITTED 读已提交：在该级别中，一个事务只能看到其他事务已提交的数据，
是大多数数据库默认的隔离级别（但不是mysql默认），可避免脏读，不可避免不可重复读、幻读。 - REPEATABLE READ 可重复读
（mysql默认隔离级别）：一个事务A读取到一条记录后，另一个事务B对该行数据进行更新并提交，但是不影响事务A重新读取该行数据，在这种隔离级别下，事务A再次读取数据还是会读取到之前读取到的值。可避免脏读、不可重复读，但幻读仍然存在。 - SERIALIZABLE 串行化：单个线程执行，在一个事务执行期间不允许其他事务进行插入、删除、更新操作。可避免脏读、不可重复读、幻读。但是性能低下！
ps: √表示存在该种情况 ×表示不存在
| 隔离级别 | 脏写 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|---|
| READ UNCOMMITTED（读未提交） | × | √ | √ | √ | × |
| READ COMMITTED（读已提交） | × | × | √ | √ | × |
| REPEATABLE READ（可重复读） | × | × | × | √ | × |
| SERIALIZABLE（串行化） | × | × | × | × | √ |
因为脏写问题过于严重，所有隔离级别均避免了这种情况。
从上往下，隔离级别从小到大，级别越高，处理并发能力越差。
查看mysql的隔离级别：select @@transaction_isolation;
设置mysql隔离级别：set [session | global] transaction isolation level 隔离级别
可选值：
1.READ UNCOMMITTED
2.READ COMMITTED
3.REPEATABLE READ
4.SERIALIZABLE
或 set [session | global] transaction_isolation = ‘隔离级别’
可选值：
1.READ-UNCOMMITTED
2.READ-COMMITTED
3.REPEATABLE-READ
4.SERIALIZABLE
3. 事务日志
3.1 redo log 重做日志
作用：当发生宕机，在内存中被更新的数据未写入到磁盘中，重启服务后可以通过redo log来恢复这些丢失的数据。保证事务持久性。
好处：
- redo日志降低了数据刷盘频率
- redo日志占用空间非常小
存储表空间ID、页号、偏移量及需要更新的值，所需存储空间很小，刷盘快。
特点：
- redo日志是顺序写入磁盘的
- 事务执行过程中，redo log不断记录
组成：
- 重做日志的缓冲（redo log buffer）:位于内存中，是易失的
在服务器启动时就会向操作系统申请一块连续内存区域作为缓冲区，这片内存区域被划分成若干个redo log block，每个占用512byte大小。
参数设置：innodb_log_buffer_size 默认16m，取值范围0m~4096m
- 重做日志文件（redo log file)：保存在硬盘中，是持久的。用于恢复数据。
（windows系统 mysql安装目录下的data文件夹中）（linux中 存放在mysql安装根目录下）存在ib_logfile0和ib_logfile1两个文件，即重做日志文件。
REDO 整体流程
- 将数据从磁盘读取到内存中，事务对内存数据进行update更新。
- 生成一条重做日志并写入redo log buffer中，记录修改后的值
- 当事务commmit时，将redo log buffer中的内容刷新到redo log file中，对redo log file采用追加写的方式（记录）
- 定期将内存中已更改的数据刷新到磁盘上
注意：redo log buffer刷盘到redo log file的过程并不是真正意义上的刷新数据到磁盘上，只是刷入到文件系统缓存中（page cache：这是现代操作系统为了提高文件写入效率做的一个优化 属于操作系统的一部分），真正的刷盘 由系统决定。那么就存在一个问题，如果操作系统也宕机了呢？
由此，Innodb给出innodb_flush_log_at_trx_commit参数。用于控制redo log buffer中的数据何时刷入redo log file。
- innodb_flush_log_at_trx_commit=1：（默认）每次事务提交，都将进行同步刷盘。
- innodb_flush_log_at_trx_commit=0：事务提交不进行刷盘，由master thread每隔1s进行一次重做日志的同步）
- innodb_flush_log_at_trx_commit=2：每次事务提交只会把redo log buffer中的内容写入page cache, 由OS决定什么时候同步磁盘。
测试可知，效率从大到小排序：0 > 2 > 1，但安全性则反之。
3.2 undo log
特点：保证事务原子性（要么全部完成，要么全部失败）。在事务中更新数据的前置操作是要先写入一个undo log。
理解：每当我们需要改动表中数据时（如INSERT、UPDATE、DELETE），都需要把回滚时所需的东西记录下来，如
- insert：对于每个insert，innodb会完成一个delete记录
- delete：对于每个delete，innodb会完成一个insert记录
- update：对于每个update，innodb会执行一个相反的update将数据恢复
Mysql将这些为了回滚而记录的日志称为回滚日志 undo log。（select不存在undo log）
此外，undo log也会产生redo log。因为undo log也需要持久化。
作用：
- 回滚数据
- MVCC：Innodb中的MVCC是通过undo来完成的，当用户读取一条记录时，若该条记录已被其他事务占用，当前事务可以通过undo读取之前的行版本信息，实现非锁定读取。
3.3 生成过程
假设有两个值：A=1和B=2，然后将A修改成3，B修改成4。
以下仅作理解
| 步骤 | 操作 |
|---|---|
| ① | start transaction |
| ② | 将A=1记录到undo log中 |
| ③ | update A = 3 |
| ④ | 将A=3记录到redo log中 |
| ⑤ | 将B=2记录到undo log中 |
| ⑥ | update B = 4 |
| ⑦ | 将B=4记录到redo log中 |
| ⑧ | 将redo log刷到磁盘 |
| ⑨ | commit |
- 若1~8步骤过程中发生宕机，事务未提交，不影响磁盘数据
- 若8~9步骤过程中发生宕机，恢复之后可选择回滚也可以选择继续完成事务提交，将数据刷入磁盘
- 若在9之后宕机，内存映射中的数据还未刷到磁盘（刷盘策略导致），那么系统恢复之后，还可继续根据redo log把数据刷入磁盘
232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
