该文章下的 mysql 题目取自网络,我是二次进行对应延伸的思考

1.MYSQL 索引使用的话要有哪些注意事项:

此问题可从两个角度来回答,索引不适用于那些场景,索引哪些场景下会失效

1.索引哪些场景下会失效

查询条件包含 or

原因:1.这我们就需要提到 mysql 的查询优化器的查询策略，他会根据查询条件和索引情况选择最优的查询计划,当 or 条件的一列未被索引,查询优化器可能会认为全表扫描比使用索引效率高，在数据分布不均匀或者大量重复值的情况下，全表扫描的优先级也可能比使用索引效率高,而且如果 or 条件设计多个索引的时候,mysql 对多个索引扫描，排序，合并，这本身就挺耗费 cpu 内存资源的。
解决办法:用 or 的查询分成多个子查询，这样每个子查询可以单独使用其自身的索引，第二个方法就是创建复合索引，比如 select * from user where col1 = val1 or col2 = val2 的查询，可以创建一个(col1,col2)的复合查询

like通配符会导致索引失效.(原因及解析)

原因:like “abc%”,就是 abc 作为前缀匹配的，索引有效，因为索引是按列值的顺序排列的，如果是后缀匹配 like %abc,索引就会失效,任意位置匹配像 like %abc% 也会失效,这 mysql 通常会走全表扫描
解决办法:尽量使用前缀匹配

联合索引，查询时的条件列不是联合索引中的第一个列，索引失效.(原因及解析)

原因:联合查询遵循最左前缀法则，查询条件必须从最左侧列查询，所以索引会失效
解决办法:拆分查询逻辑

对索引字段进行函数运算(原因及解析)

原因:对索引字段进行函数运算,会改变原本字段的原始值,而索引是依靠原始字段值进行构建的，有序性就被破坏了
解决办法:1.对要索引的查询字段不要使用函数运算 2.要不就在设计一个新字段，比如折扣价是原价的 0.9

对索引字段进行!=，not in，is null会导致索引失效(原因及解析)

原因:这些运算符通过会导致查出的数据不是唯一，mysql 可能会运用全表扫描
解决办法:1.将查询拆分为子查询

2.索引不适用于哪些场景

  • 数据量少的不适合加索引
  • 离散性低的字段不适合加索引，比如性别就男和女
  • 更新比较频繁也不适合加索引

2.InnoDB 与 MyISAM 的区别

解析
  • MyISAM 是 mysql 早期的存储引擎，支持全文索引，5.7 以下版本的 InnoDB 不支持全文索引
-- 开启事务
START TRANSACTION;

-- 从账户A中扣除100元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 向账户B中增加100元
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交事务
COMMIT;
  • MyISAM 不支持事务，要么全部成功，InnoDB 支持事务，事务是指一组逻辑操作单元，这些操作要么全部失败，事务的四个特性(ACID)为
    • 原子性:事务作为一个整体被执行，其中包含的操作要不全部执行，要不全部不执行
      实现原理:mysql 通过 redolog(重做日志)和 undolog(回滚日志),redolog 记录对数据的修改操作，在事务提交的时候写入磁盘，在系统
      崩溃的时候，可以通过 redolog 恢复数据，undolog 记录数据被修改前的数据状态,如果执行失败，可以恢复事务可是的状态

      log 的写入顺序，事务提交前先写入 undolog，在写入 redolog，只有 redolog 完成后，事务才算真正提交，为啥是这样的顺序呢

      在事务提交之前，先写入 Undo Log 可以确保在事务失败时，系统能够通过 Undo Log 恢复到事务开始之前的状态。如果先写入 Redo Log，而 Undo Log 尚未写入，那么在事务失败时，系统将无法撤销部分已完成的修改，从而破坏原子性

    • 一致性:事务执行前后，数据库从一个一致的状态转换成另一个一致的状态，比如 A+B 两个人一共有 2 万元，A 转给 B5000，这个事务执行完他俩一共还是只有 2 万元
    • 隔离性:多个事务并发访问时，事务之间是相互隔离的，即一个事务不会影响其他事务的运行效果

      ET:并发和并行有啥区别:并发指的是一个 cpu 处理多个任务，并行是多个 cpu 处理多个任务，就好比吃馒头，并发是一个人吃三个馒头，只不过


      它在一直轮转，让你以为是三个馒头一起被吃，而并行是三个人吃三个馒头


      共享锁允许多个事务同时读取事务，但是只能有一个修改，排他锁是不让其他人读取和修改
      事务隔离级别:读未提交,读已提交,可重复读,串行化

      那这个可重复读读取的都是一个快照，第一个事务处理完，那第二个事务不是还是原来的快照版本


      在MySQL的可重复读（Repeatable Read）隔离级别下，第二个事务看到的是事务开始时的快照版本，而不是第一个事务提交后的最新版本。因此，第二个事务的修改是在快照版本的基础上进行的，而不是在第一个事务提交后的版本上进行的。这可能会导致一些并发问题，比如丢失更新或不可重复读


      如何去避免这个问题呢:

      - 1.使用 SELECT … FOR UPDATE 或 SELECT … LOCK IN SHARE MODE,这些操作会读取最新的数据版本，并且会对数据加锁，防止其他事务修改 - 2.将事务隔离级别设置为读已提交（Read Committed）。在读已提交隔离级别下，每次读取都会看到最新的已提交数据版本，而不是事务开始时的快照版本 - 3.使用串行化,这样并发性能是最低的
      持久性:表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中
  • MyISAM 没有外键，InnoDB 有外键,外键是一种数据库约束，用于主表删除数据时，从表数据也进行更改。所以 InnoDB 可以进行级联删除，级联更新的操作
  • MyISAM 不支持 MVCC，MVCC 是指多版本并发控制，意思是每次对数据进行修改的时候，数据库都会生成一个新数据版本，允许读数据时候不加锁，MVCC 支持可重复读和读已提交的隔离级别
  • MYISAM 不支持行级锁,InnoDB 行级锁和表级锁都支持
  • MyISAM 的表是可以没有主键的
  • InnoDB 查询的速度比 MyISAM 快,插入和删除慢一些，因为 InnoDB 数据和索引放在一个叶子结点中，M

3.limit 大量数据(比如 100000) 加载很慢的话，你是怎么解决的呢

解析
  • 1.从业务的角度看,普通用户会爬取到这么纵深的数据吗，是不是应该返回做一些限制
  • 2.如果 id 数据是连续自增的，可以返回上次查询的最大偏移量，然后再往下 limit
select id，name from employee where id>1000000 limit 10
  • 3.order by + 索引（id 为索引）
select id，name from employee order by id  limit 1000000，10

4.在高并发的情况下,如何做到安全的修改一行数据

解析

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案

  • 悲观锁:当前线程要进来修改数据的时候,本次事务提交之前,其他线程都不能进来修改数据,可使用 select…for update
  • 乐观锁:有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现

    ET: CAS算法是啥

    AS算法的核心思想是通过一个原子操作来比较和更新变量的值如果当前变量的值等于预期值（即V == A），则将变量更新为新值（即V = B），并返回true。 如果当前变量的值不等于预期值（即V != A），则不进行任何操作，并返回false

import threading

# 创建一个锁
lock = threading.Lock()

# 定义一个共享变量
shared_variable = 0

def increment():
    global shared_variable
    with lock:  # 使用锁确保操作的原子性
        shared_variable += 1

# 创建多个线程
threads = [threading.Thread(target=increment) for _ in range(10)]

# 启动线程
for thread in threads:
    thread.start()

# 等待线程完成
for thread in threads:
    thread.join()

print(shared_variable)  # 输出：10

5.mysql 中 in 和 exists 的区别

1.exist

select * from user where exists (select 1);

SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
伪代码实现
for (i = 0; i < count(A); i++) {
    a = get_record(A, i); #从A表逐条获取记录
    if (B.id = a[id]) { #如果子条件成立
        result[] = a;
    }
}
return result;
# 这里主要用到了B表的索引，A表如何对查询的效率影响不大

exists 对外表用 loop 逐条查询，每次查询都会查看 exists 的条件语句，当 exists 里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前 loop 到的这条记录；反之，如果 exists 里的条件语句不能返回记录行，则当前 loop 到的这条记录被丢弃，exists 的条件就像一个 bool 条件，当能返回结果集则为 true，不能返回结果集则为 false
总结：如果 A 表(外表)有 n 条记录，那么 exists 查询就是将这 n 条记录逐条取出，然后判断 n 遍 exists 条件
nns

2.in

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
等效于
SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;
# 只对b进行一次查询
# 这里主要是用到了A的索引，B表如何对查询影响不大。

in 查询就是先将子查询条件的记录全都查出来，假设结果集为 B，共有 m 条记录，然后再将子查询条件的结果集分解成 m 个，再进行 m 次查询
in 查询的子条件返回结果必须只有一个字段
mms

表的规模不是看内部表和外部表，而是外部表和子查询结果集
当子查询结果集很大，而外部表较小的时候，Exists 的 Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于 IN
当子查询结果集较小，而外部表很大的时候，Exists 的 Block 嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时 IN 的查询效率会优于 Exists

到这里教程一就已圆满结束了，感谢你的浏览，thank you