Techniques and optimization of SQL statements

Using regular expressions in SQL

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

Using REGEXP in SQL statements has a greater impact on performance.

Second, use the rand () function to obtain random data

Rand (); random number function

1. Random sort mysql> Select * from Hello ordering by rand ();

2. Random column Extraction 3 mysql> select * FROM Hello ORDER by rand () limit 3;

Third, use the GROUP by group statement followed by the WITH rollup keyword, you can count more information. (with rollup cannot be used again)

Four, foreign key

A normal column in table B1 is the primary key of another table B2, and if a record in the B2 table deletes the column associated with that primary key value in the table B1 should also be deleted.

The use of foreign keys is not recommended and should be noted when used.

The InnoDB storage engine in MySQL supports foreign keys.

Five, use? % View Help Manual

? Create,? opti%, huh? Contents, huh? Functions

MySQL optimization

How do SQL statements optimize

1. Query database execution of various SQL type statements.

Show [Session | global] Status: Understanding how frequently SQL is executed in the database

Session: Represents the frequency of SQL execution for queries since the current connection was established

Global: Represents the frequency of execution of various SQL since the database was started

Generally we mainly query this information:

Com_xxx: Represents the number of executions per XXX statement, which allows you to view a record of the number of SQL statement executions for any storage engine in MySQL.

Show GLOBAL status like "com_select%"; To view the number of executions of a SELECT statement in SQL
Show GLOBAL status like "com_insert%"; To see how many INSERT statements are executed in SQL
Show GLOBAL status like "com_update%"; To view the number of executions of an UPDATE statement in SQL
Show GLOBAL status like "com_delete%";//view the number of executions of a DELETE statement in SQL

For a storage engine of InnoDB: The number of records queried in this way is the number of rows that calculate your data for each SQL operation. (For example: delete from Hello where ID <=10 the statement deletes 10 records, then innodb_rows_deleted=10)

Mysql> Show status like ' connections '; View the amount of MySQL connection (regardless of whether a connection was successful)

Mysql> Show status like ' uptime '; View MySQL working time unit seconds