mysql索引及其优化_where b=1 order by a asc 能不能走索引-CSDN博客

本文链接：https://blog.csdn.net/weixin_46370070/article/details/108051195

1.什么是索引

索引是存储引擎快速找到记录的一种数据结构。
索引可以包含一个列或多个列的值（如复合索引（a，b）），索引列的顺序也非常的重要，因为mysql只能高效的使用索引的最左前的索引（即a列）。

2.索引的类型

Mysql的索引是在存储引擎层实现的而不是服务器层。
B-Tree和B+Tree(除了archive存储引擎之外都支持）
B+TREE能够快速的访问到所需要的数据，因为存储引擎不要要进行全表扫描，而是根据索引的根节点开始一层一层的往下找。
根节点中存放的是指向子节点的指针，存储引擎是根据这些指针一层一层的往下找的。每次读取一个页（innodb默认的值页的大小为16kb，可通过参数innodb_page_size将页的大小设置为4K、8K、16K）通过比较节点页的值和要查找的值，可以找到合适的指针向下层继续查找，最终存储引擎找到对应的值，要么该记录不存在。
在这里插入图片描述

select * from xxx where name='Alice';

如上图，如果我需要找到ALice（Alice所对应的字段为二级索引），先要扫描Alice这个字段（name）的全部的值，从而找到Alice这个这个值所对应的主键的值为18，在根据主键的值去找，先读取第一个页，因为18是大于15小于56的，所以读取第二个页，又因为18大于15小于20的，所以根据指针读取第三个页。到达叶子节点读取18所对应的需要的字段值。

B+Tree跟B-Tree区别

B+tree（平衡搜索树）只会在叶子节点存放数据。而B-Tree会在所有的节点存放数据。
下图为B-Tree
在这里插入图片描述
下图为B+Tree

1、所有的叶子结点才有指向数据的指针。非叶结点就是纯的索引数据。这样的好处在于，我们可以将尽可能的非叶结点载入内存，没有浪费。

2、B+Tree每个叶结点都有指向下一个叶结点的链接。这样的好处在于，我们可以从任意一个叶结点开始遍历，获取接下来所有的数据。
B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

B+/B-Tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引
1.不能跳过索引中的列。如果联合索引（a,b,c），如果使用条件a和c条件查询，那么只能使用索引的第一列a。
2.如果查询中有某个范围查询，则其右边的所有列都无法使用索引优化查询。

Hash索引

mysql中只有memory引擎中才会显示支持

Hash索引原理

在这里插入图片描述
Hash索引是基于哈希表实现的，只有精确的匹配到所有的列的查询才有效（即等值查询），hash索引是对于每一行数据，存储引擎都会对索引列计算一个hash码，哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不一样（当键值一样时，就继续再server层进行过滤），hash索引将所有的哈希码存储在索引中，在哈希表中保存每个数据的指针。

优点：

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。

缺点：

1).Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
2).Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
3).Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
4).Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
5).Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下.

全文索引

全文索引是一种特殊类型的索引，他查找的是文本中的关键词，而不是直接比较索引中的值，全文索引和其他的几类索引的匹配方式完全不一样。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

create table test1 (
    id int(11) unsigned not null auto_increment,
    content text not null,
    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;
 
insert into test1 (content) values ('a'),('b'),('c');
insert into test1 (content) values ('aa'),('bb'),('cc');
insert into test1 (content) values ('aaa'),('bbb'),('ccc');
insert into test1 (content) values ('aaaa'),('bbbb'),('cccc');

test1表数据
按照全文索引的使用语法执行下面查询

select * from test1 where match(content) against('a');
select * from test1 where match(content) against('aa');
select * from test1 where match(content) against('aaa');

在这里插入图片描述
上述的三个查询明明有符合过滤条件的数据却查不到，这是怎么回事捏，因为MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。
在这里插入图片描述
那我们再来执行以下语句

select * from test where match(content) against('aaaa');

在这里插入图片描述

果然查询出来了

3.怎么创建索引

在创建表之后添加索引

1.添加PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加复合索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
6.添加前缀索引
mysql>ALTER TABLE `table_name` ADD  INDEX index_name( `column`(length))
7.用create方式创建
CREATE INDEX indexName ON `table_name`(username(length));

在创建表时创建索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,
    INDEX [indexName]
    (username(length))
    );

普通索引和唯一索引的区别如何选择

在读性能上的差别

普通索引和唯一索引在读的性能上的差别不大
select * from xxx where name ='wgy';
普通索引：
1.根据树搜索找到name='wgy’的字段然后再根据主键回表
2.找到name='wgy’的下一个叶子节点判断是不是name =‘wgy’;如果是就继续回表，直到找到的值不是wgy为止。
唯一索引：
1.根据树搜索找到name=‘wgy’，回表，结束。
因为innodb的页（默认16kb）为单位进行存储的。所以再读name='wgy’该叶子节点的时候，会把相邻的叶子节点也读进去（除非不在同一页）,所以普通索引和唯一索引的读的性能差不了很多。

在写数据上性能的差别

update xxx set name ='wgy' where id=1;
当数据本身就在内存中时
普通索引：直接更新内存中的值
唯一索引：判断name ='wgy’的唯一性，满足唯一性要求就更新，不满足就报错.
当数据不在内存中
普通索引:
1.再change buffer中写入对值的修改，当下次需要读取改行的值的时候再将原来的数据读入内存在与change buffer中的值进行合并(merge).
.唯一索引
因为必须要判断唯一性，所以必须将数据读入内存，再判断唯一性，因为已经读到了内存中就不用再用change buffer了，直接修改内存就可。

选择唯一索引还是普通索引？

如果业务上的写特别多并且改变数据后不会立刻就读，建议使用change buffer +普通索引的方式。
如果业务上写多但是写后会立刻读，建议用唯一索引，因为这样可以减少对change buffer的维护。

4.索引的作用

因为最常见的的B-Tree索引是按照顺序存储数据的，所以mysql可以用来做order by 和 group by 来操作。所以B-Tree也就将相关的止存储在一起，最后因为索引中存储了实际的列值，所以某些查询只能使用索引就能完成全部的查询。
总结就有以下优点
索引可以大大的减少服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机的I/O变为顺序I/O
索引并不是最好的工具，只有当索引能够帮助存储引擎快速查找到记录带来的好处大于其带来的额外的工作时，索引才是有效的，对于很小的表大部分情况下简单的全表扫描更高效。对于中大型表建立索引就是非常有必要的。建议在选择性较高的字段上建立索引。选择性高低可以通过select count(*) count(distinct(字段名)) from 表名来查看。两个值越接近选择性越高。

5.如何高效的使用索引

独立的列

指对索引列不能是表达式的一部分，也不能是函数的一部分，即不能对索引列进行计算，否则会导致查询不会走索引，而是进行全表扫描。
Select id from xxx where id-1=5
#因为对索引进行了计算，所以不会根据索引进行查询
Select id from xxx where id =4
#我们应该养成将索引列单独放在比较符号的另一侧。`

前缀索引

对于BLOB、TEXT或很大的VARCHAR类型的列，作为查询条件时（原则上是要避免这样的操作，但有时总是情非得已），该列必须使用前缀索引，这样来提高查询性能。因为MySQL是不允许索引这些列的完整长度的。
可以通过select count(distinct left(xxx,4)),count(xxx) from xxxx查看前缀索引的过滤性。

当需要索引一个很长的字符列，这会让索引变得很大很慢，可以对这个索引进行hash，也可以索引开始的部分字符（但是这样做会降低索引的，建议在保证选择性的前提下尽量的缩短列前缀)。

Select count(distinct left(city,7))/count(*) from xxx as sel1；

#查看前city字段的前七个字母的选择性。

Alter table 表名 add key(city(7));#取city的前七个字母作为索引

Mysql无法将前缀索引做为order by 和 group by 也无法使用前缀索引做覆盖扫描。
在这里插入图片描述

在这里插入图片描述
建议尽量少用前缀索引，因为前缀索引只能保证被用来做索引的前面几个字符是有序的，因此如果要使用该索引来进行排序是不行的，还是最终需要通过文件排序（using filesort）
。
如上图我们创建一个表

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb;

创建以下索引（其中index1为普通索引，index2为前缀索引）

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。但是我们可以看到email字段的前六个字符的过滤性并不好

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是 index1（即普通索引），
执行顺序是这样的：
1.从 index1 索引树找到满足索引是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2.到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3.取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用的是 index2（即 email(6)的前缀索引结构），执行顺序是这样的：
1.从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2.到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3.取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4.重复上一步，直到在 index2 上取到的值不是’zhangs’时，循环结束。在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。
通过执行过程我们不难看出，虽然前缀索引能够减少存储的空间，但是如果前缀索引的过滤性不好的话会导致扫描的行数增大很多。
如果我们对上述前缀索引去email(7)扫描的速率会有大大的增加。

mysql> alter table SUser add index index3(email(7));

上述sql语句的执行过程为
1.从 index3索引树找到满足索引是’zhangss’的这条记录，取得 ID2 的值；
2.到主键上查到主键值是 ID2 的行，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
3.取 index3 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangss’的条件了，循环结束。这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
前缀索引对覆盖索引的影响


select id,email from SUser where email='zhangssxyz@xxx.com';

如果要执行上述sql
用index1（普通索引）执行顺序为
1.从 index1 索引树找到满足索引是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；将结果加入到结果集。（因为id是主键，所以在二级索引的叶子节点上会存在所以不需要回表）
用index3（前缀索引）执行顺序为
1.从 index3索引树找到满足索引是’zhangss’的这条记录，取得 ID2 的值；
2.到主键上查到主键值是 ID2 的行，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；（因为系统并不确定前缀索引的定义是否截断了完整信息，就算你的前缀索引取的是这个字段的全部字符email(18)还是需要去回表的。）因此利用前缀索引是必须要回表的，因此前缀索引不能用于覆盖索引。

复合索引

当对服务器的多个索引做相交（and）操作时，则需要一个包含所有列的复合索引，而不是多个独立的单列索引。
当服务器需要对多个索引进行联合操作时（or），通常需要消耗大量的CPU和内存资源在算法的缓存，排序和合并操作上。特别是当其中有些索引的选择性不高时需要合并扫描大量的数据。然而优化器不会把这些计算到‘查询成本’，可能会影响查询的并发性，所以将查询改成UNION的方式往往更好。
在这里插入图片描述
以下的sql索引失效了（因为用了or）

将or改写成union之后索引成功的用上了

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，将数据存储与索引放到了一块，找到索引页就找到了数据。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值（即非主键和主键）紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引的设定

默认为主键。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoD会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包括相邻键值的页面可能会相距甚远。
(看到这里，如果你对B-Tree索引结构熟悉的话，就知道为啥[key、data]作为一个二元组存放在一个节点了)

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候（反过来也一样）。

聚簇索引的优点

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样子只需要从磁盘中读取少数的数据也能获取某个用户的全部邮件。
数据访问更快。聚簇索引把索引和数据都放在同一个B-Tree中，因此从聚簇索引中获取数据比从非聚簇索引中要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

1.最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
2.插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照逐渐顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE（当出现增删改的时候可能会出现碎片空间，可以通过这个对表重新组织一下，消除碎片空间）重新组织一下表。
3.更新聚簇索引列的代价很高。因为要强制InnoDB将每个被更新的行移动到新的位置。
4.基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页来容纳该行，这就是一次页分裂操作，这也意味着这样导致表占用更多的磁盘空间。
5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏时，或者由于页分裂导致数据存储不连续的时候。
6.二级索引(非聚簇索引)可能比想想的要更大。因为二级索引的叶子结点包含了引用行的主键列，所以主键字段的长度不宜过长。
7.二级索引访问需要两次索引查找，而不是一次。
使用Innodb时应该尽可能的安主键顺序插入，并且尽可能的使用单调增加的聚簇键的值来插入新行，否则则有可能导致8.InnoDB频繁的做页分裂，库导致大量的磁盘i/o，和大量的碎片空间。

覆盖索引

如果一个索引包含所有需要查询的字段值，我们就称其为“覆盖索引”，即：一个索引覆盖where条件的所有列。选择合适的索引顺序MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。不是所有的类型的索引都可以成为覆盖索引。覆盖索引必需存储索引列的值，而hash索引，空间索引和全文索引都不存储索引列的值，所以mysql只能使用B-Tree索引来做覆盖索引，不同存储引擎的实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引（如memory就不支持）
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排列。所以索引可以按照升序或者降序进行扫描，以满足符合列顺序的order by，group by和distinct等子句的查询需求。
创建一个复合索引(a,b,c),就相当于创建了a,(a,b),(a,b,c)三个索引。

select a,b,c from xxx where b=1;

上述sql语句因为跳过了a,所以不能用索引进行树搜索。(注意这里不是放弃用索引，而是不用索引的树搜索功能)

select a,b,c from xxx where a=1 order by c;

注意：这里的using filesort表示使用了文档排序

注意：这里的using index表示使用了索引来进行排序

上述查询虽然能用索引扫描但是不能用索引进行排序，如(1,1,1),(1,2,1),(1,2,2),(1,3,1)
1.首先通过a进行过滤（通过索引），得到(1,1,1),(1,2,1),(1,2,2),(1,3,1)
2…然后通过c来进行排序，因为a有序不能保证c有序（但如果过滤条件是a和b的话，是可以通过c来排序的如图二），得到
(1,1,1),(1,2,1),(1,3,1),(1,2,2),输出结果集。

覆盖索引的优点

1.索引条目通常远小于数据行的大小，所以如果只需要读取索引，那么MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝下。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放进去内存。
2.因为索引是按照列值顺序存储的，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
一些存储引擎，如MyISAM在内存中只缓存索引。数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
3.由于InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
所以多列索引列的顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的索引放在索引的最前列。
MySQL有两种方式可以生成有序的结果集：通过排序操作，或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能的同时满足这两种情况，即：索引列作为排序列。

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。
如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。order by子句和查找性查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行的顺序操作，而无法使用索引排序。

重复索引

重复索引是指在相同列上按照相同的顺序创建的相同类型的索引。应该避免这样的创建重复索引，发现以后也应该立即移除。
如:建了primary key，又建了一个唯一索引和非空

冗余索引

冗余索引和重复索引有一些不同，比如：如果创建了索引（A,B），再创建（A）那就是冗余索引，因为A就是前一个索引的前缀索引。索引（A,B）完全就可以当做A来使用。但是如果创建了索引（B,A）那就不是冗余索引了，索引B也不是。因为B不是索引(A,B）的最左前缀索引。另外，其他不同类型的索引，例如哈希，全文索引也不会是B-Tree的冗余索引。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A,B）而不是拓展已有的索引（A），还有一种情况是将一个索引扩展为（A,ID），其中的ID是主键，对于InnoDB来说主键列已经包含在二级索引当中了，所以这也是冗余的。
在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引区别不大。

6. 隐式转换导致索引失效

创建以下两个表

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL, 
 `trade_step` int(11) DEFAULT NULL,   
`step_info` varchar(32) DEFAULT NULL, 
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select count(*) from tradelog where month(t_modified)=7;

在这里插入图片描述
t_modified索引树
注意：上述的隐式转换，使用了函数破坏了树的有序性，优化器不是放弃使用t_modified这个索引，而是放弃用数的搜索功能，优化器会比较遍历主键和便利这个索引的消耗，比较过后发现这颗索引树更小，所以走了这个索引的全索引扫描。在这里插入图片描述
上述sql的执行过程
1.再tradelog里找到id=2的这一行。
2.取出tradelog中id=2对应的tradeid。
3.用l表中的tradeid与d表中的tradeid去关联然后查出对应的d表的所有数据。
该查询的驱动表走了主键（l.id），被驱动表是全表扫描，原因从l表中取出的值是utf8mb4的第三步等价于
select * from tradedetail where tradeid = $l2.tradeid
因为utf8mb4是utf8的超集，为了不丢失精度，自动类型转换会向utf8mb4转换所以第三步为
select * from tradedetail where convert(tradeid using utf8mb4)=$l2.tradeid
针对上述情况有两种优化方案，如果业务方的tradeid字段对大小写敏感，就可以修改trade_detail表tradeid的字段类型，如果对字段大小写不敏感可以用以下语句.

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

在这里插入图片描述
可以看到上述的过滤条件用到的主键索引进行过滤。然后用了tradeid进行关联。

以上sql不走索引的原因是做了隐式转换（varchar类型的tradeid’转换成整型的）

可以看到上述的执行计划的type是const说明走了主键，原因为是对右边的判断条件做了转换没有对id这个字段用函数，没有破坏索引的有序性，可以通过索引数进行搜索。等价于select * from test where id=1;

7.总结

1.联合索引，注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.查询记录的时候，少使用’*’，尽量去利用索引覆盖，可以减少回表操作，提升效率。
3.有些查询可以采用联合索引，进而使用到索引下推，也可以减少回表操作，提升效率。
4.禁止对索引字段使用函数、运算符操作，这样将会使索引失效。
5.字符串字段和数字比较的时候会使索引无效。
6.模糊查询’%值%'会使索引无效，变为全表扫描，但是’值%'这种可以有效利用索引。
7.排序中尽量使用到索引字段，这样可以减少排序，提升查询效率。