MySQL入门教程（8）MySQL索引介绍与日常管理
一、MySQL索引介绍
MySQL官方对索引的定义是"帮助MySQL高效获取数据的数据结构"，通俗来讲索引相当于字典的音序表或书籍的目录，通过将索引包含的字段进行排序可以让存储引擎更快速的查询到需要的数据，比如将1-10000进行排序然后再查找指定的数字，肯定要比乱序更好找到。如果一个表没有索引，在查询时就会产生全表扫描、额外排序、临时表等现象发生，甚至还会因为一个DML操作锁住整张表。索引本身需要消耗磁盘空间来存放数据，而且因为索引会不断的对字段进行排序，所以对数据的插入或者更新会带来一定的性能影响。在MySQL 8.0 版本中InnoDB存储引擎支持的索引有B树、B+树、哈希索引、全文索引等，但使用最广泛的还是B+树索引
· 二叉树
早期的二叉树存在随着数据增多树越来越高的问题（比如每次插入的数据都比之前的数据更大，这样树就会倾斜）。后期出现的红黑树（也叫平衡二叉树）进行了相对的优化但是也有树的深度高的问题
· B树、B+树
B树也叫平衡二叉树，而B+树是由B树变体而来的多路搜索树（多叉树），其定义与B树基本相同。但是B+树将所有数据都保存在了叶子节点，叶子节点里的数据是经过了排序存放的，非叶子节点可以看成是索引部分。而B树的叶子节点和非叶子节点都有可能存放数据，因为它无法稳定的按照树的结构走下去，所以在查询上性能也没有B+树优秀。B+树是目前为止排序最有效率的数据结构，B+树的高度通常只有3~4 层，所以磁盘IO最多也就只需要1-4次。通常主键类型为INT（占用4字节）或者BIGINT（占用8字节），指针类型一般也是4个字节或者8个字节。而B+树默认是会申请16K（由innodb_page_size控制）的磁盘空间作为页，那么一个页大概可以存放16KB/(8B+8B)=1000个键值，当树高为3的时候就可以存放10^3*10^3*10^3=10亿条记录（如果还不满足需求就应该考虑使用分表手段来进行业务拆分了）
· 哈希索引
哈希索引适用于在大量数据中进行=或者IN这样的等值精确查询，由于会对字段进行哈希运算，所以每个字段有一个唯一的哈希值，这样查询的性能就会非常高效。但是哈希索引无法进行排序和范围查询等模糊查询，Hash索引还有一个缺陷是存储的数据是无序的，在ORDER BY时还需要对数据重新排序。InnoDB引擎本身不支持HASH索引，但是提供了一个特殊的自适应哈希索引机制（Adaptive Hash Index），当InnoDB发现某些索引值被使用非常频繁时会自动在内存中基于BTree索引创建一个哈希索引，使得BTree索引也具有哈希索引的优点。由于这是一个全自动的内部行为，用户无法进行控制或者配置，只能通过innodb_adaptive_hash_index选项来决定是否开启，默认为ON
二、MySQL索引特性与分类
1、索引分为主键索引、普通索引、唯一索引、联合索引、全文索引5种不同类型
· PRIMARY KEY（主键索引）
一种特殊的唯一索引，在一张表中只能定义一个主键索引。对于主键索引来说建议使用高选择性字段，含有大量非重复值的列；不建议使用数据太过离散的字段或者和业务相关字段（比如UUID），因为UUID并不能保证插入是有序的，这样树的分裂就会比较频繁；另一个原因则是整型占用空间更少，排序性能也更好
· INDEX（普通索引、辅助索引）
对于MySQL来说主键索引一定是聚簇索引。而其它非聚簇索引都属于二级索引，也叫做辅助索引。其叶子节点存储的是主键的值，基于非主键索引查询时最终要再使用主键索引进行查询，这个过程会多扫描一棵索引树，称为回表。普通索引允许字段的值重复，是最常用的索引类型。对于普通索引来说，如果一个SQL已经满足查询条件，还会接着查找下一个记录，直到查出所有满足条件的记录。普通索引可以使用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特性，所以效率并不会比普通索引高。
· 组合索引（联合索引）
联合索引是指一个索引中包含了多个列，在多个字段都要创建索引的情况下，联合索引优于单列索引。联合索引在排序时会将第一个字段排序完成后，再此基础上对第二个字段进行排序，以此类推。通常建议将WHERE条件中经常同时出现的列放在联合索引中。如果要使用联合索引需要遵循最左匹配原则，在进行查询时必须包含联合索引最左侧的字段，因为需要按照字段进行挨个排序，如果跳过了前面的字段，则后面的字段获取到的数据是未经过排序的。关于联合索引有一句顺口溜“带头大哥不能死，中间兄弟不能丢”。最左前缀可以是联合索引的最左N个字段，也可以是具体字符串的最左N个字符。当已经有了 (a,b) 这个联合索引后就不需要单独在a上建立索引了。联合索引可以用来优化where ... order by语句，比如 where a=? order by b,c，就可以创建一个索引 (a,b,c)。按照联合索引的字段顺序去进行order by排序，可以利用联合索引树里的数据有序性。
· 全文索引
MySQL 5.7之前只有MyISAM引擎支持全文索引，现在基本使用ES解决全文检索问题，不会交给MySQL
2、索引特性
· 覆盖索引：当查询的字段以及过滤条件中只包含某个索引的列就称为覆盖索引（反过来说就是一个索引中包含了所有需要查询的字段或者过滤条件）。覆盖索引无需回表操作，也是一个SQL优化的常用方法。在对联合索引进行变更的时候需要注意，如果本身用到了覆盖索引的SQL可能会因为索引变更导致失效，因为二级索引的叶子节点都包含了主键值，假设ID为主键索引，那么col(A)实际等同于col(A,ID)这样一个联合索引，如果将col(A)扩展为col(A,B)，那么会导致WHERE A = 5 ORDER BY ID这样的查询无法再用到索引。还有一种情况是使用SELECT * 这样的语句也无法使用覆盖索引。
#比如worker表有索引(type,salary)，以下查询就会使用到覆盖索引特性，不用回表 select type,salary from worker where type='b' #如果一个有普通索引的字段结合主键字段进行查询，也可以实现覆盖索引。比如下表中id是主键，last_name字段有普通索引 SELECT id,last_name FROM students WHERE last_name='tanglu'
· 前缀索引：索引在默认情况下是将整个字段的长度进行包含，而前缀索引则是将字段指定长度作为索引，通常用于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
· 聚簇索引：主键索引属于聚簇索引，MySQL会优先把显式创建的主键索引自动作为聚簇索引。如果没有为表定义主键索引，则选择第一个创建的非空唯一索引作为聚簇索引。如果表中没有这样的唯一索引，MySQL会基于全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引，由于这种索引属于整个实例级别，所以存在性能问题。聚簇索引的索引和数据没有被拆分而是存放到了一个文件中（.ibd），其叶子节点存储了整行数据，也就是说索引即数据（MyISAM的数据和索引是分开到2个不同文件中的，属于非聚簇）
select * from T where ID=500 #假设ID列为主键索引，该语句则使用主键查询，只需要搜索ID列这棵B+树 select * from T where name='tanglu' #假设name列为普通索引，该语句需要先搜索name索引树，得到ID后再到ID索引树搜索，这个过程称为回表
三、MySQL索引日常管理语法
· 创建普通索引（MySQL的InnoDB表加普通索引和唯一索引的时候，支持在线加索引，不会阻塞线上的读写操作）
#通过ALTER语句增加索引 ALTER TABLE student ADD idx_name(name) #通过CREATE语句增加索引 CREATE INDEX idx_name ON student(name) #为student表的name列创建名为idx_name的索引 #在创建MySQL表的同时创建索引 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))
· 查询表索引
mysql > SELECT * FROM informaton_schema.statistics WHERE table_schema='database_test'; #查询指定库中的所有索引 mysql > SHOW INDEX FROM table_name; #查询表中存在的索引
· 删除索引
删除表中的列时，如果该列是索引的组成部分（比如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
四、索引创建原则
优秀的索引应该达到三个标准，俗称三星索引
· 索引中包含了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、索引变慢的可能性
很少变动的表可以多创建索引，经常变动的表有索引但有可能出现索引丢失问题。当出现本来很快的查询语句突然变慢了，就要考虑这个问题，进行索引重建
五、索引失效的原因
SQL是否使用索引，跟数据库版本、数据量、数据选择度等都有关系，七个字总结就是——"模型数空运最快"
1、模糊查询：使用like子句进行模糊查询时以%开头将导致索引失效（在有覆盖索引的时候使用%开头进行模糊查询是有可能使用到索引的）
SELECT * FROM `user` WHERE `name` LIKE '%linux';
2、数据类型错误：当使用了错误的数据类型，导致数据库发生隐式转换时会导致索引失效，可以使用一些内置函数先进行类型转换
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'); #使用函数将字符串转换为日期类型3、使用函数：使用了内部函数索引也会失效，这种情况可以将函数写在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) 排序
4、包含空字符串：索引不存储空值，如果没有限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。所以建议在进行表结构设计的时候最好为每个字段设置为NOT NULL，可以将默认值设置为空字符串
SELECT * FROM `user` WHERE address IS NULL #不走索引 SELECT * FROM `user` WHERE address IS NOT NULL; #走索引
5、进行数学运算：对索引列进行（+，-，*，/，!, !=, <>、IS NOT NULL）等运算，会导致索引失效（在有覆盖索引的时候使用!=是有可能使用到索引的）
SELECT * FROM `user` WHERE age - 1 = 20;
6、不符合最左匹配原则：在复合索引中索引列的顺序至关重要，如果不是按照索引的最左列开始查找，则无法使用索引。但是如果一个联合索引包含了A,B,C三列，在查询的时候只要同时使用了A,B,C三列，那优化器会自动对顺序进行调整保证能使用上索引，比如A,C,B。但是如果缺少了某个字段就必须符合最左匹配原则了
select * from linuxe where a=1 and c=3 and b=2 #如果是全等值查询，顺序即便和符合索引中不一致也是可以用到索引的
7、全表扫描或许更快：在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;
8、字符集不同：多表进行关联查询时如果关联条件数据类型或字符集不同，索引会失效
9、多表进行关联查询时，如果排序列不属于驱动表，索引会失效。对于内连接来说，优化器可以自己决定谁是驱动表谁是被驱动表
10、使用OR操作符进行多个条件判断时，若其中一个字段上没有索引（没有索引代表该列是全表扫描），其它列上即便有索引也会失效
11、如果有使用联合索引中的某个字段进行范围查找，需要把范围查找列放在索引的最右边
CREATE INDEX idx_age_name_cid ON student(age,name,classid) #如果classid列不在索引的最右边，那么下面的语句就无法完美使用到索引 SELECT * FROM student WHERE age=30 AND name='tang%' AND classid>20;
评论