1. Create a user table, which contains two columns: name and phone.
2. Use python (any language you like) to insert records (the lz notebook is old and takes about 1 minute ):
#!/usr/bin/env python# -*- coding:utf-8 -*-import MySQLdbconn = MySQLdb.connect(host='localhost',user='root',db='millionMessage')cur = conn.cursor()for i in range(1,1000000): uname = "user" + str(i) uphone = "188000" + str(i) sql = "insert into user(name,phone) values('%s','%s')" % (uname,uphone) cur.execute(sql)conn.commit()cur.close()conn.close()
3,
Search without creating an index:
Mysql> select * from user where name = 'user55555 ';
+ ------- + ----------- + ------------- +
| Uid | name | phone |
+ ------- + ----------- + ------------- +
| 55567 | user55555 | 18800055555 |
+ ------- + ----------- + ------------- +
1 row in set (0.53 sec)
Mysql> select phone from user where name = 'user55555 ';
+ ------------- +
| Phone |
+ ------------- +
| 1, 18800055555 |
+ ------------- +
1 row in set (0.46 sec)
4,Index the name attribute:
Mysql> alter table user add index index_username (name );
Query OK, 0 rows affected (22.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
5,Query:
Mysql> select * from user where name = 'user55555 ';
+ ------- + ----------- + ------------- +
| Uid | name | phone |
+ ------- + ----------- + ------------- +
| 55567 | user55555 | 18800055555 |
+ ------- + ----------- + ------------- +
1 row in set (0.00 sec)
Mysql> select * from user where name = 'user99999999 ';
+ --------- + ------------ + -------------- +
| Uid | name | phone |
+ --------- + ------------ + -------------- +
| 1000011 | user999999 | 188000999999 |
+ --------- + ------------ + -------------- +
1 row in set (0.00 sec)
Results are output in seconds. It can be seen that in databases with massive data, the index improves the search performance greatly.