MySQL 运算符、查询优化

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 |+----+------+-------------+

比较运算符

注：针对字段使用正则表达式，如果满足条件那么返回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 |+-------+-----------------+

逻辑运算符

与运算：任何一个数据为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 |+-------+----------+

或运算：存在任何一个不为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 |+-------+------------+

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

mysql> select price, !price from test;+-------+--------+| price | !price |+-------+--------+| 12 | 0 || 13 | 0 || 14 | 0 || 10 | 0 || 16 | 0 || 0 | 1 || NULL | NULL |+-------+--------+

异或运算：对于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 |+-------+-------------+

位运算符

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

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 |+-------+----------+----------+---------+---------+----------------------+

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 |+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

可以看出，访问的行数由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。 创建索引(已建好表的基础上)，总结列表：

删除索引语句：​​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 |+----+-------------+-------+------+------------------+------------------+---------+-------+------+--------------------------+

高速缓存

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

mysql> show variables like '%query_cache';+------------------+-------+| Variable_name | Value |+------------------+-------+| have_query_cache | YES |+------------------+-------+

临时表

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

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 |+------+-------+

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。