# 驗證索引提升查詢效率 ## 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) ~~~ 可以看出，使用索引確實會加快查詢速度