Mysql 面试题

1. MySQL 基础

1.1 MySQL、Redis、MongoDB、HBase的区别

• MySQL：关系型数据库，一般 web 开发搭配使用，数据存储在磁盘中，读写慢

• Redis：非关系型数据库，数据类型：字符串、列表、集合、有序集合、哈希，存储在内存中，读写速度快，可用作缓存key-value 存储 结构

  • 缺点： 因为是基于内存查询的，所以限制了可存储的数据量，限制了 Redis 在数据规模很大的应用场景中

  • 场景： 适合对读写性能极高，且数据表结构简单，查询条件简单的应用场景

• MongoDB：非关系型数据库，存储在磁盘上，读取数据会被加载到内存中，读取速度快表结构灵活可变，字段类型可以随时修改， 插入数据时，不必考虑表结构的限制

  • 缺点： 给多表查询、复杂事务等高级操作带来了阻碍

  • 场景：适合那些表结构经常改变，数据的逻辑结构没又没那么复杂不需要多表查询操作，数据量又比较大的应用场景

• HBase：非关系型数据库（列式存储）， 极强的横向扩展能力，适合存储海量数据， 使用廉价的 PC 机就能够搭建起海量数据处理的大数据集群

  • 缺点： 对数据的读取带来了局限，只有同一列族的数据才能够放在一起，而且所有的查询都必须依赖于 key，这就使得很多复杂的查询难以实现

  • 场景： hbase 是一款很重的产品，依赖很多 hadoop 组件，如果数据规模规模不大，没必要使用 hbase，MongoDB 就完全可以满足需求 由于列式存储的能力带来了海量数据的容纳能力，因此非常适合数据量极大、查询条件简单、列与列之间联系不大的场景

  参考：https://blog.csdn.net/oppo62258801/article/details/76767626https://my.oschina.net/u/4389636/blog/4528148

1.2 MySQL 有哪些引擎，说说区别

• MyISAM：默认引擎（ MySQL 5.5 之前的默认存储引擎，但是从 MySQL 5.5 开始，InnoDB 成为了默认的存储引擎），使用表级锁定控制并发访问，适用于大量读取操作的场景，但不适用于频繁写入和更新操作，因为这些操作对整个表进行锁定，从而降低性能

• InNoDB：支持事务、行级锁定、外键等特性（应用最广泛），适用于高并发和事务处理的场景，如：电子商务网站，相比较 MyISAM 性能更好，但会占用更多磁盘空间

• Memory：Memory存储引擎将数据存储在内存中，因此它的读取速度非常快。但由于数据存储在内存中，当MySQL服务关闭时，数据也会丢失。它适用于临时存储数据的场景，例如缓存。

• Archive：Archive存储引擎适用于存储大量归档数据的场景，例如日志数据。它可以压缩数据并支持快速的插入和检索操作。但由于它不支持索引和更新操作，因此不适用于需要频繁更新和查询的场景。

• CSV：CSV引擎是一种存储引擎，用于将数据存储为逗号分隔的值（CSV）格式。CSV引擎适用于需要将数据导出为CSV格式的应用程序

1.3 MySQL 事务

MySQL中的事务（Transaction）是指一系列的操作被视为一个单独的工作单元，要么全部成功执行，要么全部失败回滚。在MySQL中，事务是通过ACID（原子性、一致性、隔离性和持久性）特性来保证数据的完整性和一致性的。

1.3.1 事务的四大特性

ACID特性的含义如下：

• 原子性（Atomicity）：事务中的所有操作要么全部成功执行，要么全部回滚，不能部分执行。

• 一致性（Consistency）：事务前后数据库的状态必须保持一致，即使事务中出现了错误，也必须能够恢复到原来的状态。

• 隔离性（Isolation）：每个事务的执行必须与其他事务隔离开来，互不干扰。这可以防止多个事务同时对同一数据进行修改，导致数据不一致。

• 持久性（Durability）：一旦事务提交，其对数据库的修改就必须永久保存在数据库中，即使系统崩溃也不能丢失。

1.3.2 事务隔离级别有哪些？

概念

1. 脏读：指的是在一个事务处理过程中读取了另一个未提交的事务的数据

2. 不可重复读：指的是对于数据库中某行记录，一个事物范围内多次查询却返回了不同数据，这是因为在查询间隔，另一个事物修改了数据并提交了

3. 幻读：当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，导致之前的事务

不可重复读和脏读的区别

• 脏读是读取了另一个事物未提交的脏数据

• 不可重复读是读取了前一个事务已提交的数据

幻读和不可重复读的区别

• 共同点：都是读取了另一条已提交的事务

• 不同点：不可重复读重点是修改，幻读重点在于新增或删除

MySQL 提供了四种事务隔离级别，解决了上面说的几个问题：

• Serializable 串行化：通过强制事务排序，使得不可能相互冲突，从而解决幻读问题

• Repeatable read (可重复读)：默认事务隔离基本，确保同一个事物的多个实例在并发读取数据时，返回同样的数据库，解决了不可重复读的问题

• Read committed (读已提交)：一个事物只能看见已经提交事务所做的改变，可避免脏读的发生

• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果

# 查看隔离级别
select @@transaction_isolation;
​
# 设置隔离级别
set session transaction isolation level read uncommitted;

请问你了解 MySQL 的数据类型吗？

• 字符串类型（char、varchar、text等）：char固定长度，varchar可变长度；text是一种特殊的字符串类型，可以存储大量的文本数据。

• 数值类型（整数、浮点数等）、日期类型等

在实际使用过程中，需要根据实际情况选择合适的数据类型，避免浪费存储空间或出现数据溢出等问题。

2. MySQL 索引

索引优缺点

索引是存储引擎用于提高数据库表访问速度的一种数据结构

优点

• 加快数据查找速度

• 为用来排序或分组的字段添加索引，可以加快分组和排序的速度

• 加快表与表之间的连接速度

缺点

• 建立索引要占用物理空间

• 会降低表的增删改的效率，因为每次增删改操作，都需要动态维护索引，导致操作时间变长

索引作用

数据存储在磁盘上，查询时需加载所有数据到内存，再依次进行检索，读取磁盘次数较多，非常耗时。

有了索引后，就不需要加载全部数据，因为 B+ 树的高度一般在 2-4 层，最多只用读 2-4 次磁盘，查询速度大大提升。

什么情况下要建索引或不建立索引

需要建索引的场景

• 经常用于查询的字段

• 经常用于连接的字段建立索引，可加快连接速度

• 经常需要排序的字段建立索引，因为索引已经排好序，可加快排序查询速度

不需要建立索引的场景

• where 条件中用不到的字段不适合

• 表记录较少

• 需要经常增删改

• 参与列计算的列不适合（索引失效）

• 区分度不高的自动不适合，如性别（只有男女两种情况）

索引分类

• PRIMARY KEY：主键索引是一种特殊的索引类型，它用于保证表中每一行记录的唯一性。主键索引通常使用B-tree算法实现，它可以加速表的查找和连接操作。每个表只能有一个主键索引。

• UNIQUE KEY：唯一索引用于保证表中某一列的唯一性。唯一索引通常使用B-tree算法实现，它可以加速表的查找和连接操作。一个表可以有多个唯一索引，（可为 null）。

• INDEX：普通索引用于加速表的查找和连接操作。普通索引通常使用B-tree算法实现，它可以提高查询的效率。一个表可以有多个普通索引。

• FULLTEXT：全文索引用于快速搜索文本数据，它支持基于自然语言的全文本搜索。全文索引通常使用倒排索引（Inverted Index）实现，它可以提高文本数据的检索效率。

• 组合(联合)索引：多个列组成一个索引

如何选择合适的列建立索引

• 在 where、group by、order by、on 从句中出现的列

• 索引字段越小越好

• 离散度大的列放到联合索引的前面

select * from payment where staff_id = 2 and customer_id = 584;
​
# 是 index(staff_id, customer_id) 好？还是 index(customer_id, staff_id) 好？
# 由于 customer_id 离散度更大，所以应该使用 index(customer_id, staff_id)
# 查离散度
mysql> select count(distinct customer_id), count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+
1 row in set (1.04 sec)

索引的维护及优化--重复及冗余索引

重复索引

指相同的列以相同的顺序建立的同类型的索引，如下面中的主键和 ID 列上的唯一索引就是重复索引：

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id)
)engine=innodb;

冗余索引

指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。如下：key(name,id)就是一个冗余索引

# 可以删除冗余索引，达到优化效果。
create table test(
    id int not null primary key,
    name varchar(10) not null,
    key(name, id)       # 联合索引中包含了主键的索引，ID 含有主键索引
)engine=innodb;

查找重复或冗余的索引

mysql> use information_schema;
mysql> select a.table_schema as '数据名', a.table_name as '表名', a.index_name as '索引1', b.index_name as '索引2', a.colu
mn_name as '重复列名' from statistics a join statistics b on a.table_schema=b.table_schema and a.table_name=b.table_name a
nd a.seq_in_index = b.seq_in_index and a.column_name = b.column_name where a.seq_in_index = 1 and a.index_name <> b.index_name \G;

使用 pt-duplicate-key-checker 工具

# 使用 pt-duplicate-key-checker工具检查重复及冗余索引
pt-duplicate-key-checker \
-uroot \
-p '' \
-h 127.0.0.1

删除不用索引

目前mysql中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可通过INDEX_STATISTICS表来查看哪些索引未使用，但在mysql中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况分析。

索引失效场景

索引失效场景

• 查询条件包含 or，可能导致索引失效

• 若字段类型是字符串，where 时一定用引号括起来，否则索引失效

• like 通配符可能导致索引失效

• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效

• 在索引列上使用mysql的内置函数，索引失效

• 对索引列运算（如，+、-、*、/），索引失效

• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效

• 索引字段上使用is null， is not null，可能导致索引失效

• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效

• mysql 估计使用全表扫描要比使用索引快,则不使用索引

• 联合索引不满足最左匹配原则

• 类型隐式转换

• 两列作比较

---

示例：准备数据

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
​
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

在上述表结构中有三个索引：

• id：为数据库主键

• union_idx：为id_no、username、age构成的联合索引

• create_time_idx：是由create_time构成的普通索引

1、联合索引不满足最左匹配原则

# 最左边的字段为id_no，一般情况下，只要保证id_no出现在查询条件中，则会走该联合索引
KEY `union_idx` (`id_no`,`username`,`age`)
​
​
# 走索引
explain select * from t_user where id_no = '1002';
explain select * from t_user where id_no = '1002' and age = 12;
explain select * from t_user where id_no = '1002' and age = 12 and username = 'Tom1';
​
# 索引失效，id_no 没在查询条件中
explain select * from t_user where username = 'Tom2' and age = 12;
explain select * from t_user where age = 12;
explain select * from t_user where username = 'Tom2';

2、使用 select *

若建立了联合索引，应尽量走覆盖索引，select 尽量返回所需字段，避免返回所有字段，增加开销

什么是覆盖索引

3、索引列参与运算（全表扫描）

# 失效
explain select * from t_user where id + 1 = 2 ;
​
# 有效
# 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
​
# 参数侧计算
explain select * from t_user where id = 2 - 1 ;

4、索引列使用了函数

# 对 id_no 使用字符串截取函数
explain select * from t_user where SUBSTR(id_no,1,3) = '100';

5、like 使用错误

# 索引失效
like '%abc'
like '%abc%'
​
# 走索引
like 'abc%'

6、类型隐式转换

字段类型与参数类型不一致

# id_no 是字符串，查询参数为数字
explain select * from t_user where id_no = 1002;

7、使用 or 操作

使用 or 查询时，查询条件字段都要建立索引，若其中未建索引则会失效：

# username 没有建立索引
explain select * from t_user where id = 2 or username = 'Tom2';
​
# 范围查询
explain select * from t_user where id  > 1 or id  < 80;

8、两列作比较

# 即使都有索引也会失效
explain select * from t_user where id > age;

9、不等于比较

# 失效
explain select * from t_user where id_no <> '1002';
explain select * from t_user where create_time != '2022-02-27 09:56:42';
​
# 有效
explain select * from t_user where id != 2;

注意：查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效

10、is not null

is not null 失效，is null 走索引：

explain select * from t_user where id_no is not null;

11、not in 和 not exists

• not in 主键索引有效，普通索引无效

• not exists 失效

# 有效
explain select * from t_user where id in (2,3);
explain select * from t_user where id_no in ('1001','1002');
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
explain select * from t_user where id_no between '1002' and '1003';
​
# 无效
explain select * from t_user where id_no not in('1002' , '1003');
explain select * from t_user where id not in (2,3);     # 主键会走索引
explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

12、order by 导致索引失效

# 失效
explain select * from t_user order by id_no ;
​
# 主键和联合索引，多个字段排序，索引无效
explain select * from t_user order by id,id_no desc;
explain select * from t_user order by id,id_no desc limit 10;
explain select * from t_user order by id_no desc,username desc;
​
# 有效
explain select * from t_user order by id desc;
​
# 覆盖索引场景索引有效
explain select id from t_user order by age;
explain select id , username from t_user order by age;
explain select id_no from t_user order by id_no;

注意：当查询条件涉及到 order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证

13、参数不同导致索引失效

参考：15个必知的Mysql索引失效场景，别再踩坑了！

3. SQL语句优化

3.1 MySQL 分页

获取数据总量 total再配合 SQL limit offset 进行查询

3.2 日常工作是如何优化 SQL

• 加索引

• 避免返回不必要的数据

• 适当分批量进行

• 优化 sql 结构

• 分库分表

• 读写分离

3.3 深度分页如何优化

# 从第 500000 行开始获取 10 条数据，获取很多无用的数据
# select * 会拷贝全部列数据，当数据量比较大时，比较耗费性能、存储
​
select * from xxx order by id limit 500000, 10;

当 offset 非常大时，server 层会从引擎层获取到很多无用的数据，以下为优化方案：

1、将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询：

# last_id 为上一页最后一条记录
select * from xxx where id > last_id order by id limit 10;

2、配合子查询：

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

先执行子查询 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在 innodb 中的主键索引中获取到500000+1条数据，然后 server 层会抛弃前 500000 条，只保留最后一条数据的 id 。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

通过子查询后，SQL 就变成了：

select * from xxx  where id >=500000 order by id limit 10;

这样 innodb 再走一次主键索引，通过B+树快速定位到 id=500000 的行数据，时间复杂度是 lg(n)，然后向后取10条数据

4. 数据库表结构优化

4.1 选择合适的数据类型

• 使用可以存下你的数据的最小的数据类型

• 使用简单的数据类型。int 要比varchar类型在mysql处理上更简单

• 尽可能的使用not null定义字段

• 尽量少用text类型，非用不可时最好考虑分表

• 使用int来存储日志时间，利用FROM_UNIXTINE()(得到日期),UNIX_TIMESTAMP()(得到时间戳)两个函数来进行转换

• 使用bigint来存ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换

使用 int 存储日期时间

利用 FROM_UNIXTIME()、UNIX_TIMESTAMP() 这两个函数来进行转换：

create table test(
    id int auto_increment not null,
    timestr int,
    primary key(id)
);
​
insert into test(timestr) values(UNIX_TIMESTAMP('2019-07-13 13:12:00'));
​
select FROM_UNIXTIME(timestr) from test;

使用 bigint 存储 IP 地址

利用 INET_ATON()、INET_NTOA() 这两个函数来进行转换：

create table sessions(
    id int auto_increment not null,
    ipaddress bigint,
    primary key(id)
);
​
insert into sessions(ipaddress) values(INET_ATON('192.168.0.1'));
​
select INET_NTOA(ipaddress) from sessions;

4.2 表的范式化和反范式化

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

下表存在一些传递函数依赖关系：商品名称 -> 分类 -> 分类描述，所有下表是一个非范式化

商品名称	价格	重量	有效期	分类	分类描述
可乐	3.00	250 ml	2019.7.1	饮料	碳酸饮料
北冰洋	3.00	250 ml	2019.8.1	饮料	碳酸饮料

不符合第三范式要求的表存在下列问题：

• 数据冗余：（分类，分类描述）对于每一个商品都会进行记录

• 数据的插入异常

• 数据的更新异常

• 数据的删除异常

对于反范式化的数据表来说，最好的优化方法是将其拆分为多个表，但也不是拆分的越多越好，表越多连接查询越困难。上表中我们可以将分类单独拆分一个独立的表，然后与商品多对多连接。

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询的目的，反范式化是一种以空间来换取时间的操作。

4.3 表的垂直拆分

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：

• 把不常用的字段单独存放到一个表中

• 把大字段独立存放到一个表中

• 把经常一起使用的字段放到一起

表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。 常用的水平拆分方法为：

• 对id进行hash运算，如果要拆分成5个表则使用mod(id,5)去除0-4个值

• 针对不同的hashID把数据存到不同的表中

• 挑战：跨分区表进行数据查询、同级后台报表操作

4.4 存储引擎选 Innodb

没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb 存储引擎（mysql5.5之前默认使用 Myisam，5.6以后默认的为 Innodb）。

Innodb支持事务，支持行级锁，更好的恢复性，高并发下性能更好，MyISAM 不支持事务

参考

• 还不了解 MyISAM 和 InnoDB 的区别？看这里就够了

4.5 主键自增不够了怎么办

每个 Innodb 表必须有个主键，Innodb 是按照主键索引的顺序来组织表的：

• 不要使用更新频繁的列作为主键，不适用多列主键

• 不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）

• 主键建议使用自增ID值

5. 集群 & 并发 & 其他

并发

MySQL 如何解决高并发场景下的插入重复

在高并发场景下，可能会出现多个线程或进程同时插入相同的数据，这时候就可能出现插入重复的问题。解决这个问题，可以采用以下方法：

• 在数据库中设置唯一约束，例如使用唯一索引或主键约束，保证数据库中不会存在重复数据。当出现插入重复时，数据库会抛出唯一约束冲突的异常，应用程序可以捕获该异常并进行相应的处理，例如重试操作。

• 使用 MySQL 提供的 REPLACE INTO 或 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句进行插入操作。当出现重复数据时，这两种语句会自动删除原来的数据，再插入新数据，或者更新原有数据。需要注意的是，使用 REPLACE INTO 或 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句会影响数据库的性能，因为每次插入操作都需要对数据库进行检查，建议在数据量较小的情况下使用。

• 在应用程序中使用分布式锁，对需要插入的数据进行加锁，保证同一时刻只有一个线程或进程能够进行插入操作。这种方法需要引入分布式锁管理工具，例如 Redis、Zookeeper 等，并且需要考虑锁的有效期、锁的粒度等问题，实现起来比较复杂。

需要根据具体业务场景和系统性能情况选择适合的方法。

MySQL + Redis 分布式锁简易示例

import redis
import pymysql
import time
​
class DistributedLock(object):
    def __init__(self, key, timeout=10, sleep=0.1):
        self.key = key
        self.timeout = timeout
        self.sleep = sleep
        self.redis_client = redis.Redis(host='localhost', port=6379)
        self.mysql_conn = pymysql.connect(host='localhost', port=3306, user='root', password='password', db='test')
​
    def acquire(self):
        start_time = time.time()
        while time.time() - start_time < self.timeout:
            # 使用 Redis 的 setnx 命令尝试获取锁
            if self.redis_client.setnx(self.key, 1):
                return True
            time.sleep(self.sleep)
        return False
​
    def release(self):
        # 使用 Redis 的 del 命令释放锁
        self.redis_client.delete(self.key)
​
    def do_something(self):
        try:
            with self.mysql_conn.cursor() as cursor:
                # 进行需要加锁的操作，例如插入数据
                sql = "INSERT INTO my_table (col1, col2) VALUES (%s, %s)"
                cursor.execute(sql, ('value1', 'value2'))
                self.mysql_conn.commit()
        finally:
            self.release()
​
if __name__ == '__main__':
    lock = DistributedLock('my_lock')
    if lock.acquire():
        try:
            lock.do_something()
        except Exception as e:
            print(e)
        finally:
            lock.release()
    else:
        print('Failed to acquire lock')

以上代码中，DistributedLock 类封装了 Redis 和 MySQL 的连接，并提供了 acquire 和 release 方法实现分布式锁的获取和释放。其中，acquire 方法使用 Redis 的 setnx 命令尝试获取锁，release 方法使用 Redis 的 del 命令释放锁。在需要加锁的操作中，使用 do_something 方法进行实际操作，例如插入数据，操作完成后再调用 release 方法释放锁。

在主程序中，首先尝试获取分布式锁，如果获取成功，则进行需要加锁的操作，否则提示获取锁失败。

需要注意的是，在实际应用中，需要对分布式锁的有效期、锁的粒度等问题进行考虑和调整。

大表查询慢怎么优化？

当表的记录有千万行时，查询比较慢，该如何优化？一些常见的优化措施如下：

• 合理建立索引。在合适的字段上建立索引，例如在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

• 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能

• 利用缓存。利用Redis等缓存热点数据，提高查询效率

• 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内

• 读写分离。经典的数据库拆分方案，主库负责写，从库负责读

  • 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分

MySQL 单表多大需要分库分表

目前主流的两种说法：

• 单表大于 2000w 行，性能会明显下降

• 阿里巴巴 Java 开发手册提出单表超过 500w 或单表容量超过 2GB

事实上具体达到多少才要分库分表，需要根据实践情况分析，与 MySQL 的配置及机器硬件也有关。MySQL 为了提高性能，会将表的索引装载到内存中，在 InNoDB buffer size 足够的情况下，能够完全加载到内存，查询不会有问题。

若单表达到某个量级上限时，导致内存无法存储其索引，就要考虑分库分表了。另外在 阿里巴巴 Java 开发手册中建议：如果预计三年后数据量无法到达这个级别，请不要在创建表时就分库分表

乐观锁和悲观锁是什么

乐观锁和悲观锁是并发控制时采用的两种机制，并发控制是确保多个事务同时存取数据中同一份数据时不破坏事务的隔离性、统一性

• 悲观锁：假定会发生冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改，实现方式：使用数据库中的锁机制。

• 乐观锁：假定不会发生冲突，只在提交操作时检查数据是否被修改过，若相等则表示数据没有被修改过，否则为脏数据，不能修改，实现方式：使用版本号机制或 CAS 算法实现

查询语句执行流程

查询语句执行流程有：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎

 select * from user where id > 1 and name = "rose";

• 首先检查权限，没有权限则返回错误

• 8.0 以前会查询缓存，缓存命中则直接返回，没有则执行下一步

• 词法分析和语法分析，提取表名、查询条件，检查语法是否有错误

• 两种执行方案，先查 id > 1 还是 name = rose，优化器根据自己的优化算法选择执行效率最好的方案

• 校验权限：有权限就调用数据库引擎接口，返回引擎执行结果

binlog、redo log 和 undo logo

MySQL 日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。

bin log 二进制日志

MySQL 数据库级别的文件，记录对数据库执行修改的所有操作，不会记录 select 和 show 语句，主要用于恢复数据和同步数据库

redo log 重做日志

是 Innodb 引擎级别的日志文件，用来记录 innodb 存储引擎的事务日志，不论事务是否提交都会记录，用于数据恢复。当数据库发生故障，innodb 存储引擎会使用 redo log 恢复到发生故障前的时刻，以此保证数据的完整性。

将参数 innodb_flush_log_at_tx_commit 设置为1，那么在执行commit 时会将redo log 同 步写到磁盘。

undo log 回滚日志

用于数据的撤回操作，保留了记录修改前的内容，可以使用它实现事务回滚，并且可以根据它回溯到某个特定版本的数据，实现 MVCC

bin log 与 redo log 的区别

• binlog 会记录所有存储引擎的日志记录，redolog 只记录 innodb 自身的事务日志

• binlog 只在事务提交前写入到磁盘，一个事物只写一次，而在事务进行过程中，redo log 会不断写入磁盘

• binlog 是逻辑日志，记录的是 sql 语句的原始逻辑，redolog 是物理日志，记录的是某个数据页上做了什么修改

B+ 树索引和哈希索引有什么区别？

MySQL 中索引是用于提高查询效率的一种数据结构，常见的索引类型有 B+ 树索引、哈希索引和全文索引。请问 B+ 树索引和哈希索引有什么区别？

B+ 树的原理

B+ 树是一种多叉树，它的数据结构类似于平衡树，但不同于二叉树，每个节点可以有多个子节点。它是将数据分成多块，每块都有一个指针指向下一个块，通过这样一层层的指针，可以快速查找到数据。适合范围查找和排序，因为它的数据是安装顺序排列的。

B+ 树每个节点可以拥有更多的子节点。B+ 树的节点有两种类型：内部节点和叶子节点。叶子节点包含了数据行的信息，而内部节点则作为索引用来指向下一层节点。B+ 树的每个节点都存储了一定数量的索引信息，这样可以减少磁盘 I/O 操作的次数，从而提高查询效率。在 B+ 树中，数据行只存在于叶子节点，而非叶子节点只存储键值信息，这也是 B+ 树相对于 B 树的一个优势。

哈希索引原理

将数据化计算成一个哈希表的 key，存在哈希表中，数据行的值作为哈希表的 value。适合等值查找，比如主键查询数据，由于哈希索引是将数据行的值作为哈希表的 value，因此若要查询数据的其他列，需要先根据哈希索引查出主键，再通过主键查找数据，效率较低。

• 哈希索引适合等值查询，无法进行范围查询和模糊查询

• 哈希索引没有办法利用索引完成排序

• 哈希索引不支持多列联合索引的最左侧匹配规则

• 若有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

• 数据结构不同：B+树是一种多叉树，每个节点可以有多个子节点；哈希表是一种基于哈希函数的数据结构。

• 适用场景不同：B+树适合范围查找和排序，哈希索引适合等值查找。

• 索引效率不同：B+树索引的查询效率稳定，而哈希索引的效率会受到哈希函数的影响。

• 存储方式不同：B+树索引数据存储在磁盘中，而哈希索引存储在内存中。

总之，B+树索引适合处理大量数据，而哈希索引适合处理小型数据集。

请问什么是 SQL 注入漏洞？如何避免 SQL 注入漏洞？

SQL注入漏洞是一种常见的网络安全攻击方式，攻击者通过在输入的数据中注入恶意SQL代码，从而在后台数据库中执行非法的SQL操作，例如删除、修改、插入、查询等，从而获得敏感信息或者破坏数据库的完整性。

为了避免SQL注入漏洞，可以采取以下措施：

• 使用预编译语句或参数化查询来防止 SQL 注入，这样可以将 SQL 语句与用户输入的数据分离开来，有效地避免了 SQL 注入漏洞。

• 对用户输入的数据进行严格的数据验证和过滤，过滤掉不合法的字符，例如单引号、双引号等。

• 对敏感数据进行加密存储，比如使用加密算法对密码进行加密。

• 使用安全的存储引擎，例如InnoDB，InnoDB支持事务和行级锁定，可以提高数据的安全性。

• 尽量避免使用 root 用户来连接数据库，以及在程序中直接将数据库用户名和密码明文写入代码中，这样可以有效地避免密码泄漏。

原文地址：https://blog.csdn.net/AVICCI/article/details/142344784

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！