商城网站建设net2006,WordPress_posts,建设项目流程八个阶段,网页设计作业选材目录
索引概述
索引结构
二叉树
B-Tree
BTree
Hash
索引分类
索引语法
慢查询日志 索引概述
索引 (index#xff09;是帮助MySQL高效获取数据的数据结构(有序)。在数据之外#xff0c;数据库系统还维护着满足特定查找算法的数据结构#xff0c;这些数据结构以某种…目录
索引概述
索引结构
二叉树
B-Tree
BTree
Hash
索引分类
索引语法
慢查询日志 索引概述
索引 (index是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用(指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 演示表结构数据如下 假如我们要执行的sor语句为: select * from user where age 45;、
无索引i情况 在无索引情况下就需要从第一行开始扫描一直扫描到最后一行我们称之为全表扫描性能很低。
有索引情况
如果我们针对于这张表建立了索引假设索引结构就是二叉树那么也就意味着会对age这个字段建立一个二叉树的索引结构。 此时我们在进行查询时只需要扫描三次就可以找到数据了极大的提高的查询的效率。
备注:这里我们只是假设索引的结构是二叉树介绍一下索引的大概原理只是一个示意图并不是索引的真实结构索引的真实结构后面会详细介绍。
特点 索引结构
MysQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种: 上述是MysQL中所支持的所有的索引结构接下来我们再来看看不同的存储引擎对于索引结构的支持情况。 二叉树
假如说MySQL的索引结构采用二叉树的数据结构比较理想的结构如下: 如果主键是顺序插入的则会形成一个单向链表结构如下: 所以如果选择二叉树作为索引结构会存在以下缺点:
顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。
此时大家可能会想到我们可以选择红黑树红黑树是一颗自平衡二叉树那这样即使是顺序插入数据最终形成的数据结构也是一颗平衡的二叉树,结构如下: 但是即使如此由于红黑树也是一颗二叉树所以也会存在一个缺点:
大数据量情况下层级较深检索速度慢。
所以在MySQL的索引结构中并没有选择二叉树或者红黑树而选择的是BTree那么什么是BTree呢?在详解BTree之前先来介绍一个B-Tree。 B-Tree
B一Treez树是一种多叉路衡查找树相对于二叉树z树每个节点可以有多个分支即多叉。 以一颗最大度数(max-degree)为5 (5阶)的b-tree为例那这个B树每个节点最多存储4个key5个指针: 树的度数指的是一个节点的子节点个数。
插入一组数据: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88120 268 250 然后观察一些数据插入过程中节点的变化情况。 特点:
5阶的B树每一个节点最多存储4个key对应5个指针。一旦节点存储的key数量到达5就会裂变中间元素向上分裂。在B树中非叶子节点和叶子节点都会存放数据。 BTree
BTree是B-Tree的变种我们以一颗最大度数(max-degree)为4(4阶)的btree为例来看一下其结构示意图: 我们可以看到两部分:
绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。
插入一组数据: 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88120 268 250 。然后观察一些数据插入过程中节点的变化情况。 最终我们看到BTree 与B一Tree相比主要有以下三点区别:
所有的数据都会出现在叶子节点。叶子节点形成一个单向链表非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的BTree的数据结构接下来我们再来看看MysQL中优化之后的 BTree。
MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。 Hash
MySQL中除了支持BTree索引还支持一种索引类型---Hash索引。
结构哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。 如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突(也称为hash碰撞)可以通过链表来解决。 特点
Hash索引只能用于对等比较(, in)不支持范围查询(between ...)无法利用索引完成排序操作查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索引
存储引擎支持在MySQL中支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。
为什么InnoDB存储引擎选择使用Btree索引结构?
相对于二叉树层级更少搜索效率高;对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低;相对Hash索引Btree支持范围匹配及排序操作; 索引分类
在MySQL数据库将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。 而在在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种 聚集索引选取规则:
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构如下: 聚集索引的叶子节点下挂的是这一行的数据。二级索引的叶子节点下挂的是该字段值对应的主键值。
接下来我们来分析一下当我们执行如下的 SQL 语句时具体的查找过程是什么样子的。 接下来我们来分析一下当我们执行如下的soz语句时具体的查找过程是什么样子的 具体过程如下:
由于是根据name字段进行查询所以先根据nameArm 到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到Arm对应的主键值10。由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到1o对应的行row。最终拿到这一行的数据直接返回即可。
回表查询:这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询。
InnoDB主键索引的Btree高度为多高呢? 一行数据大小为1k一页中可以存储16行这样的数据。工nnoDB的指针占用6个字节的空间主键即使为bigint占用字节数为8。
高度为2:
n *8 (n 1)* 6 16*1024算出n约为1170 1171* 16 18736
也就是说如果树的高度为2则可以存储18000多条记录。
高度为3:
1171 *1171 *1621939856
也就是说如果树的高度为3则可以存储 2200w左右的记录。 索引语法
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
查看索引
SHOW INDEX FROM table_name ;
删除索引
DROP INDEX index_name ON table_name ;
先来创建一张表 tb_user并且查询测试数据。
create table tb_user(id int primary key auto_increment comment 主键,name varchar(50) not null comment 用户名,phone varchar(11) not null comment 手机号,email varchar(100) comment 邮箱,profession varchar(11) comment 专业,age tinyint unsigned comment 年龄,gender char(1) comment 性别 , 1: 男, 2: 女,status char(1) comment 状态,createtime datetime comment 创建时间
) comment 系统用户表;INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (吕布, 17799990000, lvbu666163.com, 软件工程, 23, 1,6, 2001-02-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (曹操, 17799990001, caocao666qq.com, 通讯工程, 33,1, 0, 2001-03-05 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (赵云, 17799990002, 17799990139.com, 英语, 34, 1,2, 2002-03-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (孙悟空, 17799990003, 17799990sina.com, 工程造价, 54,1, 0, 2001-07-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (花木兰, 17799990004, 19980729sina.com, 软件工程, 23,2, 1, 2001-04-22 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (大乔, 17799990005, daqiao666sina.com, 舞蹈, 22, 2,0, 2001-02-07 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (露娜, 17799990006, luna_lovesina.com, 应用数学, 24,2, 0, 2001-02-08 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (程咬金, 17799990007, chengyaojin163.com, 化工, 38,1, 5, 2001-05-23 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (项羽, 17799990008, xiaoyu666qq.com, 金属材料, 43,1, 0, 2001-09-18 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (白起, 17799990009, baiqi666sina.com, 机械工程及其自动化, 27, 1, 2, 2001-08-16 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (韩信, 17799990010, hanxin520163.com, 无机非金属材料工程, 27, 1, 0, 2001-06-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (荆轲, 17799990011, jingke123163.com, 会计, 29, 1,0, 2001-05-11 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (兰陵王, 17799990012, lanlinwang666126.com, 工程造价,44, 1, 1, 2001-04-09 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (狂铁, 17799990013, kuangtiesina.com, 应用数学, 43,1, 2, 2001-04-10 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (貂蝉, 17799990014, 84958948374qq.com, 软件工程, 40,2, 3, 2001-02-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (妲己, 17799990015, 2783238293qq.com, 软件工程, 31,2, 0, 2001-01-30 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (芈月, 17799990016, xiaomin2001sina.com, 工业经济, 35,2, 0, 2000-05-03 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (嬴政, 17799990017, 8839434342qq.com, 化工, 38, 1,1, 2001-08-08 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (狄仁杰, 17799990018, jujiamlm8166163.com, 国际贸易,30, 1, 0, 2007-03-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (安琪拉, 17799990019, jdodm1h126.com, 城市规划, 51,2, 0, 2001-08-15 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (典韦, 17799990020, ycaunanjian163.com, 城市规划, 52,1, 2, 2000-04-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (廉颇, 17799990021, lianpo321126.com, 土木工程, 19,1, 3, 2002-07-18 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (后羿, 17799990022, altycj2000139.com, 城市园林, 20,1, 0, 2002-03-10 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status,createtime) VALUES (姜子牙, 17799990023, 37483844qq.com, 工程造价, 29,1, 4, 2003-05-26 00:00:00);
数据准备好了之后接下来我们就来完成如下需求
# name字段为姓名字段该字段的值可能会重复为该字段创建索引。
1CREATE INDEX idx_user_name ON tb_user (name ) ;# phone手机号字段的值是非空且唯一的为该字段创建唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);# 为profession、age、status创建联合索引。
CREATE 工NDEX idx_user_pro_age_sta 0N tb_user(profession, age,status);# 为email建立合适的索引来提升查询效率。
CREATE 工NDEX idx_email ON tb_user (email);
完成上述的需求之后我们再查看tb_user表的所有的索引数据。
show index from tb_user; 慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time单位:秒默认10秒)的所有 SQL语句的日志。MySQL的慢查询日志默认没有开启我们可以查看一下系统变量slow_query_log。 如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2
配置完毕之后通过以下指令重新启动MySQL服务器进行测试查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
systemctl restart mysqld
然后再次查看开关情况慢查询日志就已经打开了。 测试
执行如下SQL语句
select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时
13.35sec 检查慢查询日志
最终我们发现在慢查询日志中只会记录执行时间超多我们预设时间2s的SQL执行较快的SQL 是不会记录的。 那这样通过慢查询日志就可以定位出执行效率比较低的SQL从而有针对性的进行优化。
