MySQL优化方案(一)优化SQL脚本与索引

MySQL的优化方案有哪一些?

本文记录MySQL优化方案 ,梗概如下:

  • 优化SQL
  • 优化索引

(一)优化SQL

1、通过MySQL自有的优化语句

优化SQL语句,通过脚本命令来了解执行率较低的语句,显示一下状态等。

  • SHOW命令

SHOW Status可以了解SQL的执行频率。可以显示日志,显示特定的数据库、表、索引以及进程还有权限表中的信息等等。

介绍一些常见的字段

Innodb_rows_read:Select查询返回的行数。

Innodb_rows_inserted:执行INSERT操作插入的行数。

Innodb_rows_updated:执行UPDATE操作更新的行数。

Innodb_rows_deleted:执行DELETE操作删除的行数。

  • EXPLAIN

通过EXPALIN命令可以分析低效的SQL执行计划。通过这个命令,可以知道MySQL是如何执行Select语句的信息,信息也包括了Select在执行的时候,各个表之间的联系以及是什么样的联系,使用了哪一种index。

比如我们想统计某个公司的电子银行账户(example@example.com)为交物业费所支付的总金额。 需要管理客户表(Customer)、付款表(Payment),并要对付款金额(Amount)字段求和(Sum)。

# explain select sum(amount) from customer  a.payment b where 1=1 and a.customer_id = b.customer_id 
and email = "pay@zhaoyaojing.io"

MySQL 在表中找到所需要的行的方式,也叫做访问类型,有一下几种:

ALL, index, range, ref, eq_ref, const/system, NULL.

从左往右,性能变化是:差—>好.

  • OPTIMIZE

Optimize 语句目前支持MyIASM和BDB表。允许用户恢复空间和合并数据文件碎片。介绍一下我的优化MySQL表和碎片整理以恢复空间的思路。

① 首先确认MySQL数据库中是否存在碎片

我随机找了一个数据库。

我随机找了一个数据库。

查询出来表中没有数据,没有碎片,不是薯片。要是有呢?

②使用优化命令(第一钟方式)
  • Optimize table TableName;
  • Optimize table TableName1,TableName2…;

执行完毕,Msg_text显示

‘numysql.SYS_APP_USER’, ‘optimize’, ‘note’, ‘Table does not support optimize, doing recreate + analyze instead’

原因是我服务器上的的MySQL是InnoDB存储引擎。

Stack Overflow 也有解释:

这里说明一下,Optimize命令可以在MyISAM、InnoDB存储引擎中使用进行优化表的操作。但是两种不同的存储引擎,它的优化方式是不一样的。在MyISAM中,是先分析这张表,然后会整理相关的MySQL datafile,之后回收未使用的空间;在InnoDB中,回收空间是简单通过Alter table进行整理空间。

在优化期间,MySQL会创建一个临时表,优化完成之后会删除原始表,然后会将临时表rename成为原始表。

③使用mysqlcheck命令(第二种方式)
# mysqlcheck -o DatabaseName TableName  -u root -pPassCode
DatabaseName.TableName  OK

mysqlcheck 是Linux中的rompt,-o是代表Optimize。

④优化所有的表
mysqlcheck -o DatabaseName -u root -pPassCode
mysqlcheck -o --all-databases -u root -pPassCode

优化完成之后,在用开始的语句检查

select table_name,
round(data_length/1024/1024) as data_length_mb, 
round(data_free/1024/1024) as data_free_mb 
from information_schema.tables 
where round(data_free/1024/1024) > 500 
order by data_free_mb;

2、通过规范MySQL 脚本语句书写

①避免SELECT *

MySQL 在解析的过程中,会通过查询数据字典将”*”按序转换成所有列名,这会大大的耗费资源和时间。在书写MySQL语句的时候,我们应该需要查询那一列就写那一列。

②GROUP BY 优化

提高GROUP BY的效率,精简语句,将不需的记录在GROUP之前去掉。这样好好学语文分析一下要求的句子句子结构了。

③ORDER BY优化

任何的在ORDER BY语句的非索引项或者是有计算表达都将降低查询速度。

可以rewrite ORDER BY语句使用索引。

绝对避免在ORDER BY中使用表达式。

为所使用的列建立另外一个索引。

④EXISTS 代替IN
⑤varchar/nvarchar 代替 char/nchar

字段存储空间小,可以节省存储空间。

⑥能用DISTINCT就不用GROUP BY
⑦使用!= 或者 < >

尽量避免这样。MySQL会想Select * 那样进行全表扫描遍历。使用> <给定区间较好。

(二)优化索引

MySQL的索引是在存储引擎层面实现的,并不是在服务器层面实现的。所以每一种的存储引擎的索引都是不是完全相同的。

MySQL目前提供了一下4种索引(概念)。
  • B-Tree索引:最常见的搜索引擎。大部分的搜索搜索引擎是支持B-Tree。
  • Hash索引:这个搜索引擎只有Memory引擎支持。
  • R-Tree索引:又称为空间索引。空间索引要是MyISAM的一个特殊搜索引擎。
  • Full-text:全文索引。全文索引也是MyISAM的一个特殊的索引类型。InnoDB 从MySQL 5.6版本开始提供了对全文的支持。

三种引擎对四种索引的支持情况

索引(语法)的分类(大小写混淆仅为可读性好一些,不建议这样做)
  • 普通索引

ALTER TABLE tableName ADD INDEX index_name(“column”);

  • 主键索引

ALTER TABLE tableName ADD PRIMARY KEY index_name(“column”);

  • 唯一索引

ALTER TABLE tableName ADD UNIQUE index_name(“column”);

  • 全文索引

ALTER TABLE tableName ADD FULLTEXT index_name(“column”);

  • 多列索引

ALTER TABLE tableName ADD INDEX index_name(“column1″,”column2”);

哪些情况下应该使用索引?
  • 当表中的字段唯一约束。
  • 表中主键自动建立唯一索印。
  • 有需要之间面向查询条件的字段,比如,公司的注册编号。
  • 表中含有外键的,建立的与其他表的一定关系。
  • 排序的字段。当通过某字段进行排序查询的时候,通过索引访问提高速度。
哪些情况下不适合使用索引?
  • 表记录太少。
  • 经常要进行增、删、修改的表。
  • 经常与主字段一起捆绑查询,但是主字段索引值比较多的字段。

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2018-02-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

803
来自专栏Golang语言社区

48. 访问MySql数据库增删改查和连接池及空字段处理 | 厚土Go学习笔记

和上一节相比,go 语言访问 MySql 数据库可以有更好的写法,今天来讲一下连接池。同时,也演示一下当表字段内容为 NULL 时,go 语言的处理。 首先我们...

3828
来自专栏帘卷西风的专栏

创建角色随机名字(mysql抽取随机记录)和mysql游标的使用

1、现在创建游戏角色的时候,基本上都是支持角色名字随机的,以前此功能在客户端用代码实现,然后向服务器请求并验证,后来发现有时候连续几次都失败,所以改成在服务器...

542
来自专栏杨建荣的学习笔记

几个行列转换的实用小例子(r4笔记第2天)

起行列转换,大家是既熟悉又陌生,在oracle 10g版本之前如果要做行列转换,都基本得使用decode来完成,在11g中情况有了改观,可以直接使用pivot...

1904
来自专栏Laoqi's Linux运维专列

Mysql中MyISAM引擎和InnoDB引擎的比较

结论 如果不清楚自己应该用什么引擎,那么请选择InnoDB,Mysql5.5+的版本默认引擎都是InnoDB,早期的Mysql版本默认的引擎是MyISAM --...

3386
来自专栏杨建荣的学习笔记

MYSQL索引条件下推的简单测试

自MySQL 5.6开始,在索引方面有了一些改进,比如索引条件下推(Index condition pushdown,ICP),严格来说属于优化器层面的改进。 ...

2695
来自专栏古时的风筝

作为开发也要了解的 mysql 优化思路

为了更好的说明,我假想出来了一个业务场景,可能在实际业务中并不存在这样的场景,只为举例说明问题:

3275
来自专栏乐沙弥的世界

SQL 基础-->创建和管理表

(列名 数据类型 [ default 默认值] [ 约束条件] [ , ......] )

331
来自专栏乐沙弥的世界

Oracle 绑定变量窥探

    Bind Peeking是Oracle 9i中引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候,查看一下当前...

873
来自专栏禁心尽力

一次浴火重生的MySQL优化(EXPLAIN命令详解)

一直对SQL优化的技能心存无限的向往,之前面试的时候有很多面试官都会来一句,你会优化吗?我说我不太会,这时可能很多人就会有点儿说法了,比如会说不要使用通配符*...

1795

扫描关注云+社区