索引的使用

以下内容基于MySQL的InnoDB引擎

1、索引可以有效提高查询效率，如果一个 SQL 查询比较慢，一般是索引使用不当。

2、InnoDB使用B+树索引模型，每一个索引对应一颗B+树；

3、主键索引：PRIMARY KEY，不为空，不重复。

主键索引存的是整行数据，也即在seelct查询中，找到主键之后，才能获取到该条记录上的其他所有字段的数据。也叫聚簇索引；
非主键索引，存的是主键的值，也叫二级索引。也即通过select查询到非主键索引对应的主键索引值，再去主键索引上取其他字段的数据，回到主键索引树搜索的过程，叫回表；

Tips: 一般情况下新增一列ID列，为其添加自增主键索引（NOT NULL PRIMARY KEY AUTO_INCREMENT），此可以减少非主键索引在存储主键值时占用的空间；

4、count()

count(1)执行得要比 count(主键 id) 快，因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段)来说：如果这个“字段”是定义为 not null，一行行地从记录里面读出这个字段，按行累加；如果这个“字段”定义允许为 null，那么还要把值取出来再判断一下，不是 null 才累加；
count(*) 专门做了优化，不取值，count(*)肯定不是 null，按行累加。

5、字符串字段加索引

等值查询，使用HASH索引；
范围查询，使用BTREE索引；
如要优化存储空间，可限制索引长度（但是会损失区分度，增加扫描次数，也就是时间成本），EX: add index index_name(field_name(6))；在设定索引长度时，可预先设定区分度损失比例，比如5%，也即DISTINCT field_name为100的话，DISTINCT field_name(6)应为100 * 0.95 = 95。损失度是具体情况而定。另外区分度很小的字段，没必要加索引，加了相当于没加。

6、order by

order by的字段，如果没有合适的索引，就要放到内存甚至磁盘中去排序了，比较费资源时间；
可以考虑在常用的WHERE筛选和排序的字段上添加联合索引，比如这个查询select city,name,age from t where city='杭州' order by name limit 1000;，可以设置联合索引(杭州,name)，使让这俩字段有序排列，免去了再次排序的过程；

7、避免对字段使用函数

比如WHERE TIMESTAMPDIFF(DAY,day_id,'2021-01-01') > 90，这时day_id字段的索引用不上。应把函数消掉，换算到逻辑符的右边；

8、小心隐式转换

隐式转换会导致InnoDB引擎对字段偷偷使用函数，导致索引失效，应采取和7类似的做法；

9、很简单的查询却执行失败或很慢