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