mysql - 性能对比

查询性能

通过主键与非主键查询对比

mysql> select * from big_data where id = '899999';
+--------+------------+------+--------------------+
| id     | name       | age  | email              |
+--------+------------+------+--------------------+
| 899999 | test899998 |    5 | test899998@163.com |
+--------+------------+------+--------------------+
1 row in set (0.01 sec)

mysql> select * from big_data where name = '999999';
Empty set (0.22 sec)

mysql> select * from big_data where email = 'test999999@163.com';
+---------+------------+------+--------------------+
| id      | name       | age  | email              |
+---------+------------+------+--------------------+
| 1000000 | test999999 |    9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.25 sec)

• 以上可以看出，主键id查询明显速度更快

查询指定字段和查询全量对比

# name创建索引前
mysql> select name from big_data where name = 'test999999';
+------------+
| name       |
+------------+
| test999999 |
+------------+
1 row in set (0.19 sec)
mysql> select * from big_data where name = 'test999999';
+---------+------------+------+--------------------+
| id      | name       | age  | email              |
+---------+------------+------+--------------------+
| 1000000 | test999999 |    9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.24 sec)

mysql> select email from big_data where email = 'test999999@163.com';
+--------------------+
| email              |
+--------------------+
| test999999@163.com |
+--------------------+
1 row in set (0.19 sec)

mysql> select * from big_data where email = 'test999999@163.com';
+---------+------------+------+--------------------+
| id      | name       | age  | email              |
+---------+------------+------+--------------------+
| 1000000 | test999999 |    9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.25 sec)

• 以上可以看出，指定字段查询比全量数据查询速度略快

创建索引前后查询对比

# 创建索引前
mysql> select name from big_data where name = 'test999999';
+------------+
| name       |
+------------+
| test999999 |
+------------+
1 row in set (0.19 sec)
mysql> select * from big_data where name = 'test999999';
+---------+------------+------+--------------------+
| id      | name       | age  | email              |
+---------+------------+------+--------------------+
| 1000000 | test999999 |    9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.24 sec)

# 创建索引
create index index_name on big_data(name);

# name创建索引后
mysql> select name from big_data where name = 'test999999';
+------------+
| name       |
+------------+
| test999999 |
+------------+
1 row in set (0.00 sec)

mysql> select * from big_data where name = 'test999999';
+---------+------------+------+--------------------+
| id      | name       | age  | email              |
+---------+------------+------+--------------------+
| 1000000 | test999999 |    9 | test999999@163.com |
+---------+------------+------+--------------------+
1 row in set (0.00 sec)

• 以上可以看出，指定索引查询比无索引查询速度明显要快。

mysql使用精准数据类型查询对比

mysql> select * from big_data where age = 44;
19841 rows in set (0.22 sec)
mysql> select * from big_data where age = '44';
19841 rows in set (0.22 sec)

# 通过age ='44abc'搜索，实际搜索age = 44
select * from big_data where age = '44abc';
19841 rows in set (0.22 sec)

# 通过age ='44abc'搜索，实际搜索age = 0
select * from big_data where age = 'abc44';
10160 rows in set, 1 warning (0.22 sec)

• 发现：Mysql 一个字段定义成int类型，查询时传入String，会截取字符串。即Mysql会将从左到右的第一个非数值开始，将后面的字符串转成0，在和数值类型相加
• 解决：1、在数据库定义为int类型时，代码层面应禁止传入srting；2、直接定义为string类型
• 性能：虽未看出性能差异，但是建议传入准确的数据类型，都则会存在数据转换过程，肯定会有性能消耗。