Summary of mysql database optimization operations

This article mainly introduces you to the common optimization operations of mysql database. The article summarizes my daily experience in developing and using mysql database, including Index index, using less SELECT*, EXPLAIN SELECT and turning on query cache. I believe it will be of certain reference value to everyone. Friends who need it can take a look below.

Preface

For a data-centric application, the quality of the database directly affects the performance of the program, so database performance is crucial important. Therefore, everyone must understand the optimization operations of mysql database. This article mainly summarizes the common optimization operations in mysql database. I won’t go into details below, let’s take a look at the detailed introduction.

1. Index index

Put Index first. Needless to say, we have been using this optimization method quietly, then It is the primary key index. Sometimes we may not care. If a suitable index is defined, the database query performance (speed) will be improved several times or even dozens of times.

Normal index

The function is to improve the query speed.

Create table, create index

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
index [索引名] (column_name)
);

Create index

CREATE INDEX index_name ON tab_name (column_name)

Delete index

DROP INDEX index_name FROM tab_name

View index

SHOW index FROM tab_name

Primary key index

The role is to speed up queries and unique constraints

Create tables and create indexes

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
PRIMARY KEY(column_name)
);

Create indexes

ALTER TABLE tab_name ADD PRIMARY KEY(column_name)

Delete indexes

ALTER TABLE tab_name DROP PRIMAY KEY(column_name)

Unique index

The role is to speed up queries and unique constraints

Create tables and create indexes

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
~
unique [索引名] (column_name)
);

Create indexes

CREATE UNIQUE INDEX index_name ON tab_name (column_name)

Delete index

DROP UNIQUE INDEX index_name FROM tab_name

2. Use less SELECT*

Some people may select whatever they want to query when querying the database. is inappropriate behavior. We should get the data we want to use, not all, because when we select, it will increase the burden on the web server, increase the load of network transmission, and the query speed will naturally decrease.

3. EXPLAIN SELECT

It is estimated that many people have never seen this function, but it is highly recommended to use it. explain shows how mysql uses indexes to handle select statements and join tables. It can help choose better indexes and write more optimized query statements. The main use is to add explain before select.

EXPLAIN SELECT [查找字段名] FROM tab_name ...

4. Turn on query cache

Most MySQL servers have query cache turned on. This is one of the most effective ways to improve performance, and it's handled by the MySQL database engine. When many of the same queries are executed multiple times, the query results will be placed in a cache, so that subsequent identical queries will directly access the cached results without operating the table.

The first step is to set query_cache_type to ON, and then query whether the system variable have_query_cache is available:

show variables like 'have_query_cache'

After that, allocate the memory size to the query cache and control the maximum value of cached query results. Relevant operations are modified in the configuration file.

5. Use NOT NULL

#Many tables contain columns that can be NULL (null value), even if the application does not need to save them The same is true for NULL, because being NULLable is the default property of a column. It is usually best to specify columns as NOT NULL unless you really need to store NULL values.

If the query contains NULL columns, it is more difficult for MySQL to optimize because NULL columns make indexes, index statistics, and value comparisons more complex. Columns that can be NULL use more storage space and require special handling in MySQL. When NULLable columns are indexed, each index record requires an extra byte, which in MyISAM can even cause a fixed-size index (such as an index with only one integer column) to become a variable-size index.

Usually the performance improvement brought by changing the NULL column to NOT NULL is relatively small, so (when tuning) there is no need to first search and modify this situation in the existing schema. Unless you are sure this will cause a problem. However, if you plan to build an index on a column, you should try to avoid designing the column to be NULL. Of course, there are exceptions. For example, it is worth mentioning that InnoDB uses a separate bit to store NULL values, so it has good space efficiency for sparse data. But this does not apply to MyISAM.

6. Selection of storage engine

Regarding how to choose MyISAM and InnoDB, if you need transaction processing or foreign keys, then InnoDB may is a better way. If you need full-text indexing, then MyISAM is usually a good choice because it is built into the system. However, we don't actually test 2 million rows of records very often. So, even if it's a little slower, we can get a full-text index from InnoDB by using Sphinx.

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要

几个小时甚至几天来干这些事，InnoDB只需要几分钟。

您操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM表中会非常快，而在InnoDB表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts语句在MyISAM下会快一些，但是updates在InnoDB 下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM也许会更适合。当然，在大型的环境下使用MyISAM也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

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

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

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

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

The above is the detailed content of Summary of mysql database optimization operations. For more information, please follow other related articles on the PHP Chinese website!