Database

发布日期: 2021-08-12

文章字数: 5.3k

阅读次数:

存储引擎与数据结构

索引的数据结构主要有散列表、红黑树、跳表、B+树以及有序数组；

MySQL的索引与存储引擎相关，常用的存储引擎有InnoDB、MyiSAM、Memory，在MySQL5.5版本后，InnoDB已经作为默认的存储引擎，并且很多互联网公司要求只允许使用InnoDB存储引擎；Memory作为临时表的默认存储引擎，支持的数据结构为：

	MyISAM	InnoDB	Memory
B+ Tree索引	支持	支持	支持
Hash索引			支持
R-Tree索引	支持
Full-Text索引	支持

InnoDB存储引擎只支持B+树；B+树比较适合磁盘存储，是多路平衡搜索树，最佳时间N值为1200左右，树高为4时就可存储1200的3次方，即17亿左右数据；由于第一层数据总是存储在内存中，相当于17亿数据，最多查询磁盘3次，如果第二层数据正好在内存中，那么最多查询2次磁盘；也就是说InnoDB的最底层数据是B+树，B+树可能存储在内存中也可能存储在磁盘中，存储的单元是数据页（即操作系统数据缓存页：内存与磁盘的链接点）；

由于缓存池大小容量有限，只能将数据命中率最高的数据放入缓存中，MySQL使用LRU缓存淘汰算法；LRU可以理解成一个链表，链表的节点就是数据缓存页，刚被访问过的放到链表的已投，最早被访问过的放到另一头，当有新的数据缓存页被访问加入时，从最早的一头淘汰；LRU由散列表+双向链表组成，类似于Java中的LinkedHashMap的数据结构；

由于磁盘局部性原理，访问数据页时有预读功能，即我们从磁盘中获取到了多余的数据页，加入LRU的话就是浪费存储空间；用户全表扫描可能会对LRU造成冲击，可能需要很长的时间才能让缓存命中率恢复；针对预读数据页和冷数据扫描的情况，MySQL对LRU进行了优化，将LRU链拆分为young区（5/8）和old区（3/8）；当数据需要淘汰时，直接从old区的末尾开始，而当新访问数据页时先判断在缓存中是否存在，如果不存在则直接将数据添加到old区，否则当满足innodb_old_blocks_time（默认值为1000，单位为毫秒）的数据移动到young区域；

对于数据库而言，数据页或者数据本身的存储结构为：表空间 –> 段（叶子点段、非叶子点段、回滚段） –> 区 –> 页 –> 行；

表空间：数据库有一个或多个表空间组成；表是一个逻辑容器，表空间存储的多项是段，在一个表空间中有一个或多个段，但一个段只能属于一个表空间；
段：段由一个或者多个区组成，段不要求区之间是否相邻，当创建数据库或者索引时，就会创建相应的段；
区：在InnoDB中，一个区会分配64个连续的页；
页：默认大小为16KB；

主键索引与二级索引

InnoDB索引由B+树构成，B+树由非叶子节点和叶子节点组成；非叶子节点不存储数据，而叶子节点存储数据；InnoDB中主键索引叶子节点存储的是整个表的行数据信息，称为聚簇索引，而非主键索引（二级索引）的叶子节点存储的是对应主键的值（内存地址、指针）；

对表创建非主键索引时，相当于是维护了一颗二级索引的B+树，经过优化器判断后知道要查找该二级索引B+树；该B+数的非叶子节点存储的是索引字段的值，叶子节点存储的是索引字段行对应主键索引B+树的值，查询过程就是两个B+树；二级索引查找到主键ID后到主键索引上的查询过程叫做回表；

复合索引

复合索引就是将一个表的多个字段创建为同一个索引，即在B+树上非叶子节点上存储的是多个有序字段的值；有序就是最左原则；（最左原则要求必须以简历索引的第一个字段作为第一个查询限制条件；）

复合索引如果在查询时，查询条件包含所有索引字段，MySQL会进行自动优化顺序；

索引中包含非等限制影响索引效率；

覆盖索引

作用：规避回表

如果查询的字段包含在二级索引中，那么就没有必要进行回表，称为覆盖索引；所以一般要求我们在写select语句时不要使用select *；

索引下推

作用：减少回表

MySQL 5.6之后，当查询限制条件中，同时包含多个复合索引限制条件，会在满足了第一个索引限制条件后，继续在复合索引上根据第二个索引进行过滤，满足条件后再进行回表，减少回表次数，该功能称为索引下推（Index Condition PushDown）；

索引前缀

作用：长字符串索引优化

添加索引时使用alter table tablename add index indexName( colName)默认为全字段索引；如果索引字段为字符串，且字符串非常大，全字段索引僵尸一颗非常庞大的B+树；此时，如果字符串的区分度非常大，比如字符串前N位可排除大部分数据，那么我们可以为该字段创建一个前缀索引，语法为alter table tablename add index indexName( colName(8))；

前缀索引优化方案：

倒序：

如果索引字段前面部分区分度不够大，而后面部分区分度比较大，则可以对字段进行逆序后再创建前缀索引；
增加Hash字段

利用散列函数，增加一个hash值字段；使用时需要均衡增加字段与长字符串索引的代价；

索引前缀与覆盖索引互斥，即索引前缀肯定会回表；

索引选择

当一个MySQL表创建了索引，但是执行查询语句时可能并不会走索引，即索引失效；表上创建了多个索引，在执行查询时却选择错了索引，导致执行SQL并没有按照预期执行；这是谁的锅呢？MySQL中执行SQL时由优化器自行选择索引，我们可以根据执行计划查看选择的索引和执行路径；优化器选择索引的目的，是为了找到一个最优执行方案，并用最小的代价去执行语句；

影响优化器选择索引的因素有：

扫描数据行数（基于采样）；
是否回表操作；
是否使用临时表；
是否使用排序；

如果执行计划不是按照我们预期执行，那么我们可以通过以下措施引导或强制：

强制指定索引，语法为select * from tablename force index(indexName) where xxx；
优化SQL，匹配最左原则；
新建合适索引或删除误导索引；

索引采样统计信息

区别度：一个索引的不同值的个数，我们建立索引的时候有一个原则就是区别度越大性能优化约好，索引一般避免使用枚举字段；

基数：统计样本的记录数；

采样率：InnoDB一般不会使用所有表的行数作为基数进行分析，否则会对性能影响很大；一般通过配置项N个数据页上统计区别度的平均值，再乘以所有数据页数，得到基数（即基数是一个近似值）；数据不断更新，那么统计信息也会不断更新，触发统计信息更新的条件为：当数据行数超过1/M；其中M和N是根据innerdb_stats_persisten参数决定，当该参数配置为ON时，表示统计信息持久化，M=20，N=10；当该参数配置为OFF时，M=8，N=16；

统计信息也可显式通过analyze table tablename来触发更新；

普通索引与唯一索引

查询操作

普通索引：查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录；
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止检索；

普通索引相比唯一索引会多检索一次，相对于CPU性能来说，几乎没有影响；因为InnoDB的数据是按照数据页为单位进行读写的，需要读取数据时，并不是直接从磁盘读取记录，而是先将数据页读取到内存中，再去内存中进行检索；

一个数据页默认为16KB，对于整形字段，一个数据页可以存放近千个key，除非要读取的数据正好在数据页的最后一条记录，需要额外读取一个数据页，这种情况很少，性能消耗基本可以忽略不计；

因此，在查询操作上，普通索引与唯一索引基本没差别；

更新操作

更新操作并不是直接对磁盘中的数据进行更新，而是先将数据页读取进内存，然后在对内存中的数据页进行更新；

普通索引：将数据页从磁盘读入内存，再将内存中数据页更新；
唯一索引：将数据页从磁盘读入内存，判断是否唯一，然后在更新数据页；

由于MySQL的change buffer机制，导致普通索引与唯一索引在更新上有所区别；

change buffer的目的是为了降低IO操作，避免系统负载过高；change buffer将数据写入数据页的过程，叫做merge；

如果待更新的数据页已加载进内存，会直接更新数据页；如果数据页不在内存中，会先将更新操作记入change buffer，当后续查询需要访问该数据页时，在将数据页读入内存时，执行change buffer中与这个数据页相关的更新操作（即merge）；change buffer也有定期merge策略，数据库正常关闭也会出发merge；

对于唯一索引，更新前需要判断数据是否唯一，如果数据页在内存中，则直接判断并更新；如果不存在，则需要从磁盘中读取数据页，判断是否唯一并完成更新；

change buffer使用的是BP的内存，因此无法无限增大；change buffer的大小，可通过innodb_change_buffer_max_size参数来动态设置，表示可使用BP内存的百分比；

结论：

BP数据命中时，唯一索引与普通索引性能基本一致；
BP数据未命中时，唯一索引性能低于普通索引；唯一索引更新不能使用change buffer，每次更新都需要将数据从磁盘读取到内存中；而普通索引会将更新操作存入change buffer，直到触发merge；

change buffer中累计的变更越多，收益越大；

change buffer降低IO操作的代价是更新不及时；

总结

由于唯一索引无法使用change buffer优化机制，因此如果业务可以接受，从性能角度，推荐优先考虑普通索引；

通过数据库实现约束：因业务要求，必须创建唯一索引；碰上大量插入数据慢、内存命中率低时，可能是因为唯一索引导致；
大量更新场景：当使用机械硬盘时，普通索引+change buffer机制的收效非常显著；
读操作频繁场景：使用唯一索引；

如果服务器使用的是SSD，那么change buffer的优化性能就不是特别大了；

索引分析与优化

EXPLAIN

MySQL提供了一个EXPLAIN命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，供开发人员有针对性的优化；EXPLAIN命令的输入结果大致如下：

mysql> explain select a.category_id,b.book_id,b.book_name from md_book_category a  join md_books b on a.book_id=b.book_id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 34
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: book_id
          key: book_id
      key_len: 4
          ref: bookstack.b.book_id
         rows: 2
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

select_type

表示查询的类型，常用属性值如下：
- SIMPLE：表示查询语句不包含子查询或union
- PRIMARY：表示查询是最外层的查询
- UNION：表示查询是UNION的第二个或后续的查询
- DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面的查询结果
- UNION RESULT：UNION的结果
- SUBQUERY：SELECT子查询语句
- DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询结果
type

表示存储引擎查询数据时采用的方式；是比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描；常用属性值如下，从上至下效率一次增强：
- ALL：表示全表扫描，性能最差
- index：表示基于索引的全表扫描，先扫描索引再扫描全表数据
- range：表示使用索引范围查询，使用>、>=、<、<=、in等；
- ref：表示使用非唯一索引进行单值查询
- eq_ref：一般出现在多表join查询，表示前面表的一个记录，都只能匹配后面表的一行结果
- const：表示使用主键或唯一索引做等值查询，常量查询
- NULL：表示不用访问表，速度最快
possible_keys

表示查询时能够使用到的索引；但实际执行时不一定会真的使用，显示的是索引名称
key

表示查询时真正使用到的索引，显示的是索引名称
rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录；原则上rows越少效率越高，可以直观的了解到SQL效率高低
key_len

表示查询使用了索引的字节数量；可以判断是否全部使用了组合索引

key_len的计算规则如下：
- 字符串类
  
  字符串长度与字符集有关：latin1=1，gbk=2，utf8=3，utf8mb4=4
  
  char(n)=n*字符长度
  
  varchar(n)=n*字符长度+2字节
- 数值类型
  
  tinyint：1字节
  
  smallint：2字节
  
  mediumint：3字节
  
  int、float：4字节
  
  bigint、double：8字节
- 时间类型
  
  date：3字节
  
  timestamp：4字节
  
  datetime：8字节
- 字段属性
  
  NULL占用1个字节
Extra

extra表示额外信息，各种操作会在Extra提示相关信息：
- Using where
  
  表示查询需要通过索引会标查询数据
- Using index
  
  表示查询需要通过索引，索引即可满足所需数据
- Using filesort
  
  表示查询出来的结果需要额外排序，数据量小在内存，大则在磁盘
- Using temporary
  
  查询使用到临时表，一般用于去重、分组等；

回表查询

上文有介绍过，InnoDB索引分为聚簇索引和辅助索引；聚簇索引的叶子节点存储行记录，InnoDB有且只有一个；辅助索引的叶子节点存储的是主键值和索引字段，通过索引无法直接定位行记录，通常情况下，需要扫描两边索引树，先通过辅助索引定位主键值，再通过聚簇索引定位行记录，此过程即为回表查询；

回表查询需要扫描两颗索引树，性能相对较低；

覆盖索引

EXPLAIN输出结果Extra字段为Using index时，即触发覆盖索引；即查询语句只需要在一颗索引树上就能够获取SQL所需的所有列数据，则无需回表，速度更快，这就叫做覆盖索引；

实现覆盖索引的方法为：将被查询的字段创建为组合索引；

最左前缀原则

符合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会用到索引，如果从索引第二列开始查找，则索引失效；

LIKE查询

使用like进行模糊查询时，也可使用索引，但只支持%在后；

select * from user where name like 'abc%'; //起作用
select * from user where name like '%abc%'; //不起作用
select * from user where name like '%abc'; //不起作用

NULL查询

索引列技术上能够支持NULL，但一般不建议这样使用，推荐配置默认值；

索引与排序

MySQL查询支持filesort和index两种方式排序；

filesort：先计算出结果，然后在缓存或磁盘上进行排序，效率较低；
- 双路排序：两次磁盘扫描读取，最终得到用户数据；第一次扫描读取排序字段，并进行排序；第二次读取其他字段数据；
- 单路排序：直接从磁盘读取所有列数据，然后在内存中进行排序；如果查询数据超出sort_buffer缓存，则会导致多次磁盘读取操作并创建临时表；解决方案为：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。
index：利用索引自动实现排序，不需要额外进行排序操作，效率较高；

index排序优先触发：

order by子句排序列组合满足索引最左前缀原则；

filesort排序优先触发：

order by子句同时存在asc和desc；
where子句包含范围限制条件；
order by子句排序列不满足最左前缀原则；
where 子句或order by子句排序列涉及到多个索引；（MySQL每次只能使用一个索引）
where子句或order by子句中对索引列使用函数表达式；

查询优化

慢查询定位

开启慢查询日志

通过命令行查看数据库是否开启了慢查询日志和慢查询日志文件的存储路径：

SHOW VARIABLES LIKE 'slow_query_log%'

如查询结果为未开启慢查询日志，则可通过以下命令开启慢查询日志：

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询
记录到日志文件中。
log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log
的值为ON，否则不会奏效。

查看慢查询日志

文本方式查看

直接使用文本编辑器或vi查看日志文件：

time：日志记录的时间

User@Host：执行的用户及主机

Query_time：执行的时间

Lock_time：锁表时间

Rows_sent：发送给请求方的记录数，结果数量

Rows_examined：语句扫描的记录条数

SET timestamp：语句执行的时间点

select…：执行的具体的SQL语句

mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志
内容。
在 MySQL bin目录下执行下面命令可以查看该使用格式。
perl mysqldumpslow.pl --help
运行如下命令查看慢查询日志信息：
perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log
除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、mysqlsla等。

慢查询优化

索引与慢查询

如何判断是否为慢查询

SQL运行时间超过long_query_time即为慢查询，默认值为10s；该参数允许用户自定义；
如何判断是否使用索引

可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。

查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定
的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

提高索引过滤性

索引的过滤性与索引字段、表数据量、表设计结构都有关系，合理设计索引能有效提升查询性能；

慢查询原因总结

全表扫描：explain分析type属性为all
全索引扫描：explain分析type属性为index
索引过滤性差
频繁回表

分页查询优化

一般性分页

一般的分页查询使用简单的limit子句即可实现，语法格式为：

select * from tablename limit [offset,] rows;
--offset：指定第一个返回记录行的偏移量，可省略，默认为0
--rows：指定返回最大记录数

偏移量及返回最大记录数100以内性能基本无差别，超出100则随数值增大耗时增加；

分页优化方案

尽量使用覆盖索引
利用子查询

semon

https://destinys.github.io/2021/08/12/04-database/mysql2-suo-yin/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 semon !

mysql

Mac初始化

2021-08-20 Skill

mac

MySQL之锁介绍

2021-08-12 Database

mysql

MySQL之索引

存储引擎与数据结构

主键索引与二级索引

复合索引

覆盖索引

索引下推

索引前缀

索引选择

索引采样统计信息

普通索引与唯一索引

查询操作

更新操作

总结

索引分析与优化

EXPLAIN

回表查询

覆盖索引

最左前缀原则

LIKE查询

NULL查询

索引与排序

查询优化

慢查询定位

开启慢查询日志

查看慢查询日志

慢查询优化

索引与慢查询

提高索引过滤性

慢查询原因总结

分页查询优化

一般性分页

分页优化方案