MySQL索引原理

1、为什么要有索引 ?

   一般应用系统, 读写比例10:1左右, 并且插入和更新数据会很少出现性能问题. 而最大的问题就是查询操作, 为了加快查询操作, 使用索引进行查询优化.

2. 索引的概念

【1】索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。
【2】当表中的数据量越来越大时，索引优化是对查询性能优化最有效的手段。
【3】索引能够轻易将查询性能提高好几个数量级。
【4】索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

3. 添加索引要注意的事项

【1】添加索引要注意平衡点, 并不是越多越好.
【2】添加索引最好在设计表开初进行添加

4、索引的原理

【1】本质
  索引的目的在于提高查询效率. 其本质是通过不断缩小查询数据的范围来筛选出想要的结果, 同时把随机的事件变成顺序的事件, 有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
【2】原理
  ① 在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构
  ② 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
  ③ 索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。
  ④ 数据库使用索引以找到特定值，然后顺时针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

5、索引的数据结构

【1】 索引的存储是基于b+树(balance tree)的数据结构来构建的。

【2】b+树特性
① 索引字段要尽量的小，IO次数取决于b+树的高度，树的高度越低，查询速度越快。
② 索引的最左匹配特性：b+树是按照从左到右的顺序来建立搜索树的，检索时会先按照最左边字段查询，如相同在依次向右进行检索. 即索引的最左匹配特性.

6、聚集索引与辅助索引

   ① 在数据库中，B+树的高度一般都在2-4层，因而查找某一个键值的行记录时最多只需要2到4次IO，2~4次的IO意味着查询时间只需要 0.02 ~ 0.04秒。
   ② 数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。
   ③ 聚集索引与辅助索引相同点：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
   ④ 聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息。

1）聚集索引

   InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

   如果未定义主键，MySQL取第一个唯一索引（ unique）而且只含非空列（ NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
   如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
   由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
   在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
   此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

聚集索引的好处：
   ① 它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。
   如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。

   ② 范围查询（ range query）
   如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

2）辅助索引

   表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
   叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
   由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
   辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

   举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

聚集索引和非聚集索引的区别：

聚集索引：
   ① 记录的索引顺序与物理顺序相同，因此更适合 between and和 order by操作。
   ② 叶子结点直接对应数据，从中间级的索引页的索引行直接对应数据页。
   ③ 每张表只能创建一个聚集索引。

非聚集索引：
   ① 索引顺序和物理顺序无关；
   ② 叶子结点不直接指向数据页；
   ③ 每张表可以有多个非聚集索引，需要更多磁盘和内容，多个索引会影响 insert和 update的速度。

7、MySQL索引管理

1）功能

   ① 索引的功能就是加速查找
   ② mysql中的 primary key, unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能。

2）MySQL索引分类

1. 普通索引: INDEX            #  加速查找

2. 唯一索引
    1) 主键索引: PRIMARY KEY  # 加速查找+约束(不为空、不能重复)
    2) 唯一索引: UNIQUE       # 加速查找+约束(不能重复)

3. 联合索引
    1) 联合主键索引: PRIMARY KEY(id,name)
    2) 联合唯一索引: UNIQUE(id,name)
    3) 联合普通索引: INDEX(id,name)

不同数据库对索引的支持情况：

1. 创建索引时, 可以指定索引类型
    1) hash类型的索引     # 查询单条快，范围查询慢
    2) btree类型的索引    # b+树，层数越多，数据量指数级增长(较为常用，innodb默认支持)

2. 不同的存储引擎支持的索引类型也不一样
    1) InnoDB 支持事务, 支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
    2) MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
    3) Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
    4) NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
    5) Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引

3）索引的创建/删除

① 创建表时

create table 表名 ( 字段1 数据类型 [完整性约束条件…], 字段2 数据类型 [完整性约束条件…]...,
                    [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY 
                    [索引名] (字段名 [(长度)] [ASC | desc]) );

示例：

create table t1(id int, name char(10), unique key u_id(id), index u_name(name));

② CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL ] index 索引名 
            on 表名 (字段名[(长度)]  [ASC |DESC]) ;

示例：

create index u_id on t1(id);

③ ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

示例：

alter table t1 add index u_id(id);
alter table t1 add index(id);

④ 删除索引

DROP INDEX 索引名 ON 表名;

小结

   ① 一定是为搜索条件的字段创建索引，比如 select * from s1 where id = 100;就需要为id加上索引。

   ② 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快。比如 create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。

   ③ 需要注意：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则有单独的索引文件table1.myi。

   ④ MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 innodb中，表数据文件本身就是按照B+Tree（BTree即 Balance True）组织的一个索引结构，这棵树的叶节点 data域保存了完整的数据记录。这个索引的key是数据表的主键，因此，innodb表数据文件本身就是主索引。
  因为inndob的数据文件要按照主键聚集，所以 innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为 innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型。

8、索引的正确使用

1）索引未命中

   并不是说我们创建了索引就一定会加快查询速度，若想利用索引来提高查询速度，我们在添加索引时，必须遵循以下规则：

1) 合理使用符号和关键字，避免范围过大或条件不明确：>、>=、<、<=、!= 、 between...and...、 like。
2) 尽量选择区分度高的列作为索引，如果索引字段的数据都为重复值, 这样并不会提高效率。
3) 索引列不能在条件中参与计算，保持列"干净".
4) 慎重使用 and / or
5) 最左前缀匹配原则：对于组合索引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）and的工作原理

条件：a = 10 and b = 'xxx' and c > 3 and d =4
索引：制作联合索引(d,a,b,c)

工作原理：

对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

2）or的工作原理

条件：a = 10 or b = 'xxx' or c > 3 or d =4
索引：制作联合索引(d,a,b,c)

工作原理：

对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

3）对索引的影响

1) 对于sql中的or来说，即便找到一个为假的条件也要去判断另外的条件是否成立，所以mysql并没有进行优化。
2) 所有的查询都是从左向右一次进行的，所以使用了or的条件语句很难命中索引。
3) 对于sql中的and，由于只要找到一个为假的条件就可以放弃判断整个语句，所以mysql进行了优化。
4) 在所有的and条件中会找到第一个区分度最高的列来使用它的索引，来达到加快查询速度的效果。
   
最左前缀匹配原则, 对于组合索引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的顺序可以任意调整。

示例:

create index union_ind on s1(id,email,name)

联合索引： 第一个索引项是这个索引的姓，每一次查询条件必须带着第一个索引项字段(例id)，才能命中联合索引。如果条件中丢失了第一个索引项来创建条件，那么不能命中联合索引。

组合索引最左前缀，如果组合索引为：(name，email)

name and email       -- 命中索引
name                 -- 命中索引
email                -- 未命中索引

其他注意事项

• 使用count(*)
• 创建表时尽量使用 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合组合索引）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合

9、联合索引与覆盖索引

1）联合索引

① 什么是联合索引 ？
  联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列。

② 语法

create table t1(a int, b int, primary key(a), key index_a_b(a, b)); # a b为联合索引

③ 联合索引的使用

• 联合索引遵循最左匹配原则, 可以使用(a)作为索引进行查询, 也可以使用(a, b)作为索引查询。
• 联合索引是在第一个键相同的情况下, 对第二个键进行了排序处理, 不能以(b)单独作为索引。

2）覆盖索引

① 什么是覆盖索引？
   InnoDB存储引擎支持覆盖索引(索引覆盖), 即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录 (InnoDB版本大于 1.0的，或MySQL数据库版本为 5.0以上支持覆盖索引)。
  辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

select id from s1 where id=1000; # id 为索引值, (Using index)代表覆盖索引

② InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。如果表中有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器会选择辅助索引。

③ 对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引。

10、查询优化神器 (explain)

1）执行计划：让mysql预估执行操作(一般正确)
all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

例如索引为:id, email

慢：
   explain select * from userinfo where name='blue'
       type: ALL(全表扫描)
   select * from userinfo limit 1;
   
快：
   select * from userinfo where email='jack'
   type: const(走索引)

2）慢查询优化的基本步骤

① 先运行看看是否真的很慢，注意设置 SQL_NO_CACHE。
② where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高。
③ explain查看执行计划，是否与预期一致(从锁定记录较少的表开始查询)。
④ order by limit形式的sql语句让排序的表优先查。
⑤了解业务方使用场景。
⑥ 加索引时参照建索引的几大原则。
⑦ 观察结果，不符合预期继续从开始分析。

3）慢日志管理

慢日志
    - 执行时间 > 10
    - 未命中索引
    - 日志文件路径
            
配置：
    - 内存
        show variables like '%query%';
        show variables like '%queries%';
        set global 变量名 = 值
    - 配置文件
        mysqld --defaults-file='E:\mysql-5.7.16-winx64\my-default.ini'
                
        my.conf内容：
            slow_query_log = ON
            slow_query_log_file = D:/....

注意：修改配置文件之后，需要重启服务。

---

MySQL日志管理

错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志： 审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等

1）bin-log

① 启用

vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
service mysqld restart

② 暂停

//仅当前会话
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;

③ 查看

查看全部：
# mysqlbinlog mysql.000002

按时间：
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 

按字节数：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

④ 截断bin-log（产生新的bin-log文件）

a. 重启mysql服务器
b. # mysql -uroot -p123 -e 'flush logs'

⑤ 删除bin-log文件

# mysql -uroot -p123 -e 'reset master' 

二、查询日志

启用通用查询日志
# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart

三、慢查询日志

启用慢查询日志
# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3  单位为秒
查看慢查询日志
测试:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);

转载自：本文