SQL statement Optimization (ii) (53)

Next part.

(4) If it is not the first part of the index column, the following example: visible Although there is a composite index on the money, but because money is not the first column of the index, then in the query this index will not be used by MySQL.

Mysql> Explain select * from Sales2 where Moneys=1 \g
1. Row ***************************
Id:1
Select_type:simple
Table:sales2
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using where
1 row in Set (0.00 sec)

(5) If the like is in%, visible although indexed on name, but because the "%" value of like in the Where condition is first, then MySQL will also take this index.

Mysql> Explain select * from Company2 where name like '%3 ' \g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using where
1 row in Set (0.00 sec)

(6) If the column type is a string, but a numeric constant is assigned to a character column name in the query, it is not used even though there is an index on the name column.

Mysql> Explain select * from Company2 where name=294\g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:all
Possible_keys:ind_company2_name
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using where
1 row in Set (0.00 sec)

and the following SQL statement can use the index correctly

Mysql> Explain select * from Company2 where name= ' 294 ' \g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:ref
Possible_keys:ind_company2_name
Key:ind_company2_name
Key_len:23
Ref:const
Rows:1
Extra:using where
1 row in Set (0.00 sec)

3 Viewing Index usage

If the index is working, the value of Handler_read_key is high, which represents the number of times a row is read by the indexed value.

A high value of handler_read_rnd_next means that the query runs inefficiently and that an index remediation should be established.

Mysql> Show status like ' handler_read% ';
+-----------------------+-------+
| variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 5 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next| 2055 |
+-----------------------+-------+
6 rows in Set (0.00 sec)

Two simple and practical optimization methods

The syntax for the parse table is as follows: (check one or more tables for errors)

Mysql> CHECK TABLE Tbl_name[,tbl_name] ... [option] ... option =
{QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

mysql> check tablesales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text|
+--------------+-------+----------+----------+
| Sakila.sales | Check | Status | OK |
+--------------+-------+----------+----------+
1 row in Set (0.01 sec)

To optimize the syntax format of a table:

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name]

If you have deleted a large part of a table, or if you have made a lot of changes to a table that contains variable-length rows, you need to do regular optimizations. This command merges the space fragments in the table, but this command only works on MyISAM, BDB, and InnoDB tables.

mysql> optimize table sales;
+--------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text|
+--------------+----------+----------+----------+
| Sakila.sales | Optimize | Status | OK |
+--------------+----------+----------+----------+
1 row in Set (0.05 sec)

4 optimization of Common SQL

1 High-Volume insert data

When importing data with the load command, the appropriate settings can increase the speed of the import.

For MyISAM storage engine tables, you can quickly import large amounts of data in the following ways.

ALTER TABLE tbl_name DISABLE KEYS
Loading the data
ALTER TABLE tbl_name ENABLE KEYS

DISABLE keys and enable keys are used to turn on or off updates to non-unique indexes on MyISAM tables, which can increase speed, note that the InnoDB table is not valid.

Do not use open or close MyISAM table non-unique indexes:
mysql> load Data infile '/home/mysql/film_test.txt ' into table Film_test2 fieldsterminated by ",";
Query ok,529056 rows Affected (1 min 55.12 sec)
records:529056 deleted:0 skipped:0 warnings:0

Use to turn MyISAM tables on or off non-unique indexes:
mysql> ALTER TABLE FILM_TEST2 Disablekeys;
Query ok,0 rows Affected (0.0SEC)
mysql> load Data infile '/home/mysql/film_test.txt ' into table film_test2;
Query ok,529056 rows Affected (6.34 sec)
records:529056 deleted:0 skipped:0 warnings:0
mysql> ALTER TABLE FILM_TEST2 Enablekeys;
Query ok,0 rows Affected (12.25SEC)
The above data import of the MyISAM table, but for the InnoDB table does not improve the efficiency of importing data

(1) Optimization for data import of InnoDB type table

Because the InnoDB table is saved in the primary key order, the order in which the imported data primary keys are sorted can effectively improve the efficiency of the imported data.

Using test3.txt text is saved in the Order of table Film_test4 primary key storage
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query OK, 1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
Use test3.txt without any order of text (1.12 times times slower)
mysql> load Data infile '/home/mysql/film_test4.txt ' into table film_test4;
Query OK, 1587168 rows Affected (31.16 sec)
records:1587168 deleted:0 skipped:0 warnings:0

(2) Turn off uniqueness validation to improve efficiency of import

Perform set unique_checks=0 before importing data, turn off uniqueness validation, perform set Unique_checks=1 after import, restore uniqueness, and improve import efficiency.

When Unique_checks=1
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
When unique_checks=0
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (19.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0

(3) Turn off auto-commit to improve import efficiency

The import efficiency can be improved by executing set autocommit=0 before importing data, turning off autocommit transactions, performing set autocommit=1 after import, and resuming autocommit.

When Autocommit=1
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (22.92 sec)
records:1587168 deleted:0 skipped:0 warnings:0
When autocommit=0
mysql> load Data infile '/home/mysql/film_test3.txt ' into table film_test4;
Query ok,1587168 rows Affected (20.87 sec)
records:1587168 deleted:0 skipped:0 warnings:0

2 Optimizing INSERT Statements

Use the INSERT statement of multiple value tables as much as possible, which can greatly reduce the loss of the client's connection to the database and the shutdown.

You can use the insert delayed (execute now) statement to get higher efficiency.

Store index files and data files on separate disks.

You can increase the speed by increasing the value of the bulk_insert_buffer_size variable, but only for the MyISAM table

When loading a table from a file, use load DATA INFILE. This is usually 20 times times faster than using many INSERT statements.

3 Optimizing GROUP BY statements

If the query contains group by but the user wants to avoid the loss of sorting results, you can use ORDER by NULL to suppress the ordering:

ORDER BY NULL is not used as follows to prohibit sorting

Mysql> explain select Id,sum (moneys) from Sales2 GROUP by id\g
1. Row ***************************
Id:1
Select_type:simple
Table:sales2
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using temporary; Using Filesort
1 row in Set (0.00 sec)

The effect of using order by NULL as follows:

Mysql> explain select Id,sum (moneys) from SALES2 Group by ID ORDER by null\g
1. Row ***************************
Id:1
Select_type:simple
Table:sales2
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using Temporary
1 row in Set (0.00 sec)

SQL statement Optimization (ii) (53)