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”);

哪些情况下应该使用索引？

• 当表中的字段唯一约束。
• 表中主键自动建立唯一索印。
• 有需要之间面向查询条件的字段，比如，公司的注册编号。
• 表中含有外键的，建立的与其他表的一定关系。
• 排序的字段。当通过某字段进行排序查询的时候，通过索引访问提高速度。

哪些情况下不适合使用索引？

• 表记录太少。
• 经常要进行增、删、修改的表。
• 经常与主字段一起捆绑查询，但是主字段索引值比较多的字段。