高级会员
- 威望
- 371
- 贡献
- 478
- 热心值
- 0
- 金币
- 19
- 注册时间
- 2020-3-29
|
1. MySQL的索引实现原理？
在MySQL中，索引属于存储引擎级别的概念，myisame和innodb索引底层实现都是用的B+树，但是实现方式却完全不同。
2. myisam和innodb中索引的区别
• MyISAM的索引与数据分开存储
• MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别
• InnoDB的聚集索引和数据行统一存储
• InnoDB的聚集索引存储数据行本身，普通索引存储主键
• InnoDB一定有且只有一个聚集索引
• InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK
3. 聚集索引和非聚集索引的区别？
• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
• 非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
• 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
4. 数据库索引 主键和唯一索引有什么区别
• 主键为一种约束，唯一索引为一种索引，本质上就不同。
• 主键在表中只能有一个，唯一索引可以有多个。
• 主键创建后一定包含唯一性索引，而唯一索引不一定就是主键。
• 主键不能为null,唯一索引可以为null.
• 主键可以被其它表引用，唯一索引不能。
• 主键和索引都是键，主键是逻辑键，索引为物理键，即主键不实际存在。
5. B树和B+树的区别，为什么MySQL要使用B+树？
• B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
• B+树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录，由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
6. 为什么不用普通查找树来替代B+树呢？
从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？
因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。
7. MySQL索引b+树的一个节点存了多少数据，怎么规定大小，与磁盘页对应
• InnoDB存储引擎的最小储存单元——页（Page），一个页的大小是16K
• 可以通过innodb_page_size设置页的大小
8. 数据库索引，B+树的特性和建树过程
最左匹配特性，b+树是采用从左到右的顺序来建树的
9. MySQL 聚簇索引，非聚簇索引有什么区别？
• 聚簇索引是叶子节点有数据的，非聚簇索引（二级索引）的叶子节点是指向数据的地址
• 一张表中只能由一个聚簇索引
• 聚簇索引的优点
○ 聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
○ 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。
• 聚簇索引的缺点
○ 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
○ 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题
○ 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中
10. MySQL 的唯一索引，主键索引，聚簇索引有什么区别？
• 一般来说，将通过主键作为聚簇索引的索引列，也就是通过主键聚集数据。但是呢，并不能说聚簇索引就是主键索引，也不能说聚簇索引就是唯一索引，因为聚簇索引可以有重复值。
• 唯一索引是在表上一个或者多个字段组合建立的索引，这个（或这几个）字段的值组合起来在表中不可以重复。一张表可以建立任意多个唯一索引，但一般只建立一个。
• 主键是一种特殊的唯一索引，区别在于，唯一索引列允许null值，而主键列不允许为null值。一张表最多建立一个主键，也可以不建立主键。
• 严格来说，聚簇索引不一定是唯一索引，聚簇索引的索引值并不要求是唯一的，唯一聚簇索引才是！在一个有聚簇索引的列上是可以插入两个或多个相同值的，这些相同值在硬盘上的物理排序与聚簇索引的排序相同，仅此而已。
11. MySQL创建索引的原则？
• 对于查询频率高的字段创建索引
• 对排序、分组、联合查询频率高的字段创建索引
• 索引的数目不宜太多
• 若在实际中，需要将多个列设置索引时，可以采用多列索引
• 尽量使用前缀来索引
• 选择唯一性索引
• 尽量使用数据量少的索引
12. 为什么选用自增量作为主键索引
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引的顺序和磁盘中数据的存储顺序是一致的，如果主键不是自增id，它会不断地调整数据的物理地址、分页。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
13. 为什么要建索引（索引的优点）？
• 通过创建唯一索引，可以保证数据库表中每一行的唯一性
• 可以大大加快数据的检索速度，这也是创建索引的最主要索引
• 可以加速表与表之间的连接
• 在使用分租和排序子语句进行数据检索时，同样可以显著减少查询中分组合排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
14. 什么样的字段需要建索引，建索引的时候一般考虑什么？
• 表的主键、外键必须有索引；
• 数据量超过300的表应该有索引；
• 经常与其他表进行连接的表，在连接字段上应该建立索引；
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
• 索引应该建在选择性高的字段上；
• 频繁进行数据操作的表，不要建立太多的索引
15. 索引什么时候会失效变成全表扫描
• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
• or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 组合索引，不是使用第一列索引，索引失效
• 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描
• 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
• 对索引字段进行计算操作、字段上使用函数
• 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效
16. 在工作中，SQL语句的优化和注意的事项
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
• 不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段
• 尽量避免大事务操作，提高系统并发能力
17. 优化数据库的方法，从sql到缓存到cpu到操作系统，知道多少说多少
• 数据库表设计时，就需要考虑对应字段的类型、索引
• 选择合适的存储引擎
• 数据库高可用、高性能，可以考虑主从复制、读写分离，增加缓存
• 数据量大了，考虑分库分表分区
• 开启慢查询日志
18. mysql如何获取慢SQL，以及慢查询的解决方式
• 数据库中设置SQL慢查询
○ 在my.ini文件中配置慢查询时间，并开启慢查询功能
○ 导出慢查询文件进行分型
• 分析慢查询日志
○ 利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句
○ type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
○ rows：显示需要扫描行数
○ key：使用的索引
19. 数据库的事务，四个性质说一下，分别有什么用，怎么实现的？
数据库事务的四个基本性质（ACID）
• 原子性（Atomicity）：事务的原子性是指事务中包含的所有操作要么全做，要么全不做
• 一致性（Consistency）：在事务开始以前，数据库处于一致性的状态，事务结束后，数据库也必须处于一致性状态
• 隔离性（Isolation）：事务隔离性要求系统必须保证事务不受其他并发执行的事务的影响，也即要达到这样一种效果：对于任何一对事务T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前已经结束，要么在 T1 完成之后才开始执行。这样，每个事务都感觉不到系统中有其他事务在并发地执行。
• 持久性（Durability）：一个事务一旦成功完成，它对数据库的改变必须是永久的，即便是在系统遇到故障的情况下也不会丢失。数据的重要性决定了事务持久性的重要性。
20. 什么是更新丢失、脏读、幻影读、不可重复读？
• 更新丢失：和别的事务读到相同的东西，各自写，自己的写被覆盖了
• 脏读：有事务A和B，A读取了B未提交的数据
• 不可重复读：有事务A和B，A负责读取，B负责写入，A连续读的过程中B写入了一次，A前后两次读出来的数据不一样
• 幻影读：有事务A和B，A修改表内数据的过程中，B向表内插入了一条数据，A修改完后发现数据并没有被全部修改完
21. MySQL的事务隔离级别，分别解决什么问题？
• 读未提交（READ UNCOMMITTED）：存在脏读、不可重复读、幻读
• 读已提交（READ COMMITTED）：解决了脏读，存在不可重复读、幻读
• 可重复读（REPEATABLE READ）：innodb存储引擎解决了不可重复读、幻读
• 序列化（SERIALIZABLE）：此级别下由于是一个事务执行完成，另一个事务才能开始执行，事务级别最高，解决了脏读、不可重复读、幻读，但是效率低
Innodb默认的隔离级别是RR（Repeatable Read）级别
22. 数据库隔离级别，每层级别分别用什么方法实现
• 读未提交（READ UNCOMMITTED）：所有的读不加锁，读到的数据都是最新的数据，性能最好，所有的写加行级锁，写完释放。
• 读已提交（READ COMMITTED）：写用排它锁，读是用MVCC机制，读当前版本，写的时候生成新版本，所以当前被锁住的版本不会出现脏读，但是会出现不可重复读(另一次读也拿到了被写过的最新版本。)
• 可重复读（REPEATABLE READ）：MVCC+间隙锁
• 序列化（SERIALIZABLE）：读写都会加锁
23. mysql实现事务的原理(MVCC)
MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
24. 数据库崩溃时事务的恢复机制
• 事务日志可以提高事务的效率。使用事务日志，存储引擎在修改表的数据时，只需要修改其内
|
|
发表于 2021-4-13 21:58
