mysql索引(sql入门新手教程)

admin 2023年12月14日 04:28 157 0

各位老铁们，大家好，今天由我来为大家分享mysql索引，以及sql入门新手教程的相关问题知识，希望对大家有所帮助。如果可以帮助到大家，还望关注收藏下本站，您的支持是我们最大的动力，谢谢大家了哈，下面我们开始吧！

一、MySQL怎么使用索引

MySQL如何使用索引???给定特定的列的值查找满足条件的行，索引的使用能够加快查找的速度。如果没有索引，MySQL将从第一行记录开始，穿越整个表找到相应的记录，表越大，相应的查询的代价也就越大。如果针对查询中的列有索引，MySQL就能在数据文件中快速确

???给定特定的列的值查找满足条件的行，索引的使用能够加快查找的速度。如果没有索引，MySQL将从第一行记录开始，穿越整个表找到相应的记录，表越大，相应的查询的代价也就越大。如果针对查询中的列有索引，MySQL就能在数据文件中快速确定需要查找的位置，再也不用穿越整个表来捞数据了。如果一个表有1000条数据，这样至少能比整表顺序读取捞数据快100倍。如果你的查询结果包含了整表的大部分记录，它也比没有索引整表捞数据要快，至少减少了磁盘的寻址时间。

??大部分的MySQL索引(PRIMARY KEY, UNIQUE, INDEX, FULLTEXT)都是以B-Tree结构来存储，而空间数据索引则使用R-Tree结构来存储，内存表则使用哈希索引。

??字符串在创建索引时会自动去除首尾的空白。

?? MySQL会在以下操作时使用索引：

快速查找匹配where语句的行记录时。

预计能够缩小结果的范围时。如果查询能够匹配多个索引，MySQL一般会使用能够过滤出结果最少的索引。

join操作时从其他表捞数据。在join时，如果声明关联的列类型和大小相同，MySQL在使用索引时能够更加高效。在这里，如果VARCHAR和CHAR的大小相同，他们在类型上会被认为是相同的。例如VARCHAR(10)和CHAR(10)是大小相同的，但是VARCHAR(10)和CHAR(15)的大小是不同的。

????在两个不同的列之间进行比较，例如string和temporal，或者numeric，不能方便直接进行比较，将妨碍?

????索引的使用。假设一个numeric列和一个string列进行比较，对于numeric列中给定的一个值，比如1，它可能会和

?????string中的很多值相同，例如：'1',' 1','00001',或者'01.e1'。string列上的任何索引对这种查询没有任何意义和帮助。

获取已有索引列的MIN()、MAX()值。在执行这两个聚合函数的时候，预处理过程会在使用该列的索引之前会首先检查where语句中是否包含有其他索引列的等于限定条件，并从该索引中分别查询一次MIN和MAX，并将获取到的常量值返回，整个查询将一次返回，而不用做原始列的全索引扫描。例如：在已有索引的列column1上获取其MIN、MAX值，如果在where中包含有”column2=常量“，而column2、column1构建有复合索引，这种情况下，MySQL将不会查找column1的索引，而是在column2、column1的复合索引中进行查找，并能一次获取到结果，不用穿越整个索引。

如果在一个已经排序并分组的最左前缀索引上执行sort或者group，例如：ORDER BY key_part1, key_part2，key_part1, key_part2是复合索引的列，如果所有的key都是DESC的，key将会反序读取。

在某些情况下，一个查询通过优化，可以不用通过获取行数据而得到结果。如果一个查询只使用了numeric列，并且这些列参与构建了最左前缀索引，那么MySQL可以直接从索引中获取到需要的结果，而不用访问具体的数据。这也就是所谓的”覆盖索引“，例如：

SELECT key_part3 FROM tbl_name

?key_part1、key_part3属于一个最左前缀索引。假设执行以下的SQL语句：

SELECT* FROM tbl_name WHERE col1=val1 AND col2=val2;

?如果在col1、col2上有一个复合索引，对应的查询结果就能直接获取到。如果在col1、col2只有分别的单列索引，优化器就会尝试使用索引合并进行优化，或者看哪个索引返回的结果集更好，然后根据该结果集去表中读取数据。

?????如果该表有一个多列索引，该索引的任意最左前缀都能被优化器使用。例如，如果在(col1, col2, col3)上有一个三列索引，则基于(col1)、(col1,col2)、(col1,col2,col3)的查询都会使用到该索引。

????如果使用的列不能构成一个最左前缀，MySQL就无法使用索引了，假设有如下的SQL查询：

SELECT* FROM tbl_name WHERE col1=val1;

SELECT* FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT* FROM tbl_name WHERE col2=val2;

SELECT* FROM tbl_name WHERE col2=val2 AND col3=val3;

????如果在(col1, col2, col3)构建索引，那么就只有头两个SQL查询能够使用索引。第三个、第四个查询虽然也使用到了被索引的列，但是(col2)和(col2, col3)不是(col1, col2, col3)的最左前缀。

SELECT* FROM tbl_name WHERE key_col LIKE'Patrick%';

SELECT* FROM tbl_name WHERE key_col LIKE'Pat%_ck%';

????而下面的语句将不会使用索引：

SELECT* FROM tbl_name WHERE key_col LIKE'%Patrick%';

SELECT* FROM tbl_name WHERE key_col LIKE other_col;

????在第一句中，LIKE操作的字符串以通配符开头，而第二句中，LIKE操作的不是一个常量字符串。

????如果使用... LIKE'%string%'，并且string不超过三个字符，MySQL将使用Turbo Boyer-Moore算法来初始化这个字符串模式，然后使用这个模式进行快速查找。

????对于创建了索引的列col_name,如果在where中包含有col_name is NULL，在操作时，MySQL也将使用索引。

????在一个AND组中，必须包含有索引前缀，才能在执行过程中使用索引，下面的WHERE语句将使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

... WHERE index=1 OR A=10 AND index=2

/* optimized like"index_part1='hello'"*/

... WHERE index_part1='hello' AND index_part3=5

/*能在index1上使用索引，不能再index2或者index3上使用索引*/

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

?????下面的WHERE语句无法使用索引：

/* index_part1索引没有被使用*/

... WHERE index_part2=1 AND index_part3=2

... WHERE index_part1=1 OR index_part2=10

????有时候，及时有索引满足条件，MySQL也不会使用它，会发生这种状况的一种情形是MySQL优化器认为使用索引会导致对整表很大一部分数据的访问，在这种情况下，直接的全表扫描可能更快，它花费的寻址时间更少。不过，如果这种查询使用limit限定只返回结果中的部分行，MySQL就会使用索引，这种只返回少量行的操作，通过索引会更快。

二、mySQL的索引功能

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。索引不是万能的，索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程度上弥补这一缺陷，许多 SQL命令都有一个 DELAY_KEY_WRITE项。这个选项的作用是暂时制止 MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但 MySQL把同一个数据表里的索引总数限制为16个。

与 InnoDB数据表相比，在 InnoDB数据表上，索引对 InnoDB数据表的重要性要大得多。在 InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。“数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及 INSERT、UPDATE和 DELETE命令。出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

如果 WHERE子句的查询条件里有不等号（WHERE coloum!=），MySQL将无法使用索引。类似地，如果 WHERE子句的查询条件里使用了函数（WHERE DAY（column）=），MySQL也将无法使用索引。在 JOIN操作中（需要从多个数据表提取数据时），MySQL只有在主键和外键的数据类型相同时才能使用索引。

如果 WHERE子句的查询条件里使用比较操作符 LIKE和 REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是 LIKE'abc%‘，MySQL将使用索引；如果查询条件是 LIKE'%abc’，MySQL将不使用索引。

在 ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。（虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快 ORDER BY方面也没什么作用）。如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。 1．普通索引

普通索引（由关键字 KEY或 INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了 MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY而不是 UNIQUE。

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引。这种索引的特点是 MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A，B，C)可以当做 A或(A,B)的索引来使用，但不能当做 B、C或(B,C)的索引来使用。在为 CHAR和 VARCHAR类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数（这个数字必须小于这个字段所允许的最大字符个数）。这么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。在为 BLOB和 TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最大索引全文索引文本字段上的普通索引只能加快对出现在字段内容最前面的字符串（也就是字段内容开头的字符）进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以的形式出现，这对 MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

这类场合正是全文索引（full-textindex）可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加：

ALTER TABLE tablename ADD FULLTEXT（column1，column2）有了全文索引，就可以用 SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：

WHERE MATCH(column1,column2) AGAINST('word1','word2','word3')

上面这条命令将把 column1和 column2字段里有 word1、word2和 word3的数据记录全部查询出来。

注解：InnoDB数据表不支持全文索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了 1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

在不确定应该在哪些数据列上创建索引的时候，人们从 EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的 SELECT命令加一个 EXPLAIN关键字作为前缀而已。有了这个关键字，MySQL将不是去执行那条 SELECT命令，而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引等信息列出来。

在 EXPLAIN命令的输出结果里，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联关系（JOIN）。在各种类型的关联关系当中，效率最高的是 system，然后依次是 const、eq_ref、ref、range、index和 All（All的意思是：对应于上一级数据表里的每一条记录，这个数据表里的所有记录都必须被读取一遍——这种情况往往可以用一索引来避免）。

possible_keys数据列给出了 MySQL在搜索数据记录时可选用的各个索引。key数据列是 MySQL实际选用的索引，这个索引按字节计算的长度在 key_len数据列里给出。比如说，对于一个 INTEGER数据列的索引，这个字节长度将是4。如果用到了复合索引，在 key_len数据列里还可以看到 MySQL具体使用了它的哪些部分。作为一般规律，key_len数据列里的值越小越好。

ref数据列给出了关联关系中另一个数据表里的数据列的名字。row数据列是 MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。row数据列里的所有数字的乘积可以大致了解这个查询需要处理多少组合。

最后，extra数据列提供了与 JOIN操作有关的更多信息，比如说，如果 MySQL在执行这个查询时必须创建一个临时数据表，就会在 extra列看到 usingtemporary字样。