MySQL入门教程（8）MySQL索引原理与使用
一、MySQL索引分类
MySQL官方对索引的定义是"帮助MySQL高效获取数据的数据结构"，通俗来讲索引相当于字典的音序表或书籍的目录，通过将索引包含的字段进行排序（默认升序）可以让存储引擎更快速的查询到需要的数据。比如将1-10000进行排序然后再查找指定的数字，肯定要比乱序更好找到。当一个表没有索引时，进行查询操作就会产生全表扫描、额外排序、临时表等现象发生，甚至还会因为一个DML操作锁住整张表。在MySQL中索引是在存储引擎层实现，而非服务器层，所以不同存储引擎具有不同的索引类型和特性。
1、按照数据结构分类，索引可以分为二叉树、红黑树（平衡二叉树）、B树\B+树、哈希索引
· 二叉树\红黑树
二叉树的每个节点最多只有两个子节点，并且左边的子节点比当前节点小，右边的节点比当前节点大。但是如果每次插入的数据都比之前的数据更大，那么整个二叉树就会越来越高，出现数据倾斜的问题。而类似红黑树这样的平衡二叉树在二叉树的基础上增加了自平衡的操作，确保每个节点的左子树和右子树的高度差不能超过 1，但是随着数据变多会出现整个树过高的问题，究其原因就是它们都是二叉树，每个节点只能保存 2 个子节点。
· B树\B+树
B树\B+树的出现解决了二叉树的高度问题，它们不再限制一个节点只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度，所以二者也叫做多路搜索树（多叉树）。
B树的叶子节点和非叶子节点都有可能存放数据，而B+树将所有数据都保存在了叶子节点，这些叶子节点里的数据是有序存放的，这样在进行范围查找时性能更好。它们会先申请一块磁盘空间（默认16K，由innodb_page_size控制）作为索引页用于存放索引元素。假设存放的是占用磁盘空间为8B的BIGINT数据类型，加上占用磁盘空间6B的下一级索引磁盘地址信息数据，那么一个16K的索引节点可以存放16K/(8B+6B)=1170个索引元素。当B+树高度为3时可以存放的数据已经可以达到2000多万（1170*1170*1170），也就是说经过3次I/O操作就可以从2000多万数据中找到自己需要的那行。
· 哈希索引
哈希索引适用于在大量数据中进行=或者IN这样的等值精确查询，由于会对字段进行哈希运算，所以每个字段有一个唯一的哈希值，这样查询的性能就会非常高效。但是哈希索引无法进行排序和范围查询等模糊查询，Hash索引还有一个缺陷是存储的数据是无序的，在ORDER BY时还需要对数据重新排序。InnoDB引擎本身不支持HASH索引，但是提供了一个特殊的自适应哈希索引机制（Adaptive Hash Index），当InnoDB发现某些索引值被使用非常频繁时会自动在内存中基于BTree索引创建一个哈希索引，使得BTree索引也具有哈希索引的优点。由于这是一个全自动的内部行为，用户无法进行控制或者配置，只能通过innodb_adaptive_hash_index选项来决定是否开启，默认为ON
2、从功能逻辑上说，索引可以分为主键索引、普通索引、唯一索引、全文索引
· 主键索引
主键索引也叫Primary Key，它属于聚簇索引，也是一种特殊的唯一索引。通过主键索引进行查询时不会有回表行为，性能更好。好比从字典中查找“数”这个字，只要找到shu的拼音就可以直接找到对应的数据行，不需要再二次查询。
在开发规范中，通常会要求每张表必须创建主键索引（一张表中也只能定义1个主键索引）。在MySQL中没有直接的语法来创建主键索引，而是通过定义主键约束来隐式创建。定义主键约束时MySQL会同时创建主键索引来保证唯一性和非空性。
对于非核心业务可以使用自增ID列作为主键。对于核心业务不建议使用类似UUID这种太过离散的数据作为主键。一是UUID占用的空间较大，另外UUID的数据不是有序的，这样在插入数据的时候会导致树的分裂频繁，排序性能差。可以通过有序UUID或者其他方式来构建一个全局唯一且单调递增的字段。
#假设ID列为主键索引，该语句使用主键查询，只需要搜索ID列这棵B+树 select * from T where ID=500 #假设name列为普通索引，该语句需要先搜索name索引树得到ID，然后再到ID索引树搜索，这个过程称为回表 select * from T where name='tanglu'
· 普通索引
普通索也可以称为辅助索引、二级索引，是最常用的索引类型，属于非聚簇索引。普通索引所在的字段的值允许重复，这里还以查找“数”字为例，如果按照部首查找的方式就需要先找到“数”字的偏旁部首，然后通过目录知道“数”字存放到第多少页，然后再去指定的页码找这个字。由于非聚簇索引的叶子节点存储的是主键的值，所以最终要还要再通过拿到的主键索引进行二次查询，有多少条数据就会查询多少次，称为回表。普通索引可以使用change buffer特性。change buffer会使用buffer pool里的内存，通过参数 innodb_change_buffer_max_size来动态设置，比如该参数设置为50表示change buffer大小最多只能占用 buffer pool 的 50%。对于写多读少的业务场景（比如账单类、日志类系统），由于页面在写完以后马上被访问到的概率比较小，此时change buffer就可以存放很多数据，减少磁盘IO，提升性能更明显。而如果业务在写入之后马上会做查询，那么change buffer中的数据会被merge到磁盘，反而增加了change buffer的维护代价。change buffer和redolog比较容易混淆，简单来说redo log主要节省随机写磁盘的IO消耗（转成顺序写），change buffer主要节省的则是随机读磁盘的 IO 消耗。
· UNIQUE（唯一索引）
添加唯一约束会自动创建唯一索引，唯一索引上的字段不允许重复，和主键索引的区别在于允许字段的值为NULL，一张表可以创建多个唯一索引。对于唯一索引来说，由于字段有唯一性，如果一个SQL已经满足查询条件就会停止继续检索。但是也由于判断SQL是否违反唯一约束的过程需要将数据页读入内存进行，不能使用change buffer特性，所以效率并不会比普通索引高。
· 全文索引
MySQL 5.7之前只有MyISAM引擎支持全文索引，现在基本使用ES解决全文检索问题，不会交给MySQL
3、按照物理实现方式，索引可以分为聚簇索引和非聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引控制数据的物理存储顺序，而二级索引只是帮助加速查询的额外结构，聚簇索引的非叶子节点存放的都是索引信息，叶子节点才存放数据本身。
聚簇索引由InnoDB存储引擎自动创建，并不需要显式创建。MySQL会优先把显式创建的主键索引自动作为聚簇索引，如果没有为表定义主键索引，则选择第一个唯一索引作为聚簇索引。如果表中没有这样的唯一索引则会基于全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引，由于这种索引属于整个实例级别，所以存在性能问题。
4、按照作用的字段个数进行划分，索引可以分为单列索引和联合索引
联合索引也称为组合索引，是指一个索引中同时包含多个列，在多个字段都要创建索引的情况下创建联合索引优于单列索引。比如已经有了 (a,b) 这个联合索引后就不需要单独在a上建立索引了；又比如一个查询语句同时使用了group by和order by子句，那么就可以为group by和order by的字段创建联合索引，在创建这样的联合索引时需要把group by写在前面。
联合索引在排序时会在完成第一个字段排序的基础上对第二个字段进行排序，依次类推。关于联合索引有一句顺口溜“带头大哥不能死，中间兄弟不能丢”，也就是说使用联合索引时需要遵循最左匹配原则，在进行查询时必须从联合索引最左侧的字段开始（查询条件中的字段书写顺序可以不和索引建立时一致，优化器会帮忙进行排序，但是必须包含最左侧的字段），因为脱离了最左侧的字段会让整个排序动作丢失。另外如果在联合索引中使用了范围查询，那么范围查询之后的字段即便有索引也会失效。通常建议将WHERE条件中经常同时出现的列放在联合索引中。最左前缀可以是联合索引的最左N个字段，也可以是具体字符串的最左N个字符。联合索引也可以用来优化where ... order by语句，比如 where a=? order by b,c，就可以创建一个索引 (a,b,c)。按照联合索引的字段顺序去进行order by排序，可以利用联合索引树里的数据有序性。
二、MySQL索引特性
1、覆盖索引
当一个查询语句中需要查询的字段或者过滤条件都是索引列（反过来说就是一个索引中包含了所有需要查询的字段或者过滤条件）就属于覆盖索引。覆盖索引无需回表，可以大大提升查询效率，是一个SQL优化的常用方法（由于SELECT * FROM 这样的语句没有明确字段，无法使用覆盖索引）。在对联合索引进行变更的时候需要注意，如果本身用到了覆盖索引的SQL可能会因为索引变更导致失效，因为二级索引的叶子节点都包含了主键值，假设ID为主键索引，那么col(A)实际等同于col(A,ID)这样一个联合索引，如果将col(A)扩展为col(A,B)，那么会导致WHERE A = 5 ORDER BY ID这样的查询无法再用到索引
#比如worker表有索引(type,salary)，以下查询就会使用到覆盖索引特性，不用回表 select type,salary from worker where type='b' #如果一个有普通索引的字段结合主键字段进行查询，也可以实现覆盖索引。比如下表中id是主键，last_name字段有普通索引 SELECT id,last_name FROM students WHERE last_name='tanglu'
2、前缀索引
索引在默认情况下是将整个字段的长度进行包含，而前缀索引则是将字段指定长度作为索引，通常用于TEXT或者很长的VARCHAR字段上设置前缀索引。使用前缀索引可以让索引长度变短以达到减少树的高度的作用，占用空间也会更小。在定义好合理的长度前提下可以做到既节省空间又不用额外增加太多的查询成本（可以通过计算字段区别度来设置合理的长度，越接近1越好）。另外使用前缀索引就用不上覆盖索引对查询性能的优化了，因为InnoDB最终都要回表进行查询
mysql> alter table user add index index1(city); mysql> alter table user add index index2(city(4)); #4个字符的前缀索引 insert into user values(beijing) #匹配 insert into user values(beiping) #不匹配 insert into user values(beijing) #匹配 #计算字段区分度 SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS sub10 , --截取前10个字符的选择度 COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS sub11 --截取前15个字符的选择度 FROM test_table
3、索引下推
索引下推优化特性简称ICP，在执行计划中表示为Using index condition，MySQL 5.6以后开始支持，该特性只能选择开启或者关闭，没有其他额外配置参数。在没有索引下推特性之前，如果查询条件非主键索引，存储引擎会把每一条查询结果都进行回表操作。使用索引下推特性后，存储引擎会先把查询出来的数据进行一次过滤，只拿这些符合条件的数据进行回表查询，这样可以提高查询效率。索引下推特性主要在进行多条件查询时出现，当查询条件的左侧条件基于索引进行了范围查找，而后续字段如果无法使用索引时，MySQL为了减少回表次数，在查询出符合左侧条件的数据以后，会下推给后面的查询条件，等完成后续条件过滤以后再进行回表。
#id索引下推给name字段 SLECT * FROM t1 where id=00001 and name like '%唐%'
三、索引创建原则
优秀的索引应该达到三个标准，俗称三星索引
· 索引中包含了WHERE条件需要的所有列
· 可利用索引完成排序，即索引中包含了ORDER BY列，这样就不再需要进行额外的filesort操作。需要注意的是所有的排序操作都是在WHERE条件过滤后再执行的，所以如果WHERE条件能过滤大部分数据，那么剩下的少量数据出现了排序影响也不大
· 索引中包含了查询中需要的所有列，无需回表，即满足索引覆盖
1、索引并不是越多越好
由于索引会不断的对字段进行排序，对数据的插入或者更新会带来一定的性能影响（这也是为什么导入大量数据时，先导入数据再建立索引比先创建索引再导入数据更快的原因）。除此之外，每个索引都会占据额外的存储空间。所以索引创建不能过度，通常单表不应该超过5个。否则就应该考虑表设计的合理性。如果某个字段存在多个索引，SQL优化器会分析所有可能的执行计划，然后选择一个成本（cost）最低的进行操作，这种机制叫做CBO（Cost-based Optimizer，基于成本的优化器）。
2、适合建立索引的字段
· 高选择性字段：指字段重复指很少，即尽量选择有唯一值的字段来创建索引，比如ID、身份证号、电话号码、学号等。而类似性别这样的字段没有创建索引的必要。下面的语句可以计算出指定列的选择性
select count(distinct test_column)/count(*) from test_table; #值越接近1，说明在整张表里的重复性越低，选择性就越高，值低则相反
· 经常作为WHERE条件或者GROUP BY、ORDER BY进行分组排序的字段需要建立索引，如果同时出现GROUP BY和ORDER BY操作，需要建立联合索引，GROUP BY在前，ORDER BY在后。由于排序操作都是在WHERE条件过滤后再执行的，所以如果WHERE条件能过滤大部分数据，那么剩下的少量数据出现了排序影响也不大。当范围条件和GROUP BY、ORDER BY字段出现二选一时，可以先看看条件字段所过滤的数据是否足够多并且排序的数据比较少，这样就可以优先在范围字段上加索引。对于UPDATE或者DELETE操作，如果WHERE字段有索引一样可以大幅提高效率
#同时出现GROUP BY和ORDER BY操作 SELECT id,COUNT(*) FROM student_info GROUP BY id ORDER BY create_time limit 100 #建立联合索引 ALTER TABLE student_info ADD INDEX idx_id_ctime(id,create_time)
· 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。通过以下命令可以查询表和索引占用的硬盘大小
show table status like table_name \G #关注data_length和index_length
3、无需创建索引的情况
· 少量数据无需建立索引，比如200万行以内的小表可以不用建索引，全表扫描可能会更快
· 不建议用无序的值作为索引
· 有大量重复数据的列不用创建索引
4、索引变慢的可能性
很少变动的表可以多创建索引，经常变动的表有索引但有可能出现索引丢失问题。当出现本来很快的查询语句突然变慢了，就要考虑这个问题，进行索引重建
四、MySQL索引日常维护命令
1、创建索引
MySQL支持使用多种方法创建索引，比如使用CREATE TABLE语句在创建表的时候同时创建索引、ALTER TABLE语句修改表结构的时候创建索引以及CREATE INDEX为已经存在的表创建索引
· 创建普通索引（MySQL的InnoDB表加普通索引和唯一索引的时候，支持在线加索引，不会阻塞线上的读写操作）
#通过CREATE INDEX语句增加索引 CREATE INDEX idx_name ON student(name) #为student表的name列创建名为idx_name的索引 #通过ALTER TABLE语句增加索引 ALTER TABLE student ADD idx_name(name) #通过CREATE TABLE语句在创建表的同时创建索引 CREATE TABLE table1 (id int,name varchar(10),age int,INDEX idx_name ( name(10))); #创建table1这张表时给name列建立普通索引，长度为10，名为name_index CREATE TABLE table2 (id int,name varchar(10),age int,unique index id_index (id)); #给ID列创建一个名为id_index的唯一索引
· 创建唯一索引
ALTER TABLE books ADD UNIQUE uk_idx_book_name(book_name)
· 创建组合索引
CREATE TABLE table3 (id int , name varchar(10) , age int , index zuhe_index (id,name,age) ); #组合索引遵循从左匹配原则，必须要带有最左边ID列的查询才会使用到索引 # 组合索引最左匹配使用效果 EXPLAIN select name,age from table3 where id<3 \G EXPLAIN select name,age from table3 where id<3 and age <50 \G EXPLAIN select name,age from table3 where age <50 \G #possible_keys是null，说明没有匹配到索引
· 创建全文索引
CREATE TABLE test4( id INT NOT NUll, name VARCHAR(20), info VARCHAR(255), FULLTEXT INDEX full_idx_info(info(50))
2、查询表索引
mysql > SELECT * FROM informaton_schema.statistics WHERE table_schema='database_test'; #查询指定库中的所有索引 mysql > SHOW CREATE TABLE table_name; #通过查看表结构的方式查看索引 mysql > SHOW INDEX FROM table_name; #查询表中存在的索引
3、删除表索引
删除表中的列时，如果该列是索引的组成部分（比如name列是name,age列的联合索引，那么删除name列后联合索引就会剔除name列），那么该列会自动从索引中被删除。如果组成索引的列都被删除，那么索引也会自动被删除
#方法1：drop index 索引名 on 表名 DROP INDEX idx_name ON student #方法2：alter table 表名 drop index 索引名 ALTER TABLE test2 DROP INDEX idx_name
五、索引失效的原因
在SQL查询中有很多种原因都会导致索引失效，下面是一些常见的原因（这些原因跟数据库版本、查询数据量、数据选择度等都有关系，优化器会基于查询成本进行考虑，不是说下列情况一定会导致索引失效）
左模糊或者全模糊查询
如果使用like子句进行全模糊查询或左模糊查询（%在左边）将导致索引失效（在有覆盖索引的时候使用%开头进行模糊查询是有可能使用到索引的）
SELECT * FROM `user` WHERE `name` LIKE '%linux';
数据类型错误
当使用了错误的数据类型，导致数据库发生隐式转换时会导致索引失效，可以使用一些内置函数先进行类型转换
SELECT * FROM `user` WHERE height='180'; #如字段类型本身为int，where条件用了字符串类型
SELECT name,hire_date,department_id FROM employees WHERE department_id IN(80,90,100) AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d'); #使用函数将字符串转换为日期类型使用函数
在SQL语句中如果在不恰当的位置使用了函数运算会导致索引失效。通常SELECT后面的函数不影响索引、WHERE条件表达式右边不影响索引，但是WHERE条件表达式左边一定会导致索引失效而全表扫描，这个时候要想办法将函数运算改为范围查找
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03'; #即便create_time字段有索引也会因为函数而失效 SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01' #即便register_date有索引，但是索引的本质是排序，所以只会对register_date字段排序，不会对DATE_FORMAT(register_date) 排序
包含空字符串
索引不存储空值，如果没有对索引列设置非空约束（NOT NULL），数据库会认为索引列可能存在空值导致失效。所以建议在进行表结构设计的时候最好为每个字段设置为NOT NULL，可以将默认值设置为空字符串
SELECT * FROM `user` WHERE address IS NULL #不走索引 SELECT * FROM `user` WHERE address IS NOT NULL; #走索引
进行数学运算
对索引列进行运算会导致索引失效，包含+、-、*、/、!=、 <>、IS NOT NULL。因为索引本身是排序的，但是加入运算或者不等判断后会，数据库需要对每一行进行判断处理，这样就让排序失去了意义。但是这里并不绝对，在有覆盖索引的时候使用 != 有可能使用到索引；当一个NULL值比例较高的行上进行IS NOT NULL判断的时候也有可能使用索引
SELECT * FROM `user` WHERE age - 1 = 20;
不符合最左匹配原则
在复合索引中索引列的顺序至关重要，如果不是按照索引的最左列开始查找，则无法使用索引。但是如果一个联合索引包含了A,B,C三列，在查询的时候只要同时使用了A,B,C三列，那优化器会自动对顺序进行调整保证能使用上索引，比如A,C,B。但是如果缺少了某个字段就必须符合最左匹配原则了。另外如果在联合索引中使用了范围查询也会导致最左匹配原则的停止
select * from linuxe where a=1 and c=3 and b=2 #如果是全等值查询，顺序即便和符合索引中不一致也是可以用到索引的
全表扫描或许更快
在MySQL的设计中，当查询结果达到了原表中的一定比例（大概30%左右），MySQL优化器会认为没有必要再使用索引，而直接采用全表扫描。因为全表扫描是顺序IO，而使用二级索引会有回表行为，这是随机IO。比如对于连续的数值使用BETWEEN来代替IN，因为IN和NOT IN所定义的值是不确定的，IN在部分情况下可以用到索引，NOT IN不会用到索引，而BETWEEN定义的是一个连续的区间可以用到索引
# IN不走索引 SELECT id, name, salary FROM worker WHERE salary IN (1, 2, 3); # BETWEEN走索引 SELECT id, name, salary FROM worker WHERE salary BETWEEN 1 AND 3;
隐式转换
多表进行关联查询时如果关联条件数据类型或字符集不同会产生隐式转换导致索引失效
多表关联查询
多表关联查询时，如果排序列不属于驱动表索引会失效。对于内连接来说，优化器可以自行选择驱动表，如果表的连接条件中只有其中一个字段有索引，则索引字段所在的表会被作为驱动表；如果两个字段都存在索引，优化器会选择数据量小的表作为驱动表
OR操作符
当OR操作符左右条件存在没有索引的列时会导致整个查询的索引失效，因为某字段没有索引代表该列必须全表扫描，所以其它列上即便有索引也会失效
范围查询
如果有使用联合索引中的某个字段进行范围查找，需要把范围查找列放在索引的最右边
CREATE INDEX idx_age_name_cid ON student(age,name,classid) #如果classid列不在索引的最右边，那么下面的语句就无法完美使用到索引 SELECT * FROM student WHERE age=30 AND name='tang%' AND classid>20;
评论