MySQL 运算符、查询优化

最新推荐文章于 2024-08-15 23:27:38 发布

theArcticOcean

最新推荐文章于 2024-08-15 23:27:38 发布

阅读量2.5k

点赞数

分类专栏：数据库文章标签： MySQL 优化索引

本文链接：https://blog.csdn.net/theArcticOcean/article/details/53574605

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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: 0

mysql> 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: test
Create 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=latin1
1 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)