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)。

  1. select id from t where num=10 or num=20    
  2. 可以这样查询:    
  3. select id from t where num=10    
  4. union all    
  5. 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/artic...

性能方面的优化:
explain执行计划==>https://blog.csdn.net/yhl_jxy/article/deta...
一、分表的分类(单表记录条数达到百万到千万级别时就要使用分表)
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 和子查询。
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!