Top Ten Optimization Methods for MySQL

1. Select the most applicable field attribute

Set the width of the field in the table as small as possible: char The upper limit of varchar is 255 bytes (fixed space occupied), the upper limit of varchar is 65535 bytes (actual occupied space), and the upper limit of text is 65535. char is more efficient than varchar.

Try to set the field to NOT NULL. When executing the query, the database does not need to compare NULL values.

2. Use joins (JOIN) instead of sub-queries (Sub-Queries)

The reason why joins (JOIN) are more efficient is because MySQL does not need Create a temporary table in memory to complete this logical query that requires two steps (joint query conditions plus indexes are faster).

3. Use UNION to replace the manually created temporary table

A query that combines two or more SELECT queries that require the use of temporary tables. middle.

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

4. Transaction

Although we You can use sub-queries (Sub-Queries), connections (JOIN) and unions (UNION) to create a variety of queries, but not all database operations can be completed with only one or a few SQL statements. More often, a series of statements are needed to complete a certain kind of work.

The effect is: Either every statement in the statement block operates successfully, or all of them fail. In other words, the consistency and integrity of the data in the database can be maintained. Things start with the BEGIN keyword and end with the COMMIT keyword. If a SQL operation fails during this period, the ROLLBACK command can restore the database to the state before BEGIN started.

5. Locking tables

Although transactions are a very good way to maintain database integrity, they sometimes affect database performance because of their exclusivity. , especially in large application systems. Since the database will be locked during the execution of the transaction, other user requests can only wait until the transaction ends.

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

Here, we use a SELECT statement to retrieve the initial data, through some calculations, and use an UPDATE statement to update the new values ​​into the table. The LOCK TABLE statement containing the WRITE keyword ensures that there will be no other access to the inventory to insert, update, or delete before the UNLOCK TABLES command is executed.

6. Using foreign keys

The method of locking the table can maintain the integrity of the data, but it cannot guarantee the relevance of the data. At this time we can use foreign keys. For example, a foreign key can ensure that each sales record points to an existing customer. Here, the foreign key can map the CustomerID in the customerinfo table to the CustomerID in the salesinfo table. Any record without a valid CustomerID will not be updated or inserted into 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;

Note the parameter "ON DELETE CASCADE" in the example. This parameter ensures that when a customer record in the customerinfo table is deleted, all records related to the customer in the salesinfo table will also be automatically deleted. If you want to use foreign keys in MySQL, be sure to remember to define the table type as a transaction-safe InnoDB type when creating the table. This type is not the default type for MySQL tables. It is defined by adding TYPE=INNODB to the CREATE TABLE statement.

7. Using indexes

When the query statement contains commands such as MAX (), MIN () and ORDERBY, the performance improvement is more obvious.

Indexes should be built on fields that will be used for JOIN, WHERE judgment and ORDER BY sorting. Try not to index a field in the database that contains a large number of duplicate values. For an ENUM type field, it is very possible to have a large number of duplicate values, such as the "province".. field in customerinfo. Building an index on such a field will not be helpful; on the contrary, it is possible Reduce database performance.

The only task of an ordinary index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, indexes should only be created for those data columns that appear most frequently in query conditions (WHEREcolumn=) or sorting conditions (ORDERBYcolumn).

The benefits of the unique index: First, MySQL simplifies the management of this index, and the index becomes more efficient; second, MySQL will automatically check the new records when new records are inserted into the data table. Whether the value of this field of the record has already appeared in this field of a record; if so, MySQL will refuse to insert the new record. In other words, a unique index can ensure the uniqueness of data records. In many cases, the purpose of creating a unique index is not to improve access speed, but just to avoid data duplication.

8. Optimized query statements

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;

Avoid letting MySQL perform automatic type conversion in the query, because the conversion process will also make the index ineffective.

9. Index failure

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教程》

The above is the detailed content of Top Ten Optimization Methods for MySQL. For more information, please follow other related articles on the PHP Chinese website!