索引的设计原则及查询优化

索引的设计原则

1、为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不创建索引

2、认真考虑列基数，列的基数是是指它所容纳的非重复值的个数，相对于表里行的总数来说，列的基数越高，索引的使用效果越好

3、索引短小值，尽量选用较小的数据类型

• 短小值可以让比较操作更快，从而加快索引查找速度
• 短小值可以让索引短小，从而减少磁盘I/O请求
• 对于更短小的键值，键缓存里的索引快可以容纳更多的键值

4、索引字符串值的前缀，想要对字符串列进行索引，应当尽可能指定前缀长度

5、利用最左前缀

6、不要建立过多索引，每增加一个索引都需要占据额外的磁盘空间，而且都会影响写入操作的性能

7、让参与比较的索引类型保持匹配

8、利用慢查询日志找出那些性能低劣的查询

查询优化

        查询优化的主要目标是尽可能的使用索引，并且使用最严格的索引来消除对行数量随时可能快速增加的顾虑，相比之下，查询优化程序之所以要尽力排除行，是因为从搜索范围中排除行的速度越快，那么找到那些与搜索条件匹配的行的速度也会越快，首先执行最严格的条件测试，那么查询处理的速度也会更快一些。

• 分析表，这将生成关于键值分布情况的统计数据，它们可以帮助优化程序更准确的评估索引效率
• 使用EXPLAIN验证优化程序的操作
• 比较拥有相同数据类型的列，如 INT 和 BIGINT比较比 INT 和 INT慢
• 让索引列在比较表达式中单独出现，尽量让索引列不参与运算
• 不要在 LIKE模式的开始位置使用通配符  "%",不然会使索引失效
• 子查询要慢与相同逻辑的连接查询，所以尽量使用连接查询
• 避免过多使用自动类型转换，如下图，num_col是整型类型，第二条语句就会牵扯到类型转换，会有一个很小的性能损失，因为要将整型或者字符串转换成双精度数才能进行比较，如果该列上带有索引，也会使索引失效