文章目录
索引是什么？
是存储引擎用于快速找到记录的一种数据结构。
索引的作用
一个系统后期的性能调优大概就是数据库调优了，由于系统到了后期维护阶段，数据量庞大，索引对性能的影响愈发的重要。
索引的优化应该是对查询优化最有效的的手段了。索引能够轻易的将 查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好几个数量级。创建一个真正“最优”的索引经常需要重写查询语句。
初步认识索引
要了解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的目录部分：如果想在一本书中找到某个特定主题，一般会先看书的“目录”部分，找到对应页码。索引在MySQL中使用，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。
mysql>SELECT*FROM product p WHERE p.id ="000000280051064738303630"
如果在id中有建立检索，则MySQL将使用该索引找到对应的值为000000280051064738303630的id的数据行，也就是说 ，MySQL先在索引进行查找，然后返回所有包含该值的数据行。
索引可以包含一个或者多个列。如果索引包含多个列，那么这些列的顺序将很重要，因为MySQL只能高效地使用索引的最左缀列。创建一个含两个列和包含一个列的索引的索引的大小是不同的。
索引的类型
按照数据结构分类
B+TREE索引
在数据结构中有对B+TREE进行详细的讲述。其主要的特性就是查询快，而且是进行顺序存储的，并且每一个叶子节点到根的距离是相同的，加了索引的查询不需要遍历一整张表。
B+Tree索引能够加快访问速度，因为存储引擎不再需要进行全表扫描来获取数据行，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义 了子节点页中值的上限和下限。最终索引要么找到该值，要么该值不存在。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的叶子节点。
实际上很多存储引擎使用的是B+Tree，即每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历，B+Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
哈希索引
是一种特殊的数据库索引类型，它利用哈希表（Hash Table）的数据结构来存储索引项。哈希表通过哈希函数（Hash Function）将索引列的值转化为一个固定长度的哈希码（Hash Code），然后用这个哈希码作为索引项在表中定位数据记录的位置。这种方式使得对于等值查询（例如 WHERE column = value）能够非常快速，理想情况下接近O(1)的时间复杂度。但是缺点就是不适合范围查找，涉及到范围查找需要使用B+Tree索引。哈希索引的数据结构如下图所示，相当于数组+链表的数据结构，每一个哈希值占用一个数组位，相同的哈希值下有对应的冲突元素：
按功能逻辑进行分类
唯一索引
唯一索引要求所有索引列的值必须唯一，但允许空值存在。它用于确保数据的唯一性。同时也可以增加查找效率。
普通索引
普通索引也称为辅助索引，是最基础的索引类型，没有唯一性和空值的限制，没有任何的约束，主要用于提高查询效率。
主键索引
主键索引是一种特殊的唯一索引，不允许有空值。它通过主键字段建立，并自动创建。每个表只能有一个主键索引。
全文索引
全文索引主要用于对大文本字段进行搜索，支持复杂的查询模式，如自然语言搜索。其中InnoDB是不支持全文索引的，而MySAMDB支持，但是不支持行锁，这里不做展开了。一般实际应用中会使用第三方索引中间件如ES，ES提供分词技术。
按照
按照字段的个数进行划分
可分为单列索引和联合索引。
单列索引
在表中的单个字段上创建索引。单列索引值根据该字段进行索引。单列索引可以是唯一索引，也可以是普通索引，也可以是全文索引。只要保证该索引对应一个字段即可。一个表可以建多个单列索引。
多列（组合，联合）索引
是数据库中一种将多个列组合在一起创建的索引。它允许数据库系统通过一个单独的索引结构来快速定位满足多个条件的数据行。
小结
每个存储引擎都支持不同类型的索引
InnoDB ：支持B-tree、全文索引，不支持Hash索引。
MylSAM ：支持B-tree、全文索引，不支持Hash索引，不支持行锁。
Memory：支持B+Tree和Hash，不支持全文索引。
NDB：支持Hash索引，不支持B+Tree、全文索引等
Archive：不支持B+Tree、全文、Hash等索引。
索引的设计原则
为了使索引的使用效率更高，在创建索引时，必须考虑在哪字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好性能非常重要，设计索引时，应该考虑相应的准则。
数据准备
1.创建数据库表
#1.创建学生表和课程表CREATETABLE`student_info`(`id`INT(11)NOTNULLAUTO_INCREMENT,`student_id`INTNOTNULL,`name`VARCHAR(20)DEFAULTNULL,`course_id`INTNOTNULL,`class_id`INT(11)DEFAULTNULL,`create_time`DATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;CREATETABLE`course`(`id`INT(11)NOTNULLAUTO_INCREMENT,`course_id`INTNOTNULL,`course_name`VARCHAR(40)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=INNODBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;
2.创建模拟数据必需的存储函数
#函数1：创建随机产生字符串函数DELIMITER//CREATEFUNCTION rand_string(n INT)RETURNSVARCHAR(255)#该函数会返回一个字符串BEGINDECLARE chars_str VARCHAR(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255)DEFAULT'';DECLARE i INTDEFAULT0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i +1;ENDWHILE;RETURN return_str;END//DELIMITER;
#函数2：创建随机数函数DELIMITER//CREATEFUNCTION rand_num (from_num INT,to_num INT)RETURNSINT(11)BEGINDECLARE i INTDEFAULT0;SET i = FLOOR(from_num +RAND()*(to_num - from_num+1));RETURN i;END//DELIMITER;
如果创建函数出现报错。
This function has none of DETERMINISTIC......
由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。
主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使 用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置。
.查看mysql是否允许创建函数：
show variables like 'log_bin_trust_function_creators';
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1;# 不加global只是当前窗口有效。
mysqld重启，上述参数又会消失。永久方法：
windows下：my.ini[mysqld]加上：
log_bin_trust_function_creators=1
linux下：/etc/my.cnf下my.cnf[mysqld]加上：
log_bin_trust_function_creators=1
3.创建插入模拟数据的存储过程
# 存储过程1：创建插入课程表存储过程DELIMITER//CREATEPROCEDURE insert_course( max_num INT)BEGINDECLARE i INTDEFAULT0;SET autocommit =0;#设置手动提交事务REPEAT#循环SET i = i +1;#赋值INSERTINTO course (course_id, course_name )VALUES(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
ENDREPEAT;COMMIT;#提交事务END//DELIMITER;
# 存储过程2：创建插入学生信息表存储过程DELIMITER//CREATEPROCEDURE insert_stu( max_num INT)BEGINDECLARE i INTDEFAULT0;SET autocommit =0;#设置手动提交事务REPEAT#循环SET i = i +1;#赋值INSERTINTO student_info (course_id, class_id ,student_id ,NAME )VALUES(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
ENDREPEAT;COMMIT;#提交事务END//DELIMITER;
4.调用函数
CALL insert_course(100);CALL insert_stu(1000000);
哪些适合创建索引
1.字段的数值有唯一性
索引本身就是起到约束作用，比如唯一索引、逐渐主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如有某个字段的值具有唯一性，可以直接创建唯一索引，或者主键索引。唯一性可以是多个字段的组合，多个字段组合创建一个唯一索引也是可以的。
2.频繁作为WHERE查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。
3. 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立组合索引 。
这句举个例子会比较好理解，比如下SQL：
SELECT student_id,COUNT(*)AS num FROM student_info
GROUPBY student_id
ORDERBY create_time DESCLIMIT100;
如果分别在字段student_id和create_time创建索引，那么只有student_id字段上加的索引生效，而create_time上加的索引失效。这种情况就要创建student_id和create_time的组合索引。MySQL8.0中排序加DESC索引不会失效，5.7则会失效。
注意：在创建索引时，GROUP BY上的字段必须放在前面。
4. UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：
SELECTDISTINCT(student_id)FROM`student_info`;
运行结果（600637 条记录，运行时间 0.683s ）
如果我们对 student_id 创建索引，再执行 SQL
SELECTDISTINCT(student_id)FROM`student_info`;
语句：运行结果（600637 条记录，运行时间 0.010s ）
你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
6.多表 JOIN 连接操作时，创建索引注意事项
首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。
最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。这是因为索引在遇到聚合函数时会失效，MySQL会自动把varchar转为int，比如10 = ‘10’，这种情况MySQL会自动把字符varchar类型的‘10’转为10，导致了索引失效。
举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：
SELECT s.course_id, name, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE name ='462eed7ac6e791292a79';
运行结果（1 条数据，运行时间 0.189s ）
其中course_id如果在student_info和course 表中数据类型不一样，那么就是即使在course_id上加索引也会失效。在开发中一定要注意这个问题。
这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s 。
7.最左匹配原则
MySQL的最左匹配原则（Leftmost Prefixing）是指在使用索引进行查询时，MySQL会从索引的最左边的列开始匹配，并且只能使用一个索引中的列进行查询。如果查询条件不从最左边的列开始，那么MySQL将不会使用这个索引。
举个例子，假设有一个组合索引（a, col2, col3），口语化的表述就是，第一列a是带头大哥，如果where没有用到a，索引将会失效。当执行查询时，哪些会索引失效，哪些会没失效，如下代码所示：
使用到索引
SELECT*FROMtableWHERE a =1;SELECT*FROMtableWHERE a =1AND b =2;SELECT*FROMtableWHERE a =1AND b =2AND c =3;`没能使用索引
SELECT*FROMtableWHERE b =2;SELECT*FROMtableWHERE b =2AND c =3;SELECT*FROMtableWHERE c =3;
8.使用列的类型小的创建索引
我们这里所说的 类型大小 指的就是该类型表示的数据范围的大小。
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT。这是因为:
。数据类型越小，在查询时进行的比较操作越快
数据类型越小，旁索引占用的存储空间就越少，在一个数据页内就可以 放下更多的记录，从而减少磁盘I/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的 主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的!0。
9.使用字符串前缀创建索引
假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:
1.B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越
大。
2.如果B+树索引中索引列存储的字符串很长，那在做字符串 比较时会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索引，这个就叫 前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既 节约空间，又 减少了字符串的比较时间，还大体能解决排序的问题。
例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引。
createtable shop(address varchar(120)notnull);altertable shop addindex(address(12);
问题是，截取多少呢?截取得多了，达不到节省索引存储空间的目的;截取得少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?这将是此类问题的关键之处。
先看一下字段在全部数据中的选择度:
selectcount(distinct address)/count(*)from shop;片
通过不同长度去计算，与全表的选择性对比:
公式如下:
count(distinctleft(列，索引长度))/count(*)
例口:
selectcount(distinctleft(address,18))/count(*)as sub18,-- 截取前18个字符的选择度count(distinct left(address,15))/count(*) as sub11.--截取前15个字符的选择度count(distinct left(address,20))/count(*)as sub12.--截取前20个字符的选择度count(distinct left(address,25))/count(*)as sub13 -- 截取前25个字符的选择度from shop;
引申另外一个问题：索引列前缀对排序的影响
如果使用了索引列前缀，比方说前边只把address列的 前12个字符放到了二级索引中，下边这个查询可能就有点儿尴尬了 :
SELECT*FROM shop
ORDERBY address
LIMIT12;
因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式 无法支持使用索引排序，只能使用文件排序。
拓展:Alibaba《Java开发手册》
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明:索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会 高达 98%以上，可以使用 count(distinct left(列名,索引长度))/count(*)的区分度来确定。
10.区分度高的列适合作为索引
列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
可以使用公式 select count(distinct a)/count(*)from t1 计算区分度，越接近1越好，一般超过 33%就算是比较高效的索引了。
*拓展:联合索引把区分度高(散列性高)的列放在前面。
11.使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
12.在多个字段都要创建索引的情况下，联合索引优于单值索引
索引数目的限制
在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量 不超过6个。原因:
① 每个索引都需要占用 磁盘空间，索引越多，需要的磁盘空间就越大。
② 索引会影响 INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
③ 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的 索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。
哪些情况不适合创建索引
1. 在where中使用不到的字段，不要设置索引
WHERE条件 (包括 GROUP BY、ORDER BY) 里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子：
SELECT course_id, student_id, create_time
FROM student_info
WHERE student_id =41251;
因为我们是按照 student_id 来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在SELECT字段中。
2. 数据量小的表最好不要使用索引
如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引 对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
3. 有大量重复数据的列上不要建立
在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的"性别"字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
4. 避免对经常更新的表创建过多的索引
第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却降低更新表的速度。
5. 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等。
6. 删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
7. 不要定义夯余或重复的索引
① 冗余索引
举例：建表语句如下
CREATETABLE person_info(
id INTUNSIGNEDNOTNULLAUTO_INCREMENT,
name VARCHAR(100)NOTNULL,
birthday DATENOTNULL,
phone_number CHAR(11)NOTNULL,
country varchar(100)NOTNULL,PRIMARYKEY(id),KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),KEY idx_name (name(10)));
我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一 个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有 什么好处。
② 重复索引
另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样。
CREATETABLE repeat_index_demo (
col1 INTPRIMARYKEY,
col2 INT,UNIQUE uk_idx_c1 (col1),INDEX idx_c1 (col1));
我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就 会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避。
总结
介绍了索引，是什么，数据结构，有哪些分类，设计原则，后面还会持续更新。也是比较粗浅的，后面会逐渐加大深度。
版权归原作者 一个儒雅随和的男子 所有， 如有侵权，请联系我们删除。