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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 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
相关文章
|
8天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
7天前
|
存储 NoSQL 分布式数据库
微服务架构下的数据库设计与优化策略####
本文深入探讨了在微服务架构下,如何进行高效的数据库设计与优化,以确保系统的可扩展性、低延迟与高并发处理能力。不同于传统单一数据库模式,微服务架构要求更细粒度的服务划分,这对数据库设计提出了新的挑战。本文将从数据库分片、复制、事务管理及性能调优等方面阐述最佳实践,旨在为开发者提供一套系统性的解决方案框架。 ####
|
8天前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
27 4
|
10天前
|
SQL druid 数据库
如何进行数据库连接池的参数优化?
数据库连接池参数优化包括:1) 确定合适的初始连接数,考虑数据库规模和应用需求;2) 调整最大连接数,依据并发量和资源状况;3) 设置最小空闲连接数,平衡资源利用和响应速度;4) 优化连接超时时间,确保系统响应和资源利用合理;5) 配置连接有效性检测,定期检查连接状态;6) 调整空闲连接回收时间,适应访问模式并配合数据库超时设置。
|
12天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
39 3
|
18天前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
26 7
|
14天前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
27 1
|
18天前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
17 5
|
15天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
38 1
|
17天前
|
XML Java 数据库连接
如何使用HikariCP连接池来优化数据库连接管理
在Java应用中,高效管理数据库连接是提升性能的关键。本文介绍了如何使用HikariCP连接池来优化数据库连接管理。通过引入依赖、配置参数和获取连接,你可以显著提高系统的响应速度和吞吐量。 示例代码展示了从配置到使用的完整流程,帮助你轻松上手。
61 3