mysql查询优化

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

select count(*) from tradelog where month(t_modified)=7;

select * from tradelog where tradeid=110717;

mysql> select ‘8‘ < ‘10‘;
+------------+
| ‘8‘ < ‘10‘ |
+------------+
|          0 |
+------------+
mysql> select 8 < 10;
+--------+
| 8 < 10 |
+--------+
|      1 |
+--------+ 
mysql> select 8 < ‘10‘;
+----------+
| 8 < ‘10‘ |
+----------+
|        1 |
+----------+

CREATE TABLE `t_test1` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure i_testdata()
begin
declare i int;
set i=1;
while(i<=100000) do
insert into t_test1 values(i,i);
set i=i+1;
end while;
end;;
delimiter ;

call i_testdata();

select * from t_test1 where id=1;

show processlist;
+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 14 | root | langdeMBP:60114 | employees | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+
1 row in set (0.00 sec)
mysql> select * from sys.schema_table_lock_waits;
Empty set (0.04 sec)

show processlist;
+----+------+-----------------+-----------+---------+------+-------------------------+-----------------------------------+
| Id | User | Host            | db        | Command | Time | State                   | Info                              |
+----+------+-----------------+-----------+---------+------+-------------------------+-----------------------------------+
| 14 | root | langdeMBP:60114 | employees | Query   |  127 | User sleep              | select sleep(1) from t_test1      |
| 15 | root | langdeMBP:64351 | employees | Query   |   93 | Waiting for table flush | flush tables t_test1              |
| 16 | root | langdeMBP:64358 | employees | Query   |   68 | Waiting for table flush | select * from t_test1 where id =1 |
| 17 | root | langdeMBP:64388 | NULL      | Query   |    0 | starting                | show processlist                  |
+----+------+-----------------+-----------+---------+------+-------------------------+-----------------------------------+
4 rows in set (0.00 sec)

mysql> kill 15;
Query OK, 0 rows affected (0.00 sec)
mysql> kill 14;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host            | db        | Command | Time | State    | Info             |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 16 | root | langdeMBP:64358 | employees | Sleep   |  215 |          | NULL             |
| 17 | root | langdeMBP:64388 | NULL      | Query   |    0 | starting | show processlist |
+----+------+-----------------+-----------+---------+------+----------+------------------+
2 rows in set (0.01 sec)

begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_test1 set c=c+1 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_test1 where id = 1 lock in share mode;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    19
Current database: employees
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show processlist;
+----+------+-----------------+-----------+---------+------+------------+-------------------------------------------------------+
| Id | User | Host            | db        | Command | Time | State      | Info                                                  |
+----+------+-----------------+-----------+---------+------+------------+-------------------------------------------------------+
| 17 | root | langdeMBP:64388 | NULL      | Query   |    0 | starting   | show processlist                                      |
| 18 | root | langdeMBP:64459 | employees | Sleep   |   73 |            | NULL                                                  |
| 19 | root | langdeMBP:64476 | employees | Query   |   36 | statistics | select * from t_test1 where id = 1 lock in share mode |
+----+------+-----------------+-----------+---------+------+------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from  sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-07-24 17:04:31
                    wait_age: 00:00:09
               wait_age_secs: 9
                locked_table: `employees`.`t_test1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 421222362831608
         waiting_trx_started: 2019-07-24 17:04:31
             waiting_trx_age: 00:00:09
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 19
               waiting_query: select * from t_test1 where id = 1 lock in share mode
             waiting_lock_id: 421222362831608:142:4:2
           waiting_lock_mode: S
             blocking_trx_id: 336441
                blocking_pid: 18
              blocking_query: NULL
            blocking_lock_id: 336441:142:4:2
          blocking_lock_mode: X
        blocking_trx_started: 2019-07-24 16:45:04
            blocking_trx_age: 00:19:36
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 18
sql_kill_blocking_connection: KILL 18
1 row in set, 3 warnings (0.00 sec)