浅析mysql索引

数据库索引是一种数据结构，目的是提高表的操作速度。可以使用一个或多个列，提供快速随机查找和访问记录的高效排序来创建索引。

要创建的索引，应当认为哪列将用于使SQL查询，创建对这些列的一个或多个索引。

实际上，索引也是表，其中保存主键或索引字段的指针并指向每个记录到实际的表的类型。

用户无法看到索引，它们只是用来加速查询，并将被用于数据库搜索引擎在查找记录时提高速度。

INSERT和UPDATE语句需要更多的时间来创建索引，作为在SELECT语句快速在这些表上操作。其原因是，在执行插入或更新数据时，数据库需要将插入或更新索引值也更新。

简单和唯一索引

可以在表上创建唯一值索引。唯一索引意味着两行不能有相同的索引值。下面是在表上创建索引的语法：

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

可以使用一个或多个列来创建索引。例如，我们可以使用tutorial_author 来创建一个 tutorials_tbl 索引。

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

可以在表上创建一个简单的索引。创建简单的索引只是省略UNIQUE关键字。简单的索引可以在表中重复的值。

如果想索引的列的值按降序排列，可以列名之后添加保留字DESC。

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)

使用ALTER命令来添加和删除索引

有四种类型的索引可以添加到一个表：

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):  添加一个主键(PRIMARY KEY)，这意味着索引值必须是唯一的，而不能为空。
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):  创建一个索引的量的值必须是唯一的(除了不能使用NULL值，其它的可以出现多次)。
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list):  增加普通的索引，其中的任何值的出现多次。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): 创建一个用于文本搜索目的一种特殊的FULLTEXT索引。

下面是一个添加索引到现有表的例子。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

可以通过使用 ALTER 命令以及DROP子句来删除索引。试试下面的例子，用来删除上面创建的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

使用ALTER命令来添加和删除PRIMARY KEY

也可以用同样的方法添加主键。但要在列确保主键正常使用，需要指定使用 NOT NULL。

下面是一个例子添加主键在现有的表。列需要添加 NOT NULL 属性，然后再添加为一个主键。

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

可以使用ALTER命令删除主键如下：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

要删除索引的不是主键，必须指定索引名。

显示索引信息

可以使用SHOW INDEX命令，列出所有与表相关的索引。 垂直格式输出(由\G指定)，这是经常有用的语句，以避免长线概括输出：

试试下面的例子：

mysql> SHOW INDEX FROM table_name\G
........

总结

以上所述是小编给大家介绍的mysql索引，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！

赞 (0)

相关推荐

  • MySQL修改表一次添加多个列(字段)和索引的方法

    MySQL修改表一次添加多个列(字段) ALTER TABLE table_name ADD func varchar(50), ADD gene varchar(50), ADD genedetail varchar(50); MySQL修改表一次添加多个索引 ALTER TABLE  table_name ADD INDEX idx1 ( `func`), ADD INDEX idx2 ( `func`,`gene`), ADD INDEX idx3( `genedetail`); 以上这篇

  • 解决MySQL中IN子查询会导致无法使用索引问题

    解决MySQL中IN子查询会导致无法使用索引问题

    今天看到一篇关于MySQL的IN子查询优化的案例, 一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.) 随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, p

  • mysql为字段添加和删除唯一性索引(unique) 的方法

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • 浅析MySQL索引结构采用B+树的问题

    浅析MySQL索引结构采用B+树的问题

    目录 1.B树和B+树 2.原因分析 3.总结 一位6年经验的小伙伴去字节面试的时候被问到这样一个问题,为什么MySQL索引结构要采用B+树?这位小伙伴从来就没有思考过这个问题.只因为现在都这么卷,后面还特意查了很多资料,他也希望听听我的见解. 另外,我花了1个多星期把往期的面试题解析配套文档准备好了,一共有10万字,想获取的小伙伴可以在我的煮叶简介中找到. 1.B树和B+树 一般来说,数据库的存储引擎都是采用B树或者B+树来实现索引的存储.首先来看B树,如图所示. B树是一种多路平衡树,用这种

  • 浅析mysql索引

    数据库索引是一种数据结构,目的是提高表的操作速度.可以使用一个或多个列,提供快速随机查找和访问记录的高效排序来创建索引. 要创建的索引,应当认为哪列将用于使SQL查询,创建对这些列的一个或多个索引. 实际上,索引也是表,其中保存主键或索引字段的指针并指向每个记录到实际的表的类型. 用户无法看到索引,它们只是用来加速查询,并将被用于数据库搜索引擎在查找记录时提高速度. INSERT和UPDATE语句需要更多的时间来创建索引,作为在SELECT语句快速在这些表上操作.其原因是,在执行插入或更新数据时

  • 浅析MysQL B-Tree 索引

    B-Tree 索引 不同的存储引擎也可能使用不同的存储结构,i如,NDB集群存储引擎内部实现使用了T-Tree结构存储这种索引,即使其名字是BTREE:InnoDB使用的是B+Tree. B-Tree通常一位这所有的值都是按顺序存储的,并且每一个叶子页道根的距离相同.下图大致反应了InnoDB索引是如何工作的. 为什么mysql索引要使用B+树,而不是B树,红黑树 看完上面的文章就可以理解为何B-Tree索引能够快速访问数据了.因为存储引擎不再需要进行全表扫描获取需要的数据,叶子节点包含了所有元

  • 深入浅析MySQL从删库到跑路_高级（一）——数据完整性

    一.数据完整性简介 1.数据完整性简介 数据冗余是指数据库中存在一些重复的数据,数据完整性是指数据库中的数据能够正确反应实际情况. 数据完整性是指数据的可靠性和准确性,数据完整性类型有四种: A.实体完整性:实体的完整性强制表的标识符列或主键的完整性(通过唯一约束,主键约束或标识列属性). B.域完整性:限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则). C.引用完整性:在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完

  • 浅析MySQL 锁和事务

    MySQL本身也是在文件系统的基础上发展而来,因为锁的存在使之有所不同. MySQL作为一种数据库软件,难免会存在对其共享资源的并发访问,为了协调和管理不同资源的并发访问,也就产生了锁机制,因为锁机制的存在为数据库提供了数据的完整性和一致性. 从锁的级别来分锁可分为:行级锁.表级锁.页级锁. 从锁的类型来分锁可分为:共享锁.排它锁(独占锁). 为了协调行锁.表锁产生了:意向锁(表级锁). 共享锁,允许事务去读取数据. 排它锁,允许事务去修改或删除数据. 意向锁,获取行级锁的时候,自动添加的表级锁

  • 浅析MySQL的基数统计

    一.基数是啥? Cardinality指的就是MySQL表中某一列的不同值的数量. 如果这一类是唯一索引,那基数 = 行数. 如果这一列是sex,枚举类型只有男女,那它是基数就是2 Cardinality越高,列就越有成为索引的价值.MySQL执行计划也会基于Cardinality选择索引. 通过下面的方式可以看到表中各列的基数. 比如这个经典的例子: 有一列为sex,那对于sex列中存储的值来说 非男即女,它的基数最大就是2. 那也就完全没有必要为sex建立索引.因为,为了提升你基于sex的查

  • 浅析MySQL 主键使用数字还是uuid查询快

    浅析MySQL 主键使用数字还是uuid查询快

    在实际开发中mysql的主键不能重复,可能会采用主键自增,为了防止主键重复也可能会采取雪花算法之类的算法保证,这两种主键保存的都是number类型 但是实际开发中可能会生成uuid作为主键那么疑问来了,到底哪种主键的效率高呢? 下面由测试来验证: 1.首先我们先创建一个表,用存储过程生成100w条数据然后分析: 创建表: CREATE TABLE `my_tables` ( `id` VARCHAR(32) NOT NULL , `name` varchar(32) DEFAULT NULL,

  • 浅析Mysql和Oracle分页的区别

    目录 MySQL使用limit进行分页 Oracle使用rownum进行分页 Mysql与Oracle级联查询 Mysql省市区级联查询 Mysql与Oracle插入数据存在修改不存在新增 MySQL使用limit进行分页 select * from stu limit m,n; // m=(pageIndex-1)*pageSize,n=pageSize -- 返回总条,查询表添加字段sql_calc_found_rows select sql_calc_found_rows a.* from

  • 详解mysql索引总结----mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引. 一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

随机推荐