MySQL_系数索引-CSDN博客

本文链接：https://blog.csdn.net/weixin_45519487/article/details/108490620

1、数据库的三大范式

第一范式：每个列都不可被拆分。

第二范式：在第一范式的基础上，非主键只完全依赖于主键，不依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

2、如何设计一个关系型数据库

首先分为两大模块：

（1）存储模块：存储数据，类似于文件系统。将数据持久化到存储设备中。

（2）程序实例模块：对存储的数据进行逻辑化的管理。

存储管理：将数据逻辑关系转化为物理存储关系，减少IO次数，通过块或者页的方式来存储数据。
缓存机制：利用内存和数据的局部性原理（内存中某块数据被访问，那么它相邻的数据很可能也会被访问），优化执行效率。
SQL解析：解析SQL语句，提供可读的SQL语句让外界操作数据库，也将SQL语句缓存在缓存中，提高效率。
日志管理：保存数据存储记录，方便查询和恢复。
权限划分：提供给不同用户私密空间，支持多用户管理。
容灾机制：在数据库不能正常响应时，做到如何恢复，以及恢复到什么程度。
索引管理：提升数据库查询的速度。
锁管理：让数据支持多线程访问，支持并发。

3、为什么使用索引

1.首先要说到全表扫描：

数据库存储数据的最小单元块或者页，是由多行组成的。（整个表就是多个块或页）我们把这些块或者页加载到内存。然后每个块或者页去轮询，找到目标返回。这适合数据比较小的情况。

2.索引的灵感来自于字典，查字典可以通过偏旁部首等。查数据我们就可以通过主键、唯一键、普通键（就是我们说的关键字）。除此之外，我们还要将这些关键字以一种友好的形式组织起来，就是我们说的索引的数据结构。例如建立二叉查找树进行二分查找，建立B-Tree结构、B+Tree结构（MySQL）、Hash结构进行查找。

4、索引的数据结构

（1）二叉查找树索引

查询效率O(logn)

缺陷：

在插入可能导致树变成线性表，查询效率降低为O(n)

查询过程中，遇到一个节点需要进行一次IO，如果树的高度过大将导致多次IO。营销那个查询效率。

（2）B_Tree

根节点最少有两个孩子。
每个节点最多有m个孩子
除根节点和叶子节点外，其他节点取ceil（m/2）个孩子。
所有终端叶子节点都是同一层。
关键字自左至右，升序排列。
每个存储块中，主要包含关键字和指向孩子的指针。
b tree 把数据文件存在非叶子节点上，检索时可能在任意一个非叶子节点结束。

阶是每个节点上最多的关键字+1，此处是3阶。

8/12 26/30 为节点中的关键字，也为概念中的K

（3）B+Tree

B+Tree的定义：

非叶子节点的子树指针和关键字个数相同。
非叶子节点的子树指针p[i]，指向关键字值[k[i],k[i+1]]的子树，大于等于K【i】小于k[i+1]。
非叶子节点仅用来做索引，数据都保存到叶子节点中。
所有的叶子节点均有一个链表指针指向下一个叶子节点。

B+Tree适合做索引的原因

B+树的磁盘读写代价低：程序运行，往往最耗时的是IO操作，B+树内部没有指向关键字的指针，只存放索引，因此节点比B数更少，IO操作就少。
B+树的查询效率更加稳定：数据存放在叶子节点。即每次查询都需要经过根节点到叶子节点的查询路径，时间复杂度为O(logn)
，比较稳定。
B+树更有利于数据库的扫描：数据只存放在叶子节点，而且有顺序，可以更好的查询数据范围。

（4）hash索引

hash索引，根据hash函数的运算，只需经过一次定位就可以找到所需数据在的桶。

B+Tree索引根节点-->非叶子节点---->叶子节点（稳定查询）

理论上hash索引比B+Tree快。

hash索引的缺点：

只能满足“=” IN 不能范围查找
不能利用部分索引键查询
不能用来避免数据的排序：因为hash索引中存放的是hash运算之后的值，hash值大小关系不一定和运行前的键值完全一致（不同key的可能有相同的hash值），数据库无法利用索引的数据来避免任何排序运算。
不能避免表扫描：不同的可以可能有相同的hash值，也就说索引可能对应多个记录，这些记录仍然需要进行扫描。
遇到大量hash值相等的情况后，性能不一定比B+Tree索引高：极端情况下所有的key对应同一个hash值，就变成了线性的了

5、密集索引和系数索引

密集索引文件中的每个搜索码值都对应一个索引值。（叶子节点保存的不仅仅是键值，还保存了为了同一行记录的其他列的信息，由于密集索引决定了表的排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引）

稀疏索引文件只为索引码的某些值建立索引项。（叶子节点只保存了键位信息，以及该行数据的地址，（有的仅保存键位信息和主键），定位到叶子节点之后，需要地址或者主键信息定位到数据）

InnoDB有且仅有一个密集索引，它密集索引的选取规则：

若一个主键被定义，该主键作为密集索引。
若没有主键被定义，该表的第一个唯一非空索引则作为密集索引。
若不满足以上条件，innoDB内部就会生成一个隐藏主键（密集索引）。

为什么InnoDB非要有主键索引？

因为非主键索引存储相关键位和其对应的主键值，包括两次查找。

InnoDB和Myisam索引的区别

InnoDB采用的密集索引+ 稀疏索引，主键索引可以直接找到叶子节点中的数据，辅助索引需要先找到主键索引，再通过主键B+Tree找到数据。即InnoDB数据和索引是存放在一个文件的。

Myisam全部采用稀疏索引，根据主键和辅助键的索引都只能找打一个地址信息，要在根据这个地址信息去另外一个文件中寻找数据，即Myisam的索引和数据是分开的。

6、如何定位并优化慢查询sql

1.根据慢日志定位慢查询sql

show variables like ‘%quer%’ :查询出一些相关的变量，如slow_query_log （慢日志）看是否关闭，若关闭需要将其打开，slow_query_log_file （记录慢日志，MySQL都写入这里）、long_query_time（每次执行SQL花费多长时间就算慢日志，是的话就会记录到slow_query_log_file中）。
show status likes ‘%slow_queries%’ :查询出系统的状态，例如slow_queries（即慢查询的数量）。
set global slow_query_log=on : 打开全局变量。
set global long_query_time=1 ：设置慢查询的时间为1秒
sudo vim /uer/local/mysql/data/VM_33_68_centos-slow.log：查看被记录到满日志里面的日志。

2.使用explain等工具分析SQL

语法：explain 慢SQL语句

Explain关键字：

type：当出现index或all的时候，就表示本次查询是全表扫描，因此是慢查询语句，需要优化。
extea：用于获取详细信息来辅助我们了解语句的执行方式。

3.修改SQL或者尽量让SQL走索引

7、联合索引的最左匹配原则

最左前缀匹配原则，非常重要的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配。例如a=3 and b=4 and c>5 and d=6 ，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）顺序的索引就都可以用到，a,b,d的顺序可以任意调整。
= 和 in可以乱序，比如a=1 and b=2 and c=3 建立（a,b,c）索引就可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

MySQL创立复合索引的规则是首先会对复合索引的最左边第一个索引字段的数据进行排序，在第一个字段的基础上，对第二个字段进行排序，实现类似于order by 字段1，order by 字段2，这样的一个排序规则。所以第一个索引绝对是有序的，第二个字段是无序的，因此使用第二个字段进行条件判断是用不到索引的，这就是MySQL强调联合索引的原因。

8、索引是建立的越多越好吗？

不是，因为：

数据量小的表不需要建立索引，建立会增加额外的索引开销。
数据变更需要维护索引。因此更多的索引意味着更多的维护成本。
更多的索引意味着也需要更多额空间。

9、MyISAM与InnoDB的锁模块之间的区别

对于myisam,当查询数据的时候，会自动加上一个表级的读锁；对数据进行增删改的时候，会为表加上一个表级别的写锁。当读锁未被释放的时候，要想为表加上写锁，就要等待读锁释放之后。

如何显示的给表加/释放读写锁呢？

lock tables person_info_myisam read/write;

unlock tables;

读锁也叫共享锁，多个session能够同时获取，同时读取数据，不会被阻塞。写锁也叫排它锁，与其他所有的锁互斥。

myisam默认是表级锁，不支持行级锁，表级锁会锁着整张表。

innoDB默认是行级锁的。支持表级锁。InnoDB在使用索引的情况下，用的是行级锁，没有索引的情况下，用的表级锁。

MySQL是默认提交事务的。innoDB 用的是二段锁，二段锁就是加锁和解锁是分两个步骤进行的，即先对同一个事务中的一批操作，分别加锁，commit的时候，在对事务里面加上的锁进行统一的解锁。而当前commit是自动提交的，看起来和myisam没什么区别，set commit=0，关闭自动提交，设置仅对当前的session的生效，其他的session仍然是自动提交的。

MyISAM适合的场景

频繁执行全表count语句。
对数据进行增删改的频率不高，查询非常频繁的时候：因为增删改会涉及到锁表操作，
没有事务。

InnoDB适合的场景：

数据增删改都相当频繁：增删改只是某些行被锁，避免了阻塞。
可靠性要求补比较高，支持事务的操作。

数据库锁的分类;

按锁的粒度来划分。可分为表级锁、行级锁、页级锁。
按所级别划分：可分为共享锁和排它锁。
按加锁方式划分：分为自动锁、显示锁。
按操作划分：DML锁（对数据进行操作的锁，）、DDL锁（对报结构进行变更的）
按使用方式划分：乐观锁（认为数据不会发生冲突，在数据进行更新时，才会对数据的冲突进行检测，发现冲突就返回用户的错误信息，一般的实现就是记录数据的版本（版本号、时间戳））、悲观锁（互斥锁就是悲观锁的一种）

10、数据库事务的四大特性（ACID）

原子性（atomic）：要么都做，要么都不做。
一致性（Consistency）：事务从一个一直状态转化为另一个一直状态。应满足完整性协议。
隔离性（Isolation）：多个事务并发执行时，互不影响，
持久性（Durability）：事务一旦提交对数据库的修改永久保存。

11、事务的隔离级别以及各级别下的并发访问问题

（1）事务并发访问引起的问题以及如何避免

更新丢失：一个事务的更新覆盖了另一个事务的更新。解决方法：MySQL所有事务隔离级别在数据库层面上均可避免。

脏读：一个事务读到另一个事务未提价的数据（READ-uncommitted级别会发生）。解决方法：READ-Committed事务隔离级别上可避免。

查看事务的隔离级别： select@_@tx_isolation

设置事务的隔离级别：set session transaction isolation level read uncommitted

不可重读：（READ-Committed隔离级别下会发生）事务A多次读取数据，事务B在事务A读取的过程中对数据进行了更新并提交，导致事务A多次读取的数据不一致。解决方法：Repeatable=read事务隔离级别上可避免。

幻读：事务A读取当前若干行数据，事务B以插入或者删除的方式修改事务A的隔离集，导致事务A多次读取同一数据时的结果不一致。Serializable事务隔离级别可以避免。

不可重复读与幻读比较相似，不可重复读重在对同一数据的修改，幻读则重在插入和删除数据。

隔离级别越高，对性能影响的越大。

12、InnoDB可重复读隔离级别下如何避免幻读的。

（1）表象：快照度（非阻塞读）---伪MVCC

当前读和快照读

当前读就是加了锁的增删改查语句。读取的是记录的最新版本，读取的时候保证其他事务不能对数据进行修改。例如：select....lock in share mode, select ...for update ， update ，delete， insert。

快照读：不加锁的非阻塞读，读到的数据不一定是最新版本，例如：select

RC、RR级别下的InnoDB的非阻塞读（快照度）如何实现？

数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段：DB_TRX_ID事务ID，标识对本行数据最近一次的更新（增删改）；DB_ROLL_PTR，回滚指针，指向回滚日志undo log的一条记录，一次更新对应一条undo log记录；DB_ROW_ID：新行插入，产生一个自增ID。
undo log ：回滚日志，存储各个老版本的数据，有undo链串联起来含insert undo log和update undo log
read view：决定当前数据看到的是哪个版本。

（2）内在：next-key锁（行锁+gap锁）

行锁：

Gap锁：

gap锁（间隙锁），也就是在查询条件周围数据加锁（查询条件部分命中，非唯一索引或者不走索引）。例如：select name from table where id in （5,7,9）lock in share（7不存在，保证了条件部分命中），当插入或者修改id<5 或者id >9的数据时，不会受到影响，当在（5,9）范围内，则会被gap锁阻塞。

对主键索引或者唯一键索引会用gap锁吗？