索引概述
介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足
特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构
上实现高级查找算法，这种数据结构就是索引。
若无索引的话，查找一个数据就需要全表扫描查找该数据，效率十分低下。
特点
劣势都可以忽略。磁盘空间占用无所谓，一般情况磁盘都是很便宜的。
增删改会降低效率主要是因为需要维护索引，往树中插入或删除节点都需要耗费时间。这点也可忽略，一般情况增删改的操作都很少，查的操作是最多的。
索引结构
mysql索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构
默认都是B+树结构
B树
B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5
个指针：(指针数为key+1，也就是阶数)
我们可以通过一个数据结构可视化的网站来简单演示一下。https://www.cs.usfca.edu/~gall
es/visualization/BTree.html
特点：
5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。
B+树
B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一
下其结构示意图：
绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
区别
最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
1、所有的数据都会出现在叶子节点。
2、叶子节点形成一个单向链表。
3、非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的
B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点
的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
Hash
和java中的哈希表一样，解决哈希冲突的方式是用链表形式。
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在
hash表中。
特点
A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
B. 无法利用索引完成排序操作
C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索 引
存储引擎支持
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是
InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
思考
为什么InnoDB存储引擎选择使用B+tree索引结构？
1、相比较于二叉树，层级更少，搜索效率高；
2、对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储
的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
3、相对Hash索引，B+tree支持范围匹配及排序操作；
分类
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
聚集索引和二级索引
在InnoDB中，索引有可分为两种
聚集索引选取规则：
1、若存在主键，主键就是聚集索引
2、如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3、如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索
引。
聚集索引和二级索引具体结构如下
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值。
当执行 select * from user where name = ‘Arm’，具体执行操作步骤如下
1、 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
2、由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最
终找到10对应的行row。
3、最终拿到这一行的数据，直接返回即可。
思考
以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
备注: id为主键，name字段创建的有索引；
A 语句的执行性能要高于B 语句。
因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然
后再查询聚集索引，也就是需要进行回表查询。
索引语法
创建索引
create (unique| fulltext) INDEX name ON table_name(index_col_name...);
unique 创建唯一索引 fulltext创建全文索引 不加代表常规索引
一个索引可以关联多个字段，若一个索引关联一个字段称为单列索引，关联多个字段称为联合索引或组合索引.
查看索引
show index from table_name;
删除索引
drop index index_name on table_name;
性能分析
SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信
息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数
通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据
库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以
查询为主，那么就要考虑对数据库的索引进行优化了。
慢查询日志（windows版）
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有
SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
打开慢查询日志 show variables like ‘slow_query%’
set global slow_query_log=1;
再次查看
设置慢查询时间
set global long_query_time=4;
再次查看慢查询时间，注意此时要用命令show global variables like ‘%long_query_time%’;
profile
能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling
参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
查看每条语句的耗时情况
explain执行计划
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行
过程中表如何连接和连接的顺序。
EXPLAIN 或者 DESC select语句;
其中各个字段的含义：
索引失效
1、最左前缀法则
如果索引了多列（联合索引），就要遵守最左前缀法则，否则索引会失效。最左前缀法则指的是：查询从索引最左列开始，不能跳过索引中的某一列，否则索引会部分失效（后面的索引会失效）
查看tb_user表的索引
show index from tb_user;
表中有联合索引
tb_user中列名排列顺序为
profession 、age和status字段，profession在最左边，使用索引查询时必须要有professsion字段。不用profession字段时，索引将会失效。
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
若无profession字段，可以看到没有使用联合索引进行查询
同理，如果只使用profession字段和 status字段，跳过了age字段，则status查询将不会使用到索引。
注意:只要查询语句中有对应字段即可，位置可随意排列，profession不必放在第一个查询位置
2、使用范围查询 (<,>)，范围查询右侧的列索引失效，使用<=或者>=不会失效。
3、 索引列运算也会失效
要使用索引查询时，不可以对查询的列进行运算，否则索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
使用字符串切割函数，phone列的索引就会失效
4、字符串不加引号，索引失效
5、使用头部模糊匹配，索引失效
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
第一条语句索引执行，其余两条失效
6、用or连接条件，or两侧的条件中都要有索引，只有前面的有后面没有，索引失效
7、 数据分布影响
mysql会对查询进行评估，若使用索引比全表查询更慢，则不适用索引。
SQL 提示
SQL提示是优化数据库的一个重要手段，就是再SQL语句中加入一些人为的提示达到优化的目的。
可以为联合索引中的列再创建单列索引
create index idx_user_pro on tb_user(profession);
此时,profession列有两个索引（联合索引和单列索引），如果查询profession时，mysql会随机选择一个索引执行。此时可以使用命令建议mysql执行哪个索引。
1、use index:建议MYSQL使用哪个索引（只是建议，mysql内部还会进行评，不一定采纳你的建议）
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2、 ignore index：忽略指定索引
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工 程';
3、force index:强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工 程';
覆盖索引
尽量使用覆盖索引，减少select * 那么什么是覆盖索引呢？ 覆盖索引是指 ：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
举例：id为聚集索引，name为二级索引，现在我执行一个语句
select id,name from table where name = 'A';
此时它不会进行回表查询，直接在二级索引中就可以查询需要的全部信息，直接返回即可。不需要先在二级索引中查到id，再根据id去聚集索引中查找对应的所有字段。
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
执行计划中Extra字段中有了值
因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。
如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。
前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
比如为某些文章建立索引，一篇文章的长度可是很长的，不可能用文章所有的字符建立一个索引，是不现实的，多以要截取文章前面一小部分当做索引。
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，
索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
比如要为email建立索引，先查看比值
select count(distinct email) / count(*) from tb_user;
然后选择一个合适的比值，截取适当的长度当做索引。
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
前缀索引的查询流程
上面以email的前5个字符建立了索引，当执行查询语句
select * from tb_user where email='1779990@sina.com';
会先截取前五个字符’17799’在索引中查找对应的id
查找到对应的聚集索引为3，就去聚集索引中查找信息
查找到以后，对比该email的值与查询语句中的email值是否相同，若对比结果发现不相同，根据B+树的存储结构叶子节点是双向链表，所以就查链表的下一个位置，id为4，再接着去聚集索引中查找id=4的所有信息，对比该email与需要查询的email='1779990@sina.com’是否相同，发现此时相同，即返回改行的所有数据。
单列索引与联合索引
单列索引：索引只包含单个列
联合索引：索引包含了多个列
若查询的条件中，字段都为单列索引，这样会进行回表查询从而影响sql性能。
phone和name都是单列索引
explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';
mysql只会根据其中一个索引来进行查询，查询到id后再回表查询另一个字段对比是否相同。
此时创建phone和name的联合索引
create unique index idx_user_phone_name on tb_user(phone,name);
此时再根据phone和 name 查询则不会进行回表查询（需要指定使用的索引）
构建联合索引时，B+树的创建默认根据其中一个字段进行排序，若该字段相同则再根据另一个字段排序
索引设计原则
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索
引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，
避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增
删改的效率。
create unique index idx_user_phone_name on tb_user(phone,name); 1
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含
NULL值时，它可以更好地确定哪个索引最有效地用于查询
扫一扫
4158
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
