# 验证索引提升查询效率
## 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)
~~~
可以看出,使用索引确实会加快查询速度