Mysql 面试题

MySQL索引

1、索引主要有普通索引、唯一索引、主键索引、外键索引、全文索引、复合索引

2、MySQL索引是一种帮助快速查找数据的数据结构，可以把它理解为书的目录

，通过索引能够快速找到数据所在位置。

3、场景的索引数据结构有：Hash表（通过hash算法快速定位数据，但不适合范围查询，因为需要每个key都进行一次hash）、二叉树（查找和修改效率都比较高），但是在InnoDB引擎中使用的索引是B+Tree，相较于二叉树，B+Tree这种多叉树，更加矮宽，更适合存储在磁盘中。

4、索引可以提高搜索速度，但是由于需要维护一个数据索引，会增加内存的消耗，同时也会降低写入数据的速度

SQL优化手段

1、开启查询缓存

2、explain select语句，分析查询语句，检查查询是否走索引

4、不走索引的话，检查查询是否满足最左前缀原则，是否覆盖索引

5、为字段创建索引

6、只有查询一条数据用 limit 1

7、固定字段使用enum不用varchar

8、select 指定查询字段不使用*

9、垂直分表

10、选择正确的存储引擎，InnoDB、MyISAM（适合读多或写多）、Memory

事务如何保证原子性 

undo log 记录了回滚相应日志信息，rollback操作，利用undo log日志将数据恢复到事务前样子

Like是否使用索引

1、like %keyword    索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。

2、like keyword%  索引有效。

3、like %keyword% 索引失效，也无法使用反向索引。

mysql 高效模糊查询 代替like

LOCATE（'substr',str,pos）方法
POSITION('substr' IN field)方法
INSTR(str,'substr')方法

$sql1 = "...... title like im_prefix%标题党%" (花费0.001秒)，前台显示数据的时候，把取到的title过滤掉"im_prefix"这个前缀

左连接 ，右连接，内连接和全外连接的4者区别 

left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

表设置了ID自增主键，当insert了17条记录之后，删除了第15，16，17条记录，再重启Mysql，再insert一条记录，这条记录的ID是18还是15？

类型MyISAM，18。

因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。

类型InnoDB，那么是15。

因为InnoDB表把最大ID主键最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

Heap 表是什么？

Heap表在mysql中就是memory存储引擎的表

数据保存在内存中，重启mysql数据库数据会消失的

默认使用 Hash 索引

BLOB 和 TEXT字段是不允许的

Mysql 默认端口

3306

InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别

Read Uncommitted（读取未提交内容）：所有事务都可以看到其他未提交事务的执行结果

Read Committed（读取提交内容）：一个事务只能看见已经提交事务所做的改变

Repeatable Read（可重读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。但是会产生幻读，简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

Serializable（可串行化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

CHAR 和 VARCHAR 的区别

Char 值被存储时，会被用空格填充到特定长度

CHAR 检索速度快，容易造成空间浪费；VARCHAR 不会造成空间浪费。

ENUM 用法

枚举，限定取值范围

CREATE TABLE `size`(`sex` enum('男','女','未知'));

字符类型是

SET、CHAR、VARCHAR、TEXT、BLOB、ENUM

如何获取版本

select version();

InnoDB 中B+树结构

高度为3 B+树能存多少数据

mysql 每页大小：16KB

int 占用大小：4b

bigint 占用大小：8b

指针占用大小: 6b

每页可以索引多少页：16KB/10b=1638

每页可以存多少条数据（假设一条数据1KB）：16KB/1KB=16

两层B+树能存多少条数据：1638 * 16 KB / 1KB = 26208

三层B+树能存多少条数据：1638 * 1638 * 16 KB / 1KB = 42,928,704

InnoDB 如何支持范围查找能走索引吗

通过索引查找到指定页表中叶子节点，通过B+树双向指针输出该节点之后或之前的所有节点。

普通索引范围查找可能会导致索引失效，需要进行回表多次

覆盖索引

查询条件字段覆盖索引字段，可以使用索引（包含范围查询）

索引底层扫描

根据包含查询条件的索引树扫描叶子节点

对字段进行操作导致索引失效

对字段进行加减法、类型转换，索引都回失效

字段为字符类型，传入值为int类型。查表之前，mysql会对该字段转换成int类型，非数字都转换成0。

MyISAM 和 InnoDB 区别

InnoDB 支持事务，MyISAM不支持事务

InnoDB支持外键，MyISAM不支持外键

InnoDB是聚集索引, MyISAM非聚集索引

        聚集索引的文件存在主键索引的叶子节点

        非聚集索引的数据文件是分离的，索引保存的是文件的指针

InnoDB 不保存整表的行数，需要进行整表扫描；MyISAM保存整表的行数变量

InnoDB 最小的锁粒度是行锁，MyISAM 最小锁粒度是表锁

MyISAM 优势：大数据量排序、全表扫描、count，由于非聚簇索引不存放数据所占空间小

mylSAM引擎的特点:

1、不支持事务（事务就是逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全部成功，要么全部失败）
2、表级锁定（更新是锁整个表）：其锁定机制是表级锁定，虽然可以让锁定的实现成本很小，但是大大的降低了其并发性能。
小结：MyISAM锁定的范围太大
3、读写互相堵塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4、只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，大大提高访问性能，减少磁盘的I/O，但是缓存区只会缓存索引，不会缓存数据。
5、读取速度较快，占用资源相对少。
6、不支持外键约束，但支持全文索引。
7、MyISQM引擎是mysql_5.5.5之前的索引。
 

 MyISAM引擎使用的场景

1、不需要事务支持的业务（转账、充值、付款这种就不行）。
2、一般为读数据比较多的应用。读写都频繁的不适合，读多或写多都适合。
3、并发访问相对低的业务（纯读、纯写高并发也可以）。
4、数据修改相对较少的业务（阻塞问题）。
5、以读为主的业务，例如：www，blog，图片信息数据库，用户数据库，商品库等业务。
6、对数据一致性要求不是很高的业务。
7、硬件资源比较差的机器可以用MyISAM。
小结：单一对数据库的操作都可以使用MyISAM引擎

事务基本特性

原子性

一致性

隔离性

持久性

事务并发可能引发问题

 

 

Mysql各种索引

主键索引

唯一索引 unique

普通索引 normal

前缀索引：

        基于前几个字符或对二进制类型字段的几个bytes建立的索引，而不是在整个字段上建索引。

        前缀索引是一种能使索引更小更快的有效方法，缺点order by 和 group by 失效。 

联合索引：多个数据列

全文索引：建立倒排索引

三星索引

一星：where后面匹配条件，可以匹配联合索引的多个列，索引列匹配字段越多，索引片越窄，最终扫描的数据行越小

二星：order by的排序是否和索引的顺序一致：意义在于避免进行额外的排序，增加消耗

三星：使用了覆盖索引 ，意义在于避免每一个索引行查询，都需要去聚簇索引进行一次随机IO查询

如何提高insert的性能？

• 合并多条insert为一条：减少 binlog和事务日志量，减少SQL语句的解析次数，减少网络传输的IO
• 增大批量插入的缓存，修改参数 bulk_insert_buffer_size
• 设置 innodb_flush_log_at_trx_commit = 0
• 手动提交事务

全局锁、共享锁、排他锁

全局锁：对整个数据库实例加锁

共享锁：读锁

排他锁：写锁

主从复制

三个线程：Log dump thread、IO线程、SQL线程

mysql 复制原理

• 从库的IO线程和主库的dump线程建立连接
• 从库提供file名和position号，IO线程向主库发起binlog的请求
• 主库dump线程根据从库请求，将本地binlog以events的方式发给从库IO线程
• 从库IO线程接收binlog events,并存放到本地relay-log中
• 从库SQL线程应用relay-log

分库分表 

hash 分库分表

按月份分库分表

聚簇索引和非聚簇索引

都是B+树结构

聚簇索引：数据和索引都存放在一起，并按照顺序存储。物理存放顺序数据和索引是一致的

非聚簇索引：B+树叶子节点不存放数据，存储数据的地址，数据存储在磁盘中。

索引设计原则

适合索引where子句中的列，连接子句的列

有外键数据的列一定要建立索引

基数较小的表没必要建立索引

更新频繁字段不适合建立索引

重复值较多列不要建立索引

text、image、bit 数据类型的列不要建立索引

        

意向共享锁 和 意向排他锁

• 意向共享锁

        当一个事务视图对整个表进行加共享锁之前，需要获取整个表的意向共享锁

• 意向排他锁

        当一个事务试图对整个表进行加排他锁之前，需要获得整个表的意向排他锁

慢查询优化

分析语句：看是否查询了多余的字段

分析语句执行计划：获得使用索引情况，修改语句或者修改索引，使其尽可能命中索引

如果对语句优化已无法进行，考虑数据量是否太大，考虑横向纵向分表分库

ACID靠什么保证的？

原子性：由 undo log 保证，它记录了回滚的信息

一致性：由其他三个特性一起保证的

隔离性：由MVCC保证

持久性：由 redo log 保证

MVCC

多版本并发控制：读取数据通过一种类似快照的方式 将数据保存来，这样读锁和写锁就不冲突，不同事务session会看到自己特定版本的数据。

MVCC只在 读已提交 和 可重复读 两个隔离级别下工作。

开始事务时创建readview，维护当前活动事务的id，排序生成一个数组，获取数据中的事务id，对比readview:

如果readview的左边，可以访问

如果在readview的右边或者就在readview中，不可以访问，获取roll_ponter，取上一版本重新对比

已提交读隔离级别下的事务每次查询的开始都会生成一个独立的ReadView

可重复读隔离级别则在第一次读的时候只生成一个ReadView

分库分表常用工具 

Mycat

ShardingSphere

什么情况下设置索引但无法使用？

不符合最左前缀原则

字段进行了隐式数据类型转化

走索引没有全表扫描效率高。比如，过大值范围查询，并且超过指定索引字段，需回聚簇索引查询