mysql性能调优3--索引相关

注：原创作品，转载请注明出处。

    众所周知，索引可以加快查询的速度，但有的时候你的一个小不注意索引就失效。 本博文结合实例追根究底研究索引。

前置条件：
   为表达效果，请按本人博客“mysql性能调优1”博文往Supan表中插入7000万数据。注意设置innodb_flush_log_at_trx_commit=2，否则插入7000万数据，大概需要耗费你4个小时左右。

实例一：演示索引的告诉查询

  首选不建立supan表的grade字段索引 进行grade字段的查询：

mysql> select * from supan where grade = 1515215;
+--------+---------+---------+
| name   | id      | grade   |
+--------+---------+---------+
| 寮犱笁   | 4535219 | 1515215 |
+--------+---------+---------+
1 row in set ([color=blue]9.17 [/color]sec)

耗时：9.17秒

创建grade字段上的索引：

mysql> create index index_supan_grade on supan(grade);
Query OK, 0 rows affected (1 min 17.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查询

mysql> select * from supan where grade = 1515215;
+--------+---------+---------+
| name   | id      | grade   |
+--------+---------+---------+
| 寮犱笁   | 4535219 | 1515215 |
+--------+---------+---------+
1 row in set (0.09 sec)

创建完索引后，用时仅为0.09秒 效率提升到 9.17 / 0.09 倍


二：索引字段上用表达式失效。

mysql> select * from supan where grade*10 = 256450;
+--------+---------+-------+
| name   | id      | grade |
+--------+---------+-------+
| 寮犱笁   | 1045649 | 25645 |
| 寮犱笁   | 2045649 | 25645 |
| 寮犱笁   | 3045649 | 25645 |
+--------+---------+-------+
3 rows in set (9.11 sec)

耗时9.11，又回到了远古时代，呵呵呵。上面的sql其实可以完全改为下面的高效率sql：

mysql> select * from supan where grade = 256450;
+--------+---------+--------+
| name   | id      | grade  |
+--------+---------+--------+
| 寮犱笁   | 1276454 | 256450 |
| 寮犱笁   | 2276454 | 256450 |
| 寮犱笁   | 3276454 | 256450 |
+--------+---------+--------+
3 rows in set (0.11 sec)

功能完全相同，但效率就是天上地下了，其中缘故你懂得。

三：mysql的索引字段上用聚凑函数，索引不失效。Oracle失效（值得注意）

mysql> select max(grade) from supan;
+------------+
| max(grade) |
+------------+
|    5792149 |
+------------+
1 row in set (0.00 sec)

四：索引对插入的影响

有索引的时候插入记录

mysql> insert into supan(name,grade) values('chenchaoyang',100);
Query OK, 1 row affected (0.09 sec)

删除索引

mysql> drop index index_supan_grade on supan;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次插入数据

mysql> insert into supan(name,grade) values('chenchaoyang',100);
Query OK, 1 row affected (0.04 sec)

可以从两次插入的耗时分析，在索引的时候插入数据，效率比较低下，所以在经常需要插入，更新的表中，尽量少使用索引。