验证mysql索引效果_验证索引可以提高查询效率

# 验证索引提升查询效率

## 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)

~~~

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