长沙网站排名方案,外包网站价格,柳州住房和城乡建设局网站,wordpress列表页文章摘要本博客全是来自于黑马程序员的mysql专题#xff0c;我写的笔记
索引
是帮助Mysql高效获取数据的数据结构#xff08;有序#xff09;
数据库系统还维护着满足特定查找算法的数据结构#xff0c;这些数据结构以某种方式引用#xff08;指向#xff09;数据#xff0c;…本博客全是来自于黑马程序员的mysql专题我写的笔记
索引
是帮助Mysql高效获取数据的数据结构有序
数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。
在一个数据库中简单的一个数据库如 执行一个语句
select * from users where age 27 有索引的情况下以二叉树为例会对age这个字段建立一个二叉树的索引结构从而大大的提高查询的效率。
如果没有索引的情况下那就需要进行全表扫描。
不同的存储引擎拥有不同的索引 不同的存储引擎对不同的索引的支持情况 正常情况下的索引通常指代的是Btree索引。 关于二叉树
传统上的二叉树存在的问题
如果主键采用的是顺序存储的时候会形成一个单向链表性能会大大降低
大数据量的情况下层级较深检索速度慢。
在MySQL中常用的是Btree和hash算法进行查询
B-Tree树
B树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。 以一颗最大度数max-degree为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key5 个指针树的度数指的是一个节点的子节点个数。
BTree
BTree是B-Tree的变种我们以一颗最大度数max-degree为44阶的btree为例。 BTree 与 B-Tree相比主要有以下三点区别
所有的数据都会出现在叶子节点。 叶子节点形成一个单向链表。 非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。
MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点 的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。
hash索引
哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。
如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。 特点 A. Hash索引只能用于对等比较(in)不支持范围查询between ... B. 无法利用索引完成排序操作 C. 查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索 引 在MySQL中支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能hash索引是 InnoDB存储引擎根据BTree索引在指定条件下自动构建的。 为什么InnoDB存储引擎选择使用BTree索引结构
a、相对于二叉树层级更少搜索效率高
b、对于Btree 无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低。
c、相对于hash索引Btree支持范围匹配及排序操作。
索引分类
在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。 而在在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种 聚集索引选取规则: 如果存在主键主键索引就是聚集索引。
如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。 如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。 思考题 以下两条SQL语句那个执行效率高? 为什么? A. select * from user where id 10 ; B. select * from user where name Arm ; 备注: id为主键name字段创建的有索引 解答 A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引直接返回数据。 而B语句需要先查询name字段的二级索引然 后再查询聚集索引也就是需要进行回表查询。 InnoDB主键索引的Btree高度为多高呢? 一行数据大小为1k一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间主键即使为bigint占用字节数为8。 高度为2 n * 8 (n 1) * 6 16*1024 , 算出n约为 1170 1171* 16 18736 也就是说如果树的高度为2则可以存储 18000 多条记录。 高度为3 1171 * 1171 * 16 21939856 也就是说如果树的高度为3则可以存储 2200w 左右的记录。 以一个数据库为例子 CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
[USING BTREE | HASH]
[COMMENT string]; name字段为姓名字段该字段的值可能会重复为该字段创建索引 create index idx_user_name on tb_user(name); phone 手机号字段的值是非空的唯一的为该字段创建唯一索引 create unique index idx_user_phone on tb_user(phone); 为profession,age, status创建联合索引 create index idx_user_pro_age_sta on tb_user(profession,age,status); 为email建立合适的索引来提升查询效率 create index idx_user_email on tb_user(email); 查看数据库的所有索引数据 show index from tb_user; # 关于删除次数
show global status like com_delete;
# 关于添加次数
show global status like com_insert;
# 关于查询次数
show global status like com_select;
# 关于修改次数
show global status like com_update;
如何开启慢查询
查看
show variables like slow_query_log
会得到 slow_query_log,OFF 这是由于慢查询没有打开
先关闭mysql改配置文件
slow_query_log1
long_query_time2
然后重启
systemctl restart mysqld #开启profile功能
set profiling 1;
#查看每一条sql的执行耗时情况
show profiles ;
#查看指定query_id的sql的耗时情况
show profile for query query_id;
#查看指定query_id的sql语句cpu的使用情况
show profile cpu for query query_id; explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法规则 在所执行的查询语句面前加上explain / desc
