MySQL Performance Optimization learning note (1) Optimization purpose, direction and database preparation

Objective:

Recently participated in the interview, asked a lot of questions about the optimization of MySQL, the answer is not very good, but also because the original project traffic is not very large, so the MySQL optimization is not too understanding, so take advantage of the weekend, bad to fill.

This article comes from Mu class network SQLERCN teacher said the "performance optimization of the MySQL optimization", according to the content of the teacher said.

First, why need to optimize MySQL database

1. Avoid page access errors
5XX error resulting from database connection timeout
Page cannot be loaded due to slow query
Data cannot be submitted due to blocking
2, increase the stability of the database
Many database problems are caused by inefficient queries

Second, MySQL database needs to optimize what content

Optimized for low-cost, high-performance optimization
Hardware, System configuration, database table structure, SQL and index

III. Presentation Database Preparation

Demo Database address: http://dev.mysql.com/doc/index-other.html

Demonstrates the database installation process.

1, download the demo database, and unzip
[Email protected] ~]# wget http://downloads.mysql.com/docs/sakila-db.zip
[Email protected] ~]# Unzip Sakila-db.zip
2, copy two files to the/usr/bin/directory, the reason is to use the source command to prevent the failure to open the file error.
[Email protected] ~]# cp/root/sakila-db/sakila-schema.sql/usr/bin/
[Email protected] ~]# cp/root/sakila-db/sakila-db.sql/usr/bin/
3, enter the database, first establish the database, and set the database code
mysql> CREATE DATABASE Sakila;
mysql> set names UTF8;
4. Import data Sheets and data
SOURCE Sakila-schema.sql;
SOURCE Sakila-data.sql;

Finally, you can delete the two files that you copied.

MySQL Performance Tuning learning note (1) Optimization purpose, direction, and database preparation