MYSQL教程之深切MySQL数据库的索引
不可否认，MySQL也是一个很好的关系型数据库，或许在技术上它与其他领先的关系数据库相差并不大，或不具有劣势。但是，对于一些企业环境来说，MySQL显然不具有优势。索引是减速表内容会见的次要手腕，出格对触及多个表的毗连的查询更是云云。这是数据库优化中的一个主要内容，我们要懂得为何必要索引，索引怎样事情和如何使用它们来优化查询。本节中，我们将先容索引的特性，和创立和删除索引的语法。索引的特性
一切的MySQL列范例能被索引。在相干的列上的利用索引是改善SELECT操纵功能的最好办法。
一个表最多可有16个索引。最年夜索引长度是256个字节，只管这能够在编译MySQL时被改动。
关于CHAR和VARCHAR列，你能够索引列的前缀。这更快而且比索引全部列必要较少的磁盘空间。关于BLOB和TEXT列，你必需索引列的前缀，你不克不及索引列的全体。
MySQL能在多个列上创立索引。一个索引能够由最多15个列构成。（在CHAR和VARCHAR列上，你也能够利用列的前缀作为一个索引的部分）。
固然跟着MySQL的进一步开辟创立索引的束缚将会愈来愈少，但如今仍是存在一些束缚的。上面的表依据索引的特征，给出了ISAM表和MyISAM表之间的不同：
表2-1通道信息特性字对比表
索引的特性ISAM表MyISAM表
NULL值
BLOB和TEXT列
每一个表中的索引数
每一个索引中的列数
最年夜索引行尺寸
不同意
不克不及索引
16
16
256字节同意
只能索引列的前缀
32
16
500字节
今后表中能够看到，关于ISAM表来讲，其索引列必需界说为NOTNULL，而且不克不及对BLOB和TEXT列举行索引。MyISAM表范例往失落了这些限定，并且减缓了其他的一些限定。两种表范例的索引特征的差别标明，依据所利用的MySQL版本的分歧，有大概对某些列不克不及举行索引。比方，假如利用3.23版之前的版本，则不克不及对包括NULL值的列举行索引。
索引有以下的几种情形：
INDEX索引：一般意义的索引，某些情形下KEY是它的一个同义词。索引的列能够包含反复的值。
UNIQUE索引：独一索引，包管了列不包括反复的值，关于多列独一索引，它包管值的组合不反复。
PRIMARYKEY索引：也UNIQUE索引十分相似。现实上，PRIMARYKEY索引仅是一个具有PRIMARY称号的UNIQUE索引。这暗示一个表只能包括一个PRIMARYKEY。
用AlterTable语句创立与删除索引
为了给现有的表增添一个索引，可以使用ALTERTABLE或CREATEINDEX语句。ALTERTABLE最经常使用，由于可用它来创立一般索引、UNIQUE索引或PRIMARYKEY索引，如：
ALTERTABLEtbl_nameADDINDEXindex_name(column_list)
ALTERTABLEtbl_nameADDUNIQUEindex_name(column_list)
ALTERTABLEtbl_nameADDPRIMARYKEYindex_name(column_list)
个中tbl_name是要增添索引的表名，而column_list指出对哪些列举行索引。一个(col1,col2,...)情势的列表制造一个多列索引。索引值有给定列的值串连而成。假如索引由不止一列构成，各列名之间用逗号分开。索引名index_name是可选的，因而能够不写它，MySQL将依据第一个索引列赋给它一个称号。ALTERTABLE同意在单个语句中指定多个表的变动，因而能够在同时创立多个索引。
一样，也能够用ALTERTABLE语句产出列的索引：
ALTERTABLEtbl_nameDROPINDEXindex_name
ALTERTABLEtbl_nameDROPPRIMARYKEY
注重下面第一条语句能够用来删除各类范例的索引，而第三条语句只在删除PRIMARYKEY索引时利用；在此情况中，不必要索引名，由于一个表只大概具有一个如许的索引。假如没有明白地创立作为PRIMARYKEY的索引，但该表具有一个或多个UNIQUE索引，则MySQL将删除这些UNIQUE索引中的第一个。
假如从表中删除列，则索引大概会遭到影响。假如所删除的列为索引的构成部分，则该列也会从索引中删除。假如构成索引的一切列都被删除，则全部索引将被删除。
比方，关于下面所利用的student为例，你大概想为之创立如许的索引，以减速表的检索速率：
mysql>ALTERTABLEstudent
->ADDPRIMARYKEY(id),
->ADDINDEXmark(english,Chinese,history);
这个例子，既包含PRIMARY索引，也包含多列索引。记着，利用PRIMARY索引的列，必需是一个具有NOTNULL属性的列，假如你乐意产看创立的索引的情形，可使用SHOWINDEX语句：
mysql>SHOWINDEXFROMstudent;
其了局为：
+---------+------------+----------+--------------+-------------+-
|Table|Non_unique|Key_name|Seq_in_index|Column_name|
+---------+------------+----------+--------------+-------------+-
|student|0|PRIMARY|1|id|
|student|1|mark|1|english|
|student|1|mark|2|chinese|
|student|1|mark|3|history|
+---------+------------+----------+--------------+-------------+-
因为列数太多，上表并没有包含一切的输入，读者能够试着本人检察。
再利用ALTERTABLE语句删除索引，删除索引必要晓得索引的名字，你能够经由过程SHOWINDEX语句失掉：
mysql>ALTERTABLEstudentDROPPRIMARYKEY,
->DROPINDEXmark;
再产看表中的索引，其语句和输入为：
mysql>SHOWINDEXFROMstudent;
Emptyset(0.01sec)
用CREATEDROPINDEX创立索引
还能够用CREATEINDEX语句来创立索引.CREATEINDEX是在MySQL3.23版中引进的，但假如利用3.23版之前的版本，可使用ALTERTABLE语句创立索引（MySQL一般在外部将CREATEINDEX映照到ALTERTABLE）。该语句创立索引的语法以下：
CREATEUNIQUEINDEXindex_nameONtbl_name(column_list)
CREATEINDEXindex_nameONtbl_name(column_list)
tbl_name、index_name和column_list具有与ALTERTABLE语句中不异的寄义。这里索引名不成选。很分明，CREATEINDEX可对表增添一般索引或UNIQUE索引，不克不及用CREATEINDEX语句创立PRIMARYKEY索引。
可使用DROPINDEX语句来删除索引。相似于CREATEINDEX语句，DROPINDEX一般在外部作为一条ALTERTABLE语句处置，而且DROPINDEX是在MySQL3.22中引进的。
删除索引语句的语法以下：
DROPINDEXindex_nameONtbl_name
仍是上一节的例子，因为CREATEINDEX不克不及创立PRIMARY索引，以是这里我们值创立一个多列索引：
mysql>CREATEINDEXmarkONstudent(english,chinese,history);
一样的反省student表，可知：
mysql>SHOWINDEXFROMstudent;
+---------+------------+----------+--------------+-------------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|
+---------+------------+----------+--------------+-------------+
|student|1|mark|1|english|
|student|1|mark|2|chinese|
|student|1|mark|3|history|
+---------+------------+----------+--------------+-------------+
然后利用上面的语句删除索引：
mysql>DROPINDEXmarkONstudent;
在创立表时指定索引
要想在公布CREATETABLE语句时为新表创立索引，所利用的语法相似于ALTERTABLE语句的语法，可是应当在您界说表列的语句部分指定索引创立子句，以下所示：
CREATETABLEtbl_name
(
…
INDEXindex_name(column_list),
KEYindex_name(column_list),
UNIQUEindex_name(column_list),
PRIMARYKEYindex_name(column_list),
…
)
与ALTERTABLE一样，索引名关于INDEX和UNIQUE都是可选的，假如未给出，MySQL将为其选一个。别的，这里KEY时INDEX的一一般名，具有不异的意义。
有一种特别情况：可在列界说以后增添PRIMARYKEY创立一个单列的PRIMARYKEY索引，以下所示：
CREATETABLEtbl_name
(
iINTNOTNULLPRIMARYKEY
)
该语句等价于以下的语句:
CREATETABLEtbl_name
(
iINTNOTNULL,
PRIMARYKEY(i)
)
后面一切表创立样例都对索引列指定了NOTNULL。假如是ISAM表，这是必需的，由于不克不及对大概包括NULL值的列举行索引。假如是MyISAM表，索引列能够为NULL，只需该索引不是PRIMARYKEY索引便可。
在CREATETBALE语句中能够某个串列的前缀举行索引（列值的最右边n个字符）。
假如对某个串列的前缀举行索引，使用column_list申明符暗示该列的语法为col_name(n)而不必col_name。比方，上面第一条语句创立了一个具有两个CHAR列的表和一个由这两列构成的索引。第二条语句相似，但只对每一个列的前缀举行索引：
CREATETABLEtbl_name
(
nameCHAR(30),
addressCHAR(60),
INDEX(name,address)
)
CREATETABLEtbl_name
(
nameCHAR(30),
addressCHAR(60),
INDEX(name(10),address(20))
)
你能够反省所创立表的索引：
mysql>SHOWINDEXFROMtbl_name;
+----------+------------+----------+--------------+-------------+-
|Table|Non_unique|Key_name|Seq_in_index|Column_name|
+----------+------------+----------+--------------+-------------+-
|tbl_name|1|name|1|name|
|tbl_name|1|name|2|address|
+----------+------------+----------+--------------+-------------+-
在某些情形下，大概会发明必需对列的前缀举行索引。比方，索引行的长度有一个最年夜下限，因而，假如索引列的长度凌驾了这个下限，那末便可能必要使用前缀举行索引。在MyISAM表索引中，对BLOB或TEXT列也必要前缀索引。
对一个列的前缀举行索引限定了今后对该列的变动；不克不及在不删除该索引并利用较短前缀的情形下，将该列延长为一个长度小于索引所用前缀的长度的列。
总结
本节对索引的范例，已怎样创立索引做了先容，个中触及三个对照主要的SQL语句――ALTERTABLE、CREATE/DROPINDEX和CREATETABLE，注重它们的用法。
索引最主要的功效是，经由过程利用索引减速表的检索，有关这方面的常识，将在第十章数据库优化中先容。
思索题
1、创建一个以下所述的表：
data：FLOAT列，利用随机函数添补数据
birth：DATETIME列，添补以后工夫。
然后，请录进几条数据。最初盘算data列的均匀值、总和、极值，而且依照data列降序排序检索值。
2、分离利用尺度SQL形式和扩大正轨表达式形式婚配，婚配下面创立的表，假定你创立表确当前日期为2001-01-01，用形式婚配检索出birth列包括该日期的值。（实践上，下面的表中纪录都是统一日期录进的，因而实践将前往全体纪录。）
3、为前几章利用的数据表创立索引：
student：为id段创立一个PRIMARY索引，为english、chinese和history创立一个多列索引。
pet：为name和owner段创立一个多类索引。
4、删除为pet表创立的索引。
MySQLAB公司的一个高级开发者者表示，“这个特点使得MySQL可以根据你当前的系统的需要来进行调整。” groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。 学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了! 外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。 习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。 比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。 我个人认为就是孜孜不懈的学习 另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);
页:
[1]