Mysql验证索引可以提高查询效率

x33g5p2x  于2021-03-14 发布在 Mysql  
字(1.9k)|赞(0)|评价(0)|浏览(224)

1.数据准备

1.1将准备好的sql语句文件导入到数据库内

load data local infile '/Users/develop/Desktop/sql1.log' into table tb_user fields terminated by',' lines terminated by '\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

1.2 解决报错问题
1.2.1 开启local_infile

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> set global local_infile=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

1.2.2 如果发现还是不可以
使用以下命令登录Mysql:

mysql --local-infile -uroot -p 

2.索引测试

mysql> desc tb_user;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| username    | varchar(45) | NO   | UNI | NULL    |                |
| password    | varchar(96) | NO   |     | NULL    |                |
| name        | varchar(45) | NO   |     | NULL    |                |
| birthday    | datetime    | YES  |     | NULL    |                |
| sex         | char(1)     | YES  |     | NULL    |                |
| email       | varchar(45) | YES  |     | NULL    |                |
| phone       | varchar(45) | YES  |     | NULL    |                |
| qq          | varchar(32) | YES  |     | NULL    |                |
| status      | varchar(32) | NO   |     | NULL    |                |
| create_time | datetime    | NO   |     | NULL    |                |
| update_time | datetime    | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

可以看到username 存在索引,name 不存在索引,我们拿user_name跟name字段举例

mysql> select username,name from tb_user where name='"name12345"';
+-----------------+-------------+
| username        | name        |
+-----------------+-------------+
| "username12345" | "name12345" |
+-----------------+-------------+
1 row in set (0.26 sec)

mysql> select username,name from tb_user where id='12345';
+-----------------+-------------+
| username        | name        |
+-----------------+-------------+
| "username12345" | "name12345" |
+-----------------+-------------+
1 row in set (0.00 sec)

可以看出,使用索引确实会加快查询速度

相关文章

微信公众号

最新文章

更多