1. MySQL架构和性能优化

MySQL是C/S架构的, Connector是连接器, 可供Native C API, JDBC, ODBC, PHP, Python等连接MySQL; ODBC是开发数据库互联, Open Database Connection; JDBC是主要用于JAVA语言利用较为底层的驱动连接数据库; 以上这些站在编程角度可以理解为连接数据库管理系统的驱动, 站在MySQL角度称为专用语言对应的连接器.

任何连接器连入mysql后, 因为mysql是单进程多线程的, 它会为每个用户连接, 创建一个单独的连接线程; mysql可以支持长连接和短连接, 使用mysql客户端连接数据库后, 直到使用quit命令才会退出, 这就是长连接, 如果使用mysql -e选项, 在mysql客户端向服务器端申请执行一个命令后立即退出, 也就意味着连接会立即断开. 所以mysql是支持长连接和短连接两种类型的. 用户连接到mysql后, 创建一个并分配一个连接线程, 完成之后, 能够通过这个连接线程完成接受客户端发来的请求, 为其处理请求, 构建响应报文并发给客户端; 由于是单进程模型, 就意味着必须维持一个线程池, 需要一个线程池来管理众多线程来处理客户端的并发请求, 完成并发响应. MySQL的connection pool就是实现这样的功能的.

connection pool对于mysql而言, 它所实现的功能, 包括authentication认证, 用户发来的账号密码是否正确;
thread reuse线程的重用功能, 一般当一个用户连接进来以后, 要用一个线程来响应它, 而后当用户退出这个线程有可能并非被销毁, 而是把它清理完以后, 重新收归到线程池当中的空闲线程中去, 已完成所谓的线程重用;
connection limit线程池的大小决定了连接并发数量的上限, 例如, 最多容纳100线程, 一旦到达此上限后续到达的连接请求则只能排队或拒绝连接;
check memory用来检测内存, caches实现线程缓存; 整个都属于线程池的功能, 当用户请求到达后, 通过线程池建立一个用户连接, 这个线程一直存在, 然后用户就通过这个会话, 发送对应的SQL语句到服务器端

SQL Interface

服务器收到SQL语句后, 要对语句完成执行, 首先要能理解sql语句, 需要有sql解释器或者sql接口, sql接口可理解为是整个mysql的外壳, 像shell是Linux操作系统的外壳一样道理. 用户无论通过哪种连接器发来的sql请求, 当然, 事实上通过native C API 也有发过来的不是SQL请求, 而仅仅是API中的传递参数后的调用, 不是SQL语句, 不过都统统理解为sql语句罢了.

2. 存储引擎

• 决定数据在磁盘如何存储
• 基于表来设定
• MySQL 5.5后默认InnoDB为存储引擎

查看可用的存储引擎和默认存储引擎

show engines;

2.1 MyISAM VS Innodb

1. 聚集索引(clustered index)

• MyISAM: 不支持, MyISAM是非聚集索引
• Innodb: 支持

2. 数据缓存

• MyISAM: 不支持
• Innodb: 支持, 效率高, 把数据缓存在内存中

3. 外键支持

• MyISAM: 不支持
• Innodb: 支持

4. 锁

多个用户访问数据, 可能存在同时修改数据的情况, 如何避免冲突?
通过锁机制, 用户在修改数据时, 把数据加锁, 这样别人就无法对其修改, 直到把锁解除

• MyISAM: 表及锁, 无论修改表的哪条记录, 都把整个表加锁, 其他人无法修改其余记录.
• Innodb: 行及锁, 修改哪条记录, 就给哪条记录加锁, 不影响其余记录. 支持并发性更好.

5. MVCC: 多版本并发控制, 提高并发性

Innodb的表中除了数据资源, 还有两个默认隐藏列, insert 和 delete

insert: 保存创建记录的事务编号, 类似时间
delete: 保存删除记录的事务编号, 类似时间

每次增加或者删除记录, 都会在对应的insert或者delete位置, 添加事务编号

增加一条记录时, 会在记录的insert列, 记录该事务编号, 删除一条记录会在删除位置记录事务编号

当修改数据时, 会创建一条新的记录, 赋予insert事务编号, 同时给原始记录的delete位置添加一个和insert位置相同的事务编号, 表示该原始记录已被删除

通过insert和delete事务编号, 达到并发访问不会冲突, 因为每个人访问时所看到的数据都是通过insert和delete的事务编号定义的.

并发访问时, 看到的都是在对应时间点以前插入并且没有被删除的数据.

比如: 按照下图, 在2500这个时间点也就是事务编号查询数据时, 看到的是前两行的数据. 如果是3500时查看, 看到的就是第一行和第三行的数据. 因为第二行在3500时被删除了, 而第三行是3000时插入的

MVCC

6. 存储容量

理论支持大小

• MyISAM: 256TB
• Innodb: 64TB

7. 事务

• MyISAM: 不支持
• Innodb: 支持

事务ACID特性:

A: 原子性

事务内的操作, 必须以整体执行, 要么全做, 要么都不做. 一旦事务开始执行, 要么把事务内的操作都做完, 要么都不做, 也就是说, 一旦事务执行到某一步操作出现问题, 导致后续操作无法执行, 那么就会rollback回退到事务执行前的状态.

8. 速度

不涉及高并发, 需求小, MyISAM快, Innodb慢

9. 表文件不同

MyISAM: frm(表定义), myd(存放数据), myi(存放索引)

Innodb: ibd(存放数据), frm(表定义), MySQL8.0中只有ibd文件, frm文件存在了系统表空间

早期的Innodb,把所有库的所有Innodb表的数据内容, 都放在了一个文件里, /var/lib/mysql/ibdata1. 只有表定义文件是放在单独的frm文件. 一旦该ibdata1文件被破坏, 所有的表都无法使用. 后期分开, ibd(存放数据), frm(表定义)

该设置通过变量定义

ON: 表示每个表单独一个文件
OFF: 表示所有表共用一个文件

#Server version:        5.1.73 Source distribution


mysql> show variables like 'innodb_file_per_table';   #innodb_file_per_table定义了, Innodb表的数据是统一存放在ibdata1里, 还是生成单独的ibd文件. 对于旧版本, 要修改服务端配置文件, 开启innodb_file_per_table, 这样每个表的数据都单独存在ibd文件里
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   | #
+-----------------------+-------+
1 row in set (0.00 sec)

#Server version:        5.7.28 MySQL Community Server (GPL)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

#Server version:        8.0.17 Source distribution

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

修改变量: 老版本数据库在安装时, 要把配置文件修改了, 让每个表的数据, 存在单独的一个文件

修改配置文件

[12:13:19 root@centos6-3 ~]#vim /etc/my.cnf

[mysqld]
innodb_file_per_table 

重启数据库, 并且再添加一个新的数据库后,再查看

[12:14:19 root@centos6-3 ~]#service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

[12:15:30 root@centos6-3 ~]#mysql < hellodb_innodb.sql 
[12:15:33 root@centos6-3 ~]#ll /var/lib/mysql/hellodb/
total 760
-rw-rw---- 1 mysql mysql  8636 Aug 25 12:15 classes.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 classes.ibd
-rw-rw---- 1 mysql mysql  8630 Aug 25 12:15 coc.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 coc.ibd
-rw-rw---- 1 mysql mysql  8602 Aug 25 12:15 courses.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 courses.ibd
-rw-rw---- 1 mysql mysql    61 Aug 25 12:15 db.opt
-rw-rw---- 1 mysql mysql  8658 Aug 25 12:15 scores.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 scores.ibd
-rw-rw---- 1 mysql mysql  8736 Aug 25 12:15 students.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 students.ibd
-rw-rw---- 1 mysql mysql  8656 Aug 25 12:15 teachers.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 teachers.ibd
-rw-rw---- 1 mysql mysql  8622 Aug 25 12:15 toc.frm
-rw-rw---- 1 mysql mysql 98304 Aug 25 12:15 toc.ibd

查看表使用的存储引擎

show table status like 'TABLE_NAME'\G

创建表时, 可以指定存储引擎

create table tb_name (...) ENGINE=InnoDB;

查看库里, 所有表的存储引擎

show table status from DATABASE_NAME\G

并不一定所有的表都是用Innodb, 比如mysql大部分的表就是用的MyISAM

查看表中每行数据大小, 和表总大小

show table status like 'TABLE_NAME'\G

mysql> show table status like 'students' \G
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 25
 Avg_row_length: 655  #字节为单位
    Data_length: 16384

InnoDB数据空间管理

1. 表中的行 -- 存到page(页)中, 每个页通常16K大小(16384个字节), 里面放了很多行
2. 多个页, 组成一个extent(区), 每个区包含64个页, 1M大小
3. 多个区再组成一个segment(段)
4. 多个段再组成表空间

数据库为什么不适合放大的数据, 比如照片, 视频码音频等?

数据在数据库中存储的最小单位是页, 每个页16K, 64个页组成一个区, 多个区再组成一个段, 多个段组成表空间. 数据库要存放结构化的数据, 横行纵列. 一个图片或者视频, 体积太大, 存不了多少数据

performance_schema: 使用的存储引擎大部分都是performance_schema, 特殊的存储引擎, 专门给某些表使用, 存放系统运行情况

information_schema: 存储引擎是memory, 内存, 不在磁盘存储

3. MySQL中的系统数据库

• mysql库

是mysql的核心数据库, 主要负责存储数据库的用户, 权限设置, 关键字等mysql自己需要使用的控制和管理信息, 比如存储过程, 函数都是存在mysql库里

• performance_schema数据库

MySQL5.5开始新增的数据库, 主要用于收集数据库服务器性能参数, 库里表的存储引擎均为performance_schema, 用户不能创建存储引擎为performance_schema的表

• information_schema数据库

MySQL5.0之后产生的, 一个虚拟数据库, 物理上并不存在, 其提供了访问数据库元数据的方式. 比如数据库库名或表名, 列类型, 访问权限(更加细化的访问方式)

• sys数据库

MySQL5.7以后添加, 库中所有的数据源来自于performance_scheme, 目标是把performance_scheme的复杂度降低, 让DBA能更好的阅读这个库里的内容, 让DBA更快的了解数据库运行情况

4. 服务器配置和状态

可以通过以下方法,进行MySQL的配置和查看状态

1. [mysqld]选项, 也叫服务器选项

• 可以作为mysqld的选项, mysqld是一个程序, 执行mysqld时可以添加选项, 临时生效, 如--datadir=/data/mysql
• 也可以写到配置文件比如, /etc/my.cnf, [mysqld], 永久生效
• mysqld的选项都是横线隔开, 写成下划线也可以, 不区分. 一般选项写成横线, 如果是变量写成下划线
• 查看方法

[13:23:01 root@centos8-3 ~]#mysqld --verbose --help

2. 服务器系统变量

通过show variables like 'VARIABLE_NAME'; 查看的变量都是下划线分割的, 和mysqld选项同名, 功能一样,但是有区别
一般选项写成横线, 如果是变量写成下划线

show variables like 'VARIABLE_NAME'; 查出来的是系统变量
如果知道系统变量的全名, 也可通过select @@VARIABLE_NAME;来查看

服务器系统变量又分为:

全局变量: global

会话变量: 窗口会话有效

只查看全局变量:
show global variables;
查看所有变量:包括global和session
show variables;

修改系统变量;

系统变量不一定都支持全局和会话, 因此, 修改变量之前一定要查看变量支持哪种形式

set [global] 变量名=变量值;
只支持会话的变量是无法设置成global的
支持global的变量,定义时要加global关键字, 否则默认指定的是session变量

有些系统变量是不让修改的, 可以参考变量列表的dynamic列, 标记yes的就是可以修改的变量, 标记no就是不让动态修改的系统变量
如果想要修改不能动态修改的系统变量, 需要看是不是有对应的服务器选项, 有的话可以修改配置文件或者启动时指定选项

比如: skip_networking即是服务器选项, 也是服务器系统全局变量, 但是不可以动态修改

mysql> select @@skip_networking;
+-------------------+
| @@skip_networking |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> set skip_networking=1;
ERROR 1238 (HY000): Variable 'skip_networking' is a read only variable

需要加到配置文件, 然后重启服务

修改系统全局变量:

1. set global 'VARIABLE_NAME'=VALUE;
2. 退出当前窗口重新登录, 否则有些global的变量不会生效
3. 退出后不会失效, 但是重启服务会失效.
4. 如果想要永久生效, 需要看有没有对应的服务器选项, 有的话就修改配置文件服务器选项, 然后重启服务

修改系统会话变量:

1. set 'VARIABLE_NAME'=VALUE;
2. 在当前窗口立即生效, 如果退出重新登录会失效
3. 在其他窗口不会生效,会话变量修改后退出失效

如果把不支持的选项放进配置文件, 服务器有可能起不来

比如character_set_results, 因此不要看到show variables有某个变量 就写到选项里

如何查看? 官网可以查看:https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html

服务器选项 vs 服务器系统变量

1. 并不是所有的服务器选项和服务器系统变量都是对应的.

有些服务器选项在系统变量里是没有的, 比如 --skip-grant-tables就没有服务器系统变量
反之亦然

2. 变量有全局的也有会话级别的, 而服务器选项都是全局的

范例: 修改mysql支持的最大并发连接数

如果修改了最大并发连接数, 比如8000, 但是重启服务后查询数据库系统变量发现只有594, 怎么办? 一般发生在老版本

1. 在service文件添加[Service]
[Service]
LimitNOFILE=65535 #同时打开文件个数

2. mkdir /etc/systemd/system/mariadb.service.d/
vim mkdir /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535

max_connections数值要根据服务器配置来设置, 不要一味地调大, 如果系统配置达不到最大并发请求的要求, 就会造成拥堵现象, 一般1000-2000差不多了

3. 服务器状态变量

只是一种状态, 相当于只读变量, 看到的内容是一种状态, 只是看而已, 不能修改

比如 Com_select, 记录执行select命令的次数, Com_insert, 记录执行insert命令的次数, 这些都是状态变量, 只读, 不能更改, 需要用show status查看, 不能使用select @@, 因为select @@只适用于系统变量

show global status like ''; # 会话系统变量
show status like ;  # 会话状态变量

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     |
+---------------+-------+
1 row in set (0.00 sec)

服务器变量SQL_MODE: 影响服务器工作行为

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

query_cache: 查询缓存, 提高速度, 将SQL查询语句结果放在缓存中, 下次执行同样的语句时直接调用缓存数据
mysql8.0砍掉了, 企业缓存用专业软件, redis和memcache

5. INDEX索引

5.1 索引介绍

索引: 是排序的快速查找的特殊数据结构, 定义在作为查找条件的字段上, 又称为键key, 索引通过存储引擎实现

优点:

索引可以减低服务需要扫描的数据量, 减少IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机IO转为顺序IO

缺点:

占用额外空间, 影响插入速度
后期索引的更改会造成磁盘的数据页的重新划分, 产生大量磁盘IO
尤其是初期没有建立索引的情况下, 后期添加索引, 会用大量的IO进行索引构建
此外, 变动多的列不适合建索引, 因为一旦修改, 除非是顺序插入, 就会造成索引的重新构建

5.2 索引类型

B+树, Hash, R树, Full Text
聚集索引(InnoDB), 非聚集索引(MyISAM): 基于索引和数据是否存在一张表(myi, myd,frm),(frm,ibd)
主键索引, 二级索引(辅助索引)
稠密索引, 稀疏索引: 是否索引了每一个数据项
简单索引, 组合索引
前缀索引: 取前面的若干字符做索引
覆盖索引: 从索引中即可取出要查询的数据, 性能高

5.2.1 基于不同的算法

b+tree索引

按顺序存储, 每一个叶子节点到根节点的距离是相同的; 利用b+树索引可以对范围查询进行优化
根节点一般都是在内存保存, b+tree一般两次I/O就能找到数据
b+tree叶子节点之间有双向指针, 指向上/下一个范围的数据的位置, 因此在搜索上/下一个范围的数据时, 不需要再从根节点查找
而是直接从当前叶子节点,靠叶子节点里的指针,找到数据对应位置, 对于where > < 等查询可以提高效率

hash索引

基于hash表实现, 只有精确匹配索引中的列值的查询才生效
针对某些列做了hash索引后, 会通过hash运算, 计算出这些列的值对应的hash值
只有精确匹配hash值的查找, 才会走hash索引. 索引自身只存储列对应的哈希值和数据指针, 索引结构紧凑, 查询性能好

memeory存储引擎支持显示hash索引, InnoDB和MyISAM存储引擎不支持
使用场景: 只支持等值和比较查询, =, <=>, in(), 不支持范围搜索

5.2.2 基于数据和索引的存储方式

聚集索引: 数据和索引放在一起, b+tree聚集索引, 叶子节点既有索引也有数据. InnoDB表结构就两个, frm和ibd
非聚集索引: 数据和索引不放在一起. MyISAM表结构是三个文件, frm, myi, myd, 是非聚集索引

主键索引(聚集索引)和二级索引(辅助索引)

建立了主键后, 会自动把主键列做排序, 数据会按照主键列的顺序, 在磁盘摆放

二级索引, 除了主键列, 又在别的列建立了索引, 这时, 会生成一个新的b+tree结构, 但是这个b+tree的叶子节点是不会存数据的, 因为数据已经按照主键索引做好了排序, 因此二级索引的叶子节点只存主键值和辅助索引值

比如在id列创建了主键索引, 之后在姓名列又创建了索引, 这时就会新建一个b+tree结构, 此b+tree结构的叶子节点, 会存放姓名和id的对应关系, 如果通过姓名去查询某个人的信息时, 通过姓名会在二级索引树的叶子节点拿到姓名对应的id, 然后再用id去主键索引的b+tree结构去做查询, 拿到最终数据.

执行2次索引查找, 效率低, 会出现回表

复合索引:

针对多个字段来同时创建索引

需要约定好字段顺序, 然后按照规定的字段顺序去做排序, 先用第一个字段排序, 然后用第二个字段排序

冗余索引

对A列建立了索引, 又对AB两列建立了复合索引, 这时A索引就会失效
但是, 这时基于A做查询时, 还会优先走A列索引, 因为复合索引包含了单列索引

B+tree索引的限制:

1. 能不能利用索引, 要看查询的字段, 是否做好了排序, 排好序的数据才能走索引查询

2. 一旦建立了复合索引, 那么就不能跳过前面的字段,直接利用后面的字段去做查询, 否则索引会失效

3. 不能跳过索引中的列, 比如对a,b,c三个列做了复合索引, 那么查询只能是a, ab, abc, 其余查询都不会走索引

可以使用b+tree索引的查询类型

- 全值匹配: 精确所有索引列
- 匹配最左前缀, 即只使用索引的第一列
- 匹配列前缀, 只匹配列值开头的部分
- 匹配范围值
- 精确匹配某一列并范围匹配另一列
- 只访问索引的查询

5.3 B树索引 vs B+树索引

• B树索引

图片.png

假设表中存的是学生信息, 以学号id为主键, 在id列创建索引, 那么会把表中的数据行按照id列, 排好序
对于B树来说, 每个数据页节点都会存放具体的每行数据, 以及索引指针, 通过指针, 来确定数据的位置
此时, 查询不同的数据, 每次查询的IO都是不同的, 如果数据在根节点, 那么一次IO即可, 如果在叶子节点, 那么需要三次IO
此外, 由于每个节点都即存放具体每一行数据, 也存放指针, 那么节点存放的指针数量有限, 因为每个节点就是一个数据页, 16kb大小. 因此, 如果存的数据行每行数据量越大, 留给指针的空间就越小, 需要消耗的叶子节点会变多, 造成B树结构变得更高, 查询的IO次数也会增加

• B+树

图片.png

所有的行数据都放在叶子节点, 其他节点只存指针和索引列的值的对应关系, 比如(id=1:指针), 这样每个数据页存放的数据行数增多, 树高也就变低
所有的行数据都放在叶子节点, 这样查询任何一条数据, 需要的IO次数都是相同的
大概三层树高就可以存放4000行万左右的一个表
根节点一般在MySQL启动就会加载到内存, 因此, 一般两次IO就可以查到所有的数据
每个叶子节点之间都有指针指向相邻的数据页, 直接在叶子节点即可完成双向查找

5.4 索引优化

- 独立地使用列: 尽量避免其参与运算, 独立的列指的是索引不能是表达式的一部分, 也不能是函数的参数
在where条件中, 始终将索引列单独放在比较符号的一侧, 尽量不要在列上进行运算, 因为索引会失效
- 前缀索引: 构建指定索引字段的左侧的字符数, 要通过索引选择性(不重复的索引值和数据表的记录总数的比值)来评估
尽量使用短索引, 如果可以, 应该指定一个索引长度
- 多列索引: AND操作时更适合使用多列索引, 而非为每个列单独创建索引, 对于每个列单独创建索引, 在多个条件查询时, 只会多第一个条件的列按照索引查询, 其余不会
- 选择合适的索引列顺序: 无排序和分组时, 将选择性最高的放左侧
- 只要列中含有NULL值, 就最好不要在此列设置索引, 复合索引如果有NULL值, 此列在使用时不会使用索引
- 对于经常在where子句使用的列, 最好设置索引
- 对于有多个列where或者order by子句, 应该建立复合索引
- 对于like语句, 以%或者_开头的不会使用索引, 以%结尾会使用索引
- 尽量不要使用not in 和 <>|!=, 对于not in和不等值查询, 辅助索引不会生效, 聚集索引大部分情况会生效
- 不要使用RLIKE正则表达式, 会导致索引失效
- 查询时不要用*, 尽量写字段名
- 大部分情况, 连接查询效率大于子查询
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询, 可开启查询缓存, 或者放到缓存产品
- 使用explain, profile分析查询语句
- 查看慢查询日志, 找出执行时间长的SQL语句进行优化

5.5 管理索引

索引是针对字段建立的, 一旦在某一列创建了主键, 那么自动会创建主键索引, 对主键列做排序

1. 查看索引信息

show indexes from 表名;

mysql> show indexes from students \G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY  #主键列
 Seq_in_index: 1
  Column_name: StuID  #索引列
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE  #实际是b+tree索引
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

2. 创建索引

#索引要起名字, 便于分辨, idx_name, 10表示只对name字段的前10个字符建立索引,
mysql> create index idx_name on students(name(10));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看创建的索引

*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name  #索引名称
 Seq_in_index: 1
  Column_name: Name  #索引列
    Collation: A
  Cardinality: 25
     Sub_part: 10
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.08 sec)

3. explain工具

可以通过explain来分析索引的有效性, 获取查询执行计划信息, 用来查看查询优化器如何执行查询
配合慢查询日志, 分析语句问题

语法:

explain select clause

explain输出信息说明

table: 此次执行计划设计到的表
type:  查询类型, 全表(ALL), 索引扫描(不同级别)
possible_keys: 可能会用的索引
key:      真正使用的索引
key_lens: 索引覆盖长度, 一般用于评估联合索引
rows:     此次查询扫描, 扫描的行数
Extra:  额外信息

索引的type尽可能达到range类型, 否则索引效率很低

举例:

mysql> explain select * from students where name like 'shi%';
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | range | idx_name      | idx_name | 32      | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

没用到索引的举例:

mysql> explain select * from students where name like '%u';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

最左匹配:

mysql> explain select * from students where name like 'shi%';
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | range | idx_name      | idx_name | 32      | NULL |    3 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意: 当通过索引查询出来的结果行数(rows), 过多, 这时mysql就不会走索引而是全表扫描, 认为走索引还不如全表扫描

举例: students表有25条数据, 如果查询以x开头的name就不会走索引,而是全表扫描

由于通过索引查出来的记录有6条, 而全表一共才25条, 因此mysql不会走索引

mysql> explain select * from students where name like 'x%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | idx_name      | NULL | NULL    | NULL |   25 |    24.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
#这里possible_keys显示idx_name, 但是最终没有使用索引, 这是mysql优化器去衡量的
mysql> select * from students where name like 'x%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     7 | Xi Ren      |  19 | F      |       3 |      NULL |
|    16 | Xu Zhu      |  21 | M      |       1 |      NULL |
|    19 | Xue Baochai |  18 | F      |       6 |      NULL |
|    22 | Xiao Qiao   |  20 | F      |       1 |      NULL |
|    24 | Xu Xian     |  27 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)

4. 创建复合索引

mysql> create index idx_name_age on students(name,age);

#复合索引会显示两条
*************************** 3. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: Age
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
4 rows in set (0.00 sec)

5. 删除索引

alter table 表名 drop index INDEX_NAME;

6. 优化表空间

optimize table 表名;
1. 整理磁盘碎片空间
2. 重新构建索引信息, 不过一般只针对大量的数据增删改才有效

6. 并发控制

6.1 锁机制

锁:

读锁: 共享锁, 也称为s锁, 多个事务可以获取同一行数据的共享锁,  多个事务的读互不阻塞. 当多个事务同时获得了一行数据的共享锁, 那么这些事务都不可以对这行数据进行修改, 因为修改就是上排他锁, 而此时其他事务是有共享锁的
写锁: 独占锁, 排他锁, 也称为x锁, 写锁, 会阻塞其他事务, 不包含当前事务的读和写
  给某一行加了读锁, 其他连接要想再加写锁, 或者反过来, 那么必须等读锁或者写锁释放才行. 写锁和写锁, 写锁和读锁不能共存. 一旦加了写锁, 只有当前连接可以读和写, 其他事务无法读写

读锁和读锁是兼容的, 写锁和其他锁是不兼容的
比如: 事务T1, 获取了一个r1的读锁, 另一个事务t2可以立即获得r1的读锁, 此时t1和t2共同获得r1的读锁
此种情况称为锁兼容
但是另一个事务t2此时如果想获得r1的写锁, 则必须等待t1对r1读锁的释放, 此情况也称为锁冲突

DML语句都会给受影响的行加上写锁, 而DQL不会加任何锁
因此, 读锁可以共享, 而写锁是互斥的, 因此, 会阻塞其他事务的写操作, 只有本事务可以写, 但不影响其他事务的读操作, 因为读是不加锁的

锁利度:

表级锁: MyISAM
行级锁: InnoDB

实现:

存储引擎: 自行实现其锁策略和锁粒度
服务器级: 实现了锁, 表级锁, 用户可显示请求

分类:

隐式锁: 由存储引擎自动施加锁
显示锁: 用户手动加锁

锁策略: 在锁粒度及数据安全性寻求的平衡机制

全局读锁:

会对所有库所有表加读锁, 一般在备份维护时可以添加

对所有库的所有表有效
所有连接可读, 不可写
加全局读锁期间, 用户无法修改数据, 比如下订单, 会影响用户体验

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
...

mysql> update students set age=99 where stuid=25;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> unlock tables

mysql> update students set age=99 where stuid=25;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students where stuid=25;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng |  99 | M      |    NULL |      NULL |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

6.2 事务

事务: 一组原子性的SQL语句, 或一个独立的工作单元
事务日志: 记录事务信息, 实现undo, redo等故障恢复

6.2.1 事务特性

A: atomicity原子性: 整个事务中的所有操作要么全部执行, 要么全部失败后回滚
C: consistency一致性: 数据库总是从一个一致性状态转换为另一个一致性状态
I: isolation隔离性: 一个事务所做出的操作在提交之前, 是不能为其他事务所看见的, 隔离有多种隔离级别, 实现并发
D: Durability持久性: 一旦事务提交, 其所做的修改会永久保存与数据库中

脏数据: 一次事务执行过程中, 产生的数据被访问到就是脏数据, 因为事务还没执行完

6.2.2 管理事务

MySQL中, 一条DML语句, update, delete, insert, 就是一个事务, 而且默认就会commit, 无需手动提交
可通过修改系统变量@@autocommit=0, 来禁止自动提交, 或者修改mysqld选项, 写到配置文件
事务的回滚: 事务是针对DML语句的, 因此, DDL语句是不能回滚的, 比如: create, drop, alter, truncate等
事务执行的速度快, 也能确保数据一致性

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

自动提交默认为开启, 建议改成显示请求和提交事务, 而不要使用自动提交功能, 而是使用commit提交, rollback回滚

• 显示启动事务

begin
begin work
start transaction

• 结束事务

commit
rollback

注意: 只有事务型存储引擎中的DML语句才支持此类操作. 比如InnoDB

• 查看事务

# 查看当前正在执行的事务
select * from information_schema.INNODB_TRX;
# 查看当前锁定的事务
select * from information_schema.INNODB_LOCKS;
# 查看当前等待锁释放的事务
select * from information_schema.INNODB_LOCK_WAITS;

案例: 找到未完成的导致阻塞的事务

# 在第一个会话开启事务, 执行update语句, 但不提交
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update students set age = 20 where stuid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 在第二个会话执行update语句, 修改相同的stuid=1的行
# 此时, 执行语句会卡住, 因为会话1正在修改相同的行, 而update会对修改的行加写锁, 其他连接是不可以修改的. 并且过了一段时间, 该事物就会自动退出
mysql> update students set age=30 where stuid=1;

# 在第三个会话查看事务信息
mysql> show engine innodb status\G

图片.png

# 在第三个会话, 查看锁信息
mysql> select * from information_schema.innodb_locks;

图片.png

# 在第三个会话, 查看锁等待

mysql> select * from information_schema.innodb_lock_waits;;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 205343(被阻塞的事务id)           | 205343:37:3:2     | 205339(锁住了行的事务id)          | 205339:37:3:2    |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

# 第三个会话, 查看锁住了行的事务id, 对应的线程信息
mysql> select * from information_schema.innodb_trx\G

图片.png 图片.png 图片.png

可以看出, 线程id=6, 产生了阻塞,  可以根据user:host, 找到对应的操作人员或者应用程序, 进行排查
如果是人为误操作, 可以直接kill 6, 把线程杀死, 这样别的事务就会立即执行

mysql> kill 6; # 需要在mysql内执行kill, 而不是退回shell
Query OK, 0 rows affected (0.00 sec)

6.2.3 事务的隔离级别

MySQL支持四种隔离级别, 事务隔离级别从上向下更加严格
隔离级别简单理解指的是某个事务在执行过程中, 根据其工作在不同的隔离级别下, 在不同时间点查看同一个数据的所能看到的状态

图片.png

• 读未提交

两个事务在执行过程中, 如果事务A还没有结束, 该事务对数据进行了修改产生了脏数据, 事务B如果工作在读未提交的情况下, 查看事务A修改的数据, 则会看到脏数据. 这种隔离级别最差

图片.png

• 读提交

可读取到提交数据, 但未提交数据不可读, 产生不可重复读, 即可读取到多个提交数据, 导致每次读取数据不一致
事务提交后, 才能查看, 但是这时会产生不可重复读的问题

图片.png

举例:

事务A和事务B同时执行, A执行到一个点时, 把事务t1提交, 之后在第二个时间点执行了第二个事务t2

事务B的执行比较久, 在执行事务的时候不断地查看某个表比如test1, 这时如果t1里改了test1表, t2改了test2表, 这时事务B在时间点2之前, 去查看test1表, 如果B工作在读未提交的情况下, 那么B能看到test1表事务t1更改的过程, 如果B工作在读提交模式, 那么B在时间点2前去查看test1表, 那么是看不到test1更新的脏数据的, 而是只能看到t1事务执行前的状态, 也就是时间点1的状态. 如果B在时间点2和时间点3之间查看了test1表, 那么这时看到的是时间点2的状态

这时事务B本身是个事务, 在不同的时间点看到的test1表的数据是不同的, 这就是不可重复读, 事务B本身没有修改test1表的数据, 那么在不同时间点看到的数据应该是一致的

读提交模式下不适合备份, 因为整个备份过程一旦数据被修改了, 那么不同时间点备份的数据是不一致的

• 可重复读

多次读取数据都一致, 产生幻读, 即读取过程中, 即使有其他提交的事务修改了数据, 仍只能读取到本事务开启时对应的数据的状态. MySQL默认设置为可重复读

图片.png

事务A和事务B同时执行, 事务A在时间点2提交了事务t1, 在时间点3提交了事务t2

事务B一直在执行

如果事务B工作在可重复读的模式下, 那么事务B无论在哪个时间点读test1表的数据, 看到的都是test1表在事务B开始那个时间点的状态, 即使事务A一直在修改test1, 事务B看到的都是同一个状态, 看到的数据一致

可重复读的好处就是, 只要事务自己本身不去做修改, 那么看到的数据无论在哪个时间点都是一致的, 适合备份

但是问题是, 别的事务已经对数据做了修改, 但是自己看到的还是未修改的数据, 意味着看到的数据是历史数据, 这就是幻读

MySQL的默认隔离级别是可重复读

转载幻读补充: 面试官：MySQL是怎么解决幻读问题的？ - 知乎 (zhihu.com)

• 序列化(串行化)

隔离级别最高, 不管是读事务还是写事务, 都会加锁, 其他人无法操作, 并发性能差

对于未提交的读事务, 会加读锁, 所有人只能读, 不能写
对应未提交的写事务, 会加写锁, 其他事务的读写都不可执行

可重复读, 即照顾了并发, 也满足了隔离, 不会读物脏数据, 不会加锁, 支持并发

6.2.4 MVCC和事务的隔离级别

MVCC多版本并发控制机制, 只在可重复读, 和读提交模式下工作, 其他两个隔离级别和MVCC都不兼容, 因为读未提交总是读取最新的数据, 而MVCC是读当前时间点前没有被delete的数据, 而不是符合当前事务版本的数据信息, 而序列化会对所有的读取的行都加锁, 并发差

6.2.5 指定事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

服务器变量tx_isolation指定, 默认为repeatable-read, 可以在global和session级别进行设置

持久保存事务的隔离界别, 要修改配置文件, 修改服务器选项, transaction-isolation

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

6.2.6 死锁

两个或多个事务, 在同一资源相互占用, 并锁定对方占用的资源的状态
发生死锁时mysql会自动关闭一个事务

案例: 事务A和B同时执行, 事务A先修改test1, 事务B先修改test2, 此时test1和test2都会被加上写锁
之后, 事务A要修改test2, 而事务B要修改test1, 因为两个事务都没有提交, 因此会互相阻塞对方
此时, MySQL会自动按照内部逻辑, 关闭一个事务

图片.png