【MySql】Mysql索引详解及优化(key和index区别)

key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）；index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储； 于是自己做了一个测试，测试的mysql版本：5.5.28 1.创建一个表

CREATE TABLE `index_test` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `key_1` bigint(20) NOT NULL,   `index_1` bigint(20) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.创建key

ALTER TABLE index_test ADD KEY key_1(key_1);

3.创建index

ALTER TABLE index_test ADD INDEX index_1(index_1);

4.查看sql

SHOW CREATE TABLE index_test;

显示：

CREATE TABLE `index_test` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `key_1` bigint(20) NOT NULL,   `index_1` bigint(20) NOT NULL,   PRIMARY KEY (`id`),   KEY `key_1` (`key_1`),   KEY `index_1` (`index_1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

也就是说创建key和创建index是一样的，在sql的体现上都是KEY；

在：https://dev.mysql.com/doc/refman/5.6/en/create-table.html 中有了解释：

也就是说key和index通常是一样的，这样是为了和其他数据库的兼容性而实现的。 但是并不是所有的key和index都是一样的，如果创建的是约束，比如PRIMARY KEY，FOREIGN KEY这种表示约束的，只能用key。  

 

MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

普通索引 普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = ...)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。 1.直接创建索引(length表示使用名称前1ength个字符) CREATE INDEX index_name ON table_name(column_name(length)) 2.修改表结构的方式添加索引 ALTER TABLE table_name ADD INDEX index_name ON (column_name) 3.创建表的时候同时创建索引 CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`), INDEX index_name (title) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 4.删除索引 DROP INDEX index_name ON table_name; 5.建立复合索引 CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id); 注意命名时的习惯了吗？使用"表名_字段1名_字段2名"的方式 唯一索引 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。 –创建唯一索引 CREATE UNIQUE INDEX index_name ON table_name(column_name) –修改表结构 ALTER TABLE table_name ADD UNIQUE index_name ON (column_name) –创建表的时候直接指定 CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`), UNIQUE index_name (title) );

3.主索引 在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的"主索引"。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

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

5. 全文索引（FULLTEXT） MySQL从3.23.23版开始支持全文索引和全文检索，fulltext索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。 文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。 　　 这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加： ALTER TABLE table_name ADD FULLTEXT(column1, column2) 　　 有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法：

SELECT * FROM table_name WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')

上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

–创建表的适合添加全文索引 CREATE TABLE `table_name` ( `id` int(11) NOT NULL AUTO_INCREMENT , `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , PRIMARY KEY (`id`), FULLTEXT (content) ); –修改表结构添加全文索引 ALTER TABLE table_name ADD FULLTEXT index_name(column_name) –直接创建索引 CREATE FULLTEXT INDEX index_name ON table_name (column_name)

6. 单列索引、多列索引 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

7. 组合(复合)索引（最左前缀） 平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引： ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。 建立这样的组合索引，其实是相当于分别建立了下面两组组合索引： –title,time –title 为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示 –使用到上面的索引 SELECT * FROM article WHREE title='测试' AND time=1234567890; SELECT * FROM article WHREE title='测试'; –不使用上面的索引 SELECT * FROM article WHREE time=1234567890;

MySQL索引的优化

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。1. 何时使用聚集索引或非聚集索引？

屏幕快照 2018-06-21 下午4.45.01.png

 

2. 索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序 MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “