【无标题】

索引是存储引擎中用于快速找到数据记录的一种数据结构，就是类似于教科书中的目录部分，可以通过目录快速的找到文章所对应的页码，MYSQL也是一样的道理，在进行数据查找的时候，首先进行判断这个条件是否命中索引，如果是就通过索引查找相关数据，如果没有那么就需要进行全表扫描，一条一条地将数据加载到磁盘中进行比对

1)如上图所示，数据库在没有索引的情况下，数据本身又分散在硬盘上面的不同位置，在进行读取数据的时候，摆臂需要前后摆动来进行查询数据，这样在磁盘上查找数据非常消耗时间，即使数据是按照顺序进行存放的，那么也是需要按顺序从磁盘上从1位置到6位置进行读取，还要将数据加载到磁盘上面6次，这样的IO操作仍然是非常浪费时间

2)如果不借助任何数据结构来来帮助快速定位数据的话，查找操作就是逐行查找比较，从Col 2 = 34 开始，进行比较，发现不是，继续下一行，当前的表只有不到10行数据，但如果表很大的话，有上千万条数据，就意味着要做很多很多次硬盘I/0才能找到；

3)CPU必须先去磁盘中去查找这条记录，找到之后加载到内存中，再来针对于数据进行处理，这个过程最好费时间的就是磁盘IO，涉及到磁盘的旋转时间和磁头的寻道时间；

如果没有索引:就需要加数据加载到内存中，一个一个加载到磁盘里面进行比对，遍历所有的数据，将所有的数据加载到磁盘中，我们先把第一条数据从磁盘加载到内存中，依次进行数据比对，相当于是顺序查找

有了索引之后减少IO次数，B+树里面每一个节点都是一个数据页，要找77，先加载根节点34到内存中，发现77比34大，那么直接向右走，直接砍掉了一半的数据，然后再加载89到内存中，然后直接向左走，最多3次IO，从磁盘将数据加载在内存中是很浪费时间的

四)说说索引的优缺点:

优点:合理的增加索引，可以提高数据的查询效率，减少数据的查询时间

缺点:

1)创建索引和维护索引需要消耗一定的时间

2)索引需要一定的物理空间

3)对创建索引的表进行新增删除和修改的时候，也需要同步动态维护索引会造成性能的影响

五)MYSQL不适合创建索引:

1)数据量太小:即使不创建索引，查询的速率也是比较快的，这个是或创建索引反而会增加维护的成本和查询时间，查询的时间可能都比不上遍历索引的时间

2)数据区分度不高，有大量重复数据的列不要建立索引:

比如说年龄，性别这样的列构建索引反而会降低检索效率

3)查询条件包含函数操作，索引会失效

4)频繁变更的表:经常要进行更新，删除和插入操作

a)频繁进行更新的字段不需要创建索引，因为更新数据的时候也是需要更新索引的，如果索引太多，那么更新索引的时候也会造成负担从而影响速率

b)如果对表创建的索引过多，虽然提高了查询速度，也会降低更新表的速度

六)InnoDB和MYSIM有什么区别？

存储引擎是定义数据的存储方式和实现数据读取的实现逻辑，

不同的存储引擎就决定了底层的文件系统中文件的物理结构，存储引擎表示表的类型

每一张表都会对应着一个存储引擎，每一个存储引擎来负责表结构在底层存数据到底是一个什么样子的结构

show vaiables like "%storage_engines%"；查看默认存储引擎

show create table+表名字，显示出表的存储引擎

create table user(id int) engine=innodb，造表的时候指定存储引擎

show create user;

alter table 表名字 engine='MYSIM"

MYSIM存储引擎数据和索引是分离的，.MYD文件，D代表Data是MYSIM的数据文件，存放的是数据记录，.MYI文件，I代表index是MYSIM的索引文件，MYSIM索引和数据是分离的

InnoDB只有一个IBD文件索引和数据是结合在一起的

 

1)数据存储的方式不同:MYSIM是将索引和数据存储到连两个不同的文件里面，而InnoDB索引即数据，数据即索引

2)InnoDB支持外键，Innodb可以在表和表之间建立关联关系，来保证数据的完整性，但是MYSIM不支持外键约束；

3)支持行级锁，提高了程序的并发访问性能多个事务可以访问不同的行，避免了锁定整张表的情况，锁的粒度更细，但是MYSIM只是支持表锁，当一个事务对表进行修改操作的时候，其他食物无法对表进行操作，会出现性能瓶颈；

4)InnoDB支持崩溃修复和自增列，可以在数据库崩溃后进行数据恢复，保证数据的一致性

InnoDB支持崩溃修复和自增列的，崩溃修复本身是依靠日志来实现的，底层是依靠日志，Redolog重写日志，重写日志就可以实现崩溃修复，就是数据信息还没有刷盘到MYSQL的磁盘里面，MYSQL崩溃了，此时MYSQL就可以使用Redlog来实现崩溃修复

InnoDB依靠readdolog，重写日志，数据还没有进行落盘，还没有放入磁盘里面，突然掉电了，突然发生意外事故了，此时有redolog，就可以崩溃修复了，但是MSIM崩溃之后就需要人工手动恢复操作可能会导致数据的丢失和数据完整性的不一致问题

5)Innodb支持事务:innodb有ACID四大特性，MYSIM针对于数据统计有额外的常数存储，因此count(*)的查询效率比较高

Memory存储引擎:不支持事务，不支持外键，它是一种内存性的存储引擎，所有的数据都存储在内存中，不支持事务，不支持外键，本身支持hash索引和B树索引

七)说一说数据库的三范式:

第一范式:第一范式规定数据表中的每一个列是不可分割的最小单元

存储地址尽量分割成几个字段去填，淘宝的京东在进行填写货物的地址的时候，先让你进行填写省，接下来让你选择市，区，接下来是详细地址，为什么不把用户的地址分割成一个字段呢？将地址分割几个成几个字段，每一个字段都被拆分成不可分割的最小单元，假设有一天某一个行政单位发生改变，有一天，口琴村变成XX村了，此时如果都写到一块，此时字段就不好修改，替换的时候还会影响其他的，如果表中的字段都是不可分割的最小单元，那么此时就很方便的进行替换了还不会影响其他的字段；

第二范式:存在非主属性对于主键的部分函数依赖

一个表当存在联合主键，有两个主键字段充当整张表的联合主键(一个主键)，不能说有一个非主键字段只依赖于联合主键中的一个而不依赖另一个联合主键，不能存在非主键字段对于部分主键的依赖，一定要对联合主键都依赖；

要想知道表中的某一个字段，必须知道联合主键，这个字段必须由联合主键一起推出来才可以，不能由某一个联合索引的一个字段就可以推出来

第三范式:消除非主属性对于逐渐的传递函数依赖

表中的列不存在对非主键列的传递函数依赖，一个非主键列3推出非主键列2，非主键列2推导出主键列1，从而非主键列3推出主键列1

 

 

上面姓名和年龄只是依赖于联合主键的球员编号和比赛编号没关系

比赛时间和比赛场地只是依赖于联合主键的比赛编号，和球员编号没关系

但是最终得分是完全依赖于主键的，必须由比赛编号和球员编号共同推出来，最终拆分成三张表

二范式解决数据量冗余

而是A->C，B->C，非主键字段不能有任何依赖关系，部门名称依赖于部门编号，部门编号依赖于员工编号

公司名字依赖于顾客id，顾客id依赖于订单id

八)内连接和外连接有什么区别？

内连接和外连接是关系型数据库常见的连接操作:

内连接:两个表中都存在的字段最终才会包含在结果集中

左外连接:左外连接直接返回左表中的所有记录，以右表中满足连接条件的匹配记录，如果右表中没有匹配的记录，那么右表的记录值就为null；

右外连接:直接返回右表中的所有记录，以及左表中满足要求的所有记录，如果左表中没有符合要求的纪录，，那么左表的记录值就是null；

九)MYSQL中索引的分类:

索引的分类:

一)按照字段特性进行分类:

1)主键索引:数据列不允许重复，不允许为null，在一张表只能有一个主键；

2)唯一索引:数据列不允许重复况且允许为null值，在一张表中允许多个列创建唯一索引；

3)普通索引:基本的索引类型，没有唯一性约束，也允许为null值

4)全文索引:对文本的内容进行分词，搜索

二)按照物理存储进行分类:聚簇索引和非聚簇索引

三)按照索引数量进行分类:

1)单列索引:针对表中的某一列创建的索引，可以根据该列的值快速定位到所对应的记录，单列索引适用于针对于单个列进行频繁的查询排序和过滤的场景，比如说可以针对于用户ID列创建索引，以便根据用户ID快速的进行查询

2)联合索引:针对于表中的多个字段进行建立索引，也被称之为是复合索引或者是组合索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式，所有的用户记录都存储在了叶子节点上面，数据行和相邻键值是存储在一起的，B+树分成聚簇索引和非聚簇索引

数据自动添加的时候，底层的B+树就已经自动创建了一个聚簇索引

1)每一个页中的记录按照主键值的大小顺序组成了一个单向链表

2)各个存放用户记录的页也是根据记录的主键顺序大小组成了一个双向链表

3)放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表，所有的用户记录都会存放在聚簇索引的叶子节点处;

4)这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX 语句去创建， InnDB 存储引擎会 自动 的为我们创建聚簇索引，由于对于MYSQL数据库来说数据物理存储排序方式只能有一种，所以每一个MYSQL的表中只能有一个聚簇索引，一般情况下就是该表的主键

5)InnoDB的主键尽量选择有序的顺序ID，而不建议使用无序的ID，比如说UUID，MD5，字符串作为主键无法保证索引的顺序增长

聚簇索引的缺点:

1)聚簇索引的插入速度严重依赖于插入顺序，按照主键的插入顺序是最快的插入顺序，否则会出现页分裂，会严重影响到性能，所以对于Innodb存储引擎来说，一般选择自增的ID作为主键

2)对于聚簇索引来说，主键的更新代价很大，因为可能会导致被更新的行进行移动，对于Innodb表来说，一般定义为表不可更新

上面建立的聚簇索引都是只能是主键作为搜索条件的时候才可以发挥作用，因为B+树中的数据都是按照主键进行排序的，那么如果想要以别的列作为搜索条件怎么办呢？肯定是不能从头到尾按照链表全部遍历一遍，答案是可以创建多个B+树不同的B+树采用不同的排序规则

这个时候c2列在叶子节点是按照升序来进行排列的，c2列下面还会存放主键ID

如果每一个二级索引都存放完整数据，那么就太浪费空间了

B+树作为索引的注意事项:

1)根节点位置万年不动:

1.1)一开始现在只有一个页page1就是来存放一条一条的记录的，假设一个页能够存放三条记录，现在这个第一个页中已经存放三条记录了，这个时候再去像这个页中添加数据，不是新创建一个页，来存放第四条数据

1.2)此时会创建一个新的目录页page2，将原来第一个页中的三条数据放到这个新的page2中，因为此时又新增了一条数据，但是此时page2也放不下，此时又会开辟一个新的页page3来存放新的记录，假设这条记录的主键值比page2的主键值都大，如果不是，那么就在进行调整，此时会将page2和page3中的最小值存放到page1里面，此时page1就变成了目录页

1.3)当为某一张表创建B+树索引的时候，聚簇索引不是人为创建的，默认就有，都会为这个索引创建一个根节点页面，最开始表中没有数据的时候，B+树的索引对应的根节点既没有用户记录，也没有目录项记录，随后向表中插入数据的时候，数据会记录在根节点中

1.4)当根的可用空间用完之后向这个表中插入记录，此时会将根结点的所有记录复制到一个新开辟的页中，比如说页a，然后针对于这个页进行页分裂的操作，得到一个新的页，页B，此时新插入的记录根据主键值的大小就会被分配到页a或者是页b中，而根节点是作为存储目录项记录中的页

这个过程需要特别注意的是，一个B+树的索引的根节点自诞生开始就不会再进行移动，只要针对于某一张表建立了索引，那么它的根节点的页号就会被移动到某一个地方，从今以后凡是InnoDB引擎在使用到这张表的时候，都会从固定的地方取出根节点的页号从而来访问索引

2)目录项记录的唯一性

3)一个页中至少存放两条记录 

 十)聚簇索引和非聚簇索引有什么区别? 

1)叶子节点上存储的东西不同:聚簇索引的叶子节点上存放的是完整的数据，而非聚簇索引叶子节点上存放的是主键的ID

2)查询效率不同:聚簇索引的查询效率要比非聚簇索引效率要高

3)数量限制不同:聚簇索引通常是主键索引，而主键一张表只能有一个，但是非聚簇索引表中是可以有多个的

回表查询:非聚簇索引的叶子节点存放的并不是真实的数据，而是聚簇索引的主键ID，，所以当时使用到普通索引查询的时候需要先查询到主键索引，然后再从主键索引中查询到真实的数据，这个过程就是回表查询

1)所以说在InnoDB存储引擎中，不建议使用过长的字段来作为主键，因为所有的二级索引的叶子节点都是主键值，过长的主键索引会使二级索引变得很大

2)使用费单调性的字段作为索引不是一个好主意，因为InnoDB的数据文件本身就是一颗B+树，而非单调性的主键在进行插入新记录的时候，数据文件会维持B+树的特性而进行分裂调整，十分低效，所以使用自增字段作为主键是一个不错的选择；

十一)MYSQL索引选择顺序结构的合理性:

全表扫描:将所有的索引依次加载到内存中，加载一个数据需要一次磁盘IO，数据库的查询本身就是查询索引的操作，当数据量比较大的时候，索引都是存放在磁盘上面的，当使用索引进行查询的时候，不可能将整个索引全部加载到内存里面，而是用到谁加载谁

1)Hash索引:只有Memory存储引擎支持哈希索引，下面是哈希索引的缺点:

1.1)哈希索引只能支持等于，不等于还有in查询，如果进行范围查询，哈希索引的时间复杂度会退化成O(N)

1.2)哈希索引本身并没有顺序，无法支持order by

1.3)针对于联合索引无能为力

1.4)一般来说针对于等值查询来说，哈希索引的效率要更高，不过就是有一种情况索引的重复值如果很多，那么哈希索引的效率就会降低，这是因为当遇到哈希冲突的时候，一般使用链式法来解决哈希冲突，链表的查询效率过低，所以说不建议hash索引建在重复列比较多的字段，比如说姓名，年龄，性别

但是InnoDB支持自适应哈希索引，什么情况下才会使用到自适应哈希索引呢？就是当某一个数据经常访问，当满足一定条件的时候，就会将这个数据页的地址存放到哈希表中，这样子下一次进行查询的时候，就可以直接找到这个页面的所在位置

innodb默认使用常驻哈希索引是不需要进行指定的

使用自适应哈希索引目的是为了方便根据SQL的查询条件很快的定位到叶子节点，尤其是当B+树比较深的时候，使用哈希索引可以快速的定位到叶子节点，可以加快数据检索效率

mysql> show variables like '%adaptive_hash_index';

2)二叉搜索树:

1)二叉搜索树可能退化成单分支的树，退化成链表

2)二叉搜索树一个节点只能存储一个值，进行一次磁盘IO只能比较一次

3)树的高度比较高，就算不退化成链表磁盘IO也很高

3)AVL树:和二叉搜索树类似，树的高度太高了，每访问一个节点就需要进行一次磁盘IO操作，虽然AVL树相比于二叉搜索树来说维持了自平衡的特性，相比于B+树一点优势都没有

4)红黑树:原因和不使用B树差不多，甚至还不如B树

1)红黑树不如B+树更矮胖，红黑树高度更高，要进行多次磁盘IO

2)红黑树一个节点只能存放一条数据，一次IO只能进行一次比较，而B+树一次磁盘IO可以进行多次比较

3)红黑树插入节点不仅要改变频繁节点颜色，有时候左旋，右旋，插入性能非常低老保证红黑树的特征，插入删除不如B+树，B+树有冗余节点，插入和删除效率更高

4)处理范围查询不方便，红黑树也要进行深度优先遍历才能得到范围内的数据

5)红黑树和B树非叶子节点即要存数据又要存放ID，没有冗余节点，没有冗余字段，所以需要的页非常多，每一个页中存放的ID是非常少的，只有B+树叶子节点全部是完整的数据

红黑树本质上是二叉树，插入很麻烦，频繁左旋右旋，一个节点值只能存放一个数据，是可以要满足平衡二叉树的性质，需要大量的旋转和更改颜色来维持红黑树的特性，增删效率都很低；

B树:

1)N个关键字有N+1个分支

2)数据分布在整个B树中，B树的叶子节点和非叶子节点都存放数据，整个数据的搜索可能在非叶子节点结束，它的搜索相当于是做一次二分查找，只能通过中序遍历来排序；

B+树和B树的区别:

1)B+树K个节点就有K个关键字，但是B树K个节点有K+1个关键字

2)B+树非叶子节点的关键字也会出现在叶子节点中，并且是作为子节点中的最大值或者是最小值

3)B+树的非叶子节点仅仅只是用做索引而不保存最终的完整的数据记录，但是B树中非叶子节点及保存索引也保存数据的完整记录

4)B+树的所有关键字都在叶子节点出现，叶子节点构成一个有序链表，并且叶子节点也是按照从小到大的顺序来进行排序的

十二)为什么索引选择B+树而不选择B树？ 

1)B+树的查询效率更稳定:

所有的数据都存储在叶子节点上，但是B树可能查询终止在叶子节点上

2)B+树处理范围查询更方便:B树只能通过中序遍历来处理范围查询，而B+树可以直接通过截取链表中的一部分进行处理范围查询

3)B+树插入和删除性能更好:

B+树有大量的冗余节点，所有的非叶子节点都是冗余索引，这些冗余索引使得B+树在进行插入删除结点相比于B树的效率更高

4)B+树的IO次数更少:

B+树的非叶子节点不存放具体的实际的记录数据而是存放索引，B树的所有结点既存放用户的数据又存放索引，因此当数据量相同的情况下，B+树的非叶子节点可以存放更多的索引，因此在查询的时候IO查询次数更少，效率更高，16K的页只存ID，是可以存储很多ID的，但是如果这个16K页既存储数据又存储ID，在同一个页中，存储的ID就会比B+树同等的叶子节点少很多，B+树的非叶子节点一次IO可以得到更多的ID，就可以进行更多的比较，那么在同等查询下，遍历B+树的非叶子节点的个数就要B树少，比B树遍历IO次数就会很少，效率会很高，MYSQL的根节点是常驻内存的，B树的一个页存储的目录项要比B+树的存储的目录项少，所以B+树更矮胖；

十三)B+树的存储能力如何，为什么说只需要一次磁盘IO或者是三次磁盘IO？

1)InnoDB存储引擎中的页的大小是16KB，假设BIGINT占用8个字节，指针类型占用8个字节，也就是说一个页中大概存储16KB/(8B+8B)=1000个键值，也就是说深度是3的B+树可以存放10^3*10^3*10^3=10亿条记录，这里面是假定一个数据页可以存储10^3条行记录数据了

2)但是实际情况中可能每一个节点可能不能完全填充满，因此在数据库中B+树的高度一般都是在2-4层，MYSQL的存储引擎在设计的时候是将根节点常驻内存的，也就是说再进行查找某一个键值的行记录最多只是需要1-3次磁盘IO操作的

十五)如何创建索引删除索引？ 

一)创建索引:

1)在创建表的时候构建索引:

1.1)使用create table创建表时，除了可以定义列的数据类型外，定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引,没有给索引起名字，那么就直接使用字段名当索引

1.2)可以在创建表的时候构建索引:

2)创建表以后创建索引:

--创建索引,具体格式是 index 索引名字(表中的字段名字)
create table user(
userID int,
username varchar(30),
index idx_username(username)
);
--1.命令查看索引
show create table user;
--2.查看索引
show index from user;
--3.创建唯一索引,下面的username就不能有相同的值
create table user(
userID int,
username varchar(30),
unique index idx_username(username)
);
--4.删除主键索引,不能有auto_increment
alter table user drop primary key;
--5.创建联合索引
create table user(
userID int,
username varchar(30),
index idx_username(userID,username)
);
--6.创建全文索引,只会拿前20个字符作为索引
create table user(
userID int,
username varchar(30),
fulltext index text_username(username(20))
);

十六)哪些情况适合创建索引呢？

1)针对字段有唯一性的限制:索引本身是可以起到约束的作用的，例如唯一索引和主键索引都是可以起到约束作用的，因此在数据表中如果某一个字段是唯一的，就可以创建唯一性索引或者是主键索引，这样就可以快速地根据索引确定该条记录，例如学生表中学号是唯一的字段，针对该字段建立唯一索引就可以快速地确定学生的信息

2)频繁做Where查询的字段:某一个字段经常在Select语句中经常被使用到，那么就需要给这个字段建立索引了，尤其是数据量比较大的情况下，创建普通索引就可以大幅度提升数据查询的效率，假设现在学生表中有1000条数据，查询name="张三"的信息，就可以建立索引

针对于多表的join的连接字段要创建索引，where的条件要创建索引，不能连接过多的表

3)经常group by和order by的列:然后针对于order by来说如果存在索引那么这个索引已经排好序了，于是就节省了排序的时间，同理group by是相同的字段组合成一组，同理索引已经是默认排好序的，那么排好序之后相同的字段挨在一起了;

先考虑student_id在考虑create_time，创建联合索引效率更高

单独使用group by你就针对该字段建立一个索引，order by也是同理，如果既要进行group by又要进行order by，那么首先将group by放在前面

4)经常要update和delete的条件列:

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率,原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除,如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护

5)针对于distinct的字段需要建立索引:select distinct(studentid) from user

因为在对去重的字段建立索引的时候去重的字段本身就挨着，对于紧挨着的字段进行去重本身就简单很多，因为索引本身就是排好序的

6)针对于列的类型小的字段建立索引:

假设说现在有一个字段叫做ID，那么这个ID类型上可以在考虑满足实际要求的情况下，尽量选择类型小的，创建表以后添加这个表中数据的时候占用表空间比较少一些，另一种情况就是当我们给这个字段添加索引的情况下，假设如果针对id建立索引，id占用的空间比较大，那么在非聚簇索引中一个页中存放的数据项就会相对于来说比较少，如果id本身占据的数据相比较小，那么一个页中能够存放的数据项就比较多，那么此时一个页中放的数据更多，那么这颗B+树也就会变得越扁平，此时IO次数就会更少

假设极端情况下这个ID是一个主键，此时ID所占的大小，此时影响的就不光是一个聚簇索引了，而是会影响所有的非聚簇索引

7)针对于字符串的前缀创建索引:

当字段类型是字符串类型的时候(varchar，text等)，有的时候需要索引长度很长的字符串，这就会使得索引占用的空间很大，查询的时候浪费大量的磁盘IO，影响查询效率，此时就可以之将字符串的一部分前缀作为索引，这样可以大大的节省空间，从而提升索引效率

create index 索引名字 on 表名(字符串的列(前几个字符作为索引))

如何选择截取的字符的大小呢？

如果截取字符截取的过多，那么会达不到节省空间的目的，如果截取字符截取的太少，那么字段的离散度和选择度就会变得很低，那么如何进行计算不同长度的选择性呢

1)首先先观察一下字段在全部数据中的选择度:

select count(distinct(address))/count(*) from user

2)通过不同长度去计算和全表的选择性进行对比:count(distinct(left(address，10)))/count(*)

这个值越接近1越好，说明越有区分度

elect count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

count(distinct(left(address，10))/count(*)==1，说明截取前10个字符也是不重复的

假设现在使用到了前缀索引，只是把address列的前12个字符放到了二级索引中，下面的这个查询就有点尴尬了:

select * from user order by address limit 12；

但是这个二级索引不包含完整的address列信息，所以无法对前12个字符相同，后面字符不同的记录进行排序，所以使用前缀索引不支持索引排序，因为唯一索引的选择性是1，这是最好的索引选择性

假设一张表中有50条记录，现在针对adress列的前12个字符建立了前缀索引，那么此时就会出现问题，假设50条记录中前49条中前12个字符都是都是相同的，那么此时针对于order by进行排序或者是group by进行排序麻烦，索引使用前缀索引无法支持索引排序

8)选择区分度高的字段作为索引

select count(要计算区分度的字段)/count(*) from 表名

假设现在有100万条数据，假设根据性别建立索引，男生50W，女生50W，那么此时基数就是2，所谓的区分度非常差劲，这个时候针对gender创建索引就不太好的

9)使用最频繁的列放在联合索引的左侧:

十六)如何排查慢SQL 

在MYSQL中排查慢SQL通过开启慢查询日志来开启排查慢SQL，然后分析和解决慢SQL

慢查询是MYSQL提供的一种慢查询日志，具体指运行时间超过long_query_time的SQL，那么会被记录到慢查询日志中，这个参数默认是10，意思就是说运行时间超过10s以上的语句

默认情况下MYSQL是不会开启慢查询日志，况且需要我们手动来设置这个参数，如果不是条有需要的话，一般是不建议开启这个参数的，因为开启慢查询日志会给MYSQL服务器带来一定的性能影响，慢查询日志支持将日志记录写入到文件，也支持将日志记录写入到数据库表

通过下面这个命令就可以来进行查询慢日志是否开启:

如果要开启慢查询日志:但是下面这个命令只会对当前数据库生效，如果MYSQL重启也会消失，如果要想永久生效，就必须修改MYSQL的配置文件my.cnf

设置命令:set gloabl show_query_log=1;

修改阈值:

查询慢查询日志条数:

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

开启慢查询日志

然后找到慢SQL记录以后

再执行Explain执行计划来查询慢SQL

然后分析慢SQL的原因

1)没有加索引，直接加索引就好了

2)SQL语句写法问题导致没有触发索引，从而来调整查询SQL就可以解决性能问题

3)如果数据量过大，加缓存来减少数据查询的次数或者是分库分表，要进行垂直分割和水平分割，绝大多数加上索引和修改SQL的结构就可以了；

十七)explain执行计划:

1)table:查询出来的每一条记录都对应着一张单表，因为结果集可能出现多条记录

2)id:最终的结果最上面的记录称之为驱动表，下面的记录称之为是被驱动表，有时候优化器会针对驱动表和被驱动表做一个替换，表示在一个大的查询语句中每一个select关键字都对应着一个唯一的id，查询语句中有两个表那么就一共有两条记录，但是select关键字一共只有一个，那么id也就只有一个，一层嵌套一个select，那么就代表有一个id是外查询，有一个id是内查询，内查询的id比外查询的大

1)查询优化器可能对涉及到子查询的查询语句进行重写，转化成多表查询的操作

2)union去重，内部需要创建临时表来去重，unionall不去重

3)select_type:可以确定select关键字对应的查询的类型，确定小查询在大查询中扮演了一个什么样子，普通查询和连接查询都是simple类型(不包含union，不包含子查询)

1)查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型

2)union前面是primary 后面是union

3)对于子查询来说，最左边就是primary，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY,该子查询没有转化成多表连接

4)最终转化成了多表连接:

4)type:

1)system:当表中只有一条记录况且存储引擎是MYSIM的时候因为统计记录时精确的，查询就是sysyem，越在前面效果越好，MYSIM存储引擎内部维护一个变量来记录表中字段的个数，但是在innodb中，它统计数据就不是精确的了，没有维护内部变量，此时即使是数据中只一条记录，也是走的是全表扫描；

2)const:当根据主键或者是唯一二级索引与常数进行等值匹配的时候，对于单表的访问方法就是const，假设现在User表中的userID是主键，username是唯一索引，此时针对于这两个字段进行等值匹配的时候，type的类型是const

 create table user(
     userID int,
     username varchar(30),
     primary key(userID),
     unique index username_index(username));

3)eq_ref:再进行连接查询的时候，如果被驱动表是通过主键或者是唯一二级索引等值匹配的方式来进行访问的，如果该主键或者是唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较，此是被驱动表的访问方法就是eq_ref 

select * from user where username=123，此时这个字符串没有加双引号，此时就会使用到函数，此时就会索引失效

select * from s1 inner join s2 on s1.id=s2.id

在s2表中找到值和s1.id进行相同的记录，此时的查询过程是在s1中取出每一个id值然后去s2表中去查找有没有s2的id和s1相同的，所以针对s1是全表扫描针对于s2是使用到了索引

4)ref:当通过普通的二级索引和常量进行等值匹配的时候来查询这张表，那么此时的查询结果就是ref

5)ref_or_null:当对普通二级索引进行等值匹配的时候，该索引列的值也可以是null的时候，那么对该表的访问方法就是ref_or_null

6)index merage:当时用到or的时候

7)unique_subquery:驱动表是全表扫描

8)range:如果使用索引来获取某些范围区间内的记录，那么就有可能使用到range访问方法

9)index:可以使用到覆盖索引，但是需要扫描全部的索引记录的时候就会使用到index

ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

5)key:真实使用到的索引；

6)possible keys:可能是用到的索引

7)key_len:实际使用到的索引长度，主要针对于联合索引有参考意义

MYSQL，int占用四个字节，如果说int类型的变量可以是null，那么实际使用到的索引长度会多一个字节，针对于字符串类型，MYSQL中utf-8编码字符类型占用三个字节，null的情况1个字节，还要使用2个字节来记录实际长度，因为varchar长度是不确定的，针对于联合索引来说，联合索引使用到的索引长度越长，那么查询效果越好，如果没有使用到索引，那么key_len长度是0；

8)ref:当时用到索引或者是等值查询的时候，与索引列进行等值查询的匹配的对象信息，比如说只是一个常数或者是某一个列

rows和fiter可以联合在一起进行查看:

rows:预估的需要读取的记录数，值越少越好，值越少，页越少

fiter:rows中能够查询到记录的概率的百分比，百分比越高越好

1)这个SQL语句表示的是382条数据满足key1>'z"其中只有百分之10的记录满足common_field="a"

2)下面中的这个SQL语句代表的是，一共有9895条记录中只有10%的数据满足s1.common_field='a'，然后MYSQL再拿着10%的驱动表的记录去匹配被驱动表进行连表查询

10)extra:

1)no tables used:没有任何表被使用

2)imposble where

 3)using where:没有使用到任何的索引，此时针对于s1没有任何的索引，就是当使用全表扫描的时候完成针对于某一张表的查询，并且该where与具有搜索条件但是还没有索引

4)using index:使用覆盖索引，建议使用覆盖索引，联合索引使用覆盖索引比较好

key1本身有索引，这里就是用到了覆盖索引

5)using condition:有些搜索条件虽然使用到了索引列，但是却不能够使用索引，索引下推是再进行索引遍历过程中对索引中的字段先做判断，直接过滤掉不满足要求的纪录，从而减少回表的次数

1)先找key1>"z"的所有记录，然后进行回表查询，此时再从主键索引中筛选key1 like "a%"的记录，此时回表次数比较多；

2)下面这种情况，针对于key1>'z'使用到了索引，索引遍历过程中，然后再根据key1进行过滤掉不满足要求的纪录，甚至此时经过过滤完成之后，符合两个条件的记录一个主键ID值都没有，此时就不用再进行回表查询了，此时回表次数相比于第一次来说比较少很多

6)再进行连接查询的过程中，当被驱动表不能有效地利用索引来加快访问速度，MYSQL会为其分配一块名字是join buffer的内存块来加快查询速度

下面common field没有索引

7)not exists:此时的id字段是一个主键，主键是不可能是null的

8) zero limt:

9)using intersect

10)using filesort:不能使用已经排好序的B+树了，显然性能很低下，当我们出现这个字段的时候是很有必要给这个字段加上一个索引，所以说如果某一次查询需要使用到文件排序是在的的方式来进行查询那么此时就会在执行计划中的Extra列显示using filesort

下面的字段针对于common_field字段是用排序会使用到文件排序

11)using tempory:比如说要进行去重操作的时候如果没有使用到索引，group by distinct

是使用到临时表，有时候是用到临时表进行去重，但是索引本身已经是有序的的