Mysql面试题（一）

什么是索引

索引是帮助MySQL高校获取数据的排好序的数据结构。
索引的数据结构：

  • 二叉树
  • 红黑树
  • Hash表
  • B_Tree
    索引是用来快速寻找那些具有特定值的记录，MySQL的索引是以B_Tree树的形式保存的。如果没有索引，执行查询的时候MySQL是从表的第一条记录开始扫描整个表的记录，直到找到符合要求的数据。表里面的数据越多，这个操作的代价就越大。如果在搜索的条件上加上索引，MySQL可以快速的得到目标记录所在的位置，从而大大提高查询效率。

索引的分类

1. 主键索引（主键索引不能为null）

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。
主键一般在创建表的时候就指定哪个字段是主键。如果创建表的时候不指定，也可以通过修改表的形式添加主键。

alter table tablename add primary key

主键索引只能有一个
不能使用like，会失效

1.1 查询索引
desc 表名： 不能显示索引的名称
show index from 表名
show keys from 表名

2. 全文索引

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

❌ 错误用法:

select * from articles where body like '%mysql%'; 错误用法 索引不会生效

✅ 正确用法:

select * from articles where match(titlle,body) against('database')

说明：

  • 在mysql中fulltext 索引只针对 myisam生效
  • mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
  • 使用方法是 match(字段名..) against(‘关键字’)
mysql> select match(title,body) against ('database') from articles;（输出的是每行和database的匹配度）

全文索引：停止词。 因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.比如（a，b，mysql，the）

create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);

可以使用like，但是前面不能带%，后面可以带%好
如果非要使用%进行模糊查询，并且使用索引。可以 like '000%111' 这样写。加如按照订单号进行模糊查询。订单在存储的时候，默认在前面加上000，这样就可以实现模糊查询，而不使索引生效

3. 普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

4. 唯一索引（唯一索引可以为null）

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

create unique index 索引名称 on tablename (列的名称)

修改表的时候

alter table tablename  add unique 索引名称 (列的名称)

注意：

  • unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，但是不能存有重复的空字符串’’
  • 可以使用like，但是前面不能带%，后面可以带%好

5. 组合索引（联合索引）

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
ａ. 建表的时候一起创建

CREATE TABLE mytable (`id` int(11) ,`name` VARCHAR(32) ,INDEX index_mytable_id_name (`id`,`name`));

b. 建表后，直接创建索引

CREATE INDEX index_mytable_id_name ON mytable(id,name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

联合索引的注意事项

一定要和第一部分的索引一起使用
不要加or关键字
可以使用like，但是like不要加上模糊内容前面加上%，后面可以加%
如果列类型是字符串，一定要使用''号引起来
如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

索引的代价

  • 占用磁盘空间（索引保存在对应表后缀名为MYI文件中）

frm、MYD、MYI是myisam引擎表的结构文件、数据文件、索引文件。
frm、ibd是innodb引擎表的结构文件、数据和索引文件。

哪些列适合加索引。

  • 经常作为查询条件的列应该创建索引
  • 不会出现在where语句后面的不应该创建索引。
  • 频繁更新的字段，也不应该创建索引
  • 唯一性太差的字段，不适合创建索引，即使更新很频繁。
    • 如：select * from emp where sex = '男'

总结：满足以下条件的字段，才应该创建索引。

  • 肯定在where中经常使用的
  • 该字段内容不是唯一的几个值
  • 字段内容不是频繁变化的
  • 如果不加索引和加上索引效率差不多，就不要加索引了。

索引创建的方法

ALTER table 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；
-- 或
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；

删除索引

DROP INDEX 索引名 ON 表名
-- 或
ALTER TABLE 表名 DROP INDEX 索引名

能说下myisam 和 innodb的区别吗？

myisam引擎是5.5版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。
innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
锁机制（MyISAM时表锁，innodb是行锁）

说下mysql的索引有哪些吧，聚簇和非聚簇索引又是什么？

索引按照数据结构来说主要包含B+树和Hash索引。

假设我们有张表，结构如下：

create table user( id int(11) not null, age int(11) not null, primary key(id), key(age) );

B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引。假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引。

那你知道什么是覆盖索引和回表吗？

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。
而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。
// 加入说又一个表。有三个字段，id（主键），username(普通索引)，sex
那么 select * from user where username = "liufei"; // 这种是没有使用覆盖索引的，回表查询了
如果 select id,username where username = "liufei"; // 这种就使用了覆盖索引。
因为索引中没有sex，当命中username索引时，索引的叶子节点存储了id，还需要通过id去找sex字段。
那么怎么样使用覆盖索引将三个字段全部查询出来？？
将username的普通索引，升级成username和sex的联合索引。

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

详情查看：https://www.cnblogs.com/myseries/p/11265849.html

mysql锁的类型有哪些？

mysql的锁分为共享锁和排他锁，也叫做读锁和写锁。
读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。
表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

你能说下事务的基本特性和隔离级别吗？

事务基本特性ACID分别是：

  • 原子性（Atomicity）：事务是一个完整的操作，事务的各部分是不可分割的（原子），要么都执行，要么都不执行。
  • 一致性（consistency）：当事务完成时，数据必须处于一致性。
  • 隔离型（isolation）：是指一个事务的修改在最终提交前，对其他事务是不可见的。
  • 永久性（durability）：是指事务一旦提交，所做的修改就会永久保存到数据库中。

而隔离性有4个隔离级别，分别是：

  • 读未提交(read uncommit)。可能会读到其他事务未提交的数据，也叫做脏读。
    用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。

  • read commit 读已提交，两次读取结果不一致，叫做不可重复读（通常针对数据更新（UPDATE）操作）。
    不可重复读解决了脏读的问题，他只会读取已经提交的事务。
    用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。

  • repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读（通常针对数据插入（INSERT）操作）。

  • serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

二、事务的并发问题
　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

那ACID靠什么保证的呢？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性一般由代码层面来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

那你说说什么是幻读，什么是MVCC？

要说幻读，首先要了解MVCC，MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

我们每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。

还是拿上面的user表举例子，假设我们插入两条数据，他们实际上应该长这样。

a-draft-type=“table” data-size=“normal” data-row-style=“normal”>
id name create_version delete_version
这时候假设小明去执行查询，此时current_version=3

select * from user where id<=3; 复制代码

同时，小红在这时候开启事务去修改id=1的记录，current_version=4

update user set name=‘张三三’ where id=1; 复制代码

执行成功后的结果是这样的

a-draft-type=“table” data-size=“normal” data-row-style=“normal”>
id name create_version delete_version
如果这时候还有小黑在删除id=2的数据，current_version=5，执行后结果是这样的。

a-draft-type=“table” data-size=“normal” data-row-style=“normal”>
id name create_version delete_version
由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本，小明的真实的查询应该是这样

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null); 复制代码

所以小明最后查询到的id=1的名字还是’张三’，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。

明白MVCC原理，我们来说什么是幻读就简单多了。举一个常见的场景，用户注册时，我们先查询用户名是否存在，不存在就插入，假定用户名是唯一索引。

小明开启事务current_version=6查询名字为’王五’的记录，发现不存在。
小红开启事务current_version=7插入一条数据，结果是这样：
a-draft-type=“table” data-size=“normal” data-row-style=“normal”>
id Name create_version delete_version
小明执行插入名字’王五’的记录，发现唯一索引冲突，无法插入，这就是幻读。

一条SQL的执行过程

参考连接

MySQL 基本架构概览：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。
主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是> 会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

  • 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会> > 直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。
所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

  • 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：
第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。
完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

  • 优化器： 按照 MySQL 认为最优的方案去执行。

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

  • 执行器: 执行语句，然后从存储引擎返回数据。

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

简单来说 MySQL 主要分为 Server 层和存储引擎层：
•Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
•存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

总结

  • MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。
  • 引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  • SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎
  • 对于更新等语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit

你们数据量级多大？分库分表怎么做的？

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。

垂直分库

基于现在微服务拆分来说，都是已经做到了垂直分库了。

垂直分表
如果表字段比较多，将不常用的、数据较大的等等做拆分。

水平分表

首先根据业务场景来决定使用什么字段作为分表字段(sharding_key)，比如我们现在日订单1000万，我们大部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询支持到最近3个月的订单，超过3个月的做归档处理，那么3个月的数据量就是9亿，可以分1024张表，那么每张表的数据大概就在100万左右。

比如用户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的表上了。

那分表后的ID怎么保证唯一性的呢？

因为我们主键默认都是自增的，那么分表之后的主键在不同表就肯定会有冲突了。有几个办法考虑：

  • 设定步长，比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。
  • 分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种。
  • 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
  • 利⽤ redis ⽣成 id : 性能⽐᫾好，灵活⽅便，不依赖于数据库。但是，引⼊了新的组件造成系统更加复杂，可⽤性降低，编码更加复杂，增加了系统成本。
  • 美团的Leaf分布式ID⽣成系统 ：Leaf 是美团开源的分布式ID⽣成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全，⾥⾯也提到了⼏种分布式⽅案的对⽐，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的⼀篇⽂章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

主从复制的应用场景

通过主从复制，可以实现数据的备份、故障转移、MySQl集群、高可用、读写分离等。
MySQL的主从复制是MySQL本身自带的一个功能，不需要额外的第三方就可以实现，其复制的功能是借助binlog二进制日志文件里面的SQL命令实现的主从复制，可以理解为我Matster端执行了一条SQL命令，那么Slave端同样会执行一遍，从而达到主从复制的效果。

说说mysql主从同步怎么做的？


Binary log：主数据库的二进制日志。
Relay log：从服务器的中继日志。

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

那主从的延迟怎么解决呢？

这个问题貌似真的是个无解的问题，只能是说自己来判断了，需要走主库的强制走主库查询。

  • 方法一：忽略错误后，继续同步
  • 方式二：重新做主从，完全同步

什么是慢查询？

MySQL默认10秒内没有响应SQL结果,则为慢查询
可以去修改MySQL慢查询默认时间

-- 查询慢查询时间
show variables like 'long_query_time';
-- 修改慢查询时间
set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

如何将慢查询定位到日志中
在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以
bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在
my.ini 文件中记录的位置

Path to the database root

datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

mysql5.7下的
在/etc/my.cnf的配置文件下添加下面这个

[mysqld]
-- 下面这三个必须填写在mysqld下
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow_query.log
long_query_time=1

注意：
slow_query_log=ON 开启MySQL的慢查询。
slow_query_log_file=/var/log/mysql/slow_query.log 指定日志的文件
long_query_time=1 设置慢查询的时间，默认是10s

然后重启MySQL

service mysql restart

SQL优化的技巧

  • 查询SQL尽量不要使用select *，而是select具体字段。
  • 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1
  • 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描， 可以 使用UNION合并查询：
select id from t where num=10 
union all 
select id from t where num=20
  • 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]
  • 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
  • 应尽量避免在 where 子句中使用!=或<>操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE
  • in 和 not in 也要慎用，否则会导致全表扫描
    对于连续的数值，能用 between 就不要用 in 了：
Select id from t where num between 1 and 3
  • 对于like的用法，前后都加%和前面加%将导致全表扫描，而%放到最后才会用到索引。
  • 索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  • 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。
posted @ 2021-04-17 23:59  刘翊扬  阅读(982)  评论(0)  编辑  收藏  举报