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 条评论
登录 后参与评论

相关文章

来自专栏Kevin-ZhangCG

Oracle学习笔记三

  一个表空间可以与多个数据文件(物理结构)关联一个数据库下可以建立多个表空间,一个表空间可以建立多个用户个用户下可以建立多个表。

744
来自专栏技术碎碎念

sql server 使用函数辅助查询

函数是所有语言系统下都具备的内部数据处理过程,SQL SERVER也同样内置了许多函数。在SQL SERVER中,函数是由一个或多个T-SQL语句组成的子程序。...

3304
来自专栏技术记录

mySQL优化方案

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得...

1868
来自专栏眯眯眼猫头鹰的小树杈

猫头鹰的深夜翻译:如何优化MYSQL查询

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

622
来自专栏PHP技术

MySQL的TRUNCATE使用

TRUNCATE [TABLE] tbl_name TRUNCATE TABLE用于完全清空一个表。从逻辑上说,该语句与用于删除所有行的DELETE语句等同,...

2758
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

3469
来自专栏深度学习之tensorflow实战篇

数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。

SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。 1. 数据查询语言DQL 数据查询语言DQL基本结构...

3469
来自专栏大数据挖掘DT机器学习

50多条实用mysql数据库优化建议

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 缺省情况下建立的索引是非群集索引,但有时它并不是最...

4686
来自专栏PHP技术

MYSQL 优化常用方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更...

3228
来自专栏用户2442861的专栏

游标--数据库

http://blog.csdn.net/liujiahan629629/article/details/18014051

923

扫码关注云+社区