mysql 索引pk_mysql索引

Mysql索引

索引优点：减少I/O次数，加快检索速度

根据索引分组和排序，可以加快分组和排序

缺点：索引是空间换时间来实现快速查询的，所以索引本身也会占用存储空间

索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大

1)创建索引时，会给表加锁，因此实际操作中需要在业务空闲期间进行

2)索引会降低数据表的修改操作(删除，添加，修改)的效率，因为在修改数据表的同时还需要修改索引表

索引失效：

需要注意字符串与整数之间的强制类型转换，导致索引失败，没有走索引，有时候少一个引号，就会使得行锁升级为表锁。

聚集索引：

数据和主键索引存储在一起

InnoDB聚集索引是按照主键进行聚集的，每张表只能有一个聚集索引，表数据文件本身就是

按B+Tree组织的一个索引结构，叶子节点的data域保存了完整的数据记录;所以说，InnoDB表数据的文件本身就是

主索引文件(也就是刚刚说的，同一个结构中保存了B+Tree索引和数据行)。聚集索引的方式会根据主键的范围

查找和排序非常快

对于一张表来说，聚集索引只能有一个，因为数据真实的物理存储顺序就是按照聚集索引存储的(聚集索引规定数据在表中的物理存储顺序)

辅助索引：

也叫普通索引：叶子节点存储了主键

InnoDB辅助索引的实现方式是所有辅助索引都引用主键作为data域，因此辅助索引所有需要检索两次索引才能获取

到数据记录(辅助索引查到主索引，在根据主索引获取叶子节点中的数据行)，但是这样辅助索引的变更会很方便(不会影响主索引组织的数据文本本身)，同时因为所有的辅助

索引都引用主索引，不建议主索引过大

聚集索引与辅助索引区别：

聚集索引存的就是数据，非聚集索引(辅助索引)存的是指针. 所以表只有一个聚集索引，但可以有多个非聚集索引

回表查询：数据库根据索引找到了指定的记录所在行(主键)，还需要根据主键再次到数据块里中取数据的操作

mysql通过索引找到主键，根据主键再次通过聚集索引找到数据;

1)如果表定义了PK，则PK就是聚集索引

2)如果表没有定义PK，则第一个not NULL unique列是聚集索引

3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引

这里强调下oracle,(索引值→ROWID->将ROWID换算成一行数据的物理地址->得到一行数据)

1)在oracle中，没有聚集索引的概念：在数据中，当查询数据的时候，通过索引获得该行的rowid，根据rowid再查询表中数据，就是回表。

2)索引存储了两样东西，一个索引列值，另一个rowid；当查询的返回结果正好是索引列值时，不会更具rowid在此回表查询其他列的数据oracle 执行计划 ACCESS BY INDEX ROWID 等于回表

ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。

ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID

能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。

索引覆盖：

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表

数据库允许空值(null)

1)负向比较(例如：!=)会引发全表扫描；

2)如果允许空值，不等于(!=)的查询，不会将空值行(row)包含进来，此时的结果集往往是不符合预期的，此时往往要加上一个or条件，把空值(is null)结果包含进来；

3)or可能会导致全表扫描，此时可以优化为union查询

4)建表时加上默认(default)值，这样能避免空值的坑

SQL优化方向：执行计划、索引、SQL改写(mysql的资源通常比程序代码的资源紧张的多)。

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

架构优化方向：高可用架构、高性能架构、分库分表。

业务方向： 级联查询，变更为单表查询，然后再次代码循环其他的表

如何快速定位低效sql:

1。explain结果中的Extra段(额外信息，可以判断是否走索引)

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。这类SQL语句往往性能较好

Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。这类SQL语句性能也较高，但不如Using index。

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化。索引全覆盖情况下，不会走Using filesort，其他无论字段是否包含索引，都会显示Using filesort

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。这类SQL语句性能较低，往往也需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。这类SQL语句性能往往也较低，需要进行优化。典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

Extra为Using where说明，SQL使用了where条件过滤数据。使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type(连接类型)来综合判断

2。explain结果中的type字段，表示(广义)连接类型，它描述了找到所需数据使用的扫描方式；

1)常见的扫描类型有：

system>const>eq_ref>ref>range>index>ALL

其扫描速度由快到慢；

2)各类扫描类型的要点是：

system最快：不进行磁盘IO(系统表已加载到内存)

const：PK或者unique上的等值查询，被连接的部分是一个常量(const)值 id=2323 2323常量值

eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中

ref：非唯一索引，等值匹配，可能有多行命中

range：索引上的范围扫描，例如：between/in/>

index：索引上的全集扫描，例如：InnoDB的count

ALL最慢：全表扫描(full table scan)

3)建立正确的索引(index)，非常重要；

4)使用explain了解并优化执行计划，非常重要

Mysql多表连接查询的执行细节 ：

1)mysql表与表之间的关联查询使用Nested-Loop join算法，顾名思义就是嵌套循环连接

2)首先根据第一步的逻辑来确定驱动表a，然后通过a.a1>4，a.来查询一条记录a1=5，将此记录的c2关联到c表，取得c2索引上的主键c1，然后用c1的值再去聚集索引上查询c.*，组成一条完整的结果，放入net buffer，然后再根据条件a.a1>4，a. 取下一条记录，循环此过程

3)通过索引关联被驱动表，使用的是Index Nested-Loop join算法，不会使用msyql的join buffer。根据驱动表的筛选条件逐条地和被驱动表的索引做关联，每关联到一条符合的记录，放入net-buffer中，然后继续关联。此缓存区由net_buffer_length参数控制，最小4k,最大16M,默认是1M。 如果net-buffer满了，将其发送给client，清空net-buffer，继续上一过程

Order by 工作原理：

[深入理解MySQL系列] - sort_buffer 地址中可以发现，为什么order by字段加索引后，会提升查询速度(使用主键索引不会出现Using filesort)

先把满足条件的数据取出来(如果是索引全覆盖，不会使用这个，索引本身已经产生顺序)，放入到sort_buffer中，然后根据排序条件，对sort_buffer内容进行排序，如果排序量过大，还会借助磁盘

sort buffer是系统中对数据进行排序的时候用到的Buffer。

Using filesort MySQL需要额外的一次传递，以找出如何按排序顺序检索行。

Using temporary为了解决查询，MySQL需要创建一个临时表来容纳结果。(比如两张表，先查出一张表，然后根据第二张表的字段进行排序)

1)Simple Nested-Loops(简单嵌套循环联接) Join算法相当简单、直接。即外表(驱动表)中的每一条记录与内表(被驱动表)中的记录进行比较判断。对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左(外)连接的驱动表就是左边的那个表，右(外)连接的驱动表就是右边的那个表。

内表(被驱动表全表扫描)，管理列不包含索引

For each row r in R do -- 扫描R表(驱动表)

For each row s in S do -- 扫描S表(被驱动表) ---扫描的是表，一条条数据比较

If r and s satisfy the join condition -- 如果r和s满足join条件

Then output the tuple -- 返回结果集

2)Index Nested-Loops Join(INLJ，基于索引的嵌套循环联接)Join的优化时候，通常都会建议在内表建立索引，以此降低Nested-Loop Join算法的开销，减少内表扫描次数，MySQL数据库中使用较多的就是这种算法，以下称为INLJ。来看这种算法的伪代码：

For each row r in R do -- 扫描R表

lookup s in S index -- 查询S表的索引(固定3~4次IO，B+树高度) --根据r,进行二分查找(索引多路查找，不再需要一条条记录进行比较)扫描的是索引

If find s == r -- 如果r匹配了索引s

Then output the tuple -- 返回结果集

3)Block Nested-Loops Join(BNL，基于块的嵌套循环联接)扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用Simple Nested-Loop Join算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。这也就是Block Nested-Loop Join算法的思想。(在内存中多条进行比较 )

也就是说在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，因为那个算法太粗暴，不忍直视。数据量大些的复杂SQL估计几年都可能跑不出结果。而Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。其使用Join Buffer(联接缓冲)来减少内部循环读取表的次数。

For each tuple r in R do -- 扫描外表R

store used columns as p from R in Join Buffer -- 将部分或者全部R的记录保存到Join Buffer中，记为p

For each tuple s in S do -- 扫描内表S ---扫描的是表，一条条数据，与JoinBuffer数据比较(注意与simple Nested——loop比较)

If p and s satisfy the join condition -- p与s满足join条件

Then output the tuple -- 返回为结果集

4)问题：真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表，为什么

1：可以减少带宽

2：如果查询的列，正好满足索引覆盖，则不会用回表查询