绝无经由的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，下同。

　　◆修正表布局

　　ALTERmytableADDINDEX[indexName]ON(username(length))

　　◆创立表的时分间接指定

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

　　删除索引的语法：

　　DROPINDEX[indexName]ONmytable;

　　(2)独一索引

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

　　◆创立索引

　　CREATEUNIQUEINDEXindexNameONmytable(username(length))

　　◆修正表布局

　　ALTERmytableADDUNIQUE[indexName]ON(username(length))

　　◆创立表的时分间接指定

　　CREATETABLEmytable(IDINTNOTNULL,usernameVARCHAR(16)NOTNULL,UNIQUE[indexName](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对于财务部门来说更具吸引力。

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

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

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

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

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

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

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。