Mysql 高级（全）

最新推荐文章于 2024-01-15 20:35:40 发布
最新推荐文章于 2024-01-15 20:35:40 发布
阅读量687 收藏 8
点赞数
分类专栏： 数据库 文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46079238/article/details/128191252
版权

（该篇写
1.Mysql架构组成，
2.Mysql的恢复备份
3.Mysql查询和慢查询日志分析
4.Mysql的存储引擎
5.Mysql索引优化）

1.Mysql架构组成

先说说一个DBA的职责

  • 负责MySQL的容量规划，架构设计及安装、部署.
  • 负责MySQL的日常管理，监控和维护, 并对MySQL进行持续性能优化.
  • 负责MySQL开发支持，参与数据架构规划设计，以及相关业务的数据建模、设计评审、SQL代码 审核优化.

Mysql的逻辑架构

学习mysql就好比盖房子 ，如果想把房子盖得特别高，地基一定要稳，基础一定要牢固。
Mysql由连接池、Sql接口、解析器、优化器、缓存、存储引擎等组成，即连接层。服务层，引擎层和文件系统层
在这里插入图片描述

1.连接层

最上边是一些客户端和连接服务，不是mysql特有的，所有基于网络的C/S的网络应用程序都应该包括这些连接处理，认证。安全管理等。

2.服务层

中间层是mysql的核心，包括查询解析，分析和优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程，触发器和视图等

3.引擎层

存储引擎层，他负责数据的存取，服务器通过Api可以通过各种存储引擎进行交互，不同的存储引擎具有不同的功能，我们可以根据实际的需求选择对应的存储引擎

4.存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并且完成存储引擎的交互

SQL查询流程

在这里插入图片描述

  1. 通过客户端/服务器通信协议与 MySQL 建立连接
  2. 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
  3. 预处理器生成新的解析树。
  4. 查询优化器生成执行计划。
  5. 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结
    果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 QueryCache 中，以后若有相同的 SQL 语句执行则直接返回结果。

MYSQL物理文件

物理文件包括 ：日志文件，数据文件，配置文件

日志文件：
error log 错误日志 排错 /var/log/mysqld.log (默认开启)
bin log 二进制文件 备份 增量备份DDL DMLDCl
Relay log 中继日志 复制
slow log 慢查询日志 调优 查询时间超过指定值

// 查看错误日志文件路径
show variables like 'log_error';

配置文件&数据文件

1》 配置文件my.cnf
在my.cnf文件中可以对一些参数进行设置，对数据库进行调优

[client] #客户端设置，即客户端默认的连接参数
port = 3307 #默认连接端口
socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字
default-character-set = utf8mb4 #编码
[mysqld] #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一
个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录
character_set_server = utf8mb4 #服务端默认编码（数据库级别）

2》数据文件

-- 查看数据文件的位置
show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------+

1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在
此文件中，包括表结构的定义信息等。
2、.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同
样存放在所属数据库的目录下
3、.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的
位置和.frm及.MYD一样
4、.ibd文件
存放innoDB的数据文件（包括索引）。
5. db.opt文件 此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

2Mysql的备份和恢复

为什么要对数据进行备份？
我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢 对于一些网站、系统来说，数据库就是一切，所以做好数据
库的备份是至关重要的！

数据库备份的应用场景

系统硬件或软件故障
自然灾害,比如水灾 火灾 地震等
黑客攻击,非法访问者故意破坏
误操作 , 人为的误操作占比最大

开发测试环境数据库搭建
数据库或者数据迁移
###数据备份的类型
按照业务场景分

完全备份

将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储
位置以及数据库中的全部对象和相关信息。

差异备份

备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件以及数据库中其他被修改的内容。

增量备份

增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。
在这里插入图片描述
在这里插入图片描述

Mysql的冷备份和热备份

冷备份：

冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

  1. 冷备份的优点：

是操作比较方便的备份方法（只需拷贝文件）
低度维护，高度安全。

  1. 冷备份的缺点:

在实施备份的全过程中，数据库必须要作备份而不能作其它工作。
若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢慢。
不能按表或按用户恢复。
冷备份实战：
关闭SELinux
修改 selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退出

vim /etc/selinux/config
SELINUX=disabled

修改后需要重启

reboot # 重启命令

找到MySQL数据文件位置,停止MySQL服务

SHOW VARIABLES LIKE '%dir%';
-- 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/
service mysqld stop -- 停止mysql

)进入到 /mysql 目录, 执行打包命令 将数据文件打包备份

cd /var/lib/ # 进入其上级目录
tar jcvf /root/backup.tar.bz2 mysql/ # 打包压缩到 root目录下

删除掉数据目录下的所有数据

-- 删除原目录
rm -rf /var/lib/mysql/

恢复数据 (使用tar命令)

-- 解压
tar jxvf backup.tar.bz2 mysql/
-- 把备份的文件移动到/var/lib/里面去替代原来的mysql
mv /root/mysql/ /var/lib/

启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功

service mysqld  start
热备份：

热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执
行一遍备份的sql语句。

  1. 热备份的优点: 可在表空间或数据文件级备份，备份时间短。 备份时数据库仍可使用。 可达到秒级恢复（恢复到某一时间点上）。
  2. 热备份的缺点:
    不能出错，否则后果严重。
    因难维护，所以要特别仔细小心，不允许“以失败而告终”。

mysqldump 备份工具

mysqldump是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）
可以用于备份。热备可以对多个库进行备份，可以对单张表或者某几张表进行备份。
备份单个数据库

[root@localhost ~]# mkdir databackup
[root@localhost ~]# cd databackup
[root@localhost databackup]# mysqldump -uroot -p ywc_edu > ywc_edu.sql

模拟数据丢失，删除数据库,然后重新创建一个新的库.

DROP DATABASE ywc_edu;
CREATE DATABASE ywc_edu CHARACTER SET 'utf8';

恢复数据

[root@localhost databackup]# mysqldump -uroot -p ywc_edu course course_lesson
> backupTable.sql

3mysql查询和慢查询日志分析

sql性能下降的原因
在日常的维护过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQl语句后 ，在对这些sql执行分析之前，需要明确可能导致sql执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面

  1. 等待时间长
  2. 执行时间长
    查询语句写的很烂
    索引失效
    关联查询太多
    服务器调优及各个参数的设置

需要遵守的优化原则

第一条: 只返回需要的结果
一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行
避免使用 select * from , 因为它表示查询表中的所有字段
第二条: 确保查询使用了正确的索引
经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；
将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；
多表连接查询的关联字段建立索引，可以提高连接查询的性能；
将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。
第三条: 避免让索引失效
在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效
使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引
如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL

sql的执行顺序

程序员编写的sql：
在这里插入图片描述
Msql执行的SQl：
在这里插入图片描述
1. FORM子句 : 左右两个表的笛卡尔积
2. ON: 筛选满足条件的数据
3. JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行
4. WHERE： 对不满足条件的行进行移除, 并且不能恢复
5. GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值
6. HAVING： 对分组后的数据进行筛选
7. SELECT： 执行select操作，获取需要的列。
8. DISTINCT： 去重
9. ORDER BY: 排序
10. LIMIT：取出指定行的记录, 并将结果返回。

join查询的七种方式

在这里插入图片描述

join 查询sql编写：

1、创建表 插入数据

--部门表
Drop table if exists t_dept;
create table t_dept (
id varchar(40) not null,
name varchar(40) default null,
primary key (id)
)engine=innodb Default charset=utf8;


---员工表
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
`id` varchar(40) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`deptid` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `deptid` (`deptid`),
CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');
--插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

在这里插入图片描述
在这里插入图片描述

内连接

在这里插入图片描述

select * from t_emp e inner join t_dept d on e.deptid =d.id

在这里插入图片描述

左连接

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id

在这里插入图片描述

左连接去重叠部分

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL;

在这里插入图片描述

右连接

在这里插入图片描述

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

在这里插入图片描述

右连接去重叠部分

在这里插入图片描述

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;

在这里插入图片描述

全连接

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

在这里插入图片描述

各自独有

在这里插入图片描述

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL

在这里插入图片描述

慢查询日志分析

慢查询介绍
MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。
默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。

慢查询参数

执行下面的语句

show variables like '%query%'；

MySQL 慢查询的相关参数解释：

slow_query_log 是否开启慢查询日志，1 表示开启，0 表示关闭。
slow-query-log-file 新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
long_query_time 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

. 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1;
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则
会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

-- 编辑配置
vim /etc/my.cnf
-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ywc-slow.log
-- 重启MySQL
service mysqld restart
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/ywc-slow.log |
+---------------------+-------------------------------+

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数
long_query_time 控制，默认情况下long_query_time的值为10秒

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到
呢？注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能
看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 参数是指定日志的存储方式。 log_output=‘FILE’ 表示将日志存入文件，默认值
是’FILE’。 log_output=‘TABLE’ 表示将日志存入数据库，这样日志信息就会被写入到
mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：
log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资
源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.
. 系统变量 log-queries-not-using-indexes ：未使用索引的查询也被记录到慢查询日志中（可选
项）。如果调优的话，建议开启这个选项。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)

4MYSQL存储引擎

存储引擎介绍

什么是存储引擎
在这里插入图片描述

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。就像汽车的发动机一样, 存储引擎好坏 决定的数据库提供的功能和性能
存储引擎的作用

  • 并发性
  • 事务支持
  • 引用完整性
  • 索引支持

常見的三种存储引擎

MySQL给用户提供了很多种类的存储引擎, 主要分两大类:

  • 事务安全表: InnoDB
  • 非事务安全表: MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、 CSV、BLACKHOLE、FEDERATED等。
    查看MySQL数据的存储引擎有哪些：
SHOW ENGINES;

在这里插入图片描述
查看当前的默认存储引擎 (MySQL5.7 默认使用 InnoDB)

SHOW VARIABLES LIKE '%default_storage_engine%';
  • 在MySQL中,不需要整个服务器都是用同一种引擎,针对具体的需求,可以对 每一个表使用不同的存 储引擎 .并且想要进一步优化,
    还可以自己编写一个存储引擎.
-- 创建新表时指定存储引擎
create table(...) engine=MyISAM;

接下来说一下InnoDB（推荐）：

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择
优点：

  • Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别
  • 支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快。
  • 支持自增长列。
  • 支持外键。
  • 适合于大容量数据库系统，支持自动灾难恢复。

缺点：

  • 它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表

MyISAM

MyISAM引擎, 不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
优点：

  • MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器。
  • MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点
  • 另外进行大批量插入操作时执行速度也比较快。

缺点：

  • MyISAM表没有提供对数据库事务的支持。
  • 不支持行级锁和外键。
  • 不适合用于经常UPDATE（更新）的表，效率低。

MEMORY

MEMORY的特点是 将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的纬度表
优点：

  • memory类型的表访问非常的快，因为它的数据是放在内存中的

缺点：

  • 一旦服务关闭，表中的数据就会丢失掉。
  • 只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式

如何选择这些存储引擎呢？

在这里插入图片描述
在这里插入图片描述

5MYSQL索引优化

什么是索引

索引就是排好序的,帮助我们进行快速查找的数据结构.
简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能.

专业一点来说呢，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

  1. 没有用索引时执行 select * from where t.Col2 = , 数据从磁盘一条一条拿去最终找到结果，
    效率低下。
  2. 为了加快查找,可以维护一个二叉树,左侧节点小于父节点, 右侧节点大于父节点,每个节点分别保存
    字段数据和一个指向对应数据记录物理地址的指针.
  3. 查找时 就可以使用二叉树查找获取相应的数据,从而快速检索出符合条件的记录
    一般来说索引本身也比较大,不可能全部保存在内存中,因此索引通常是以索引文件的形式存储在磁盘上
    索引的种类：
    普通索引：这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

唯一索引：与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主键索引：它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引：用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

  1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
  2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高
    全文索引：查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。
CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE两种
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');
-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE);

全文索引使用注意事项：
全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

索引的原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。
### Hash结构
Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

  • 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值， 并且不同键值的行计算出来的哈希码也不一样。

Hash索引的缺点

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
  • 哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

Hsah索引的优点

  • 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
  • 访问哈希索引的数据非常快，除非有很多哈希冲突。

B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

  • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接，提高区间的访问性能
    在这里插入图片描述
    B树索引的应用
  • 全键值查询 where x=123
  • 键值范围查询 where 45 < x < 123

Explain性能分析

Explain简介（这里只给出简介，Explain详细内容见后面的博客）

概述
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：
表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
表直接的引用。（对应ref）
每张表有多少行被优化器查询。（对应rows）

啊啊啊杨
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL高级MySQL高级
02-21
在这个“MySQL高级”主题中，我们将深入探讨SQL语句，这是与MySQL交互的基础。 一、SQL基础 SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。在MySQL中，SQL语句主要包括以下类型： 1. **...
MySQL数据库 --- 高级篇
shuai_h的博客
06-12 3927
大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
MySQL高级
qiumin的博客
05-19 3035
MySQL数据库高级进阶，索引，锁，分库分表，都写分离，MySQL数据库集群，运维
MySQL高级（三）MySQL的体系结构和存储引擎
m0_53312145的博客
05-21 381
MySQL的体系结构和存储引擎1 MySQL的体系结构2 存储引擎2.1 存储引擎概述2.2 存储引擎特性2.2.1 InnoDB 引擎 1 MySQL的体系结构 1） 连接层   最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作
【MYSQL高级篇】
xuxuxux123的博客
03-27 1479
mysql高级
Mysql高级篇
scfvlog的博客
06-27 716
（在MYISAM中读操作，会自动加上读锁，写操作自动加上写锁，因为这个引擎不支持事务，所有他会加强行锁来保证稳定性，而在InnoDB中必要情况下会用表锁，大多情况用的是行锁，这个后面会解释）作用：当事务A已经存在行锁的时候，mysql会自动为其增加一个意向锁，事务B如果想申请表级写锁，那么就不需要在遍历每一行是否有行锁（因为表级写锁和行级写锁不共存），直接判断是否存在意向锁即可，大大提高了时间效率（如果没有意向锁，那么就会循环遍历，浪费shi'j）排他锁常被用于修改数据的操作，例如插入、更新或删除操作。
mysql高级.mmap
07-10
找了好久找到的，尚硅谷MySQL高级思维导图mmap格式（不是pdf格式）完整版，不好的话骂我！
mysql高级笔记.zip
11-07
"mysql高级笔记.zip"压缩包文件很可能包含了一系列深入探讨MySQL高级特性和最佳实践的教程或文档。从`mysql_advanced-master`这个子文件名来看，我们可以预期这些内容可能组织成一个项目结构，涵盖了多个高级主题。 ...
Mysql高级文档超详细
12-14
"Mysql高级文档超详细" 本文档详细讲解了Mysql的高级知识点，涵盖了Mysql的安装、配置、管理等方面的内容。下面是对标题、描述、标签和部分内容的详细解释： 1. Mysql安装 Mysql的安装可以通过rpm包来实现。在...
MySQL高级详解
weixin_45900005的博客
11-18 2249
存储引擎 InnoDB和MyISAM性能对比对比 索引优化分析 SQL性能下降的原因 查询语句写的差。 索引失效：索引建了，但是没有用上。 关联 查询太多join（设计缺陷或者不得已的需求）。 服务器调优以及各个参数的设置（缓冲、线程数等）。 SQL机读执行顺序 select # 5 ... from # 1 ... where # 2 .... group by # 3 ...
mysql高级使用教程
最新发布
Ostkakah的博客
01-15 1151
页分裂：主键底层是存到数据页上，一遍主键是自增的，所以会进行顺序存储。但是当数据不按顺序的时候会，数据在找到对应的位置会会将后百分之五十的数据存储到新的页上，如何将新的看数据存储到对应的位置上，最终在调整页的位置。页合并：但主键进行删除的时候，会先将要删除的数据进行标记，当达到阈值（MERGE_THRESHOLD）时就会进行删除，并判断前后侧的页是否能合并，能就进行合并，最终出现页合并。因为二级索引中的数据就会主键，当二级索引多的情况就会占用大量的空间，并在数据的读取的时候需要大量的io操作。
MySql 高级-0706
lannister_awalys_pay的博客
07-09 303
mysql 架构；索引；索引优化；
MySql高级篇
weixin_45756851的博客
11-26 1538
事务、存储引擎、性能分析、索引、SQL优化、锁、InnoDB引擎
数据库应用：MySQL数据库SQL高级语句与操作
热门推荐
cronaldo91的博客
07-11 1万+
克隆表将数据表的数据记录生成到新的表中.清空表删除表内的所有数据。group by查询结果进行汇总分组，having用来过滤由group by语句返回的记录集，通常与group by语句联合使用。
MySQL高级语句的用法
System_TuTu
06-17 1134
select查询sql语句的用法 查询不重复记录 where 有条件的查询 and 且 ； or 或 通配符通常是和 like 一起使用
[MySQL] MySQL 高级(进阶) SQL 语句
热心网友
12-27 1653
视图能否插入数据，要看情况而定：1）如果视图表是两个表的连接查询（比如视图的A字段来自A表，B字段来自B表，数据是无法插入的）。因为表结构和原表不一致。视图中的字段是根据原表中某个字段，通过函数运算，产生的新字段，而没有真正能够存储的字段，所以该数据是无法插入的。2）如果视图表结构与原表保持一致，数据是可以插入的，插入的数据是存储在原表中，视图所更新出的数据，其实是映射原表的数据。
mysql高级
weixin_58838927的博客
04-27 82
MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。2.读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。垂直分库就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。使用最多的就是垂直分库，因为我们都是微服务开发，
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

啊啊啊杨

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值