MySQL面试问题（二）

MySQL面试问题（二）

为什么要使用索引

1. 索引减少了存储引擎需要扫描的数据量。
2. 帮助我们进行排序，避免使用临时表。
3. 索引可以将随机IO转换为顺序IO。

索引是不是越多越好

1. 索引越多，更新的速度越慢。索引会增加数据库写入操作的成本，innodb使用插入缓存，把多次插入合并成一次插入进行优化。
2. 太多的索引会影响mysql查询优化器的选择时间。
3. 更多的索引需要更多的空间。

MySQL索引机制

1. 索引本质上是一个优化查询的数据结构比如B+树，可以优化查询效率。索引一般以文件的形式存储在磁盘上，索引检索需要磁盘IO操作，评价一个数据结构作为索引的优劣重要的指标就是在查找过程中磁盘IO操作次数的渐进复杂度。当数据存储在磁盘类存储介质上时，它是作为数据块存放。这些数据块被当做一个整体来访问，可以保证操作的原子性。磁盘数据块类似于链表结构，包含数据部分和指向下一个节点的指针，不需要连续存储，所以没有索引则检索过程变成了顺序查找。

什么是聚簇索引

1. 聚簇索引是按照表的主键构造一棵B+树，同时叶子节点存放的就是行记录数据，聚簇索引的叶子节点也称为数据页。这个特性决定了索引组织表中数据也是索引的一部分。
2. 由于聚簇索引的索引页面指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。
3. 每张表只能建一个聚簇索引，并且建聚簇索引需要相当于该表120%的附加空间来存储该表的副本和索引中间页。

没有主键innodb如何处理

1. MySQL的innodb引擎本身存储的形式必须是聚簇索引的形式，在磁盘上树状存储，但是不一定是根据主键聚簇。如果有主键，主键就是聚簇索引。没有主键的情况下，第一个非空的唯一索引就是聚簇索引。如果都没有，那么就是有一个隐藏的rowid即行ID作为聚簇索引。

联合索引

1. 联合索引的选择原则是最左匹配原则、离散度高原则、最少空间原则。最左匹配原则，经常用的列优先。离散度高原则，选择离散度高的列优先，列的离散性越高，选择性越好。最少空间原则，宽度小的列优先。

2. 联合索引的作用有减少开销、覆盖索引和效率高。建一个联合索引，相当于建立了多个索引，每多一个索引都会增加写操作和磁盘空间的开销，对于大数据量的表，使用联合索引会大大减少开销。使用联合索引，可以利用索引覆盖，无需回表，减少随机IO操作。索引列越多，通过索引筛选出的数据越少，效率越高。

批量向MySQL中导入1000w数据如何优化

1. 批量录入数据，手动开启事务、手动提交。
2. 批量SQL

insert into tab_name() values();
-- 多条数据录入，带有缓存，单条SQL不要录入过多的数据，通常不超过10M
insert into tab_name() values(),();

3. 数据库配置

   配置SQL批处理缓存、IO缓存、是否记录binlong。

4. 通过csv或者txt文件做本地导入，mysql import xxx文件。

5. 代码及开发，batch批处理，多次访问数据库，批量写入。

6. 索引会降低写效率。建表时，先不创建索引，当数据相对趋于稳定或正式发布时创建索引，先内存维护索引，索引内存空间不足，需要持久化到磁盘。

分页时偏移量很大效率很差如何优化

1. MySQL分页时，并不直接查rows的数据，而是把offset和rows的数据全部都查出来，然后再将offset的数据扔掉，返回rows的数据。查询所有列导致回表，limit a, b会查询前a*b+b条数据，然后丢弃前a*b条记录。
2. 可以使用覆盖索引和条件过滤的方式进行优化。

大数据量高并发访问数据库优化方法

1. 数据库结构设计优化，比如优化数据模型，增加冗余减少数据库访问次数，优化查询SQL键索引等，增加批量查询。
2. 分布式部署，增加主从配置，实现读写分离。
3. 数据库分库分表、分区。
4. 使用缓存和nosql，实现冷热数据分离。
5. 更新存储介质，使用固态硬盘。