代码收藏家 MySQL数据库笔记3：优化技巧
MySQL数据库—笔记3
一、储存引擎
1.1、MySQL体系结构
MySQL体系结构图
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
1.2、存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
- 在创建表时，指定存储引擎
CREATE TABLE表名(
字段1 字段1 类型[COMMENT 字段1 注释],
......
字段n 字段n 类型[COMMENT 字段n 注释]
)ENGINE = INNODB[COMMENT 表注释];
- 查看当前数据库的存储引擎
SHOW ENGINES;
1.3、存储引擎特点
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。
特点
- DML操作遵循ACID模型，支持事务;
- 行级锁，提高并发访问性能;
- 支持外键FOREIGN KEY约束，保证数据的完整性和正确性;
文件
xxx.ibd: xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构(frm.sdi)、数据和索引。
参数: innodb_file_per_table
一个Extent（区）的大小固定为 1M
一个 Page（页）的大小固定为 16K
一个区包含64个页
MyISAM
介绍
MyISAM是MySQL早期的默认存储引擎
特点
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
文件
xxx.sdi :存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引
Memory
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。
- 内存存放
- hash索引（默认)
xxx.sdi:存储表结构信息
存储引擎的区别
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | – | – |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | – | – | 支持 |
| 全文索引 | 高 | 低 | N/A |
| 空间使用 | 支持(5.6版本之后) | 支持 | – |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | – | – |
1.3、存储引擎选择
二、索引
MySQL 8.0 对于密码格式有以下要求：
- 密码长度必须大于等于 8 个字符；
- 密码必须包含大小写字母、数字和特殊字符；
- 特殊字符至少包括 !@#$%^&*()_±=[]{}|;':",./<>?;
- 不允许出现空格字符。
- 如果修改的密码不符合上述要求，系统会提示密码不符合要求，并且不允许设置。
需要注意的是，为了保证数据库的安全性，在设置 MySQL 8.0 的密码时，应该避免使用过于简单的口令，以免被攻击者轻易破解。建议使用强密码生成器来生成随机复杂的密码，并妥善保管密码信息。另外，为了提高安全性，尽量避免使用相同的密码同时连接到多个数据库服务器。
该错误提示为“拒绝访问”，说明 MySQL 数据库用户没有足够的权限或者用户名或密码输入错误。常见的解决方法如下：
- 首先，确认用户名和密码正确。如果忘记了密码，可以使用以下命令来修改密码：
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;
exit;
- 如果用户名和密码正确，那么请确认 MySQL 是否允许远程连接。在 MySQL 8.0 中，默认情况下只允许本地连接。可以通过以下步骤开启远程连接：
SELECT user,host FROM mysql.user;
- 如果没有可用于远程连接的用户，请添加新用户。例如：
CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';
FLUSH PRIVILEGES;
注意，在上述示例中，“%”表示允许所有主机远程连接。如果指定特定主机，请使用相应的 IP 地址或主机名；
- 如果以上方法均无效，则可能是由于安全组或防火墙等其他网络安全策略导致，请检查相关设置。
总之，需要仔细排查错误原因，逐步解决问题。
2.1、索引概述
索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
备注：上述二叉树索引结构的只是一个示意图，并不是真实的索引结构
索引的优缺点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的。 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
2.2、索引-结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包括以下几种：
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
索引在不同引擎当中的支持情况
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。
2.2.1、索引结构-B-Tree
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
以一颗最大度数〈max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针):
2.2.2、索引-结构-B+Tree
插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。
相当于B-Tree区别：
- 所有的数据都会出现在叶子节点
- 叶子节点i形成一个单项链表
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
2.2.3、索引-结构-hash
Hash索引特点
- Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，<，…)
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
存储引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
2.2.4、索引-结构-思考题
为什么InnoDB存储引擎选择使用B+tree索引结构?
2.3、索引-分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered lndex) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
思考题
lnnoDB主键索引的B+Tree高度为多高呢?
假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
高度为2：
n*8 + (n + 1)*6 = 16*1024,算出n约为1170
1171 *6 = 18736
高度为3：
1171 * 1171 * 16 = 21939856
2.4、索引-语法
CREATE [ UNIQUE |FULLTEXT] INDEX index_name ON table_name ( index_col_name....);
SHOW INDEX FROM table_name ;
DROP INDEX index_name ON table_name ;
# 给姓名创建一个索引
create index idx_user_name on user(name);
# 有phone创建一个唯一索引
create unique index idx_user_phone on user(phone);
# 给profession,age,status创建一个联合索引
create index idx_user_pro_age_sta on user(profession,age,status);
# 给email创建一个索引
create index idx_user_ema on user(email);
# 删除email的索引
drop index idx_user_ema on user;
2.5、索引-性能分析
2.5.1、索引-性能分析-查看执行频次
MySQL客户端连接成功后，通过show [session|globall] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE 'Com_______';
2.5.2、索引-性能分析-慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位:秒默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf）中配置如下信息:
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
