MySQL-索引和事务-EW帮帮网

简介

上一篇文章讲了mysql的基本使用，这一篇继续介绍mysql的使用，包括：存储引擎、索引、事务、执行计划、数据库设计

存储引擎

存储引擎是MySQL的底层软件组织，用于创建、查询、更新、删除数据，不同的存储引擎提供了不同的特性。

查看MySQL支持哪些存储引擎：SHOW ENGINES;

比较重要的存储引擎：

InnoDB：支持外键、事务，MySQL默认的存储引擎
MyISAM：不支持事务、外键，查询速度快
MEMORY：置于内存的表

查看表的存储引擎：SHOW CREATE TABLE <表名>;

在建表时指定存储引擎：CREATE TABLE <表名>(字段数据类型, …)ENGINE=存储引擎;

索引

索引：index，在MySQL中索引也叫key或键。

作用：索引对一列或多列的值进行排序，同时提供值所在行的位置信息，从而达到快速访问的目的。

优缺点：

优点：索引会加快数据的检索速度，
缺点：索引会占用物理空间，当对表的数据进行增删改的时候，索引也要动态的维护，这样就会降低数据的维护速度

使用索引

查看索引：SHOW INDEX FROM <表名>;
创建索引：
- CREATE { INDEX | UNIQUE INDEX} 索引名称 ON 表名(列名);
- ALTER TABLE 表名 ADD {INDEX | UNIQUE} 索引名(列名);
删除索引：DROP INDEX <索引名> ON <表名>;

案例：查看student表的索引

SHOW INDEX FROM student\G

*************************** 1. row ***************************
        Table: student  -- 表名
   Non_unique: 0
     Key_name: PRIMARY   -- 索引名称
 Seq_in_index: 1
  Column_name: id        -- 索引所在列的名称
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE    -- 索引的数据类型
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

案例2：索引对于查询效率的影响。

为了演示索引对于查询效率的影响，在这里，向student表中插入一万条数据，然后，在name列上创建索引。

向student表中插入一万条数据：这个java代码，一次性可以生成向student表中插入5000条数据的sql。

import java.util.Random;

public class Test3 {
    private static final String sql = "INSERT INTO student (name, date_of_birth, class) VALUES ";

    public static void main(String[] args) {
        StringBuilder sBuilder;
        String name;
        String dateStr;
        String className;
        Random random = new Random();
        for (int i = 0; i < 100; i++) {
            sBuilder = new StringBuilder();
            sBuilder.append(sql);
            for (int j = 0; j < 100; j++) {
                int a = random.nextInt(100);
                int b = random.nextInt(100);
                name = randomChineseChar() + (a * b);
                dateStr = "2018-05-04 08:30:00";
                className = "1班";
                sBuilder.append("('").append(name).append("', '")
                        .append(dateStr).append("', '")
                        .append(className).append("'), ");
            }
            sBuilder.delete(sBuilder.length() - 2, sBuilder.length());
            sBuilder.append(";");
            System.out.println(sBuilder);
        }
    }

    private static String randomChineseChar() {
        final char[] charArr = {'流', '六', '三', '千', '去'
                , '是', '时', '额', '人', '他'
                , '到', '的', '突', '哦', '有'
                , '对', '但', '非', '方', '符'
                , '我', '为', '位', '完', '五'
                , '无', '外', '武', '未', '尾'
                , '王', '伪', '晚', '唯', '握'
                , '饿', '恶', '俄', '鹅', '厄'
                , '鄂', '然', '热', '日', '仁'
                , '让', '入', '弱', '软', '如'};
        Random random = new Random();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < 3; i++) {
            int idx = random.nextInt(charArr.length); // 汉字的Unicode码范围
            sb.appendCodePoint(charArr[idx]);
        }
        return sb.toString();
    }
}

在name字段上创建索引：CREATE INDEX student_name_idx ON student(name);

查看创建结果：SHOW CREATE TABLE student\G

*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `date_of_birth` datetime DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_name_idx` (`name`)                   -- 刚刚创建的索引
) ENGINE=InnoDB AUTO_INCREMENT=13409 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在索引列上查询数据：可以看到，在使用了索引的情况下，一万多条数据，只花费了0秒

SELECT * FROM student WHERE name = '俄对然4978';

+-------+---------------+---------------------+-------+
| id    | name          | date_of_birth       | class |
+-------+---------------+---------------------+-------+
| 13387 | 俄对然4978    | 2018-05-04 08:30:00 | 1班   |
+-------+---------------+---------------------+-------+
1 row in set (0.00 sec)

删除刚刚创建的索引：DROP INDEX student_name_idx ON student;

删除索引后查询数据：在删除索引后查询数据，花费了0.05秒

SELECT * FROM student WHERE name = '俄对然4978';

+-------+---------------+---------------------+-------+
| id    | name          | date_of_birth       | class |
+-------+---------------+---------------------+-------+
| 13387 | 俄对然4978    | 2018-05-04 08:30:00 | 1班   |
+-------+---------------+---------------------+-------+
1 row in set (0.05 sec)

索引的优化措施

创建索引时

1、在合适的列上创建索引：例如ID列、where条件需要用到的列

2、索引列应该被限制为NOT NULL，并且没有太多的重复值，IS NOT NULL会让索引失效，但是IS NULL不会

3、在使用InnoDB做存储引擎时，如果没有特别的需要，最好使用一个与业务无关的自增字段做主键索引，因为这样可以避免频繁的移动、分页操作，以及它们所带来的内存碎片

使用索引时

1、最左前缀优化

如果是单列索引，并且索引是一个字符串，那么会对索引依据字典顺序进行排序，
如果联合索引，那么首先会对第一列进行排序，在第一列有序的情况下对第二列进行排序，依次类推，如果查询条件中，联合索引第一列没有被使用到，那么不走索引
在对字符串使用LIKE进行模糊查询时，如果通配符在最左边，那么不走索引。

2、不要在索引列上进行计算

案例：select * from users where YEAR(add_date)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此可以改成 select * from users where add_date<‘2007-01-01’;

3、如果WHERE子句中含有 != 、not in 操作符，不走索引

4、对于连续的数值，使用BETWEEN来代替

5、用UNION、UNION ALL来代替OR，对于OR运算符，如果一个字段有索引，一个字段没有索引，将导致引擎放弃索引而使用全表扫描

6、正则表达式不走索引

7、使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

8、排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

会让索引失效的情况

违反最左原则（复合索引、like模糊查询）
查询的字段有一个隐式的类型转换会造成索引失效
索引字段上有计算
不等于条件、is not null
or关键字（只有在左右都有索引时才会生效）

索引的数据结构

mysql使用b+树来作为索引的数据结构

b+ 树

b+树是从b数演变过来的，类似的还有b-树

b树：b树是从二叉排序树变化而来。

结构：
- 在一个节点存放多个键，每个键之间都是有序的，
- 键与键之间有一个指针，指向下一个节点，
- 下一个节点的所有键都在这个指针两边的键的范围之内。
优点：b树降低了二叉树的高度，从而减少了读盘的次数。

b-树：

每个节点存储多个索引关键字和索引对应行的物理地址
每个节点的两端是指针
索引文件和数据文件是两个文件，这种索引叫做非聚集索引

b-树的图片：

在这里插入图片描述

b+树：

每个节点的key数和指针数相等，
指针左侧的key会被包含在下一个节点中，
非叶子节点只存储key，不存储data，它的存在的目的是通过树结构加快查找速度
叶节点包含所有的索引关键字，并且叶节点中存储数据本身。
索引文件和数据文件放在一起，这种索引叫做聚集索引。
双向链表：每个叶子节点增加一个指向相邻叶子节点的指针，所有叶子节点间形成一个双向链表，这么做是为了优化范围查询

b+树的图片：

在这里插入图片描述

索引和存储引擎

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

innodb存储引擎只支持b+tree索引。

默认主键：在b+tree中，InnoDB 要求表必须有主键，如果没有显式指定，则MySQL自动为 InnoDB表生成一个隐含字段作为主键，类型为长整形。

主键索引：InnoDB默认使用主键作为索引，叫做主键索引

辅助索引：主键索引之外的索引叫做辅助索引，所有辅助索引都引用主键作为data域。

回表：使用辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键索引检索记录，这种检索方式叫做回表。

索引覆盖：如果只用到辅助索引而没有用到主键索引，叫做索引覆盖。

非聚集索引和聚集索引

非聚集索引：索引文件和数据文件不存放在一起，这种索引叫做非聚集索引。

聚集索引：索引文件和数据文件存放在一起，这种索引叫做聚集索引。

索引在磁盘上的存储

InnoDB的表对应两个文件：

frm文件，表结构文件；
ibd文件，索引和数据文件

MyISAM的表对应三个文件：

frm文件，表结构文件；
myd文件，数据文件；
myI文件，索引文件。

事务

事务：一系列sql的集合，这些sql要么全部执行，要么全部不执行

自动提交模式：MySQL默认采用自动提交模式，在自动提交模式下，每个SQL语句都会被当做一个事务提交。

事务的使用

把多个命令组成一个事务

开启一个事务：start transaction 或 begin
如果失败则回滚：rollback
如果成功则提交：commit

案例：开启两个mysql客户端，在两个客户端同时执行sql，查看事务执行过程中的sql的互相影响。

1、首先查询任意3条数据，随后对这三条数据做增删改查，查看事务的影响。

mysql> SELECT * FROM student LIMIT 100000, 3;
+--------+---------------+---------------------+-------+
| id     | name          | date_of_birth       | class |
+--------+---------------+---------------------+-------+
| 100001 | 武位有4160    | 2018-05-04 08:30:00 | 1班   |
| 100002 | 让他晚5795    | 2018-05-04 08:30:00 | 1班   |
| 100003 | 晚到日6630    | 2018-05-04 08:30:00 | 1班   |
+--------+---------------+---------------------+-------+
3 rows in set (0.03 sec)

客户端1：开启事务，修改id为100002的学生的class为2班

-- 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 修改id为100002的学生的class为2班
mysql> UPDATE student
    -> SET class = '2班'
    -> WHERE id = 100002;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

客户端2：查询id为100002的学生，并且尝试修改它的class为3班

-- 查询id为100002的学生，此时客户端1在这条数据上有一个事务，但是不耽误客户端2查询该条数据，
-- 可见，事务读写不互斥，而且不会读到另一个事务未提交的数据
mysql> SELECT *
    -> FROM student
    -> WHERE id = 100002;
+--------+---------------+---------------------+-------+
| id     | name          | date_of_birth       | class |
+--------+---------------+---------------------+-------+
| 100002 | 让他晚5795    | 2018-05-04 08:30:00 | 1班   |
+--------+---------------+---------------------+-------+
1 row in set (0.00 sec)

-- 尝试修改id为100002的学生的class为3班，本条sql会阻塞，因为客户端1正在这条数据上执行事务
-- 写写互斥。
mysql> UPDATE student
    -> SET class = '3班'
    -> WHERE id = 100002;
Query OK, 1 row affected (14.21 sec)   -- sql执行了将近14秒
Rows matched: 1  Changed: 1  Warnings: 0

客户端1：提交事务

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

客户端1提交事务后，客户端2被阻塞的sql也可以正常执行了。

事务的特性

事务有四大特性，原子性、持久性、隔离性、一致性，它们简称ACID

原子性：SQL语句要么全部执行，要么全部不执行。
持久性：事务一旦提交，对数据库的改变就是永久性的，接下来的其他操作或故障不应该对其由任何影响。
隔离性：事务之间互不影响。
一致性：事务的最终目标，实现了原子性、隔离性、持久性，也就达到了一致性。

隔离性详解

事务的隔离级别：

读未提交：最低隔离级别，事务A可以读取到事务B未提交的数据，此时读取到的是脏数据
读已提交：事务A只能读取到事务B提交的数据，这种隔离级别会导致不可重复读，意味着事务A开始读取到的数据和随后读取到的数据是不同的
可重复读(RR)：MySQL的默认隔离级别，事务A在执行过程中，即使事务B修改了相关数据，事务A还是可以读取到之前的数据，也就是可重复读，但是它会造成幻读。幻读是事务A按照某个条件先后两次查询数据库，两次查询结果地条数不同。不可重复读是数据变了，幻读是行数变了。
串行化：最高隔离级别，一个事务在执行时，另一个事务只能等待。

不同隔离性之间会造成的问题：

读未提交会造成脏读：脏读，Dirty Read，A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。
读已提交会造成不可重复读：不可重复读，Unrepeatable Read，事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务B修改过了。
可重复读会造成幻读：幻读，Phantom Read，事务A重新执行一个查询，返回一系列符合查询条件的行，发现其中插入了被事务B提交的行。

mysql实现事务的方式

事务功能内部使用到的组件：撤销日志、重做日志、多版本并发控制、锁机制

撤销日志：undo log

当事务对数据进行修改时，存储引擎会生成对应的撤销日志，如果事务执行失败或调用了回滚，就利用撤销中的信息将数据回滚到修改之前的样子，
撤销日志是逻辑日志，记录的是SQL执行相关的信息，当发生回滚时，存储引擎会根据撤销日志的内容做与之前相反的工作。

重做日志：redo log

当数据修改时，会在redo log记录这次操作，当事务提交时，会调用接口对redo log进行刷盘，
如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log用来防止因MySQL宕机而引起的数据丢失。
redo log可以减少无效io。默认情况下，读和写都会经过缓冲区，缓冲区中的数据会定期刷盘，称为脏刷。redo log是顺序io，脏刷是以数据页为单位的，一个page上一个小修改都要整页写入，而redo log中只包含真正需要写入的部分，无效io大大减少

多版本并发控制

multi-version concurrency control，mvcc，数据库事务的并发控制机制，通过维护数据的多个版本，使得读操作无需加锁，同时保证一致性，减少事务之间的阻塞。

多版本并发控制的工作机制：

每一行数据都有隐藏列，隐藏列包括了该行数据的事务id、删除id和指向undo log的指针等等，事务id是创建或修改这行数据的最后一个事务的id，删除id是删除这行数据的事务的id
解决脏读：mvcc不会读取其它事务没有提交的数据
实现可重复读：当前事务读取数据时，如果发现数据行的删除id比当前事务id大，会根据undo log把数据恢复到对应于当前事务id的版本，从而实现可重复读。
解决幻读：在事务中读取数据时会使用锁机制对读取的范围进行标记，再次读取时如果发现版本号更高，会结合undo log执行回滚操作，避免了幻读。

锁机制

事务在修改数据之前，需要先获得相应的锁，获得锁之后，事务就可以修改数据，在事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

在一次事务中，如果对某行数据进行修改，这一行的数据会被锁定，其它事务只可以读取数据，不可以修改数据

按照锁的粒度划分：行锁、表锁、页锁

行锁：加锁粒度最小，但加锁的开销最大，有可能会出现死锁的情况，是InnoDB存储引擎的默认锁，行级锁按照使用方式分为共享锁和排他锁，共享锁多个线程可以同时读取数据，排他锁在同一时刻只能被一个线程占有。行锁是针对索引加的锁
页锁：一次锁定相邻的一组记录
表锁：一次锁定一张标，加锁粒度最大，资源开销最少，最容易发生锁冲突

InnoDB中几种锁：

record lock：在单条索引上加锁，而不是在数据上加锁，
gap lock：间隙锁，在索引的间隙之间加上锁，可以防止幻读，它可以把当前事务读取的所有行锁住，保证其它事务不在这些行上进行操作。
next-key lock：record lock 和 gap lock 的结合，即锁住当条索引，又锁住索引之间的间隙。

InnoDB锁的特性：通过非索引字段检索数据，加表锁。

死锁：在InnoDB中，当两个事务同时执行，一个锁住主键索引在等待其它索引，一个锁住其它索引在等待主键索引，就会发生死锁

锁的监控：执行SQL语句，查看关于锁的变量的信息：SHOW STATUS LIKE 'innodb_row_lock%';。在返回的结果中的变量的含义：

Innodb_row_lock_current_waits：当前正在等待锁的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间长度；
Innodb_row_lock_waits：系统启动到现在总共等待的次数；

事务的执行机制

事务的执行机制：

事务开始：事务开始时，InnoDB 为事务分配一个唯一的事务 ID
数据修改：当事务修改数据时，InnoDB 会记录修改前的数据到 Undo Log，并将修改后的数据写入 Redo Log。
事务提交：事务提交时，Redo Log 被持久化到磁盘，确保数据的持久性。
事务回滚：如果事务需要回滚，InnoDB 使用 Undo Log 中的信息撤销修改。
并发控制：通过 MVCC 和锁机制，InnoDB 确保并发事务的隔离性，允许并发读取和写入

mysql使用撤销日志实现原子性，使用重做日志实现持久性，使用多版本并发控制和行锁来实现隔离性

支持事务的存储引擎

MySQL中只有InnoDB存储引擎支持事务。

SQL的执行计划

使用explain关键字，可以查看sql的执行计划，执行计划展示了sql的执行细节，方便用户优化sql。

EXPLAIN关键字

使用explain关键字，MySQL会返回执行计划的信息，而不是执行SQL

案例：查询一个最简单的SQL的执行计划

EXPLAIN
SELECT id, name, date_of_birth, class
FROM student\G   -- 以\G结尾，这是mysql客户端支持的特殊格式

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

执行计划中的字段

id：SELECT的序列号，id越大，select越先执行
select_typr：查询的类型，有几种取值：
- simple：简单查询，查询不包含子查询和union
- primary：复杂查询中最外层的select
- subquery：包含在select中的子查询
- derived：包含在from中的子查询，mysql会将结果存放在一个临时表中，也称为派生表
- union：在union关键字后的select
table：当前查询访问的是哪一张表
partitions：匹配的分区
type：表示表的访问类型，即MySQL决定如何查找表的行，依次从最优到最差的分别为：system、const、eq_ref、range、index、all，和一个特殊的值：null。
- const、system：用于将主键索引或唯一索引的所有部分与常量值进行比较，system是const的一个特例
- eq_ref：在多表查询中，当连接条件使用主键索引或唯一索引时，将使用该值。
- ref：使用的是普通索引，索引要和某个值相比较，可能会找到多个符合条件的行
- range：使用索引来检索给定范围的行
- index：扫描全表索引
- all：全表扫描，MySQL需要从头到尾查找所需要的行
- null：表示mysql在执行阶段不需要访问表，例如查询索引列的最小值，只需要访问索引即可。
possible_keys：表示查询时可能使用的索引
key：表示实际使用的索引
key_len：表示mysql在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。索引最大长度是768字节，
- 当字符串过长时，mysql会做一个类似于前缀索引的处理，将前半部分的字符串提取出来做索引
ref：表示在key列记录的索引中，表查找值所用到的列或常量，如果是列，值为字段名，如果是常量，值为const
rows：预计扫描出的行数
extra：执行情况的描述和说明,
- Using index：使用索引覆盖
- Using index condition：查询的列不完全被索引覆盖，where 条件中是一个前导范围
- Using where：使用where语句来处理结果，查询的列未被索引覆盖
- Using temporary：mysql 需要创建一张临时表来处理查询
- Using filesort：将使用外部排序而不是索引排序
- 一些其它输出结果：No tables used, Null, Not optimized, outer query is empty

案例

案例1：查看使用普通索引时的执行计划

EXPLAIN
SELECT *
FROM student
WHERE name = '俄对然4978'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE               -- 简单查询，查询不包含子查询和union
        table: student
   partitions: NULL
         type: ref                  -- type是ref，表示使用的是普通索引，索引要和某个值相比较
possible_keys: student_name_idx
          key: student_name_idx      -- WHERE子句中name字段是有索引的，走了索引
      key_len: 203
          ref: const              
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

案例2：使用主键索引时的执行计划

EXPLAIN
SELECT *
FROM student
WHERE id = 10023\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: const
possible_keys: PRIMARY           
          key: PRIMARY           -- 使用主键索引
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

数据库设计

数据库设计：根据业务系统的需求，设计表结构和表与表之间的关系，为业务系统构造出最合适的数据存储模型，使数据能够被高效的存储和访问

数据库范式：设计关系数据库时需要遵守的规范，称为范式。要想设计—个好的关系数据库，必须使关系满足一定的约束条件，此约束已经形成了规范，称为范式。

范式的等级：范式分成几个等级，一级比一级要求得严格。

第一范式：每一个字段都不可再分
第二范式：每一行数据必须能够被唯一的标识
第三范式：一个表对应一个实体，不能包含其他特性

范式的优缺点：

优点：减少数据冗余，尽量让每个数据只出现一次，保证数据一致性
缺点：获取数据时，需要通过Join拼接出最后的数据

sql优化

慢sql

慢sql，执行时间比较长的sql，关于执行多久的sql算慢sql，每个公司都有不同的标准。慢sql会影响用户的访问速度，需要优化慢sql。

分析慢sql的步骤：

1、在mysql的配置文件中开启慢查询：

[mysqld]
slow_query_log = 1    # 开启慢查询
slow_query_log_file = /path/to/your/log-file-name.log  # 慢查询日志的位置
long_query_time = 2                                    # 超过多久算慢查询，这里是两秒

推荐在配置文件中开启，永久生效。

如果不想重启mysql服务，可以在命令行中设置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/path/to/your/log-file-name.log';
SET GLOBAL long_query_time = 2;

2、在命令行查看慢查询配置情况：

SHOW VARIABLES LIKE 'slow_query_log';  -- 其它变量也类似

3、查看慢查询日志：在配置信息中找到慢查询日志的位置，查看慢查询日志。

日志案例；

# Time: 2024-04-10T14:02:01.933982Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 1.201410  Lock_time: 0.000005 Rows_sent: 0  Rows_examined: 6938112
SET timestamp=1744193720;
select * from student where name like '%abc%';

这就是一条man查询sql的执行详情，日志中的关键信息：

Query_time：SQL 执行时间
Lock_time：锁等待时间
Rows_examined：扫描的行数
Rows_sent：返回的行数

4、使用mysql提供的工具来分析慢sql日志

执行命令： mysqldumpslow -s t -t 10 慢sql日志

-s t: 按查询时间排序。
-t 10: 显示最慢的 10 条 SQL

执行结果：这里一共两条sql，一条insert语句、一条select语句

Count: 2  Time=34.47s (68s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into student (name, date_of_birth, class) select name, date_of_birth, class from student

Count: 2  Time=1.43s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  select * from student where name like 'S'

5、分析慢sql的执行计划，尝试优化慢sql，从索引等角度

总结：这里主要介绍慢sql日志相关的知识点，sql的优化放在下节

常见的优化策略

数据表创建

合理设计表结构：

避免过度范式化：适当冗余高频查询字段。
垂直拆分：将大字段（如 TEXT）拆分到单独表。
合理使用数据类型

索引相关

常用的条件字段加索引

常用的条件字段、join字段加索引，确保where和join可以走索引，避免全表扫描。要注意，索引会优化查询情况，但是会减缓新增数据的速度，因为新增数据时需要额外维护索引。

索引的最左匹配原则

当使用复合索引进行查询时，查询条件必须从索引的最左边的列开始匹配，才能有效利用索引

避免索引失效的情况

1、对索引列使用函数或运算

-- 索引失效，对索引列进行运算
SELECT * FROM users WHERE YEAR(create_time) = 2023;

2、LIKE 左模糊匹配

SELECT * FROM users WHERE name LIKE '%张%';

避免使用 select *

明确要获取的字段，最少字段原则，减少数据量

优化分页语句

limit字段，如果偏移量特别大，会导致查询速度变慢，例如，偏移量为0，取10条数据，只需要扫描10条数据，偏移量为1000，取10条数据，就需要扫描1010条数据。

常见的优化措施：

使用上一页的id：SELECT * FROM logs WHERE id > ${上一页的id} LIMIT 20;，这种方法比较简单，但是缺陷也比较大，它不支持随机查看某一页。
建立一张新表，只存储几个查询条件需要的关键字段，表中的数据可以放在elastic search等数据库中，通过这张表来快速计算出某一页的id，然后通过id去表中查数据。

不常见的优化策略

这里的优化策略是从网上找来的，经过我的实际验证，未必有用，需要分情况，这里做个总结。

JOIN 用小表驱动大表

案例：小表在前，大表在后

-- 假设 users 表小，orders 表大
SELECT * 
FROM users u 
    JOIN orders o 
    ON u.id = o.user_id 
WHERE u.age > 30;

这个规则知道就好，mysql内部会自动优化，我对比了小表驱动大表和大表驱动小表的执行计划，完全是一样的。

小表驱动大表：score是小表，student是大表

在这里插入图片描述

大表驱动小表：

在这里插入图片描述

可以看到，不论哪张表在前，执行计划完全一致。小表驱动大表是数据仓库中的概念，和mysql这样的数据库是不一样的。

用 JOIN 替代 IN 子查询

案例：

-- in语句
select * 
from student 
where id in (select student_id from score);  -- student_id字段有索引

-- join语句
explain select st.*
from student st
    join score sc
    on st.id = sc.student_id;

首先，未必可以直接用join来替代in，可能使用join还需要更复杂的计算，其次，分析了它们的执行计划，是一样的。

in的查询计划：

在这里插入图片描述

join的查询计划：

在这里插入图片描述

总结

sql优化的基本操作：

合理设计表结构，大字段单独存放，
常见查询字段加索引，使用时记得最左匹配原则，不要在索引列上计算

MySQL-索引和事务

简介

存储引擎

索引

分类

使用索引

索引的优化措施

创建索引时

使用索引时

会让索引失效的情况

索引的数据结构

b+ 树

索引和存储引擎

非聚集索引和聚集索引

索引在磁盘上的存储

事务

事务的使用

事务的特性

隔离性详解

mysql实现事务的方式

撤销日志：undo log

重做日志：redo log

多版本并发控制

锁机制

事务的执行机制

支持事务的存储引擎

SQL的执行计划

EXPLAIN关键字

执行计划中的字段

案例

数据库设计

sql优化

慢sql

常见的优化策略

数据表创建

索引相关

常用的条件字段加索引

索引的最左匹配原则

避免索引失效的情况

避免使用 select *

优化分页语句

不常见的优化策略

JOIN 用小表驱动大表

用 JOIN 替代 IN 子查询

总结

网站公告

今日签到

热门文章

最新发布