MySQL 架构和性能优化1
MySQL是C/S 架构的，connectors是连接器；可供Native C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol等连接mysql；ODBC叫开放数据库（系统）互联，open database connection；JDBC是主要用于java语言利用较为底层的驱动连接数据库；以上这些，站在编程角度可以理解为连入数据库管理系统的驱动，站在mysql角度称作专用语言对应的链接器.任何链接器连入mysql以后，mysql是单进程多线程模型的，因此，每个用户连接，都会创建一个单独的连接线程；其实mysql连接也有长短连接两种方式，使用mysql客户端连入数据库后，直到使用quit命令才退出，可认为是长连接；使用mysql中的-e选项，在mysql客户端向服务器端申请运行一个命令后则立即退出，也就意味着连接会立即断开；所以，mysql也支持长短连接类似于两种类型；所以，用户连入mysql后，创建一个连接线程，完成之后，能够通过这个连接线程完成接收客户端发来的请求，为其处理请求，构建响应报文并发给客户端；由于是单进程模型，就意味着必须要维持一个线程池，跟之前介绍过的varnish很接近，需要一线程池来管理这众多线程是如何对众多客户端的并发请求，完成并发响应的，组件connection pool就是实现这样功能；connection pool对于mysql而言，它所实现的功能，包括authentication认证，用户发来的账号密码是否正确要完成认证功能；thread reuse线程重用功能，一般当一个用户连接进来以后要用一个线程来响应它，而后当用户退出这个线程有可能并非被销毁，而是把它清理完以后，重新收归到线程池当中的空闲线程中去，以完成所谓的线程重用；connection limit 线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦到达此上限后续到达的连接请求则只能排队或拒绝连接；check memory用来检测内存，caches实现线程缓存；整个都属于线程池的功能.当用户请求之后，通过线程池建立一个用户连接，这个线程一直存在，然后用户就通过这个会话，发送对应的SQL语句到服务器端.
服务器收到SQL语句后，要对语句完成执行，首先要能理解sql语句需要有sql解释器或叫sql接口sqlinterface就可理解为是整个mysql的外壳，就像shell是linux操作系统的外壳一样；用户无论通过哪种链接器发来的基本的SQL请求，当然，事实上通过native C API也有发过来的不是SQL 请求，而仅仅是对API中的传递参数后的调用；不是SQL语句不过都统统理解为sql语句罢了；对SQL而言分为DDL 和DML两种类型，但是无论哪种类型，提交以后必须交给内核，让内核来运行，在这之前必须要告诉内核哪个是命令，哪个是选项，哪些是参数，是否存在语法错误等等；因此，这个整个SQL 接口就是一个完完整整的sql命令的解释器，并且这个sql接口还有提供完整的sql接口应该具备的功能，比如支持所谓过程式编程，支持代码块的实现像存储过程、存储函数，触发器、必要时还要实现部署一个关系型数据库应该具备的基本组件例如视图等等，其实都在sql interface这个接口实现的；SQL接口做完词法分析、句法分析后，要分析语句如何执行让parser解析器或分析器实现
parser是专门的分析器，这个分析器并不是分析语法问题的，语法问题在sql接口时就能发现是否有错误了，一个语句没有问题，就要做执行分析，所谓叫查询翻译，把一个查询语句给它转换成对应的能够在本地执行的特定操作；比如说看上去是语句而背后可能是执行的一段二进制指令，这个时候就完成对应的指令，还要根据用户请求的对象，比如某一字段查询内容是否有对应数据的访问权限，或叫对象访问权限；在数据库中库、表、字段、字段中的数据有时都称为object，叫一个数据库的对象，用户认证的通过，并不意味着就能一定能访问数据库上的所有数据，所以说，mysql的认证大概分为两过程都要完成，第一是连入时需要认证账号密码是否正确这是authentication，然后，验证成功后用户发来sql语句还要验证用户是否有权限获取它期望请求获取的数据；这个称为object privilege，这一切都是由parser分析器进行的
分析器分析完成之后，可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的，可能有多条路径都可实现，就像文件系统一样可以使用相对路径也可使用绝对路径；它有多种方式，在多种路径当中一定有一个是最优的，类似路由选择，因此，优化器就要去衡量多个访问路径中哪一个代价或开销是最小的，这个开销的计算要依赖于索引等各种内部组件来进行评估；而且这个评估的只是近似值，同时还要考虑到当前mysql内部在实现资源访问时统计数据，比如，根据判断认为是1号路径的开销最小的，但是众多统计数据表明发往1号路径的访问的资源开销并不小，并且比3号路径大的多，因此，可能会依据3号路径访问；这就是所谓的优化器它负责检查多条路径，每条路径的开销，然后评估开销，这个评估根据内部的静态数据，索引，根域根据动态生成的统计数据来判定每条路径的开销大小，因此这里还有statics；一旦优化完成之后，还要生成统计数据，这就是优化器的作用；如果没有优化器mysql执行语句是最慢的，其实优化还包括一种功能，一旦选择完一条路径后，例如用户给的这个命令
执行起来，大概需要100个开销，如果通过改写语句能够达到同样目的可能只需要30个开销；于是，优化器还要试图改写sql语句；所以优化本身还包括查询语句的改写；一旦优化完成，接下来就交给存储引擎完成.
mysql是插件式存储引擎，它就能够替换使用选择多种不同的引擎，MyISAM是MySQL 经典的存储引擎之一，InnoDB是由Innobase Oy公司所开发，2006年五月由甲骨文公司并购提供给MySQL的，NDB主要用于MySQL Cluster 分布式集群环境，archive做归档的等等，还有许多第三方开发的存储引擎；存储引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换，数据库中的行数据都是存储在磁盘块上的，因此存储引擎要把数据库数据映射为磁盘块，并把磁盘块加载至内存中；进程实现数据处理时，是不可能直接访问磁盘上的数据的，因为它没有权限，只有让内核来把它所访问的数据加载至内存中以后，进程在内存中完成修改，由内核再负责把数据存回磁盘；对于文件系统而言，数据的存储都是以磁盘块方式存储的，但是，mysql在实现数据组织时，不完全依赖于磁盘，而是把磁盘块再次组织成更大一级的逻辑单位，类似于lvm中的PE或LE的形式；其实，MySQL的存储引擎在实现数据管理时，也是在文件系统之上布设文件格式，对于文件而言在逻辑层上还会再次组织成一个逻辑单位，这个逻辑单位称为mysql的数据块datablock 一般为16k ，对于关系型数据库，数据是按行存储的；一般一行数据都是存储在一起的，因此，MySQL 在内部有一个datablock，在datablock可能存储一行数据，也可能存放了n行数据；将来在查询加载一行数据时，内核会把整个一个数据数据块加载至内存中，而mysql存储引擎，就从中挑出来某一行返回给查询者，是这样实现的；所以整个存储是以datablock在底层为其最终级别的.
事实上，整个存取过程，尤其是访问比较热点的数据，也不可能每一次当用户访问时或当某SQL语句用到时再临时从磁盘加载到内存中，因此，为了能够加上整个性能，mysql的有些存储引擎可以实现，把频繁访问到的热点数据，统统装入内存，用户访问、修改时直接在内存中操作，只不过周期性的写入磁盘上而已，比如像InnoDB，所以caches和buffers组件就是实现此功能的；MySQL为了执行加速，因为它会不断访问数据，而随计算机来说io是最慢的一环，尤其是磁盘io，所以为了加速都载入内存中管理；这就需要MySQL 维护cache和buffer缓存或缓冲；这是由MySQL 服务器自己维护的；有很多存储引擎自己也有cache和buffer
一个数据库提供了3种视图，物理视图就是看到的对应的文件系统存储为一个个的文件，MySQL的数据文件类型，常见的有redo log重做日志，undo log撤销日志，data是真正的数据文件，index是索引文件，binary log是二进制日志文件，error log错误日志，query log查询日志，slow query log慢查询日志，在复制架构中还存在中继日志文件，跟二进制属于同种格式；这是mysql数据文件类型，也就是物理视图；逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件，给它映射为一个个关系型数据库应该具备组成部分，比如表，一张表在底层是一个数据文件而已，里面组织的就是datablock，最终映射为磁盘上文件系统的block，然后再次映射为本地扇区的存储，但是整个mysql需要把他们映射成一个二维关系表的形式，需要依赖sql接口以及存储引擎共同实现；所以，把底层数据文件映射成关系型数据库的组件就是逻辑视图；DBA 就是关注内部组件是如何运作的，并且定义、配置其运作模式，而链接器都是终端用户通过链接器的模式进入数据库来访问数据；数据集可能非常大，每一类用户可能只有一部分数据的访问权限，这个时候，最终的终端用户所能访问到的数据集合称作用户视图；
为了保证MySQL运作还提供了管理和服务工具，例如:备份恢复工具，安全工具，复制工具，集群服务，管理、配置、迁移、元数据等工具
1.1 存储引擎
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力,此种技术称为存储引擎,MySQL 支持多种存储引擎其中目前应用最广泛的是InnoDB和MyISAM两种。
官方参考资料：
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.html
1.1.1 MyISAM 存储引擎
MyISAM 引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5 前默认的数据库引擎
MyISAM 存储引擎适用场景 - 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
MyISAM 引擎文件
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
1.1.2 InnoDB 引擎
InnoDB引擎特点
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
InnoDB数据库文件
- 所有InnoDB表的数据和索引放置于同一个表空间中
数据文件：ibdata1, ibdata2,存放在datadir定义的目录下
表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下
- 每个表单独使用一个表空间存储表的数据和索引
两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm
启用：innodb_file_per_table=ON (MariaDB 5.5以后版是默认值)
1.1.3 管理存储引擎
查看mysql支持的存储引擎
show engines;
查看当前默认的存储引擎
show variables like '%storage_engine%';
设置默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB
查看库中所有表使用的存储引擎
show table status from db_name;
查看库中指定表的存储引擎
show table status like 'tb_name';
show create table tb_name;
设置表的存储引擎：
CREATE TABLE tb_name(... ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;
1.2 MySQL 中的系统数据库
- mysql 数据库
是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息 - information_schema 数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式） - performance_schema 数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表 - sys 数据库
MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况
1.3 服务器配置和状态
可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态
官方帮助：
https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-andstatus-variables/
注意：
- 其中有些参数支持运行时修改，会立即生效
- 有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
- 有些参数作用域是全局的，为所有会话设置
- 有些可以为每个用户提供单独（会话）的设置
1.3.1 服务器选项
注意: 服务器选项用横线,不用下划线
获取mysqld的可用选项列表：
#查看mysqld可用选项列表和及当前值
mysqld --verbose --help
#获取mysqld当前启动选项
mysqld --print-defaults
设置服务器选项方法：
- 在命令行中设置
shell> /usr/bin/mysqld_safe --skip-name-resolve=1
shell> /usr/libexec/mysqld --basedir=/usr
2.在配置文件my.cnf中设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables
范例: skip-grant-tables是服务器选项,但不是系统变量
[root@centos8 ~]#mysqladmin variables |grep skip_grant_tables
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show variables like 'skip_grant_tables';
Empty set (0.001 sec)
1.3.2 服务器系统变量
服务器系统变量：可以分全局和会话两种
注意: 系统变量用下划线,不用横线
获取系统变量
SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)
#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;
#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables
修改服务器变量的值：
help SET
修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;
修改会话变量：
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;
范例: character_set_results是系统变量并非服务器选项
mysql> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)
mysql> set character_set_results="utf8mb4";
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)
[root@rocky8 ~]#vim /etc/my.cnf
[mysqld]
character_set_results=utf8mb4
[root@rocky8 ~]#systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.
范例：修改mysql的最大并发连接数
#默认值为151
[root@centos8 ~]#mysqladmin variables |grep 'max_connections'
| max_connections | 151
[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.001 sec)
MariaDB [hellodb]> set global max_connections=2000;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 2000 |
+-----------------+-------+
1 row in set (0.001 sec)
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
max_connections = 8000
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 594 |
+-------------------+
1 row in set (0.000 sec)
#方法1
[root@centos8 ~]#vim /usr/lib/systemd/system/mariadb.service
[Service]
#加下面一行
LimitNOFILE=65535
#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@node3 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535
[root@centos8 ~]#systemctl daemon-reload
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p -e "select @@max_connections"
Enter password:
+-------------------+
| @@max_connections |
+-------------------+
| 8000 |
+-------------------+
1.3.3 服务器状态变量
服务器状态变量：分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改
SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;
范例：
mysql> show status like "innodb_page_size";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)
mysql> SHOW GLOBAL STATUS like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 1 |
+---------------+-------+
1 row in set (0.00 sec)
1.3.4 服务器变量 SQL_MODE
SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
常见MODE:
- NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
- NO_ZERO_DATE：在严格模式，不允许使用'0000-00-00'的时间
- ONLY_FULL_GROUP_BY： 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
- NO_BACKSLASH_ESCAPES： 反斜杠""作为普通字符而非转义字符
- PIPES_AS_CONCAT： 将"||"视为连接操作符而非"或"运算符
范例：CentOS 8 修改SQL_MODE变量实现分组语句控制
MariaDB [hellodb]> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------
---------------------------+
| Variable_name | Value
|
+---------------+----------------------------------------------------------------
---------------------------+
| sql_mode |
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUB
STITUTION |
+---------------+----------------------------------------------------------------
---------------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
8 rows in set (0.000 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
+-------+---------+----------+
| stuid | classid | count(*) |
+-------+---------+----------+
| 24 | NULL | 2 |
| 2 | 1 | 4 |
| 1 | 2 | 3 |
| 5 | 3 | 4 |
| 4 | 4 | 4 |
| 6 | 5 | 1 |
| 9 | 6 | 4 |
| 8 | 7 | 3 |
+-------+---------+----------+
8 rows in set (0.001 sec)
#修改SQL_MODE
MariaDB [hellodb]> set sql_mode="ONLY_FULL_GROUP_BY";
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.001 sec)
MariaDB [hellodb]> select classid,count(*) from students group by classid;
+---------+----------+
| classid | count(*) |
+---------+----------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+----------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select stuid,classid,count(*) from students group by classid;
ERROR 1055 (42000): 'hellodb.students.StuID' isn't in GROUP BY
范例：CentOS 7 修改SQL_MODE变量
MariaDB [hellodb]> create table test (id int ,name varchar(3));
Query OK, 0 rows affected (0.04 sec)
MariaDB [hellodb]> insert test values(1,'abcde');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [hellodb]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | abc |
+------+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show variables like 'SQL_MODE';
+---------------+----------------------------------------------------------------
---------------------------------------------------------------------------------
-----+
| Variable_name | Value
|
+---------------+----------------------------------------------------------------
---------------------------------------------------------------------------------
-----+
| sql_mode |
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV
ISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------
---------------------------------------------------------------------------------
-----+
1 row in set (0.00 sec)
MariaDB [hellodb]> insert test values(2,'magedu');
ERROR 1406 (22001): Data too long for column 'name' at row 1
1.4 INDEX 索引
1.4.1 索引介绍
索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现
优点：
- 索引可以降低服务需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序 I/O
缺点： - 占用额外空间，影响插入速度
索引类型： - B+ TREE、HASH、R TREE、FULL TEXT
- 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
- 主键索引、二级（辅助）索引
- 稠密索引、稀疏索引：是否索引了每一个数据项
- 简单索引、组合索引: 是否是多个字段的索引
- 左前缀索引：取前面的字符做索引
- 覆盖索引：从索引中即可取出要查询的数据，性能高
1.4.2 索引结构
参考链接 : https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉树
参考链接: https://www.cs.usfca.edu/~galles/visualization/BST.html
红黑树
参考链接:https://www.cs.usfca.edu/~galles/visualization/RedBlack.html
- 根节点是黑色的, 叶节点是不存储数据的黑色空节点,图中叶节点为正方形的黑色节点
- 任何相邻的两个节点不能同时为红色,红色节点被黑色节点隔开,红色节点的子节点是黑色的
- 任意节点到其可到达的叶节点间包含相同数量的黑色节点,保证任何路径相差不会超出2倍,从而实现基本平衡
B-Tree 索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+Tree索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
InnoDB中一颗的B+树可以存放多少行数据？
假设定义一颗B+树高度为2，即一个根节点和若干叶子节点。那么这棵B+树的存放总行记录数=根节点指针数*单个叶子记录的行数。这里先计算叶子节点，B+树中的单个叶子节点的大小为16K，假设每一条目为1K，那么记录数即为16(16k/1K=16)，然后计算非叶子节点能够存放多少个指针，假设主键ID为bigint类型，那么长度为8字节，而指针大小在InnoDB中是设置为6个字节，这样加起来一共是14个字节。那么通过页大小/(主键ID大小+指针大小），即16384/14=1170个指针，所以一颗高度为2的B+树能存放16*1170=18720条这样的记录。根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录。所以在InnoDB中B+树高度一般为2-3层，它就能满足千万级的数据存储
可以使用B+Tree索引的查询类型：(假设前提: 姓,名,年龄三个字段建立了一个复合索引)
- 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
- 匹配最左前缀：即只使用索引的第一列，如：姓wang
- 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录
- 匹配范围值：如：姓ma和姓wang之间
- 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的记录
- 只访问索引的查询
B+Tree索引的限制：
- 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
- 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
Hash索引
Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
- 不适用于顺序查询：索引存储顺序的不是值的顺序
- 不支持模糊匹配
- 不支持范围查询
- 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
地理空间数据索引R-Tree（ Geospatial indexing ）
MyISAM支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持
全文索引(FULLTEXT)
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持
聚簇和非聚簇索引，主键和二级索引
冗余和重复索引：
- 冗余索引：（A），（A，B）,注意如果同时存在,仍可能会使用(A)索引
- 重复索引：已经有索引，再次建立索引
1.4.3 索引优化
- 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数 操作和表达式操作）
- 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的
- 记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
- 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
- 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或者order by子句，应该建立复合索引
- 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
- 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
- 不要使用RLIKE正则表达式会导致索引失效
- 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
- 大部分情况连接效率远大于子查询
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启查询缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
1.4.4 管理索引
创建索引：
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;
删除索引:
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
查看索引：
SHOW INDEX FROM [db_name.]tbl_name;
优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1; #MySQL无此变量
SHOW INDEX_STATISTICS;
范例:
MariaDB [hellodb]> SET GLOBAL userstat=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [hellodb]> show index 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
Comment:
Index_comment:
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)
MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 1 |
+--------------+------------+------------+-----------+
MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)
MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb | students | PRIMARY | 2 |
+--------------+------------+------------+-----------+
1 row in set (0.000 sec)
1.4.5 EXPLAIN 工具
可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询
参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.htm
语法：
EXPLAIN SELECT clause
EXPLAIN输出信息说明：
说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system >const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref
NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
备注：掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL
范例：
MariaDB [hellodb]> create index idx_name on students(name(10));
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> 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
Comment:
Index_comment:
*************************** 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.000 sec)
MariaDB [hellodb]> explain select * from students where name like 'w%';
+------+-------------+----------+-------+---------------+----------+---------+---
---+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+---
---+------+-----------------------+
| 1 | SIMPLE | students | range | idx_name | idx_name | 152 |
NULL | 1 | Using index condition |
+------+-------------+----------+-------+---------------+----------+---------+---
---+------+-----------------------+
1 row in set (0.000 sec)
MariaDB [hellodb]> explain select * from students where name like 'x%';
+------+-------------+----------+------+---------------+------+---------+------+-
-----+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
+------+-------------+----------+------+---------------+------+---------+------+-
-----+-------------+
| 1 | SIMPLE | students | ALL | idx_name | NULL | NULL | NULL |
25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+-
-----+-------------+
1 row in set (0.000 sec)
范例: 复合索引
mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
#创建复合索引
mysql> create index idx_name_age on students(name,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | MUL | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
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
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. 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:
Visible: YES
Expression: NULL
*************************** 3. 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:
Visible: YES
Expression: NULL
3 rows in set (0.01 sec)
#跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
mysql> explain select * from students where age=20;
+----+-------------+----------+------------+------+---------------+------+-------
--+------+------+----------+-------------+
| 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 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+-------
--+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
范例: 复合索引和覆盖索引
mysql> desc testlog;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
| salary | int(11) | YES | | 20 | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#创建复合索引
mysql> create index idx_name_salary on testlog(name,salary);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc testlog;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | MUL | NULL | |
| salary | int(11) | YES | | 20 | |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> show indexes from testlog\G
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 90620
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: testlog
Non_unique: 1
Key_name: idx_name_salary
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 95087
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: testlog
Non_unique: 1
Key_name: idx_name_salary
Seq_in_index: 2
Column_name: salary
Collation: A
Cardinality: 99852
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)
#覆盖索引
mysql> explain select * from testlog where salary=66666;
+----+-------------+---------+------------+-------+-----------------+------------
-----+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+------------
-----+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | testlog | NULL | index | idx_name_salary |
idx_name_salary | 36 | NULL | 100290 | 10.00 | Using where; Using index
|
+----+-------------+---------+------------+-------+-----------------+------------
-----+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
1.4.6 使用 profile 工具
#打开后，会显示语句执行详细的过程
set profiling = ON;
#查看语句,注意结果中的query_id值
show profiles ;
MariaDB [hellodb]> show profiles ;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 0.00019238 | select @@profiling |
| 2 | 0.00115590 | select * from students where age=20 |
| 3 | 0.00006616 | show profiles for query 2 |
| 4 | 4.00319568 | select sleep(1) from teachers |
+----------+------------+-------------------------------------+
4 rows in set (0.000 sec)
#显示语句的详细执行步骤和时长
Show profile for query #
MariaDB [hellodb]> show profile for query 4;
| Status | Duration |
+------------------------+----------+
| Starting | 0.000157 |
| Checking permissions | 0.000009 |
| Opening tables | 0.000025 |
| After opening tables | 0.000005 |
| System lock | 0.000004 |
| Table lock | 0.000006 |
| Init | 0.000017 |
| Optimizing | 0.000009 |
| Statistics | 0.000018 |
| Preparing | 0.000028 |
| Executing | 0.000003 |
| Sending data | 0.000070 |
| User sleep | 1.001128 |
| User sleep | 1.000313 |
| User sleep | 1.000834 |
| User sleep | 1.000348 |
| End of update loop | 0.000032 |
| Query end | 0.000003 |
| Commit | 0.000014 |
| Closing tables | 0.000004 |
| Unlocking tables | 0.000003 |
| Closing tables | 0.000012 |
| Starting cleanup | 0.000003 |
| Freeing items | 0.000056 |
| Updating status | 0.000024 |
| Logging slow query | 0.000069 |
| Reset for next command | 0.000004 |
+------------------------+----------+
27 rows in set (0.000 sec)
MariaDB [hellodb]>
#显示cpu使用情况
Show profile cpu for query #
MariaDB [hellodb]> Show profile cpu for query 4;
+------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting | 0.000157 | 0.000090 | 0.000065 |
| Checking permissions | 0.000009 | 0.000005 | 0.000004 |
| Opening tables | 0.000025 | 0.000014 | 0.000010 |
| After opening tables | 0.000005 | 0.000003 | 0.000002 |
| System lock | 0.000004 | 0.000002 | 0.000002 |
| Table lock | 0.000006 | 0.000004 | 0.000002 |
| Init | 0.000017 | 0.000010 | 0.000007 |
| Optimizing | 0.000009 | 0.000005 | 0.000004 |
| Statistics | 0.000018 | 0.000010 | 0.000007 |
| Preparing | 0.000028 | 0.000016 | 0.000012 |
| Executing | 0.000003 | 0.000002 | 0.000002 |
| Sending data | 0.000070 | 0.000059 | 0.000000 |
| User sleep | 1.001128 | 0.000665 | 0.000000 |
| User sleep | 1.000313 | 0.000716 | 0.000000 |
| User sleep | 1.000834 | 0.000379 | 0.000100 |
| User sleep | 1.000348 | 0.000319 | 0.000231 |
| End of update loop | 0.000032 | 0.000017 | 0.000012 |
| Query end | 0.000003 | 0.000002 | 0.000002 |
| Commit | 0.000014 | 0.000008 | 0.000005 |
| Closing tables | 0.000004 | 0.000002 | 0.000002 |
| Unlocking tables | 0.000003 | 0.000002 | 0.000001 |
| Closing tables | 0.000012 | 0.000007 | 0.000005 |
| Starting cleanup | 0.000003 | 0.000001 | 0.000001 |
| Freeing items | 0.000056 | 0.000034 | 0.000024 |
| Updating status | 0.000024 | 0.000013 | 0.000010 |
| Logging slow query | 0.000069 | 0.000040 | 0.000029 |
| Reset for next command | 0.000004 | 0.000002 | 0.000001 |
+------------------------+----------+----------+------------+
27 rows in set (0.000 sec)
MariaDB [hellodb]>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了