MySQL 运算符、查询优化

MySQL运算符

算术运算符

mysql提供的运算符有+, -, *, /(DIV), %(MOD)

mysql> select * from test;+----+------+-------+| id | name | price |+----+------+-------+|  1 | tag1 |    12 ||  2 | tag2 |    13 ||  3 | tag3 |    14 ||  4 | tag4 |    10 ||  5 | tag6 |    16 |+----+------+-------+5 rows in set (0.00 sec)mysql> select id,name,price*price from test;+----+------+-------------+| id | name | price*price |+----+------+-------------+|  1 | tag1 |         144 ||  2 | tag2 |         169 ||  3 | tag3 |         196 ||  4 | tag4 |         100 ||  5 | tag6 |         256 |+----+------+-------------+5 rows in set (0.00 sec)

比较运算符

operator operator = Is not null > Between < in >= not in <= like !=(<>) not like Is null regexp

注：针对字段使用正则表达式，如果满足条件那么返回1，否则返回0。between and确定一个范围，如果字段值在范围内，那么返回1，否则返回0.
使用例子：

mysql> select * from test where price in (13,14);+----+------+-------+| id | name | price |+----+------+-------+|  2 | tag2 |    13 ||  3 | tag3 |    14 |+----+------+-------+2 rows in set (0.00 sec)mysql> select * from test where price not in (13,14);+----+------+-------+| id | name | price |+----+------+-------+|  1 | tag1 |    12 ||  4 | tag4 |    10 ||  5 | tag6 |    16 |+----+------+-------+3 rows in set (0.00 sec)mysql> select * from test where name like '%6';+----+------+-------+| id | name | price |+----+------+-------+|  5 | tag6 |    16 |+----+------+-------+1 row in set (0.00 sec)mysql> select name, name regexp '^tag', name regexp '4$' from test;+------+--------------------+------------------+| name | name regexp '^tag' | name regexp '4$' |+------+--------------------+------------------+| tag1 |                  1 |                0 || tag2 |                  1 |                0 || tag3 |                  1 |                0 || tag4 |                  1 |                1 || tag6 |                  1 |                0 |+------+--------------------+------------------+5 rows in set (0.00 sec)mysql> select price, price between 14 and 16 qualified_price  from test;+-------+-----------------+| price | qualified_price |+-------+-----------------+|    12 |               0 ||    13 |               0 ||    14 |               1 ||    10 |               0 ||    16 |               1 |+-------+-----------------+5 rows in set (0.00 sec)

逻辑运算符

character effect &&(AND) 与 ||(OR) 或 !(NOT) 非 XOR 异或

与运算：任何一个数据为0返回0，所有数据不为0，不为NULL，返回1.

mysql> select price,price&&1 from test;+-------+----------+| price | price&&1 |+-------+----------+|    12 |        1 ||    13 |        1 ||    14 |        1 ||    10 |        1 ||    16 |        1 ||     0 |        0 |+-------+----------+6 rows in set (0.00 sec)

或运算：存在任何一个不为NULL，不为0的数据，返回1. 否则返回NULL（如果有数据中有NULL）或者0。

mysql> select price, price || 0 from test;+-------+------------+| price | price || 0 |+-------+------------+|    12 |          1 ||    13 |          1 ||    14 |          1 ||    10 |          1 ||    16 |          1 ||     0 |          0 ||  NULL |       NULL |+-------+------------+7 rows in set (0.00 sec)

非运算：返回和数据相反的结果。不过需要注意的是NULL的操作数据结果依然是NULL。

mysql> select price, !price from test;+-------+--------+| price | !price |+-------+--------+|    12 |      0 ||    13 |      0 ||    14 |      0 ||    10 |      0 ||    16 |      0 ||     0 |      1 ||  NULL |   NULL |+-------+--------+7 rows in set (0.00 sec)

异或运算：对于x1 XOR x2, 当x1,x2都为0或非0，那么返回0；一个是0，一个是非0，返回1；只要有一个是NULL，返回NULL。

mysql> select price, price XOR 1 from test;+-------+-------------+| price | price XOR 1 |+-------+-------------+|    12 |           0 ||    13 |           0 ||    14 |           0 ||    10 |           0 ||    16 |           0 ||     0 |           1 ||  NULL |        NULL |+-------+-------------+7 rows in set (0.00 sec)

位运算符

&, |, ~, ^, <<, >>
针对二进制数进行的运算。

mysql> select price, price>>1,price<<1, price&1,price|1,~price from test;+-------+----------+----------+---------+---------+----------------------+| price | price>>1 | price<<1 | price&1 | price|1 | ~price               |+-------+----------+----------+---------+---------+----------------------+|    12 |        6 |       24 |       0 |      13 | 18446744073709551603 ||    13 |        6 |       26 |       1 |      13 | 18446744073709551602 ||    14 |        7 |       28 |       0 |      15 | 18446744073709551601 ||    10 |        5 |       20 |       0 |      11 | 18446744073709551605 ||    16 |        8 |       32 |       0 |      17 | 18446744073709551599 ||     0 |        0 |        0 |       0 |       1 | 18446744073709551615 ||  NULL |     NULL |     NULL |    NULL |    NULL |                 NULL |+-------+----------+----------+---------+---------+----------------------+7 rows in set (0.00 sec)

MySQL查询优化

索引

索引可以快速定位表中的记录，提高查询的效率。
例如：

mysql> select * from test;+----+------+-------+| id | name | price |+----+------+-------+|  1 | tag1 |    12 ||  2 | tag2 |    13 ||  3 | tag3 |    14 ||  4 | tag4 |    10 ||  5 | tag6 |    16 ||  6 | dada |     0 ||  7 | dada |  NULL |+----+------+-------+7 rows in set (0.00 sec)mysql> explain select * from test where price="NULL";+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.01 sec)mysql> create index price_index on test(price);Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from test where price="NULL";+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+|  1 | SIMPLE      | test  | ref  | price_index   | price_index | 5       | const |    1 | NULL  |+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+1 row in set (0.00 sec)

可以看出，访问的行数由7变成1.
删除索引：

mysql> drop index price_index on test;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

多列索引

在表的多个字段中创建一个索引。

mysql> select * from test;+----+------+-------+| id | name | price |+----+------+-------+|  7 | dada |  NULL ||  6 | dada |     0 ||  1 | tag1 |    12 ||  2 | tag2 |    13 ||  3 | tag3 |    14 ||  4 | tag4 |    10 ||  5 | tag6 |    16 |+----+------+-------+7 rows in set (0.02 sec)mysql> create index name_price_index on test(name,price);mysql> desc select * from test where name="tag1" and price=12;+----+-------------+-------+------+-----------------------------------+------------------+---------+-------------+------+--------------------------+| id | select_type | table | type | possible_keys                     | key              | key_len | ref         | rows | Extra                    |+----+-------------+-------+------+-----------------------------------+------------------+---------+-------------+------+--------------------------+|  1 | SIMPLE      | test  | ref  | index_name_price,name_price_index | index_name_price | 37      | const,const |    1 | Using where; Using index |+----+-------------+-------+------+-----------------------------------+------------------+---------+-------------+------+--------------------------+

通过desc可以看出，多列索引让检索的行数减小至1。
创建索引(已建好表的基础上)，总结列表：

类型 语法 normal create index index_name on table_name(field); unique create unique index index_name on table_name(field); fulltext create fulltext index index_name on table_name(field); single-row create index index_name on table_name(field(length)); multiple-row create index index_name on table_name(field1,field2,filed3…); space create spatial index index_name on table_name(field);

删除索引语句：drop index index_name on table_name
可以通过show create语句查看新的表结构。

mysql> show create table test\G;*************************** 1. row ***************************       Table: testCreate Table: CREATE TABLE `test` (  `id` smallint(6) NOT NULL,  `name` varchar(30) NOT NULL,  `price` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name_price_index` (`name`,`price`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

分析查询语句
除了前面使用的explain，还有describe（desc）。

mysql> desc select * from test where name="tag1";+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+|  1 | SIMPLE      | test  | ref  | index_name_price | index_name_price | 32      | const |    1 | Using where; Using index |+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+1 row in set (0.00 sec)

高速缓存

MySQL将查询的结果放到高速缓存中，如果用户有相同的查询操作，高速缓存就发挥作用了。
查询是否开启高速缓存

mysql> show variables like '%query_cache';+------------------+-------+| Variable_name    | Value |+------------------+-------+| have_query_cache | YES   |+------------------+-------+1 row in set (0.01 sec)

临时表

临时表存储于内存中，读写速率更快。临时表依赖于会话，如果处于不同的会话中，即使是同一用户也能使用同名临时表，不会冲突。当断开数据连接的时候，整个临时表也就消失了。

mysql> create temporary table tmp(id smallint, power int);Query OK, 0 rows affected (0.04 sec)mysql> insert into tmp value (12,34);Query OK, 1 row affected (0.02 sec)mysql> select * from tmp;+------+-------+| id   | power |+------+-------+|   12 |    34 |+------+-------+1 row in set (0.00 sec)