mysql创建lower索引

mysql创建lower索引的方法：

1、自动创建。在表中定义一个primarykey或者unique时，MySQL数据库会自动创建一个对应的主键索引或者是唯一索引。

2、手动创建。在创建表的时候创建索引，即可给已经有的表创建索引。

mysql数据库索引是一种能够让mysql数据查询更加快速的数据结构，我们在新建数据库的时候，如果设置了某个字段的Primary key主键，那么数据库会默认为我们的主键字段创建一个唯一索引(Unique Index)的东西，所以你就不需要再为此字段创建mysql数据库索引了，当然了，如果你想设置别的字段索引，那么就要额外加入该字段的数据库索引了。

一：Mysql数据库索引的创建

1)如何为我们的mysql数据库添加索引呢？很简单，请看下面的创建mysql普通索引格式：

CREATE INDEX [index name] ON [table name]([column name]);

参数说明：NameDescription

index name索引名称

table name需要添加索引的表名称

column name需要添加索引的列名称

例如我们要创建一个索引，就可以使用如下的mysql命令运行即可：CREATE INDEX myindex ON mytable(aut_id);

2)唯一索引的创建，和mysql普通索引差不多，只需要在INDEX关键词前面加入UNIQUE关键词即可，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id);

我们也可以使用USING BTREE关键字，B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度，mysql命令如下：CREATE UNIQUE INDEX myindex ON mytable(aut_id) USING BTREE;

二：查看mysql数据库索引

我们可以使用以下命令查询刚刚新建的mysql数据库索引，“from mytable”代表查询该数据库表里面有哪些索引，mysql命令如下：show index from mytable;

创建成功之后，会在“key_name”字段中显示刚刚创建的数据库索引名称，如图所示：

三：删除mysql数据库索引

我们也可以使用mysql命令删除某张表的索引，命令如下：drop index english on mytable

四：查询sql语句是否使用了mysql索引

我们上面常见好mysql数据库索引之后，如果想要知道我们在执行sql语句时是否使用了数据库索引，就要在sql语句前面加入“EXPLAIN”关键词，命令如下所示：EXPLAIN SELECT FROM `allword` where english='America';

如果使用到了我们创建的字段索引，就会看到如图所示的字段都不会是NULL空值，如下：

在有些情况下mysql索引会失效，也就是在执行到sql语句时没有使用到我们创建的数据库表字段索引，可能有以下这些情况：

1：使用了OR关键字查询

2：或者LOWER()，UPPER()函数，

3：还有一种就是使用了LIKE关键字查询，像(like '%XX'或者like '%XX%')这样的语句，但是像(like 'XX%')这种情况的mysql表索引是不会失效的。

引入一个面试问题:

看完以下以后再回顾，会发现迎刃而解

Mysql 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎

由于实际情况，数据页只能按照一棵 B+树 进行排序， 因此每张表只能拥有一个 聚集索引（即 主键）。

栗子：

每个叶子节点的索引行中包含了一个书签（bookmark） 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据！ 由于InnoDB存储引擎表， 是按照主键来构建的， 所以 ，该书签内其实包含或者说指向了 数据行所对应的聚集索引键

也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引， 可以通过该聚集索引 找到对应的数据行

辅助索引的存在并不影响数据在聚集索引中的组织，因为每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键，然后再通过聚集索引找到一个完整的数据行。

例如：

聚集索引辅助索引关系:

： 又叫做组合索引 ， 辅助索引的一种 ， 和普通创建索引的方式一样，不同的是 可以同时添加多列来作为索引项;

从本质上来说，联合索引也是一课B+树

个人理解： 所谓最左原则， 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ，所以使用组合索引，必须满足 条件中必须存在 最左边那一列的索引项，这样 才可以找到对应的索引，继而 去寻找对应的数据

： 又叫做 索引覆盖，InnoDB中支持覆盖索引，即 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

比如 这里没有根据最左原则使用组合索引，但是 优化器依然进行选择

共勉，欢迎指导谢谢~

谈到索引，大家并不陌生。索引本身是一种数据结构，存在的目的主要是为了缩短数据检索的时间，最大程度减少磁盘 IO。

任何有数据的场景几乎都有索引，比如手机通讯录、文件系统（ext4\xfs\ntfs)、数据库系统（MySQL\Oracle）。数据库系统和文件系统一般都采用 B+ 树来存储索引信息，B+ 树兼顾写和读的性能，最极端时检索复杂度为 O(logN)，其中 N 指的是节点数量，logN 表示对磁盘 IO 扫描的总次数。

MySQL 支持的索引结构有四种：B+ 树，R 树，HASH，FULLTEXT。

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。索引不是万能的，索引可以加快数据检索 *** 作，但会使数据修改 *** 作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程度上弥补这一缺陷，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是暂时制止 MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但 MySQL 把同一个数据表里的索引总数限制为16个。

1．InnoDB 数据表的索引

与 InnoDB数据表相比，在 InnoDB 数据表上，索引对 InnoDB 数据表的重要性要大得多。在 InnoDB 数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。“数据行级锁定”的意思是指在事务 *** 作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE 命令以及 INSERT、UPDATE 和 DELETE 命令。出于效率方面的考虑，InnoDB 数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

2．限制

如果 WHERE 子句的查询条件里有不等号（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．外键索引

如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

5．复合索引

索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询 *** 作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。 在为 CHAR 和 VARCHAR 类型的数据列定义索引时，可以把索引的长度限制为一个给定的字符个数（这个数字必须小于这个字段所允许的最大字符个数）。这么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15 个字符已经足以把搜索范围缩小到很少的几条数据记录了。在为 BLOB 和 TEXT 类型的数据列创建索引时，必须对索引的长度做出限制；MySQL 所允许的最大索引全文索引文本字段上的普通索引只能加快对出现在字段内容最前面的字符串（也就是字段内容开头的字符）进行检索 *** 作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以的形式出现，这对 MySQL 来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

这类场合正是全文索引（full-textindex）可以大显身手的地方。在生成这种类型的索引时，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 列看到 usingtemporary 字样。

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

那么，这几种索引有什么功能和性能上的不同呢？

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表 *** 作的，可见，在数据量较大时是极其的耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间，当然这里不对异步IO作进一步讲解，想了解的童鞋，自行谷哥。

全文索引的使用方法并不复杂：

创建ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);

使用SELECT FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('word' MODE );

其中， MODE为搜寻方式（IN BOOLEAN MODE ，IN NATURAL LANGUAGE MODE ，IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION）。

关于这三种搜寻方式，愚安在这里也不多做交代，简单地说，就是，布尔模式，允许word里含一些特殊字符用于标记一些具体的要求，如+表示一定要有，-表示一定没有，表示通用匹配符，是不是想起了正则，类似吧；自然语言模式，就是简单的单词匹配；含表达式的自然语言模式，就是先用自然语言模式处理，对返回的结果，再进行表达式匹配。

对搜索引擎稍微有点了解的同学，肯定知道分词这个概念，FULLTEXT索引也是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割。但很明显，中文不能按照这种方式进行分词。那又怎么办呢？这个向大家介绍一个Mysql的中文分词插件Mysqlcft，有了它，就可以对中文进行分词，想了解的同学请移步Mysqlcft，当然还有其他的分词插件可以使用。

HASH

Hash这个词，可以说，自打我们开始码的那一天起，就开始不停地见到和使用到了。其实，hash就是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正是由于这个特性，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据（这里在概念上和函数映射有区别，不要混淆）。在java语言中，每个类都有自己的hashcode()方法，没有显示定义的都继承自object类，该方法使得每一个对象都是唯一的，在进行对象间equal比较，和序列化传输中起到了很重要的作用。hash的生成方法有很多种，足可以保证hash码的唯一性，例如在MongoDB中，每一个document都有系统为其生成的唯一的objectID（包含时间戳，主机散列值，进程PID，和自增ID）也是一种hash的表现。额，我好像扯远了-_-!

由于hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。那为什么还需要其他的树形索引呢？

在这里愚安就不自己总结了。引用下园子里其他大神的文章：来自 14的路 的MySQL的btree索引和hash索引的区别

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序 *** 作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

愚安我稍作补充，讲一下HASH索引的过程，顺便解释下上面的第4,5条：

当我们为某一列或某几列建立hash索引时（目前就只有MEMORY引擎显式地支持这种索引），会在硬盘上生成类似如下的文件：

hash值 存储地址

1db54bc745a1 77#45b5

4bca452157d4 76#4556,77#45cc…

…

hash值即为通过特定算法由指定列数据计算出来，磁盘地址即为所在数据行存储在硬盘上的地址（也有可能是其他存储地址，其实MEMORY会将hash表导入内存）。

这样，当我们进行WHERE age = 18 时，会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。

所以，每次查询时都要遍历hash表，直到找到对应的hash值，如（4），数据量大了之后，hash表也会变得庞大起来，性能下降，遍历耗时增加，如（5）。

BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新，反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番，不过那次考试好像没怎么考这个。如二叉树一样，每次查询都是从树的入口root开始，依次遍历node，获取leaf。

BTREE在MyISAM里的形式和Innodb稍有不同

在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息

而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息

RTREE

RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找

各种索引的使用情况

（1）对于BTREE这种Mysql默认的索引类型，具有普遍的适用性

（2）由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法，至少愚安我是经常这么做的。

（3）对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是你的选择。

（4）正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

（5）至于RTREE，愚安我至今还没有使用过，它具体怎么样，我就不知道了。有RTREE使用经历的同学，到时可以交流下！

以上就是关于mysql创建lower索引全部的内容，包括:mysql创建lower索引、mysql为啥建选不了普通索引、MySql 索引（聚集索引，辅助索引，联合索引，覆盖索引..）等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！