mysql 索引相关

索引是什么索引就像是一本书的目录索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。优点与缺点优点大大加快查询速度所有字段类型均可以设置索引缺点创建和维护索引需要时间，数据量越多，耗时越多索引占用存储空间，数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度使用原则和场景索引不是越多越好，需要视情况而定频繁更新的表应尽量少的索引频繁用于查询的字段进行构建索引数据量小的字段尽量不要使用索引，查询所有数据花费的时间比遍历索引的数据要短，索引将没有优化效果字段不同值少的字段尽量不要使用索引，如性别字段仅有男女两个不同值。索引分类注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引1. 单列索引一个索引只包含单个列，但一个表中可以有多个单列索引1.1. 普通索引MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。1.2. 唯一索引索引列中的值必须是唯一的，但是允许为空值1.3. 主键索引是一种特殊的唯一索引，不允许有空值2. 组合索引在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合3. 全文索引全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录4. 空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL索引创建和删除创建建表时创建CREATE TABLE 表名[字段名数据类型] [UNIQUE|FULLTEXT|SPATIAL|…] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]示例：CREATE TABLE NewTable ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR (255) NOT NULL, name VARCHAR (255) NOT NULL, sex TINYINT NOT NULL DEFAULT 0, address VARCHAR (255) NULL, PRIMARY KEY (id), # 主键索引 INDEX name (name) USING BTREE, # 普通索引 UNIQUE INDEX username (username) USING BTREE # 唯一索引 INDEX u_n_a (username, name,address) USING BTREE # 组合索引);已存在表创建ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]示例：ALTER TABLE testADD PRIMARY KEY (id), # 主键索引ADD INDEX name (name) USING BTREE , # 普通索引ADD UNIQUE INDEX username (username) USING BTREE , # 唯一索引ADD INDEX u_n_a (username, name, address) USING BTREE ; # 组合索引删除索引ALTER TABLE 表名 DROP INDEX 索引名。示例：ALTER TABLE testDROP PRIMARY KEY,DROP INDEX username,DROP INDEX name,DROP INDEX u_n_a;更新索引先删后建ALTER TABLE testDROP INDEX username ,ADD UNIQUE INDEX username1 (username) USING BTREE ,DROP INDEX name ,ADD INDEX name2 (name) USING BTREE ,DROP INDEX u_n_a ,ADD INDEX u_a_n (username, address, name) USING BTREE ;