只想知道 发表于 2015-1-16 20:12:24

绝无经由的MySQL索引范例一览 让MySQL高效运转起来

“通过有能力的DBA的一个简单计划,MySQL可以达到令人难以想像的运行速度,”一位资深DBA如此表示。MySQL中没有多余的功能来拖累CPU或占用内存。本文先容了七种MySQL索引范例。在数据库表中,对字段创建索引能够年夜年夜进步查询速率。经由过程善用这些索引,能够令MySQL的查询和运转加倍高效。  索引是疾速搜刮的关头。MySQL索引的创建关于MySQL的高效运转是很主要的。上面先容几种罕见的MySQL索引范例。

  在数据库表中,对字段创建索引能够年夜年夜进步查询速率。假设我们创立了一个mytable表:

  CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL);

  我们随机向内里拔出了10000笔记录,个中有一条:5555,admin。

  在查找username="admin"的纪录SELECT*FROMmytableWHEREusername=admin;时,假如在username上已创建了索引,MySQL不必任何扫描,即正确可找到该纪录。相反,MySQL会扫描一切纪录,即要查询10000笔记录。

  索引分单列索引和组合索引。单列索引,即一个索引只包括单个列,一个表能够有多个单列索引,但这不是组合索引。组合索引,即一个索包括多个列。

  MySQL索引范例包含:

  (1)一般索引

  这是最基础的索引,它没有任何限定。它有以下几种创立体例:

  ◆创立索引

  CREATEINDEXindexNameONmytable(username(length));

  假如是CHAR,VARCHAR范例,length能够小于字段实践长度;假如是BLOB和TEXT范例,必需指定length,下同。

  ◆修正表布局

  ALTERmytableADDINDEXON(username(length))

  ◆创立表的时分间接指定

  CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,INDEX(username(length)));

  删除索引的语法:

  DROPINDEXONmytable;

  (2)独一索引

  它与后面的一般索引相似,分歧的就是:索引列的值必需独一,但同意有空值。假如是组合索引,则列值的组合必需独一。它有以下几种创立体例:

  ◆创立索引

  CREATEUNIQUEINDEXindexNameONmytable(username(length))

  ◆修正表布局

  ALTERmytableADDUNIQUEON(username(length))

  ◆创立表的时分间接指定

  CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,UNIQUE(username(length)));

  (3)主键索引

  它是一种特别的独一索引,不同意有空值。通常为在建表的时分同时创立主键索引:

  CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,PRIMARYKEY(ID));

  固然也能够用ALTER命令。记着:一个表只能有一个主键。

  (4)组合索引

  为了抽象地对照单列索引和组合索引,为表增加多个字段:

  CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,cityVARCHAR(50)NOTNULL,ageINTNOTNULL);

  为了进一步压迫MySQL的效力,就要思索创建组合索引。就是将name,city,age建到一个索引里:

  ALTERTABLEmytableADDINDEXname_city_age(name(10),city,age);

  建表时,usernname长度为16,这里用10。这是由于一样平常情形下名字的长度不会凌驾10,如许会减速索引查询速率,还会削减索引文件的巨细,进步INSERT的更新速率。

  假如分离在usernname,city,age上创建单列索引,让该表有3个单列索引,查询时和上述的组合索引效力也会年夜纷歧样,远远低于我们的组合索引。固然此时有了三个索引,但MySQL只能用到个中的谁人它以为仿佛是最无效率的单列索引。

  创建如许的组合索引,实际上是相称于分离创建了上面三组组合索引:

  usernname,city,ageusernname,cityusernname

  为何没有city,age如许的组合索引呢?这是由于MySQL组合索引“最左前缀”的了局。复杂的了解就是只从最左面的入手下手组合。并非只需包括这三列的查询城市用到该组合索引,上面的几个SQL就会用到这个组合索引:

  SELECT*FROMmytableWHREEusername="admin"ANDcity="郑州"SELECT*FROMmytableWHREEusername="admin"

  而上面几个则不会用到:

  SELECT*FROMmytableWHREEage=20ANDcity="郑州"SELECT*FROMmytableWHREEcity="郑州"

  (5)创建索引的机会

  到这里我们已学会了创建索引,那末我们必要在甚么情形下创建索引呢?一样平常来讲,在WHERE和JOIN中呈现的列必要创建索引,但也不完整云云,由于MySQL只对<,<=,=,>,>=,BETWEEN,IN,和某些时分的LIKE才会利用索引。比方:

  SELECTt.NameFROMmytabletLEFTJOINmytablemONt.Name=m.usernameWHEREm.age=20ANDm.city=郑州

  此时就必要对city和age创建索引,因为mytable表的userame也呈现在了JOIN子句中,也有对它创建索引的需要。

  方才提到只要某些时分的LIKE才需创建索引。由于在以通配符%和_开首作查询时,MySQL不会利用索引。比方下句会利用索引:

  SELECT*FROMmytableWHEREusernamelikeadmin%

  而下句就不会利用:

  SELECT*FROMmytableWHEREtNamelike%admin

  因而,在利用LIKE时应注重以上的区分。

  (6)索引的不敷的地方

  下面都在说利用索引的优点,但过量的利用索引将会形成滥用。因而索引也会有它的弱点:

  ◆固然索引年夜年夜进步了查询速率,同时却会下降更新表的速率,如对表举行INSERT、UPDATE和DELETE。由于更新表时,MySQL不但要保留数据,还要保留一下索引文件。

  ◆创建索引会占用磁盘空间的索引文件。一样平常情形这个成绩不太严峻,但假如你在一个年夜表上创立了多种组合索引,索引文件的会收缩很快。

  索引只是进步效力的一个要素,假如你的MySQL有年夜数据量的表,就必要花工夫研讨创建最优异的索引,或优化查询语句。

  (7)利用索引的注重事项

  利用索引时,有以下一些技能和注重事项:

  ◆索引不会包括有NULL值的列

  只需列中包括有NULL值都将不会被包括在索引中,复合索引中只需有一列含有NULL值,那末这一列关于此复合索引就是有效的。以是我们在数据库计划时不要让字段的默许值为NULL。

  ◆利用短索引

  对串列举行索引,假如大概应当指定一个前缀长度。比方,假如有一个CHAR(255)的列,假如在前10个或20个字符内,多半值是唯一的,那末就不要对全部列举行索引。短索引不但能够进步查询速率并且能够节俭磁盘空间和I/O操纵。

  ◆索引列排序

  MySQL查询只利用一个索引,因而假如where子句中已利用了索引的话,那末orderby中的列是不会利用索引的。因而数据库默许排序能够切合请求的情形下不要利用排序操纵;只管不要包括多个列的排序,假如必要最好给这些列创立复合索引。

  ◆like语句操纵

  一样平常情形下不勉励利用like操纵,假如非利用不成,怎样利用也是一个成绩。like“%aaa%”不会利用索引而like“aaa%”可使用索引。

  ◆不要在列长进交运算

  select*fromuserswhereYEAR(adddate)<2007;

  将在每一个行长进交运算,这将招致索引生效而举行全表扫描,因而我们能够改成

  select*fromuserswhereadddate<‘2007-01-01’;

  ◆不利用NOTIN和操纵

  以上,就对个中MySQL索引范例举行了先容。
对于现有业务,可以轻松移植到MySQL。当你需要替换掉老的硬件,当你需要削减历史遗留下的老系统的时候,选用MySQL对于财务部门来说更具吸引力。

第二个灵魂 发表于 2015-1-18 18:36:38

习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

山那边是海 发表于 2015-1-26 22:36:23

你觉得我的非分区索引无法对起子分区,你可以提醒我一下呀!没有任何的提醒,直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

再现理想 发表于 2015-2-4 23:34:39

SQL语言是学习所有数据库产品的基础,无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块,如果是做数据库管理(DBA),侧重点应该放在SQLServer的系统管理上.

精灵巫婆 发表于 2015-2-10 23:32:28

原来公司用过MYSQL自己也只是建个表写个SQL

灵魂腐蚀 发表于 2015-3-1 17:31:36

个人感觉没有case直观。而且默认的第三字段(还可能更多)作为groupby字段很容易造成新手的错误。

变相怪杰 发表于 2015-3-17 11:11:41

你可以简单地认为适合的就是好,不适合就是不好。

飘飘悠悠 发表于 2015-3-24 09:35:24

微软对CLR作了大篇幅的宣传,这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜,感觉对象数据库的一些概念可以实现了。
页: [1]
查看完整版本: 绝无经由的MySQL索引范例一览 让MySQL高效运转起来