<strong># Query_time: 0.018522</strong>  Lock_time: 0.000107  Rows_sent: 3572  Rows_examined: 7144  Rows_affected: 0  

# Bytes_sent: 95709  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0  

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  

#   InnoDB_pages_distinct: 125  

 

 

针对以上的实验结果，我们可以得出以下结论：

 

1. 针对主键做排序操作不会有性能损耗；
2. 针对不在选择条件中的索引字段做排序操作，索引不会起优化排序的作用；
3. 针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。

 

下面我们再研究下用选择条件中的索引字段排序，数据库是否会优化排序算法，我们任然用UP_User表来研究。

 

 

select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17

 

 

然后我们用选择条件中的索引字段做排序：

 

 

select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17

 

 

然后我们用选择条件中的非索引数值字段做排序：

 

 

select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17

 

 

从上面三条查询语句的执行时间来分析，使用索引字段排序和不排序花的时间差不多，比使用普通字段排序花的时间少一些，因此我们可以得出第四条结论：

 

4.针对在选择条件中的索引字段做排序操作，索引会起优化排序的作用。

 

5.索引维护

 

在前面我们可以看到所有的主键和索引都是排好序的，那么排序这件事情就需要销号资源，每次有新的数据插入，或者老的数据的数值发生变更，排序就需要调整，这里面是需要损耗性能的，下面我们分析一下。

自增型字段作为主键时，数据库对主键的维护成本非常低：

 

1. 每次新增加的值都是一个最大的值，追加到最后即可，其他数据不需要挪动；
2. 这种数据一般不做修改。

  

 

 

使用业务型字段作为主键时，主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。

 

 

 

因为innodb主键和数据是在放在一块的，每次挪动主键，也需要挪动数据，维护的成本会比较搞，对于需要频繁写入的表，不建议使用业务字段作为主键的。

 

由于主键是所有索引的叶节点的值，也是数据排序的依据，如果主键的值被修改，那么需要修改所有相关索引，并且需要修改整个主键B+Tree的排序，损耗会非常大。避免频繁更新主键可以避免以上提到的问题。

 

update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11

 从上面的数据可以看出来，索然SQL语句只修改了一条数据，却影响了11个Page。

 

相对于主键而已，索引就轻很多，它的叶节点的值是主键，很轻，维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低，维护8个就不一定低了，这种事需要均衡地对待。

 

6.索引设计原则

索引其实是一把双刃剑，用好了事半功倍，没用好，事倍功半。

主键的字段无特殊情况，一定要使用数值类型的，排序时占用计算资源少，存储时占用空间也少。若主键的字段值很大，则整个数据表的各种索引也会变得没有效率，因为所有的索引的叶节点的值都是主键。

 

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

 

mysql中的索引原理与表设计

标签：