MySQL optimization three major directions

MySQL optimization three major directions

① optimizes the server kernel where MySQL is located (this optimization is typically done by operations personnel).
② tuning MySQL configuration parameters (my.cnf) This optimization requires a stress test to adjust the parameters.
③ to SQL statements and table optimizations.

MySQL parameter optimization

1:mysql The default maximum number of connections is 100, you can use the following command to view the MySQL client
Mysql> Show variables like ' max_connections ';
2: View the threads that are currently accessing MySQL
Mysql> show Processlist;
3: Set maximum number of connections
Mysql>set globle max_connections = 5000;
Max can set 16384, more than useless
4: View the currently used connections
Mysql>show globle status like ' Max_user_connections '

16 lessons on MySQL statement performance optimization

① optimizing queries for query caching
②explain Our select query (can see the number of rows executed)
③ use limit 1 when only one row of data is used
④ Indexing a search field
⑤ uses a fairly type of column when it is in the join table and indexes it
⑥ never ORDER by RAND ()
⑦ Avoid SELECT *
⑧ always set an ID for each table
⑨ can use enum instead of varchar
⑩ use not NULL as much as possible
? Fixed-length tables are faster
? Vertical split
? Split a hit delete or INSERT statement
? The smaller the column, the quicker it will be.
? Choosing the right Storage engine
? Be careful with "permanent link"

(i) Optimizing queries using query caching
Most MySQL servers have query caching turned on. This is one of the most effective ways to improve performance, and this is handled by the MySQL engine. When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to be manipulated to directly access the cached results.
The main problem here is that this is a very easy thing to ignore for our programmers. Because some of our query statements will let MySQL not use the cache, the example is as follows:
1:select username from user WHERE signup_date >= curdate ()
2:select username from user WHERE signup_date >= ' 2014-06-24 '
The difference between the two SQL statements above is curdate (), and the MySQL query cache does not work for this function. Therefore, SQL functions such as now () and RAND () or whatever, do not turn on the query cache because the return of these functions is variable. So all you need to do is use a variable instead of the MySQL function to turn on the cache.

(ii) Detection of queries using the Explain keyword
Using the Explain keyword allows us to know how MySQL handles SQL statements, which can help us analyze the performance bottlenecks of our query statements or table structures, and the results of the explain query will also tell us how the index primary key is used, how the data table is searched or sorted. .... Wait a minute. The syntax format is: EXPLAIN +select statement;

As we can see, the previous result shows a search of 7883 rows, and the second one searches only 9 and 16 rows of two tables. Looking at the rows column allows us to find potential performance issues.

(iii) Use limit 1 when only one row of data is used
Adding limit 1 can increase performance. Instead of continuing to query for the next qualifying data record, the MySQL database engine stops the search after finding a single piece of data.

(iv) Indexing of search fields
An index is not necessarily a primary key or a unique field, and if there is a field in the table that is often used to search, it needs to be indexed.
The operation of the index is as follows:

1. Create an index
You can create an index when you execute the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone.
1.1> ALTER TABLE
ALTER TABLE is used to create a normal index, a unique index, a primary key index, and a full-text index
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
ALTER TABLE table_name ADD fulltext (column_list);
Where table_name is the name of the table to increase the index name, column_list indicates which column columns are indexed, and the columns are separated by commas using a half-width comma. Index name index_name is optional, and if you do not specify an index name, MySQL will automatically specify the index name based on the first indexed column, and ALTER TABLE allows multiple tables to be changed in a single statement, so you can create multiple indexes at the same time.
1.2> CREATE INDEX
CREATE index adds a normal or unique index and full-text index to a table, but you cannot add a primary key index to a table
CREATE INDEX index_name on table_name (column_list);
CREATE UNIQUE index_name on table_name (column_list);
CREATE Fulltext INDEX_NAME on table_name (column_list);
TABLE_NAME, index_name, and column_list have the same meaning as in the ALTER TABLE statement, and the index name must be specified. In addition, the primary key index cannot be created with the CREATE INDEX statement.
2. Index type
Normal index: For general properties such as name, email, etc.
Unique indexes unique: Similar to normal indexes, different unique indexes require that indexed field values be unique in a table, similar to a primary key index, except that a unique index allows null values. The unique index generally applies to XXX numbers, user accounts, etc. that do not allow duplicate attribute fields.
Primary KEY index: In fact, is the primary key, the general construction of the table is specified, do not need to add additional.
Full-Text Search: Fields that apply only to varchar and text types.
Note: full-text indexes and ordinary indexes are very different, if the establishment of a normal index, generally using like to fuzzy query, only the first part of the query content is valid, that is, only the previous query does not use wildcards valid, if there are wildcard characters before and after, the normal index will not work. For full-text indexing, there is a unique way to match queries, such as when we index the title and content of an article in full-text:
ALTER TABLE article ADD fulltext (' title ', ' content '); The following syntax is needed to retrieve the search:
SELECT from article WHERE MATCH (' title ', ' content ') against (' query string ');
Considerations when using Full-text indexing:
MySQL comes with full-text indexes only for data tables that are MyISAM by the database engine, and if it is a different data engine, the full-text index does not take effect. In addition, MySQL's own full-text index can only be full-text search in English, currently cannot be full-text retrieval of Chinese. We need to use Sphinx (Sphinx)/coreseek technology to deal with Chinese if we need to do full-text retrieval of text data including Chinese. In addition to using MySQL's own full-text index, if the length of the query string is too short, you will not get the desired search results. The default minimum length of words that can be found for a MySQL full-text index is 4 characters. In addition, if the queried string contains a stop word, the stop word is ignored.
3. Composite index
The combined index multiprocessing the column index, which is to specify multiple field properties when the index is established. A bit like a dictionary directory, such as query ' Guo ' this pinyin word, first find the G-letter, and then in the search range of G to query the second letter is the list of U, and finally in the range of U to find the last letter O word. For example, the combination index (A,B,C), ABC is well-ordered, in any section of a, B is a good order, any section B under C is a well-ordered
Composite index of the effective principle is used in order to take effect, if an intermediate index is not used, then the index portion of the preceding breakpoint function, The index after the breakpoint does not work;
causes the breakpoint:
any index in front of it does not participate in the query, and all behind it does not take effect. Any one of the index fields in the front of the
participates in a scope query, and the latter does not take effect. The
Breakpoint is independent of the position of the index Word field in the SQL statement and is only relevant to the existence of the. A good example is found on the Web:
For example:
[SQL]
where a=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点, all play a role;
where A=3 and c=5 ... #这种情况下 B is the breakpoint, A has an effect, C has no effect
where b=3 and c=4 ... #这种情况下a就是断点, the index after a does not work, and the Union index does not have any effect;
where b=45 and A=3 and c=5. .. #这个跟第一个一样, it all works, ABC just use it, not the order of the writing.
(0) Select From MyTable where A=3 and b=5 and c=4;
#abc三个索引都在where条件里面用到了, and they all played a role.
(1) SelectFrom mytable where c=4 and b=6 and a=3;
#这条语句为了说明 The combined index has nothing to do with the position in SQL, where the condition order is automatically optimized by MySQL before query, the effect is the same as the previous sentence
(2) SelectFrom MyTable where A=3 and c=7;
#a用到索引, B is useless, so C is not used for the index effect
(3) SelectFrom mytable where A=3 and b>7 and c=3;
#a用到了, B is also used, C is not used, this place B is the range value, but also the breakpoint, but the use of the index itself
(4) SelectFrom MyTable where b=3 and c=4;
#因为a索引没有使用, so BC doesn't have an index effect here.
(5) SelectFrom mytable where a>4 and b=7 and c=9;
#a用到了 B is not used, C is not used
(6) SelectFrom MyTable where a=3 order by B;
#a用到了索引, B is also used in the results of the index of the effect, said before, a below any paragraph of the b is a good order
(7) SelectFrom MyTable where a=3 order by C;
#a用到了索引, but this place C did not play the sorting effect, because in the intermittent point, use explain can see Filesort
(8) SelectFrom MyTable where b=3 order by A;
#b没有用到索引, sort a also does not have an index effect

4. View Index
Mysql> Show index from Tblname;
Mysql> show keys from Tblname;

5. Deleting an index
Delete the indexed MySQL format:D ORP index indexname on Tab_name;
Note: The use of indexes is not possible
For normal indexes, when using the like for wildcard fuzzy queries, the index is invalid if wildcards are used.
Suppose the keyword for the query is ' ABC '
SELECT from tab_name WHERE index_column like ' abc% '; #索引是有效的
SELECT from Tab_name WHERE index_column like '%abc '; #索引是无效的
SELECT from tab_name WHERE index_column like '%CBA '; #索引是有效的
SELECT from Tab_name WHERE index_column like '%abc% '; #索引是无效的
When the retrieved field content is large and the contents of the retrieved content are indeterminate, you can change to a full-text index and use a specific retrieval method

(v) Use a fairly typed column in the Join table and index it
If you have many join queries in your program, you should ensure that the fields of join in two tables are indexed. This way MySQL temporal will start the mechanism of optimizing the SQL statement of the join. Note: These fields that are used for join should be of the same type. For example, if you join a DECIMAL field with an INT field, MySQL cannot use their index. For those string types, you also need to have the same character set. (Two tables may not have the same character set)
For example:
Select Company_Name from the users left JOIN companies on (users.state = companies.state) WHERE users.id = "user_id"
The two state fields should be indexed and should be of the same type, with the same character set.

(vi) Remember not to use ORDER by RAND ()
If you really want to disrupt the data rows that you return, there are n ways you can achieve this. This use only degrades the performance of your database exponentially. The problem here is that MySQL will have to execute the rand () function (which consumes CPU time), and this is done for each row of records to be recorded and then sorted. Even if you use limit 1 is useless (because to sort)

(vii) Avoid using SELECT
The more data you read from the database, the slower the query becomes. And, if our database server and Web server are two separate servers, this also increases the load on the network transport. So, we should develop a good habit of taking whatever we need.
Hibernate performance will be poor, it does not , but it will be all the fields of the whole table to find out all
Advantages: Fast Development speed

(eight) always set an ID primary key for each table
We should set an ID for each table in the database as its primary key, and the best is an int type (recommended to use unsigned), and set the automatically added Auto_increment flag. Even if we have a field in the users table that has a primary key called "email", let's not make it a primary key. Use the VARCHAR type to degrade performance when the primary key is used. In addition, in our program, we should use the ID of the table to construct our data structure. Also, under the MySQL data engine, there are some operations that need to use primary keys, in which case the performance and settings of the primary key become very important, such as clustering, partitioning ... In this case, there is only one exception, which is the "foreign key" of the "association table", that is, the primary key of the table, which consists of the primary key of several other tables. We call this the "foreign key". For example: There is a "student table" has a student ID, there is a "curriculum" has a course ID, then, "Score table" is the "association table", which is associated with the student table and curriculum, in the score table, student ID and course ID is called "foreign key" it together to form a primary key.

(ix) Use of enums rather than varchar
The ENUM type is very fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance. In this way, using this field to make a list of options becomes quite perfect. If we have a field such as "gender", "Country", "nation", "state" or "department", we know that the values of these fields are limited and fixed, then we should use ENUM instead of VARCHAR.

(10) Do not assign null if possible
If it is not a special case, do not use NULL as much as possible. In MySQL, for the int type, empty is 0, and NULL is a null value. In Oracle, NULL and empty strings are the same. Null also takes up storage space and makes our programs more complex to judge. The reality is very complex and there are still cases where we need to use null values. Here is an excerpt from MySQL's own documentation: "NULL columns require additional space in the row to record whether their values is NULL. For MyISAM tables, each of the NULL column takes one bit extra, rounded up to the nearest byte. "

(11) Fixed-length tables are faster
If all the fields in the table are fixed length, the entire table is considered "static" or "Fixed-length". For example, there are no fields of the following type in the table: Varchar,text,blob. As long as we include one of these fields, the table is not a fixed-length static table, so the MySQL engine will handle it in a different way. Fixed-length tables can improve performance because MySQL searches faster because these fixed lengths are easy to calculate the offset of the next data, so the nature of reading will be fast. And if the field is not fixed, then every time you want to find the next one, you need the program to find the primary key. Also, fixed-length tables are more likely to be cached and rebuilt. However, the only side effect is that a fixed-length field wastes some space, because the field is set to allocate so much space whether we use it or not. Also use trim to remove spaces when removing values

(12) Vertical Division
"Vertical Segmentation" is a method of turning a table in a database into several tables, which reduces the complexity of the table and the number of fields for optimization purposes.

MySQL optimization three major directions