MySQL之索引（三）

什么是索引？有什么用？ 

 

索引是帮助MySQL高效获取数据的一种数据结构

索引可以提高MySQL数据查询效率。例如我们要找书名为"MySQL"的书籍，那么我们的筛选条件是where book_name = "MySQL"，此时如果没有排序，我们可能要进行全表扫描，反之若排了序，直接去M开头里面的数据找就行了。

也就是说数据库中除了存放数据之外，还会维护一个数据结构使得我们的查询效率提高，这种数据结构就是索引，比如B+树索引。

一般索引结构会很大，所以不会放在内存中，而是以索引文件的形式存放在磁盘上。

索引的优劣：

提高数据库检索效率，降低IO成本；通过索引对数据进行排序降低排序成本，减少cpu消耗。

索引需要额外占用空间；索引虽然提高了查询效率，但是修改效率会降低，如insert、delete、update。

索引的结构有很多，如B+tree索引、Full-text索引、R-tree索引、hash索引等，不同的存储引擎支持情况不一样，这里只介绍InnoDB支持的B+tree索引。

关于B+tree忘了的可以去数据可视化网站亲自插入一组数据回顾：

B+ Tree Visualization

InnoDB中的B+tree索引结构：

为什么InnoDB选择B+tree索引？

hash索引只能做精确匹配，不支持范围查找和排序。

 如果选二叉树做索引，我们知道二叉树最坏情况下可能会退化为链表，如果改用红黑树保证平衡性，在大数据量的情况下树的层级还是会过深从而降低效率，所以引出多路平衡查找树（B-tree），但是B树的每一个节点都会存数据，这样就导致一个结点放不了太多指针，从而使得树的层级加深，使用B+树就可以让非叶节点只存指针，不存数据，数据用最下面一层的叶子节点存，并且叶子结点之间通过双向循环链表建立连接从而支持范围匹配和排序操作。

索引分类

 全文索引就是通过关键字匹配的方式查询，和like相似，但是like在数据量大的情况下效率低，另外全文索引只能针对char、varchar、text列建立索引。

在InnoDB中，索引又可分为聚集索引和二级索引

红色为聚集索引，绿色为二级索引，聚集索引的叶子结点存该行数据，二级索引叶子结点存该行的主键。 

聚集索引

每张表有且仅有一个

• 如果存在主键索引，那么主键索引就是聚集索引。
• 如果不存在主键索引，那么唯一索引就是聚集索引。
• 如果唯一索引也没有，那么InnoDB会自动生成rowid作为隐藏的聚集索引

回表查询

查询时，先通过二级索引找到主键，再通过主键去聚集索引里面取数据，这个过程就是回表查询。

也就是说select * from user where id = 1 比 select * from user where name = "abc"效率更高。

那么InnoDB的B+tree一般有多高呢？

假设一行数据大小1k，一页16k，那么一页能存16行数据，InnoDB的指针1个占用6字节，主键假设为bigint，1个占用8字节。

---

当高度为2时：

n*8 + (n+1)*6 = 16 * 1024

算出n约为1170，上式中n为主键个数，n+1为指针个数。

1170*16 = 18736 

那么一页能存1170个主键，也就是18736行数据。

---

当高度为3时：

1170 * 1170 * 16 = 21939856

能存2千多万条数据

---

索引语法

创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

查看索引

SHOW INDEX FROM table_name;     查看某张表的全部索引

删除索引

DROP INDEX index_name ON table_name;    删除哪张表的哪个索引

练习

给tb_user表创建索引，要求如下：

1. name字段为姓名字段，该字段可能出现重复值，为该字段创建索引。

create index idx_user_name on tb_user(name);

2. phone手机号字段的值非空且唯一，为该字段创建唯一索引。

create unique index idx_user_phone on tb_user(phone);

3. 为profession、age、status创建联合索引。

create index idx_user_pro_age_sta on tb_user(profession,age,status);

4. 为email建立合适的索引来提升查询效率。

create index idx_user_email on tb_user(email);

SQL性能分析

在做SQL优化时，我们一般是针对select语句进行优化，如果一张表特别大，但是主要以插入操作为主，那么就不需要优化，所以我们需要先知道一张表增、删、改、查的频率是怎么分布的。

通过 SHOW GLOBAL STATUS LIKE 'Com_______';（7个下划线）查看各语句执行次数。

现在我们知道某张表的查询频率比较多，需要做优化，那么应该针对哪些select语句做优化呢？

慢查询日志

慢查询日志记录了所有执行时间超过预设时间（默认10s）的sql语句，我们可以通过

SHOW VARIABLES LIKE 'slow_query_log';  

想要开启慢查询日志需要去MySQL的配置文件(my.cnf)中配置如下信息：

# 开启MySQL慢查询日志开关

slow_query_log=1

#设置慢查询日志的时间为2s，SQL语句执行时间超过2s就会被记录

long_query_time=2

配置完毕后重启MySQL服务器即可查看慢查询日志(/var/lib/mysql/localhost-slow.log)中记录的信息

profile详情

慢查询日志仅记录执行时间超过预设时间的sql语句，如果有一些sql语句很简单并且数据量不大，但是执行时间却接近预设时间，这样的sql我们也需要优化，那么我们怎么定位这些sql呢?除了慢查询，我们还可以利用profile手段定位sql。

show profiles 能够帮助我们了解一条sql执行的时间都花在哪去了

先执行  SELECT @@have_profiling   查看当前MySQL是否支持profile操作。

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

SET profiling = 1；

#查看每一条SQL的耗时基本情况

show profiles；

#查看指定query_id的sql语句各个阶段的耗时情况

show profile for query query_id;

#查看指定query_id的sql语句cpu使用情况

show profile cpu for query query_id;

explain执行计划（用得比较多）

使用方式：直接在sql语句前加上explain即可。

id是指执行顺序，相同即从上往下执行，不同则值越大越先执行。

select_type表示select的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY（主查询，即外层的查询）、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY（SELECT/WHERE之后包含了子查询）等。

type表示连接类型，性能由高到低依次为NULL（查询不涉及表就可以，业务中几乎不可能）、system、const(通过主键或唯一索引查询)、eq_ref、ref（非唯一性索引）、range、index（扫描整个索引树）、all（全表扫描），优化时尽量不出现all。

possible_key显示可能应用在这张表上的索引，一个或多个。

Key实际使用的索引，如果为NULL，表示没有使用索引。

Key_len表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度。

rowsMySQL认为必须要执行的查询行数，在innoDB引擎的表中，是一个估计值，可能并不准确。

filtered表示返回结果的行数占需读取行数的百分比，filtered值越大越好。

Extra字段值如果为using index; using where，表示查找使用了索引，并且需要的数据在索引列就能找到，所以不需要回表查询；如果字段值是using index condition则表示查找使用了索引但也用到了回表查询，效率会低一些。（MySQL不同版本字段值可能不同，不用记）

一般type字段就可以反映出sql语句的性能。

最左前缀法则

针对联合索引的，当一个索引与多列有关系，那么在查询时的条件要按顺序从左到右写（从右往左可以少写，但如果出现了跳跃则跳跃过后的字段会索引失效，也就是说第一列不包含就全部失效）。

比如前面建立了一个联合索引(profession、age、status)，那么

where profession = '1' and age = 13 and status = '0'

就可以利用索引增加性能，同理只包含两个字段

where profession = '1' and age = 13

也可以，但是如果是

where profession = '1' and status = '0'

这样写的语句就导致status字段没用索引

但是下面这条虽然顺序不对，mysql会帮我们进行优化，真正执行的sql age会在status前面，所以利用了索引

 where profession = '1'  and status = '0' and age = 13

注：这里并不是说where子句中必须要先写profession，而是说最左一列profession必须出现，也可以写在后面。

范围查询

联合索引中，出现范围查询(<,>,>=,<=)，范围查询右侧的列索引失效，比如：

where profession = '1' and age > 13 and status = '0'

中，由于age使用>查询，所以status一列索引失效。

因为status有序只是在age相同的情况下，如果age是范围查询那么查询结果可能有多个不同的age，status就相对无序了。

索引列运算

尽量不要再索引列上做运算（包括函数运算），索引会失效。

select * from tb_user where substring(phone,10,2) = '15'

这里使用了函数运算，phone的索引会失效。

字符串不加引号

字符串不加引号也会导致索引失效。

模糊匹配

尾部模糊匹配不会导致索引失效，但是头部模糊匹配则会导致索引失效。

or连接导致的索引失效

用or分割的条件，只有当两侧都建立了索引，才不会导致索引失效。

select * from tb_user where id = 10 or age = 23

这里id建立了索引，age因为建立的是联合索引，根据最左前缀法则，单独使用age是没有索引的，所以这句sql会出现索引失效。

数据分布影响

如果MySQL评估使用索引比全表扫描满，则不使用索引（范围查询时如果大多数数据都满足要求则可能出现这种情况）

SQL提示

SQL提示是数据库优化的一个重要手段。

还是以之前的pro、age、status联合索引为例，如果此时我给pro单独加上一个单列索引，那么在查询条件用到pro时，MySQL会优先选择联合索引，那么如果我想让MySQL使用单列索引就需要手动去进行SQL提示。

use index：

使用某索引（MySQL权衡利弊后可能不会接受我们的建议）

select * from tb_user use index(idx_user_pro) where profession = '123'

ignore index:

忽略某索引

select * from tb_user ignore index(idx_user_pro_age_sta) where profession = '123'

force index:

强制使用某索引

select * from tb_user force index(idx_user_pro) where profession = '123'

select后尽量不要使用*

哪种情况下使用*会导致性能变差？

现在有一级索引和二级索引如下：

 

 select * from tb_user where name='jack' 这里先走二级索引拿到id，再去一级索引拿整行信息，如果我们只需要知道id和name，就这样写：

select id , name from tb_user where name = 'jack'，此时二级索引就能拿到需要的信息，不会出现回表查询，性能就更高。

练习：一张表有四个字段（id,pwd,username,status），由于数据量大，需要对下面的sql进行优化，该如何优化才是最优方案？

select id,username,pwd from tb_user where username = 'jack'

应该给username和pwd建立联合索引（只给username建立单列索引会出现回表查询，不是最优方案）。

前缀索引

当字段类型为字符串（varchar、text）时，索引树可能占用空间过大，并且索引查询效率也会受到影响。此时可将字符串的一部分前缀建立索引以节约空间。

create index idx_xxx on table_name(column_name(n))

和之前唯一区别就是column_name后面多了一个n来指定要抽取的前缀长度。

那么前缀长度多少合适呢？这里给出选择性的概念

选择性：不重复的索引值（基数）和数据表的记录总数的比值

选择性越大越好，怎么得到选择性？

某一列的选择性计算公式：

select (count(distinct column_name)) from tb_user / select count(*) from tb_user

选择性等于1性能最好，我们应该在选择性尽可能趋于1的情况下截取的前缀越短越好。

select count(distinct substring(email,1,n)) / count(*) from tb_user ; 

假设email字段长为10，那么n可以从10开始取，不断减1，选择性会不断降低，不过前缀长度也会减少，实际场景中可以权衡选择性和前缀长度对n进行选择。

单列索引&联合索引

实际场景中应该尽量使用单列索引还是联合索引呢？联合索引

select id,phone,name from tb_user where phone = '1' and name = '2'

如果我对phone和name各自建立了单列索引,MySQL只会使用其中一个效率比较高的索引，这里是phone索引，name则会通过回表查询去筛选，所以这里我们应该建立phone和name的联合索引，并且使用use index来指定使用联合索引，这样可以避免回表查询降低效率

索引设计原则

 几千几万条数据不算大，上百万条再考虑建立索引