MySQL索引-2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL索引2

存储引擎

1.简介
相当于Linux文件系统,只不过比文件系统强大

2、功能了解
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
等.

  1. 存储引擎介绍
    show engines;
    CSV
    MRG_MYISAM
    MyISAM
    BLACKHOLE
    PERFORMANCE_SCHEMA
    MEMORY
    ARCHIVE
    InnoDB
    FEDERATED

笔试题:
InnoDB ,MyISAM ,MEMORY,CSV

默认的存储引擎:InnoDB

PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

第三方的存储引擎:
RocksDB MyRocks TokuDB
压缩比较高,数据的插入性能高.其他功能和InnoDB没差.

  1. 简历案例---zabbix监控系统架构整改(真实案例)
    环境: zabbix 3.2 mariaDB 5.5 centos 7.3
    现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
    问题 :
  2. zabbix 版本
  3. 数据库版本
  4. zabbix数据库500G,存在一个文件里

优化建议:
1.数据库版本升级到mariaDB最新版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

为什么?

  1. 原生态支持TokuDB,另外经过测试环境,10版本要比5.5 版本性能 高 2-3倍
  2. TokuDB:insert数据比Innodb快的多,数据压缩比要Innodb高
    3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
    4.关闭binlog ----->减少无关日志的记录.
    5.参数调整...----->安全性参数关闭,提高性能.
  1. InnoDB 存储引擎核心特性说明
    事务
    行锁
    MVCC
    外键
    ACSR自动故障恢复
    热备
    复制(多线程,GTID,MTS)

  2. InnoDB个MyISAM存储引擎的替换(客户案例)
    环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
    现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
    问题分析:
    1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
    2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
    职责
    1.监控锁的情况:有很多的表锁等待
    2.存储引擎查看:所有表默认是MyISAM
    解决方案:

  3. 先升mysql 5.5,再升级MySQL 5.6.10版本
  4. 迁移所有表到新环境
  5. 开启双1安全参数
  1. 存储引擎的查看(了解)

7.1 查看存储引擎设置
mysql> show engines;
mysql> SELECT @@default_storage_engine;
vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB
7.2 查看表存储引擎状态

查看创表语法:
mysql> show create table t111;

查看某个表的状态信息:
mysql> SHOW TABLE STATUS LIKE 'CountryLanguage'\G

查看mysql所有非系统数据库的表的存储引擎: *
mysql>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

8.存储引擎的修改
8.1 修改存储引擎
create table t222 (id int,name varchar(20)) engine=myisam;
alter table t222 engine=innodb;
show create table t222;

8.2 整理碎片 *
mysql> alter table t111 engine=innodb;

平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工truncate表,然后导入进去
(delete:删除行或所有行,但不释放空间;truncate:删除表所有行,释放空间 *)
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式
定期进行碎片整理

8.3 批量替换zabbix 100多张 innodb为tokudb
alter table zabbix.a engine=tokudb;
select concat("alter table ",table_schema,".",table_name," engine=tokudb;") from information_schema.tables
where table_schema='zabbix';

  1. InnoDB存储引擎物理存储结构

9.0 最直观的存储方式(/usr/local/mysql/data) **
查看mysql的数据目录:select @@datadir;

auto.conf: mysql实例的UUID号,主从群集中不允许重复
ibdata1:系统数据字典信息(统计信息,元数据信息,information_schema),UNDO(回滚)表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件,事务日志文件。
ibtmp1: 临时表空间磁盘位置,存储临时表
frm:存储表的列信息
ibd:表的数据行和索引

9.1 表空间(Tablespace)

9.1.0 表空间数据问题
ibdata1 : 整个库的统计信息+Undo *
ibd : 数据行和索引

9.1.1 共享表空间(ibdata1~N) *
5.5 版本的默认模式,5.6中转换为了独立表空间
需要将所有数据存储到同一个表空间中 ,管理比较混乱
5.5版本出现的管理模式,也是默认的管理模式。
5.6版本以后,共享表空间保留,只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了

具体变化参考官方文档:
https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

共享表空间设置(在搭建MySQL时,初始化数据之前设置到参数文件中)
select @@innodb_data_file_path;
show variables like '%extend%';

例如: *
mysqld --initialize-insecure --user=mysql --basedir=xxxxxx......
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
注释:autoextend 最大自增空间64TB,每次自增8MB

9.1.2 独立表空间 *
从5.6,默认表空间不再使用共享表空间,替换为独立表空间。
主要存储的是用户数据
存储特点为:一个表一个ibd文件,存储数据行和索引信息

9.1.3 最终结论:
一张InnoDB表= frm+ibd+ibdata1
MySQL的存储引擎日志:
Redo Log: ib_logfile0 ib_logfile1,重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中),回滚日志

临时表:ibtmp1,在做join union操作产生临时数据,用完就自动清理

9.1.4 独立表空间设置问题
select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+

9.1.5 独立表空间迁移
(1)创建和原表结构一致的空表
(2)将空表的ibd文件删除
alter table city discard tablespace;
(3)将原表的ibd拷贝过来,并且修改权限
(4)将原表ibd进行导入
alter table city import tablespace;

9.2 真实故障案例
9.2.1 案例背景:
硬件及软件环境:
联想服务器(IBM)
磁盘500G 没有raid
centos 6.8
mysql 5.6.33 innodb引擎 独立表空间
备份没有,日志也没开

开发用户专用库:
track(bug追踪) 、 inner(内部数据库) ------>LNMT

9.2.2 故障描述:
断电了,启动系统失败,"/"只读
fsck 重启,系统成功启动,mysql启动不了。
(注:centos6修复文件系统用fsck ,centos7修复文件系统用xfs_repair)
结果:inner库在 , track库不见了

9.2.3 求助内容:

这种情况怎么恢复?

连二进制日志都没有,没有备份,没有主从

没办法,track库需要硬盘恢复。

求助:
1、track问题找数据修复公司
2、能不能暂时把inner库先打开用着
将生产库inner,拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

有没有工具能直接读取ibd?

最后发现没有

9.2.4 办法

表空间迁移:
create table t1;
alter table inner.t1 discard tablespace;
alter table t1 row_format=compact;
复制源ibd文件,改权限: chown mysql.mysql t1.ibd
alter table inner.t1 import tablespace;
虚拟机测试可行。

9.2.5 处理问题思路:
inner库中一共有107张表。
(1)创建107张和原来一模一样的表。
开发电脑上 mysqldump备份inner库
mysqldump -uroot -ppassw0rd -B inner --no-data >test.sql
拿到测试库,进行恢复
到这步为止,表结构有了。

(2)表空间删除。
select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema='test' into outfile '/tmp/discad.sql';
source /tmp/discard.sql

执行过程中发现,有20-30个表无法成功。主外键关系
很绝望,一个表一个表分析表结构,很痛苦。

set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

(3)拷贝生产中inner库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='test' into outfile '/tmp/import.sql';

(4)验证数据
表都可以访问了,数据挽回到了出现问题时刻的状态

===========================================================================

  1. InnoDB 核心特性*

10.1 事务
10.1.1 事务的ACID特性
Atomic(原子性)
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent(一致性)
如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态。

Isolated(隔离性)
事务之间不相互影响。

Durable(持久性)
事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

10.1.2 事务的生命周期(标准的事务控制语句)

(1) 如何开启事务
begin ;

(2) 标准的事务语句
DML :
insert
update
delete
mysql> use world;
mysql> update city set countrycode='CHN' where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> update city set countrycode='CHN' where id=3;

(3)事务的结束
提交:
commit;
回滚:
rollback;

10.1.3 自动提交机制(autocommit)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

在线修改参数:
(1) 会话级别:
mysql> set autocommit=0;
及时生效,只影响当前登录会话
(2)全局级别:
mysql> set global autocommit=0;
断开窗口重连后生效,影响到所有新开的会话
(3)永久修改(重启生效) *
vim /etc/my.cnf
autocommit=0

10.1.4 隐式提交的情况
触发隐式提交的语句:
begin
a
b
create database
导致提交的非事务语句:
DDL语句: (ALTER、CREATE 和 DROP)
DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

10.2 事务的ACID如何保证?

10.2.1 一些概念名词
redo log: 重做日志
ib_logfile0~1 默认50M , 轮询使用

redo log buffer :
redo内存区域

ibd :
存储 数据行和索引

data buffer pool :
缓冲区池,数据和索引的缓冲

LSN : 日志序列号
ibd ,redolog ,data buffer pool, redo buffer

MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

WAL (持久化):
write ahead log 日志优先写的方式实现持久化
日志是优先于数据写入磁盘的.

脏页:
内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.

CKPT:
Checkpoint,检查点,就是将脏页刷写到磁盘的动作

TXID:
事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

10.2.2 事务日志-- redo 重做日志
作用?
主要功能 保证 "D" , A C 也有一定得作用
(1)记录了内存数据页的变化.
(2)提供快速的持久化功能(WAL)
(3)CSR过程中实现前滚的操作(磁盘数据页和redo日志LSN一致)

redo日志位置
redo的日志文件:iblogfile0 iblogfile1

redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号

redo的刷写策略
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一: *
我们做了一个事务,begin;update;commit.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer

  1. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,
    在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
    6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
    7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
    MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正常启动
    以上的工作过程,我们把它称之为基于REDO的"前滚操作"

10.2.3 undo
回滚日志.
作用: 在 ACID特性中,主要保证A的特性,同时对CI也有一定功效

(1)记录了数据修改之前的状态
(2)rollback 将内存的数据修改恢复到修改之前
(3)在CSR中实现未提交数据的回滚操作
(4)实现一致性快照,配合隔离级别保证MVCC,读和写的操作不会互相阻塞

10.2.4 锁(为了防止出现脏读、幻读、不可重复读)
读锁(共享锁): 我读的时候,其他人不能改
写锁(排他锁): 我写的时候,其他不能改,也不能读
写锁又分为:
row-level lock 行锁: 锁行
gap lock 间隙锁: 锁区域,锁范围
next-key lock 临键锁(行锁+间隙锁)

10.2.5 隔离级别 *
RU : 读未提交,可脏读、幻读,一般不许出现
RC : 读已提交,可能出现幻读,可以防止脏读.(高并发网站为了快速读写,会使用RC级别)
RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(临键锁)
SR : 可串行化,可以防止死锁,但是并发事务性能较差

transaction_isolation=read-uncommitted #隔离级别最低,并发性能高
transaction_isolation=read-committed #锁定正在读取的行
transaction_isolation=REPEATABLE-READ #锁定所读取的所有行
MVCC ---> undo 快照

RU 会出现脏读 ,
RC 会出现不可重复读 ,也会出现幻读.
RR 通过MVCC基础解决了不可重复读,但是有可能会出现幻读现象
在RR模式下,GAP和Next-lock进行避免幻读现象,必须索引支持

查看隔离级别:select @@tx_isolation;


脏读、幻读、不可重复读的区别:

  1. 脏读 : 脏读就是指当一个事务正在访问数据,并且对数据进行了修改,

         而这种修改还没有提交到数据库中,这时,另外一个事务也访问 
         这个数据,然后使用了这个数据。
    
  2. 不可重复读 :是指在一个事务内,多次读同一数据。在这个事务还没有结束时,

         另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数
         据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可
         能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,
         因此称为是不可重复读。
    
  3. 幻读 : 是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据

         进行了修改,这种修改涉及到表中的全部数据行。 同时,第二个事务也修改这个
         表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个
         事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
    

不可重复读的重点是修改 :
同一事务,两次读取到的数据不一样。

幻读的重点在于新增或者删除:
同样的条件 , 第 1 次和第 2 次读出来的记录数不一样

脏读:
强调的是第二个事务读到的不够新。

====================================
实验:验证隔离级别
前提:创建库和表
mysql -uroot -p123.com
create database world;
use world;
create table t1 (id int primary key not null,name varchar(20));
insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'maliu'),(5,'zhuqi'),(6,'shenba'),(7,'gengjiu');

1.修改隔离级别为RU,验证脏读
vim /etc/my.cnf
添加:
transaction_isolation=read-uncommitted
autocommit=0
保存退出
systemctl restart mysqld

同时开启两个mysql连接窗口:
use world
select * from t1;

第一个窗口:
update t1 set name='hehe' where id=1;

第二个窗口:
select * from t1;
如果能看到commit之前的数据就是脏读。

2.修改隔离级别为RC,验证幻读
vim /etc/my.cnf
修改:
transaction_isolation=read-committed
保存退出
systemctl restart mysqld

同时开启两个mysql连接窗口:
use world
select * from t1;

第一个窗口:
update t1 set name='haha' where id>3;
commit;
select * from t1;

第二个窗口:
insert into t1 values (8,'zhangsan'),(9,'lisi'),(10,'wangwu');
commit;
select * from t1;

注:第二个窗口先提交,发现最终查看结果不一样,幻读

3.修改默认隔离级别为RR
vim /etc/my.cnf
修改:
transaction_isolation=REPEATABLE-READ
保存退出

systemctl restart mysqld

再按照第二个RC的步骤,实验一遍,会出现锁的状态避免幻读

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
199 66
|
18天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
118 9
|
3月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
1天前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
39 22
 MySQL秘籍之索引与查询优化实战指南
|
3天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
21 10
|
23天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
61 18
|
15天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
48 8
|
22天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
22 7
|
21天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
52 5
|
25天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
105 7