Skills and optimization of SQL statements learned by mysql, and skills of mysqlsql statements

Skills and optimization of SQL statements learned by mysql, and skills of mysqlsql statements
I. use regular expressions in SQL

Select name, email from user where email Regexp "@ 163 [.,] com $ ";

The use of Regexp in SQL statements has a great impact on performance.

2. Use the Rand () function to obtain random data

Rand (); random number Function

1. Randomly sort mysql> select * from hello order by rand ();

2. Randomly select three mysql> select * from hello order by rand () limit 3;

3. Use the group by statement followed by the with rollup keyword to obtain more information. (Order by cannot be used after with rollup)

Iv. Foreign keys

A common column in table B1 is the primary key of another table B2. If a record in Table B2 is deleted, the column associated with the primary key value in Table B1 should also be deleted.

We do not recommend that you use foreign keys.

The Innodb Storage engine in mysql supports foreign keys.

5. Use? % View help manual

? Create ,? Opti % ,? Contents ,? Functions

Mysql Optimization

I. How to optimize SQL statements

1. query the execution of various SQL statements in the database.

Show [session | global] status: shows the execution frequency of various SQL statements in the database.

Session: queries the execution frequency of various SQL statements since the current connection is established.

Global: The frequency of running various SQL statements since the database is started.

Generally, we mainly query this information:

Com_xxx: the number of executions of each xxx statement. In this way, you can view the SQL statement execution records of any mysql storage engine.

Show GLOBAL status like "com_select %"; // view the number of select statement executions in SQL
Show GLOBAL status like "com_insert %"; // view the execution times of the insert statement in SQL
Show GLOBAL status like "com_update %"; // view the number of times the update statement is executed in SQL.
Show GLOBAL status like "com_delete %"; // view the execution times of the delete statement in SQL

If the storage engine is InnoDB, the number of records queried in this method is the number of rows of data in each SQL operation. (For example, if the statement delete from hello where id <= 10 deletes 10 records, Innodb_rows_deleted = 10)

Mysql> show status like 'connections'; check the connection quantity of mysql (whether a connection is successful or not)

Mysql> show status like 'uptime'; views the mysql working time in seconds.

 

2. mysql slow Query

A. View mysql slow query status

Slow_query_log: whether to enable slow query logs. 1 indicates enabled, and 0 indicates disabled.

Log_slow_queries: the log storage path for slow query of MySQL Databases in earlier versions (versions earlier than 5.6. You can leave this parameter unspecified, and the system defaults to a default file host_name-slow.log

Slow_query_log_file: the log storage path for slow query of MySQL databases in the new version (5.6 or later. You can leave this parameter unspecified, and the system defaults to a default file host_name-slow.log

B. Slow query time (in seconds): the SQL statement that runs the select statement for more than 10 seconds is recorded in the slow query log.

3. Locate and analyze SQL statements (either desc or explain)