Solutions for mysql index optimization

mysql database optimization methods?

We all know that the development of server databases are generally programmed through the java or PHP language to achieve, and in order to improve the speed and efficiency of our database operation, database optimization has become the focus of our daily work, today, Sandy River IT training to understand mysql server database optimization methods.

Why do we need to understand the index

Real Case

Case 2: Nearly online application of the database frequently appear multiple slow sql risk tips, and since the work, little is known about database optimization. For example, a user data page needs to perform many database queries, performance is very slow, by increasing the timeout time is barely accessible, but performance needs to be optimized.

Benefits of indexing

A suitable index can greatly reduce the amount of data scanned by the mysql server, avoid in-memory sorting and temporary tables, and improve the query performance of the application.

Types of indexes

There are multiple index types in mysql data, primarykey, unique, normal, but the underlying stored data structures are BTREE; some storage engines also provide hash indexes, full-text indexes.

BTREE is a common optimization to face the index structure, are based on BTREE discussion.


Query data simple and violent way is to traverse all the records; if the data is not duplicated, it can be organized into a sorted binary tree, through the binary lookup algorithm to query, greatly improving query performance. And BTREE is a more powerful sorting tree, support for multiple branches, lower height, data insertion, deletion, update faster.

Modern databases have index files and file system file blocks organized as BTREE.

Each node of a btree contains key, data, and pointers to think-only child nodes.

Btree has the concept of degree d>=1. Assuming that the degree of btree is d, each internal node can have n=[d+1, 2d+1) keys, n+1 child node pointers. The large height of the tree is h=Logb[(N+1)/2].

In indexing and file systems, the nodes of a B-TREE are often designed to be close to a memory page size (which is also the size of a disk sector), and the degree of the tree is very large. The number of disk I/Os is thus equal to the height of the tree, h. Assuming b = 100, a tree of one million nodes, h will have only 3 levels. I.e., only 3 disk I/Os to find out, very high performance.

Indexed queries

After the index is created, the appropriate query statement can greatly utilize the advantages of the index.

In addition, because the query optimizer can parse the client’s sql statement, it will adjust the conditional order of the sql query statement to match the appropriate index.

How to carry out mysql optimization_MySQL

How to carry out mysql optimization_MySQL

1, this is relatively simple: there are provided in phpmyadmin

2. first design table when considering what kind of storage engine to choose, myisam does not support transactions, but the query speed is fast, but now generally adopted are InnoDB, can meet the needs of 95% of the project. Specifically you can see the difference between the two.

3. To avoid the operation of the full table query, this online information.

4. where and orderby fields above the index, of course, the index is not more than good, will make the insert and update slower

5, try not to use the null value, because the search engine will spend a lot of time here, and there is a difference in storage, it is recommended to use notnull.

6. varchar and char selection. Can save a lot of space.

7. fields as short as possible, meaningful names, in line with naming conventions.

8. Avoid where conditions to use <> or ! =, because this will cause a full table scan.

9, for sub-distributed tables

10. Build views to replace complex queries in the program.

11. set cache

12. like less efficient, try to use different sql query

13. in the field after where try not to use arithmetic and the use of mysql function, such as LOWER () and so on.

14. between and in, former is better

15, when writing sql statement, think whether to return so much data, limit can be limited

16, delete table use truncate not delete, because delete will delete row by row.

17. If some sql statements are the same only the variables are not the same, you can do sql statement merge, using preprocessing, you can save the compilation time.

18. the use of database connection pools

19, the use of clustering

20, unused resources immediately released

21. split large DELETE or INSERT statements, batch submit SQL statements

22. usleep to avoid server downtime

23, select On top of the slave server, the operation to modify the data on the master server

If there are others, to be added later.

How to use index optimization in mysql

Usually in every book the first few pages are the table of contents and the last few pages will have a keyword index.

For a database the system tables (e.g., sosobjects, etc.) are the table of contents, and the indexes on the labeled fields are like the keyword indexes at the back of the book.

In a database, the difference between a catalog (data dictionary) and an index: catalog vertical, index horizontal.

I. Factors Affecting the Role of Indexes

Distinction (Retrieval Ratio)

The optimizer generates the execution plan based on the statistical information. If the database does not collect statistical information about the indexes, the optimizer has no way to proceed but to follow the steps and execute the query by full table scan. So, newly created indexes need to be re-run with statistics, otherwise the index is invalid.

As an example, there is a table TABLE1, which has a field COL1 that takes the values “1”, “2”, and “3”, and the result of running the statistics is to tell the database TABLE1 data in which the field COL1 of the various values of the proportion. Schematic as follows:



“3 “-22%.

Suppose there is also a field COL2 taking the following values and percentages of data:

“A” – 50%;

“B” – 50%.

Then the query statement 1:

select*fromTABLE1whereCOL1=”1 “andCOL2=”A”,

the database optimizer prefers the index on field COL1 to locate the data in the table, because the result set can be quickly located within a small range of 12% by the index on COL1. In contrast, for query statement 2:

select*fromTABLE1whereCOL1=”2″ andCOL2=”A”,

the database will prioritize the index on COL2 because, for statement 2, the index on COL1 is used to locate the data in the table. index because the index on COL2 has better discrimination for the query condition of statement 2.

From the above, you can see that the database optimizer usually prefers indexes with better discrimination (for query conditions, which may be different).

The data in the database is changing, so statistics collected at one time may be out of date after a while, or even mislead the database optimizer, which will also result in low operational performance. So in addition to the need to run statistics when the index is initially built, you also need to run statistics when the data in the table changes. Lesson learned: when the amount of data in the table changes by 10%, you need to re-run the statistics.

II. Aggregation

Range Scan

Table Size:

Small Table

Medium to Large Table

Extra Large Table

Business Types


Functions and Indexes

Functions, like statements.

Substring(col_name,1,3) vs.Substring(col_name,3,3)


Indexes Overhead

Performance boon

Double-edged sword

Impact of indexes on insert operations (Oracle)

Impact of indexes on insert operations (MySQL)

Comparing indexes vs. proppers for performance

Indexes summary

Using indexes to achieve efficient access to critical data. But it’s important to realize that every index imposes additional overhead on database updates. This means that inefficient indexes can spell disaster for the database.

With databases, we must focus on reading critical data to provide them with the most efficient access path. The basic strategy for this is to build indexes. While indexes provide efficient access, they also introduce additional system overhead. The overhead is divided into disk space overhead and processor overhead. We discuss the processor overhead below. Whenever a record is inserted or deleted in a table, all the indexes of that table must be adjusted accordingly. This adjustment also occurs whenever an update is made to an indexed field. For example, if it takes 100 units of time to insert data into an unindexed table, then each additional index adds 100 to 250 units of time. Interestingly, the overhead of maintaining indexes is roughly equivalent to the overhead associated with simple triggers.

Before building indexes the front line presents some of the most general information available from developWorks, listed because I think it’s generally worthwhile to refer to it:

1. Avoid adding indexes when the query is going to be finished in a reasonable amount of time, because indexes slow down update operations and consume extra space. Sometimes large indexes that cover several queries may also exist.

1. Columns with large bases are good for indexing.

3. Considering the administrative overhead, you should avoid using more than five columns in an index.

4. For multi-column indexes, place the column most referenced in the query at the front of the definition.

6. If the table is read-only and contains a lot of rows, try defining an index that is made to contain all the columns referenced in the query by the INCLUDE clause in CREATEINDEX (columns that are included by the INCLUDE clause are not part of the index, but are only stored as part of the index page to avoid additional data FETCHES).

For data warehouses (query system databases) more indexes can be created (the ratio of indexes to data can be 1:1).

Deciding whether to use indexes can focus on the retrieval ratio. That is, the basis for judging the effectiveness of the index on the use of the key value as the uniqueness of the conditions of the percentage of data retrieved. The lower the percentage, the more effective the index. This assertion is based on a number of assumptions, such as the performance associated with disk access.

It is also important that the physical locations of the records associated with the index key values are adjacent, because the data is manipulated through blocks. After an index is created, if the records pointed to by the index key are scattered throughout the table, even though they make up a small percentage of the table, the performance of the index will be greatly reduced because they are scattered throughout the disk.

It is also worth noting that functions and type conversions can cause indexes to fail.

What are MySQL indexes? How to optimize it?

The creation of MySQL indexes is important for the efficient operation of MySQL, and indexes can greatly increase the speed of MySQL retrieval.

An analogy:

If a properly designed and indexed MySQL is a Lamborghini, then a MySQL that is not designed and indexed is a human tricycle.

Indexes are divided into single-column indexes and combined indexes.

Single-column indexes, in which an index contains only a single column, can have more than one single-column index on a table, but this is not a combined index.

Combined indexes, that is, an index contains multiple columns.