索引建立的基本原则_MySQL：设计索引的时候，我们一般需要考虑哪些因素

设计过程

（1）首先，我们根据业务需求建立了表的结构后，我们会知道表中有哪些字段，每个字段是什么类型，包含什么数据。

(2)设计完表的结构之后，接下来要做的就是设计表的索引。设计索引时，我们首先需要考虑的是以后我们如何查询表。

可能我们一开始设计好表的结构后，不知道以后如何查询表。 这时候我们不能在表结构设计好之后就急着去设计索引，因为这个时候你还不知道如何去查询表。

(3)然后就可以进入系统开发流程了，就是根据需求文档编写业务代码。

等系统开发差不多了，所有功能都运行顺利之后，就可以考虑如何创建索引了，因为这时候系统所有的SQL都已经写好了，你清楚的知道每张表会发起什么样的查询。 。

在互联网公司，我们提倡的是写尽可能简单的SQL。 复杂的逻辑可以由业务系统来实现。 如果SQL可以单表查询，就不要查询多表。 如果SQL可以关联多个表，尽量不要写子查询。 如果你能写几十行SQL，就不要写几百行SQL。 相反，可以考虑使用业务代码来对内存中的某些数据实现复杂的计算逻辑，而不是使用 SQL 来完成。

其实在一般的系统中，只要你的SQL语句尽可能简单，并且建立了必要的索引，每条SQL语句都可以建立索引，数据库性能往往问题不大。复杂SQL调优主要是针对复杂SQL必须写数百行，没有任何其他选择。

设计原则

那么这个时候，第一个索引设计原则就在这里：根据你的SQL语句中的where条件、order by条件、group by条件来设计索引。

步骤1：想想你的where条件中应该使用哪些字段来过滤数据？ 哪些字段应该按 order by 排序？ group by 应该使用哪些字段进行分组和聚合？

问题是：比如我们要这样写： xx from where xx=xx order by xx limit xx, xx

where、group、order by 中可能需要使用多个字段。 我们应该选择哪些字段来创建索引呢？ 考虑以下问题：

(1)第一个是字段基数问题。

(2)其次，尽量选择字段类型较小的列来设计索引。

（3）频繁更新的字段是否应该作为主键？

（4）尽量不要让你的查询语句中的字段执行任何函数或计算（即where=(a)=xx），否则会进行全表扫描。

总之：

最终创建所有索引后，大多数查询都应该建立索引。

步骤2：当我们选择了哪些字段需要建立索引后，我们必须根据这些字段设计一到两个联合索引。

因为系统插入的数据值可能根本不按顺序排列，所以很可能会导致索引树中的某个页自动分裂。 这个页面分割过程非常耗时。 所以一般大家不会设计太多的指标。 建议两个或三个联合索引覆盖该表上的所有查询。 否则，过多的索引必然导致增删改查时性能不佳，因为需要更新多个索引树。前缀索引中应该截取多少个字段作为前缀？

什么是前缀索引？

前缀索引中应该截取多少个字段作为前缀？

创建商户表。 由于地址字段比较长，因此在地址字段上创建前缀索引：

create table shop(address varchar(120) not null);
alter table shop add key (address(12));

问题是，拦截多少？ 如果拦截过多，就达不到节省索引存储空间的目的。 如果拦截太少，重复内容就会过多，字段的哈希度（选择性）就会降低。 如何计算不同长度的选择性？

select count(distinct address) / count(*) from shop;

select count(distinct left(address,10))/count(*) as sub10, count(distinct left(address,11))/count(*) as sub11, count(distinct left(address,12))/count(*) as sub12, count(distinct left(address,13))/count(*) as sub13
from shop

只要截取前13个字段，就已经有比较高的选择性了。

建立前缀查询后，where查询仍然可以使用，但是不能使用order by和group by。

例如，我们需要创建一个像KEY(name(20),age,)这样的索引。 假设 name 的类型为 (255)，但在索引树中您只提取 name 值的前 20 个字符。

但如果按名称排序，那么由于名称只包含索引树中的前20个字符，因此索引无法用于此排序。 group by也是如此。什么情况下数据库索引会失败？

柱与柱的比较

在某个表中，两列（id 和 c_id）具有单独的索引。 以下查询条件不会被索引。

select * from test where id=c_id;

这种情况下认为最好进行全表扫描。

存在 NULL 值条件（is null/is not null）

select * from test where id is not null;

如果需要在索引中存储空值，有两种方法：

Like 查询以 % 开头

使用模糊搜索时，尝试使用尾随通配符。

例如，姓名 | 您可以搜索“张%”。 相反，如果查询所有名为“Ming”的人，那么只能是%Ming。 这个时候索引怎么定位呢？

在前面匹配的情况下，执行计划会更倾向于选择全表扫描。 匹配完成后，就可以使用INDEX RANGE SCAN。

因此，在业务设计时，尽量考虑模糊搜索问题，多使用后通配符。

条件包括函数、表达式、计算（+ - * /）：

查询条件中尽量不要对索引列使用函数，比如下面的SQL

select * from test where upper(name)='SUNYANG';

这样就不会使用索引了，因为创建时和计算后索引可能不同，无法定位到索引。 但如果查询条件没有计算索引列，那么索引仍然可以使用。例如

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

此类函数还包括：、、、trunc等。

也可以没有表达式或计算 (+ - */)：

explain SELECT * FROM `t2` where id+1 = 4;

隐式转换发生在数据类型中

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
explain SELECT * FROM `user_innodb` where name = 136;
explain SELECT * FROM `user_innodb` where name = '136';

如果条件中有or，即使有带索引的条件，也不会使用（这就是为什么要尽量少用or）

如果想使用OR，又想让索引失效，只能在OR条件下为每一列添加索引。

否定查询

不像不能

explain select *from employees where last_name not like 'wang';

!= () 并且在某些情况下 NOT IN 工作：

explain select *from employees where emp_no not in (1)
explain select *from employees where emp_no <> 1

组合索引，不使用第一列索引，索引会失效（最左匹配原则）

如果MySQL估计使用全表扫描而不是使用索引块，则不会使用索引

需要注意的是，SQL语句是否使用索引与数据库版本、数据量以及数据选择和读取有关。

事实上，是否使用索引由优化器说了算。

优化器是基于什么？ 基于成本开销，不是基于规则或者语义。如何低成本去做

查看索引使用情况：

显示如“%”；

注意：

：值越高越好。 该值越高，使用索引的查询数量就越多。

t：值越高，查询效率越低。

示例：创建陌生人社交应用索引

陌生人社交App的一个功能就是根据一定的条件进行搜索和选择，找到App上哪些用户更符合你的期望，然后与他们交友。

那么，当您进行筛选时，您会搜索社交应用中的哪个表格呢？ 显然是用户信息表。 该表将包含什么样的用户个人信息？ 大致会包括你所在的地区（你在哪个省市，这个很重要，不然不在同一个城市，可能网上聊得很好，线下没机会见面），性别，年龄，身高，体重、爱好、性格特征、照片，当然还有最后一次上线时间（不然APP已经半年没上线了，你会怎么找到他？）另外，如果你支持允许别人评价他在约会过程中，那么可能还需要包括该人的综合分数。

搜索这个用户表不仅仅是过滤那么简单，可能还需要以页面的形式展示。 另外，在搜索时，一般需要将过滤后的结果按照一定的规则进行排序。

现在省、市、性别这三个几乎每次查询都会添加的条件已经放在联合索引的最左边，那么这个联合索引应该放在哪些字段呢？

接下来的问题是，假设查询的时候还有一个条件，就是根据用户7天内的最新登录时间进行过滤，过滤出最近7天内登录过APP的用户，那么实际上可能是在你的user表有这么一个字段，

其实一般来说，如果可以通过where语句中的上述联合索引过滤掉大部分数据，保留一小部分数据，并根据磁盘文件的order by语句进行排序，最后执行如果基于限制进行分页，那么总体性能还是比较高的。

但有时候我害怕一个问题，那就是如果只用联合索引中一些基数很小的字段来过滤怎么办？

比如根据性别进行过滤，比如一下子过滤掉所有女性，可能有几百万的用户数据，然后对磁盘文件进行排序分页？ 那么这个表现可能就极其糟糕了！

因此，此时这种基于分数排序的性别低基数字段和语句的过滤整体运行效率非常高，完全可以基于辅助索引来实现。

以此类推，完全可以分析查询场景，使用(, city, sex, hobby,,,age)等联合索引来抵抗复杂的where条件过滤查询。 这种情况下，索引过滤速度是非常快的。 过滤掉的数据量较小，然后进行排序和限制分页。

同时，对于一些低基数的字段过滤+分数排序的查询场景，可以设计类似(性别，分数)的辅助索引来处理，让他快速定位到低基数对应的大量数据字段，然后按照索引顺序执行limit语句。 获取指定页面的数据也会非常快。

总结：尽量用一两个复杂的多字段联合索引来抵抗你80%以上的查询，然后用一两个辅助索引来抵抗剩下20%的非典型查询，保证99%以上您的疑问都可以得到充分满足。 使用索引可以保证你的查询速度和性能！

概括

索引应该如何设计？

如果where子句后面有范围查询，那么经常用于范围查询的字段必须放在联合索引的末尾。 因为在SQL中，一旦对某个字段进行范围查询时使用了索引，那么该字段的后续条件就无法使用索引了。 如何在所有适合联合索引的字段中进行选择？ 创建索引时，除非要存储 NULL，否则应将列指定为 NOT NULL。 在MySQL中，包含空值的列很难优化查询，因为它们使索引、索引统计和比较操作变得复杂。应该将空值替换为0、特殊值或空字符串

什么情况下数据库索引会失效？