MySQL Performance tuning considerations and Indexing

mysql performance tuning considerations and Indexing

One: Optimization of the database

1 impact of business needs

For example, posts in the Forum statistics, and real-time updates

from a functional command     select  count* from   can get results if the forum generates thousands of posts per second, we don't use MyISAM The store uses the innodb storage; Even the best devices are not likely to be queried very quickly.

note: in Where count * myisam ratio innodb myisam built-in a counter, count ( * innodb

therefore , when the count(*) is executed on the InnoDB, itis generally accompanied by where, and The where to include index columns other than the primary key.

If you have to implement an update to create a table specifically for this feature, it would be nice to look at the table for the results, and tens of thousands of posts per second would be a hassle, and conversely, but how many people would be concerned about this real-time update, if the real-time update is removed is easy to achieve; It can be refreshed every once in a while. This is an unreasonable business requirement.

2: System architecture and impact of implementation

1) binary multimedia data

mainly include pictures, video, other binary files, if the data in the database space resources consumption is very serious, the other is to consume the host CPU resources, because the database is not to deal with these advantages,

Workaround: You can put these binary multimedia data into a special text file, and then make a connection to the database to the text file, the implementation of the database call multimedia files, there is no need to consume the database space and CPU resources.

2) Super-large text data

If large text data is put into the database, it will also cause the waste of space.

WORKAROUND: You can use a non-relational database for storage

3) Impact of query statements on performance

The performance difference between each SQL statement before and after optimization is also different

in the database management software, the biggest performance bottleneck is the disk io, that is,the data access operations above, and for the same data, when we look at a certain point in different ways, the amount of data required to read the volume may be a big difference, the search consumption of resources is very different

start by writing a script to insert data into the 20000 row

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/98/BD/wKiom1k_4Xvyuz39AAEtFnYqw9A671.png-wh_500x0-wm_ 3-wmp_4-s_1463024725.png "title=" image 1.png "alt=" Wkiom1k_4xvyuz39aaetfnyqw9a671.png-wh_50 "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/98/BD/wKiom1k_4ZHzoSMzAAAMgJ3_Osg051.png-wh_500x0-wm_ 3-wmp_4-s_58498681.png "title=" image 2.png "alt=" Wkiom1k_4zhzosmzaaamgj3_osg051.png-wh_50 "/>

For example , you can use explain to view execution plans when executing SQL statements:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/98/BD/wKioL1k_4cnxkiovAABZJLKSILs550.png-wh_500x0-wm_ 3-wmp_4-s_3276056154.png "title=" image 3.png "alt=" Wkiol1k_4cnxkiovaabzjlksils550.png-wh_50 "/>

use another way to query the Open profiling feature again to see The actual execution plan for SQL

Open feature

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/98/BD/wKiom1k_4d3CIzWNAAARMXU4rY4020.png-wh_500x0-wm_ 3-wmp_4-s_2475442831.png "title=" image 4.png "alt=" Wkiom1k_4d3cizwnaaarmxu4ry4020.png-wh_50 "/>

Start query

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/98/BD/wKioL1k_4fLTucF7AAB1UAJtdF0215.png-wh_500x0-wm_ 3-wmp_4-s_4205556329.png "title=" image 5.png "alt=" Wkiol1k_4fltucf7aab1uajtdf0215.png-wh_50 "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/98/BE/wKioL1k_4nWSnj-BAAA6UnOpfyY455.png-wh_500x0-wm_ 3-wmp_4-s_3287841957.png "title=" image 6.png "alt=" Wkiol1k_4nwsnj-baaa6unopfyy455.png-wh_50 "/>

View The CPU,block, and io usage of the profile on the database:

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/98/BE/wKioL1k_4oiBfpozAABUs1irruk160.png-wh_500x0-wm_ 3-wmp_4-s_1869069810.png "title=" image 7.png "alt=" Wkiol1k_4oibfpozaabus1irruk160.png-wh_50 "/>

4) Database schema(schema) design also has an impact on performance

5) The impact of hardware selection on performance

The database host is where the data is stored, so IO performance must be prioritized, regardless of the factors that the database must take into account, and of course the io -related board

In addition , the CPU processing power can not be ignored, the enterprise must use multi-core, the other memory must be at least 64G

In fact, the optimization of the database is not only from the physical aspects of the configuration, but also including the logic of the number of connections ... , and business needs. In short, system architecture optimization, logical structure simplification, hardware facilities rationalization

Ii. Introduction and creation and use of indexes

What is an index?

indexis tohelp MySQL efficiently obtain data structures, help DBAs quickly locate, in short, the equivalent of a dictionary directory

The type of index has been mentioned in the last chapter three kinds of {b-tree,r-tree,full-tree} types, most commonly B-tree

The main introduction Here is the index structure of B-tree :

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M00/98/BE/wKioL1k_4qGifoL-AAOWM6ruWeM416.png-wh_500x0-wm_ 3-wmp_4-s_1599930196.png "title=" image 8.png "alt=" Wkiol1k_4qgifol-aaowm6ruwem416.png-wh_50 "/>

Here's just the point, light blue. We become disk blocks, we can see each disk block contains several data items, and pointers (yellow) actually the real data on the leaf node, is the bottom layer, while others do not store the data, only the index of the direction of the guide data.

For example: To find , first of all, the disk block 1, loaded into memory, an iooccurs, in memory with a binary lookup determined Lock the p2 pointer of disk block 1 between This is calculated down until the third layer is calculated.

Advantages of the index:

let MySQL run efficiently, can greatly improve The query efficiency of MySQL, data constraints, rapid positioning

The cost of using the index:

1) It needs to be loaded into memory and stored as a file on the hard disk, so increase the cost of the disk

2) write data, need to update the index, the database is a big cost, reduce the table update, add and delete speed

The use of indexes is not recommended:

1) less table records

2) Low selectivity of index, refers to the ratio of non-repeating index to the number of table records, the value range (0-1), the higher the selectivity, the greater the index value

1: Normal index

The most basic index, without any restrictions

CREATE INDEX index_name on tablename(columm1"column2, ..... ")

2: Unique index

similar to a normal index, the value of an indexed column must be unique, but a null value is allowed, which means null, and if it is a combined index, the value of the column must be unique.

CREATE TABLE tablename(ID int not null,username varchar (+) not null,primary key (ID)) c15>;

3: Combined index

to further improve the efficiency of MySQL, you can use the combined index

CREATE INDEX index_name on table_name(column1,column2,column3 );

Such a combination index is more efficient than a single-column index and uses the result of the leftmost prefix. The simple idea is to start with the leftmost combination.

4: Full-Text Indexing

Use only the MyISAM table to index a text field. Fields include char,varchar,text

But remember large data tables, generating full-text indexes is a very time-consuming and hard-disk practice

View Index

Show index from TABLE_NAME

Show keys from table_name

Time to create an index:

Generally need to be indexed in the where and join clauses

Considerations for Using Indexes:

In some cases like needs to be indexed, because in a wildcard % and - start query,mysql Do not use indexes

SELECT * from table-name where name like ' % admin';

Besides, there's no way to do the math on the list.

SELECT * from Users where Year (adddate) <2000;

The operation on each row will cause the index to fail and perform a full table scan

can be modified to select * from Users where adddate<2000-10-4;

Summarize:

the optimization of an index is primarily used in the presence of where and join clauses

The larger the cardinality of the columns in the index, the better the index works

Using a short index, if the string is indexed, you should specify a prefix length, save a lot of index space, improve the speed of the query

This article is from the "Apache" blog, make sure to keep this source http://xiaorenwutest.blog.51cto.com/12754924/1935146

MySQL Performance tuning considerations and Indexing