MySQL索引及SQL优化

一、索引

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引；所有的索引都会创建B+树。

1.1 几种索引

主键索引

非空唯一索引，一个表只有一个主键索引；在innodb中，主键索引的B+树的叶子节点包含表数据信息。

PRIMARY KEY（key）

唯一索引

不可以出现相同的值，可以有一个NULL值。

UNIQUE（key）

普通索引

允许出现相同的索引内容。

KEY（key[,....]）
-- OR
INDEX(key)

组合索引

对表上的多个列进行索引

INDEX idx (key1,key2[,....])
UNIQUE (key1,key2[,....])
PRIMARY KEY(key1,key2[,....])

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词FULLTEXT；

在短字符串中用LIKE %；在全文索引中用match和against；

在elasticsearch中使用的就是全文索引。

1.2 主键、外键、约束

主键选择

innodb中表是索引组织表，每张表有且仅有一个主键；

  1. 如果显式设置PRIMARY KEY，则改设置的key为该表的主键；
  2. 如果没有显式设计，则从非空唯一索引中选择；
    1. 只有一个非空唯一索引，则选择该索引为主键；
    2. 有多个非空唯一索引，则选择声明的第一个为主键；　　
  3. 没有非空唯一索引，则自动生成一个6字节的_rowid作为主键；

约束（constraint）

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，foreign key， default， not null。

外键约束（事务性）

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只能起到注释作用；二innodb完整支持外键。

外键约束的执行具备事务性。

FOREIGN KEY（myfield） REFERENCES parent_table(parent_field) 
ON DELETE CASCADE ON UPDATE CASCADE

被引用的表为父表，引用的表称为子表。

外键定义时，可以设置父表行为行为ON DELETE和ON UPDATE，行为发生时的操作可选择：

  • CASCADE：子表做同样的行为
  • SET NULL：更新子表相应的外键字段为null，（子表的外键字段不能设置为not null）
  • NO ACTION：如果子表中有匹配的记录，则不允许父表delete/update操作
  • RESTRICT：同 NO ACTION

实际项目中如果需要外键约束，建议用自己写的sql语句替换约束操作，因为在实际操作中可能因为忽视外键约束导致出现问题。

约束和索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构，既包含逻辑的概念也包含物理的存储方式　　

二、索引实现

2.1 索引存储

innodb由端、区、页组成；段分为数据段、索引段、回滚段等；区大小为1MB（一个区由64个连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为4K或者8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请4~5个区

页

页是innodb磁盘管理的最小单位，默认16k，可通过innodb_page_size参数来修改；B+树中的一个节点的大小就是该页的值。

2.2 B+树

全称：多路平衡搜索树（叶子节点都在同一层），矮胖结构减少磁盘访问次数；用来组织磁盘数据，以页为单位。对页的访问是一次磁盘io，缓存中会缓存常访问的页。

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，即叶子节点会存储前后叶子节点的物理地址，方便范围查询。

叶子节点最少要存储两行数据。（所以如果行数据过长，此时会有一些特殊操作）。

每个索引对应着一个B+树。

 

 

B树层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取的内存的数据页越多，io次数越多。

innodb一个节点16kb，假设：key为10byte且指针大小6byte，假设一行记录的大小为1kb；

那么一个非叶子节点可存下16kb/16byte = 1024个（key+point）；每个叶子节点可存储大约16行数据。

结论：

2层B+树叶子节点1024个，可容纳最大记录数为1024* 16 = 16384；

3层B+树叶子节点1024*1024，可容纳最大记录数为：1024*1024*16 = 16777216；

4层B+数叶子节点1024*1024*1024，可容纳最大记录数为：1024*1024*1024*16 = 17179869184；

关于自增id

如果数据行较多，自增超过类型最大值会报错；此时我们可以选用bigint类型（-263，263-1）；

假设采用bigint类型，1秒插入1亿条数据，大概需要5849年才会用完索引。

为什么innnodb推荐使用自增整数作为索引，因为这样子在插入B+树平衡耗时最优。

根据阿里巴巴提出的针对数据库的50条建议，当表中数据超过500万条，建议进行分表。

2.3 索引表

主键索引又称聚集索引，除主键索引外的其他索引称辅助索引。

聚集索引

按照主键构造的B+树；叶子节点中存放数据页；数据集也是索引的一部分

辅助索引

按照非主键的索引构造的B+树，叶子节点中存放数据页。

MyISAM和InnoDB的索引区别

MyISAM和InnoDB在索引树的叶子节点存储数据有所不同。

MyISAM存储有三个文件：

  1. frm文件，存储表的构建信息
  2. myd文件，存储表数据的文件，由堆结构组织（堆表）
  3. myi文件，存储表索引的文件，由B+树组织

MyISAM的聚集索引和辅助索引的叶子节点储存内容是一样的，都存储索引（key）值+对应数据的地址（在myd文件中的地址）。所以查找非索引数据时，MyISAM会进行一个回表查询。从索引树上获取对应数据的地址，然后去堆表中查找。

InnoDB存储有两个文件：

  1. frm文件
  2. ibd文件

InnoDB表只有两个文件，InnoDB的聚集索引在叶子节点会存储索引（key）+具体的行数据。而InnoDB的辅助索引会在叶子节点存储辅助索引（key）+主键（key）。

所以InnoDB只有通过辅助索引查找非索引数据时，才会进行回表查询，从辅助索引B+树获取主键索引，再去主键索引B+树查找具体数据。

二分查找

因为叶子节点存储的数据大小在建表设置索引后就能知道，所以我们在叶子节点中访问数据是可以进行二分查找的。

2.4 innodb体系

根据索引结构，我们可以看到一次数据查找需要进行多次磁盘IO（10ms/次），而实际一次数据库查找并不需要使用这么多时间，那是因为InnoDB有缓存结构。

Buffer Pool

Buffer Poll缓存表和索引数据；采用LRU算法（原理如下图）让Buffer Pool只缓存比较热的数据。

Buffer Pool使用环状结构，使得插入删除头尾数据不用移动数据。

一般的LRU算法是直接将最近访问的数据插入头部，而InnoDB使用的LRU算法是将最新最近访问的数据插到5/8的位置附近，然后当该数据再次被访问时会逐渐向头部移动。 

Change Buffer

Change Buffer缓存非唯一索引的数据变更（DML操作），Change Buffer中的数据会异步meger到磁盘当中。

注意：对于唯一索引的DML操作则不会进入Change Buffer，会直接更新到磁盘中。

Log Buffer

日志缓存

对数据操作，会先写日志，再异步刷盘。因为访问磁盘分随机IO和顺序IO。（https://www.cnblogs.com/unrealCat/p/16052505.html）

2.5 最左匹配

对于组合索引，从左到右依次匹配索引字段。当写的SQL语句使用组合索引没有按照组合索引中的顺序使用索引字段，则优化器会进行顺序的调整，优化索引过程。但是，当优化器遇到> < between like时会停止优化匹配，只会优化这些关键字前的部分。

2.6 覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引 树；较少磁盘 io；

当查询的字段都包含在辅助索引内，此时使用辅助索引不会组成回表查询（即只查询辅助索引的字段和主键索引的字段，因为这些字段都在辅助索引的B+树内有存储）

我们可以使用如下语句查看表的索引

SHOW INDEX FROM `table_name`

三、索引失效

  • select ...where A and B，若A或B中有一个不包含索引，则索引失效；
  • 索引字段参与运算，则索引失效；例如： from_unixtime(idx) = '2021-04-30';当然，如果只是进行简单的运算，优化器会对此进行优化使索引不会失效，例如：id*2 = 30会优化为id = 15
  • 索引字段发生隐式转换，则索引失效；例如："1"隐式转换成1；
  • LIKE模糊查询，通配符%开头，则索引失效；例如：'%Mark'会导致索引失效而’Mark%‘不会；
  • 在索引字段上使用 NOT <> != 索引失效；如果判断 id<>0 则修改为 id > 0 or id < 0;
  • 组合索引中，没有使用第一列索引，索引失效
  • in+or会导致索引失效；单独的in不会导致索引失效；not in会导致索引失效

四、索引原则

  • 查询频次较高且数据量的大的表建立索引，索引选择使用频次较高、过滤效果好的列或者组合；
  • 对于短索引；B+树节点包含的信息越多，能减少磁盘IO操作；使用合适的数据类型，比如：smallint，tinyint；
  • 对于很长的动态字符串，考虑使用前缀索引；
#有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的
#部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索
#引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越
#高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。对于 BLOB , TEXT ,
#VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用
#该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。


select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6,
from user;
alter table user add key(name(4));
-- 注意：前缀索引不能做 order by 和 group by
  • 对于组合索引，考虑最左侧匹配原则和覆盖索引
  • 尽量选择区分度高的列作为索引；该列的值相同的越少越好
select count(distinct idx) / count(*) from table_name;
-- 或者
show index from student;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
-- Cardinality 这个值代表 select count(distinct idx) / count(*) from
table_name;
-- 该值决定了优化器的执行计划的选择；
-- 立马更新 Cardinality 值
analyze table table_name;
-- 在非高峰时间段，对数据库中几张核心表做 analyze table 操作，这能使优化器和索引更
好的为你工作；
  • 在修改表结构时，尽量扩展索引而不是增加索引，在现有索引的基础上，添加复合索引；建议一个表最多6个索引
  • 尽量不用select *，尽量列出只需要的列字段，方便使用覆盖索引
  • 对于索引列，列数据尽量设置为非空
  • 可选：开启自适应hash索引或者调整change buffer
select @@innodb_adaptive_hash_index;
set global innodb_adaptive_hash_index=1; -- 默认是开启的

select @@innodb_change_buffer_max_size;
-- 默认值为25 表示最多使用1/4的缓冲池内存空间 最大值为50
set global innodb_change_buffer_max_size=30；

五、优化器成本分析

mysql 优化器主要针对 IO 和 CPU 会计算语句的成本；可能不会按照分析的原理来执行语句；

成本分析步骤

  • 找出所有可能需要使用到的索引
  • 计算全表扫描的代价
  • 计算不同索引执行查询的代价
  • 对比找出代价最小的执行方案

六、SQL优化

参考文档：https://dev.mysql.com/doc/refman/5.7/en/optimization.html

七、EXPLAIN

用来查看SQL语句的具体执行过程。

原理：模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

7.1 执行计划的字段

id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或者操作表的顺序；

id 号分为三种情况：

  1.  id 相同，那么执行顺序从上到下；
  2.  id 不同，id 越大越先执行；
  3.   id 有相同的也有不同的，id 相同的按 1 执行，id 不同的按 2 执行；

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集;

  1. 具体表名或者表的别名，从具体的物理表中获取数据；
  2. 表明为derivedN的形式，表示 id 为 N 的查询产生的衍生表；
  3.  当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id;

type

type 显示访问类型；采用怎么样的方式来访问数据；效率从好到坏依次为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys

 查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳；

key

实际使用的索引，如果为 NULL ，则没有使用索引

key_len

表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数；

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

extra

额外信息；

7.2 优化器选择过程

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划；

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

八、慢日志查询

参考文件：MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log

开启

查看

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

设置

SET GLOBAL slow_query_log = ON; -- on 开启 off 关闭
SET GLOBAL long_query_time = 4; -- 单位秒；默认10s；此时设置为4s

或者修改配置

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

mysqldumpslow

查找最近10条慢查询日志

mysqldumpslow -s t -t 10 -g 'select' D:/mysql/mysql57-slow.log

SHOW PROFILE

# 查看是否开启
SELECT @@profiling;
# 设置开启
SET profiling = 1;
# 查看所有 profiles
show profiles;
# 查看query id 为 10 那条查询
show profile for query 10;
# 查看最后一条查询
show profile;
# 最后关闭
SET profiling = 0;

SHOW PROCESSLIST

查看连接线程；可以查看此时线上运行的 sql 语句；

如果要查看完整的SQL语句：SHOW FULL PROCESSLIST; 然后优化该语句； 

九、找到有问题的索引

  • 使用htop/top查看cpu/磁盘的情况
  • 查看sql-slow-log，看哪条语句执行超过10s
  • 在线上环境，有用户在使用的时候发现使用数据库很慢，可以使用show processlist和show full processlist
  • 如果从原理分析分析不出问题，可以开启优化器分析优化器的选择过程

 

posted @ 2022-03-25 00:28  幻cat  阅读(131)  评论(0)  编辑  收藏  举报