How to improve the query speed of mysql

Methods to improve mysql query speed: 1. Select the most applicable field attributes; 2. Use joins (JOIN) instead of subqueries; 3. Use unions (UNION) to replace manually created temporary tables; 4 , add index; 5. Optimize the query and try to avoid full table scan; 6. Try to use table variables instead of temporary tables, etc.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The reason for slow query speed

From a programmer’s perspective

• The query statement is not well written

• The index is not built, the index is built unreasonably or the index is invalid

• There are too many related queries join

From the server's perspective

##Insufficient server disk space
• Server adjustment The setting of optimal configuration parameters is unreasonable

Eight ways to optimize MySQL database

1. Select the most applicable field attributes

MySQL can well support the access of large amounts of data, but generally speaking, the smaller the table in the database, the faster the queries executed on it will be. Therefore, when creating a table,

In order to obtain better performance, we can set the width of the fields in the table as small as possible.

For example, when defining the postal code field, if you set it to CHAR(255), it will obviously add unnecessary space to the database. Even using VARCHAR is redundant, because CHAR(6 ) can complete the task very well. Likewise, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to NOTNULL when possible, so that the database does not need to compare NULL values ​​when executing queries in the future.

For some text fields, such as "province" or "gender", we can define them as ENUM type.

Because in MySQL, the ENUM type is treated as numeric data, and numeric data is processed much faster than text types. In this way, we can improve the performance of the database.

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

MySQL supports SQL subqueries starting from 4.1. This technique allows you to use a SELECT statement to create a single column of query results, and then use this result as a filter condition in another query. For example, if we want to delete customers who do not have any orders in the basic customer information table, we can use a subquery to first retrieve the IDs of all customers who issued orders from the sales information table, and then pass the results to the main query, as shown below :

DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)

Using

subqueries can complete many SQL operations that logically require multiple steps to complete at one time. At the same time, it can also avoid transaction or table locks, and it is also easy to write. However, in some cases, subqueries can be replaced by more efficient joins (JOIN).. For example, suppose we want to retrieve all users who do not have order records, we can use the following query to complete it:

SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

If you use a connection (JOIN)... to complete this query, the speed will be faster a lot of. Especially when there is an index on CustomerID in the salesinfo table, the performance will be better. The query is as follows:

SELECT * FROM customerinfo LEFT JOIN salesinfo 
ON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID ISNULL

Connection (JOIN).. The reason why it is more efficient is that MySQL does not need to be in memory. Creating a temporary table to complete this logical query requires two steps.

3. Use union (UNION) to replace the manually created temporary table

MySQL supports union query starting from version 4.0, it can Combine two or more select queries that require the use of temporary tables into one query. When the client's query session ends, the temporary table will be automatically deleted to ensure that the database is tidy and efficient. When using union to create a query, we only need to use UNION as the keyword to connect multiple select statements. It should be noted that the number of fields in all select statements must be the same. The following example demonstrates a query using UNION.

SELECT Name,Phone FROM client UNION

SELECT Name,BirthDate FROM author UNION

SELECT Name,Supplier FROM product

4、Transaction

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERT INTO salesinfo SET CustomerID=14; 
UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

...

UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

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

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE  customerinfo(CustomerIDINT  NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE  TABLE  salesinfo(SalesIDNT  NOT  NULL,CustomerIDINT  NOT  NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)  REFERENCES  customerinfo(CustomerID)  ON  DELETE    CASCADE)TYPE=INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

28种 SQL 查询语句的优化方法：

1、应尽量避免在 where 子句中使用 != 或者 a8093152e673feb7aba1828c43532094 操作符，否则将引擎放弃使用索引而进行全表扫描。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null;

可以在 num 上设置默认值 0 ，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num = 0;

3、查询语句的查询条件中只有OR关键字，并且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

4、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

5、前导模糊查询不能利用索引(like '%XX'或者like '%XX%')，可以使用索引覆盖避免。

6、in 和 not in 也要慎用，否则会导致全表扫描。如:

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了： 

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择到运行时；它必须在编译时进行选择。然而，如果在编译时简历访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： 

select id from t where num=@num

可以改为强制查询使用索引： 

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100;

应改为：

select id from t where num = 100 * 2;

9、应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name, 1, 3) = ’abc’–name;  //以abc开头的id
 
select id from t where datediff(day,createdate,’2005-11-30′) = 0–’2005-11-30′;  //生成的id

应改为:

select id from t where name like ‘abc%’
 
select id from t where createdate >= ’2005-11-30′ and createdate < ’2005-12-1′;

10、不要在 where 子句中的 “=” 左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。 

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12、很多时候用 exists 代替 in 是一个好的选择： 

select num from a where num in(select num from b);

用下面的语句替换： 

select num from a where exists(select 1 from b where num=a.num);

13、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

16、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。较一次就够了。

17、任何地方都不要使用 select * from t ，用具体的字段列表代替 *，不要返回用不到的任何字段。

18、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

19、避免频繁创建和删除临时表，以减少系统表资源的消耗。

20、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

21、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。

22、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

23、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

24. Before using the cursor-based method or the temporary table method, you should first look for a set-based solution to solve the problem. The set-based method is usually more effective.

25. Like temporary tables, cursors are not unusable. Using FAST_FORWARD cursors with small data sets is often better than other row-by-row processing methods, especially when several tables must be referenced to obtain the required data. Routines that include "totals" in a result set are usually faster than using a cursor. If development time permits, you can try both the cursor-based method and the set-based method to see which method works better.

26. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and set SET NOCOUNT OFF at the end. There is no need to send a DONE_IN_PROC message to the client after each statement of stored procedures and triggers.

27. Try to avoid returning large amounts of data to the client. If the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

28. Try to avoid large transaction operations and improve system concurrency.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to improve the query speed of mysql. For more information, please follow other related articles on the PHP Chinese website!