Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你


// 创建

create table user(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引,unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复,主键字段,不能为NULL,也不能重复。

创建唯一索引

create table user(id int primary key auto_increment, name varchar(32));

create unique index 索引名 on 表名 (列表…);

查询索引

desc 表名 //不能够显示索引名

show index(es) from 表名

show keys from 表名

删除索引

alter table 表名 drop index 索引名;

//如果删除主键索引。

alter table 表名 drop primary key

索引使用的注意事项

由于索引本身很大,占用磁盘空间,对dml操作有影响,变慢,满足以下条件的字段,才应该创建索引。

  1. 肯定在where条经常使用
  2. 该字段的内容不是唯一的几个值
  3. 字段内容不是频繁变化

explain 可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令。

  1. id:查询的序列号。
  2. select_type:查询类型。
  3. table:查询表名。
  4. type:扫描方式,all表示全表扫描。
  5. possible_keys:可是使用到的索引。
  6. key:实际使用到的索引。
  7. rows:该sql扫面了多少行。
  8. Extra:sql语句额外的信息,比如排序方式

sql语句的小技巧

  1. 在使用group by 分组查询时,默认分组后,还会排序,可能会降低速度,在group by 后面增加 order by null 就可以防止排序。如下图所示

  1. 有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno;

// 替换成

select * from dept left join emp on dept.deptno=emp.deptno;

正确的选择mysql的存储引擎

Myisam : 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表,回复表。

INNODB : 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表。

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

分表技术

为什么要分表?

(1) 如果一个表的每条记录的内容很大,那么就需要更多的IO操作,如果字段值比较大,而使用频率相对比较低,可以将大字段移到另一张表中,当查询不查大字段的时候,这样就减少了I/O操作

(2)如果表的数据量非常非常大,那么查询就变的比较慢;也就是表的数据量影响这查询的性能。

(3)表中的数据本来就有独立性,例如分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用,而另外一些数据不常用。

(4) 分表技术有(水平分割和垂直分割)

垂直分割

垂直分割是指数据表列的拆分,把一张列比较多的表拆分为多张表。垂直分割一般用于拆分大字段和访问频率低的字段,分离冷热数据。

垂直分割比较常见:例如博客系统中的文章表,比如文章tbl_articles

(id, titile, summary, content, user_id, create_time),因为文章中的内容content会比较长,放在tbl_articles中会严重影响表的查询速度,所以将内容放到tbl_articles_detail(article_id, content),像文章列表只需要查询tbl_articles中的字段即可。

垂直拆分的优点:可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

垂直拆分的缺点:主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂。

水平分割

水平拆分是指数据表行数据的拆分,表的行数超过500万行或者单表容量超过10GB时,查询就会变慢,这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当,比较均匀。

水平拆分会给应用增加复杂度,它通常在查询是需要多个表名,查询所有数据需要union操作。在许多数据库应用中,这种复杂性会超过它带来的优点。

因为只要索引关键字不大,则在索引用于查询时,表中增加2-3倍数据量,查询时也就增加读一个索引层的磁盘次数,所以水平拆分要考虑数据量的增长速度,根据实际情况决定是否需要对表进行水平拆分。

水平分割最重要的是找到分割的标准,不同的表应根据业务找出不同的标准

  1. 用户表可以根据用户的手机号段进行分割如user183、user150、user153、user189等,每个号段就是一张表
  2. 用户表也可以根据用户的id进行分割,加入分3张表user0,user1,user2,如果用户的id%3=0就查询user0表,

如果用户的id%3=1就查询user1表

  1. 对于订单表可以按照订单的时间进行分表


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
2天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
11 3
|
5天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
22 1
|
12天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
39 9
|
12天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
33 5
|
17天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
20 1
|
17天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
6天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
37 0
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
34 0
|
16天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
71 0