MySQL basic introduction and optimization tips

I. MySQL framework and basic introduction
1. Frame diagram

More details:

2. Storage Engine
Comparison of MyISAM and InnoDB:
myisam:mysql5.1 and previous versions of the default storage engine. Support full-text indexing, compression, table-level locks, etc., but not support transactions, row-level locks, data recovery after crash, etc.
The default storage engine for innodb:mysql5.5 and later. Support for transactions, row-level locks, data recovery, mysql5.6 in InnoDB (1.2) Support full-text indexing.

How to choose: InnoDB is the best choice for the vast majority of users, unless some storage engines meet special needs and users know the storage engine.

two.ways to query performance
1. Configuration file My.cnf (Linux),
Read the general order of the configuration files:
1)/etc/my.cnf
2) datadir/my.cnf
3) ~/.my.cnf
You can learn by command:
Mysqld--verbose--help | Grep-a 1 ' Default options '
Results:
/ETC/MY.CNF/ETC/MYSQL/MY.CNF/USR/LOCAL/MYSQL/ETC/MY.CNF ~/.my.cnf

2. Environment Variables
Read the environment variable value from MY,CNF,
View variable value method: Show variables like "", (Fuzzy matching% when variable name is unclear)
For example

To set the (global) variable method:
Set (global) variable name = variable Value
For example, set the slow query log on
Set global slow_query_log=1;
View variable values after setting
Show variables like "%slow%";

| Slow_query_log | On

Variable list:http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

Some useful and default non-open variables when parsing SQL statements:
1) Slow query log: Switch Slow_query_log, Threshold: Long_query_time (units per second)
2) SQL Profiling tool profile, switch: profiling, History: Profiling_history_size (max 100)
3) record each SQL statement, switch: general_log, log file location: General_log_file
... ...

3. View SQL Service Status
Grammar SHOW [GLOBAL | SESSION] STATUS [like ' pattern ' | WHERE expr] Clears the state FLUSH status;
Clear Table Cache
Reset query Cache;
FLUSH table [table NAME]

Examples of usage
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS like ' handler_read% '; (SHOW SESSION STATUS like ' handler_% ';)
EXPLAIN SELECT ...;

Specific parameters can be consulted:
http://lxneng.iteye.com/blog/451985
Http://hi.baidu.com/thinkinginlamp/item/8d038333c6b0674a3075a1d3

4.Query Profiling ToolsShow Profiles
Open
Set profiling=1;
Set profiling_history_size=50 maximum of 100

Usage:
Show Profiles;
Show profile; Show last query time consumption
Show profile for query ID;//replace ID value from show Profiles table.
Show more content
Show profile Cpu,block io for query 4;

5. Get information about the query plan explain
usage explain [SQL query statement]
For example:

Notice the meaning of each column

three. Data type optimization
1. Select the optimized data type principle
1) Try to use the smallest data type that can store the data correctly. For example: Only save 0-200, with tinyint unsigned better.
2) Select a simple data type. For example, shaping is less costly than word Fu Cao, storing the IP with a datetime instead of a string, using shaping.

2. Data types (mainly cosmetic and string)
     1) Integral type
TINYINT 1 bytes (-128,127) (0,255) Small integer value
SMALLINT 2 bytes (-32 768,32 767) (0,65 535) Large integer value
Mediumint 3 bytes (-8 388 608,8 388 607) (0,16 777 215) Large integer value
int or integer 4 bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value
BIGINT 8 Bytes (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) Maximum integer value

thinking: tinyint (1) and tinyint (2) storage space compared?

     2) string
VARCHAR: Content + length, 0-255 bytes.
Char: When the length is insufficient, the space is top-up, 0-255 bytes.

How to choose:
Select with varchar: The maximum length of the string is much larger than the average length and the column is updated less. But be sure to estimate the length (when sorting).

Text and blobs, big data types.

3) floating point, time, bit, etc.

Four. High Performance index
1. Index Basics
The index results are B + trees.

B-Tree:


B-Tree:


B + Tree:


MYISAM Primary Index (secondary index structure is the same):

INNODB Main index:

InnoDB Secondary Index:


Index Benefits:
1) The index greatly reduces the amount of data that the server needs to scan;
2) indexing can help the server avoid sorting and staging tables;
3) The index can turn random I/O into sequential I/O.

2. High-performance indexing strategy

1) Stand-alone columns
Index is not available by placing the index on one side of the comparison symbol alone.

2) prefix index and index selectivity

3) Appropriate index order

4) Overlay Index
greatly improve performance.

5) Sorting using index scan

Other Strategies:
1) Multi-conditional filtering, try to reuse index, (sex,country,age) have index, now have query condition Sex,country,region,age or sex,country,region,city,age need to build index again?
For example: (gender,name) Gender,gender name
SELECT * from the staff where name is like "123";
SELECT * from the staff where gender in (0,1) and the name like "123";
2) Avoid multiple range queries
3) Delayed correlation
SELECT * FROM Table2 ORDER by Cnt,id limit 100000, 10;

SELECT * FROM table2 join (SELECT ID from table2 ORDER by CNT limit 100000,10) as x using (ID);

Five. Query performance optimization
1) Basis of query execution

2) Limitations of the query optimizer
Union limit
(select First_name,last_name from actor order by last_name) union (select First_name,last_name from Customer order by last _name) Order by last_name limit 20;

(select First_name,last_name from actor order by last_name limit) union (select First_name,last_name from Customer Ord Er by last_name limit, order by last_name limit 20;

Querying and updating on the same table
Update Foo as outer set cnt= (SELECT COUNT (*) from Foo as inner where inner.type=outer.type);

Update foo JOIN (select Type,count (*) as CNT from Foo Group by type) as Der using (type) set foo.cnt=der.cnt;

3) Optimize specific related queries
Optimize correlation:
Explain select * FROM film inner JOIN (Film_actor,actor) on (film.film_id = film_actor.film_id and film_actor.actor_id = a CTOR.ACTOR_ID); Explain select Straight_join * from film inner JOIN (Film_actor,actor) on (film.film_id = film_actor.film_id and Film_acto r.actor_id = actor.actor_id);

Optimize limit
Optimize sql_calc_found_rows
Select Sql_calc_found_rows * from Table2 ORDER by Cnt,id limit 100000, 10;
Select Found_rows ();

Selelt Count (*) from table:

MySQL basic introduction and optimization tips