Mysql关于索引的问题

Mysql关于索引的问题

索引的优点

  1. 减少了mysql需要扫描的数据量
  2. 帮服务器避免排序和创建临时表
  3. 将随机io变成顺序io

mysql有哪些数据类型

  1. 整数:,tinyint,0~8位。smallint,16位。mediumint,24位。int,32位,bigint 64位

    1. 长度:create table (id int(2));这样创建的int,还是32位,只是可以配合UNSIGNED ZEROFILL,当不满2个宽度就高位填充0
  2. 小数:float,double,decimal

    1. decimal存储的精确小数
  3. 日期类型:year,time,date,datetime,timestamp

    1. datetime和timestamp区别,三点

      datetime 1. 是8位,表示从1000-1-1 到10000回退一秒. 2. 且datetime不会根据时区进行转换,存什么是什么。3.datetime类型可以为null,不会自动更新时间,需要上层指定

      timestamp 1.是4位 表示从1970-1-1 到2038-1-19 ,2.timestamp 会根据时区将当前时间转换为时间标准时间存储,取出时在转换为当前时区。3.timestamp,非空,每次update如果没此字段更新值,则会自动更新为当前时间。

    2. year就是年,time当前时间,date当前日期。datetime当前日期加时间

  4. 文本类型:char(0~255) varchar(0~65 535) text(0~65 535)

    char:定长字符串,适用于字符数确定,身份证号码,手机号等

    varchar:可变字符串,需仔细分析。指定长度在这里是字符个数。

    怎么选择这俩种类型?

     1. 在经常变更的字段,使用char。char不容易产生碎片。
     2. 在非常短的列,使用char,char比varchar在存储空间更有效率
    

MySQL存储引擎MyISAM与InnoDB区别

  1. 存储结构:前者会分为三个文件,分别存储表格结构,数据内容,索引内容。后者是数据索引存储在一个文件

  2. 存储空间:前者可以进行空间的压缩,后者需要更多的内存空间,它会在内存创建专用的缓冲池,用来缓冲高速缓存数据和索引

  3. 记录存储的顺序:前者是根据插入的顺序,后者根据索引的大小插入 ?

  4. 外键,事务:前者不支持,后者都支持

  5. 锁支持:前者只有表级锁,后者表级锁和粒度更小的行级锁,锁的粒度更小并发性能更高
    6. select count(*) :前者更快,内部维护了一个计数器,直接返回。只是前者必须无where,有就不一定了

  6. 索引的实现方式:B+索引,前者堆表,后者索引组织表 在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。

  7. 哈希索引:前者不支持,后者支持

  8. 全文索引:前者支持,后者不支持

两者索引区别

  1. InnoDB是聚簇索引,MyISAM是非聚簇索引
  2. InnoDB主键索引的叶子节点直接存储者行数据
  3. MyISAM索引的叶子节点则是存储的是行数据的地址,需要在做寻找
  4. InnoDB非主键索引叶子节点存储的是以当前索引为键值,主键值为元素,所以做覆盖查询会很快。
    1. 如果是非主键索引,这时候查询就select (带索引的字段)

InnoDB存储引擎特性:

  1. 插入缓存

    1. 二次写
  2. 自适应哈希索引 ,innodb存储引擎不支持显示指定索引为hash索引,会隐式的根据执行情况将设置hash索引

  3. 预读,innodb引擎是面向行存储的,存储在磁盘的数据页中。数据页按照指定行格式存储每一行内容。当访问一个元素时,

    会将隔壁数据页一同读出。 磁盘预读的长度一般为数据页的整数倍。从而有效降低io次数

InnoDB有哪些索引种类 ,

  1. 主键索引,主键索引唯一非null alter table tab_1 add primary key(id);

  2. 唯一索引,unique index ,create unque index indexname on tablename(username),唯一可null,只有一个null

  3. 普通索引,index , create index indexname on table tablename(username),只是索引

  4. 复合索引,index(l1,l2), create index indexname on tablename(username1,username2),指定多列为索引

也可创建表时创建索引

​ create table (id int(2) primary key, username varchar(3) ,name1 varchar(3) , name2 varchar(2), unique key(username),key(name1,name2));

三种创建方式,创建表时,alter table 表名 ADD INDEX 索引名 (列名),CREATE INDEX indexname ON TABLE 表名(列名)

删除方式,ALTER TABLE 表名 DROP KEY 列名;主键删除前必须取消自增长,ALTER TABLE 表名 DROP PRIMARY KEY;取消自增长,ALTER TABLE 表名 MODIFY id int drop PRIMATY KEY 。 drop index 索引名 on 表名

InnoDB索引是个b+树,b树的变种。

插入数据,是按照索引的大小排序插入的,索引已链表的顺序插入。这样就可以更快的找到元素,比如 1 2 3 4 5 6 7 找3 则直接1 不是 2 不是 3 找到了。而不是全表扫描寻找。但是这样时间复杂度也是o(n) ,则提出分页的概念,将链表按照页,分成多个块。每页默认大小16kb。然后第二层,记录每页的首节点的id和指向下一个节点的指针。这样就将时间复杂度降低了。这样组织起来就是b+树的结构了。b树区别不只是叶子节点存储行数据。每个节点也会存储。为什么由b树变为b+树?刚才b+树,第二层只存储了id和指针,这样数据量就低了,能指向的页就多。就减少了树的深度。更快的查询到数据。

当需要一次全数据遍历时,b+树可以通过0logn找到最小值,然后on遍历全部。b树则需要一层一层读取。更多的内存置换次数

链表为有序双向链表

聚簇和非聚簇索引

InnoDB主键索引就是聚簇索引

* 叶子节点存储行数据。

非聚簇索引,除了主键的索引

  • 叶子节点存储主键id,然后通过聚簇索引找到行数据

同时,索引的根节点常驻内存。?

​ 如果表中无主键,则使用唯一非空的,再没就隐式的创建一个。如果已经有主键,还想创建聚簇索引,必须先将主键取消,之后创建我们想要的聚簇索引之后创建主键

为什么非主键索引,使用非聚簇索引?

  1. 聚簇索引,需要再叶子节点保留行数据,而非聚簇只需保留主键id,所占用的空间小
  2. 对于增删改,【聚簇索引需要维护行数据。每次新增数据,必定会插入到b+树中,地址也都会有改动。非聚簇索引,只需插入主键值即可,不会影响其余节点】

MyISAM的不同

只有非聚簇索引。每个叶子节点都是直接指向物理地址的行数据。每个索引都是一样的,无需通过主键索引查找。

聚簇索引的优势?

  1. 聚簇索引叶子节点是分页管理的,每次io都会读取一个页进入内存,这样第二次访问就不需要再次io了,直接内存获取。
  2. 聚簇索引辅助索引,先再辅助索引找打主键值,再去聚簇索引找到对应叶子节点。这样可以避开增删改时,树上地址的变化。另外,辅助索引叶子节点占用空间小。

什么情况无法使用索引查找?

  1. 两个字段通过 or 来连接查询时,如果都是索引字段,则可,只要有一放不是,就不行
  2. 两个字段的组合索引,查询时没有第一个索引也不行
  3. 使用关键字 like查询时,%号再前面也会失效,%在后面依旧可以通过索引

创建索引需要注意什么?

  1. 非null字段,null值很难查询优化,应该用一个0值,“”值代替
  2. 离散值很大的,比如uuid
  3. 索引字段越小越好,数据库的数据以页为存储单位一页存储越多一次io就获取的元素更多

前缀索引?

索引字段太长,需截取。index(field(10)) 截取前10个,默认使用全部

实操截取多少合适:可以使用 select count(distinct left(password,prefixlen))/count(*);通过累加prefixlen确定截取多少合适。看结果什么时候最接近1

  • 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
    =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
  • alter table tablename add key

hash索引和b+树索引

hash索引底层就是hash表,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据

b+树是多路平衡查找树,对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。

不同:

​ hash查询进行等值查询快,而范围查询则由于hash散列顺序不同,则不支持

​ b+树所有节点都是左《父《右,支持范围查询

  • hash不支持索引排序
  • hash不支持模糊查询以及多列最左前缀原则
  • hash索引避免不了回表查询,b+树可以只(聚簇索引,覆盖索引)通过索引查找
  • hash索引不稳定,有大量重复值,hash碰撞严重,则等值查询性能也低

索引维护

假设 表中只有主键 id

1, 3, 4, 5 。。。

此时使用主键索引,是一颗b+树。

当你插入6时还好,只是追加。但是当你插入2时,就会涉及到逻辑上的移动。

再严重一点,当你插入数据之后,数据页满了,就会分裂出部分数据到新的数据页,称为数据页分裂。

有分裂就有合并,两个相邻数据页数据少时,会合并。页分裂的逆过程

案例:自增主键

​ 自增主键是最优插入模式。会以前一条id的值+1,插入数据页。正符合了我们前面提到的递增插入的场景。每次插入一条

新记录,都是追加操作,都不涉及到挪动其他记录

怎么选择主键索引?

​ 是选择自增索引,还是自己设置的主键索引?比如身份证是唯一的,我们可以用来做主键索引。但是身份证一般占20字节。主键索引4字节,bigint才8字节。这样用身份证做主键,则其它二级索引叶子节点占的空间就大。所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。

​ 适合自己字段做主键索引?

  1. 索引唯一

  2. 改索引必须为唯一索引

    这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则,直接将这个索引设置为主键,

    可以避免每次查询需要搜索两棵树。

如何避免长事务对业务的影响?

  1. 确认设置了自动提交,set autocommit = 1;
  2. 确认是否有不必要的只读事务。这种只读事务可以去掉
  3. 业务连接数据库的时候,根据业务本身的预估,通过SETMAX_EXECUTION_TIME命令, 来控制每个语句执行的最长时间,避免单个语句意外执行太长时间。

覆盖索引

  1. 得是一个复合索引。
  2. 不回表查询
  3. 所以使用覆盖索引是一个常用的性能优化的手段,显著提升查询性能

最左前缀原则

1. 二级复合索引,按照第一个字段排序,划分树节点。如果第一个字段重复了才安装第二个字段。
2. 只要匹配到联合索引的最左N个字段,也可以是字符串索引的最左M个字符,就是用到了复合索引

索引下推

Mysql5.6之后才有

就是复合索引时,后面一部分怎么处理。

比如 (name,age)复合索引。select * from table where name like “张%” and age = 10;

数据1 张1 age = 3 数据2 张2 age = 10 数据3 张3 age = 4 数据4 张4 age = 10

在5.6之前,就是先找到满足张的,这也用到了复合索引。但是找到之后,就会回表判断是否age=10 四条记录都会回表

5.6之后呢 会先用复合索引的age进行判断 直接就去掉俩条记录,之后再回表

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:黑客帝国 设计师:我叫白小胖 返回首页

打赏作者

天上飞的云传奇

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值