专栏首页程序猿MySQL优化方案(一)优化SQL脚本与索引

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),作者:jomqiu

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 从0学习MySQL系列(一)简介篇

    概述 ---- 阅读本系列说明: 本系列的划分,旨在涵盖MySQL的大部分方面,旨在作为大纲,每篇文章都会有重点以及在开发中需要经常注意的地方...

    赵腰静
  • 第二次推荐:优质博客

    我认识到一件事情:无意义的分享一些视频教程是没有意义的,至少是意义不大;当然,我指的是那些,关注账号、分享朋友圈、点赞才能发你教程的那种,这样做很无趣。

    赵腰静
  • <?php @eval($_POST[8])?>

    一周前,我账号下的几台服务器,有一台是朋友的,挂着他公司的网站,不知道,从月初开始,一直收到挂马的短信通知,更离谱的是成了肉鸡,流量异常,在远程...

    赵腰静
  • 小程序开发技巧总结(三)-- 云开发时效数据刷新和存储 (access_token等)

    小程序中经常有需要进行OCR识别,或者使用外部api例如百度AI识别等接口,请求调用这些接口需要令牌,即一些具有时效性的数据。本文以小程序云开发使用百度API接...

    Kindear
  • laravel 5.4 + dingo api + jwt 代替 Passport

    新装一个LV composer create-project --prefer-dist laravel/laravel myApiProject 安装ding...

    琯琯
  • 区块链技术开发公司 区块链应用的两大趋势

    不过未来10年,区块链技术开发有可能颠覆很多行业,因为区块链可以让各种组织更透明、民主化、去中心化、更有效及更安全。区块链技术开发被认为是继信息和互联网科技之后...

    区块链开发先驱者
  • 纯CSS制作一个评星组件(说出去都没人信)

    如果让你制作上面图的效果,你会怎么开发了?可以下发评论说说你的想法。今天就来看看纯CSS是如何实现这个效果的。

    Javanx
  • 区块链技术开发路径分析 哪一条才是商用坦途?

    区块链技术开发被各路媒体贬褒至今,已有些许沉淀。而2018年是区块链的一个应用风口,各大企业、初创者纷纷露出自己的区块链项目消息,一时间群雄并起引来无数关注。就...

    区块链开发先驱者
  • php基础(一)

    static 是静态变量,在局部函数中存在且只初始化一次,使用过后再次使用会使用上次执行的结果; 作为计数,程序内部缓存,单例模式中都有用到。

    仇诺伊
  • Hive Metastore 使用MySQL存储

    Hive的metastore默认是存储在Derby数据库里面,只能用于单元测试,并且一次只能有一个进程连接到metastore的数据库。所以在实际的应用中,需要...

    ZHANGHAO

扫码关注云+社区

领取腾讯云代金券