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

I. MySQL hierarchical architecture

1. Link layer

Deal with network links, link network authentication.

-View link permissions

Click (here) to collapse or open

Mysql > select user,host from mysql.user\ Gbomber 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 3Current database: * NONE * * 1. Row * * user: mysql.sessionhost: localhost** 2.row * * user: mysql.syshost: localhost** 3. Row * * user: roothost: localhost3 rows in set (0.00 sec) ERROR:No query specifiedmysql > grant all on *. * to root@'%' identified by 'root' Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > flush privilege;- View connections

Click (here) to collapse or open

Mysql > show processlist

+-+ +

| | Id | User | Host | db | Command | Time | State | Info |

+-+ +

| | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | |

+-kill session

Kill 3

Kill query 4: kill the SQL that the session is executing without closing the session.

-connection pool

(1)。 If mysql and client connections are created and destroyed frequently in the program, the overhead is very high.

(2)。 To reduce the overhead of related connection creation, deploy a connection pool at the application layer.

(3)。 A certain connection is kept in the connection pool. If the application wants to request a connection, it no longer needs to request a link from the mysql, but directly requests a link from the connection pool, which reduces the overhead of frequently creating links between the application and the mysqld.

-Thread pool

(1)。 For each connection, a thread is assigned to provide services to the link, and after the link is revoked, the thread is revoked, which will consume certain system resources in the process of frequent creation and destruction of the connection.

(2)。 In order to avoid this situation, threads within the thread pool can be shared, and if the connection is destroyed, the thread will not be destroyed and can continue to serve the next connection.

(3)。 The Community Edition does not have the feature of thread pool.

2. SQL layer

SQL query parsing, analysis, optimization, caching and all built-in functions, all storage engine functions are implemented at this layer, such as stored procedures.

-query cache

Query_cache_size 1048576

Query_cache_type OFF

(1)。 If the sql previously executed by mysql, the result is returned directly from the query cache

(2)。 Limitations are relatively large, any query results change, need to be updated, but also hold the global lock, the lock strength is very heavy, the efficiency is very low.

Usage scenario: if the SQL statement and result set of the query are relatively fixed, you can consider turning on the query cache.

-modify system parameters

Show variables like 'log%'

(1)。 Read-only parameters: modify after closing the database, take effect after restarting the database, and cannot be modified online

Mysql > set global query_cache_type=on

ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

(2)。 Online parameters: you can directly open the database and modify it, and it can take effect without restarting the database.

(3)。 Global parameters: after modification, all new sessions will take effect

Show global variable

SET GLOBAL var_name = value

(4)。 Session parameters: valid only in the current session

Show session variable

SET SESSION var_name = value

-SQL execution process

(1) .SQL parsing (converting sql text to sql parsing tree within the database)

(2) .SQL optimization rewriting

(3) the execution plan of SQL is established.

(4) .SQL execution

(5) binlog

3. Storage engine layer

MySQL's storage engine is at the storage engine layer and is responsible for storing data. It is because of the existence of layering that MySQL itself has some limitations.

The default storage engine prior to the mysql5.5 version is MyISAM

View Storage Engin

Show plugins

Choose a storage engine when building a table

Create table T1 (id int) ENGINE=myisam

Show create table T1\ G

-Innodb MyISAM distinction

-innodb table structure design

one-for-one

One to many

Many to many

The use of foreign keys is not recommended, because when inserting data in large quantities, foreign key constraints are checked every time you insert, resulting in a large consumption of performance. Although not recommended, dependencies still exist.

-innodb and myisam read and write performance testing

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.