MySQL学习笔记--索引的创建，删除

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

<code>/*索引*/</code>

<code>/*索引设计原则</code>

<code>1.尽量选择唯一性索引</code>

<code>2.为经常需要</code><code>order</code> <code>by</code><code>,</code><code>group</code> <code>by</code><code>,</code><code>distinct</code><code>,</code><code>union</code><code>的字段设置索引</code>

<code>3.为常作为查询条件的字段设置索引</code>

<code>4.限制索引的数目</code>

<code>5.尽量使用数据量少的索引，索引值长，查询慢</code>

<code>6.尽量使用前缀来索引</code>

<code>7.删除不再使用的索引，或者很少使用的索引</code>

<code>*/</code>

<code>/*一般来说，应该在这些列上创建索引，例如：</code>

<code>       </code><code>第一、在经常需要搜索的列上，可以加快搜索的速度； </code>

<code>       </code><code>第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； </code>

<code>       </code><code>第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； </code>

<code>       </code><code>第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； </code>

<code>       </code><code>第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； </code>

<code>       </code><code>第六、在经常使用在</code><code>WHERE</code><code>子句中的列上面创建索引，加快条件的判断速度。*/</code>

<code>/*创建表时创建索引*/</code>

<code>--普通索引</code>

<code>use test</code>

<code>create</code> <code>table</code> <code>index1(id </code><code>int</code><code>,</code>

<code>name</code> <code>varchar</code><code>(20),</code>

<code>sex boolean,</code>

<code>index</code><code>(id)</code><code>--普通索引</code>

<code>)</code>

<code>show </code><code>create</code> <code>table</code> <code>index1</code><code>--检查创建表格语句,查看是否有索引存在，且索引名为'id',Ok </code>

<code>--使用explain语句查看索引是否被引用</code>

<code>explain </code><code>select</code> <code>* </code><code>from</code> <code>index1 </code><code>where</code> <code>id =1</code><code>--查到possible_keys和key都是‘id’，说明id索引存在，且被使用</code>

<code>--唯一性索引</code>

<code>create</code> <code>table</code> <code>index2(id </code><code>int</code> <code>unique</code><code>, </code><code>--若不进行Unique约束，也可以创建唯一性索引，但起不到提高查询速度的功能！</code>

<code>unique</code> <code>index</code> <code>index2_id(id </code><code>asc</code><code>) </code><code>--升序排列</code>

<code>--全文索引</code>

<code>create</code> <code>table</code> <code>index3(id </code><code>int</code><code>,</code>

<code>info </code><code>varchar</code><code>(20),</code>

<code>fulltext </code><code>index</code> <code>index3_info(info)</code>

<code>show </code><code>create</code> <code>table</code> <code>index3 </code><code>--验证一下,Innodb引擎的全文索引</code>

<code>--单列索引，可以为普通索引，也可以是唯一性索引，也可以是全文索引</code>

<code>create</code> <code>table</code> <code>index4(id </code><code>int</code><code>,</code>

<code>subject </code><code>varchar</code><code>(30),</code>

<code>index</code> <code>index4_st(subject(10)) </code><code>--subject单列10长度前缀索引</code>

<code>show </code><code>create</code> <code>table</code> <code>index4</code>

<code>--多列索引</code>

<code>create</code> <code>table</code> <code>index5(id </code><code>int</code><code>,</code>

<code>sex </code><code>char</code><code>(4),</code>

<code>index</code> <code>index5_ns(</code><code>name</code><code>,sex)</code><code>--多列索引中,只有查询条件使用了第一个字段时,索引才会被使用</code>

<code>explain </code><code>select</code> <code>* </code><code>from</code> <code>index5 </code><code>where</code> <code>name</code><code>=</code><code>'zrz'</code><code>--possible_keys和key都为index5_ns</code>

<a href="http://s4.51cto.com/wyfs02/M02/7F/FB/wKioL1cz0v6wC1n_AABHJVnQoA4723.png" target="_blank"></a>

<code>explain </code><code>select</code> <code>* </code><code>from</code> <code>index5 </code><code>where</code> <code>sex=</code><code>'boy'</code> <code>--possible_keys和key都为null;因为查询没有引用第一个字段</code>

<code>--空间索引，听说用不到，跳过</code>

<code>/*在已经存在的表上创建索引*/</code>

<code>/*直接添加*/</code>

<code>--创建普通索引</code>

<code>--补坑</code>

<code>create</code> <code>table</code> <code>example0(</code>

<code> </code><code>id </code><code>int</code><code>(11) </code><code>default</code> <code>null</code><code>,</code>

<code> </code><code>name</code> <code>varchar</code><code>(20) </code><code>default</code> <code>null</code><code>,</code>

<code> </code><code>sex tinyint(1) </code><code>default</code> <code>null</code>

<code> </code><code>)engine = innodb </code><code>default</code> <code>charset = utf8</code>

<code>create</code> <code>index</code> <code>index7_id </code><code>on</code> <code>example0(id) </code><code>--创建一个索引，名为Index7_id 在example的id列上</code>

<code>show </code><code>create</code> <code>table</code> <code>example0</code>

<a href="http://s2.51cto.com/wyfs02/M01/7F/FB/wKioL1cz0sSxwpOXAAAyOg-PEaw413.png" target="_blank"></a>

<code>--修改表创建唯一性索引</code>

<code>--替作者填坑，视频里面有的表竟然在书上不定义就直接引用。。。。</code>

<code>create</code> <code>table</code> <code>index8(</code>

<code>id </code><code>int</code><code>,</code>

<code>name</code> <code>varchar</code><code>(20)</code>

<code>);</code>

<code>create</code> <code>unique</code> <code>index</code> <code>index8_id </code><code>on</code> <code>index8(id) </code><code>--这里视频与书不同</code>

<code>show </code><code>create</code> <code>table</code> <code>index8 </code><code>--成功创建唯一性索引</code>

<code>--替作者填坑，视频里面有的表竟然在书上不定义就直接引用。</code>

<code>create</code> <code>table</code> <code>index9(</code>

<code>info </code><code>varchar</code><code>(50)</code>

<code>)engine=myisam</code>

<code>create</code> <code>fulltext </code><code>index</code> <code>index9_info </code><code>on</code> <code>index9(info)</code><code>--创建全文索引</code>

<code>show </code><code>create</code> <code>table</code> <code>index9</code><code>--成功创建</code>

<code>create</code> <code>table</code> <code>index10(</code>

<code>address </code><code>varchar</code><code>(20)</code>

<code>create</code> <code>index</code> <code>index10_addr </code><code>On</code> <code>index10(address(4) ) </code><code>--长度为4的单列前缀索引</code>

<code>show </code><code>create</code> <code>table</code> <code>index10 </code><code>--成功创建</code>

<a href="http://s3.51cto.com/wyfs02/M01/7F/FE/wKiom1cz0aLTn2O-AAA1aADyDYg686.png" target="_blank"></a>

<code>create</code> <code>table</code> <code>index11(id </code><code>int</code><code>,</code>

<code> </code><code>name</code> <code>varchar</code><code>(20),</code>

<code> </code><code>address </code><code>varchar</code><code>(50)</code>

<code>create</code> <code>index</code> <code>index11_na </code><code>on</code> <code>index11(</code><code>name</code><code>,address)</code><code>--在name,address上创建的多列索引</code>

<code>show </code><code>create</code> <code>table</code> <code>index11</code><code>--成功创建</code>

<code>/*使用</code><code>alter</code> <code>table</code><code>语句创建索引*//*暂时跳过这个实验，已有</code><code>create</code> <code>index</code> <code>xx </code><code>on</code> <code>table_c（） 的语句，且未发现与</code><code>alter</code> <code>table</code> <code>a </code><code>add</code> <code>index</code><code>()的区别*/</code>

<code>alter</code> <code>table</code> <code>a </code><code>add</code> <code>index</code> <code>index1(</code><code>name</code><code>(20))</code>

<code>alter</code> <code>table</code> <code>a </code><code>add</code> <code>index</code> <code>[</code><code>unique</code><code>|fulltext] index2(attribute_a)</code>

<code>alter</code> <code>table</code> <code>a </code><code>add</code> <code>index</code> <code>index3(attribute_a,attribute_b)</code>

<code>/*删除索引*/</code>

<code>show </code><code>create</code> <code>table</code> <code>index1 </code><code>--查看要被删除索引的名称，为id</code>

<code>drop</code> <code>index</code> <code>id </code><code>on</code> <code>index1 </code><code>--删除名为id，建在index1表上的索引</code>

<a href="http://s4.51cto.com/wyfs02/M00/7F/FB/wKioL1cz1KaT1p5-AAA7dZF4nhw324.png" target="_blank"></a>

 本文转自 angry_frog 51CTO博客，原文链接:http://blog.51cto.com/l0vesql/1772478