mysql中添加索引的三种⽅法
在mysql中有多种索引，有普通索引，全⽂索引，唯⼀索引，多列索引，⼩伙伴们可以通过不同的应⽤场景来进⾏索引的新建，在此列出三种新建索引的⽅法
mysql 中添加索引的三种⽅法
1.1 新建表中添加索引
①普通索引
1 2 3 4 5 6 7create table t_dept(
no int not null primary key,    name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
index index_no(no)
)
②唯⼀索引
1 2 3 4 5 6 7create table t_dept(
no int not null primary key,      name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
unique index index_no(no)）
③全⽂索引
1 2 3 4 5 6create table t_dept(
no int not null primary key,      name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
fulltext index index_no(no)
④多列索引
1 2 3 4 5 6 7create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
key index_no_name(no,name)    )
1.2 在已建表中添加索引
①普通索引
1 2create index index_name              on t_dept(name);
②唯⼀索引
1 2create unique index index_name              on t_dept(name);
③全⽂索引
1create fulltext index index_name
1 2create fulltext index index_name              on t_dept(name);
④多列索引
1 2create index index_name_no                on t_dept(name,no)
1.3 以修改表的⽅式添加索引
①普通索引
1 2alter table t_dept
add index index_name(name);
②唯⼀索引
1 2alter table t_dept
add unique index index_name(name);
③全⽂索引
1 2alter table t_dept
add fulltext index_name(name);
④多列索引
1 2alter table t_dept
add index index_name_no(name,no);
1.4 ⼩结
以上就是mysql中索引的新建的三种⽅法。以上的表名以及列名，请根据实际表列名替换。
mysql索引的类型和优缺点
索引是⼀种特殊的⽂件(InnoDB数据表上的索引是表空间的⼀个组成部分)，它们包含着对数据表⾥所有记录的引⽤指针。
注：[1]索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新⼀次。为了在某种程序上弥补这⼀缺陷，许多SQL命令都有⼀个DELAY_KEY_WRITE项。这个选项的作⽤是暂时制⽌MySQL在该命令每插⼊⼀条新记录和每修改⼀条现有之后⽴刻对索引进⾏刷新，对索引的刷新将等到全部记录插⼊/修改完毕之后再进⾏。在需要把许多新记录插⼊某个数据表的场合，DELAY_KEY_WRITE选项的作⽤将⾮常明显。[2]另外，索引还会在硬盘上占⽤相当⼤的空间。因此应该只为最经常查询和最经常排序的数据列建⽴索引。注意，如果某个数据列包含许多重复的内容，为它建⽴索引就没有太⼤的实际效果。
从理论上讲，完全可以为数据表⾥的每个字段分别建⼀个索引，但MySQL把同⼀个数据表⾥的索引总数限制为16个。
1. InnoDB数据表的索引
与MyISAM数据表相⽐，索引对InnoDB数据的重要性要⼤得多。在InnoDB数据表上，索引对InnoDB数
据表的重要性要在得多。在 InnoDB 数据表上，索引不仅会在搜索数据记录时发挥作⽤，还是数据⾏级锁定机制的苊、基础。”数据⾏级锁定”的意思是指在事务操作的执⾏过程中锁定正在被处理的个别记录，不让其他⽤户进⾏访问。这种锁定将影响到(但不限于)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。
出于效率⽅⾯的考虑，InnoDB数据表的数据⾏级锁定实际发⽣在它们的索引上，⽽不是数据表⾃⾝上。显然，数据⾏级锁定机制只有在有关的数据表有⼀个合适的索引可供锁定的时候才能发挥效⼒。
2. 限制
如果WEHERE⼦句的查询条件⾥有不等号(WHERE coloum != …)，MySQL将⽆法使⽤索引。
类似地，如果WHERE⼦句的查询条件⾥使⽤了函数(WHERE DAY(column) = …)，MySQL也将⽆法使⽤索引。
在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数据类型相同时才能使⽤索引。
如果WHERE⼦句的查询条件⾥使⽤⽐较操作符LIKE和REGEXP，MySQL只有在搜索模板的第⼀个字符不是通配符的情况下才能使⽤索引。⽐如说，如果查询条件是LIKE ‘abc%’，MySQL将使⽤索引；如果
查询条件是LIKE ‘%abc’，MySQL将不使⽤索引。
在ORDER BY操作中，MySQL只有在排序条件不是⼀个查询条件表达式的情况下才使⽤索引。(虽然如此，在涉及多个数据表查询⾥，即使有索引可⽤，那些索引在加快 ORDER BY⽅⾯也没什么作⽤)
如果某个数据列⾥包含许多重复的值，就算为它建⽴了索引也不会有很好的效果。⽐如说，如果某个数据列⾥包含的净是些诸如”0/1″
或”Y/N”等值，就没有必要为它创建⼀个索引。
普通索引、唯⼀索引和主索引
1. 普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯⼀任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择⼀个数据最整齐、最紧凑的数据列(如⼀个整数类型的数据列)来创建索引。
2. 唯⼀索引
普通索引允许被索引的数据列包含重复的值。⽐如说，因为⼈有可能同名，所以同⼀个姓名在同⼀个”员⼯个⼈资料”数据表⾥可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该⽤关键字UNIQUE把它定义为⼀个唯⼀索引。这么做的好处：⼀是简化了MySQL对这个索引的管理⼯作，这个索引也因此⽽变得更有效率；⼆是MySQL会在有新记录插⼊数据表时，⾃动检查新记录的这个字段的值是否已经在某个记录的这个字段⾥出现过了；如果是，MySQL将拒绝插⼊那条新记录。也就是说，唯⼀索引可以保证数据记录的唯⼀性。事实上，在许多场合，⼈们创建唯⼀索引的⽬的往往不是为了提⾼访问速度，⽽只是为了避免数据出现重复。
3. 主索引
在前⾯已经反复多次强调过：必须为主键字段创建⼀个索引，这个索引就是所谓的”主索引”。主索引与唯⼀索引的唯⼀区别是：前者在定义时使⽤的关键字是 PRIMARY⽽不是UNIQUE。
4. 外键索引
varchar2最大长度
如果为某个外键字段定义了⼀个外键约束条件，MySQL就会定义⼀个内部索引来帮助⾃⼰以最有效率的⽅式去管理和使⽤外键约束条件。
5. 复合索引
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使⽤⼀个这样的索引。如果查询操作只需要⽤到columnA数据列上的⼀个索引，就可以使⽤复合索引INDEX(columnA, columnB)。不过，这种⽤法仅适⽤于在复合索引中排列在前的数据列组合。⽐如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使⽤，但不能当做B、C或(B, C)的索引来使⽤。
6. 索引的长度
在为CHAR和VARCHAR类型的数据列定义索引时，可以把索引的长度限制为⼀个给定的字符个数(这个数字必须⼩于这个字段所允许的最⼤字符个数)。这么做的好处是可以⽣成⼀个尺⼨⽐较⼩、检索速度却⽐较快的索引⽂件。在绝⼤多数应⽤⾥，数据库中的字符串数据⼤都以各种各样的名字为主，把索引的长度设置为10~15个字符已经⾜以把搜索范围缩⼩到很少的⼏条数据记录了。
在为BLOB和TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最⼤索引长度是255个字符。
全⽂索引
⽂本字段上的普通索引只能加快对出现在字段内容最前⾯的字符串(也就是字段内容开头的字符)进⾏检
索操作。如果字段⾥存放的是由⼏个、甚⾄是多个单词构成的较⼤段⽂字，普通索引就没什么作⽤了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很⼤，响应时间就会很长。
这类场合正是全⽂索引(full-text index)可以⼤显⾝⼿的地⽅。在⽣成这种类型的索引时，MySQL将把在⽂本中出现的所有单词创建为⼀份清单，查询操作将根据这份清单去检索有关的数据记录。全⽂索引即可以随数据表⼀同创建，也可以等⽇后有必要时再使⽤下⾯这条命令添加：
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全⽂索引，就可以⽤SELECT查询命令去检索那些包含着⼀个或多个给定单词的数据记录了。下⾯是这类查询命令的基本语法：
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)
上⾯这条命令将把column1和column2字段⾥有word1、word2和word3的数据记录全部查询出来。
注解：InnoDB数据表不⽀持全⽂索引。
查询和索引的优化
只有当数据库⾥已经有了⾜够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库⾥只有⼏百条数据记录，它们往往在执⾏完第⼀条查询命令之后就被全部加载到内存⾥，这将使后续的查询命令都执⾏得⾮常快–不管有没有使⽤索引。只有当数据库⾥的记录超过了1000条、数据总量也超过了 MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
在不确定应该在哪些数据列上创建索引的时候，⼈们从EXPLAIN SELECT命令那⾥往往可以获得⼀些帮助。这其实只是简单地给⼀条普通的SELECT命令加⼀个EXPLAIN关键字作为前缀⽽已。有了这个关键字，MySQL将不是去执⾏那条SELECT命令，⽽是去对它进⾏分析。MySQL将以表格的形式把查询的执⾏过程和⽤到的索引(如果有的话)等信息列出来。
在EXPLAIN命令的输出结果⾥，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联关系(JOIN)。在各种类型的关联关系当中，效率最⾼的是system，然后依次是const、eq_ref、ref、range、index和 All(All的意思是：对应于上⼀级数据表⾥的每⼀条记录，这个数据表⾥的所有记录都必须被读取⼀遍–这种情况往往可以⽤⼀索引来避免)。
possible_keys数据列给出了MySQL在搜索数据记录时可选⽤的各个索引。key数据列是MySQL实际选⽤的索引，这个索引按字节计算的长度在key_len数据列⾥给出。⽐如说，对于⼀个INTEGER数据列的
索引，这个字节长度将是4。如果⽤到了复合索引，在key_len数据列⾥还可以看到MySQL具体使⽤了它的哪些部分。作为⼀般规律，key_len数据列⾥的值越⼩越好(意思是更快)。
ref数据列给出了关联关系中另⼀个数据表⾥的数据列的名字。row数据列是MySQL在执⾏这个查询时预计会从这个数据表⾥读出的数据⾏的个数。row 数据列⾥的所有数字的乘积可以让我们⼤致了解这个查询需要处理多少组合。
最后，extra数据列提供了与JOIN操作有关的更多信息，⽐如说，如果MySQL在执⾏这个查询时必须创建⼀个临时数据表，就会在extra列看到 using temporary字样。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。