索引与优化

索引与优化技术分享部门：技术部姓名：程彦苹花名：卡罗拉时间：2016.7.14选择索引的数据类型1B-Tree索引2HASH索引3全文索引4聚族索引5选择索引的数据类型1对于任何DBMS，索引都是进行优化的最主要的因素。选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。(3)尽量避免NULL：应该指定列为NOTNULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。选择标识符2选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。(1)整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。(2)字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。索引类型2索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。广泛的说索引分三类：index----普通的索引,数据可以重复unique----唯一索引,唯一索引,要求所有记录都唯一primarykey----主键索引,也就是在唯一索引的基础上相应的列必须为主键创建索引2–创建表的时候同时创建索引CREATETABLE`table`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,`time`int(10)NULLDEFAULTNULL,PRIMARYKEY(`id`),INDEXindex_name(title(length)))–直接创建索引CREATEINDEXindex_nameONtable(column(length))–修改表结构的方式添加索引ALTERTABLEtable_nameADDINDEXindex_nameON(column(length))–删除索引DROPINDEXindex_nameONtableB-Tree索引2索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀来进行查询。(1)匹配全值：对索引中的所有列都指定具体的值。(2)匹配最左前缀：仅仅使用索引中的第1列。(3)匹配列前缀：例如，你可以利用索引查找lastname以J开始的人，这仅仅使用索引中的第1列。(4)匹配值的范围查询：可以利用索引查找lastname在Allen和Barrymore之间的人，仅仅使用索引中第1列。B-tree索引2(5)匹配部分精确而其它部分进行范围匹配：可以利用索引查找lastname为Allen，而firstname以字母K开始的人。(6)仅对索引进行查询(Index-onlyqueries)：如果查询的列都位于索引中，则不需要读取元组的值。由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDERBY。当然，使用B-tree索引有以下一些限制：(1)查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。(2)不能跳过某一索引列。例如，你不能利用索引查找lastname为Smith且出生于某一天的人。(3)存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERElast_name=SmithANDfirst_nameLIKE'J%'ANDdob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。HASH索引2MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型。其实，是一种（key=value）形式的键值对，hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像btree(B-Tree)索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以hash索引的查询效率要远高于btree(B-Tree)索引。Hash索引有以下一些限制：(1)由于索引仅包含hashcode和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。(2)不能使用hash索引排序。(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。(4)Hash索引只支持等值比较，例如使用=，IN()和=。对于WHEREprice100并不能加速查询。全文索引2全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。目前只有MyISAM引擎支持。其可以在CREATETABLE，ALTERTABLE，CREATEINDEX使用，不过目前只有CHAR、VARCHAR，TEXT列上可以创建全文索引。它的出现是为了解决WHEREnameLIKE“%word%”这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间。聚族索引非聚族索引2聚族索引的组织顺序和数据本身的组织顺序是一致。mysql的聚簇索引是指innodb引擎的特性，mysiam并没有，如果需要该索引，只要将索引指定为主键（primarykey）就可以了。聚簇索引：MySQLInnoDB一定会建立聚簇索引，把实际数据行和相关的键值保存在一块，这也决定了一个表只能有一个聚簇索引，即MySQL不会一次把数据行保存在二个地方。1)InnoDB通常根据主键值(primarykey)进行聚簇2)如果没有创建主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引3)上面二个条件都不满足，InnoDB会自己创建一个虚拟的聚集索引。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。另外，二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据。非聚族索引2非聚族索引就好比字典里的偏旁、笔画索引，其索引组织顺序和数据组织顺序不一致，因此非聚族索引可以创建多个。当查找一条数据时，非聚族索引和聚族索引的效率相差不大，但查找一批数据(n)时，非聚族索引需要的io可能是聚族索引的n倍，因为非聚族索引需要一条一条地进行查找。使用索引的注意事项2使用索引时，有以下一些技巧和注意事项：1.索引不会包含有NULL值的列只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。2.使用短索引对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。使用索引的注意事项23.索引列排序MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么orderby中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。4.like语句操作一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%aaa%”不会使用索引而like“aaa%”可以使用索引。使用索引的注意事项25.不要在列上进行运算代码如下:select*fromuserswhereYEAR(adddate)2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:代码如下:select*fromuserswhereadddate‘2007-01-01';6.不使用NOTIN和操作