Day08_MySQL

一、索引

1.什么是索引?

索引是一种数据结构,可以帮助我们快速的进行数据的查找。

1.MySQL的索引

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。MySQL的BTree索引使用的是B树中的B+Tree。

1.Hash索引和B+树索引有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.
B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

那么可以看出他们有以下的不同:

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致，Hash值是无序的所以就不能支持范围查询.
而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

• hash索引不支持使用索引进行排序,原理同上.
• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引
  等)的时候可以只通过索引完成查询.

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

hash无序，不支持范围查找、不支持排序；
hash碰撞，不稳定

1.有了解过“回表”的概念吗？什么情况下会出现“回表”？

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

当查询的字段在二级索引上没有的时候，就需要“回表”在主键索引上再查一次。

表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句

SELECT * FROM tbl WHERE a=1

这样不会产生回表，因为所有的数据在a的索引树中均能找到

SELECT * FROM tbl WHERE b=1

这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。

1.MySQL 什么时候适合创建索引，什么时候不适合创建索引？

有了索引会使得它的查询速度变快，但是做数据更新（update、delete、insert）时就会变慢

1、什么时候适合创建索引

1）主键自动建立唯 一 索引
2）查询中与其它表关联的字段，外键关系建立索引
3）频繁作为查询条件的字段应该创建索引
4）频繁更新的字段不适合创建索引，因为每次更新不单是更新了记录还会更新索引
5）查询中排序的字段，排序字段若通过索引法访问将大大提高排序速度
7）查询中统计或者分组字段，给它们用上索引也会大大提高

总结：主键、外键；多查少写的字段；where后面用得着的字段（排序、统计、分组的字段）会使得检索排序更快

小常识：排序（Order By）更消耗性能呢还是分组（Group By）更消耗性能呢？分组更消耗性能，因为它分组时里面已经做了一次排序了。

2、什么时候不适合创建索引

1）表记录太少
2）经常增删改的表
3）注意，如果某个数据列包含许多重复的内容，为它建立索弓|就没有太大的实际效果。

1.在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?

• 当mysql分析全表扫描比使用索引快的时候不使用索引.
• 列参与了数学运算或者函数（select * from single_table where age > 11;像这种只能全表扫描啊）
• 若模糊查询以%开头，不使用索引（比如人名like ‘%慧’，这种也只能全表扫描啊）.

以上情况,MySQL无法使用索引.

1.为什么要尽量设定一个主键?

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

1.分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
• 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
• 利用 redis 生成 id :性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

1.主键使用自增ID还是UUID?

推荐使用自增ID,不要使用UUID.

如果主键索引是自增ID,那么只需要不断向后排列即可保证有序性,如果是UUID,不仅太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

总之,在数据量大一些的情况下,用自增主键性能会好一些.

1.key和index的区别

1.key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。

• primary key 有两个作用，一是约束作用（constraint)，用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
• unique key 有两个作用，一是约束作用（constraint)，规范数据的唯一性，但同时也在这个key上建立了一个index；
• foreign key 有两个作用，一是约束作用（constraint)，规范数据的引用完整性，但同时也在这个key上建立了一个index；

可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在Oracle上建立外键，不会自动建立index）

2.index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；
因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。

1.了解过哪些存储引擎？各有什么优缺点？

常用的是MyISAM和InnoDB。

InnoDB：支持事务和崩溃后的安全恢复、支持外键、支持行级锁、不支持全文索引、

MyISAM：不支持事务和崩溃后的安全恢复：、不支持外键、不支持行级锁、支持全文索引

我们常用的MySQL不就是支持事务、支持外键的吗？而且因为它不支持全文所以所以诞生了redis
InnoDB支持行级锁：如果对表中某一行进行操做，可以对该行进行加锁；
如果对表中某一行进行操做，MyISAM只能对表进行加锁。
基于那些特点，MyISAM支持小型应用（因为不支持事务、锁、外键等），也支持大批量的查询（因为快）；

MyISAM 强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持,崩溃后无法安全恢复。
InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

1.MySQL有哪些锁？以及各种锁的作用？

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁。
• InnoDB支持行级锁和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大的一种锁，对当前操作的整张表加锁，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

1、InnoDB锁算法

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

MySQL 中常见的日志有哪些？

1.binlog（二进制日志）

binlog，全称为Binary Log（二进制日志），主要记录了数据库中所有修改了数据的SQL语句或者二进制的事件，用来保证数据的一致性与恢复。具体来说，binlog记录了所有的DDL（Data Definition Language，数据定义语言）和DML（Data Manipulation Language，数据操作语言）语句，但不包括纯查询语句（如SELECT）和一些无需修改数据的操作（如SHOW等）。binlog是MySQL复制和数据恢复的基础，具有以下几个主要作用。

（1）数据复制：在主从复制架构中，主服务器会记录所有修改数据的操作到binlog中，而从服务器通过读取这些binlog记录并在自己的数据库上重新执行这些操作，从而实现与主服务器数据的同步。

（2）数据恢复：在数据库发生故障时，可以使用binlog来进行点的恢复。例如，如果数据库在某个时间点之后的数据因为操作错误或者其他原因被破坏，可以通过恢复到最近的备份，然后使用binlog中记录的日志，重新执行从备份时间点到故障时间点之间的所有操作，从而实现数据的恢复。

为了有效地使用binlog，需要在MySQL的配置文件（如my.cnf或my.ini）中开启binlog，并配置相关参数，比如：
	 log_bin：启用binlog，并设置binlog文件的名称。
	 max_binlog_size：设置binlog文件的最大