MySQL学习---进阶阶段
进阶阶段
目标：深入理解数据库设计与性能优化
数据库设计
范式
- 第一范式（1NF）：字段不可再分。
- 第二范式（2NF）：满足 1NF 且非主键字段完全依赖主键。
- 第三范式（3NF）：满足 2NF 且非主键字段不传递依赖主键。
- 反范式化设计（适当冗余以提高查询性能）。
ER图
- 实体：是存在于用户组织中的抽象的但有意义的“事物”
- 属性：是指实体集中所有试题所具有的共同特征
- 码：一般把能够唯一地标识实体集中每一个不同实体的一个或一组属性称为实体集的标识码。（其实这便是主码或叫做主键）
- 联系：实体之间的联系，是指实体集之间有意义的相互作用及相对应关系。
索引
索引概述
-
介绍
-
一种有序的数据结构
-
用来高效获取数据
-
-
演示
- 注：上述二叉树索引结构只是示意图，不是真实的索引结构
-
优缺点
-
优点
-
提高查询效率
-
提高排序效率
-
-
缺点
-
占用磁盘空间
-
降低更新效率
-
-
索引结构
-
MySQL的索引在第三层存储引擎层实现，不同的存储引擎有不同的结构
-
索引种类
-
B+Tree索引，最常见的索引类型（默认），InnoDB支持
-
Hash索引，哈希表实现，性能高能够支持精确匹配，但不支持范围查询
-
R-tree（空间索引）
-
Full-text（全文索引）
-
-
二叉树缺点
-
顺序插入时会形成一个链表，查询性能大大降低
-
大数据量情况下，层级较深，检索速度慢
-
-
红黑二叉树缺点
-
红黑树是一种自平衡的二叉查找树，是一种高效的查找树。
-
大数据量情况下，层级较深，检索速度慢
-
-
B-Tree（多路平衡查找树）
-
以一颗最大度数（max-degree)为5阶的b-Tree为例（每个节点最多存储4个key，5个指针）
- 树的度数指的是一个节点的子节点个数
-
插入过程
-
元素存储在key中，从左到右排序
-
指针在首尾和元素之间
-
从根元素开始判断，小于根元素找左子树，大于根元素找右子树，数据插入到叶子节点，根元素和中间元素是由叶子节点分裂上来的
-
满4个元素再插入元素时中间元素向上分裂，左右分成两部分
-
-
-
B+Tree
-
以一颗最大度数（max-degree)为4阶的b+Tree为例（每个节点最多存储3个key，4个指针）
-
特点
-
所有元素在叶子节点上
-
非叶子节点起索引作用
-
叶子节点形成单向链表
-
-
插入过程
- 满四个key再插入值发生裂变，中间元素向上裂变并在叶子节点复制一个元素，两个叶子节点之间形成指针
-
MySQL索引数据结构对经典的B+Tree进行了优化。在原有基础上增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问性能。
-
-
Hash索引
-
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
-
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。
-
特点
-
Hash索引只能用于对等比较(=，in)，不支持范围查询 (between，>，<，…)
-
无法利用索引完成排序操作
-
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引（不出现Hash碰撞的情况）
-
-
存储引擎支持
- 在MySQL中，支持hash索引的是Memory引擎，而lnnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
-
-
面试题：
-
为什么InnoDB存储引擎选择使用B+tree索引结构?
-
相对于二叉树，层级更少，搜索效率更高；
-
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页（固定16k）中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;B+树只能到叶子节点找数据，搜索效率稳定；B+树可以在相邻节点生成双向链表，便于范围搜索和排序。
-
Hash索引只支持等值匹配，相对Hash索引，B+tree支持范围匹配及排序操作。
-
-
索引分类
-
主键索引
- 表中主键创建的索引，唯一且非空
-
唯一索引
- 唯一约束创建，表中可以有多个可为空，但该数据列值不能重复
-
普通索引
- 快速定位数据，一张表可以有多个
-
全文索引
- 查找的是文本中的关键词，而不是比较索引中的值，可有多个
索引存储形式分类
-
聚集索引：数据存储与索引放到一块，索引结构的叶子节点保存了行数据，有且只有一个
-
主键
-
第一个唯一索引
-
自动生成rowid
-
-
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
-
InnoDB主键索引的B+Tree为多高呢？
-
假设：一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
-
高度为2 :
n*8 +（n+1）*6 = 16*1024,每个节点key的个数n约等于1170- 能够存储的数据量为：1171*16=18736
-
高度为3 :
1171*1171*16=2200万条记录 -
如果存储四五千万以上的记录，要考虑分库分表
-
-
索引语法
-
创建索引
- create [unique|fulltext] index index_name on table_name (index_col_name,…);
-
查看索引
- show index from table_name;
-
删除索引
- drop index index_name on table_name;
SQL性能分析
-
SQL执行频率
-
查看增、删、改、查SQL语句执行的频率
-
show [session|global] status like ‘Com___’
-
-
慢查询日志
-
记录了所有执行时间超过指定参数（long_query_time,默认10秒）
-
show variables like ‘long_query_log’; # 查看状态
-
开启，需要再MySQL的配置文件(/etc/my.cnf)中配置
-
# 开启MySQL慢日志查询开关
show_query_log 1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
查看慢查询日志信息：/var/lib/mysql/localhost-show.log
-
profile详情
-
每一条执行的SQL耗时情况。
show profiles;
-
通过have_profiling参数，能够看到当前MySQL是否支持profiling操作：
select @@have_profiling;
-
默认profiling是关闭的
set [session|global] profiling=1; #开启
-
-
explain执行计划
-
explain或desc查看SQL语句的执行计划，包括select语句执行过程中表如何连接和连接顺序。
-
id：SQL语句中表的执行顺序，id值不同越大越先执行，id值相同，从上到下。
-
select_type:select的类型
-
type：连接类型，避免all全表扫描
-
possible_key:可能用到的索引
-
key：实际用到的索引
-
-
索引使用
-
验证索引效率
-
联合索引使用规则
-
最左前缀法则：组合索引，必须包含最左边的列，从最左向右，不能跳过中间列，否则跳过之后的列会失效。
-
范围查询：组合索引，出现范围查询（>,<），范围查询右侧的列索引失效，在业务允许时，尽量使用>=,<=来规避
-
-
索引失效
-
不要在索引列进行运算操作，索引会失效
-
字符串类型字段，不加单引号索引会失效
-
尾部模糊匹配索引不会失效，头部进行模糊匹配索引会失效（前面加%）
-
用or分割开的条件，那么涉及的索引都不会被用到
-
如果MySQL评估走索引比全表扫描更慢，会放弃走索引
- is [not] null走不走索引要看数据分布情况
-
-
SQL提示
-
是优化数据库的一个重要手段，简单说就是在SQL语句中加入一些人为的提示来达到优化操作的目的
-
在from 表名后面加
-
use index(索引名) 优先使用该索引
-
ignore index(索引名) 忽略该索引
-
force index(索引名) 必须用该索引
-
-
-
-
覆盖索引
-
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引列都能找到），减少select *
-
using index condition：查找使用了索引，但需要回表查询数据
-
using where；using index：查询使用了索引，并且需要返回的列，在该索引列都能找到，所以不需要回表查询数据
-
-
-
前缀索引
-
当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
-
语法
create index ind_xxxx on table_name(column(n));
-
前缀长度
-
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email)/count() from 表名;
select count(distinct substring(email,1,5))/count() from 表名;
-
-
-
单列索引与联合索引
-
单列索引：即一个索引只包含单个列
-
联合索引：即一个索引包含多个列
-
在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引，避免回表查询。注意联合索引的顺序。
-
索引设计原则
-
针对于数据量较大（100w以上），且查询比较频繁的表建立索引
-
针对于常作为查询条件(where)、排序(order by)、分组 (group by) 操作的字段建立索引。
-
尽量选择区分度高（身份证、手机号）的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
-
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
-
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
-
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
-
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
事务
事务简介
事务是一组操作的集合，它是一个不可分割的工作单位，所有操作要么同时成功，要么同时失败。
注：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
事务操作
查看/设置事务提交方式
方式一：
select @@autocommit; # 默认为1，自动提交
set @@autocommit = 0; # 设置为手动提交
方式二：
start/begin transaction #开启事务
提交事务
COMMIT; #设置手动提交后，若成功执行所有DML语句，需执行提交事务才能成功
回滚事务
ROLLBACK; #设置手动提交后，若某DML语句执行失败，需执行回滚事务回到执行前
事务四大特性（ACID）
原子性（Atomicity）：事务是不可分割的最小操作单元，所有操作要么同时成功，要么同时失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation）：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的。（存放在磁盘中）
并发事务问题
问题：
脏读：一个事务读到另一个事务还没提交的数据
不可重复读：一个事物先后读取同一条记录，但两次读取的数据不同
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了
事务隔离级别
从上到下隔离级别越来越高，数据安全性越来越高，性能越来越低
– 查看事务隔离级别
select @@transaction_isolation
– 设置事务隔离级别
set [ session(当前会话) | global(所有会话) ] transaction isolation [read uncommitted | read committed | repeatable read | serializable]
read committed（RC）：一个会话事务提交之后另一个会话才能看到提交后的结果
repeatable read（RR）：在一个事务中执行两次相同的SQL结果是一样的
serializable ：在并发操作时只允许一个事务操作
注：MySQL默认隔离级别为Repeatable Read，Oracle默认隔离级别为Read committed
锁机制
概述
-
介绍
- 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
-
分类
-
MySQL中的锁，按照锁的粒度分为以下三类，粒度由大到小，性能由大到小:
-
全局锁:锁定数据库中的所有表。
-
表级锁:每次操作锁住整张表。
-
行级锁:每次操作锁住对应的行数据。
-
-
全局锁
-
介绍
-
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞（重、性能差）。
-
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
-
mysqldump -h远程IP -uroot -p123456 库名 > 路径 -
注：mysqldump在本地系统执行，不在MySQL系统执行
-
-
-
特点
-
数据库中加全局锁，是一个比较重的操作，存在以下问题:
-
如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
-
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。
-
-
在innoDB引擎中，我们可以在备份时加上参数–single-transaction 参数来完成不加锁的一致性数据备份。
-
表级锁
-
介绍
- 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
-
分类
-
表锁
-
表共享读锁（read lock）
- 读锁不会影响其他客户端的读，会阻塞其他客户端写
-
表独占写锁（write lock）
- 写锁即会堵塞其他客户端的读，又会堵塞其他客户端的写
-
语法
-
加锁：lock tables 表名…read/write
-
释放锁：unlock tables / 客户端断开连接
-
-
-
元数据锁（meta data lock，MDL）
-
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
-
为了避免DML与DDL冲突，保证读写的正确性
-
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)，当对表结构进行变更操作的时候，加MDL写锁(排他)。
-
查看元数据锁
-
-
意向锁
-
为了避免DML在执行时，加的行锁与表锁的冲突，在InnDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减 少表锁的检查。
-
意向共享锁(IS): 由语句 select …lock in share mode添加。
- 与表锁共享锁(read)兼容，与表锁排它锁(write)互斥
-
意向排他锁 (IX): 由insert、update、delete、select … for update 添加。
- 与表锁共享锁(read)及排它锁 (write)都互斥。意向锁之间不会互斥。
-
可以通过以下SQL，查看意向锁及行锁的加锁情况
-
-
行级锁
-
介绍
-
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
-
lnnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:
-
行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
-
间隙锁(Gap Lock):锁定索引记录回(不含该记录)，确保索引记录间酿不变，防止其他事务在这个间隙进行insert，产生幻读，在RR隔离级别下支持
-
临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
-
-
-
行锁
-
InnoDB实现了以下两种类型的行锁:
-
共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
-
排他锁(X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
-
-
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，innoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读
-
针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
-
innoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。
-
-
可以通过以下SQL，查看意向锁及行锁的加锁情况：
-
-
间隙锁/临键锁
-
默认情况下，innoDB在 REPEATABLE READ事务隔离级别运行，innoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
-
索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁
-
索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁
-
索引上的范围查询(唯一索)，会访问到不满足条件的第一个值为止。
-
-
注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
-
扫一扫
859
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
