网站开发设计文档模板,.电子商务网站规划,手机兼职app,中国建设网站写在前面#xff1a;索引对查询的速度有着至关重要的影响#xff0c;理解索引也是进行数据库性能调优的起点。考虑如下情况#xff0c;假设数据库中一个表有10^6条记录#xff0c;DBMS的页面大小为4K#xff0c;并存储100条记录。如果没有索引#xff0c;查询将对整个表进…写在前面索引对查询的速度有着至关重要的影响理解索引也是进行数据库性能调优的起点。考虑如下情况假设数据库中一个表有10^6条记录DBMS的页面大小为4K并存储100条记录。如果没有索引查询将对整个表进行扫描最坏的情况下如果所有数据页都不在内存需要读取10^4个页面如果这10^4个页面在磁盘上随机分布需要进行10^4次I/O假设磁盘每次I/O时间为10ms(忽略数据传输时间)则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引则只需要进行log100(10^6)3次页面读取最坏情况下耗时30ms。这就是索引带来的效果很多时候当你的应用程序进行SQL查询速度很慢时应该想想是否可以建索引。进入正题 第二章、索引与优化 1、选择索引的数据类型 MySQL支持很多数据类型选择合适的数据类型存储数据对性能有很大的影响。通常来说可以遵循以下一些指导原则 (1)越小的数据类型通常更好越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间处理起来更快。(2)简单的数据类型更好整型数据比起字符处理开销更小因为字符串的比较更复杂。在MySQL中应该用内置的日期和时间数据类型而不是用字符串来存储时间以及用整型数据类型存储IP地址。(3)尽量避免NULL应该指定列为NOT NULL除非你想存储NULL。在MySQL中含有空值的列很难进行查询优化因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。1.1、选择标识符选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型应该保证所有相关的表都使用相同的数据类型。(1) 整型通常是作为标识符的最好选择因为可以更快的处理而且可以设置为AUTO_INCREMENT。 (2) 字符串尽量避免使用字符串作为标识符它们消耗更好的空间处理起来也较慢。而且通常来说字符串都是随机的所以它们在索引中的位置也是随机的这会导致页面分裂、随机访问磁盘聚簇索引分裂对于使用聚簇索引的存储引擎。 2、索引入门对于任何DBMS索引都是进行优化的最主要的因素。对于少量的数据没有合适的索引影响不是很大但是当随着数据量的增加性能会急剧下降。如果对多列进行索引(组合索引)列的顺序非常重要MySQL仅能对索引最左边的前缀进行有效的查找。例如假设存在组合索引it1c1c2(c1,c2)查询语句select * from t1 where c11 and c22能够使用该索引。查询语句select * from t1 where c11也能够使用该索引。但是查询语句select * from t1 where c22不能够使用该索引因为没有组合索引的引导列即要想使用c2列进行查找必需出现c1等于某值。2.1、索引的类型索引是在存储引擎中实现的而不是在服务器层中实现的。所以每种存储引擎的索引都不一定完全相同并不是所有的存储引擎都支持所有的索引类型。2.1.1、B-Tree索引假设有如下一个表 CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum(m, f) not null, key(last_name, first_name, dob) ); 其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下 索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询当然如果想使用索引你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。(1)匹配全值(Match the full value)对索引中的所有列都指定具体的值。例如上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。(2)匹配最左前缀(Match a leftmost prefix)你可以利用索引查找last name为Allen的人仅仅使用索引中的第1列。(3)匹配列前缀(Match a column prefix)例如你可以利用索引查找last name以J开始的人这仅仅使用索引中的第1列。(4)匹配值的范围查询(Match a range of values)可以利用索引查找last name在Allen和Barrymore之间的人仅仅使用索引中第1列。(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)可以利用索引查找last name为Allen而first name以字母K开始的人。(6)仅对索引进行查询(Index-only queries)如果查询的列都位于索引中则不需要读取元组的值。由于B-树中的节点都是顺序存储的所以可以利用索引进行查找(找某些值)也可以对查询结果进行ORDER BY。当然使用B-tree索引有以下一些限制(1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。(2) 不能跳过某一索引列。例如你不能利用索引查找last name为Smith且出生于某一天的人。(3) 存储引擎不能使用索引中范围条件右边的列。例如如果你的查询语句为WHERE last_nameSmith AND first_name LIKE J% AND dob1976-12-23则该查询只会使用索引中的前两列因为LIKE是范围查询。 2.1.2、Hash索引MySQL中只有Memory存储引擎显示支持hash索引是Memory表的默认索引类型尽管Memory表也可以使用B-Tree索引。Memory存储引擎支持非唯一hash索引这在数据库领域是罕见的如果多个值有相同的hash code索引把它们的行指针用链表保存到同一个hash表项中。假设创建如下一个表CREATE TABLE testhash ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, KEY USING HASH(fname)) ENGINEMEMORY;包含的数据如下 假设索引使用hash函数f( )如下 f(Arjen) 2323 f(Baron) 7437 f(Peter) 8784 f(Vadim) 2458 此时索引的结构大概如下 Slots是有序的但是记录不是有序的。当你执行mysql SELECT lname FROM testhash WHERE fnamePeter;MySQL会计算’Peter’的hash值然后通过它来查询索引的行指针。因为f(Peter) 8784MySQL会在索引中查找8784得到指向记录3的指针。因为索引自己仅仅存储很短的值所以索引非常紧凑。Hash值不取决于列的数据类型一个TINYINT列的索引与一个长字符串列的索引一样大。 Hash索引有以下一些限制(1)由于索引仅包含hash code和记录指针所以MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的不会对性造成太大的影响。(2)不能使用hash索引排序。(3)Hash索引不支持键的部分匹配因为是通过整个索引值来计算hash值的。(4)Hash索引只支持等值比较例如使用IN( )和。对于WHERE price100并不能加速查询。2.1.3、空间(R-Tree)索引MyISAM支持空间索引主要用于地理空间数据类型例如GEOMETRY。2.1.4、全文(Full-text)索引全文索引是MyISAM的一个特殊索引类型主要用于全文检索。 3、高性能的索引策略3.1、聚簇索引(Clustered Indexes)聚簇索引保证关键字的值相近的元组存储的物理位置也相同所以字符串类型不宜建立聚簇索引特别是随机字符串会使得系统进行大量的移动操作且一个表只能有一个聚簇索引。因为由存储引擎实现索引所以并不是所有的引擎都支持聚簇索引。目前只有solidDB和InnoDB支持。聚簇索引的结构大致如下 注叶子页面包含完整的元组而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引InnoDB会定义一个隐藏的主键然后对其建立聚簇索引。一般来说DBMS都会以聚簇索引的形式来存储实际的数据它是其它二级索引的基础。 3.1.1、InnoDB和MyISAM的数据布局的比较为了更加理解聚簇索引和非聚簇索引或者primary索引和second索引(MyISAM不支持聚簇索引)来比较一下InnoDB和MyISAM的数据布局对于如下表 CREATE TABLE layout_test ( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) ); 假设主键的值位于1---10,000之间且按随机顺序插入然后用OPTIMIZE TABLE进行优化。col2随机赋予1---100之间的值所以会存在许多重复的值。(1) MyISAM的数据布局其布局十分简单MyISAM按照插入的顺序在磁盘上存储数据如下 注左边为行号(row number)从0开始。因为元组的大小固定所以MyISAM可以很容易的从表的开始位置找到某一字节的位置。据些建立的primary key的索引结构大致如下 注MyISAM不支持聚簇索引索引中每一个叶子节点仅仅包含行号(row number)且叶子节点按照col1的顺序存储。来看看col2的索引结构 实际上在MyISAM中primary key和其它索引没有什么区别。Primary key仅仅只是一个叫做PRIMARY的唯一非空的索引而已。(2) InnoDB的数据布局InnoDB按聚簇索引的形式存储数据所以它的数据布局有着很大的不同。它存储表的结构大致如下 注聚簇索引中的每个叶子节点包含primary key的值事务ID和回滚指针(rollback pointer)——用于事务和MVCC和余下的列(如col2)。相对于MyISAM二级索引与聚簇索引有很大的不同。InnoDB的二级索引的叶子包含primary key的值而不是行指针(row pointers)这减小了移动数据或者数据页面分裂时维护二级索引的开销因为InnoDB不需要更新索引的行指针。其结构大致如下 聚簇索引和非聚簇索引表的对比 3.1.2、按primary key的顺序插入行(InnoDB) 如果你用InnoDB而且不需要特殊的聚簇索引一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个AUTO_INCREMENT的列这会保证记录按照顺序插入而且能提高使用primary key进行连接的查询的性能。应该尽量避免随机的聚簇主键例如字符串主键就是一个不好的选择它使得插入操作变得随机。 3.2、覆盖索引(Covering Indexes)如果索引包含满足查询的所有数据就称为覆盖索引。覆盖索引是一种非常强大的工具能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点(1)索引项通常比记录要小所以MySQL访问更少的数据(2)索引都按值的大小顺序存储相对于随机访问记录需要更少的I/O(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。(4)覆盖索引对于InnoDB表尤其有用因为InnoDB使用聚集索引组织数据如果二级索引中包含查询所需的数据就不再需要在聚集索引中查找了。覆盖索引不能是任何索引只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。对于索引覆盖查询(index-covered query)使用EXPLAIN时可以在Extra一列中看到“Using index”。例如在sakila的inventory表中有一个组合索引(store_id,film_id)对于只需要访问这两列的查询MySQL就可以使用索引如下 mysql EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: inventory type: index possible_keys: NULL key: idx_store_id_film_id key_len: 3 ref: NULL rows: 5007 Extra: Using index 1 row in set (0.17 sec) 在大多数引擎中只有当查询语句所访问的列是索引的一部分时索引才会覆盖。但是InnoDB不限于此InnoDB的二级索引在叶子节点中存储了primary key的值。因此sakila.actor表使用InnoDB而且对于是last_name上有索引所以索引能覆盖那些访问actor_id的查询如 mysql EXPLAIN SELECT actor_id, last_name - FROM sakila.actor WHERE last_name HOPPER\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ref possible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 2 Extra: Using where; Using index 3.3、利用索引进行排序MySQL中有两种方式生成有序结果集一是使用filesort二是按索引顺序扫描。利用索引进行排序操作是非常快的而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时可以使用索引来排序。如果查询是连接多个表仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。 create table actor( actor_id int unsigned NOT NULL AUTO_INCREMENT, name varchar(16) NOT NULL DEFAULT , password varchar(16) NOT NULL DEFAULT , PRIMARY KEY(actor_id), KEY (name) ) ENGINEInnoDB insert into actor(name,password) values(cat01,1234567); insert into actor(name,password) values(cat02,1234567); insert into actor(name,password) values(ddddd,1234567); insert into actor(name,password) values(aaaaa,1234567); mysql explain select actor_id from actor order by actor_id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using index 1 row in set (0.00 sec) mysql explain select actor_id from actor order by password \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using filesort 1 row in set (0.00 sec) mysql explain select actor_id from actor order by name \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: NULL key: name key_len: 18 ref: NULL rows: 4 Extra: Using index 1 row in set (0.00 sec) 当MySQL不能使用索引进行排序时就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序如果内存装载不下它会将磁盘上的数据进行分块再对各个数据块进行排序然后将各个块合并成有序的结果集实际上就是外排序。对于filesortMySQL有两种排序算法。(1)两遍扫描算法(Two passes)实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出然后在设定的内存通过参数sort_buffer_size设定中进行排序完成排序之后再次通过行指针信息取出所需的Columns。注该算法是4.1之前采用的算法它需要两次访问数据尤其是第二次读取操作会导致大量的随机I/O操作。另一方面内存开销较小。(3) 一次扫描算法(single pass)该算法一次性将所需的Columns全部取出在内存中排序后直接将结果输出。注从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数效率较高但是内存开销也较大。如果我们将并不需要的Columns也取出来就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时MySQL 就会选择使用第一种排序算法反之则会选择第二种。为了尽可能地提高排序性能我们自然更希望使用第二种排序算法所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。当对连接操作进行排序时如果ORDER BY仅仅引用第一个表的列MySQL对该表进行filesort操作然后进行连接处理此时EXPLAIN输出“Using filesort”否则MySQL必须将查询的结果集生成一个临时表在连接完成之后进行filesort操作此时EXPLAIN输出“Using temporary;Using filesort”。 3.4、索引与加锁索引对于InnoDB非常重要因为它可以让查询锁更少的元组。这点十分重要因为MySQL 5.0中InnoDB直到事务提交时才会解锁。有两个方面的原因首先即使InnoDB行级锁的开销非常高效内存开销也较小但不管怎么样还是存在开销。其次对不需要的元组的加锁会增加锁的开销降低并发性。InnoDB仅对需要访问的元组加锁而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做即达不到过滤的目的MySQL服务器只能对InnoDB返回的数据进行WHERE操作此时已经无法避免对那些元组加锁了InnoDB已经锁住那些元组服务器无法解锁了。来看个例子 create table actor( actor_id int unsigned NOT NULL AUTO_INCREMENT, name varchar(16) NOT NULL DEFAULT , password varchar(16) NOT NULL DEFAULT , PRIMARY KEY(actor_id), KEY (name) ) ENGINEInnoDB insert into actor(name,password) values(cat01,1234567); insert into actor(name,password) values(cat02,1234567); insert into actor(name,password) values(ddddd,1234567); insert into actor(name,password) values(aaaaa,1234567); SET AUTOCOMMIT0; BEGIN; SELECT actor_id FROM actor WHERE actor_id 4 AND actor_id 1 FOR UPDATE; 该查询仅仅返回2---3的数据实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询而没有进行过滤操作WHERE中第二个条件已经无法使用索引了 mysql EXPLAIN SELECT actor_id FROM test.actor - WHERE actor_id 4 AND actor_id 1 FOR UPDATE \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec) mysql 表明存储引擎从索引的起始处开始获取所有的行直到actor_id4为假服务器无法告诉InnoDB去掉元组1。为了证明row 1已经被锁住我们另外建一个连接执行如下操作 SET AUTOCOMMIT0; BEGIN; SELECT actor_id FROM actor WHERE actor_id 1 FOR UPDATE; 该查询会被挂起直到第一个连接的事务提交释放锁时才会执行这种行为对于基于语句的复制(statement-based replication)是必要的。如上所示当使用索引时InnoDB会锁住它不需要的元组。更糟糕的是如果查询不能使用索引MySQL会进行全表扫描并锁住每一个元组不管是否真正需要。 转载于:https://www.cnblogs.com/williamjie/p/9485281.html
