Mysql索引设计建议

技术
  1. MySQL索引类型
  • 按字段特性来划分有：主键索引，唯一索引，普通索引
  • 按字段个数划分有：单列索引，组合索引
  • 按数据结构划分有：B+tree、Hash、全文索引
  • 按物理存储划分有：聚簇索引，辅助索引
  1. 常用查看/优化索引的命令
1
2
3
4
#查看某个表中已有索引情况
SHOW INDEX FROM TABLE_NAME;
# 栗子
SHOW INDEX FROM `user`;
参数 说明
table 表示创建索引的数据表名。
non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
key_name 表示索引的名称。
seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
column_name 表示定义索引的列字段。
collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
packed 指示关键字如何被压缩。若没有被压缩，值为NULL。
NULL 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
comment 显示评注。 
1
2
3
4
#查看SQL命中索引情况
EXPLAIN SQL;
# 栗子
EXPLAIN SELECT * FROM `user`;

字段解释见这里。

1
2
#查看当前数据库中索引使用情况
SHOW STATUS LIKE 'Handler_read%';
  1. 索引设计的原则
  • 适合索引的列是出现在where子句中的列，或者连接子句中指定的列；
  • 基数较小的类，索引效果较差，没有必要在此列建立索引；
  • 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
  • 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
  1. 索引优化的建议
  • 查询返回结果大于全表总量的30%，别建索引走走全表扫描吧。
  • 前导模糊查询不能命中索引，如果想要优化可以根据情况看是否能优化为非前导。
  • 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。
  • union、in、or都能够命中索引，建议使用in。
  • 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
  • 负向条件查询不能使用索引（比如说<>,not in），可以优化为in查询。
  • 范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
  • 数据库执行计算不会命中索引。（计算最好不要放在sql中，而应该放在业务逻辑中）。
  • 建立索引的列，不允许为NULL，其实数据表设计都尽量不要有NULL。