親身體驗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)

mysql> select * from user where name='user999999';
+---------+------------+--------------+
| uid | name | phone |
+---------+------------+--------------+
| 1000011 | user999999 | 188000999999 |
+---------+------------+--------------+
1 row in set (0.00 sec)

結果秒出。可見在海量數據的數據庫上，索引對搜索性能的提升是非常大的。