How does MySQL know what needs to be optimized under CentOS and how to optimize it to achieve the be

Here are the steps to modify:

1. Of course, if the economic capacity allows, adding hardware equipment and increasing the hardware performance is the most direct and effective.

2. Choose the appropriate database storage engine, if you often query and don't insert much, use the myisam storage engine, if you often insert it, then use the innodb engine.

3. Modify the system of mysql to change face, myisam is the most direct key buffer size, sort buffer size, innodb is the most direct innodb buffer pool size, innodb flush log at trx commit, innodb additional mem pool size, adjust these well, then there will be a qualitative leap.

4. Add appropriate indexes and establish appropriate compound indexes, but don't add them randomly, otherwise it will be super slow when inserting.

5. Of course, your business is very fixed, you can use the cache, and there is no need to mention the speed, but the conditions change frequently, so it's better to give up.

6. For the others, turn on the slow log, so that you can catch the statements that should be optimized, and you can directly locate and analyze the efficiency of the query.

7. Use explain and profile more, so that the efficiency of the statement can be seen intuitively.

The above is my own personal experience, and I will revise it slowly. Evaluate it.,It's not easy for me.,Don't you say?。。。

Look at the slow query records of the SQL statement, and then optimize the SQL based on the slow query day.

There are 3 ways to speed up your MySQL server, from least to most efficient:

Replace the problematic hardware. Tune the settings of the MySQL process. Optimize your query.

Replacing problematic hardware is often our first consideration, mainly because the database is resource-intensive. But that's not all there is to it. In fact, you can usually double the speed of your CPU or disk, or you can increase your memory by 4 to 8 times.

The second method is to tune the MySQL server (also known as MysqlD). Tuning this process means allocating memory appropriately and letting Mysqld know what type of load it will be subjected to. Speeding up your disk is not as good as reducing the number of disk visits you need.

Similarly, ensuring that the MySQL process is operating correctly means that it spends more time on service queries than it does on background tasks such as processing temporary disk tables or opening and closing files. Tuning mysqld is the focus of this article.

The best way to do this is to make sure that the query has been optimized. This means that the table is properly indexed and the query is written in a way that takes full advantage of MySQL capabilities. Although this article doesn't cover query tuning (which has been covered in many books), it does configure mysqld to report queries that may need to be tuned.

Although these tasks have been assigned a sequence, it is still important to pay attention to the hardware and mysqld settings to facilitate proper tuning of queries. I've seen fast machines run well-designed queries and fail due to heavy load because mysqld is too busy to serve queries.

Subquery optimization strategy.

For different types of subqueries, the optimizer chooses a different strategy.

1.For the in, =any subquery, the optimizer has the following policy options:

semijoin

materialization

exists

2.For the not in and <>all subqueries, the optimizer has the following policy options:

materialization

exists

3.For derived tables, the optimizer has the following policy options:

derived merge, which merges the derived table into an external query ( Introduced );

Materialize derived tables into internal temporary tables and then use them for external queries.

Note: Subqueries in the update and delete statements cannot use the semijoin or materialization optimization strategies.

Try to minimize its configuration.

The permissions are insufficient, or the directory cannot be written, the space is insufficient, and the quota is used up.

I don't have a GCC bag!

MySQL installation, requires C compiler.

On CentOS, just pack the GCC bag!

Your ** seems to be a little wrong, but, from the main meaning of **, it should be: change the login password of the root user in the command line mode.

However, the complete ** should be like this:

mysqladmin -h localhost -u root -p password 123456

If you are the root user adding a new password, then it should look like this:

mysqladmin -uroot password 123456;

I don't know much, I hope it helps!

The root password of the local host is 123456

In your case, the MySQL service is not started, and the best way is to install it with yum groupinstall MySQL.

Check whether mysql is started, check whether the mysql environment variable is configured correctly, if not, go to the absolute path where mysql is located to execute mysql.

1 All rhe15 refers to Red Hat Enterprise 5 x86 64 is the 64-bit package rpm -ivh

The two are different versions.

Two are not the same thing, install the one below.

It is a centos operating system, you can use yum to install software, it is very convenient, yum search mysql

Then find the yum install mysql. that needs to be installed* That's it.

Then to configure the password: usr bin myadmin -u root password 'your password'

After setting the password: log in again: mysql -u root -p

1. It's the source file, so you're sure you're compiled.

2. Use netstat -ntlp to see if there is 3306 on the starting port

3. Reference.