MySQL memo (for your own query)

1.1 Grant authority

GRANT Permission list ON Database name . Table name TO ‘ user name ’@‘ Source address ’ [IDENTIFIED BY ‘ password ’];

Allow users company Query at all terminals hrm All the tables under the database .
GRANT select ON hrm.* TO ‘company’@‘%’ [IDENTIFIED BY ‘123456]’;

Allow users company Database at all terminals hrm Perform all operations
GRANT ALL ON hrm.* TO ‘company’@‘%’ [IDENTIFIED BY ‘123456]’;

Be careful After all authorized operations are completed Refresh permissions are required ！
flush privileges;

1.2 utilize EXPLAIN Perform performance analysis

The main thing is to add keywords in front of the query statement EXPLAIN.
Through the above keywords , We can get the reading order of the table 、 Operation type 、 The index that can be used and the index used in time and other important data , From this, we can see that this article sql Whether the best performance is achieved ！
Here is an example :

Among the results obtained above are 12 A field ：
The more important fields are described below ：

1. id The serial number of the query Represents execution in a query select The order of clauses or operation tables
2. type Query usage type From good to bad system > const > eq_ref > ref > range > index > all
3. possible_keys Index available
4. key Index actually used

Following pair type Further description of each type ：

1、system The system tables There is only one line in the table , The ideal situation that basically won't happen ：
2、const Indicates that it is found through index once . For example, put the primary key in where In the list ,MySQL You can convert the query to a constant .
3、eq_ref Uniqueness index . For each index key , Only one record in the table matches it . Common in primary key or unique index scan
4、ref Non unique index
5、range Retrieve only rows in the given range Generally speaking, it is where of use Greater than less than in between Wait to find multiple conditions
6、index Scan the whole table But this is read from the index
7、all Full table scan Read from the hard disk

Generally speaking, at least range Level ！