mysql索引_MySQL索引

矫情吗；* 2023-01-10 05:25 219阅读 0赞

MySQL索引\#

参考：http://blog.csdn.net/xifeijian/article/details/20312557

是存储引擎用于快速找到记录的一种数据结构。

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有\\(10^6\\)条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取\\(10^4\\)个页面，如果这\\(10^4\\)个页面在磁盘上随机分布，需要进行\\(10^4\\)次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立100叉B-Tree索引，则只需要进行\\(log\_\{100\}(10^6)=3\\)次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

对表进行DML(包括INSERT、UPDATE、DELETE)时，必须处理额外的工作量(也就是对索引结构的维护)以及存储方面的开销。所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。

1.索引基础

在MySQL中，存储引擎用一本书的“索引”找到对应页码类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL仅能对索引最左边的前缀进行有效的查找。例如：

假设存在组合索引t1 c1 c2(c1,c2)，查询语句select \* from t1 where c1=1 and c2=2能够使用该索引。查询语句select \* from t1 where c1=1也能够使用该索引。但是，查询语句select \* from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

2.索引与优化

选择索引的数据类型

(1)小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

主键的选择

原则：

1、主键不应具有任何实际意义，因为任何有业务含义的列都有改变的可能性。假如关键字具有了业务意义，当用户决定改变业务含义，也许他们想要为关键字增加几位数字或把数字改为字母，那么就必须修改相关的关键字。一个表中的主关键字有可能被其他表作为外键。就算是一个简单的改变，譬如在客户号码上增加一位数字，也可能会造成极大的维护上的开销。

2、永远也不要更新主键。因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明第一条原则被违反了。注：这项原则对于那些经常需要在数据转换或多数据库合并时进行数据整理的数据并不适用。

3、主键应当有计算机自动生成。如果由人来对主键的创建进行干预，就会使它带有除了惟一标识一行以外的意义。

常见的数据库主键选取方式有：

1、自动增长字段：

自动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数据库系统会自动为其分配一个值，确保绝对不会出现重复。这是我们设置主键的首选：

innodb 中的主键是聚簇索引，会把相邻主键的数据放在相邻的物理存储位置上。如果主键不是自增，而是随机的，那么频繁的插入会使 innodb 频繁地移动磁盘块；

在innodb中，别的索引还都要包含主键的值，因此建立索引时占用空间小；

利用数字，更容易比较排序。

缺点：

当我们需要在多个数据库间进行数据的复制时，自动增长型字段可能造成数据合并时的主键冲突。(可以不同的分表分别从不同的起始主键开始自增，比如分表1从1自增，分表2从1000 000自增，在分布式数据中也可以这么处理)

2、使用UniqueIdentifier

比如使用UUID(全局唯一标识符)来作为主键，UUID算法的核心思想是结合机器的网卡、当地时间、一个随机数来生成UUID。从理论上讲，如果一台机器每秒产生10000000个UUID，则可以保证(概率意义上)3240年不重复。

在mysql中有函数生成uuid：SELECT UUID();一般用CHAR(36)类型来存储uuid。本身 UUID 是16字节的，即每个字节byte对应8位二进制数bit，每个16进制对应4位bit。但是我们在MySQL中存储时，是将生成的UUID转化为字符串，字符串的每一位是一个char(mysql中char(1)可以存1个字节)，所以有些说UUID是32字节也没有错。同时 MySQL 生成的 UUID 有四个中划线，所以在 utf8 字符集里，长度为 36 字节，即char(36)。

UUID uuid = UUID.randomUUID();

UUID: 由4个连字号(-)将32个字节长(128位)的字符串分隔后生成的字符串，总共36个字节长。比如：550e8400-e29b-41d4-a716-446655440000 (8-4-4-4-12，32个16进制数对应的字符)

GUID:微软的UUID实现标准，目前应用最为广泛，其格式为：xxxxxxxx-xxxx- xxxx-xxxxxxxxxxxxxxxx(8-4-4-16)

优点：通过程序直接生成GUID填充主键，不用考虑是否会出现重复。

缺点： UUID 的值有32个字节，与其它那些诸如 4 字节的整数相比，存储空间增大；索引时间较慢。

import java.util.UUID;

public class UUIDUtils

/\*\*

\* 获得一个UUID

\* @return String UUID

\*/

public static String getUUID()

return UUID.randomUUID().toString().replaceAll("-", "");

/\*\*

\* 获得指定数目的UUID

\* @param number需要获得的UUID数量

\* @return String\[\] UUID数组

\*/

public static String\[\] getUUID(int number)

if (number < 1)

return null;

String\[\] ss = new String\[number\];

for (int i = 0; i < number; i++)

ss\[i\] = getUUID();

return ss;

public static void main(String\[\] args)

System.out.println("::" + UUIDUtils.getUUID());

String\[\] ss = getUUID(10);

for (int i = 0; i < ss.length; i++)

System.out.println(ss\[i\]);

3、使用“COMB(Combine)”类型

COMB数据类型的基本设计思路是这样的：既然UniqueIdentifier数据因毫无规律可言造成索引效率低下，影响了系统的性能，那么我们能不能通过组合的方式，保留UniqueIdentifier的前10个字节，用后6个字节表示GUID生成的时间(DateTime)，这样我们将时间信息与UniqueIdentifier组合起来，在保留UniqueIdentifier的唯一性的同时增加了有序性，以此来提高索引效率。也许有人会担心UniqueIdentifier减少到10字节会造成数据出现重复，其实不用担心，后6字节的时间精度可以达到1毫秒，时间4095年，两个COMB类型数据完全相同的可能性是在这1毫秒内生成的两个GUID前10个字节完全相同，这几乎是不可能的！注意这16字节转化为16进制再转化为字符串存储时也是32字节。

首先，MySQL时间戳timestamp是采用int存储，4个字节，最多32位，可以从1970年1月1日00:00:00一直到2037年，精度为一秒，其值作为数字显示。

下面说明：6个字节的时间精度问题，6字节共48位

![7b8f1077d42b779f8ff7b15870a47e1c.png][]

那么精度为毫秒，可以用4095年，算上0可以用4096年。

其实可以直接用System.currentTimeMillis()获取当前时间距离1970年1月1日0点0分0秒的毫秒数，然后转化为16进制，可以表示到10889年，

\`import java.util.UUID;

/\*\*

\* @author xie 由于randomUUID()方法生成的uuid除了4个版本位：49-52位，

\* 2个变体位(表示uuid的显示形式，即-在哪个位置，8-4-4-4-12 Java默认的，当然还有其他的形式)：65-66位，

\* 都是为了随机而产生的比如，伪随机数，MAC地址等，我们替换第1-48位，保证主键尽量有序，还不删掉有用信息

\*/

public class Main \{

public static void main(String\[\] args) throws Exception \{

long nowTime = System.currentTimeMillis();

System.out.println(nowTime + "");

String uuid = UUID.randomUUID().toString().replaceAll("-", "");

System.out.println(uuid + "");

String now16 = Long.toHexString(nowTime);

System.out.println(now16 + "");

StringBuffer str = new StringBuffer(uuid);

int length = now16.length();

if (length <= 12) \{

str.replace(0, 12 - length, "0");

System.out.println(str.replace(12 - length, 12, now16).toString());// 这么设定是为了生成的uuid字符串还是32字节

\} else \{

throw new Exception("时间超出10889年，请重新设计主键！");

\}\`

索引的类型

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

2.3.1、B-Tree索引

如果将数据放入磁盘中，由于指令的执行速度远远超过磁盘的读写速度，因此控制运行时间的几乎都是磁盘访问次数。那么写一个复杂的程序来将磁盘访问次数降低到一个很小的常数是很有意义的。

B－Tree：所有的数据项都存储在树叶上，每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。B－Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页(每个叶子页包含多个树叶)到根的距离相同，很适合查找范围数据。( InnoDB使用的是B+Tree)

![4d09b96360b0a81d2671e8ce85c2fbf6.png][]

![b1068833d81b0165e15027a1982491a0.png][]

注意叶子页中的每一个节点，保存了数据的值、指向数据的指针(数据的物理地址，对innodb，由于使用聚簇索引，指定primary key的值即可)和指向下一个节点或者下一个叶子页的链接。

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。由于B+树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。

2.3.2、Hash索引

哈希索引基于哈希表实现。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。哈希索引将所有的哈希存储在索引中，同时在哈希表中保存指向每个数据的指针。

MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当 InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希査找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要， 完全可以关闭该功能。

优点：访问十分迅速，同时Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

缺点：不能使用hash索引排序。Hash索引只支持等值比较。那就很难查询范围和排序。也不能部分匹配，那就不能模糊查询。

2.3.3、空间(R-Tree)索引

MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

2.3.4、全文(Full-text)索引

全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词主要用于全文检索，对中文意义不大。InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index 的方式。全文检索通常使用倒排索引(inverted index)来实现。它在辅助表中存储了单词与单词自身在一个或多个文裆中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

inverted file index，其表现形式为\{单词，单词所在文档的ID\}

full inverted index,其表现形式为\{单词，(单词所在文档的ID,在具体文档中的位置)\}

索引的优点：

最常见的B-Tree索引，按照顺序存储数据，所以MYSQL可以用来做order by和group by操作。因为数据是有序的，所以B-Tree也就会将相关的列值存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

总结下来索引有如下三个优点：

大大减小了需要扫描的数据量

避免排序和临时表

将随机IO变成顺序IO(如聚簇索引)

索引是最好的解决方案吗？

索引并不总是最好的工具。总的来说只有索引帮助存储引擎快速查找到记录的好处大于其带来的额外工作时，索引才是有效的。

对于非常小的表，大部分情况下简单的全表扫描更高效；

对于中到大型的表，索引就非常有效。

但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如使用分区技术。

mysql建索引的几大原则

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。

5．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

6．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.最左前缀匹配原则

当对多个列同时索引时，MySQL会一直向右匹配直到遇到范围查询(>、

聚簇索引(Clustered Indexes)

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作)。具体的细节依赖于其实现方式，InnoDB的聚族索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚族索引时，它的数据行存放在索引的叶子页中。术语“聚族”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚族索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。除聚簇索引之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引(secondary indexes)。

聚簇索引的结构大致如下：

![56c14d96d9f9a3109a7d706927d6499c.png][]

聚簇索引中的每个叶子节点包含primary key的值，事务ID和回滚指针(rollback pointer)——用于事务和MVCC，和余下的列，但是节点页只包含了索引列，即主键。

叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

MyISAM按照插入的顺序在磁盘上存储数据

聚族索引的优点

可以把相关数据保存在一起。例如实现电子邮件时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚族索引，则每封邮件都可能导致一次磁盘I/O；

数据访问更快。聚族索引将索引和数据保存在同一个B-Tree中，因此从聚族索引中获取数据通常比在非聚族索引中查找更快。

缺点：

二级索引叶子节点保存的不是指行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获取对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B－TREE查找而不是一次。

插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。否则在插入新行时，可能需要大量的移动数据行和“页分裂”的问题。

为什么对主键加索引？

在innodb中，由于聚簇索引的顺序就是数据的物理存储顺序，因此我们要尽量使主键有序，方法就是使用COMB，前6个字节表示时间，可以精确到毫秒，后10个字节利用UUID的前10个字节。

由于主键需要唯一性，加了索引可以在插入新数据时快速确定唯一性，不用遍历数据库。

索引与加锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。

这点十分重要，InnoDB直到事务提交时才会解锁。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

Optimize Table

是mysql中一个可以回收更多的空间、减少“碎片”(defragment)的命令。当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排(注意：是磁盘上，而非数据库)。

多数时间并不需要运行OPTIMIZE TABLE，只需在批量删除数据行之后，或定期(每周一次或每月一次)进行一次数据表优化操作即可，只对那些特定的表运行。

分表和分区

参考：http://www.2cto.com/database/201503/380348.html

为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

mysql分表和分区有什么联系呢？

1.都能提高mysql的性高，在高并发状态下都有一个良好的表现。

2.分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式(如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试)，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

3.分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。

4.表分区相对于分表，操作方便，不需要创建子表。

[7b8f1077d42b779f8ff7b15870a47e1c.png]: /images/20221119/ca80739f6e0947a1b5a64ddb44789a75.png
[4d09b96360b0a81d2671e8ce85c2fbf6.png]: /images/20221119/a8fa3c45a9104f04a70ec715a99ce5f4.png
[b1068833d81b0165e15027a1982491a0.png]: /images/20221119/33a2757885894463ba215b11a5673675.png
[56c14d96d9f9a3109a7d706927d6499c.png]: /images/20221119/8f53a1b7c483412383e55caeb7716d7b.png