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

jingjihui

注：原创作品，转载请注明出处。
    众所周知，索引可以加快查询的速度，但有的时候你的一个小不注意索引就失效。 本博文结合实例追根究底研究索引。
前置条件：
   为表达效果，请按本人博客“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 (9.17 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)

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