DBA专题
DBA授课
DBA公开课
DBA训练营三天
01.Mysql基础入门-数据库简介
02.Mysql基础入门-部署与管理体系
03.MySQL主流版本版本特性与部署安装
04.Mysql-基础入门-用户与权限
05 MySQL-SQL基础2
06 SQL高级开发-函数
07 MySQL-SQL高级处理
08 SQL练习 作业
09 数据库高级开发2
10 Mysql基础入门-索引
11 Mysql之InnoDB引擎架构与体系结构
12 Mysql之InnoDB存储引擎
13 Mysql之日志管理
14 Mysql备份,恢复与迁移
15 主从复制的作用及重要性
16 Mysql Binlog Event详解
17 Mysql 主从复制
18 MySQL主从复制延时优化及监控故障处理
19 MySQL主从复制企业级场景解析
20 MySql主从复制搭建
21 MySQL高可用-技术方案选型
22 MySQL高可用-MHA(原理篇)
23 MySQL MHA实验
24 MySQL MGR
25 部署MySQL InnoDB Cluster
26 MySQL Cluster(MGR)
27 MySQL ProxySQL中间件
相信可能就有无限可能
-
+
首页
10 Mysql基础入门-索引
# 1.索引是什么 ## 1.0.数据结构与树 - 基本概念 ```sql #数据结构: 互相存储一到多种关系的数据集合和关系组成 #分类: 数组，栈，链表，队列，树，图，堆，散列表 #树-->树状图 由n（n>=1）个有限结点组成一个具有层次关系的集合。一种非线性存储结构，存储的是具有“一对多”关系的数据元素的集合。 #术语 节点深度：对任意节点x，x节点的深度表示为根节点到x节点的路径长度。所以根节点深度为0，第二层节点深度为1，以此类推 节点高度：对任意节点x，叶子节点到x节点的路径长度就是节点x的高度 树的深度：一棵树中节点的最大深度就是树的深度，也称为高度 父节点：若一个节点含有子节点，则这个节点称为其子节点的父节点 子节点：一个节点含有的子树的根节点称为该节点的子节点 节点的层次：从根节点开始，根节点为第一层，根的子节点为第二层，以此类推 兄弟节点：拥有共同父节点的节点互称为兄弟节点 度：节点的子树数目就是节点的度 叶子节点：度为零的节点就是叶子节点 祖先：对任意节点x，从根节点到节点x的所有节点都是x的祖先（节点x也是自己的祖先） 后代：对任意节点x，从节点x到叶子节点的所有节点都是x的后代（节点x也是自己的后代） 森林：m颗互不相交的树构成的集合就是森林 ``` - 树的种类 ```sql 1.无序树 - 树的任意节点的子节点没有顺序关系。 2.有序树 - 树的任意节点的子节点有顺序关系。 3.二叉树 - 树的任意节点至多包含两棵子树。 - 二叉树遍历：从二叉树的根结点出发，按照某种次序依次访问二叉树中的所有结点，使得每个结点被访问一次，且仅被访问一次。 4.AVL树:平衡二叉查找树,增加和删除可能需要通过一次或多次树旋转来重新平衡这个树。 5.红黑树： - 每个节点都带有颜色属性的二叉查找树，颜色或红色或黑色。 性质1. 节点是红色或黑色。 性质2. 根节点是黑色。 性质3. 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点) 性质4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。 使用场景： 红黑树多用于搜索,插入,删除操作多的情况下 红黑树应用比较广泛： 1. 广泛用在C++的STL中。map和set都是用红黑树实现的。 2. 著名的linux进程调度Completely Fair Scheduler,用红黑树管理进程控制块。 3.epoll在内核中的实现，用红黑树管理事件块 4.nginx中，用红黑树管理timer等 6.B树 一棵m阶B树(balanced tree of order m)是一棵平衡的m路搜索树。它或者是空树，或者是满足下列性质的树： 1、根结点至少有两个子女； 2、每个非根节点所包含的关键字个数 j 满足：m/2 - 1 <= j <= m - 1； 3、除根结点以外的所有结点（不包括叶子结点）的度数正好是关键字总数加1，故内部子树个数 k 满足：m/2 <= k <= m ； 4、所有的叶子结点都位于同一层。 7.B+树 ##定义 B+树是B树的一种变形形式，B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。一棵m阶的B+树定义如下: (1)每个结点至多有m个子女； (2)除根结点外，每个结点至少有[m/2]个子女，根结点至少有两个子女； (3)有k个子女的结点必有k个关键字。 ##重要特性 链表指针(重要特性):B+树的查找与B树不同，当索引部分某个结点的关键字与所查的关键字相等时，并不停止查找，应继续沿着这个关键字左边的指针向下，一直查到该关键字所在的叶子结点为止。 ##特点 更适合文件索引系统 ##原因 增删文件（节点）时，效率更高，因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率 ##使用场景： 连续的数据能够进行较快的定位和访问，减少查找时间，减少IO操作。他广泛用于文件系统及数据库中，如： Windows：HPFS 文件系统 Mac：HFS，HFS+ 文件系统 Linux：ResiserFS，XFS，Ext3FS，JFS 文件系统 数据库：ORACLE，MYSQL，SQLSERVER 等中 B树：有序数组+平衡多叉树 B+树：有序数组链表+平衡多叉树 ##B*树 B*树是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针； ``` ## 1.1.索引简介 ### 1.1.0.基础概念补充 1.磁盘相关 ```sql 系统从磁盘读取数据到内存以磁盘块(block)为基本单位,一个磁盘块中数据会一次性读取出来(不是需要什么取什么) #什么是磁盘块(block) 1.操作系统与磁盘交流最小单位(磁盘块),软件概念。扇区是对硬盘而言，硬件概念。 2.磁盘读写最小单位扇区(物理区域,512byte或4K)-->磁盘块(block)=扇区x2^n(是连接操作系统与扇区的桥梁).软件概念 3.如何查看磁盘块 shell>df >tune2fs -l /dev/vda1|grep Block Block count: 11796219 Block size: 4096 #一般为4096字节.8个扇区组成 Blocks per group: 32768 InnoDB存储引擎有页(Page)的概念,页是磁盘管理的最小单位.InnoDB默认16KB,参数innodb_page_size控制 mysql> show variables like 'innodb_page_size'; ``` **2.InnoDB表结构** (1)InnoDB逻辑存储结构 MySQL表中的所有数据被存储在一个空间内，称之为**表空间（.ibd文件）**，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),逻辑结构如下图：  - 段(segment):表空间由不同的段组成 1.数据段:B+树 索引及数据 叶子节点 2.索引段:非叶子节点 3.回滚段:存储undo日志,事务失败回滚和未提交之前的数据.(下一节) - 区(extent) 1.连续的页组成(默认一页16K),每个区固定大小1MB。默认不压缩,一个区64个连续的页 - 页(page) 1.InnoDB存储引擎的最小管理单位,默认16KB 2.页类型:数据页,undo页,系统页... - 行(row) 1.对应表中行记录 2.每页存储最多行记录规定 16KB/2-200 = 7992行 (每个记录最少2字节,每个页预留200字节，硬性规定) 3.每个页最少存储2行,虚拟记录,限定记录边界,最大虚拟记录和最小虚拟记录 ### 1.1.1.索引用处和索引种类? 索引: - 帮助MySQL高效获取数据的数据结构 - 存储在文件系统 - 文件存储格式与存储引擎有关 - 索引文件结构 - hash:无序,适合等值查询.范围用不了.（Mysql-AHI,Memory存储引擎） - 二叉树与红黑树:树深度过深造成IO次数变多,读取效率不行  - - B树 ```sql #说明 (1)不是二叉搜索,而是m叉搜索 (2)叶子节点,根节点,非叶子节点:都存储数据 (3)每个节点占用一个磁盘块,一个节点两个关键字和三个指针,指针指向磁盘块地址(一般为4K)。 p1指针小于16,p2 16-34，p3 大于34 (4)完美利用局部性原理,适合做索引. #知识补充-局部性原理 逻辑: (1)内存IO(2-12GB/S,延时30-100ns)>磁盘IO(50MB->2GB/S,延时10us-1ms) (2)磁盘预读: 数据库磁盘读取不是按需,而是按页,一次读取一页数据,未来数据在此页中，避免未来磁盘IO。(通过一页是4K,MySQL中默认一页是16K) (3)局部性原理:软件设计尽量遵循"数据读取集中"与"使用相邻数据"，磁盘预读能充分利用IO #查找关键字过程 1.根节点找到磁盘块1,读入内存,[IO操作一次] 2.比较关键字29,找到磁盘块指针p2 3.根据p2指针-->磁盘块3,读入内存 [IO第二次] 4.找到p2指针 5.比较P2指针-->磁盘块8--》读入内存 [IO第三次] 6.8找到关键字29 #缺点 1.每个节点都有key和data,页存储空间有限,data大-->每个节点key数量小 2.查询不稳定,可能一次,没可能多次,数据量极大,查询深度深，IO次数多，影响性能 ```  ### 1.1.2.InnoDB索引数据结构 B+树 - B+树 ```sql 在B树上做了一些优化,如下: 1.非叶子节点存储key，叶子节点存储key和数据 2.叶子节点两两链表连接(符合磁盘预读特性),顺序查询性能更高.不需要中序遍历. 3.所有数据存储在叶子节点中 这样设计带来比B树的优势特性 1.范围查找：定位min和max后，中间叶子节点，就是结果集，不用在中序回溯 2.叶子节点记录行数据,适合大数据量磁盘存储.非叶子节点存储记录PK,用于查询加速,适合内存存储。 3.非叶子节点仅存储key，相同内存,B+树存储更多索引,降低树的高度 假设每个磁盘块存储4个键值与指针信息,则B+Tree结构下图所示 ```   ```sql #InnoDB中的B+树索引 1.主键索引记录的是整行数据. 2.3-4次IO基本满足(一个磁盘块1000key，第一层1000，第二层1001,第三层1002,四层B+树 几百亿数据)。InnoDB设计根节点常住内存,也就是查找某一行键值记录1-3次磁盘IO. 3.没有主键-->唯一键(不推荐)-->6位row_id作为主键(不推荐) 4.MySQL读取B+数索引数据:查找到数据行所在的页,通过数据库把也读入到内存,在内存中查找数据. #补充知识-为什么MySQL一般一页是16K 以聚簇索引为例: - 非叶子节点:主键+指针(假设主键ID为bigint，长度为8Byte,指针在Innodb源码为6B，一共14B),一页存储16K/14=1170（主键+指针） - 叶子节点:假设一行数据大小1K,一页存16K数据(一个叶子节点). 高度为2的B+树存储数据:1170*16=18720 高度为3的B+树存储数据:1170**1170*16=21902400(千万数据级别) 高度为4的B+树存储数据:1170**1170*1170*16=25625808000(百亿数据级别) 业务一行数据500B,存储的数据量级大概3层B+树大概在4KW左右,高于这个值也就是3次磁盘IO+一次内存节点（IO）(如果是二级索引就是6次IO),称为大表,4层的B+树。 ``` ### 1.1.3.InnoDB中索引分类 - 从存储类型分类 ```sql 聚簇索引(主键索引): 1.InooDB存储引擎表就是索引组织表:表中数据按照PK逻辑上顺序存放,物理并不连续. （叶子节点双向链表连接,页中记录也是双向链表维护）(物理存储是文件系统决定,大文件采用分层路由,小文件直接存放) 2.按照表的PK构建一颗B+树,非叶子节点存放指针和PK，叶子节点存放整条row记录。 3.聚簇索引定义了数据逻辑顺序,对范围查询优化特别快,那些数据页需要扫描. 辅助索引(二级索引) 1.手动创建,作为辅助查询条件(业务常用) 2.非主键索引存放的是键值(column)和主键ID(data)，通过主键找到对应记录 3.回表:通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。(多增加查找完整一次树的IO,比如3次辅助索引树+3次聚簇索引树) ``` - 从功能分类 ```sql 1.主键索引:Primary key,唯一,非空非null，建议数字自增(有的业务会用uuid)。 (1)如果表设置主键(ID列),根据ID列生产聚簇索引 (2)如果没有设置主键,自动选择第一个唯一键列作为聚簇索引 (3)自动生成6字节的row_id 作为聚簇索引 2.唯一索引:索引列只能出现一次,value唯一. 3.普通索引:基本索引类型,没有唯一性限制。 4.全文索引:索引类型FULLTEXT(基本索引)-ES或者MongoDB 5.组合索引:多个索引列组成一个索引 6.覆盖索引:又称三星索引,select column与索引列完全对应 ``` - 优点 ```sql 1.减少了服务器需要扫描的数据量 2.帮助服务器避免排序和临时表 3.随机IO变成顺序IO ``` - 缺点 ```sql - 提高查询速度，降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。索引文件也要更新 - 建立索引占用磁盘空间(大表多个组合索引),生产不超过8个索引. - 数据列过滤因子过小(比如 1,0),索引没有什么效果 - 小表全表扫描更高效 ``` - 例子:一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针） ```sql 叶子节点中，为了突出记录，把(id, name, age)实际上是连在一起的，它们是构成一条记录的整体 ```  ```sql - 而一个非聚簇索引（以age为索引）的结构是： - 它的叶子节点中，不包含整个记录的完整信息，除了age字段本身以外，只包含当前记录的主键id。如果想要获取整行记录数据还需要再通过id号到聚簇索引中回表查询。 - 覆盖索引解决问题 select name,age from userinfo where age = 10 ; - 建立索引 (name,age) # id name age index age primary key id select name,age from userinfo where age = 10; ```  ## 1.2.索引使用 - 准备测试环境 ```sql #建表 #建表 CREATE TABLE `app_user` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '' COMMENT '昵称', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `phone` bigint DEFAULT NULL COMMENT '手机号', `gender` tinyint DEFAULT NULL COMMENT '性别 0-男, 1-女', `password` varchar(100) NOT NULL COMMENT '密码', `age` tinyint NOT NULL COMMENT '年龄', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `id_app_user_name` (`name`), KEY `idx_name_phone_password` (`name`,`phone`,`password`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb3 COMMENT='app用户表'; #插入百万数据 DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i < num DO INSERT INTO `school`.`app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES (CONCAT('用户',i), '123456@qq.com', CONCAT('18', FLOOR(RAND()*((999999999-100000000)+100000000))), FLOOR(RAND()*2), UUID(), FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END $$ #调用函数 select mock_data(); #校验数据 select count(*) from app_user; ``` - mysqlslap自带压测 ```bash mysqlslap --defaults-file=/chj/class/data/mysql3306/etc/my3306.cnf --concurrency=100 --iterations=1 --create-schema='school' --query="SELECT * FROM app_user WHERE name='用户9999';" engine=innodb --number-of-queries=2000 -uqianlong -ptest123 -h127.0.0.1 -verbose --concurrency=100 ： 模拟同时100会话连接 --create-schema='test' ： 操作的库是谁 --query="select * from test.t100w where k2='780P'" ：做了什么操作 --number-of-queries=2000 ： 一共做了多少次查询 ``` - 不加索引测试 ```sql SELECT * FROM `app_user` WHERE `name`='用户9999'; EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' ``` - 添加普通索引测试 ```sql #添加一个普通二级索引 CREATE INDEX id_app_user_name ON app_user(`name`); #再次压测 mysqlslap --defaults-file=/chj/class/data/mysql3306/etc/my3306.cnf --concurrency=100 --iterations=1 --create-schema='school' --query="SELECT * FROM app_user WHERE name='用户9999';" engine=innodb --number-of-queries=2000 -uqianlong -ptest123 -h127.0.0.1 -verbose EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999' ``` - 添加联合索引测试 ```sql #复合索引,作用:解决多个where条件走索引 alter table app_user add index idx_name_phone_password(name,phone,password); #应用场景(也是覆盖) select name,phone,password from app_user where name='用户0' and phone=18399466985 and password='f93758ef-0d93-11ec-99bf-fa2020131854'; ``` - 前缀索引 ```sql #较少索引树高度,取前5位数字作为索引 alter table app_user add index idx_phone(phone(5)); ``` - 删除索引 ```sql alter table app_user city drop index idx_phone; ``` ## 1.3.执行计划 ### 1.3.1.介绍与使用方法 - 可以得到什么 ```sql 查询执行计划(QEP) - 表的加载顺序 - sql 的查询类型 - 可能用到哪些索引，实际上用到哪些索引 - 读取的行数 - 表之间的引用 - 每张表有多少行被优化器查询 ``` - 使用方法 ```sql explain SQL (SELECT DELETE UPDATE) explain select name,phone,password from app_user where name>'用 户0' and phone>1 limit 500,10; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: app_user partitions: NULL type: range possible_keys: id_app_user_name,idx_name_phone_password key: idx_name_phone_password key_len: 153 ref: NULL rows: 496529 filtered: 33.33 Extra: Using where; Using index #加强版 explain format=json select name,phone,password from app_user where name>'用 户0' and phone>1 limit 500,10; ``` EXPLAIN列的解释： 1.ID ```sql 含义:select查询的序列号,查询中执行select子句或操作表的顺序 - id相同:执行顺序从上至下 , - id不同:有子查询或者派生表(id大先执行) join 举例: CREATE TABLE `discuss` ( `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论唯一id', `discuss_body` varchar(255) NOT NULL COMMENT '评论内容', `discuss_time` datetime NOT NULL COMMENT '评论时间', `user_id` int NOT NULL COMMENT '用户id', `blog_id` int NOT NULL COMMENT '博文id', PRIMARY KEY (`discuss_id`) ) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb3 explain select discuss_body from discuss where blog_id = ( select blog_id from blog where user_id = ( select user_id from user where user_name = 'admin')); ``` 2.select_type: ```sql select_type:查询类型，比如普通查询,联合查询,子查询 SIMPLE:简单的select查询，不包含子查询或者UNION PRIMARY:子查询的最外层查询 SUBQUERY:在SELECT或WHERE列表中包含了子查询 DERIVED: 衍生表把结果放在临时表中 UNION:UNION后的第二个SQL UNION RESULT:从UNION表获取结果的SELECT ``` 3.table ```sql table:当前执行的表 ``` 4.type ```sql type:查询类型,性能排序 system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL system:表只有一行记录 const:索引一次找到,主键和unique 索引 eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。 ref:辅助索引等值访问 range:索引范围扫描 index:全索引扫描 all: full table scan ``` 5.possible_keys ```sql possible_keys 和 key:可能使用索引和真正使用的索引 ``` 6.key_len ```sql key_len：索引使用字节数 ```  7.ref ```sql ref: 显示索引的哪一列被使用了 select * from t1,t2 where t1.col1 = t2.col1(t1.col1为索引列) ; ``` 8.其余 ```sql 8.rows:大致估计读取行数 9.filtered:满足条件记录/扫描记录的百分比 10.extra：MYSQL如何解析查询的额外信息 ``` ### 1.3.2.type信息详解 - system ```sql mysql> explain select * from mysql.proxies_priv; 表示:系统表，少量数据，往往不需要进行磁盘IO ``` - const：单表操作的时候，查询使用了主键或者唯一索引 ```sql mysql> explain select * from app_user where id=1; 表示:通过索引一次就匹配到,用于primary 或者unique索引,常量连接。 user_id是 primary ``` - eq_ref ```sql 多表关联查询：，主键和唯一索引作为关联条件。如下图的sql，对于user表（外循环）的每一行，user_role表（内循环）只有一行满足join条件，只要查找到这行记录，就会跳出内循环，继续外循环的下一轮查询 一句话:对于前表的每一行(row)，后表只有一行被扫描 (1)join查询 (2)命中主键或者命中非空唯一索引 (3)等值连接 ### create table user_role like user; explain select u.user_name from user u,user_role ur where u.user_id = ur.user_id; ``` - ref ```sql 含义:使用索引但值不唯一(没用主键和唯一索引), 不需要全表扫描.eq_ref降级为了ref，此时对于前表的每一行(row)，后表可能有多于一行的数据被扫描。 条件:ref扫描，可能出现在join或单表普通索引里，每一次匹配可能有多行数据返回，比eq_ref要慢。 ### mysql> explain select user_id from user where user_name='admin'; #ref_or_null：类似 ref，会额外搜索包含NULL值的行。 ``` - index_merge ```sql (1)索引合并,对多个索引分别进行条件扫描， (2)结果进行合并 #mysql5.0之后出现的功能 mysql> explain select id,name from app_user where id=1 or name='用户0'; ``` - range ```sql 含义:基于Index数据上的范围扫描，扫描特定范围内的值,between、and、'>'、'<'、in和or都是范围索引扫描。 explain select * from user where user_id>0; ``` - index ```sql 含义:需要扫描索引上的全部数据 mysql> explain select user_name from user ; ``` - all ```sql full table scan 生产禁止 #案例 业务说小表5W(config),500条以下生产可以 all select a from config_table where a in ('xx','xx','xx'); 业务上线--->注册中心-->1000多个服务-->mysql实例重启(buffer_pool清空) -->线上服务报失联-->业务找到DBA-->连接数打满-->全都是select语句-->从库加了idx_a索引-->(MHA)主从切换-->故障解决 ``` 总结 ```sql （1）explain结果中的type字段，表示（广义）连接类型，它描述了找到所需数据使用的扫描方式； （2）常见的扫描类型有： system>const>eq_ref>ref>range>index>ALL 扫描速度由快到慢； （3）各类扫描类型的要点是： system最快:不进行磁盘IO const：PK或者unique上的等值查询 eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中 ref：非唯一索引，等值匹配，可能有多行命中 range：索引上的范围扫描 index：索引上的全集扫描 ALL最慢：全表扫描(full table scan) （4）建立正确的索引(index)，非常重要，解决80%性能故障； （5）使用explain了解并优化执行计划，非常重要； ``` ### 1.3.3.extra 额外的Mysql查询优化器执行信息 ```sql #建立测试表 CREATE TABLE `t_order` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int DEFAULT NULL, `order_id` int DEFAULT NULL, `order_status` tinyint DEFAULT NULL, `create_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`) ) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8; CREATE TABLE `t_orderdetail` ( `id` int NOT NULL AUTO_INCREMENT, `order_id` int DEFAULT NULL, `product_name` varchar(100) DEFAULT NULL, `cnt` int DEFAULT NULL, `create_date` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_orderid_productname` (`order_id`,`product_name`) ) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8; ``` ```sql #1.useing where 查询索引列未覆盖 mysql>explain select order_id,order_status from t_order where order_id=1\G mysql> explain select * from app_user where phone>123456; mysql>explain select * from app_user where gender='男'; #2.useing index 覆盖索引 mysql> explain select user_id,order_id from t_order where user_id=1\G #3.Using where&Using index 索引覆盖,但是无法通过索引找到数据,通过索引扫描 ##(`user_id`,`order_id`,`create_date`) 索引不符合最左匹配原则 mysql> explain select user_id,order_id,create_date from t_order where order_id=1\G ##where 条件索引前导列一个范围 mysql> explain select user_id,order_id,create_date from t_order where user_id>1\G #4.NULL：select 列未覆盖,用到索引，需要回表 mysql> explain select user_id,order_id,order_status from t_order where user_id=1\G #5.using index condition 索引下推（index condition pushdown，ICP），先使用where条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。 explain select user_id, order_id, order_status from t_order where user_id > 1 and user_id < 5\G; set optimizer_switch='index_condition_pushdown=off'; set optimizer_switch='index_condition_pushdown=on'; ``` - 不使用索引下推 `set optimizer_switch='index_condition_pushdown=off'`  - 使用索引下推  总结 ```sql using index ：使用覆盖索引的时候就会出现 using where：在查找使用索引的情况下，需要回表去查询所需的数据 using index condition：查找使用了索引，但是需要回表查询数据 using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 using index condition:索引下推,对索引进行where条件过滤,找到符合索引条件的行. ``` ```sql #6.using temporary 使用了临时表保存中间结果，常见于 order by column和 group by 中。 #7.usering filesort 文件排序。表示无法利用索引完成排序操作，以下情况会导致filesort： - order by 的字段不是索引字段 - select 查询字段不全是索引字段 - select 查询字段都是索引字段，但是 order by 字段和索引字段的顺序不一致 mysql> explain select * from t_order order by order_id; #8.using join buffer Block Nested Loop，需要进行嵌套循环计算。 两表join，关联字段未建立索引. #join buffer介绍 暴力表连接时候的重要优化手段.a*b - 不使用: 笛卡尔积,a的每个值,b都要一次全表扫描 - 使用:a的值加载进 joinbuffer,1*b 次 ``` ### 1.3.2.联合索引 1.条件 ```sql 联合索引应用要满足最左原则 （1）建立联合索引时，过滤因子高的列作为最左列。 (2)使用联合索引时，查询条件中，必须包含最左列，才有可能应用到联合索引。 （3）`idx_name_phone_password` (`name`,`phone`,`password`) 相当于3个索引 (name),(name,phone),(name,phone,password) 走索引: select * from app_user where name=? select * from app_user where name=? and phone=? select * from app_user where name=? and phone=? and password=? 不走索引: select * from app_user where phone=? select * from app_user where password=? select * from app_user where phone=? and password=? 联合索引最佳使用场景 explain select name,phone,password from app_user where name='' and phone='' and password > ``` 2.覆盖索引场景应用 ```sql 1.全表count查询优化 explain select count(name) from app_user; 2.场景2：列查询回表优化 explain select name,phone,password from app_user where name='用户0' and phone=2147483647 and password='f93758ef-0d93-11ec-99bf-fa2020131854'; 3.分页查询 explain select name,phone,password from app_user where name='用户0' and phone>1 order by name limit 500,100; explain select name,phone,password from app_user where name>'用户0' and phone>1 limit 500,10; ``` ## 1.4.explain应用场景 - 数据库突然hang住,连接数打满 ```sql #分析正在执行的大SQL 1.select COMMAND,SUBSTRING_INDEX(host,':',1) as ip , count(*),INFO from information_schema.processlist group by ip; 2.explain SQL 3.看索引,看代价,看量级-->联系业务进行处理 explain format=json select user_id, order_id, order_status from t_order where user_id > 1 and user_id < 5\G ``` - 日常分析 ```sql 1.分析slowlog 2.找出慢SQL 3.分析执行计划explain 4.优化 ``` ## 1.5.Mysql8.0 索引新特性 ### 1.5.1.不可见索引（INVISIBLE INDEX） ```sql 介绍: 1.INVISIBLE INDEX,不可见索引或者叫隐藏索引. 2.不可见是指对优化器不可见,查询的时候优化器不会作为备选.其实InnoDB仍然维护 3.操作更改的是metadata,开销非常小. 使用场景: 1.有索引idx1,idx2,idx3.通过数据字典检索，通过数据字典检索idx3没有用过,是否判断索引直接删除呢? 删除后有业务使用怎么办,这个时候用开销较小的隐藏索引 2.业务一个月用一次的SQL,使用隐藏索引 3.测试 mysql> alter table app_user add index idx_phone(phone); mysql> alter table app_user alter index idx_phone invisible; mysql> alter table app_user alter index idx_name_phone_password invisible; mysql> explain select count(*) from app_user where phone=123; #测试报错 mysql> explain select count(*) from app_user force index(idx_phone) where phone=123; #开启优化器使用隐藏索引 mysql> set @@optimizer_switch = 'use_invisible_indexes=on'; #再次执行可以使用索引 mysql> explain select count(*) from app_user force index(idx_phone) where phone=123; #不可见索引变回可见索引 ALTER TABLE app_user ALTER INDEX idx_name_phone_password VISIBLE; ``` ### 1.5.2.倒序索引 ```sql #MySQL5.7之前对索引建立只允许asc正向存储,所有索引都是按升序创建的。当语法本身被解析时，元数据不会被保留,比如以下查询 mysql> explain select name,phone from app_user order by name asc,phone asc limit 10 ; mysql> explain select name,phone from app_user order by name asc,phone desc limit 10 ; #Using index; Using filesort 出现filesort要用外部文件排序 ##排序过程 #Mysql8.0 ##建立倒序索引 alter table app_user add index idx_name_phone(name asc,phone desc); ##再次执行explain语句 mysql> explain select name,phone from app_user order by name asc,phone desc limit 10 ; Backward index scan; #出现反向扫描，与索引的顺序相反，因此需要反向扫描 ``` # 2.索引应用规范与优化 ## [2.1.](http://2.1.ss)索引建立原则 原则: ```sql (1) 必须要有主键,最好数字自增列。 (2) where条件,order by,group by,join ,distinct 的条件加索引 (3) 联合索引最左原则。 (4) 列值长度较长的索引列,建议使用前缀索引. (5) 不常用索引进行清理 (6) 业务流量高,索引添加和删除，要在低峰期使用,pt-osc(inception)或gh-osc(go-inception) (7) 在执行ALTER操作之前,确定实例没有大事务未提交(防止一系列的元数据锁) (8) 减少索引树的高度:尽量用varchar变成节省空间和每列行大小 (9) 频繁进行数据操作的表，不要建立太多的索引； (10)删除无用的索引，避免对执行计划造成负面影响 #不推荐使用索引的情况 (1)数据唯一性差(比如性别),尽量不要用索引 (2)频繁更新的字段不建议用索引(每次update索引的值也要update),会造成很多碎片,同时增加实例负担，增加了随机IO次数 ``` 问题?为什么PK推荐数字自增 - 使用自增id的内部结构 ```sql 自增主键的值是顺序的.页面达到最大填充因子(默认页的15/16).留出1/16的空间留作以后的修改 (1)下一条记录写到新的value，主键页按照顺序方式填满记录,提升页的最大填充率,不会有页的浪费 (2)新插入的行一定是原有最大数据的下一行,mysql定位和寻址很快,不会为计算新行的位置做出额外的消耗 (3)减少页分裂和碎片的产生 ``` - 使用uuid的聚簇索引内部结构 ```sql uuid的自增id无规律,innodb无法做到顺序插入,需要寻找合适位置分配新的空间,数据无顺序导致数据分布散乱,导致以下问题: (1)数据已经从内存落地到磁盘或从缓冲中移除:innodb插入前要从磁盘读取目标页到内存中,增加随机IO (2)写入乱序,innodb的索引树频繁做页分裂操作，为新行分配空间,页分裂导致移动大量数据,一次插入至少修改三个页以上 (3)频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片 (4)有机会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充,带来时间消耗。 ```  ## 2.2.不走索引的情况 ```sql 1.条件中有or且有谓词没有索引,需要每个列都加索引 explain select name,gender from app_user where name='用户37374' or age = 2; 2.like查询 %开头 explain select name,gender from app_user where name like '%sd'; 3.多列索引,不是使用的第一部分,不符合最左匹配原则 explain select name,gender from app_user where phone=2147483647; 4.隐式转换(一定要提醒业务开发),比如type是字符串但是没用引号 explain select name,gender from app_user where name=37374; 5.where子句有数学运算和函数运算 explain select name,gender from app_user where name+1='用户37374' ; explain select name,gender from app_user where length(name)=95 ; 6.mysql判断全表扫描比索引快,不使用索引（比如数据量级极少的表） 7.<> ，not in 不走索引（辅助索引） ``` ```sql | app_user | CREATE TABLE `app_user` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT '' COMMENT '昵称', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `phone` int DEFAULT NULL COMMENT '手机号', `gender` tinyint DEFAULT NULL COMMENT '性别 0-男, 1-女', `password` varchar(100) NOT NULL COMMENT '密码', `age` tinyint NOT NULL COMMENT '年龄', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `id_app_user_name` (`name`), KEY `idx_name_phone_password` (`name`,`phone`,`password`) ``` 如何查看未使用的索引 ```sql mysql> SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA not in ('mysql','performance_schema') ORDER BY OBJECT_SCHEMA,OBJECT_NAME; ``` ## 2.3.索引优化器算法 > https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html 万事找官网 > ### 2.3.1.AHI-自适应hash索引 ```sql AHI:将查询频繁的条件和索引树结果做一个hash映射,无需搜索B+tree,直接通过hash定位查询数据。 1.DBA不能干预,innodb_adaptive_hash_index参数控制是否开启 2.等值查询非常快(大并发量级大约30-40%),范围查找没有优化. 3.副作用(5.7.34之前有):MySQL8.0.23版本已经修复 删除大表,Buffer pool比较大的时候(超过32G),会有短暂hang住的情况,造成业务抖动 原因:删除表会把该表在BP内存都淘汰掉,释放空间(数据页,索引页,AHI),扫描BP中的flush_list时间较长，造成波动. #额外知识点: 查询频繁如何计算: n_fields hash_analysis，n_hash_potential,last_hash_succ的信息结构 当一次查询成功后，累加hash_analysis属性,记录查询成功次数,当hash_analysis超过17次时候且select字段值唯一,MySQL对此构建AHI.即hash_analysis>=17 and n_hash_potential=100. last_hash_succ是mysql发现场景不适合建立AHI,直接设置false #优化参数 set global innodb_adaptive_hash_index_parts=16; #默认值是8,可以打散AHI锁粒度,hashtable变多. ``` ```sql AHI:将查询频繁的条件和索引树结果做一个hash映射,无需搜索B+tree,直接通过hash定位查询数据。 1.DBA不能干预,innodb_adaptive_hash_index参数控制是否开启 2.等值查询非常快(大并发量级大约30-40%),范围查找没有优化. 3.副作用(5.7.34之前有):MySQL8.0.23版本已经修复 删除大表,Buffer pool比较大的时候(超过32G),会有短暂hang住的情况,造成业务抖动 原因:删除表会把该表在BP内存都淘汰掉,释放空间(数据页,索引页,AHI),扫描BP中的flush_list时间较长，造成波动. #额外知识点: 查询频繁如何计算: n_fields hash_analysis，n_hash_potential,last_hash_succ的信息结构 当一次查询成功后，累加hash_analysis属性,记录查询成功次数,当hash_analysis超过17次时候且select字段值唯一,MySQL对此构建AHI.即hash_analysis>=17 and n_hash_potential=100. last_hash_succ是mysql发现场景不适合建立AHI,直接设置false #优化参数 set global innodb_adaptive_hash_index_parts=16; #默认值是8,可以打散AHI锁粒度,hashtable变多. ``` ### 2.3.2.Change-Buffer - 左边内存,右边磁盘。  ```sql 原理:DML操作二级索引,判断数据页是否在buffer pool中，不在加载到change buffer操作,然后以一定频率对数据和辅助索引数据页进行merge 作用:多次操作合并一次,减少IO。提升DML性能 1.特殊的缓存结构,用于缓存不在buffer pool里面的二级索引 2.支持inser,update,delete(DML) 3.Change Buffer什么时候会merge - 二级索引被读取到buffer pool时候:执行select语句检查是否在change buffer，在则merge到索引页 - 二级索引页 no space left:InnoDB内部监测二级索引页是否有可用空间(至少1/32页),没有则强制执行merge操作 - 后台线程:Master Thread定时merge:核心后台线程,讲缓冲池数据异步刷新到磁盘,保证数据一致性 疑问1？为什么只能针对非聚集非唯一索引 1.主键索引或者唯一索引，需要判断数据是否唯一,去索引页加载数据判断而不能仅操作缓存. ``` ### 2.3.3.ICP(前面) ### 2.3.4.MRR: Multi-Range Read Optimization ```sql 概念:MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能 作用:查询二级索引时，先对查询到的结果按照主键进行排序，再去聚簇索引进行回表 ``` 三个问题: (1)为什么要把随机读转化为顺序读？ (2)怎么转化的？ (3)为什么顺序读就能提升读取性能？ ```sql #执行一个范围查询 explain select * from stu where age between 10 and 20; ``` sql执行-去磁盘读取数据(假设数据不再buufer pool) - 红色是查询过程 - 蓝色是磁盘路线  转化为顺序访问 ```sql set optimizer_switch='mrr=on'; explain select * from stu where age between 10 and 20; #Extra 里多了一个「Using MRR」 ``` 查询过程 ```sql #步骤 - 优化器将二级索引查询到的记录放到一块缓冲区中 - 如果二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序 - 用户线程调用MRR接口取cluster index，然后根据cluster index 取行数据 - 当根据缓冲区中的 cluster index取完数据，则继续调用过程 2) 3)，直至扫描结束 对InnoDB,按照聚簇索引键值排好序,在顺序读取聚簇索引。 好处: 1.磁盘和刺头不需要回来做机械运动 2.利用磁盘预读 3.在一次查询中,每一页的数据只从磁盘读取一次.（如果读取1页，在读2，3，4,再次读取1页可能被缓存剔除了,顺序读则一次读取1234页）. #相关参数 mrr: on/off #开关 mrr_cost_based: on/off #优化器是否基于MRR成本（cost-based choice）,决定SQL使用 read_rnd_buffer_size #给rowid排序的内存大小 如果读取1行数据,没必要MRR,建议设置为on ```  ```sql #总结 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。本质使用空间换时间的算法.对基于索引的查询做的一个的优化，优化的优化了。 ``` ### 2.3.5.Simple Nested-Loop Join（简单的嵌套循环连接） ```sql 定义:嵌套循环连接算法双层的for循环,外表数据逐个与内表数据比较 例如:select * from user tb1 left join level tb2 on tb1.id=tb2.user_id 伪代码类似于: for (user表的行 ur:user 表{ for (level表的行 lr:level表) { if（ur.id = lr.id）\\返回成功匹配的数据 } } 特点:简单粗暴,双层循环比较数据,如果有2个表每个1W条数据. 1W*1W=1亿. 再此基础后优化出现后面两种join优化算法 ``` 匹配过程如下图  ### 2.3.6.Index Nested-Loop Join(索引嵌套循环连接) ```sql 优化思路: 1.减少内层表数据匹配次数,通过外表条件直接与内层表索引匹配,避免笛卡尔积. 2.匹配次数变为: 外表行数*内表索引树高度(一般为2 or 3),极大提升join性能 例子: select * from user tb1 left join level tb2 on tb1.id=tb2.user_id 使用提前: 匹配字段必须**建立索引**,这里指id和user_id ```  ### 2.3.7.Block Nested-Loop Join(缓存块嵌套循环连接) ```sql 优化思路: 1.减少外层表IO次数,一次性缓存外表多条数据,减少内表扫描次数. 2.无法使用index Nested-Loop Join，默认使用此算法 如何使用: 1.开启优化器管理配置的optimizer_switch的设置block_nested_loop为on 2.默认开启：Show variables like 'optimizer_switc%'; 3.设置join buffer大小,join_buffer_size 影响Block缓存条数 ```  block优化图解  ### 2.3.8.Batched Key Access (BKA) ```sql 作用:提高表join性能,当被join表能使用索引-->先排序--->在检索被join的表，没有索引使用BNL 使用范围:inner join, outer join, semi-join operations（半连接）,including nested outer joins(嵌套外连接) 策略:如果被join表没索引.使用 BNL策略 原理步骤: 1.多表join,访问第二个join表,使用join buffer保存操作产生符合条件的数据 2.BKA算法构建Key访问被连接表,批量使用MRR API提交keys到存储引擎查找 3.MRR获取行反馈给BKA 4.BKA返回给被join表# BNL与BKA区别 1.BNL比BKA出现的早，BKA直到5.6才出现，而BNL5.1里面就存在。 2.BNL主要用于当被join的表上无索引 3.BKA主要是指在被join表上有索引可以利用，对这些行按照索引字段进行排序，因此减少了随机IO。 相关参数 使用BKA先打开MRR SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; ``` ### 2.3.9.Join算法总结 ```sql 1.都是在Simple算法基础上优化, 2.INLJ对join循环匹配次数,BNLJ对IO次数 两个角度优化 3.INLJ通过索引机制,减少内层表循环,BNLJ 缓存外表多条数据批量匹配减少外层表IO次数和内表扫表次数 4.表连接查询优化思路 - 小结果集驱动大结果集(减少内层表读取的次数) - 匹配条件增加索引(减少内层表循环次数) - 增大join buffer size大小,同时约束业务join的取值数据量级(一次缓存数据多) - Join Buffer会缓存所有参与查询的列而不是只有Join的列。 - 减少不必要字段查询(join buffer缓存数据多) ```
sunrise
2024年3月20日 10:21
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码