文章目录

• 前言
• 一、索引优化
• • • 1、Btree索引
    • • 1.1、概述
      • 1.2、存储结构
      • 1.3、MHISAM引擎索引结构
    • 2、HASH索引
    • • 2.1、概述及存储结构
      • 2.2、HASH索引的弊端
    • 3、FULLTEXT
    • • 3.1、概述
      • 3.2、存储结构
    • 4、聚集索引
    • • 4.1、聚集索引
      • 4.2、举例
      • 4.3、误区
    • 5、非聚集索引
    • • 5.1、非聚集索引
      • 5.2、举例
    • 6、区别
    • 7、索引与主键的区别
    • 8、面试题：hash和btree的区别
• 二、索引利弊
• • • 1、索引的好处
    • 2、索引的弊端
• 三、判断是否应该建索引的条件
• 四、数据库性能相关命令
• • • • 1、查看每个客户端IP过来的连接消耗了多少资源
      • 2、查看某个数据文件上发生多少I/O请求
      • 3、查看每个用户消耗了多少资源
      • 4、查看总共分配了多少内存
      • 5、数据库连接来着哪里，以及这些连接对数据库的请求情况是怎样的？查看当前连接情况
      • 6、查看当前正在执行的SQL和执行show full processlist的效果相当
      • 7、数据库中哪些SQL被频繁执行,执行查询top10sql
      • 8、哪个文件产生了最多的I/O，读多，还是写的多
      • 9、哪个表上的I/O请求最多？
      • 10、哪个表被访问最多
      • 11、哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引
      • 12、列出所有做过排序的规范化语句
      • 13、哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表
      • 14、列出所有使用临时表的语句-----访问最高的磁盘临时表，然后访问内存临时表
      • 15、哪个表占用了最多的buffer pool？
      • 16、每个库占用多多少buffer pool?
• 五、删除的区别
• • • （1）DELETE语句
    • （2）TRUNCATE TABLE
    • （3）表和索引所占空间
    • （4）速度
    • （5）应用范围
    • （6）TRUNCATE table表名 速度块，而且效率高
• 六、delete
• 七、truncate
• 八、drop
• 总结

前言

索引作为数据库的优化选项，但索引也存在很大缺陷，索引会增加 增、删、改操作所带来的IO量与调整索引的计算量，所以我们作为运维工程师最主要的还是需要对优化选项索引进行优化，加快检索，较少占据的空间，同时，在MYSQL中，高频的SQL语句也是一名合格的运维工程师需要具备的技能。

一、索引优化

1、Btree索引

1.1、概述

Mysql数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引，正是其有益的检索表现，才使其有此地位。

1.2、存储结构

这类索引的物理文件大多就是以Btree结构

（B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点）来存储，但会有不同的存储引擎在使用Btree索引时，对存储结构稍作修改，例如MYISAM存储引擎，使用B+TREE的数据结构，相对于Btree而言，所有的数据都放在叶子节点上，且把叶子节点通过指针连接在一起，形成数据链表(链表就是线性表的链式存储方式。链表的内存是不连续的，前一个元素存储地址的下一个地址中存储的不一定是下一个元素。链表通过一个指向下一个元素地址的引用将链表中的元素串起来。)

以加快邻数据的检索效率。另外，对于INNODB存储引擎，虽然同样使用B+TREE作为索引的存储结构，但具体实现却与MYISAM截然不同，这也是作为MYISAM和iNNODB存储引擎的一个重要的区别。

（MYISAM：
*第一部分
1、物理文件结构
1.1、任何存储引擎都不可或缺的存放表结构定义 的.frm(form)文件，

1.2、存放表数据的.MYD文件[my data]，

【1.2.1、分为静态固定长度，动态可变长度、压缩三种格式，表数据默认不压缩，在创建表时，可以通过ROW_format设置默认压缩，也可以通过myisampack工具进行压缩；1.2.2、当表数据没有进行压缩时，静态与动态的区分就与表中的字段类型有关，当出现VARCHAR等可变长度类型时，这个表则为动态，没出现可变长度类型时，这个表为静态，动态;】

1.3、存放索引数据的.MYI(MY INDEX)；
第二部分：
1、支持的索引类型BTREE索引、R-tree索引、fulltext索引【MYISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的，访问速度块、对事务完整性没有要求。适合查询、插入为主的应用创建】；

第三部分；锁机制—支持表级锁定;

第四部分：事务处理----为保证效率、不支持事务处理；

第五部分：增删改查性能----select 性能较高，适合执行查询较多的情况使用第六部分：count()问题------myisam存储引擎记录表行数，所以在使用count()时，只需要取出存储的行数，而不是遍历表，效率高第七部分：适用场景：公司业务不需要事务的支持 单方面读取或写入数据比较多的业务MyISAM存储引擎数据读写都比较频繁场景不适合使用读写并发访问相对较低的业务数据修改相对较少的业务对数据业务一致性要求不是非常高的业务服务器硬件资源相对比较差：

总结：MYISAM适合单反向的任何场景、同时并发量不高、对事务要求不高的场景）*

INNODB存储引擎
1、物理文件结构
1.1、同MYISAM一样，INNODB存储引擎也有;.fm文件存储表结构定义

1.2、与MYISAM不同的是：innodb的表结构与索引数据是存储在一起的，但在这个文件中每张表都是独自占有一块表空间还是共享所有表空间，是由用户决定的，如果独享表空间，每个表的表数与索引数据就都会放在一个.ibd文件中，如果是共享表空间，通过innodb_data_file_path指定后，每次增加数据文件后必须重启才能生效，不方便。

1.3、InnoDB有支持事务及安全的日志文件，这个文件非常重要，InnoDB可以通过日志文件将数据库崩溃时已经完成但还没来得及将内存中已经修改但未完全写入磁盘的数据写入磁盘，也可以把已部分完成并写入磁盘的未完成事务回滚，保证数据一致性。如果错误删除日志文件会导致数据库崩溃且无法启动。

2、支持索引类型与MYISAM基本一致，但具体实现因为文件结构的不同有很大的差异

3、锁机制的改进：实现了行级锁，为承受高并发增加竞争力

4、增删改查性能：如果执行大量的增删改查功能，推荐使用INNODB存储引擎，他在删除操作时是对行删除，不会创建表

5、Count(*)问题：innodb存储引擎会遍历表以计算数量，效率低下

6、其他特点：提高了外键，提供了多版本数据的提取

7、读写阻塞与事务隔离级别相关，能非常高效的缓存索引和数据，表与主键以簇的方式存储，支持分区、表空间。支持外键约束，5.5版本后支持全文索引

8、清空整个表时，INNODB是一行一行的删除，效率很慢，MYISAM则会重建表）

1.3、MHISAM引擎索引结构

MHISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址，索引文件域数据文件分离，这样的索引称为“非聚簇索引”

(非聚簇索引:如果你去的不是图书馆，而是某城市的商业性质的图书城，那么你想找的书就摆放比较随意了，由于商业图书城空间比较紧正，藏书通常按照藏书上架的先后顺序来摆放的，所以如果查询到某书籍放在C区2柜3排5仓，但你可能要绕过F区，而不是A.B.C.D…连贯一致的，也可能同在C区的2柜，书柜上第一排是计算机类的书记，也可能最后一排就是医学类书籍；那么对照着来看非聚簇索引的概念就比较好理解了，非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；),

MYISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

2、HASH索引

2.1、概述及存储结构

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。

2.2、HASH索引的弊端

一般来说，索引的检索效率非常高，可以一次定位，不像B-Tree索引需要进行从根节点到叶节点的多次IO操作。有利必有弊，Hash算法在索引的应用也有很多弊端。

a、Hash索引仅仅能满足等值的查询，范围查询不保证结果正确。因为数据在经过Hash算法后，其大小关系就可能发生变化。

b、Hash索引不能被排序。同样是因为数据经过Hash算法后，大小关系就可能发生变化，排序是没有意义的。

c、Hash索引不能避免表数据的扫描。因为发生Hash碰撞时，仅仅比较Hash值是不够的，需要比较实际的值以判定是否符合要求。

d、Hash索引在发生大量Hash值相同的情况时性能不一定比B-Tree索引高。因为碰撞情况会导致多次的表数据的扫描，造成整体性能的低下，可以通过采用合适的Hash算法一定程度解决这个问题。

e、Hash索引不能使用部分索引键查询。因为当使用组合索引情况时，是把多个数据库列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。

3、FULLTEXT

3.1、概述

全文索引目前MYSQL中只有MYISAM存储引擎支持，并且只有CHAR、VARCHAR、TEXT类型，它用于替代效率低下的LIKE模糊匹配模式，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段

3.2、存储结构

同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置

4、聚集索引

4.1、聚集索引

聚集索引来源于生活尝试。这中索引可以说是按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录 进行 堆划分。即主要描述的是物理上的存储。

4.2、举例

比如图书馆新进了一批书。那么这些书需要放到图书馆内。书如何放呢？一般都有一个规则，杂志类的放到101房间，文学类的放到102房间，理工类的放到103房间等等。这些存储的规则决定了每本书应该放到哪里。而这个例子中聚集索引为书的类别。 正式因为这种存储规则，才导致 聚集索引的唯一性。

4.3、误区

有的人认为，聚集索引的字段是唯一的。这是因为sql server 中添加主键的时候，自动给主键所在的字段生成一个聚集索引。所以人们会认为聚集索引所加的字段是唯一的。思考一下上面这个问题。杂志类的书放到101房间。那么如果杂志类的书太多，一个101房间存放不下。那么可能101，201两个房间来存放杂志类的书籍。如果这样分析的话，那么一个杂志类对应多个房间。放到表存储的话，那么这个类别字段就不是唯一的了。

5、非聚集索引

5.1、非聚集索引

也可以从生活中找到映射。非聚集索引强调的是逻辑分类。可以说是定义了一套存储规则，而需要有一块控件来维护这个规则，这个被称之为索引表。

5.2、举例

同学如果想去图书馆找一本书，而不知道这本书在哪里？那么这个同学首先应该找的就是 检索室吧。对于要查找一本书来说，在检索室查是一个非常快捷的的途径了吧。但是，在检索室中你查到了该书在XX室XX书架的信息。你的查询结束了吗？没有吧。你仅仅找到了目的书的位置信息，你还要去该位置去取书。

5.3、对于这种方式来说，你需要两个步骤：1、查询该记录所在的位置；2、通过该位置去取要找的记录。

6、区别

聚集索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。

非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。

7、索引与主键的区别

主键：主键是唯一的，用于快速定位一条记录。
聚集索引：聚集索引也是唯一的。（因为聚集索引的划分依据是物理存储），而聚集索引的主要是为了快速的缩小查找范围，即记录数目未定。主键和索引没有关系。他们的用途相近。如果聚集索引加上唯一性约束之后，他们的作用就一样了

8、面试题：hash和btree的区别

B+树索引和哈希索引的明显区别是：

1、如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

2、如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

3、同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询；

4、哈希索引也不支持多列联合索引的最左匹配规则

5、B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

在MySQL中，只有HEAP/MEMORY引擎表才能显式支持哈希索引（NDB也支持，但这个不常用），InnoDB引擎的自适应哈希索引（adaptive hash index）不在此列，因为这不是创建索引时可指定。还需要注意到：HEAP/MEMORY引擎表在mysql实例重启后，数据会丢失。 通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引

二、索引利弊

1、索引的好处

1.1、提高数据检索的效率，降低检索过程中必须要读取得数据量，降低数据库IO成本。

1.2、降低数据库的排序成本。因为索引就是对字段数据进行排序后存储的，如果待排序的字段与索引键字段一致，就在取出数据后不用再次排序了，因为通过索引取得的数据已满足排序要求。另外，分组操作是先排序后分组，所以索引同样可以省略分组的排序操作，降低内存与CPU资源的消耗。

2、索引的弊端

2.1、索引会增加 增、删、改操作所带来的IO量与调整索引的计算量
2.2、索引要占用空间，随着数据量的不断增大，索引还会带来存储空间的消耗。

三、判断是否应该建索引的条件

1、较频繁的作为查询条件的字段应该创建索引

2、唯一性太差的字段不适合单独创建索引，即使频繁使用作为查询条件

3、增删改操作较多的数据库字段不适合建索引

四、数据库性能相关命令

1、查看每个客户端IP过来的连接消耗了多少资源

select * from sys.x$host_summary;

2、查看某个数据文件上发生多少I/O请求

select * from sys.x$io_global_by_file_by_bytes;

3、查看每个用户消耗了多少资源

select * from sys.x$user_summary;

4、查看总共分配了多少内存

select * from sys.x$memory_global_total;

5、数据库连接来着哪里，以及这些连接对数据库的请求情况是怎样的？查看当前连接情况

select host, current_connections, statements from sys.x$host_summary;

6、查看当前正在执行的SQL和执行show full processlist的效果相当

select conn_id, user, current_statement, last_statement from sys.x$session;

7、数据库中哪些SQL被频繁执行,执行查询top10sql

select db,exec_count,query from sys.x$statement_analysis order by exec_count desc limit 10;

8、哪个文件产生了最多的I/O，读多，还是写的多

select * from sys.x$io_global_by_file_by_bytes limit 10;

9、哪个表上的I/O请求最多？

select * from sys.x$io_global_by_file_by_bytes where file like “%ibd” order by total desc limit 10;

10、哪个表被访问最多

select * from sys.x$statement_analysis order by avg_latency desc limit 10;

11、哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引

select * from sys.x$statements_with_full_table_scans;

12、列出所有做过排序的规范化语句

select * from sys.x$statements_with_sorting

13、哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表

select db, query, tmp_tables, tmp_disk_tables from sys.x$statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

14、列出所有使用临时表的语句-----访问最高的磁盘临时表，然后访问内存临时表

select * from sys.statements_with_temp_tables

15、哪个表占用了最多的buffer pool？

select * from sys.x$innodb_buffer_stats_by_table order by allocated desc limit 10;

16、每个库占用多多少buffer pool?

select * from sys.x$innodb_buffer_stats_by_schema order by allocated desc limit 10;

五、删除的区别

（1）DELETE语句

执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

（2）TRUNCATE TABLE

一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（3）表和索引所占空间

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（4）速度

drop>truncate>delete

（5）应用范围

TRUNCATE 只能对TABLE
DELETE可以是table和view

（6）TRUNCATE table表名 速度块，而且效率高

truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（7）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

六、delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

七、truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

八、drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

总结

索引优化的方式本章总结了三点：第一是Btree索引，第二点是hash索引，第三点是FULLTEXT索引，同时，进一步理解聚集索引和非聚集索引，聚集索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。 非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。使用删除时，分为drop,Truncate,delete.drop是删除表，不可回滚，truncate删除不可回滚，delete删除表后，每一步存入日志，方便回滚。