MySQL数据库-优化-详细优化-必看

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL数据库-优化-详细优化-必看

1. MySQL数据库 优化

MySQL数据库硬件的性能瓶颈:
1)CPU的瓶颈
2)I/O的瓶颈

对于MySQL系统本身性能优化:
1)使用索引
2)使用EXPLAIN分析查询
3)调整MySQL的内部配置

2. 索引

Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制。
Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。
Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。
Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索。
在MYSQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。
空间列类型的索引使用R-树,MEMORY表支持hash索引。

3. 使用索引注意事项

在查询中,WHERE条件也是一个比较重要的因素,尽量少并且是合理的where条件是很重要的,
尽量在多个条件的时候,把会提取尽量少数据量的条件放在前面,减少后一个where条件的查询时间。
应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
有些where条件会导致索引无效:
1.WHERE字句的查询条件里有不等于号(WHERE column!=…),MYSQL将无法使用索引
2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=…),MYSQL将无法使用索引
3.在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,
否则即使建立了索引也不会使用
4.如果WHERE子句的查询条件里使用了比较操作符LIKE,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能
使用索引。比如说,如果查询条件是LIKE 'abc%',MYSQL将使用索引;
如果条件是LIKE '%abc',MYSQL将不使用索引。
5.在ORDER BY操作中,MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。
尽管如此,在涉及多个数据表的查询里,即使有索引可用,那些索引在加快ORDER BY操作方面也没什么作用。
6.如果某个数据列里包含着许多重复的值,就算为它建立了索引也不会有很好的效果。
比如说,如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值,就没有必要为它创建一个索引。
7.如果条件中有or(并且其中有or的条件是不带索引的),
即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。
注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
8.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

4. 索引优化建议

合理的建立索引的建议:
(1)  越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。 
(2)  简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。
在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
(3)  尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,
含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。
你应该用0、一个特殊的值或者一个空串代替空值
 
这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。
1. 当结果集只有一行数据时使用LIMIT 1
2. 避免SELECT *,始终指定你需要的列从表中读取越多的数据,查询会变得更慢。
他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。
你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接(JOIN)来代替子查询(Sub-Queries)(理论上)
   连接(JOIN).. 之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤
   的查询工作。
4. 使用ENUM、CHAR 而不是VARCHAR,使用合理的字段属性长度
5. 尽可能的使用NOT NULL
6. 固定长度的表会更快
7. 拆分大的DELETE 或INSERT 语句
8. 查询的列越小越快

5. explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。
这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
1)表的读取顺序
2)数据读取操作的操作类型
3)哪些索引可以使用
4)表之间的引用
5)每张表有多少行被优化器查询

6. 案例分析。

1)EXPLAIN
SELECT * FROM (
                  SELECT go_shoplist.title,
                         go_member.username
                  FROM go_shoplist
                    LEFT JOIN go_member ON go_shoplist.q_uid = go_member.uid
                  WHERE go_shoplist.sid IN (196,175,7064,1370,185,195,199,203)
                    AND go_shoplist.q_uid IS NOT NULL
                    ORDER BY id
                ) a
                GROUP BY a.title ;
2)EXPLAIN
SELECT go_shoplist.title,
                     go_member.username
              FROM go_shoplist
                LEFT JOIN go_member ON go_shoplist.q_uid = go_member.uid
              WHERE go_shoplist.id IN (
                          SELECT MAX(id) FROM go_shoplist
                          WHERE go_shoplist.sid IN (196,175,7064,1370,185,195,199,203)
                            AND go_shoplist.q_uid IS NOT NULL
                            GROUP BY sid
                          )
              ORDER BY go_shoplist.id DESC;
为什么第二个更慢?
答: mysql 在处理子查询时,会改写子查询。
    通常情况下,我们希望由内到外,先完成子查询的结果,然后再用子查询来驱动外查询的表,完成查询。
    上一页的sql语句,通常我们会感性地认为该 sql 的执行顺序是:
    go_shoplist 表中根据 sid in (…)取得 max(id)记录,
    然后再到 go_shoplist中,带入 id in (…)取得查询数据。
    但是实际mysql的处理方式为:
    select * from go_shoplist … where exists (
    select * from go_shoplist where sid in (…)
    )
    mysql 将会扫描 go_shoplist 中所有数据,每条数据都将会传到子查询中与 go_shoplist 关联,
    子查询不会先被执行,所以如果 go_shoplist表很大的话,那么性能上将会出现问题。
3)还能继续优化吗?
答:   SELECT a.id,b.title,d.username
      FROM go_shoplist b
      RIGHT JOIN (SELECT MAX(id) AS id FROM go_shoplist c
                  WHERE c.sid IN (196,175,7064,1370,185,195,199,203)
                    AND c.q_uid IS NOT NULL
                    GROUP BY c.sid ) a ON a.id = b.id
      LEFT JOIN go_member d ON b.q_uid = d.uid
4)用explain看看好在哪了。

7. 配置优化

连接请求的变量:
1)max_connections: max_used_connections / max_connections * 100% (理想值≈ 85%)
2)back_log:默认数值是50,可调优为128,对于Linux系统设置范围为小于512的整数
3)interactive_timeout:默认数值是28800,可调优为7200

8. 其他优化建议

1)in 和 not in 要慎用(前面提到了)
2)应尽量避免在 where 子句中对字段进行表达式操作,
如:select id from t where num/2=100 
应改为: select id from t where num=100*2
3)在使用索引字段作为条件时,如果该索引是复合索引,
那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,
否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致
4)索引并不是越多越好,索引固然可以提高相应的 select 的效率,
但同时也降低了 insert 及 update 的效率
5)不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的字段
6)尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写
7)尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理
8)能用UNION ALL就不要用UNION
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 缓存 监控
数据库优化技术:提升性能与效率的关键策略
【10月更文挑战第15天】数据库优化技术:提升性能与效率的关键策略
46 8
|
5天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
17 7
|
1天前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
5 1
|
5天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
11 5
|
2天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
10 1
|
4天前
|
XML Java 数据库连接
如何使用HikariCP连接池来优化数据库连接管理
在Java应用中,高效管理数据库连接是提升性能的关键。本文介绍了如何使用HikariCP连接池来优化数据库连接管理。通过引入依赖、配置参数和获取连接,你可以显著提高系统的响应速度和吞吐量。 示例代码展示了从配置到使用的完整流程,帮助你轻松上手。
19 3
|
9天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
36 9
|
6天前
|
Java 数据库连接 数据库
优化之路:Java连接池技术助力数据库性能飞跃
在Java应用开发中,数据库操作常成为性能瓶颈。频繁的数据库连接建立和断开增加了系统开销,导致性能下降。本文通过问题解答形式,深入探讨Java连接池技术如何通过复用数据库连接,显著减少连接开销,提升系统性能。文章详细介绍了连接池的优势、选择标准、使用方法及优化策略,帮助开发者实现数据库性能的飞跃。
16 4
|
9天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
28 5
|
9天前
|
存储 缓存 监控
数据库优化:提升性能与效率的关键策略
【10月更文挑战第21】数据库优化:提升性能与效率的关键策略