MySQL的数据类型

MySQL数据类型简介

整数类型、浮点数类型和定点数类型

整数：
如上表所示INT和INTEGER的字节数与取值范围相同，其实，在MySQL中INT类型和INTEGE类型是一样的。TYNYINT类型占用的字节数最小为1个字节，取值范围最小；BIGIYNT类型占用的字节数最大，需要八个字节，取值范围最大。
容易看出，不同整数类型的字节数不同，根据不同类型所占的字节数可以推算出该类型的取值范围。

默认整数数据类型宽度如下：

Field	Type
a	tinyint(4)
b	smallint(6)
c	mediumint(9)
d	int(11)
e	bigint(20)

MySQL各种数据类型的字段都有宽度限制，默认数据类型宽度恰好为显示该数据类型所有的值的宽度。

一、在整数类型使用时，还可搭配zerofill参数。zerofill参数在数字显示不足的空间由0来填充。需要注意的是，使用zerofill参数时，MySQL会自动加上UNSIGNED属性。也就是说该整数类型就只能表示无符号数，期限是宽度比默认宽度小1。
二、以上数据类型的字段可以设置显示宽度，但是依然可以插入大于显示宽度的值。前提是不超过对应数据类型的默认宽度，因为该数据类型的最大值宽度是小于等于默认宽度的，超过默认宽度相当于超过该数据类型可显示的最大值，显然这个值是不可插入的。
三、整数类型还有一个AUTO_INCREMENT属性。该属性可是字段变成自增字段，对该字段，每插入一条新的纪录，该字段都会自增1，自增的初值默认从1开始自增，如果第一条记录设置了初值就从该记录开始。（技巧：加上AUTO_INCREMENT的字段，其每个值都是自动增加的。因此，这个字段不可能出现相同的值；所以，通常用于表中ID字段的约束，ID作为表的主键）。

浮点类型和顶点类型：
由上表可知，DECIMALl类型和DOUBLE类型的取值范围一样，但是DECIMAL的有效取值范围由 M和D决定，而且DICIMAL 字节数是M+2。也就是说，定点数的存储空间是根据其精度确定的。MySQL中可以指定浮点数和定点数的精度。其基本形式如下
数据类型 （M,D）
数据类型为float或double；M参数为精度，是数据的总长度，小数点不占位置；D参数是标度，指小数点后的长度为D。

一、虽然以上指定小数精度方法都适合于浮点数和定点数，但不是浮点数的通用标准，除非必要不要使用于浮点数，否则可能会影响数据库的迁移。
二、如果没有指定精度，浮点数和定点数都有其默认精度，float和double类型谁都会保存其实际精度。DECIMAL类型的整数位默认是10位，小数位为0，即默认为整数。
三、在MySQL中，定点数以字符串的方式存储。因此，其精度比浮点数要高。而且浮点数会出现误差，如果要求精度比较高，还是选择定点数（DECIMAL)比较安全。

日期与时间类型

1、YEAR
如果插入的年份信息超出范围就回插入‘0000’；如果插入‘24’，‘86’，‘0’,‘00’，实际插入的值分别是‘2024’,‘1986’，‘2000’

使用YEAR类型时，要区分0和‘0’。如果插入的是0，存入的年份是0000。如果插入的是‘0’，存入的年份则是2000。‘00’和‘0’的效果相同。

2、TIME类型

TIME字段赋值及表示方法如下：

‘D HH:MM:SS’格式的字符串；向表中插入‘2 23：50:50’，‘11:11’，‘30’结果如下：

a
71:50:50
11:11:00
00:00:30

HHMMSS格式的字符串或者HHMMSS格式的数值表示；向表中插入121212、‘131313’、‘0’和0。

a
12:12:12
13:13:13
00:00:00
00:00:00

a是TIME 数据类型。使用CURRENT_TIME和NOW（）插入系统当前时间。

a
11:48:00
11:48:05

如果插入的TIME值是无效的，系统会提示错误，就算插入表中也会被转换为00:00:00。

3、DATE类型
‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表示；这种方式可以表达的范围是‘1000-01-01’~‘9999-12-31；MySql还支持一些不严格的语法格式，任何标点都可以用爱做分隔符。
a是DATE数据类型，向表中插入‘1949-10-01’、‘1950#2#3’、‘1951@3@4’和‘19520101’。结果如下

a
1949-10-01
1950-02-03
1951-03-04
1952-01-01

如果向表中插入‘53-01-01’、‘78@1@10’、‘540101’和‘790101’。显示结果如下

a
2053-01-01
1978-01-01
2054-01-01
1979-01-01

虽然MySQL支持不严格语法格式，但是为了统一一般情况用‘-’做日期分隔符，‘:’做时间分隔符。同样可以使用CURRENT_DATE()和NOW（）插入当前系统的信息。例如：2020-01-02

4、DATETIME类型
DATETIME类型使用8个字节来表示日期和时间。MySQL中以‘YYYY-MM-DDHH:MM:SS’的形式显示DATETIME类型的值。从其形式可以看出，DATETIME类型可以直接用DATE类型和TIME类型组合而成。给DATETME类型的字段赋值的表示方法如下:
‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表示。
这种方式可以表达的范围是‘1000-01-01 00:00:00’~‘9999-12-3123:59:59’。

5、TIMESTAMP类型
TIMESTAMP类型使用4个字节来表示日期和时间。TIMESTAMP类型的范围是从1970-01-01 08:00:01~2038-01-19 11:14:07。
值得注意的是，TIMESTAMP类型范围比较小，没有 DATETIME类型的范围大。因此，输入值时要保证在 TIMESTAMP类型的有效范围内。
下面介绍 TIMESTAMP类型的几种与DATETIME类型不同的形式。内容如下:
(1)使用 CURRENT_TIMESTAMP来输入系统当前日期与时间。
(2）输入NULL时，系统会输入系统当前日期与时间。
(3）无任何输入时，系统会输入系统当前日期与时间。
TIMESTAMP类型还有一个很大的特殊点，就是时间是根据时区来显示的。例如，在东八区插入的TIMESTAMP类型为2009-09-30 14:21:25。在东七区显示时，时间部分就变成了13:21:25。在东九区显示时，时间部分就变成了15:21:25。
若要选择的时间范围较大还是选择DATETIME合适。

字符串类型

字符串类型包括：CHAR、VARCHAR、BLOB、TEXT、ENUM和SET。
1、CHAR和VARCHAR
CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0～255的任意值。例如，CHAR(10O)就是指定CHAR类型的长度为100。
VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~～65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100。但是，不是每条记录都要占用100个字节。而是在这个最大值范围内，使用多少分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1。这样，即可有效节约系统的空间。

需要注意的是，CHAR类型和VARCHAR类型在空格的存储上有区别，例如存储‘123*’，*代表空格，VARCHAR会把空格也保存下来，CHAR类型却把空格舍弃了。

2、TEXT
TEXT 类型是一种特殊的字符串类型。TEXT只能保存字符数据，如新闻的内容等。TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

从表4.5可以看出，各种TEXT类型的区别在于允许的长度和存储空间不同。因此在这几种 TEXT类型中，根据需求选取既能满足需要又最节约空间的类型即可。

3、ENUM
ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了。其基本形式如下:
属性名ENUM(‘值1，值2然，值n’)
其中，属性名参数指字段的名称;“值n”参数表示列表中的第 n个值，这些值末尾的空格将会被系统直接删除。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。

如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加 NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。

4、SET
在创建表时，SET类型的取值范围就以列表的形式指定了。其基本形式如下。属性名 SET(值1,值2,值n)
其中，“属性名”参数指字段的名称;“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合。
同ENUM类型一样，列表中的每个值都有一个顺序排列的编号。MySQL 中存入的是这个编号，而不是列表中的值。
插入记录时，SET字段中的元素顺序无关紧要。存入MySQL 数据库后，数据库系统会自动按照定义时的顺序显示。

SET 和ENUM类型，在插入的值不为空，且该字段已有指定的元素时，插入的元素如不在预设范围内就都会报错。

二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括 BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。
1.BINARY和VARBINARY类型
BINARY类型和 VARBINARY类型都是在创建表时指定了最大长度，其基本形式如下:
字符串类型 (M)
其中，“字符串类型”参数指定了数据类型为BINARY类型还是VARBINARY类型;M参数指定了该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY (10)就是指数据类型为BINARY类型，其最大长度为10。
BINARY类型的长度是固定的，在创建表时就指定了。不足最大长度的空间由“O”补全。例如，BINARY (50)就是指定BINARY类型的长度为50。
VARBINARY 类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后，其长度可以在0到最大长度之间。例如，VARBINARY(50)的最大字节长度是50。但是，不是每条记录的字节长度都是50。在这个最大值范围内，使用多少分配多少。VARBINARY类型实际占用的空间为实际长度加1。这样，可以有效的节约系统的空间。

2.BIT类型
BIT类型也是在创建表时指定了最大长度，其基本形式如下:
BIT( M)
其中，“M”指定了该二进制数的最大字节长度为M,M的最大值为64。例如，BIT(4)就是数据类型为BIT类型，长度为4。若字段的类型BIT(4)，存储的数据是从0~15。因为，变成二进制以后，15的值为1111，其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度。因此，大于等于16 的数是不能插入到BIT(4)类型的字段中的。
在查询BIT类型类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。

3. BLOB类型
BLOB类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，如图片等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种 BLOB 类型最大的区别就是能够保存的最大长度不同。LONGBLOB 的长度最大，TINYBLOB的长度最小。
BLOB类型与TEXT类型很类似。不同点在于BLOB类型用于存储二进制数据，BLOB类型数据是根据其二进制编码进行比较和排序。而 TEXT类型是文本模式进行比较和排序的。

技巧:BLOB类型主要用来存储图片、PDF文档等二进制文件。通常情况下，可以将图片、PDF文档都可以存储在文件系统中，然后在数据库中存储这些文件的路径。这种方式存储比直接存储在数据库中简单，但是访问速度比存储在数据库中慢。

MySQL数据类型选择

整数类型与浮点类型的选择：

是否需要表达小数，要表达小数就用浮点型，反之就用整数类型

浮点数与定点数的选择：

FLOAT,需要小数时一般使用float类型
DOUBLE，需要小数并且要求精度时使用double类型
DECIMAL，需要自定义精度时使用定点数类型，要求更高精度时也可以使用定点数（因为MySQL中定点数以字符串存储）。

日期与时间数据类型的选择：

YEAR仅用到年份信息就是用YEAR类型

DATE不使用时分秒，仅用于日期

TIME 类型专门用来存储时间数据（时分秒）， 而且仅占三个字节。如果需要记录时间，TIME类型最合适。

DATETIME类型用于记录日期和时间，其作用等价于DATE类型和TIME类型的组合。一个DATETIME类型的字段可以用一个DATE类型的字段和一个TIME类型的字段代替。但是，如果需要同时记录日期和时间，选择DATETIME类型是个不错的选择。

虽然TIMESTAMP表示范围较DATETIME小，但是TIMESTAMP是根据时区显示，如果跨时区使用时间TIMESTAMP较好。

CHAR类型和VARCHAR类型的选择：

CHAR类型的长度是固定的，而 VARCHAR类型的长度是在范围内可变的。因此，VARCHAR类型占用的空间比CHAR类型小。而且，VARCHAR类型比CHAR类型灵活。对于长度变化比较大的字符串类型，最好是选择VARCHAR类型。
虽然CHAR类型占用的空间比较大，但是CHAR类型的处理速度比VARCHAR快。因此，对于长度变化不大和查询速度要求较高的字符串类型，最好选择CHAR类型。

ENUM类型和SET类型的选择：

ENUM类型最多可以有65535个成员，而SET类型最多只能包含64个成员。两者的取值只能在成员列表中选取。ENUM类型只能从成员中选择一个，而SET类型可以选择多个。
因此，对于多个值中选取一个的，可以选择ENUM类型。例如，“性别”字段就可以定义成ENUM类型，因为只能在“男”和“女”中选其中一个。对于可以选取多个值的字段，可以选择SET类型。例如，“爱好”字段就可以选择SET 类型，因为可能有多种爱好。

TEXT类型和BLOB类型:
TEXT类型与BLOB类型很类似。TEXT类型存储只能存储字符数据。而BLOB类型可以用于存储二进制数据。如果要存储文章等纯文本的数据，应该选择TEXT类型。如果需要存储图片等二进制的数据，应该选择BLOB类型。
TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这4者最大的不同是内容的长度不同。TINYTEXT类型允许的长度最小，LONGTEXT 类型允许的长度最大。BLOB类型也是如此。

MySQL范式

MySQL范式介绍

应用数据库范式可以带来许多益处，大概总结为三点：
1）减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）
2）消除异常（插入异常，更新异常，删除异常）
3）让数据组织的更加简介简洁便利

但是数据库范式绝对不是越高越好；因为范式越高，意味着表越多，多表联合查询的几率就越大，SQL查询的效率就变低了。
一般开发中只遵从第三范式就好，是具体情况而定。

第一范式：每一个数据项都是不可再分的，即表中每一列具有原子性

列都是基本数据项，不能够再进行分割，否则设计成一对多的实体关系。例如表中的地址字段，可以再细分为省，市，区等不可再分割（即原子特性）的字段，如下：
列是基本数据项、不能再进行擦缝，否则设计成一对多的关系
不满足第一范式不能称之为关系型数据库

拆分改造后：
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021010213200410
.png)

学生表（学号、用户名、性别、年龄，地址）
例：陕西省西安市西安工大学
例：陕西省西安市未央区学府中路西安工业大学
地址信息还包含省市区可以拆分
拆分改造后：
学生表（学号、用户名、性别、年龄、地址ID）
地址表（地址ID、省、市、区、街道、学校）

上图的表就是把地址字段分成更详细的city,country,street三个字段，注意，不符合第一范式不能称作关系型数据库。

第二范式：非主属性完全依赖于主键(主要针对联合主键-》消除部分依赖)
符合第一范式的基础上，非主属性完全依赖于主关键字，如果不是完全依赖主键，应该拆分成新的实体，设计成一对多的实体关系。
示例：
假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)
即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题：
(1) 数据冗余：
同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
(2) 更新异常：
若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。
(3) 插入异常：
假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。
(4) 删除异常：
假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
示例2：

学生选课表（学生ID、学生姓名、学生性别、课程ID、课程成绩）
主键（学生ID、课程Id）
学生姓名-》学生ID -》部分依赖
学生性别-》学生ID -》部分依赖
课程成绩-》（学生ID、课程id）-》完全依赖
拆分改造后：
学生表（学生ID、学生姓名、学生性别） 主键：学生ID
课程成绩表（课程ID、学生ID、成绩） 主键：（课程ID、学生ID）
示例：选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 课程所占学分)，（学号，课程名称）是联合主键，但是学分字段只和课程名称有关，和学号无关，相当于只依赖联合主键的其中一个字段，不符合第二范式。

第三范式：属性不依赖于其它非主属性（消除依赖传递）
基于第二范式的基础，非主属性只依赖于主属性
要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
示例：学生表（学生ID、学生姓名、学生性别、学院id、学院电话）
主键：学生ID
学生姓名-》学生ID
学生性别-》学生ID
学院名称-》学生ID
学院电话 -》 学生ID -》查询学院id-》查询学院电话

数据冗余、更新异常、插入异常和删除异常的情况同样存在。

拆分改造后：
学生表：（学生ID、学生姓名、学生性别、学院ID）主键：学生ID
学院表：（学院ID、学院名称、学院电话） 主键：学院ID

示例：学生关系表为Student(学号, 姓名, 年龄, 课程, 成绩)，学号是主键，但是学院 电话只依赖于所在学院，并不依赖于主键学号，因此该设计不符合第三范式，应该把学院专门设计成一张表，学生表和学院表，两个是一对多的关系。

BCNF范式：每一个决定属性集都包含候选键，也就是只有一个候选键才可以达到这种情况；

简单的说，BC范式是在第三范式的基础上的一种特殊情况，即每个表中只有一个候选键（在一个数据库中每行的值都不相同，则可称为候选键）；（可以有多个决定属性集，决定属性集中必定含有吗）

假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
主键（仓库ID,管理员ID, 存储物品ID）
（仓库ID,管理员ID, 存储物品ID） → (数量)
但是我们发现：
(仓库ID, 存储物品ID) →(管理员ID, 数量)
(管理员ID, 存储物品ID) → (仓库ID, 数量)
所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
(仓库ID) → (管理员ID)
(管理员ID) → (仓库ID)
即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：
（1）数据冗余
（2）删除异常：
当仓库被清空后，所有”存储物品ID”和”数量”信息被删除的同时，”仓库ID”和”管理员ID”信息也被删除了。
（3）插入异常：
当仓库没有存储任何物品时，无法给仓库分配管理员。
（4）更新异常：
如果仓库换了管理员，则表中所有行的管理员ID都要修改。
把仓库管理关系表分解为二个关系表：
仓库管理：StorehouseManage(仓库ID, 管理员ID)；
仓库：Storehouse(仓库ID, 存储物品ID, )。
这样的数据库表是符合BCNF范式的，基本消除了删除异常、插入异常和更新异常。

第四范式：消除表中的多值依赖

简单来说，第四范式就是要消除表中的多值依赖，也就是说可以减少维护数据一致性的工作。

对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多。表多会带来很多问题：
1 查询时需要连接多个表，增加了SQL查询的复杂度
2 查询时需要连接多个表，降低了数据库查询性能
因此，并不是应用的范式越高越好，要看实际情况而定。第三范式已经很大程度上减少了数据冗余，并且基本预防了数据插入异常，更新异常，和删除异常了。
…

MySQL范式联系与转化

由上图可知，范式越高所包含的范围与小；因此可以理解第几范式能向高级范式分解，以解决数据操作的各种异常。
分解其实就是把可能形成另一个关系的属性列拿出来创建一个新的表，或者对一些可以继续分解的属性列分解为另一个关系。

MySQL索引

索引的含义及特点

索引的含义：创建在表上的，对数据库中一列或者多列的值进行排序的一种结构。索引可以提高查询速度。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引。InnoDB和 MyISAM存储引擎支持 BTREE索引，MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。

索引有其明显的优势，也有其不可避免的缺点。

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因;对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度;使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间，每一个索引要占一定的物理空间;增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

索引的分类

1．普通索引
在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在 student表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。

2．唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在 student表的 stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。

3．全文索引
使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student 表的 information字段是TEXT类型，该字段包含了很多的文字信息。在 information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

4．单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

5．多列索引
多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和 sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

6．空间索引
使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL 中的空间数据类型包括GEOMETRY和POINT、LINESTRING和 POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

MySQL建立索引原则

1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT 和 UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6.尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

注意:选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。.

MySQL创建索引

1.创建表时创建索引
2.在已经存在的表上创建索引
3.用alter table创建索引

MySQL删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。
对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下:

drop INDEX 索引名 on 表名;

MySQL索引的有效性

索引失效：
1.隐式转换导致索引失效。
例：
由于表的字段name定义为varchar(20),但在查询时把该字段作为数字类型，以where条件传给Mysql,这样会导致索引失效.
强转会引起索引失效：
name varchar（20）
index（name）;
错误的例子：select * from test where name = 999;

N行数据 把N行数据的name属性分别取出来先强转在比较。
由于需要将每一行数据都拿出来进行强换之后再比较。
强转之后改变了原有字段的比较规则
正确的例子：select * from test where name=‘999’; 能用到索引

2.对索引列进行运算导致索引失效,对索引列进行运算包括(+，-，*，/，! 等)
例：
index(id)
错误的：select * from test where id - 1 < 9;
需要将每一个元组中的id值先取出，再减一然后和9再去比较
正确的例子：select * from test where id < 10;能用到索引

3.使用MySQL内部函数导致索引失效.对于这样情况应当创建基于函数的索引。
错误的例子：select * from test where F(id)=10; 说明，此时id的索引已经不起作用了（需要将每一个元组中的id值先取出,再通过函数计算，计算之后的值在和10比较）
要使用索引的话需要将：F(x) = y; ----> x = R(y);
正确的例子：select * from test where id= R(10）;

4. 以下使用会使索引失效，应避免使用；
a.使用 <> 、not in 、!=
index(id)
select * from … where id not in (12,13,15); 用不到 没有明确查询条件
id
1
…
…
999

select * from … where id in (12,13,15); 能用到

b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
%:任意个任意字符
index（name）
select * from name like ‘%a’; //由于字符串的最左比较法 用不到
字符串的索引是如何建立：从左到右一个字符一个字符进行大小比较然后得出的B+树的结构。

abcdsdsa
abdr
select * from name like ‘a%’; //由于字符串的最左比较法 能用到

5、使用OR关键字查询语句
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，查询中才会使用索引。否则，查询将不使用索引。

name = “tom” or(或) age < 90; ---->index(name) index(age);

name = “tom” and age < 90; ----> index(name,age)

索引的使用规则

最左前最原则：对带有联合索引的表进行多字段查询时，应将联合索引中的第一个索引字段放在第一位，这样索引才能使用到。
**索引使用时的时间复杂度：**小表决定查询次数，大表决定查询时间。

例：

联合查询中如何使用索引，在表rb1,rb2中均在stu_id 字段上添加索引 index(stu_id)。判断哪张表的索引能用到。

假设rb2是大表，rb1是小表
1.explain select * from rb1 a , rb2 b where a.stu_id = b.stu_id\G
where a.stu_id = b.stu_id
底层处理：把b中的所有的stu_id取出来，分别和a中的stu_id作比较；大表rb2能够使用到索引 而小表rb1用不到索引

explain select * from rb1 a  join rb2 b 
	on a.stu_id = b.stu_id where a.stu_id < 25\G

rb1使用到索引：where条件
rb2使用到索引：rb2是大表当然用到索引

3.explain select * from rb1 a join rb2 b
on a.stu_id = b.stu_id where b.stu_id < 25\G
rb2用到了索引因为where条件，同时变成小表，rb1变成大表用到了索引

4.没给id加索引时:
a表的id上没有索引所以还是小表不用索引，rb2用索引

给id加上索引后：