1. 为什么选择 MySQL ?


要说为什么,我们可以从以下几点进行分析:

  • 开源免费,方便拓展。
  • 轻量,相比于 Oracle 安装起来颇为简单。
  • 社区活跃,碰到的问题总是能找到合适的解决方案。
  • ......

之前很长一段时间都仅仅把数据库当做一款工具在使用,而没有认真地去了解。近来在看《高性能MySQL》和一些有关 MySQL 的文章、帖子,也使得我对 MySQL 有一个重新的认识。

SQL 能力检验,通过它检验 SQL 基本功。

最后在这里推荐几篇写得比较好的文章:

2. 存储引擎


2.1 查看 MySQL 存储引擎

通过查看官方文档,或者使用命令查看 MySQL 支持的存储引擎。

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

从输出结果上看 MySQL 当前默认的存储引擎是 InnoDB,同时还支持如 MyISAM 等其他的存储引擎。同时在 MySQL 5.7 版本中,只有 InnoDB 支持事务(这一点在选择数据库引擎上要着重考虑)。

2.2 数据库表的存储引擎

我们可以在创建表时为数据库表指定存储引擎。

drop table if exists tb_test1;
create table tb_test1(
    id int auto_increment,
    name varchar(20) not null,
    primary key (id)
) ENGINE = InnoDB;

drop table if exists tb_test2;
create table tb_test2(
    id int auto_increment,
    name varchar(20) not null,
    primary key (id)
) ENGINE = MyISAM;

查看数据库表的存储引擎,表 tb_test1 存储引擎为 InnoDB。

mysql> show table status like 'tb_test1';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| tb_test1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-04-15 10:54:27 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

mysql> mysql> show table status like 'tb_test2';

+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| tb_test2 | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         1024 |         0 |              1 | 2020-04-15 10:54:28 | 2020-04-15 10:54:28 | NULL       | utf8mb4_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+

可以看出 tb_test1 存储引擎为 InnoDB, tb_test2 存储引擎为 MyISAM。

2.3 MyISAM 和 InnoDB 的区别

上面我们只对 MyISAM 和 InnoDB 两种存储引擎进行了简单的了解。(其他的别问我,我目前也不会😂)。

MyISAM 在 5.5 版本之前是 MySQL 的默认存储引擎,5.5 版本后引入了 InnoDB 作为它的默认存储引擎。

那么这两种存储引擎有什么区别呢?(仅作参考)

  • 支持锁的类型:MyISAM 仅支持表级锁(table-level locking)。InnoDB 同时支持表级锁以及行级锁(row-level locking),且默认为行级锁。
  • 是否支持外键:MyISAM 不支持外键(foreign key),而 InnoDB 支持。
  • 是否支持事务:MyISAM 强调性能,查询语句的执行速度比 InnoDB 更快,但是不提供事务支持。InnoDB 提供事务支持且比较完善,通过设置可达到最高隔离级别(SERIALIZABLE),下面会讲到。
  • 是否支持安全恢复:MyISAM 不支持, InnoDB 支持。
  • 是否支持 MVVC :MyISAM 不支持, 仅 InnoDB 支持。

综上,在一般情况下,我们使用 InnoDB 作为存储引擎是完全没有问题的,但是在某些情况下可能对安全性、回滚等功能都不太在乎,这时选择 MyISAM 也是没有问题的。

3. 字符集校对规则


3.1 字符集

查看 MySQL 系统变量使用的字符集

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

这些系统变量代表的意思大家望文生义,这里就不对它们的功能做解释了。

一般情况下,我们在创建数据库的时候指定数据库的编码字符集为 UTF-8 即可。

mysql> create database test character set utf8;

3.2 字符集的继承性

所谓继承性,就是说如果未指定字符集就默认使用其上层指定的字符集。

比如:表中的字段如果没有指定字符集,则其默认使用表的字符集,如果表也没有指定字符集,就使用表所在的数据库所使用的的字符集。那么如果数据库也没设置字符集咋办呢?那就使用 character_set_server 系统变量所指定的字符集。

MySQL 规范地使用会在我们使用过程中减少很多不必要的麻烦,所以建议我们在创建数据库以及创建数据库表的时候都要指定所使用的字符集。

create database test character set utf8;

create table tb_test3(
    id int auto_increment,
    name varchar(20) not null,
    primary key (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

4. 事务


谈起事务,我们的第一反应就是 ACID 原则,这里我们一起回顾一下:

  • 原子性:Atomicity 指事务中的最小执行单位,要么整个事务全部执行成功,要么全部失败。
  • 一致性:Consistency 指执行事务的前后数据保持一致,即多个事务对同一个数据的读取结果是一样的。
  • 隔离性:Isolation 指多个事务之间互不干扰。
  • 持久性:Durability 指一个事务被提交后,该事务所做的改变是持久的,即使发生一些不可控的外力因素也不应该对其造成影响。

那么在数据库中并发事务会带来哪些问题呢?

数据库的并发事务可能会引发以下问题:

  • 脏读:当一个事务正在访问数据库中的数据且对其进行修改,但是这个修改操作还未提交至数据库,这时另一个事务也来访问这条数据,并使用这条数据做了一些逻辑操作。由于前一个事务的修改还未提交至数据库,所有后一个事务获取到的数据是一条 “脏数据” ,这种情况我们称之为 脏读。
  • 不可重复读:一个事务多次访问同一数据,在这个事务还未结束时另一个事务也访问了该数据且对其进行了修改,这会造成前一个事务多次访问该数据得到了不同的结果,这种情况我们称之为 不可重复读。
  • 幻读:与 不可重复读 类似,在一个事务多次访问同一数据时,另外一个事务也访问了该数据且对数据进行了增加或者是删除操作,这会造成前一个事务访问该数据时多了一些不存在的记录或者少了一些已有的记录,就犹如出现 “幻觉” 一般,这种情况我们称之为 幻读。
  • 第一类丢失修改:一个事务访问数据且对其进行了修改,而另一个事务也访问了该数据并撤销了事务操作,这个时候前一个事务的修改已经提交,而后一个事务的撤销会导致前一个事务提交的修改被覆盖从而导致丢失修改,我们称之为 第一类丢失修改。
  • 第二类丢失修改:一个事务访问数据且对其进行了修改,而另一个事务也访问了这条数据并进行了修改,这会导致前一个事务的修改结果丢失,这种情况我们称之为 第二类丢失修改。

数据库是怎么处理这些并发事务所带来的的问题呢?

说到这个,我们就要谈一谈 SQL 标准定义的四个隔离级别了:

  • READ-UNCOMMITED:顾名思义,读未提交 是 SQL 标准中最低的隔离级别,允许读取没有提交至数据库的数据。这就可能导致脏读、幻读、不可重复读以及第二类丢失修改问题。
  • READ-COMMITED:读已提交,允许读取已经提交至数据库的数据。可以避免脏读,但还是可能会发生不可重复读、幻读以及第二类丢失修改的问题。
  • REPEATABLE-READ:可重复读,多次读取的结果是一致的,除非数据被事务本身修改了。可以避免脏读、不可重复读和丢失修改,但是幻读还是有可能会发生。
  • SERIALIZABLE:可串行化,SQL 标准中最高的隔离级别,所有事务串行化依次执行,这样事务之间就完全不会互相干扰。不会出现事务所引发的问题。
隔离级别脏读不可重复读幻读第一类丢失修改第二类丢失修改
不设置任何隔离级别YYYYY
READ-UNCOMMITEDYYYNY
READ-COMMITEDNYYNY
REPEATABLE-READNNYNN
SERIALIZABLENNNNN

MySQL 默认支持的隔离级别是什么呢?

我们通过 select @@global.tx_isolation 进行查看

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

可以看到 MySQL 默认的隔离级别是 可重复读。这里我顺便提一句,其他的如 Oracle、SQLServer 等一些主流数据库默认的隔离级别为 读已提交。

与 SQL 标准不同的是 MySQL 中的 REPEATABLE-READ 通过使用 next-key lock 算法锁定了包含记录本身在内的部分数据,从而也能避免 幻读 的发生。也就是说,尽管 MySQL 默认的隔离级别是 REPEATABLE-READ,但其实际上已经达到了 SERIALIZABLE 隔离级别。

5. SQL 优化


如果说前面的可以不了解,那 SQL 优化数据库表优化 是我们必须要掌握的技能。

SQL 优化这里就不细说了,推荐阅读 后端程序员必备:书写高质量SQL的30条建议

索引

我们要善用索引,但不要滥用索引。

说一下学习过程中目前我对使用索引的理解:

  • 同一张表中索引列不要超过 5 个。
  • 注意,创建联合索引时相当于创建了多个索引,如 index_a_b_c (a, b, c) 相当于创建了 index_a (a), index_a_b (a, b)以及 index_a_b_c (a, b, c) 三个索引。
  • 使用联合索引时注意 SQL 语句一定要满足 最左匹配原则 ,否则 SQL 语句不走索引。
  • 删除冗余索引,如创建了联合索引,就删除掉之前创建的且包含在联合索引之内的索引。
  • 不要在值重复较多的列上创建索引,如 性别 。
  • 不要在 where 条件中对索引列进行运算操作,否则 SQL 语句不走索引。
  • 不要在 where 条件中对索引列进行函数操作,否则 SQL 语句不走索引。
  • 不要在 where 条件中对索引列使用 !=<> 操作符。
  • 养成使用 explain 的习惯,在执行 SQL 语句前先看看它是否用到了索引。

下面对部分情况进行实例演示,加深理解!

错误示范:在重复值较多的列上创建索引

在 sex 列上创建索引,我们看一下效果

explain select * from tb_test3 where sex = '1';

image.png

这条查询语句没有走索引,而是全表扫描。

为什么会这样呢?

首先要了解到 非主键索引 中存放的不是这个字段的数据,而是 主键字段的值。所以数据库在使用非主键索引的时候,会先查询到主键索引的值,然后再走主键索引。也就是说我们只是通过非主键索引进行了一次筛选,最后还是会走主键索引,是执行了两次操作的

了解了这个之后,我们再来看看 MySQL 走索引的机制。MySQL 通过对索引列采取抽样的方式计算出一个名为 索引基数 的东西,这个基数的值越大说明这个字段中拥有相同数据的行数越少,从而就更适合使用索引来提升效率,反之它会认为走索引比直接走主键(也就是全表扫描)更慢从而进行全表扫描。这就是为什么 不要在重复值较多的列上创建索引 的原因。

错误示范:在索引列上进行运算操作

在 num 列上创建索引,我们在 where 条件中对其进行运算操作

explain select * from tb_test3 where num + 10 = 100;

image.png

修改一下 SQL 语句

explain select * from tb_test3 where num = 100 - 10;

image.png

这个没什么好说的,虽然看起来没什么区别,但是可能就是这一点点细小的差别而导致效率上的千差万别,所以一定要注意了。

错误示范:在索引列上使用函数

在 num 列上创建索引,在 where 条件中对其使用函数

explain select * from tb_test3 where char(num) = '100';

image.png

这个与上面的同理。

错误示范:索引列上使用 != 或者 <>

还是 num 列,直接看效果

explain select * from tb_test3 where num <> 100;

image.png

这个时候可以考虑自身的业务,如果不走索引会引发严重效率问题,建议将 SQL 语句进行拆分。


关于作者:NekoChips
本文地址:https://chenyangjie.com.cn/articles/2020/04/15/1586926804823.html
版权声明:本篇所有文章仅用于学习和技术交流,本作品采用 BY-NC-SA 4.0 许可协议,如需转载请注明出处!
许可协议:知识共享许可协议