MySQL进阶-索引（四）

SQL提示

SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

 

 

 测试查询profession=软件工程的用户：

使用了联合索引，再为profession创建一个单列索引 ：

 再次进行测试使用哪种索引

 仍然使用联合索引

使用use index：使用索引（只是建议）

使用ignore index：忽略索引 

 使用force index：强制使用索引

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *。

首先查看该表的所有索引：

测试查询，关注点在select * 语句和Extra列上面：

 Extra列中using where,using index 效率更高于using index condition

 其中第二条查询语句比第一条多了一个name字段，已知tb_user表中有一个联合索引（profession,age,status），其中执行二级索引查询，叶子结点为主键id值，所以第一条查询语句可以直接返回结果，而第二个查询语句多了一个name字段，二级索引和叶子结点下没有，所以需要根据叶子结点下的id值去聚集索引查找name字段返回（回表查询），因此效率会更低

例如：

 在辅助索引中已经把Arm和id全部搜索出来了，就不用再去聚集索引查了

思考题：

一张表有四个字段（id,username,password,status）由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：

select id,username,password from tb_user where username = 'itcast';

对username和password建立联合索引，使用覆盖索引，避免回表查询。

前缀索引

当字段类型为varchar、text等，有时候需要索引很长的字符串，这会让索引变的很大，查询时会浪费大量的磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

与建立索引极其相似，唯一的区别是最后的n代表的是提取前几个 字符构建索引

前缀长度：

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

例如查看email索引的选择性(唯一索引的选择性为1）：

select count(distinct email)/count(*) from tb_user;

 我们可以逐渐缩小前缀长度来决定最佳选项：

 注释：

        substring为字符串截取函数，例如substring（email，1,4）代表的是截取email字段的第一到第四个字符。

我们考虑截取前五个字符作为前缀索引

create index idx_user_email_5 on tb_user(email(5));

 再次查询索引：

 其中一列sub_part代表的就是截取了几位作为前缀索引

测试查询：

前缀索引查询流程：

 辅助索引根据所截取的前缀长度，其中可能存在相同的前缀，因为底层是一个链表结构，所以如果第一个不是的话就查第二个，依次找到目标。

单列索引与联合索引

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

数据准备：

其中id为主键索引，phone、name、email都是单列索引，（profession、age、status）是联合索引

测试查询

 此时用到了phone字段的索引，一次只能用一个索引，而name字段则不在phone字段的二级索引上，所以会出现回表查询！

创建联合索引：

 再次测试查询：

 使用use index再次进行查询

 观察Extra列，使用联合查询后有Using index出现，效率更高

联合索引查询情况：

创建联合索引时需要考虑他的顺序（最左前缀法则），如上图phone字段和name字段

索引设计原则

1.针对数据量大，查询比较频繁的表建立索引。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3.尽量选择区分度高的列作为索引、尽量建立唯一索引、区分度越高，使用索引的效率越高。

4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率！

7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效用于查询