mysql索引1-19

目录

1.为什么要给表加上主键?

一个重要概念:平衡树，平衡因子的绝对值不能超过1 
MyISAM和InnoDB存储引擎只支持BTREE索引，即将平衡树当做数据表默认的索引数据结构

#1.平时建表的时候都会为表加上主键，在某些关系数据库中,如果建表时不指定主键，数据库会拒绝建表的语句执行。 
#2.一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的'表'很接近 
#3.事实上， 一个加了主键的表，并不能被称之为'表',如果给表上了主键，那么表在磁盘上的存储结构就
由整齐排列的 结构转变成了树状结构，也就是上面说的「平衡树」结构,换句话说，就是整个表就变成了一
个索引 -->聚集索引

主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。(如下图)

上图就是带有主键的表(聚集索引)的结构图 其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成，即节点只存key，也就是通常我们指定主键的id字 段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

select * from table where id = 1256;

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行

细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

另外在数据之间是一个双链表的结构
select * from table where id > 1256;那么据不需要再一层一层往上面找，直接找他后边的数据就行

2.为什么加索引后会使查询变快?

#不使用索引 O(N)
假如一张表有一亿条数据，需要查找其中某一条数据，按照常规逻辑，一条一条的去匹配的话，最坏的情况
下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条
  数据显然不能一次性读入内存供程序使用，因此，这一亿次匹配在不经缓存优化的情况下就是一亿次IO开
  销，以现在磁盘的IO能力和CPU的运算能力，有可能需要几个月才能得出结果。
  
#使用索引
如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树)，假设这棵树有10层，那么只需要10次IO开销就 能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(logn)
  因此，利用索引会使数据库查询有惊人的性能提升

3.为什么加索引后会使写入、修改、删除变慢?

然而,事物都是有两面的,索引能让数据库查询数据的速度上升而使写入数据的速度下降，原因很简单的 
#因为平衡树这个结构必须一直维持在一个正确的状态:
  增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构
因此，在每次数据改变时，DBMS必须去重新梳理树(索引)的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

1.并不是索引越多查询越快，应该按需创建索引
2.索引越多，对数据的写入和修改需要维护索引，因为是B+树就会面临一个问题：
	分页（存不下了），合并（存的值太小）

4.当我给表加上主键后，根据非主键字段查找时怎么加快，要是设置索引，查询机制是什么?

#引子:当我们设置id为主键，当我们执行下列查询语句:
select * from table where name = alex: 遍历树结构，一个个找，同样很慢，因此需要将name也创建索引，就是常规索引即非聚集索引

#非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。 索引树结构中各节点的值来自于表
中的索引字段 假如给user表的name字段加上索引,那么索引就是由name字段中的值构成,也就是说此树的
节点就是name 在数据改变时，DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么
就会出现多个独立的索引结构，每个索引(非聚集索引)互相之间不存在关联。
每次给字段建一个新索引，字段中的数据就会被复制一份出来， 用于生成索引。 因此给表添加索引，会增
加表的体积，占用磁盘存储空间

5.非聚集索引和聚集索引的区别？

#聚集索引(主键):可以查到需要查找的数据

#非聚集索引(其他字段的索引)：可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据

不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据，聚集索引(主键)是通往真实数据所在的唯一路径

6.什么情况下要同时在两个字段上建复合索引?

 #有一种例外可以不使用聚集索引就能查询出所需要的数据，这种方法称之为「覆盖索引」查询
		即复合索引或者多字段索引查询 
		当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。 

#先看下面这个SQL语句
    create index index_age on user_info(age);
    select user_name from user_info where age = '20'
这句SQL语句的执行过程如下
  首先，通过非聚集索引index_age查找age等于19的所有记录的主键ID值 
  然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据(数据行)存储的位置 
  最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果

#我们把birthday字段上的索引改成双字段的覆盖索引
	create index index_age_and_user_name on user_info(age, user_name);
#这句SQL语句的执行过程就会变为
		通过非聚集索引index_age_and_user_name查找age等于19的叶节点的内容 
		叶节点中除了有user_name表主键ID的值以外,user_name字段的值也在里面,因此不需要通过主键ID值的查找数据行的真实所在,直接取得叶节点中user_name的值返回即可 
		通过这种覆盖索引直接查找的方式,可以省略不使用覆盖索引查找的后面两个步骤,大大的提高了查询性能，如下图：

7.什么是最左前缀的规则？

#使用联合索引时候,如果想要命中索引必须遵循最左前缀原则 
#最左匹配原则中，有如下说明:
	最左前缀匹配原则，非常重要的原则，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的查询优化器会帮你优化成索引可以识别的形式

8.如何命中索引？

1.注意范围问题,或者说条件不明确, 条件中出现这些符号或关键字:>、>=、<、<=、!= 、between.and.、like 
2.尽量使用区分度高的列设置索引
3.索引列不能在条件中参与计算
4.使用最左前缀匹配原则
5.不要使用函数 select * from tb1 where reverse(email) = 'egon';sql语句出现函数时候,索引也未命中 
6.注意类型一致
7.条件为索引，则select字段必须也是索引字段，否则无法命中 select name from s1 order by email desc;

9.如何创建索引？

#方式一
create table t1(
    id int,
    name char,
    age int,
sex enum('male','female'), unique key uni_id(id),
index ix_name(name) #index没有key
);

# 方式二
create index ix_age on t1(age);

# 方式三
alter table t1 add index(sex);

10.Mysql的索引时间复杂度

B+树：O(logn)

11.索引分类？

MySQL索引从存储方式、逻辑角度和实际使用的角度来进行分类。
存储方式区分:
 1) B-树索引
 2) 哈希索引

逻辑区分:
 1) 普通索引：基本的索引类型，值可以为空。没有唯一性的限制
 2) 唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空
 3) 主键索引：主键是一种唯一索引，但他必须制定为PRIMAPR KEY，每个表唯一性
 4) 空间索引：空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
 5) 全文索引：全文索引的索引类型为FULLTEXT。全文索引可以在varchar,char,text类型上创建

组合索引：
	多列值组成一个索引，专门用于组合搜索

12.在普通索引中，什么情况下会引发表锁

常用的索引有三类:主键、唯一索引、普通索引。
	主键:自带最高效的索引属性; 
	唯一索引:指的是该属性值重复率为0，一般可作为业务主键，例如学号;
	普通索引:与前者不同的是，属性值的重复率大于0，不能作为唯一指定条件，例如学生姓名。

1.结论:
	当 Where 查询条件中的字段没有索引时，更新操作会锁住全表! 可以看到，在有索引的情况下，更新不同的行，InnoDB 默认的行锁不会阻塞。
	当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁;当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。
	所有explain查看sql执行计划

13.使用like的时候会出发索引吗？

情况1:
%abc:不会出发索引
abc%:会出发索引

14.如何创建索引

索引分类为：主键索引、唯一索引、普通索引、全文索引（用于varchar和text）、组合索引
1、唯一索引： CREATE UNIQUE INDEX unique_order_id ON biz_data_supplement_task(order_id);
2、普通使用：CREATE INDEX index_order_order_id ON his_supplement_record(order_id);
3、组合索引：CREATE INDEX index_order_id ON rel_order_supplement_approval(order_id,del_flag);
注：如果字段类型为varchar需指定字段长度，避免索引过长 例：
CREATE UNIQUE INDEX unique_order_id ON biz_data_supplement_task(order_id(11));


CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

15.索引越多的坏处

1.并不是索引越多查询越快，应该按需创建索引
2.索引越多，对数据的写入和修改需要维护索引树。

16.你知道哪些数据结构可以提高查询速度？

B+树

17.哈希表索引格式

缺点：
	1.利用hash存储的话需要讲所有的数据文件添加到内存中，比较耗费内存空间
	2.如果所有的查询都是等值查询，那么hash确实很快，但是在企业中查找的数据更多，而不是等值查询(范围查询较多)，因此hash不太适合

18.><between可以用到索引吗

范围条件是：<、<=、>、>=、between
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

19.mysql为什么说单表最多 2 千万数据？真的是这样吗？

数据库表里面的数据是放在页中存储的，一个页大小 16K。包含了页号，指向前后页的指针数据。
mysql采用的B+树结构存储，叶子节点存储数据，非叶子节点存储主键ID和指向子节点指针。

非叶子节点包含X个指向子节点的指针,叶子节点存储数据条数为Y,B+树高度为Z.可容纳的记录条数为(X^（Z-1))*Y.
假设：非叶子节点页头页尾校验码占 1KB,一行数据占 12byte，那么非叶子节点包含 1280 个指向子节点指针，既X=1280,叶子节点中的数据假设占 1KB,一个叶子节点可以存储的数据就是 15 条,Y=15，假设B+树的高度为 3，Z=3,那么存储的数据就是 2500 万。
(1280^(2))*15=24576000
所以规范建议单表最多 2 千万条数据。
但是如果单条数据只有250byte,那么一个叶子节点可以存储60 多条数据，Y=60。B+树高度 3，Z=3，X=1280。
(1280^(2))*60=98304000。约等于 1 亿条数据，高度为 3，查询效率不变

posted @ 2021-05-24 18:34  Jeff的技术栈  阅读(383)  评论(0)  编辑  收藏  举报
回顶部