MySQL索引学习-1

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL索引学习

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

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

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

笔试题: 常见的存储引擎?
InnoDB ,MyISAM ,MEMORY,CSV

MySQL默认的存储引擎:InnoDB
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

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

  1. Innodb于myisam 的区别 *
    (1)innodb支持事务
    (2)InnoDB支持外键
    (3)InnoDB是聚集索引,myisam是非聚集索引
    (4)InnoDB不保存表的具体行数,而MyISAM用一个变量保存了整个表的行数
    (5)Innodb不支持全文索引,而MyISAM支持全文索引
    (6)MyISAM表格可以被压缩后进行查询操作
    (7)InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁
    (8)InnoDB表必须有唯一索引
    (9)Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI

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

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

优化结果:
监控状态良好

为什么?
(1)Mariadb支持TokuDB,经过测试:10版本要比5.5 版本性能 高 2-3倍
(2)TokuDB:insert数据比Innodb快的多,数据压缩比要比Innodb高
(3)监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
(4)关闭binlog ----->减少无关日志的记录.
(5)参数调整...----->安全性参数关闭,提高性能.

  1. InnoDB 存储引擎核心特性说明
    事务
    行锁:Myisam为表锁
    MVCC:多版本并发控制
    外键
    ACSR:自动故障恢复
    热备
    复制(多线程,GTID:全局事务标识,MTS:并行复制)
  1. InnoDB和MyISAM存储引擎的替换(客户案例)
    环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
    现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
(1)MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
(2)MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责:
(1)监控锁的情况:有很多的表锁等待
(2)存储引擎查看:所有表默认是MyISAM

解决方案:
(1)先升级mysql 5.5,再升级MySQL 5.6.10版本
(2)迁移所有表到新环境
(3)开启双1安全参数

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

mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置,这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义,性能,安全角度阐述两个参数为不同的值时对db 性能,数据的影响。

1.参数意义

innodb_flush_log_at_trx_commit
设置为0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作;
设置为1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去;
设置为2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意:由于进程调度策略问题,这个"每秒执行一次 flush(刷到磁盘)操作"并不是保证100%的"每秒"。

sync_binlog
默认值是0,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注意:如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。

  1. 性能
    查看当前参数值:
    show variables like 'innodb_flush_log%';
    show variables like 'sync_binlog%';

两个参数在不同值时对db的纯写入的影响表现如下:
测试场景1
innodb_flush_log_at_trx_commit=2
sync_binlog=1000

测试场景2
innodb_flush_log_at_trx_commit=1
sync_binlog=1000

测试场景3
innodb_flush_log_at_trx_commit=1
sync_binlog=1

在以上3个场景下的TPS分别为:
场景1 41000
场景2 33000
场景3 26000

由此可见,当两个参数设置为双1的时候,写入性能最差,sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时,(在当前模式下)MySQL的写操作才能达到最高性能。

  1. 安全

当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得,双11 会导致频繁的io操作,因此该模式也是最慢的一种方式。
当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。

"双1设置"适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。

============================================================================
1.存储过程定义

存储过程是一组为了完成特定功能的 SQL 语句集合。
使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。

2.存储过程的优点:
(1)封装性
存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。

(2)可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

(3) 可减少网络流量
由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。

(4)高性能
存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。

(5)提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。

3.创建无参数存储过程(CREATE PROCEDURE)
create database school default charset utf8mb4 collate utf8mb4_bin;
use school;
create table tb_students_score (student_name char(20) not null,student_score int);
insert into tb_students_score values ('dany','90'),('green','99'),('henry','95'),('jane','98'),('jim','88');

DELIMITER // #修改mysql结束符为//
CREATE PROCEDURE ShowStuScore()
BEGIN
SELECT * FROM tb_students_score;
END //

调用存储过程
DELIMITER ; #把mysql结束符改回;号
CALL ShowStuScore();

4.创建有参数的存储过程
DELIMITER //
CREATE PROCEDURE GetScoreByStu
(IN name VARCHAR(30))
BEGIN
SELECT student_score FROM tb_students_score
WHERE student_name=name;
END //

调用存储过程
DELIMITER ;
CALL GetScoreByStu('green');

5.修改存储过程
ALTER PROCEDURE <过程名> [ <特征> … ]

6.删除存储过程
DROP PROCEDURE GetScoreByStu;

7.查看存储过程
show procedure status;

8.查看创建存储过程语法
show create procedure ShowStuScore;

===============================================================================
存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:

IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

1、in 输入参数

delimiter //
create procedure in_param(in p_in int)
begin
select p_in;
set p_in=2;
select p_in;
end//

delimiter ;

set @p_in=1;

call in_param(@p_in);

select @p_in;

可以看出,p_in 在存储过程中被修改,但并不影响 @p_in 的值,因为前者为局部变量、后者为全局变量。

2、out输出参数

delimiter //
create procedure out_param(out p_out int)
begin
select p_out;
set p_out=2;
select p_out;
end //

delimiter ;

set @p_out=1;

call out_param(@p_out);

因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null

select @p_out;

调用了out_param存储过程,输出参数,改变了p_out变量的值

3、inout输入参数

delimiter //
create procedure inout_param(inout p_inout int)
begin
select p_inout;
set p_inout=2;
select p_inout;
end //

delimiter ;

set @p_inout=1;

call inout_param(@p_inout);

select @p_inout;

调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量

扩展:https://www.cnblogs.com/phpper/p/7587556.html

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器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秘籍之索引与查询优化实战指南
|
11天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
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