《数据库索引》PPT课件.pptx

《《数据库索引》PPT课件.pptx》由会员分享，可在线阅读，更多相关《《数据库索引》PPT课件.pptx（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MySql数据库索引索引基础 索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用与否。为了满足应用的高效数据查询需要，选择合适的表类型以及数据类型也至关重要。建表-表类型MySQL中表的类型表现为不同的存储引擎，同一种存储引擎在不同的应用中表现出的性能差异也是不容忽略的重要因素。因此，在建表之初应当正确选择表的类型。MyISAM和InnoDB两种使用比较多的存储引擎，MyISAM类型(不支持事务，不支持外键，对访问完整性无要求）在select操作多的应用中优势明显；InnoDB在insert、update操作多的应用中优势明显。索

2、引基础建表-数据类型高效的查询也要求在合适的表字段上使用好的索引策略，所以创建表的字段类型选择对于快速查询也有很重要的影响。选择数据类型来帮助提高查询运行速度。把数据列定义成不能为空（NOT NULL）。这会使处理速度更快，需要的存储更少。它有时候还简化了查询，因为在某些情况下不需要检查值的NULL属性。考虑使用ENUM数据列。如果某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值。索引基础 以下是几种常用数据类型在MySQL中的性能顺序（从高到低）INT CHAR VARCHAR TEXTENUM介于INT与

3、CHAR性能行之间；在相同数据类型的情况下，长度短的性能更高，因此在建表时要充分考虑每个字段最合适的类型及长度。索引类型 B-Tree索引一般来说，MySQL 中的 B-Tree 索引的物理文件大多是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的Leaf Node，而且到任何一个 Leaf Node的最短路径的长度都是完全相同的，所以把它称之为 B-Tree 索引。不过，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 InnoDB 存储引擎的 B-Tree 索引使用的存储结构实际上是

4、B+Tree，在 B-Tree 数据结构的基础上做了很小的改造，在每一个 Leaf Node 上面除了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 Leaf Node 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率。索引类型在 InnoDB 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（Primary Key），另外一种则是和其他存储引擎（如MyISAM存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 InnoDB 存储引擎中被称为 Secondary Index。索引分类主键索引与第二索引索引

5、类型使用B-Tree索引的查询类型B-Tree索引能够很好的用于全键值，键值范围以及键前缀的索引查找，它只有在查找时候使用了最左前缀的时候还有用，有效的查询类型如下：匹配全名：全键值匹配指和索引中的所有列匹配。匹配最左前缀：按照索引顺序，只匹配前几列。匹配列前缀：按照这种索引顺序，只匹配索引第一列中以某字符串开头的所有结果。匹配范围值：可以查找大于某一个值且小于某一个值的集合，但是只能用于第一列。索引类型精确匹配一部分，并且部分匹配另一部分：查找索引前几个字段精确匹配，后面一个字段的模糊匹配。只访问索引的查询：B-Tree索引通常能支持只访问索引的查询，它不需要访问数据行（覆盖索引）。上面的查

6、找方式适用于查找，同样也适用于order by排序。B-Tree索引的局限：1.没有从索引列的最左边开始将不起作用。2.不能跳过索引中的列，不然只有索引前一部分的索引生效。3.不能优化第一个范围条件右边的列。索引类型哈希索引 对于每一行，存储引擎根据被索引的列计算出哈希码，它是一个很小的值，并且有可能和其它行的哈希码不同，它把哈希码保存到索引中，并且保存了一个哈希表中每一行的一个指针。在哈希索引的数据结构中，索引的哈希值是有序的，但是指针指向的数据行不是有序的。哈希索引哈希值的长度不会依赖于索引列，TINNYINT的索引和大型字符串的索引大小是一样的。所以查找速度还是很快的。但是也有一些缺点：

7、1.只存取了哈希码和指向行的指针，不是值本身，因此不能避免读取行。2.哈希索引不能进行排序，因为它不会按序保存行。3.不支持索引的部分匹配，因为哈希码是所有索引列计算出来的。4.碰撞率很高时，索引维护操作很慢，因为不得读取每一行进行匹配。索引类型 Innodb存储引擎可实现自适应的哈希索引，当Innodb注意到一些索引值被频繁访问的时候，它会在B-Tree顶端建立起内存中的索引。这使它能快速的哈希查找。这个过程是全自动的，不能控制也不能配置。空间索引：使用地理空间数据类型，不要求where语句使用最左前缀，全方位的索引数据查找。全文索引：它主要是从文本中查找关键字不是直接和索引中的值比较。它是

8、基于数据关联性的搜索。索引高效使用隔离列 如果你在一个查询中不隔离已经索引的列，一般来说MySQL就不会使用这个列上的索引，除非在查询中隔离这个列。“隔离”列的意思就是，这个列不能是一个表达式的一部分或者被放在一个函数的内部。例：SELECT actor_id FROM sakila.actor WHERE actor_id+1=5;前缀索引和索引选择性对很长的varchar列，BLOG,TEXT使用索引，就会使索引变大且使查询很慢，可以只索引开始的几个字符，使索引空间减少。但是前缀索引可能带来索引选择性比较低。需要对真实的数据，逐一递增索引列的长度，直到选择出的选择性与全部索引选择性相近时，

9、使用此前缀长度就可以。而对于数据不均的情况下，不能看平均情况，看到达到某一前缀长度，选择率不会明显升高时，使用选取的索引前缀长度即可。索引高效使用聚集索引它不是一种单独的索引类型，而是一种存储数据方式。Innodb聚集索引实际上是在同样的数据结构中保存了索引和数据行。当表有聚集索引时候，数据行是保存在索引的叶子节点中。但节点页只包含被索引的列。MySql存储引擎目前不能选择聚集的列，Innodb目前是按照主键进行聚集。如果没有主键，Innodb会使用非空的唯一索引，否则会构造隐藏的主键。聚集索引优点：1.可以将相关数据保存在一起，按照某一字段聚集，可以只提取几个页面就获取所有需要的信息。2.数

10、据访问快，因为索引和数据都保存在同一颗B-Tree中。聚集索引缺点：1.维护聚集索引时昂贵的，插入速度严重依赖插入数据顺序，更新聚集索引时候，强制更新行到新的位置。索引高效使用2.建立在聚集索引上的表在插入新行，而行被移动到的数据页已经满了的情况，就会分页，分页导致需要分配更多的存储空间。3.聚集表可能比全表扫描慢，尤其当表数据存储比较稀疏或者因为分页等造成数据的多次随机读取。4.第二索引（非聚集）索引需要两次索引查找，因为第二索引叶子节点包含了被引用行的主键列。下图展示了聚集索引（Innodb）与非聚集索引（MyISAM)的索引布局：索引高效使用2.在聚集索引的表中插入新行，当行必须被移动的

11、时候，会进行分页。分页会占用更多的存储空间。3.第二索引会变大，因为它们叶子节点要包含被引用行的主键列。这样第二索引的查找需要两次，首先找到引用行的主键列，然后查找行数据。索引高效使用覆盖索引：索引是找到高效的行，但如果索引叶子节点包含了索引的数据，就不需要读取行，这样能够满足查询需要数据的索引，叫做覆盖索引。覆盖索引的优点：1.索引通常都远小于全行大小，只读取索引，可以减少数据的访问量。2.索引时按照索引值有序的，要比从磁盘中随机读取某一行的速度要快。3.覆盖索引对于Innodb特别有用，Innodb聚集缓存，Innodb在第二索引叶子节点上保存了行的主键值，因此覆盖了查询的第二索引在主键中

12、避免了另外一次索引查找。Innodb在叶子节点保存了主键值的第二索引，也就意味着Innodb第二索引获取覆盖查询的额外列主键列。索引高效使用如下在innodb上查询，在last_name上有索引，即使actor_id列不是该索引一部分，但是也可以覆盖取得主键actor_id的查询。Select actor_id,last_name from actor where last_name=zhang;排序索引扫描：MySql产生排序结果有两种方式，一种是文件排序，另外一种是扫描索引。使用EXPLAIN输出type值为index，表明MySql会扫描索引。注意：1.如果没有使用索引覆盖查询，就不得查

13、找在索引中发现的每一行，这是随机I/O的，这要比顺序扫描表慢的多。2.按照索引进行排序，当索引的顺序和oder by字句字段的顺序一样时，并且排序方向一致时，才能使用索引排序。排序使用索引一样要使用最左前缀。3.order by不使用最左前缀规则一种情况是前导列为常量。如下查询：查看解释器不会看到filesort。索引高效使用rental表在（rental_date,inventory_id,customer_id)上有索引，查询语句：select rental_id,staff_id from rental where rental_date=2012-05-25 order by inve

14、ntory_id,customer_id;多余和重复索引Mysql允许在同一个列上创建多个索引，但是Mysql不得单独维护每一个索引，查询优化器在查询的时候会逐个考虑它们，这样会影响性能。重复索引是指类型相同，以同样顺序在同样列上创建的索引。应避免重复索引，并将多余的重复索引删除。如下创建表语句：Create table testID INT NOT NULL PRIMARY KEY,UNIQUE(ID),INDEX(ID);索引高效使用多余索引和重复索引是不同的，比如已有（a,b)上的索引，而在列（a)上的索引就是多余的，因为（a,b)索引能当成（a）上索引使用。但是（b)上的索引不是多余的

15、，因为最左前缀规则。注意项：1.多余索引发生在已有索引基础上，添加索引，导致如（a)上有索引，又添加一个（a,b）的单独索引。这种情况，应该是扩展已有索引而不是新增。2.有的情况下，处于性能考虑，需要多余索引，比如在一个整数列上有索引，将把它扩展到一个很长的varchara列时，原来只使用第一个整数列的索引会变得很慢。示例:1.select count(*)from userinfo where state_id=5;索引高效使用而当有查询2：select state_id,city,address from userinfo where state_id=5;查询效率会很低，解决办法是将索引扩展到（state_id,city,address),第二个查询快了很多，但是第一个查询却很慢。增加索引（state_id,city,address)会发现两个查询都有很高的效率。3.多余索引会带来索引维护开销，在插入，更新，删除数据的时候会有性能影响。