认识MySQL索引
2018-07-07 16:24:39 
阿炯
现代计算机体系下，机器上粗粒度的资源就那么几种，无非是CPU，内存，硬盘，和网络。那么我们来看下SQL需要消耗哪些资源：
比较、排序、SQL解析、函数或逻辑运算需要用到CPU；
缓存数据访问，临时数据存放需要用到内存；
冷数据读取，大数据量的排序和关联，数据写入落盘，需要访问硬盘；
SQL请求交互，结果集返回需要网络资源。
那么SQL优化思路自然是减少SQL的解析，减少复杂的运算，减少数据处理的规模，减少对物理IO的依赖，减少服务器和客户端的网络交互。索引策略的组合最大化提升SQL优化性能：
独立的列： 减少SQL的解析
前缀索引和索引选择性： 减少数据处理的规模，减少对物理IO的依赖
多列索引：减少对物理IO的依赖
选择合适的索引列顺序： 减少数据处理的规模，减少对物理IO的依赖
聚簇索引： 减少数据处理的规模，减少对物理IO的依赖
覆盖索引： 减少对物理IO的依赖
使用索引扫描来做排序： 减少复杂的运算
返回必要的列： 减少对物理IO的依赖，减少服务器和客户端的网络交互
前缀索引是一种能使索引更小更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引(suffix index)也有用途例如，找到某个域名的所有电子邮件地址。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新的版本引入了一种叫"索引合并"(index merge)策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
索引合并策略有时候是一种优化的结果，但大多数时候说明表索引建得很糟糕：
当出现服务器对多个索引做相交操作时通常有多个AND条件，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时通常有多个OR条件，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到"查询成本"cost中，优化器只关心随机页面读取。这使得查询的成本被"低估"，导致该执行计划还不如直接走全表扫描。这样做不但消耗更多的CPU和内存资源，还可能影响查询的并发性，但如果是单独运行这样的查询，则往往忽略对并发性的影响。
如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用ignore index提示让优化器忽略掉某些索引。
索引的类型
B 树索引
B+树，所有叶子节点在同一层，每一个叶子节点包含指向下一个叶子结点的指针。
B-树索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。
全字匹配：和索引中的所有列进行匹配，如查找姓名为Cuba Allen、出生于1960-01-01的人；
匹配最左前缀：即只使用索引的第一列，如查找所有姓为Allen的人；
匹配列前缀：匹配某一列的值的开头部分，如查找所有以J开头的姓的人。这里只使用了索引的第一列；
匹配范围值：如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列；
精确匹配某一列并范围匹配另一列：如查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配；
只访问索引的查询：覆盖索引；
如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人。类似的，也无法查找姓以某个字母结尾的人。不能跳过索引中的列。也就是说，上述索引无法用于查找姓为Smith并且在某个特定日期出生的人。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查询WHERE 姓='Smith' AND 名 LIKE 'J%' AND 出生日期='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以使用多个等于条件来代替范围条件
到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。MyISAM和InnoDB都使用B+Tree。例如：
MyISAM使用前缀压缩技术使得索引更小，InnoDB则按照原数据格式进行存储。
MyISAM索引通过数据的物理位置引用被索引的行，InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都是按顺序存储的，井且每一个叶子页到根的距离相同。
哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时保持指向数据行的指针。在MySQL中，只有Memory引起显示支持哈希索引。
哈希索引数据并不是按照索引数据顺序存储的，所以无法用于排序；
哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
哈希索引只支持等值比较查询，不支持任何范围查询；
InnoDB引擎有个特殊的功能叫做"自适应哈希索引"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为。
覆盖索引
通常大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再索引读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为"覆盖索引"。
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
索引条目通常远小于数据行大小，如果只读取索引，那么MySQL访问更少数据量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据还小，更容易全部放入内存中这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小。
索引按照列值顺序存储至少在单个页内是如此，对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
大多数据引擎能更好的缓存索引。比如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据多一次系统调用。
覆盖索引对于InnoDB表特别有用，因为InnoDB使用聚集索引组织数据。InnoDB的二级索引在叶子节点中保存行的主键值，如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。对于索引覆盖查询(index-covered query)，使用EXPLAIN时，在Extra一列中看到"Using index"。
在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。
冗余和重复索引
MySQL允许在相同列上创建多个索引，MySQL需要单独维护重复的索引，并且优化器在优化查询的时候需要逐个地进行考虑，影响查询性能。
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非在同一列上创建不同类型的索引来满足不同的查询需求。
重复索引是指在相同的列上按相同的顺序创建相同类型的索引。冗余索引和重复索引有一些不同。如果创建了索引A, B，再创建索引A就是冗余索引，因为这只是前一个索引的前缀索引，索引A, B也可以当做索引A来使用。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引如扩展索引A为A，B。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变大太大，从而影响其他使用该索引的查询的性能。一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
冗余索引和重复索引有一些不同。如果创建了索引(a,b)，再创建索引(a)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(a,b)也可以当作索引(a)来使用这种冗余只是对B-Tree索引来说的。但是如果再创建索引(b,a)，则不是冗余索引，索引(b)也不是，因为b不是索引(a,b)的最左前缀列。另外其他不同类型的索引例如哈希索引或者全文索引也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
索引的优点及评分
最常见的B-Tree索引，按照顺序存储数据，所以可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成查询。据此特性，总结下来索引有如下三大优点：
索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机IO变为顺序IO；
评价一个索引是否适合某个查询的"三星系统"：
索引将相关的记录放到一起则获得一星；
索引中的数据顺序和查找中的排列顺序一致则获得二星；
索引中的列包含了查询需要的全部列则获得三星；
使用索引扫描来做排序
MySQL有两种方式生成有序的结果：
通过排序操作，Explain 的Extra 输出"Using filesort"， MySQL使用文件排序；
通过索引顺序扫描，Explain的type列值为index，MySQL使用索引扫描排序不要和Extra 列的"Using index"混淆。
扫描索引本身很快，因为只需从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，就要每扫描一条索引记录得回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是在IO密集型。所以，设计索引时让同一个索引既满足排序，又用于查找行，避免随机IO。
当索引的列的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向倒序和正序都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一个表时，才能使用索引来做排序。ORDER BY子句和Where查询的限制是一样的：需要满足索引的最左前缀的要求。
当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集实际上就是外排序，使用临时表。
对于filesort，MySQL有两种排序算法：
(1)两次扫描算法(Two passes)
实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存通过参数sort_buffer_size设定中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(2)一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出"Using filesort"；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出"Using temporary;Using filesort"。
当前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求。例如，Sakila数据库的表rental在列(rental_date,inventory_id,customer_id)上有名为rental_date的索引，如下表所示。
CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
MySQL使用rental_date索引为下面的查询排序，从EXPLAIN中看出没有出现filesort：
mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: rental_date
key: rental_date
key_len: 8
ref: const
rows: 1
Extra: Using where
即使ORDER BY 字句不满足最左前缀索引，也可以用于查询排序，因为索引的第一列被指定为常数。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：
... where rental_date = '2005-05-25' order by inventory_id desc;
下面这个查询也没问题，因为order by使用的就是索引的最左前缀：
... where rental_data > '2005-05-25' order by rental_date,inventory_id;
下面一些不能使用索引做排序的查询：
下面这个查询使用两种不同的排序方向：
... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;
下面这个查询的order by 子句中引用一个不在索引中的列(staff_id)：
... where rental_date = '2005-05-25' order by inventory_id,staff_id;
下面这个查询的where 和 order by的列无法组合成索引的最左前缀：
... where rental_date = '2005-05-25' order by customer_id;
下面这个查询在索引列的第一列是范围条件，所以MySQL无法使用索引的其余列：
... where rental_date > '2005-05-25' order by customer_id;
这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：
... where rental_date = '2005-05-25' and inventory_id in(1,2) order by customer_id;
索引概览
* 普通索引
create index 索引名 on 表名(字段名(索引长度));
alter table 表名 add index 索引名 (字段名(索引长度));
create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度));
* 唯一索引
create unique index 索引名 on 表名(字段名(索引长度));
alter table 表名 add unique 索引名 (字段名(索引长度));
create table 表名(字段名 字段类型,字段名 字段类型,unique 索引名 (字段名(索引长度));
* 全文索引
//只支持 MyISAM 引擎
create fulltext index 索引名 on 表名(字段名);
alter table 表名 add fulltext 索引名(字段名);
create table 表名(字段名 字段类型,字段名 字段类型,fulltext (字段名);
* 组合索引
create index 索引名 on 表名(字段名(索引长度),字段名(索引长度),...);
alter table 表名 add index 索引名 (字段名(索引长度),字段名(索引长度),...;
create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度),字段名(索引长度));
* 查看索引
show index from 表名;
* 删除索引
alter table 表名 drop index 索引名;
约束概览
* 主键约束
create table 表名(字段名 字段类型 primary key,字段 字段类型,...);
//一个表只能有一个主键,这个主键可以由一列或多列组成
create table 表名(字段1 字段类型,字段2 字段类型,primary key(字段1,字段2);
* 唯一键约束
create table 表名(字段名 字段类型 unique,字段名 字段类型,...);
* 外键约束
create table 表1(字段1 字段类型,字段2 字段类型,foreign key(字段1) references 表2(字段名),...);
* 非空约束
create table 表名(字段名 字段类型 not null,字段名 字段类型,...);
* 默认值约束
create table 表名(字段名 字段类型 default 默认值,字段名 字段类型,...);
* check约束(MySQL 不支持)
create table 表名(字段1 字段类型,字段2 字段类型,check(字段1 > 30),...);
索引失效的场景
索引有相关的方面，因为它是大家都比较关心的公共话题，确实有很多坑：比如明明在某个字段上加了索引，但实际上并没有生效；索引有时候生效了，有时候没有生效。下面说一说mysql数据库索引失效的一些场景，为大家提供一个参考。
1.准备工作
所谓空口无凭，如果我直接把索引失效的这些场景丢出来，可能没有任何说服力。所以决定建表和造数据，一步步演示效果，尽量做到有理有据。
1.1创建user表
创建一张user表，表中包含：id、code、age、name和height字段。
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
此外还创建了三个索引：
id：数据库的主键
idx_code_age_name：由code、age和name三个字段组成的联合索引。
idx_height：普通索引
1.2插入数据
为了方便给大家做演示，我特意向user表中插入了3条数据：
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');
1.3查看数据库版本
为了防止以后出现不必要的误会，在这里有必要查一下当前数据库的版本。不说版本就直接给结论，是耍流氓，哈哈哈。
select version();
8.0.21
1.4查看执行计划
在mysql中，如果你想查看某条sql语句是否使用了索引，或者已建好的索引是否失效，可以通过explain关键字，查看该sql语句的执行计划，来判断索引使用情况。例如：explain select * from user where id=1;
执行结果：从中可以看出，由于id字段是主键，该sql语句用到了主键索引。
2.不满足最左匹配原则
之前我已经给code、age和name这3个字段建好联合索引：idx_code_age_name。
该索引字段的顺序是：
code
age
name
如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效喔，不信一起往下看。
2.1哪些情况索引有效？
先看看哪些情况下，能走索引。
explain select * from user where code='101';
explain select * from user where code='101' and age=21
explain select * from user where code='101' and age=21 and name='周星驰';
执行结果： 上面三种情况，sql都能正常走索引。
其实还有一种比较特殊的场景：explain select * from user where code = '101' and name='周星驰';
执行结果：查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引。
看到这里，不知道聪明的你，有没有发现这样一个规律： 这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
这就是我们所说的最左匹配原则。
2.2哪些情况索引失效？
前面我已经介绍过，建立了联合索引后，在查询条件中有哪些情况索引是有效的。接下来，我们重点看看哪些情况下索引会失效。
explain select * from user where age=21;
explain select * from user where name='周星驰';
explain select * from user where age=21 and name='周星驰';
执行结果：从中看出这3种情况下索引确实失效了。
说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。
3.使用了select *
在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select * 。知道为什么吗？废话不多说，按照国际惯例先上一条sql：
explain select * from user where name='苏三';
执行结果：在该sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。如果查询的时候，只查我们真正需要的列，而不查所有列，结果会怎么样？
非常快速的将上面的sql改成只查了code和name列：
explain select code,name from user where name='苏三';
执行结果：从执行结果不难看出，该sql语句这次走了全索引扫描，比全表扫描效率更高。
其实这里用到了：覆盖索引。
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
4.索引列上有计算
介绍本章节内容前，先跟大家一起回顾一下，根据id查询数据的sql语句：
explain select * from user where id=1;
执行结果： 从中可以看出，由于id字段是主键，该sql语句用到了主键索引。但如果id列上面有计算，比如：
explain select * from user where id+1=2;
执行结果：从执行结果能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。
5.索引列用了函数
有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度。假如现在有个需求：想查出所有身高是17开头的人，如果sql语句写成这样：explain select * from user where height=17;
该sql语句确实用到了普通索引：但该sql语句肯定是有问题的，因为它只能查出身高正好等于17的，但对于174这种情况，它没办法查出来。为了满足上面的要求，我们需要把sql语句稍稍改造了一下：
explain select * from user where SUBSTR(height,1,2)=17;
这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。执行结果有没有发现，在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了。
6.字段类型不同
在sql语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。请大家注意观察一下t_user表中的code字段，它是varchar字符类型的。在sql语句中查询数据时，查询条件我们可以写成这样：
explain select * from user where code="101";
执行结果：从中看到，该code字段走了索引。
温馨提醒一下，查询字符字段时，用双引号“和单引号'都可以。但如果你在写sql时，不小心把引号弄掉了，把sql语句变成了：
explain select * from user where code=101;
执行结果：你会惊奇的发现，该sql语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。这时你心里可能有一万个为什么，其中有一个肯定是：为什么索引会失效呢？
答：因为code字段的类型是varchar，而传参的类型是int，两种类型不同。
此外，还有一个有趣的现象，如果int类型的height字段，在查询时加了引号条件，却还可以走索引：
explain select * from user where height='175';
执行结果：从中看出该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。
这是变魔术吗？这不科学。
答：mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。
mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。接下来，看一个更有趣的sql语句：select 1 + '1';
它的执行结果是2，还是11呢？好吧，不卖关子了，直接公布答案执行结果是2。
mysql自动把字符串1，转换成了int类型的1，然后变成了：1+1=2。
但如果你确实想拼接字符串该怎么办？
答：可以使用concat关键字。
具体拼接sql如下：select concat(1,'1');
接下来，关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
答：根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值？感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
7.like左边包含%
模糊查询，在我们日常的工作中，使用频率还是比较高的。比如现在有个需求：想查询姓李的同学有哪些？使用like语句可以很快的实现：
select * from user where name like '李%';
但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。不信，我们一起往下看。目前like查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
假如现在有个需求：想查出所有code是10开头的用户。这个需求太简单了吧，sql语句如下：
explain select * from user where code like '10%';
执行结果：看出这种%在10右边时走了索引。而如果把需求改了：想出现出所有code是1结尾的用户。查询sql语句改为：
explain select * from user where code like '%1';
执行结果：看出这种%在1左边时，code字段上索引失效了，该sql变成了全表扫描。
此外，如果出现以下sql：
explain select * from user where code like '%1%';
该sql语句的索引也会失效。下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。那么，为什么会出现这种现象呢？
答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，这样是不行的。
8.列对比
上面的内容都是常规需求，下面来点不一样的。假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录。这个需求很简单，sql可以这样写：
explain select * from user where id=height
执行结果：索引竟然失效了。为什么会出现这种结果？
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。但如果把两个单独建了索引的列，用来做列对比时索引会失效。
9.使用or关键字
平时在写查询sql时，使用or关键字的场景非常多，但如果你稍不注意，就可能让已有的索引失效。不信一起往下面看。某天你遇到这样一个需求：想查一下id=1或者height=175的用户：
explain select * from user where id=1 or height='175';
执行结果：没错，这次确实走了索引，恭喜被你蒙对了，因为刚好id和height字段都建了索引。但接下来的一个夜黑风高的晚上，需求改了：除了前面的查询条件之后，还想加一个address='成都'。
这还不简单，sql走起：explain select * from user where id=1 or height='175' or address='成都';
执行结果：结果悲剧了，之前的索引都失效了。
为什么，我做了什么？
答：因为你最后加的address字段没有加索引，从而导致其他字段的索引都失效了。
注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。
10.not in和not exists
在我们日常工作中用得也比较多的，还有范围查询，常见的有：
in
exists
not in
not exists
between and
今天重点聊聊前面四种。
10.1 in关键字
假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：
explain select * from user where height in (173,174,175,176);
执行结果：从中可以看出，sql语句中用in关键字是走了索引的。
10.2 exists关键字
有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：
explain select * from user t1 where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：从中可以看出，用exists关键字同样走了索引。
10.3 not in关键字
上面演示的两个例子是正向的范围，即在某些范围之内。那么反向的范围，即不在某些范围之内，能走索引不？话不多说，先看看使用not in的情况：
explain select * from user where height not in (173,174,175,176);
执行结果：没看错，索引失效了。
看如果现在需求改了：想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：
explain select * from user where id not in (173,174,175,176);
执行结果：可能会惊奇的发现，主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。
10.4 not exists关键字
除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：
explain select * from user t1
where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：从中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。
11.order by的坑
在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。但我始终觉得order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。
11.1 哪些情况走索引？
首先当然要温柔一点，一起看看order by的哪些情况可以走索引。之前说过，在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name。
11.1.1 满足最左匹配原则
order by后面的条件，也要遵循联合索引的最左匹配原则，具体有以下sql：
explain select * from user order by code limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;
执行结果：从中看出这3条sql都能够正常走索引。除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。
11.1.2 配合where一起使用
order by还能配合where一起遵循最左匹配原则：explain select * from user where code='101' order by age;
执行结果：code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。
假如中间断层了，sql语句变成这样，执行结果会是什么呢？
explain select * from user where code='101' order by name;
执行结果：虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。
11.1.3 相同的排序
order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。具体sql如下：
explain select * from user order by code desc,age desc limit 100;
执行结果： 该示例中order by后面的code和age字段都用了降序，所以依然走了索引。
11.1.4 两者都有
如果某个联合索引字段，在where和order by中都有，结果会怎么样？
explain select * from user where code='101' order by code, name;
执行结果：code字段在where和order by中都有，对于这种情况，从中看出，还是能走了索引的。
11.2 哪些情况不走索引？
前面介绍的都是正面的用法，是为了让大家更容易接受下面反面的用法。
好了，接下来重点聊聊order by的哪些情况下不走索引？
11.2.1 没加where或limit
如果order by语句中没有加where或limit关键字，该sql语句将不会走索引：explain select * from user order by code, name;
执行结果：从中看出索引真的失效了。
11.2.2 对不同的索引做order by
前面介绍的基本都是联合索引，这一个索引的情况。但如果对多个索引进行order by，结果会怎么样呢？
explain select * from user order by code, height limit 100;
执行结果： 从中看出索引也失效了。
11.2.3 不满足最左匹配原则
前面已经介绍过，order by如果满足最左匹配原则，还是会走索引。下面来看看不满足最左匹配原则的情况：
explain select * from user order by name limit 100;
执行结果：name字段是联合索引的第三个字段，从中看出如果order by不满足最左匹配原则，确实不会走索引。
11.2.4 不同的排序
前面已经介绍过，如果order by后面有一个联合索引的多个字段，它们具有相同排序规则，那么会走索引。但如果它们有不同的排序规则呢？
explain select * from user order by code asc,age desc limit 100;
执行结果：从中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。
比较、排序、SQL解析、函数或逻辑运算需要用到CPU；
缓存数据访问，临时数据存放需要用到内存；
冷数据读取，大数据量的排序和关联，数据写入落盘，需要访问硬盘；
SQL请求交互，结果集返回需要网络资源。
那么SQL优化思路自然是减少SQL的解析，减少复杂的运算，减少数据处理的规模，减少对物理IO的依赖，减少服务器和客户端的网络交互。索引策略的组合最大化提升SQL优化性能：
独立的列： 减少SQL的解析
前缀索引和索引选择性： 减少数据处理的规模，减少对物理IO的依赖
多列索引：减少对物理IO的依赖
选择合适的索引列顺序： 减少数据处理的规模，减少对物理IO的依赖
聚簇索引： 减少数据处理的规模，减少对物理IO的依赖
覆盖索引： 减少对物理IO的依赖
使用索引扫描来做排序： 减少复杂的运算
返回必要的列： 减少对物理IO的依赖，减少服务器和客户端的网络交互
前缀索引是一种能使索引更小更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引(suffix index)也有用途例如，找到某个域名的所有电子邮件地址。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新的版本引入了一种叫"索引合并"(index merge)策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
索引合并策略有时候是一种优化的结果，但大多数时候说明表索引建得很糟糕：
当出现服务器对多个索引做相交操作时通常有多个AND条件，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时通常有多个OR条件，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到"查询成本"cost中，优化器只关心随机页面读取。这使得查询的成本被"低估"，导致该执行计划还不如直接走全表扫描。这样做不但消耗更多的CPU和内存资源，还可能影响查询的并发性，但如果是单独运行这样的查询，则往往忽略对并发性的影响。
如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用ignore index提示让优化器忽略掉某些索引。
索引的类型
B 树索引
B+树，所有叶子节点在同一层，每一个叶子节点包含指向下一个叶子结点的指针。
B-树索引适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。
全字匹配：和索引中的所有列进行匹配，如查找姓名为Cuba Allen、出生于1960-01-01的人；
匹配最左前缀：即只使用索引的第一列，如查找所有姓为Allen的人；
匹配列前缀：匹配某一列的值的开头部分，如查找所有以J开头的姓的人。这里只使用了索引的第一列；
匹配范围值：如查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列；
精确匹配某一列并范围匹配另一列：如查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配；
只访问索引的查询：覆盖索引；
如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人。类似的，也无法查找姓以某个字母结尾的人。不能跳过索引中的列。也就是说，上述索引无法用于查找姓为Smith并且在某个特定日期出生的人。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查询WHERE 姓='Smith' AND 名 LIKE 'J%' AND 出生日期='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值的数量有限，那么可以使用多个等于条件来代替范围条件
到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。MyISAM和InnoDB都使用B+Tree。例如：
MyISAM使用前缀压缩技术使得索引更小，InnoDB则按照原数据格式进行存储。
MyISAM索引通过数据的物理位置引用被索引的行，InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都是按顺序存储的，井且每一个叶子页到根的距离相同。
哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时保持指向数据行的指针。在MySQL中，只有Memory引起显示支持哈希索引。
哈希索引数据并不是按照索引数据顺序存储的，所以无法用于排序；
哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
哈希索引只支持等值比较查询，不支持任何范围查询；
InnoDB引擎有个特殊的功能叫做"自适应哈希索引"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为。
覆盖索引
通常大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再索引读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为"覆盖索引"。
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
索引条目通常远小于数据行大小，如果只读取索引，那么MySQL访问更少数据量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据还小，更容易全部放入内存中这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小。
索引按照列值顺序存储至少在单个页内是如此，对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
大多数据引擎能更好的缓存索引。比如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据多一次系统调用。
覆盖索引对于InnoDB表特别有用，因为InnoDB使用聚集索引组织数据。InnoDB的二级索引在叶子节点中保存行的主键值，如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。对于索引覆盖查询(index-covered query)，使用EXPLAIN时，在Extra一列中看到"Using index"。
在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。
冗余和重复索引
MySQL允许在相同列上创建多个索引，MySQL需要单独维护重复的索引，并且优化器在优化查询的时候需要逐个地进行考虑，影响查询性能。
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非在同一列上创建不同类型的索引来满足不同的查询需求。
重复索引是指在相同的列上按相同的顺序创建相同类型的索引。冗余索引和重复索引有一些不同。如果创建了索引A, B，再创建索引A就是冗余索引，因为这只是前一个索引的前缀索引，索引A, B也可以当做索引A来使用。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引如扩展索引A为A，B。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变大太大，从而影响其他使用该索引的查询的性能。一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
冗余索引和重复索引有一些不同。如果创建了索引(a,b)，再创建索引(a)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(a,b)也可以当作索引(a)来使用这种冗余只是对B-Tree索引来说的。但是如果再创建索引(b,a)，则不是冗余索引，索引(b)也不是，因为b不是索引(a,b)的最左前缀列。另外其他不同类型的索引例如哈希索引或者全文索引也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
索引的优点及评分
最常见的B-Tree索引，按照顺序存储数据，所以可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成查询。据此特性，总结下来索引有如下三大优点：
索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机IO变为顺序IO；
评价一个索引是否适合某个查询的"三星系统"：
索引将相关的记录放到一起则获得一星；
索引中的数据顺序和查找中的排列顺序一致则获得二星；
索引中的列包含了查询需要的全部列则获得三星；
使用索引扫描来做排序
MySQL有两种方式生成有序的结果：
通过排序操作，Explain 的Extra 输出"Using filesort"， MySQL使用文件排序；
通过索引顺序扫描，Explain的type列值为index，MySQL使用索引扫描排序不要和Extra 列的"Using index"混淆。
扫描索引本身很快，因为只需从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，就要每扫描一条索引记录得回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是在IO密集型。所以，设计索引时让同一个索引既满足排序，又用于查找行，避免随机IO。
当索引的列的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向倒序和正序都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一个表时，才能使用索引来做排序。ORDER BY子句和Where查询的限制是一样的：需要满足索引的最左前缀的要求。
当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集实际上就是外排序，使用临时表。
对于filesort，MySQL有两种排序算法：
(1)两次扫描算法(Two passes)
实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存通过参数sort_buffer_size设定中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(2)一次扫描算法(single pass)
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出"Using filesort"；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出"Using temporary;Using filesort"。
当前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求。例如，Sakila数据库的表rental在列(rental_date,inventory_id,customer_id)上有名为rental_date的索引，如下表所示。
CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
MySQL使用rental_date索引为下面的查询排序，从EXPLAIN中看出没有出现filesort：
mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: rental_date
key: rental_date
key_len: 8
ref: const
rows: 1
Extra: Using where
即使ORDER BY 字句不满足最左前缀索引，也可以用于查询排序，因为索引的第一列被指定为常数。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：
... where rental_date = '2005-05-25' order by inventory_id desc;
下面这个查询也没问题，因为order by使用的就是索引的最左前缀：
... where rental_data > '2005-05-25' order by rental_date,inventory_id;
下面一些不能使用索引做排序的查询：
下面这个查询使用两种不同的排序方向：
... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;
下面这个查询的order by 子句中引用一个不在索引中的列(staff_id)：
... where rental_date = '2005-05-25' order by inventory_id,staff_id;
下面这个查询的where 和 order by的列无法组合成索引的最左前缀：
... where rental_date = '2005-05-25' order by customer_id;
下面这个查询在索引列的第一列是范围条件，所以MySQL无法使用索引的其余列：
... where rental_date > '2005-05-25' order by customer_id;
这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：
... where rental_date = '2005-05-25' and inventory_id in(1,2) order by customer_id;
索引概览
* 普通索引
create index 索引名 on 表名(字段名(索引长度));
alter table 表名 add index 索引名 (字段名(索引长度));
create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度));
* 唯一索引
create unique index 索引名 on 表名(字段名(索引长度));
alter table 表名 add unique 索引名 (字段名(索引长度));
create table 表名(字段名 字段类型,字段名 字段类型,unique 索引名 (字段名(索引长度));
* 全文索引
//只支持 MyISAM 引擎
create fulltext index 索引名 on 表名(字段名);
alter table 表名 add fulltext 索引名(字段名);
create table 表名(字段名 字段类型,字段名 字段类型,fulltext (字段名);
* 组合索引
create index 索引名 on 表名(字段名(索引长度),字段名(索引长度),...);
alter table 表名 add index 索引名 (字段名(索引长度),字段名(索引长度),...;
create table 表名(字段名 字段类型,字段名 字段类型,index 索引名 (字段名(索引长度),字段名(索引长度));
* 查看索引
show index from 表名;
* 删除索引
alter table 表名 drop index 索引名;
约束概览
* 主键约束
create table 表名(字段名 字段类型 primary key,字段 字段类型,...);
//一个表只能有一个主键,这个主键可以由一列或多列组成
create table 表名(字段1 字段类型,字段2 字段类型,primary key(字段1,字段2);
* 唯一键约束
create table 表名(字段名 字段类型 unique,字段名 字段类型,...);
* 外键约束
create table 表1(字段1 字段类型,字段2 字段类型,foreign key(字段1) references 表2(字段名),...);
* 非空约束
create table 表名(字段名 字段类型 not null,字段名 字段类型,...);
* 默认值约束
create table 表名(字段名 字段类型 default 默认值,字段名 字段类型,...);
* check约束(MySQL 不支持)
create table 表名(字段1 字段类型,字段2 字段类型,check(字段1 > 30),...);
索引失效的场景
索引有相关的方面，因为它是大家都比较关心的公共话题，确实有很多坑：比如明明在某个字段上加了索引，但实际上并没有生效；索引有时候生效了，有时候没有生效。下面说一说mysql数据库索引失效的一些场景，为大家提供一个参考。
1.准备工作
所谓空口无凭，如果我直接把索引失效的这些场景丢出来，可能没有任何说服力。所以决定建表和造数据，一步步演示效果，尽量做到有理有据。
1.1创建user表
创建一张user表，表中包含：id、code、age、name和height字段。
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
此外还创建了三个索引：
id：数据库的主键
idx_code_age_name：由code、age和name三个字段组成的联合索引。
idx_height：普通索引
1.2插入数据
为了方便给大家做演示，我特意向user表中插入了3条数据：
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');
1.3查看数据库版本
为了防止以后出现不必要的误会，在这里有必要查一下当前数据库的版本。不说版本就直接给结论，是耍流氓，哈哈哈。
select version();
8.0.21
1.4查看执行计划
在mysql中，如果你想查看某条sql语句是否使用了索引，或者已建好的索引是否失效，可以通过explain关键字，查看该sql语句的执行计划，来判断索引使用情况。例如：explain select * from user where id=1;
执行结果：从中可以看出，由于id字段是主键，该sql语句用到了主键索引。
2.不满足最左匹配原则
之前我已经给code、age和name这3个字段建好联合索引：idx_code_age_name。
该索引字段的顺序是：
code
age
name
如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效喔，不信一起往下看。
2.1哪些情况索引有效？
先看看哪些情况下，能走索引。
explain select * from user where code='101';
explain select * from user where code='101' and age=21
explain select * from user where code='101' and age=21 and name='周星驰';
执行结果： 上面三种情况，sql都能正常走索引。
其实还有一种比较特殊的场景：explain select * from user where code = '101' and name='周星驰';
执行结果：查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引。
看到这里，不知道聪明的你，有没有发现这样一个规律： 这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
这就是我们所说的最左匹配原则。
2.2哪些情况索引失效？
前面我已经介绍过，建立了联合索引后，在查询条件中有哪些情况索引是有效的。接下来，我们重点看看哪些情况下索引会失效。
explain select * from user where age=21;
explain select * from user where name='周星驰';
explain select * from user where age=21 and name='周星驰';
执行结果：从中看出这3种情况下索引确实失效了。
说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code。
3.使用了select *
在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select * 。知道为什么吗？废话不多说，按照国际惯例先上一条sql：
explain select * from user where name='苏三';
执行结果：在该sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。如果查询的时候，只查我们真正需要的列，而不查所有列，结果会怎么样？
非常快速的将上面的sql改成只查了code和name列：
explain select code,name from user where name='苏三';
执行结果：从执行结果不难看出，该sql语句这次走了全索引扫描，比全表扫描效率更高。
其实这里用到了：覆盖索引。
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
4.索引列上有计算
介绍本章节内容前，先跟大家一起回顾一下，根据id查询数据的sql语句：
explain select * from user where id=1;
执行结果： 从中可以看出，由于id字段是主键，该sql语句用到了主键索引。但如果id列上面有计算，比如：
explain select * from user where id+1=2;
执行结果：从执行结果能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。
5.索引列用了函数
有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度。假如现在有个需求：想查出所有身高是17开头的人，如果sql语句写成这样：explain select * from user where height=17;
该sql语句确实用到了普通索引：但该sql语句肯定是有问题的，因为它只能查出身高正好等于17的，但对于174这种情况，它没办法查出来。为了满足上面的要求，我们需要把sql语句稍稍改造了一下：
explain select * from user where SUBSTR(height,1,2)=17;
这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。执行结果有没有发现，在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了。
6.字段类型不同
在sql语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。请大家注意观察一下t_user表中的code字段，它是varchar字符类型的。在sql语句中查询数据时，查询条件我们可以写成这样：
explain select * from user where code="101";
执行结果：从中看到，该code字段走了索引。
温馨提醒一下，查询字符字段时，用双引号“和单引号'都可以。但如果你在写sql时，不小心把引号弄掉了，把sql语句变成了：
explain select * from user where code=101;
执行结果：你会惊奇的发现，该sql语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。这时你心里可能有一万个为什么，其中有一个肯定是：为什么索引会失效呢？
答：因为code字段的类型是varchar，而传参的类型是int，两种类型不同。
此外，还有一个有趣的现象，如果int类型的height字段，在查询时加了引号条件，却还可以走索引：
explain select * from user where height='175';
执行结果：从中看出该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。
这是变魔术吗？这不科学。
答：mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。
mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。接下来，看一个更有趣的sql语句：select 1 + '1';
它的执行结果是2，还是11呢？好吧，不卖关子了，直接公布答案执行结果是2。
mysql自动把字符串1，转换成了int类型的1，然后变成了：1+1=2。
但如果你确实想拼接字符串该怎么办？
答：可以使用concat关键字。
具体拼接sql如下：select concat(1,'1');
接下来，关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
答：根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值？感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
7.like左边包含%
模糊查询，在我们日常的工作中，使用频率还是比较高的。比如现在有个需求：想查询姓李的同学有哪些？使用like语句可以很快的实现：
select * from user where name like '李%';
但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。不信，我们一起往下看。目前like查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
假如现在有个需求：想查出所有code是10开头的用户。这个需求太简单了吧，sql语句如下：
explain select * from user where code like '10%';
执行结果：看出这种%在10右边时走了索引。而如果把需求改了：想出现出所有code是1结尾的用户。查询sql语句改为：
explain select * from user where code like '%1';
执行结果：看出这种%在1左边时，code字段上索引失效了，该sql变成了全表扫描。
此外，如果出现以下sql：
explain select * from user where code like '%1%';
该sql语句的索引也会失效。下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。那么，为什么会出现这种现象呢？
答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，这样是不行的。
8.列对比
上面的内容都是常规需求，下面来点不一样的。假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录。这个需求很简单，sql可以这样写：
explain select * from user where id=height
执行结果：索引竟然失效了。为什么会出现这种结果？
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。但如果把两个单独建了索引的列，用来做列对比时索引会失效。
9.使用or关键字
平时在写查询sql时，使用or关键字的场景非常多，但如果你稍不注意，就可能让已有的索引失效。不信一起往下面看。某天你遇到这样一个需求：想查一下id=1或者height=175的用户：
explain select * from user where id=1 or height='175';
执行结果：没错，这次确实走了索引，恭喜被你蒙对了，因为刚好id和height字段都建了索引。但接下来的一个夜黑风高的晚上，需求改了：除了前面的查询条件之后，还想加一个address='成都'。
这还不简单，sql走起：explain select * from user where id=1 or height='175' or address='成都';
执行结果：结果悲剧了，之前的索引都失效了。
为什么，我做了什么？
答：因为你最后加的address字段没有加索引，从而导致其他字段的索引都失效了。
注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。
10.not in和not exists
在我们日常工作中用得也比较多的，还有范围查询，常见的有：
in
exists
not in
not exists
between and
今天重点聊聊前面四种。
10.1 in关键字
假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：
explain select * from user where height in (173,174,175,176);
执行结果：从中可以看出，sql语句中用in关键字是走了索引的。
10.2 exists关键字
有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：
explain select * from user t1 where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：从中可以看出，用exists关键字同样走了索引。
10.3 not in关键字
上面演示的两个例子是正向的范围，即在某些范围之内。那么反向的范围，即不在某些范围之内，能走索引不？话不多说，先看看使用not in的情况：
explain select * from user where height not in (173,174,175,176);
执行结果：没看错，索引失效了。
看如果现在需求改了：想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：
explain select * from user where id not in (173,174,175,176);
执行结果：可能会惊奇的发现，主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。
10.4 not exists关键字
除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：
explain select * from user t1
where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：从中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。
11.order by的坑
在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。但我始终觉得order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。
11.1 哪些情况走索引？
首先当然要温柔一点，一起看看order by的哪些情况可以走索引。之前说过，在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name。
11.1.1 满足最左匹配原则
order by后面的条件，也要遵循联合索引的最左匹配原则，具体有以下sql：
explain select * from user order by code limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;
执行结果：从中看出这3条sql都能够正常走索引。除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。
11.1.2 配合where一起使用
order by还能配合where一起遵循最左匹配原则：explain select * from user where code='101' order by age;
执行结果：code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。
假如中间断层了，sql语句变成这样，执行结果会是什么呢？
explain select * from user where code='101' order by name;
执行结果：虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。
11.1.3 相同的排序
order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。具体sql如下：
explain select * from user order by code desc,age desc limit 100;
执行结果： 该示例中order by后面的code和age字段都用了降序，所以依然走了索引。
11.1.4 两者都有
如果某个联合索引字段，在where和order by中都有，结果会怎么样？
explain select * from user where code='101' order by code, name;
执行结果：code字段在where和order by中都有，对于这种情况，从中看出，还是能走了索引的。
11.2 哪些情况不走索引？
前面介绍的都是正面的用法，是为了让大家更容易接受下面反面的用法。
好了，接下来重点聊聊order by的哪些情况下不走索引？
11.2.1 没加where或limit
如果order by语句中没有加where或limit关键字，该sql语句将不会走索引：explain select * from user order by code, name;
执行结果：从中看出索引真的失效了。
11.2.2 对不同的索引做order by
前面介绍的基本都是联合索引，这一个索引的情况。但如果对多个索引进行order by，结果会怎么样呢？
explain select * from user order by code, height limit 100;
执行结果： 从中看出索引也失效了。
11.2.3 不满足最左匹配原则
前面已经介绍过，order by如果满足最左匹配原则，还是会走索引。下面来看看不满足最左匹配原则的情况：
explain select * from user order by name limit 100;
执行结果：name字段是联合索引的第三个字段，从中看出如果order by不满足最左匹配原则，确实不会走索引。
11.2.4 不同的排序
前面已经介绍过，如果order by后面有一个联合索引的多个字段，它们具有相同排序规则，那么会走索引。但如果它们有不同的排序规则呢？
explain select * from user order by code asc,age desc limit 100;
执行结果：从中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。