Mysql 的索引分类和设计原则
1、Mysql 的几种索引总结
1.1、普通索引
普通索引是最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度，索引值可出现多次。。
- 创建普通索引脚本
// 1、新增时创建索引
CREATE INDEX indexName ON table_name (column_name);
// 2、创建表以后，添加索引，修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName);
1.2、唯一索引
唯一索引与普通索引类似，两者不同点在于：唯一索引的索引列的值必须唯一（除了NULL外，NULL可能会出现多次），但允许有空值。如果是组合索引，则列值的组合必须唯一。需要特别说明的是，主键也是一种唯一索引，但它必须指定为“PRIMARY KEY”。
创建索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。
- 创建唯一索引脚本
// 1、新增时创建索引.这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
// 关于 length : 如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
CREATE UNIQUE INDEX indexName ON mytable(columnName(length)) ;
// 2、创建表以后，添加索引.这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER table mytable ADD UNIQUE [indexName] (columnName(length));
// 3、主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
1.3、组合索引
组合索引是在多个字段上创建一个索引。
组合索引是针对单列索引的一种划分。单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。
组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵循“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。
例如：这里由id、name 和 age 3 个字段构成的索引，索引行中按 id/name/age 的顺序存放，索引可以搜索下面字段组合：（id、name、age）、（id、name） 或者 id.如果列不构成索引最左前缀原则，Mysql不能使用局部索引，如（age）或者（name、age）组合则不能使用索引查询。
- 创建组合索引脚本
ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);
1.3.1、起作用索引
1.3.1、不起作用索引
1.4、全文索引
FULLTEXT 全文索引可以用于全文搜索。只有 MyISAM 存储引擎支持 FULLTEXT 索引，并且只为 CHAR 、VARCHAR 和 TEXT 列。索引总是对整个列进行，不支持局部（前缀）索引。
- 创建全文索引脚本
// 创建全文索引脚本,该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
1.5、空间索引
空间索引必须在 MyISAM类型的表中创建，且空间类型的字段必须为非空。
2、索引设计原则
2.1、适合创建索引的情况
-
①
字段的数值有唯一性的限制.索引本身可以起到约束作用，比如：唯一索引、主键索引。如果表结构中某个字段是唯一性的，就直接创建
唯一索引或主键索引，这样可更快速地通过该索引来确定某条记录。-
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源 Alibaba）
-
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但是提高查找速度是很明显的。
-
-
②
频繁作为 WHERE 查询条件的字段.某个字段在 SELECT 语句中的 WHERE 条件中经常被使用到，那就需要给这个字段创建索引。尤其是在数据量大的情况下，创建普通索引就可以大幅提高数据查询的效率。
-
③
经常 GROUP BY 和 ORDER BY 的列.索引就是让数据按照某种顺序进行存储或检索，当使用 GROUP BY 分组或 ORDER BY 排序时，就需要
对分组或者排序字段进行索引。如果有多个字段，可建立组合索引。 -
④
update、delete 的 WHERE 条件列. -
⑤
多表 JOIN 连接操作时，创建索引注意事项.-
连接表的数量尽量不要超过3张，每增加一张表就类似增加了一次嵌套循环，数据级增长会非常快，严重影响查询效率。 -
对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤，如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。 -
对用于连接的字段创建索引，并且该字段在多表中的类型必须一致。
-
-
⑥
使用列的类型小的创建索引.如果要对某个整数列创建索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT，能使用 MEDIUMINT 就不要使用 INT. 该建议
对于主键来讲更加适用，因为不仅是聚簇索引会存储主键值，二级索引也会存储主键值，如果主键较小，那每个数据页存的数据会越多，相同数量级而言，B+Tree 越矮胖，I/O操作越少，查询越高效。-
数据类型越小，在查询时进行的比较操作越快。
-
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以
放更多的记录，树型层级就越少，I/O操作越少，查询越高效。
-
-
⑦
使用字符串前缀创建索引.InnoDB 使用较长的字符串字段创建索引，会有以下两个问题：
-
B+Tree 需要把该列完整的字符串存储起来，更费时，且字符串越长，
在索引中占用的存储空间越大，意味着数据页存放的记录条越少。 -
B+Tree 中索引列存储的字符串很长，在做字符串
比较时会占用更多的时间。
解决办法：截取字段的前面一部分内容建立索引，这就叫前缀索引。虽不能精准定位记录 和 无法支持该字段使用order by 索引排序，但能定位到相应前缀所在位置。
前缀索引既节约空间，又减少字符串的比较时间。-
select count(distinct left(字段，n)) / count(*) as sub_n from table ; 通过 该语句查询字段前n个字符的区分度，n越小区分度越接近1，该索引创建的就越高效。
-
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
-
索引的长度和区分度是一对矛盾体，一般字符串类型数据，长度为20 的索引，区分度会高达 90% 以上。
-
-
⑧
区分度高（散列性高）的列适合作为索引.列的基数是指，某一列中不重复数据的个数。例如某列包含以下数据：2、5、2、4、3、5，该列中6条数据的基数为 4。
在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中.-
select count(distinct 字段)/ count(*) from tb_table； 计算字段的区分度，越接近1越好，一般超过 33% 就算比较高效的索引了。
-
联合索引把区分度高（散列性高）的列放在前面。
-
-
⑨
使用最频繁的列放到联合索引的左侧.最左前缀原则，提高联合索引的使用率。 -
⑩
在多个字段都有创建索引的情况下，联合索引优于单值索引.
2.2、限制索引的数目
实际工作中需要平衡创建索引的优劣势，并不是索引创建的越多越好，要考虑内存等多方面因素。所以我们需要限制单表上的索引数量，建议单张表索引数量不超过6个。原因如下：
- ①
每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 - ②
索引会影响 INSERT、DELETE、UPDATE 等语句的性能。因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。 - ③
优化器在选择如何优化查询时，会根据统一信息，对每个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有多个索引都可以用于查询，会增加 MySQL 优化器生成执行计划的时间，降低查询性能。
2.3、不适合创建索引的情况
-
①
在 WHERE 中使用不到的字段，不要设置索引. -
②
数据量小的表，最好不要使用索引. -
③
有大量重复数据的列（区分度不高）不要建立索引. -
④
避免对经常更新的表创建多个的索引.- 经常修改的字段创建索引，会导致InnoDB 的 B+Tree 不得不频繁地做页分裂操作。
-
⑤
不建议用无序的值作为索引.- 例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。
-
⑥
删除不再使用或者很少使用的索引.- 目的：减少无用索引对更新操作的影响。
-
⑦
不要定义冗余或重复的索引.
3、索引分析
3.1、索引优劣势
过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。同时，建立索引会占用磁盘空间的索引文件。
3.2、索引语句分析
3.2.1、删除索引
- 删除索引脚本
// 删除索引，第一种语法
ALTER TABLE table_name DROP INDEX index_name;
// 删除索引，第二种语法
DROP INDEX index_name ON table_name;
说明：添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除。删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
3.2.2、查看索引
- 查看索引脚本
// 查询表索引语法
SHOW INDEX FROM table_name;
// 例：查询 exp 表的索引
SHOW index from exp;
3.2.3、SHOW INDEX 语句说明
| 关键字 | 关键字说明 |
|---|---|
| Table | 表示创建索引的表 |
| Non_unique | 表示索引非唯一，1-代表是非唯一索引，0-代表唯一索引 |
| Key_name | 表示索引的名称 |
| Seq_in_index | 表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。 |
| Column_name | 表示定义索引的列字段 |
| Sub_part | 表示索引的长度 |
| Null | 表示该字段是否能为空值 |
| Index_type | 表示索引类型 |
3.2.3、EXPLAIN 语句说明
EXPLAIN 语句用于分析查询的SQL语句的执行，看一条查询的SQL语句是怎么去执行的，有没有用到索引，需不需要回表查询，需不需要额外排序的情况，然后我们针对SQL语句执行来进行一些特定语句。
| 关键字 | 关键字说明 | 备注 |
|---|---|---|
| id | id 指执行的优先级，id越大优先级越大；值相同的时候，从上往下，依次执行。 | - |
| select_type | select_type 行指定所使用的 SELECT 查询类型，这里值为 SIMPLE 表示简单的 SELECT，不使用 UNION 或子查询。其他可能的取值有：PRIMARY、UNION、SUBQUERY 等。 | SIMPLE（表示简单查询，其中不包括连接查询和子查询）、PRIMARY（表示主查询，或者是最外层的查询语句）、UNION（表示连接查询的第二个或后面的查询语句） |
| table | table 行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。 | - |
| type | type 行指定了本数据表与其他数据表之间的关联关系，从好到坏依次是：system,const,eq_ref,ref,range,index,all. | system（表中只有一条数据，引擎只能使MYISAM和MEMORY）、const（使用唯一索引或者主键，用where限制条件后返回一条数据，有且只有一条）、eq_ref（唯一性索引，对每个索引的查询只能返回匹配的唯一一条数据）、ref（非唯一性索引，对每个索引返回匹配的所有）、range（索引指定范围的行，where后边是范围）、index（查询索引的全部数据） 和 all （查询所有数据） |
| possible_keys | possible_keys 行给出了 Mysql 在搜索数据记录时，可选用的各个索引。 | - |
| key | key 行是 Mysql 实际选用的索引。 | - |
| key_len | key_len 行给出索引按字节计算的长度，key_len数值越小，表示越快。 | - |
| ref | ref 行给出了关联关系中另一个数据表里的数据列的名字。 | - |
| rows | rows 行是 Mysql 在执行这个查询时，预计会从这个数据表里读出的数据行的个数。 | - |
| extra | extra 行提供了与关联操作有关的信息。可能的取值有 using filesort 、using temporary、using index、using where、impossible where | using filesort （需要额外的排序，性能损耗）、using temporary（使用到了临时表，性能损耗） 、using index（索引覆盖，性能提升）、using where（需要回表查询）、impossible where（where子句永远为false） |
3.3、索引失效
以下几种情况会造成索引失效，使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。
- 1、
like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
- 2、
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
- 3、
组合索引，不是使用第一列索引，索引失效。
- 4、
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
- 5、
在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
- 6、
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0.
- 7、
对索引字段进行计算操作、字段上使用函数。
- 8、
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
4、InnoDB 的索引相关的几个特性
-
回表查询
-- tb_table 表中的主键为 c1,二级索引为c2,普通字段为c3、c4. select c1,c2,c3 from tb_table where c2 = 2; -- 该语句中，根据二级索引c2查到的聚簇索引的数据，再根据聚簇索引查到其他真实数据的过程，称为回表查询。 -- 二级索引的节点中，存的是聚簇索引字段和该二级索引字段。 -- 聚簇索引的叶子节点中，存放的是完整的行记录数据。
-
覆盖索引
-- 回表有代价，尽量去避免。当指定我们需要查询的列时，就有机会达到覆盖索引。 -- tb_table 表中的主键为 c1,二级索引为c2,普通字段为c3、c4. select c1,c2 from tb_table where c2 = 5; -- 通过二级索引查询的字段中，只包含二级索引和聚簇索引，就不需要进行回表，称为覆盖索引。
-
索引查询原理
-- tb_table 表中的c1为主键 ,c2为二级索引,c3为二级索引，c4普通字段. insert into tb_table (c1,c2,c3,c4) values (1,1,1,'红'); insert into tb_table (c1,c2,c3,c4) values (2,1,2,'黄'); insert into tb_table (c1,c2,c3,c4) values (3,2,1,'黑'); insert into tb_table (c1,c2,c3,c4) values (4,2,2,'绿'); -- 执行语句 select * from tb_table where c2=1 and c3=1;-
① c2 和 c3 都是索引列的情况下，优化器只会选择一个作为查询条件。
-
② 二级索引通过c2=1 过滤出两条数据的主键为 1 和 2，再回表查出这两行数据。
-
③ 最后在 server 层中过滤出 c3 = 1 的数据，以上涉及到了回表、内存过滤。
-
大部分情况下，
MySQL 只会使用一个索引列。除非特殊场景，优化器认为同时使用两个索引列的代价更低，就会把两个索引的数据分别过滤出来，再到内存中做交集运算。
-
-
索引下推
-- 索引下推是 MySQL 5.6及以上的版本才有，用来对查询进行优化。 -- 索引下推 是把本该在 server 层内存中进行筛选的条件，下推到存储引擎层来筛选判断。 -- tb_table 表中的c1为主键 ,c2、c3为联合索引，c4普通字段. select * from tb_table where c2 >1 and C3 =1; -- 以上没有索引下推，会先通过c2 筛选数据后，在到 server 层用 c3字段再过滤数据。 -- 有了索引下推，存储引擎发现在二级索引中有c3字段，就会直接对c3做过滤，避免了内存过滤操作。
系列文章：
一： 《搞懂 MySql 的架构和执行流程》
二： 《从InnoDB索引的数据结构，去理解索引》
三： 《从 Hash索引、二叉树、B-Tree 与 B+Tree 对比看索引结构选择》
四： 《MySQL 的索引分类和设计原则》
五： 《MySQL 优化思路篇》
六： 《理解MySQL的日志 redo、undo、binlog》
七： 《并发事务下，不同隔离级别可能出现的问题》
八： 《多维度梳理 MySQL 锁》
九： 《MySQL 之多版本并发控制 MVCC》
.
2540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
