MySQL索引&InnoDB存储引擎&聚簇索引、回表、索引下推、覆盖、前缀索引、最左匹配，索引失效、创建索引时机、不宜创建的列& SQL优化&查询执行过程&Index Key&Table filter

MySQL索引&InnoDB存储引擎&聚簇索引、回表、索引下推、索引覆盖、前缀索引、最左匹配，索引失效、创建索引时机& MySQL优化

什么是索引？

《高性能MySQL（第3版）.pdf》如是说：

当前数据库版本：

 

存储引擎

存储引擎：基于表而非数据库

MySQL数据库不同于其他数据的一个重要特点： 插件式的表存储引擎

存储引擎表：

• InnoDB： 支持事务、行锁、外键、非锁定读、聚簇索引、

• MyISAM：不支持事务、表锁、支持全文检索

查看mysql支持的引擎：

-- 查看支持的存储引擎
SHOW ENGINES
​
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'
​
--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename
​
--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

 

 

 

索引是谁实现的？

它帮助MySQL快速查询数据，由存储引擎实现！

命中索引，为何查询快？

不断缩小想要获取数据的范围来筛选数据、避免全表扫描

采用InnoDB存储引擎时，有哪些索引经常被提及？

索引类别	描述	特点	语句(ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）)
主键索引PRIMARY KEY	一个表只能有一个，未显示声明时，InnoDB会为每一行生成一个6字节的ROWID作为主键	唯一、主键索引是一种特殊的唯一索引，必须指定为 primary key、可被其他表引用、逻辑键、不可空、一种约束
唯一索引UNIQUE	可以有多个	列值唯一、可以有NULL值、物理键、不可被引用、可多个、一种索引	需判断唯一性
单列索引
组合索引	由多个字段组成的索引		//普通索引 alter table table_name add index index_name (column_list) ; //唯一索引 alter table table_name add unique (column_list) ; //主键索引 alter table table_name add primary key (column_list) ;
普通索引INDEX	无唯一性之类的限制	允许出现相同的索引的内容重复出现、仅加速查询	创建索引1: CREATE index <索引名> on table_name column_name; 增加索引2: ALTER table table_name add index <索引名> column_name;
自适应哈希索引
前缀索引	取索引列的前N个字段	对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。

索引的类型：

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值

• INDEX(普通索引)：允许出现相同的索引内容

• PRIMARY KEY(主键索引)：不允许出现相同的值

• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

 

索引类别(聚簇索引&非聚簇索引)

• 聚簇索引： 索引与数据存在一起；非聚簇索(也叫辅助索引)引反之！

B 树

根节点至少包括两个孩子
树中每个节点最多含有m个孩子(m>=2 )
所有叶子节点都位于同一层
除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子

假设每个非终端结点中包含有n个关键字信息，其中
a)Ki (i=1...n)为关键字，且关键字按顺序升序排序K(i-1)< Ki
b)关键字的个数n必须满足:[ceil(m/ 2)-1]<= n <= m-1
c)非叶子结点的指针:P[1],P[2],，...P[M];其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1]，K[i])的子树
 

B+ 树

B+树是B树的变体，其定义基本与B树相同，除了:
>非叶子节点的子树指针与关键字个数相同
>非叶子节点仅用来索引，数据都保存在叶子节点中
>所有叶子节点均有一个链指针指向下一个叶子结点
>非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树

为什么B+Tree更适合用来做存储索引

>B+树的磁盘读写代价更低
>B+树的查询效率更加稳定
>B+树更有利于对数据库的扫描

全文索引 是MyISAM的一个特殊索引，InnoDB存储引擎也支持！

 

回表（非聚簇索引查询，不能索引覆盖的话，必定回表查询）

回表就是普通索引未能覆盖，查询到主键，然后依据主键索引查询对应的记录

 

索引覆盖

使用非主键索引查询数据时就能获取到需要的字段，不需要查询聚簇索引！

如： SELECT age,name from user where name = '张三' and age = 18 ;

其中，age,name 创建组合索引，不需要回表查询记录（查询列要被所建的索引覆盖。）

如果仅name建了索引，虽然命中索引，但未覆盖，需要回表！！！

最左匹配

索引是复合索引，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个过程就是最左匹配特性。

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<.
between、like)就停止匹配，比如a = 3 and b = 4 and c > 5 and d = 6如果建立(a,b.c.d)顺序的
索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c= 3建立(a,b,c)索引可以任意顺序，mysql的查询
优化器会帮你优化成索引可以识别的形式
 

索引下推（Index Condition Pushdown） 优化

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的数据，减少回表次数！

如：MySQL数据库会取出索引的同时，判断是否可以进行 where 条件的优化，也就是将WHERE部分的过滤操作在存储层进行而非服务层！

何时合适创建索引

• 主键自动创建唯一索引

• 查询频繁

• 与其他表关联的字段，外键关系建立索引

• 排序字段，排序字段通过索引访问大幅提高排序速度

• 统计、分组字段

• 单键/组合索引选择查询，高并发下倾向创建组合索引

注意： 组合索引的列不宜超过5个 单表索引不宜过多 单表字段数不宜超过20 列值较长的应该分表存储

不适合创建索引

• 修改频繁字段

• 区分度不高（唯一性太差的字段不适合单独创建索引、数据分布比较均匀） 性别（要么男、要么女），但是某些状态的记录很少时且会被经常查询，建索引可以加速查询

• 数据量较少

• WHERE条件用不到的字段

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。

也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。（这种计算方式存在误差，而且没有计算叶子节点，如果计算叶子节点其实是深度为4了）

• 建立索引

         create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name (length) , …..);

         alter table table_name ADD INDEX [index_name] (index_col_name,...)

          添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

• 删除索引

          DROP INDEX index_name ON tbl_name; alter table table_name drop index index_name;

          删除主键(索引)比较特别: alter table t_b drop primary key;

• 查询索引(均可)

          show index from table_name;

          show keys from table_name;

          desc table_Name;

 

 

索引的优缺点

优势

• 提高数据检索效率，降低数据库IO成本

• 降低数据排序的成本，降低CPU的消耗

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息(页分裂、合并)

• 

索引失效:

• 字段做函数计算  
• 类型隐式转换
• 隐式字符编码转换
• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引
• like查询是以%开头

 

常用的SQL优化

大批量插入数据
对于MyISAM：
alter table table_name disable keys;
loading data;
alter table table_name enable keys;
对于Innodb：
1，将要导入的数据按照主键排序
2，set unique_checks=0,关闭唯一性校验。
3，set autocommit=0,关闭自动提交。
4，set foreign_key_checks = 0; 禁用外键
5，删除主外键和索引
6，对于插入操作尽量使用insert into table select或insert into table values(),(),()提高插入性能
7，控制小批量的范围不要太多行才提交，控制好缓存
8，优化group by 语句
    默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但      用户想要避免排序结果的消耗，则可以使用order by null禁止排序
9，有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
10，如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引
    select * from 表名 where 条件1=‘’ or 条件2=‘tt’

优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

count(*) 和 count(1)和count(列名)区别

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(列名)会比count(1)快

• 列名不为主键，count(1)会比count(列名)快

• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)

• 如果有主键，则 select count（主键）的执行效率是最优的

• 如果表只有一个字段，则 select count(*) 最优。

SQL语句的执行过程

SQL解析

MySQL执行查询过程:

MySQL优化:

优化顺序及层次：

 

• （1）SQL 语句及索引的优化
• （2）数据库表结构的优化
• （3）系统配置的优化
• （4）硬件的优化

优化方法：

• （1）选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如’省份’、’性别’最好适用 ENUM
• （2）使用连接(JOIN)来代替子查询
• （3）适用联合(UNION)来代替手动创建的临时表
• （4）事务处理
• （5）锁定表、优化事务处理
• （6）适用外键，优化锁定表
• （7）建立索引
• （8）优化查询语句

语句优化: 

• （1）Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。
• （2）用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
• （3） 避免在索引列上使用计算
• （4）避免在索引列上使用 IS NULL 和 IS NOT NULL
• （5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• （6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• （7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

慢查询设置：

开启 ——> 设置慢查询日志文件路径 & 慢查询时间

show variables like '%quer%';
show status like '%slow_queries%';
set global slow_query_log = on;
set global long_query_time = 1; // 需要重新连接生效或者直接修改配置文件里的值

 

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file='/var/lib/mysql/logs/slow-query.log';
Query OK, 0 rows affected (0.00 sec)
# mysql必须对该目录有读写权限

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

或者直接修改配置文件：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/logs/slow-query.log
long_query_time = 1

重启mysql服务！！！

定位慢查询（需排除缓存干扰，SQL_NO_CACHE）

SQL_NO_CACHE 不一定真的不使用缓存，具体你可以试验下！（这两种说法都正确）

1.对当前query不使用数据库已有缓存来查询，则当前query花费时间会多点

2.对当前query的产生的结果集不缓存至系统query cache里，则下次相同query花费时间会多点

explain  + sql语句

如: 

Index Key 和 Table Filter

在 RR隔离级别下：

若SQL 语句的 Where 条件使用了两个索引，分别是唯一索引和非唯一索引。MySQL 会根据索引选择性等指标选择其中一个索引来使用，而另外一个没有被使用的 Where 条件就被当做普通的过滤条件，一般称被用到的索引称为 Index Key，而作为普通过滤的条件则被称为 Table Filter。多数情况下，唯一索引性能更高，通常也比主键索引高！

所以，该 SQL 执行的过程就是依次将 Index Key 范围内的索引记录读取，然后回表读取完整数据记录，然后返回给MySQL的服务层按照 Table Filter 进行过滤。

ICP (索引下推)技术

MySQL 5.6 推出的 ICP 技术其实就是 Index Filter 技术，只不过是因为 MySQL 分为服务层和存储引擎层，而 Index Filter 将原本服务层做的过滤操作“下推”到存储引擎层处理。将原来的在服务层进行的Table Filter中可以进行Index Filter的部分，在引擎层面使用 Index Filter 进行处理，不再需要回表进行 Table Filter。

这样做的好处就是减少了加锁的记录数，减少了回表查询的数量，提高了 SQL 的执行效率。

 

表数据少的时候索引不会被使用

extral 为 null 根据执行计划可知，实际SQL执行的时候并未使用索引！！！

 

优化器选择不使用索引的情况: 

在范围查找、JOIN连接操作等情况下，优化器通常会直接进行全表扫描来得到数据。

如果用户访问的数据量很小，则优化器还是会选择辅助索引；但当访问的数据占整个表的蛮大一部分时（一般20%左右），优化器会选择通过聚集索引来查找数据。因为顺序读远远快于离散读。

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是 ——> 通过辅助索引查到的数据是少量的。这是当前传统的机械硬盘特性决定的，利用顺序读来替换随机读。如果用的是固态硬盘（随机读操作很快），同时有足够的信心确认辅助索引可以带来更好的性能，那么可以使用关键字 FORCE INDEX来强制使用某个索引！

SELECT * FROM tb FORCE INDEX(索引列) WHERE 索引列 > 1000 and 索引列 < 1100

CREATE TABLE index_test (a INT, b INT, KEY(a), KEY(b)) ENGINE = INNODB;

INSERT INTO index_test SELECT 1,1;
INSERT INTO index_test SELECT 1,2;
INSERT INTO index_test SELECT 2,3;
INSERT INTO index_test SELECT 2,4;
INSERT INTO index_test SELECT 1,2;

DESC index_test

SELECT * FROM index_test

EXPLAIN SELECT * FROM index_test WHERE a = 1 and b = 2;

EXPLAIN SELECT * FROM index_test USE INDEX(a) WHERE a = 1 and b = 2;

EXPLAIN SELECT * FROM index_test FORCE INDEX(a) WHERE a = 1 and b = 2;

更详细SQL知识  官方文档SQL优化