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测试数据mit() 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)
mysql> select * from user where name='user999999'; +---------+------------+--------------+ | uid | name | phone | +---------+------------+--------------+ | 1000011 | user999999 | 188000999999 | +---------+------------+--------------+ 1 row in set (0.00 sec)结果秒出。可见在海量数据的 数据库 上，索引对搜索性能的提升是非常大的。
查看更多关于亲身体验MySQL的索引对搜索性能的提升 - mysql数据的详细内容...