当前位置：Gxlcms > 数据库问题 > 索引-mysql索引创建、查看、删除及使用示例

索引-mysql索引创建、查看、删除及使用示例

时间：2021-07-01 10:21:17 帮助过：11人阅读

1.创建索引：

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> table_name <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">INDEX</span><span style="color: #000000"> index_name (column_list)
  2. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> table_name <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">UNIQUE</span><span style="color: #000000"> (column_list)
  3. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> table_name <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> (column_list)

CREATE INDEX可对表增加普通索引或UNIQUE索引。

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">INDEX</span> index_name <span style="color: #0000ff">ON</span><span style="color: #000000"> table_name (column_list)
  2. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">UNIQUE</span> <span style="color: #0000ff">INDEX</span> index_name <span style="color: #0000ff">ON</span> table_name (column_list)

2.删除索引：

  1. <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">INDEX</span> index_name <span style="color: #0000ff">ON</span><span style="color: #000000"> talbe_name
  2. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> table_name <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">INDEX</span><span style="color: #000000"> index_name
  3. </span><span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> table_name <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>

3.查看索引：

  1. mysql<span style="color: #808080">></span> show <span style="color: #0000ff">index</span> <span style="color: #0000ff">from</span><span style="color: #000000"> tblname;
  2. mysql</span><span style="color: #808080">></span> show keys <span style="color: #0000ff">from</span> tblname;

SQL代码在线格式化美化工具:http://tools.jb51.net/code/sqlcodeformat
4.索引的使用场景：

===========================以下为索引的使用场景(sqlserver)=================================

[1].漫谈数据库索引

1.创建表并插入数据

在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：

技术分享 
  1. <span style="color: #0000ff">USE<span style="color: #000000"> Test
  2. <span style="color: #0000ff">IF <span style="color: #808080">EXISTS (<span style="color: #0000ff">SELECT <span style="color: #808080">* <span style="color: #0000ff">FROM<span style="color: #000000"> INFORMATION_SCHEMA.TABLES 
  3.       <span style="color: #0000ff">WHERE TABLE_NAME <span style="color: #808080">= <span style="color: #ff0000">‘<span style="color: #ff0000">emp_pay<span style="color: #ff0000">‘<span style="color: #000000">)
  4.    <span style="color: #0000ff">DROP <span style="color: #0000ff">TABLE<span style="color: #000000"> emp_pay
  5. <span style="color: #0000ff">GO
  6. <span style="color: #0000ff">USE<span style="color: #000000"> Test
  7. <span style="color: #0000ff">IF <span style="color: #808080">EXISTS (<span style="color: #0000ff">SELECT name <span style="color: #0000ff">FROM<span style="color: #000000"> sys.indexes 
  8.       <span style="color: #0000ff">WHERE name <span style="color: #808080">= <span style="color: #ff0000">‘<span style="color: #ff0000">employeeID_ind<span style="color: #ff0000">‘<span style="color: #000000">)
  9.    <span style="color: #0000ff">DROP <span style="color: #0000ff">INDEX<span style="color: #000000"> emp_pay.employeeID_ind
  10. <span style="color: #0000ff">GO
  11. <span style="color: #0000ff">USE<span style="color: #000000"> Test
  12. <span style="color: #0000ff">GO
  13. <span style="color: #0000ff">CREATE <span style="color: #0000ff">TABLE<span style="color: #000000"> emp_pay
  14. (
  15.  employeeID <span style="color: #0000ff">int <span style="color: #808080">NOT <span style="color: #0000ff">NULL<span style="color: #000000">,
  16.  base_pay <span style="color: #0000ff">money <span style="color: #808080">NOT <span style="color: #0000ff">NULL<span style="color: #000000">,
  17.  commission <span style="color: #0000ff">decimal(<span style="color: #800000; font-weight: bold">2, <span style="color: #800000; font-weight: bold">2) <span style="color: #808080">NOT <span style="color: #0000ff">NULL<span style="color: #000000">
  18. )
  19. <span style="color: #0000ff">INSERT<span style="color: #000000"> emp_pay
  20.    <span style="color: #0000ff">VALUES (<span style="color: #800000; font-weight: bold">1, <span style="color: #800000; font-weight: bold">500, .<span style="color: #800000; font-weight: bold">10<span style="color: #000000">)
  21. <span style="color: #0000ff">INSERT<span style="color: #000000"> emp_pay 
  22.    <span style="color: #0000ff">VALUES (<span style="color: #800000; font-weight: bold">2, <span style="color: #800000; font-weight: bold">1000, .<span style="color: #800000; font-weight: bold">05<span style="color: #000000">)
  23. <span style="color: #0000ff">INSERT<span style="color: #000000"> emp_pay 
  24.    <span style="color: #0000ff">VALUES (<span style="color: #800000; font-weight: bold">6, <span style="color: #800000; font-weight: bold">800, .<span style="color: #800000; font-weight: bold">07<span style="color: #000000">)
  25. <span style="color: #0000ff">INSERT<span style="color: #000000"> emp_pay
  26.    <span style="color: #0000ff">VALUES (<span style="color: #800000; font-weight: bold">5, <span style="color: #800000; font-weight: bold">1500, .<span style="color: #800000; font-weight: bold">03<span style="color: #000000">)
  27. <span style="color: #0000ff">INSERT<span style="color: #000000"> emp_pay
  28.    <span style="color: #0000ff">VALUES (<span style="color: #800000; font-weight: bold">9, <span style="color: #800000; font-weight: bold">750, .<span style="color: #800000; font-weight: bold">06)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
技术分享

执行完上述sql代码以后我们会发现在Test数据库中多出了一张emp_pay表，数据库表的内容如下图所示：

技术分享

2.无索引查找

从上图我们可以看出数据库中存储的数据排列顺序与我们插入的先后顺序一致。接下来我们查询employeeID=5的字段，执行如下sql代码：

  1. <span style="color: #0000ff">USE<span style="color: #000000"> Test
  2. <span style="color: #0000ff">SELECT <span style="color: #808080">* <span style="color: #0000ff">FROM emp_pay <span style="color: #0000ff">where employeeID<span style="color: #808080">=<span style="color: #800000; font-weight: bold">5</span></span></span></span></span></span></span></span>

在SQL SERVER MANAGEMENT STUDIO中我们点击“显示估计的查询计划”，会出现如下图所示的查询计划图：

技术分享

其中表扫描的内容为：

技术分享

3.创建索引

接下来我们为上述表添加聚集唯一索引，代码如下：

  1. <span style="color: #0000ff">SET NOCOUNT <span style="color: #0000ff">OFF
  2. <span style="color: #0000ff">CREATE <span style="color: #0000ff">UNIQUE <span style="color: #0000ff">CLUSTERED <span style="color: #0000ff">INDEX<span style="color: #000000"> employeeID_ind
  3.    <span style="color: #0000ff">ON<span style="color: #000000"> emp_pay (employeeID)
  4. <span style="color: #0000ff">GO</span></span></span></span></span></span></span></span></span></span>

在执行完上述创建索引的代码以后，我们再次查询emp_pay的数据内容，如下图所示：

技术分享

从上图我们可以发现数据内容已经按照employeeID进行了排序。

我们继续执行前面关于employeeID=5的查询，点击“显示估计的执行计划”，出现如下图所示内容：

技术分享

聚集索引查找的内容为：

技术分享

总结：

当我们为数据库表中的某一个字段创建索引，并且在查询语句中where子句中用到这样一个字段，那么查询效率会有所提高，我们上述实验因为数据量的关系查询效率提高不明显。

补充

我们上面添加的索引是唯一聚集索引，因此当插入的数据在employeeID字段出现重复时会报错。假如我们在创建索引之前数据字段出现重复，那么就不能创建唯一索引。

创建索引以后的排序（PS:2012-5-28）

执行如下sql语句

  1. <span style="color: #0000ff">update emp_pay <span style="color: #0000ff">set employeeID<span style="color: #808080">=<span style="color: #800000; font-weight: bold">7 <span style="color: #0000ff">where employeeID<span style="color: #808080">=<span style="color: #800000; font-weight: bold">1;</span></span></span></span></span></span></span>

然后再次执行全表查询，我们发现查询结果如下所示：

技术分享

只要我们更新了employeeID，那么最后的更新结果都会按照employeeID的值进行升序排序。这是因为我们在employeeID上创建了索引的缘故。

删除索引（PS:2012-6-4）

我们可以通过sql server management studio这个工具删除索引，也可以通过sql语句进行索引的删除，假设我们要求删除在前面创建的索引employeeID_ind，那么sql语句如下代码所示：

  1. <span style="color: #0000ff">DROP <span style="color: #0000ff">INDEX employeeID_ind <span style="color: #0000ff">ON emp_pay;</span></span></span>

 

索引-mysql索引创建、查看、删除及使用示例

标签：tools   uniq   int   studio   eid   drop   gif   exist   src   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！