16. mysql partitioning with simple SQL optimization; mysql

16. mysql partition-simple SQL optimization 1

1. use show session status like '% Com _ %'; to view the execution frequency of each currently connected SQL statement: show global status like '% Com _ % '; you can view the SQL execution frequency since the last mysql server was started up till now. 2. explain select * from tmp; mysql> explain select * from zi_emp where tid = 1000/G *************************** 1. row ************************* id: 1 select_type: SIMPLE table: zi_emp type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec) PS: in type, ALL is the most efficient, this means that the full table scan index type is full index scan range is index range scan ref is non-unique index or unique index prefix scan eq_ref unique index scan const/system single table only one match, for example, if the user table NULL does not access the table or index, for example, select 1 = 1; 3. haha, I found a good thing: explain extended select * from zi_emp; check what the mysql Optimizer did before executing the SQL statement show warnings; mysql> show warnings/G ***************************** 1. row *************************** Level: Note Code: 1003 Message: select 'bin '. 'Zi _ emp '. 'tid' AS 'tid', 'bin '. 'Zi _ emp '. 'tname' AS 'tname' from 'bin '. 'Zi _ emp' 1 row in set (0.00 sec) PS: Yes, you see a complete SQL statement, that is to say, this may be the best SQL statement given by msyql. you can learn and reuse it. 4. explain partitions select * from zi_emp where xxxxx can be used to view the partition location of the current record