知识点总结之MySQL(1)

harvey_yh
185 阅读11分钟

知识点题总结之MySQL(1)

  • 什么是数据库事务、数据库事务的四个特性是什么。

定义：事务其实是一系列数据库操作，为了保证操作的原子性，这些操作要么都被执行，要么都不执行。

四个特性（ACID）：

  • 1.原子性（Atomicity）：事务中的所有操作，要么全部执行，要么都不执行。
  • 2.隔离性（Isolation）：事务间的数据库操作互不影响，比如说：A向B同时进行两次金额为5元的转账，可以看成两个事务，每个事务都由 读取A账户余额到内存中 --->减去转账金额 ---> 修改后的余额写到磁盘 --->读取B账户余额到内存中 --->加上转账金额 ---> 修改后的余额写到磁盘。假如事务T1的read（A）操作读完之后，紧接着是事务T2的read（A），那么就会出现问题，因为按理说应该是T1事务结束后，A账户少5元，B账户多5元，在执行T2，但是若如上面两个事务穿插着执行，那么最后的结果有可能是A只少了5元，而B多了十元。这不符合现实场景。所以多个事务并发执行时，一个事务的执行不应影响其他事务的执行，这个规则被称之为隔离性。
  • 3.一致性（Consistency）：总是从一个一致性的状态转换到另一个一致性的状态。
  • 4.持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
  • 请分别举例说明幻读和不可重复读、并描述一下它们之间的区别。

不可重复读：如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读。通俗的讲，一个事务范围内，多次查询某个数据，却得到不同的结果。

幻读：如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读。

区别：那对于先前已经读到的记录，之后又读取不到这种情况，其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

  • MySQL 的隔离级别有什么，默认隔离级别是什么。

READ UNCOMMITTED： 未提交读，可能发生脏读、不可重复读和幻读问题。
READ COMMITTED： 已提交读，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
REPEATABLE READ： 可重复读，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
SERIALIZABLE： 可串行化，各种问题都不可以发生。

MySQL的默认隔离级别为REPEATABLE READ。

  • 为什么要使用索引。

使用索引能够减少存储引擎需要扫描的数据量（也就是页的数量），通过索引能够避免全表查询这种低效率的查询方式（不绝对，个别时候全表查询速度比索引查询速度快，所以建立一个好的索引是至关重要的）。主要的索引方式是B+树，B+树索引可以将随机的I/O转为顺序的I/O。也可以帮助我们进行排序以避免以避免使用临时表。当然，mysql的一切改进都是为了两个目的：1.加快数据库操作的速度。2.为了节省空间。

  • 索引这么多优点，为什么不对表中每个字段都创建索引呢。

对于每个索引都需要建立一颗B+树，每个字段都贱索引会大大加大磁盘空间的成本，会增加数据库服务器写入操作的成本。而且每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。其次，比如我们查询一条记录，MySQL的查询优化器会分别计算用所涉及的的索引来查询这条语句所带来的成本，经过比较，选择成本最小的索引来执行数据库操作。所以索引越多，那么查询优化器所要做的工作（选择时间）就越多。

  • 索引是如何提升查询速度的。

索引就是通过排序，然后在查找的时候应用二分查找等比较高效的算法，所以查询效率比较高。

  • 请说出你知道的索引失效的几种情况。
  1. 条件中有or；
  2. 复合索引未用左列字段；
  3. like以%开头；
  4. 存在索引列的数据类型隐形转换；
  5. where中索引列有数学运算；
  6. where中索引列使用了函数；
  7. 如果mysql觉得全表扫描更快时（数据少）；
  • 什么是聚簇索引与非聚簇索引。

聚簇索引：索引列为主键的索引。聚簇索引建立的B+树中存放的表中的所有记录。并且一个表只有一个聚簇索引。（注：InnoDb主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。）

非聚簇索引：也叫二级索引，由非主键列建立的索引，该索引建立的B+树是由按索引列大小的顺序构建，其次按照主键的大小排序，所以二级索引中值存放索引列和主键的信息，并不是表的所有记录信息。

  • MySQL 索引主要使用的数据结构有哪些。

主要的就两个：B+树，Hash。另外还有B树,平衡二叉树等。

  • 什么是覆盖索引、请举例说明。

为了彻底告别回表操作带来的性能损耗，最好在查询列表里只包含索引列，这样的方式叫覆盖索引。也就是说SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

SELECT name, birthday, phone_number FROM person_info WHERE name > 'lisi' AND name < 'Zhangsan'

（有个表叫person_info，其中有个联合索引索引idx_name_birthday_phone_number (name, birthday, phone_number)）
因为我们只查询name, birthday, phone_number这三个索引列的值，所以在通过idx_name_birthday_phone_number索引得到结果后就不必到聚簇索引中再查找记录的剩余列，这样就省去了回表操作带来的性能损耗。

  • 谈谈你对最左前缀原则的理解。

当建立一个联合索引时，比如上面说的idx_name_birthday_phone_number，当你想要使用这个索引时，你的where里面必须按照从左到右的顺序写查询条件：
比如：

SELECT * FROM person_info WHERE name = 'harvey'
SELECT * FROM person_info WHERE name = 'harvey' and age = 18
SELECT * FROM person_info WHERE name = 'harvey' and age = 18 and phone_number = '13837981231'

上面这三种方式都可以用到该索引，因为它符合最左前缀原则。

SELECT * FROM person_info WHERE age=18 SELECT * FROM person_info WHERE phone_number = '13837981231' and age=18

像上面这种就不能使用该索引，因为它不是最左匹配优先。

  • MySQL 中 InnoDb 和 MyISAM 有什么区别。

这两个都是MySQL比较常见的存储引擎，InnoDb是MySQL默认的存储引擎，区别如下：

  1. InnoDb支持事务，而MyISAM不支持事务。
  2. InnoDb支持外键，MyISAM不支持。
  3. 从锁的方面看，InnoDb支持行锁，MyISAM只支持表锁。
  4. InnoDB是聚集索引，使用B+树作为索引结构，数据文件是和（主键）索引绑在一起的，所以需要要拥有主键。MyISAM是非聚集索引，所以可以没有主键，也是使用B+树作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。
  5. InnoDb不保存表的具体行数（只有一个大概值），MyISAM保存具体的行数。
  6. Innodb不支持全文索引，而MyISAM支持全文索引。
  7. MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
  • 如何用 explain 分析 SQL 执行效率，explain 显示的字段具体解释下。

explain select * from t1; （explain +具体数据库操作；）

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，而我们可以用EXPLAIN来查看执行计划。

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息
  • 请举出可能形成数据库死锁的原因、如何能避免死锁。
  1. 系统资源不足。
  2. 进程运行推进的顺序不合适。
  3. 资源分配不当等。
    如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
  • 互斥条件：一个资源每次只能被一个进程使用。
  • 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
  • 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
  • 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
    这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

避免死锁方法：
下列方法有助于最大限度地降低死锁：

  1. 按同一顺序访问对象。
  2. 避免事务中的用户交互。
  3. 保持事务简短并在一个批处理中。
  4. 使用低隔离级别。
  5. 使用绑定连接。
  • MyISAM与InnoDb索引的实现方式有何不同。

MyISAM: data存的是数据地址。索引是索引，数据是数据，索引和数据是分开存储的。索引放在XX.MYI文件中，数据放在XX.MYD文件中，所以也叫非聚集索引。

InnoDb: data存的是数据本身。索引也是数据，索引和数据是一起存储的。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。

  • 与 B-Tree 相比，B+Tree 有什么不同。

B树： 每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

B+树： 只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。 在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

  • 为什么 B+Tree 更适合做文件索引，为什么不用 AVL 树或者红黑树做索引。

B+树

  • 索引下推。

举个例子：
对于user_table表，我们现在有（username,age）联合索引 如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，如下：

select * from user_table where username like '张%' and age <= 10

该查询有两种执行可能：

  1. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足年龄小于等于10的用户数据。
  2. 根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。

很明显，第二种方式回表的记录要少很多，所以InnoDb优先采用这种方式来查询数据，这种方式就是索引下推。MySQL是默认启动索引下推的。

注：
1.InnoDb的表，索引下推只能用于二级索引。
2.索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

avatar
文章
阅读
粉丝