Mysql Performance Optimization analysis ideas:SQL query optimization and index; database table structure; server-level optimization (System configuration; hardware).
(one, use index)
Indexes are the most common and efficient way to optimize queries, and a data table, especially a table with large capacity, creates a suitable index, which increases the speed of queries.
1. impact of an index on a single table query: You do not need to search the full table for matching terms by row by line, but you can use the index
To find.
- The impact of indexes on multiple table queries.
as mentioned above ,MySQL uses the index to speed up the search for the row that matches the condition in the WHERE clause, or
The search for rows that match rows in other tables is accelerated when the connection is performed.
Determine which type of index to create based on the SQL query statement and how to optimize the query
Select an indexed column:
A. in the performance optimization process, choosing which column to create an index on is one of the most important steps. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause.
B. Consider the distribution of the values in the column, and the larger the cardinality of the indexed columns, the better the index works.
C. using a short index, if you index a string column, you should specify a prefix length, which can save a lot of index space and improve query speed.
D. do not over-index , just keep the desired index. First, the index file takes up disk space. Second, the index file accelerates the retrieval, but increases the time to insert and delete, as well as update the values in the indexed column (that is, the performance of the write operation is reduced), because the write operation involves not only the data rows but also the indexes. The more indexes a table has, the greater the average performance degradation of the write operations .
(Ii. problems with data types)
1 . Make your data as small as possible
one of the most basic optimizations is to make your data ( and index ) On the disk ( and in Memory ) occupy as small a space as possible.
You can use the following techniques to make the table more performance and minimize storage space:
L
A. use the most effective ( minimal ) type possible. MySQL has a lot of savings in disk space and memory specialization
Type.
L
B. if you can make the table smaller, use a smaller integer type. For example, Mediumint is often better than INT
Some.
L
C. if possible, the declaration is listed as not NULL. It makes everything faster and you save one for each column. Note
If you really need it in your application , NULL, you should undoubtedly use it, just to avoid
The default is to have it on all columns.
2 , use fixed length column, do not use variable length column
This guideline is particularly important for tables that are frequently modified and thus prone to fragmentation. For example, you should select CHAR
column without selecting VARCHAR column. The tradeoff is that when using fixed-length columns, the table occupies more space, but if
Being able to afford this space, using a fixed-length row is much faster than using a variable-length line.
3 . Define column as not NULL
This process is faster and requires less space. And sometimes it simplifies the query because there is no need to check if there is a special
example NULL.
4 . Consider using the ENUM column
If you have a column that contains only a limited number of specific values, you should consider converting it to ENUM column. ENUM
The values of the columns can be processed more quickly because they are internally represented numerically.
(Blob type and text type
Blob is a binary large object that can hold a variable amount of data. There are 4 blob type: Tinyblob blob mediumblob and longblob
there 4 text tinytext , text , mediumtext longtext 4 blob
The BLOB column is considered a binary string ( a byte string ). the TEXT column is treated as a non-binary string ( character string ). BLOB columns do not have a character set, and sort and compare numeric values based on column-valued bytes. the TEXT column has a character set, and the values are sorted and compared according to the collation rules of the character set.
)
5, using the Analyse process check table column SELECT * from student PROCEDURE analyse (); A column in the corresponding output is a recommendation about the best column type for each column in the table.
(second, optimization of SQL query)
1. use the EXPLAIN statement to check the SQL statement EXPLAIN SELECT student.name from student, Pet WHERE Student.name=pet.owner;
with the help of EXPLAIN, you can tell when you have to index the table to get a faster SELECT that uses the index to find records .
(iii)
This section describes the optimization techniques for data tables, the main content is to select the type of table, open as few tables as possible,
The relationship between locking the table and the query speed and how to optimize the table to improve the query speed.
(Four, server-level optimization)
Some server parameters are directly applicable to query processing and can be opened. Some hardware configuration problems directly affect query processing speed, they should be adjusted.
Hardware problem: Install more memory in the machine.
MySQL performance optimization-Self-summary