Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about optimizing mysql performance and indexing. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic of optimizing mysql performance and indexing. I hope we can bring you some practical help.

One: the optimization of database

1 the impact of business demand

For example, the statistics of posts in the forum are updated in real time.

Functionally, you can get the results by ordering the select count (*) from table name. If the forum generates thousands of posts per second, we use innodb instead of myisam storage; no matter how good the device is, it is impossible to query it quickly.

Note: myisam is much faster than innodb in the use of where and count (*); because myisam has a built-in counter, count (*) can be read directly from the counter, while innodb scans the whole table.

So the execution of count (*) on innodb is generally accompanied by where, and the where contains index columns other than the primary key.

If you have to implement updates, create a table specifically for this function, and if you want to query the results, just check this table; at that time, tens of thousands of posts generated per second will also be a hassle. On the other hand, how many people will pay attention to this real-time update, it is easy to remove the real-time update; by creating statistical tables, you can refresh it at regular intervals. This is an unreasonable business requirement.

2: the impact of system architecture and implementation

1) binary multimedia data

It mainly includes pictures, videos and other binary files. If you put it in the database, the consumption of data space resources is very serious, and the other is to consume the host's cpu resources, because the database does not have the advantage of dealing with these.

Solution: you can put these binary multimedia data into a special text file, and then make a connection to the database to point to the text file, so that the database can call the multimedia file without consuming database space and cpu resources.

2) very large text data

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

Solution: you can use a non-relational database for storage

3) the impact of query statements on performance

The performance difference of each sql statement before and after optimization is also different.

In database management software, the biggest performance bottleneck lies in the disk io, that is, the access operation of data. For the same data, when we find a certain point of content in different ways, the amount of data we need to read may be very different, and the resources consumed by search may also be very different.

First, write a script to insert 20000 rows of data.

For example, when executing a sql statement, you can use explain to view the execution plan:

Use other ways to query the open profiling function again to see the actual execution plan of sql

Open function

Start query

View the cpu,block of profile to the database and the usage of io:

4) the schema (schema) design of the database also has an impact on performance

5) the influence of hardware selection on performance

Database host is the place where data is stored, so io performance must be given priority. No matter what database it is, it must be considered, of course, the board related to io.

In addition, the processing power of cpu can not be ignored. Multi-cores must be used in enterprises, and the memory must be large at least 64GB.

In fact, the optimization of the database not only improves the configuration from the physical aspects, but also includes logicalization such as the number of connections. And business needs. In short, the system architecture is optimized, the logical structure is refined and simplified, and the hardware facilities are rational.

Introduction, creation and use of the index

What is an index?

Index (index) is to help mysql efficiently obtain data structures and help dba locate quickly, which is simply equivalent to a directory in a dictionary.

The type of index mentioned in the previous chapter, there are three types of {B-Tree, R-Tree, Full-Tree}, the most commonly used is B-Tree

Here we mainly introduce the index structure of B-Tree:

As shown in the figure: here we only talk about the key points, light blue, we become disk blocks, we can see that each disk block contains several data items, and the pointer (× ×) actually the real data on the leaf node, that is, the lowest layer, while the others do not store data, only indexes that guide the direction of the data.

For example, to find 29, first load disk block 1 into memory, io occurs, use binary search in memory to determine that 29 is between 17 and 35, lock the p2 pointer of disk block 1, and then calculate until the third layer calculates.

Advantages of the index:

The efficient operation of mysql can greatly improve the query efficiency, data constraints and fast positioning of mysql.

The cost of using the index:

1) it needs to be loaded into memory and stored on the hard disk in the form of files, so the overhead of the disk is increased.

2) to write data, the index needs to be updated, which is a great expense to the database and reduces the speed of table updates, additions and deletions.

The use of indexes is not recommended:

1) there are few table records

2) the selectivity of the index is lower, which refers to the ratio of the non-repeated index to the number of table records, and the value range is (0-1). The higher the selectivity, the greater the index value.

1: general index

The most basic index, without any restrictions.

Create index index_name on tablename (columm1 [column2,. ])

2: unique index

Similar to a normal index, except that the value of the index column must be unique, but null values are allowed, which refers to null, and in the case of a combined index, the value of the column must be unique.

Create table tablename (id int not null,username varchar 16) not null,primary key (id))

3: composite index

To further improve the efficiency of mysql, you can use composite indexes

Create index index_name on table_name (column1,column2,column3)

Such a combined index is more efficient than a single-column index and uses the result of the leftmost prefix. The simple understanding is to start from the far left.

4: full-text index

Used only for myisam tables to index text fields. Fields include char, varchar, text

However, keep in mind that large-capacity data tables and generating full-text indexes are very time-consuming and hard disk-consuming.

View Index

Show index from table_name

Show keys from table_name

Time to create the index:

Generally, indexes need to be established in where and join clauses

Considerations for using the index:

In some cases, like needs to build an index, because mysql does not use an index when querying at the beginning of a wildcard% and-

Select * from table-name where name like'% admin'

In addition, the operation can no longer be performed on the column.

Select * from users where YEAR (adddate)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.