如何优化MySQL？

如何优化MySQL？针对这个问题，今天小编总结这篇有关MySQL优化的文章，希望帮助更多想学习MySQL优化的同学找到更加简单易行的办法。

1. 选取最适用的字段属性

表中字段的宽度设得尽可能小：char 的上限为 255 字节（固定占用空间），varchar 的上限 65535 字节（实际占用空间），text 的上限为 65535。char 比 varchar 处理效率高。

尽量把字段设置为 NOT NULL，执行查询的时候，数据库不用去比较 NULL 值。

2. 使用连接（JOIN）来代替子查询 (Sub-Queries)

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作（联合查询的条件加索引更快）。

3. 使用联合 (UNION) 来代替手动创建的临时表

把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。

SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author  UNION SELECT Name, Supplier FROM product;

4. 事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条 SQL 语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。

作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以 BEGIN 关键字开始，COMMIT 关键字结束。在这之间的一条 SQL 操作失败，那么，ROLLBACK 命令就可以把数据库恢复到 BEGIN 开始之前的状态。

5. 锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

LOCK TABLE inventory WRITE 
SELECT Quantity FROM inventory 
WHEREItem='book'; 
... 
UPDATE inventory SET Quantity=11 
WHEREItem='book'; 
UNLOCK TABLES

这里，我们用一个 SELECT 语句取出初始数据，通过一些计算，用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前，不会有其它的访问来对 inventory 进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把 customerinfo 表中的 CustomerID 映射到 salesinfo 表中 CustomerID，任何一条没有合法 CustomerID 的记录都不会被更新或插入到 salesinfo 中。

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

注意例子中的参数 “ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候，salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表 InnoDB 类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。

7. 使用索引

查询语句当中包含有 MAX (), MIN () 和 ORDERBY 这些命令的时候，性能提高更为明显。

索引应建立在那些将用于 JOIN, WHERE 判断和 ORDER BY 排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个 ENUM 类型的字段来说，出现大量重复值是很有可能的情况，例如 customerinfo 中的 “province”.. 字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。

普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。

唯一索引的好处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL 会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

8. 优化的查询语句

SELECT FROM order WHERE YEAR(OrderDate)<2001; 
SELECT FROM order WHERE OrderDate<"2001-01-01";
SELECT FROM inventory WHERE Amount/7<24; 
SELECT FROM inventory WHERE Amount<24*7;

避免在查询中让 MySQL 进行自动类型转换，因为转换过程也会使索引变得不起作用。

9. 索引失效情况

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

当全表扫描速度比索引速度快时，mysql 会使用全表扫描，此时索引失效。

应尽量避免在 where 子句中使用 or,and,in,not in 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，合理使用 union all（允许重复的值，请使用 UNION ALL）。

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10    
union all    
select id from t where num=20

10. 引擎的选取

MyISAM 索引文件在数据库中存放的对应表的磁盘文件有.frm,.MYD,*.MYI 结尾的三个文件：

frm 文件是存放的表结构，表的定义信息；

MYD 文件是存放着表中的数据；

MYI 文件存放着表的索引信息；

InnoDB 存储引擎在磁盘中存放的对应的表的磁盘文件有.frm,.ibd 这两个文件；

frm 文件是存放表结构，表的定义信息；

ibd 文件是存放 表中的数据、索引信息；

详细出处参考：https://blog.csdn.net/jinxingfeng_cn/article/details/16878355

性能方面的优化：

explain 执行计划 ==>https://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

关于MySQL优化就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果喜欢这篇文章，不如把它分享出去让更多的人看到。