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. 插入缓存

2. 2. 二次写

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

4. 预读，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进行判断 直接就去掉俩条记录，之后再回表