亲身体验MySQL的索引对搜索性能的提升
1,创建一个user表,包括两列name,phone
2,用python(你喜欢的不论什么语言)插入100W条记录(lz的笔记本比較老。大概用了1分钟吧):
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import MySQLdb
conn = 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,在没建立索引的情况下搜索:
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 |
+-------------+
| 18800055555 |
+-------------+
1 row in set (0.46 sec)
4,对name属性建立索引:
mysql> alter table user add index index_username(name);
Query OK, 0 rows affected (22.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
5, 查询:
mysql> select * from user where name='user55555';
+-------+-----------+-------------+
| uid | name | phone |
+-------+-----------+-------------+
| 55567 | user55555 | 18800055555 |
+-------+-----------+-------------+
1 row in set (0.00 sec)
+---------+------------+--------------+
| uid | name | phone |
+---------+------------+--------------+
| 1000011 | user999999 | 188000999999 |
+---------+------------+--------------+
1 row in set (0.00 sec)
结果秒出。
可见在海量数据的数据库上,索引对搜索性能的提升是很大的。