Mysql索引设计原则

1、代码先行，索引后上
不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
因为开发懂一些业务功能，所以能想到哪些字段是比较常用的，查询可能会比较多的，然后直接根据某几个字段直接创建联合索引。
这其实是不标准的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引 。主体业务功能代码开发完成就可以分析sql建立索引，一般后期会有一些别的功能扩展，到时候新增的sql再重新考虑是否新增或者修改索引。

这样会让建立的索引更精准,而且如果是联合索引还要考虑顺序问题，多字段查询如果能够用到索引，适当的调整联合索引的字段位置或者查询sql的查询条件位置。
以用户积分表为例：t_inte_user_real
  INDEX `COMBINE_INDEX`(`c_corp_user_id`,`c_corp_id`),
  INDEX `IDX_CORPID`(`c_corp_id`)
EXPLAIN select * from t_inte_user_real where c_corp_user_id = 218423449680707584 and c_corp_id = 191209049942917120;

EXPLAIN select * from t_inte_user_real where c_corp_id = 191209049942917120 and c_corp_user_id = 218423449680707584;

EXPLAIN select * from t_inte_user_real where c_corp_user_id = 218423449680707584;

EXPLAIN select * from t_inte_user_real where c_corp_id = 191209049942917120;

  INDEX `COMBINE_INDEX`(`c_corp_id`，`c_corp_user_id`),
UNIQUE KEY `UNIQUE_COMBINE_INDEX` (`c_corp_id`,`c_corp_user_id`)

尽量建联合索引，减少单值索引，因为索引是占存储空间的。通常的查询是多字段联合查询，而mysql大部分情况下只会选择一个索引使用，除了一些字段确定是唯一的，那么就优先建立唯一索引，一方面有利于高效查询，另一方面确保业务字段的唯一性。

system > const > eq_ref > ref > range > index > ALL
ALTER TABLE t_inte_user_real ADD INDEX `COMBINE_INDEX`(`c_corp_user_id`,`c_corp_id`); 
ALTER TABLE t_inte_user_real DROP INDEX `COMBINE_INDEX`;
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原 则。
3、不要在小基数字段上建立索引
索引 基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段， 其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY
index(name(20),age,position)。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据 name 字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来 完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的 name 因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树数据库组成表6509 人正在系统学习中
————————————————
版权声明：本文为CSDN博主「当时明月plus」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/xuruanshun/article/details/125057770