MySQL数据库快问快答

秒速五厘米 2022-02-22 04:29 220阅读 0赞

### 前言 ###

今天楼主给大家列一下关于数据库几个常见问题的要点，如果大家对其中的问题感兴趣，可以自行扩展研究。

### 1. union all 与 union 的区别 ###

*  UNION和UNION ALL关键字都是将两个结果集合并为一个。
 *  UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
 *  而UNION ALL只是简单的将两个结果合并后就返回。
 *  由于union需要排序去重，所以 union all 的效率比 union 好很多。

### 2. TRUNCATE 与 DELETE 区别 ###

*  TRUNCATE 是DDL语句，而 DELETE 是DML语句。
 *  TRUNCATE 是先把整张表drop调，然后重建该表。而 DELETE 是一行一行的删除，所以 TRUNCATE 的速度肯定比 DELETE 速度快。
 *  TRUNCATE 不可以回滚，DELETE 可以。
 *  TRUNCATE 执行结果只是返回`0 rows affected`，可以解释为没有返回结果。
 *  TRUNCATE 会重置水平线（自增长列起始位），DELETE 不会。
 *  TRUNCATE 只能清理整张表，DELETE 可以按照条件删除。
 *  一般情景下，TRUNCATE性能比DELETE好一点。

### 3. datetime 与timestamp 的区别 ###

`相同点`

*  TIMESTAMP 列的显示格式与 DATETIME 列相同。显示列宽固定在19字符，并且格式为`YYYY-MM-DD HH:MM:SS`

`不同点`

*  `TIMESTAMP`
    
     *  4个字节存储，时间范围：`1970-01-01 08:00:01~2038-01-19 11:14:07`
     *  值以UTC格式保存，涉及时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区
 *  `DATETIME`
    
     *  8个字节存储，时间范围：`1000-10-01 00:00:00~9999-12-31 23:59:59`
     *  实际格式存储，与时区无关

### 4. 什么是联合索引 ###

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。

### 5. 为什么要使用联合索引 ###

*  `减少开销`：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。
 *  `覆盖索引`：对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而**无需回表**，这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化手段之一。
 *  `效率高`：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql `select from table where col1=1 and col2=2 and col3=3`，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出`1000W*10%=100w`条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出`1000w*10%*10%*10%=1w`，效率得到明显提升。

### 6. Mysql 联合索引最左匹配原则 ###

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

### 7. 什么是聚集和非聚集索引 ###

*  聚集索引就是以主键创建的索引。
 *  非聚集索引就是以非主键创建的索引。

### 8. 什么是覆盖索引 ###

*  覆盖索引（covering index）指一个查询语句的执行只用从索引页中就能够取得（如果不是聚集索引，叶子节点存储的是主键+列值，最终还是要**回表**，也就是要通过主键再查找一次），避免了查到索引后，再做回表操作，减少I/O提高效率。
 *  可以结合第10个问题更容易理解。

### 9. 什么是前缀索引 ###

前缀索引就是对文本的前几个字符（具体是几个字符在创建索引时指定）创建索引，这样创建起来的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前缀索引，也不能把它们用作覆盖索引。

创建前缀索引的语法：

ALTER TABLE table_name ADD
    KEY(column_name(prefix_length))

### 10. InnoDB 与 MyISAM 索引存储结构的区别 ###

*  MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
 *  而在InnoDB中，**表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录**。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，所以必须有主键，如果没有显示定义，自动为生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
 *  InnoDB的辅助索引（Secondary Index，也就是非主键索引）存储的只是**主键列和索引列**，如果主键定义的比较大，其他索引也将很大。
 *  MyISAM引擎使用B+Tree作为索引结构，索引文件叶节点的data域存放的是数据记录的地址，指向数据文件中对应的值，每个节点只有该索引列的值。
 *  MyISAM主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，辅助索引可以重复，（由于MyISAM辅助索引在叶子节点上存储的是数据记录的地址，和主键索引一样，所以不需要再遍历一次主键索引）。

**简单的说：**

1.  `主索引的区别`：InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
2.  `辅助索引的区别`：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

### 11. 为什么尽量选择单调递增数值类型的主键 ###

*  InnoDB中数据记录本身被存于主索引（B+树）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的结点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。
 *  如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引结点的后续位置，当一页写满，就会自动开辟一个新的页，这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
 *  如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插入到现有索引页的中间某个位置，此时MySQL不得不为了将新记录查到合适位置而移动元素，甚至目标页可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 `OPTIMIZE TABLE` 来重建表并优化填充页面。

**简单的说：**

索引树只能定位到某一页，每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。

### 总结 ###

为了保持文章结构的完整性，这里强行加上一段总结。。。