当前位置：文档之家› 由浅入深探究mysql索引结构原理、性能分析与优化

由浅入深探究mysql索引结构原理、性能分析与优化

摘要：

第一部分：基础知识

第二部分：MYISAM和INNODB索引结构

1、简单介绍B-tree B+ tree树

2、MyisAM索引结构

3、Annode索引结构

4、MyisAM索引与InnoDB索引相比较

第三部分：MYSQL优化

1、表数据类型选择

2、sql语句优化

(1)最左前缀原则

(1.1)能正确的利用索引

(1.2)不能正确的利用索引

(1.3)如果一个查询where子句中确实不需要password列，那就用“补洞”。

(1.4)like

(2)Order by 优化

(2.1)filesort优化算法.

(2.2)单独order by 用不了索引，索引考虑加where 或加limit

(2.3)where + orerby 类型，where满足最左前缀原则，且orderby的列和where子句用到的索引的列的

子集。即是(a,b,c)索引，where满足最左前缀原则且order by中列a、b、c的任意组合

(2.4) where + orerby+limit

(2.5)如何考虑order by来建索引

(3)隔离列

(4)OR、IN、UNION ALL，可以尝试用UNION ALL

(4.1)or会遍历表就算有索引

(4.2)关于in

(4.2)UNION All

(5)范索引选择性

(6)重复或多余索引

3、系统配置与维护优化

(1)重要的一些变量

(2)Fds optimize、Analyze、check、repair维护操作

(3)表结构的更新与维护

第四部分：图说mysql查询执行流程

第一部分：基础知识：

索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的资料。关键字index

-------------------------------------------------------------

唯一索引

强调唯一，就是索引值必须唯一，关键字unique index

创建索引：

1、create unique index索引名on 表名(列名);

2、alter table 表名add unique index索引名(列名);

删除索引：

1、drop index 索引名on 表名;

2、alter table 表名drop index 索引名;

主键

主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increatment列，关键字是primary key

主键创建：

creat table test2 (id int not null primary key auto_increment);

-------------------------------------------------------------

全文索引

InnoDB不支持，Myisam支持性能比较好，一般在CHAR、VARCHAR 或TEXT 列

上创建。

Create table 表名( id int not null primary anto_increment,title

varchar(100),FULLTEXT(title))type=myisam

------------------------------

单列索引与多列索引

索引可以是单列索引也可以是多列索引（也叫复合索引）。按照上面形式创建出来的索引是单列索引，现在先看看创建多列索引：

create table test3 (id int not null primary key auto_increment,uname char (8) not null default '',password char(12) not null,INDEX(uname,password))type =myisam;

注意：INDEX(a, b, c)可以当做a或(a, b)的索引来使用，但和b、c或(b,c)的索引来使用这是一个最左前缀的优化方法，在后面会有详细的介绍，你只要知道有这样两个概念-------------------------------------------------------------

聚集索引

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引确定表

中数据的物理顺序。Mysql中myisam表是没有聚集索引的，innodb有（主键就是聚集索引），聚集索引在下面介绍innodb结构的时有详细介绍。

查看表的索引

通过命令：Show index from 表名

如：

1mysql> show index from test3;

2+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+

4Packed | Null | Index_type | Comment |

5+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+

6| test3 | 0 | PRIMARY| 1 | id | A | 0 | NULL |

7NULL| | BTREE | |

8+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

Table：表名

Key_name：什么类型索引（这了是主键）

Column_name：索引列的字段名

Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引

Index_type：如果索引是全文索引，则是fulltext,这里是b+tree索引，b+tre也是这篇文章研究的重点之一

其他的就不详细介绍，更多：

第二部分：MYISAM和INNODB索引结构

1、简单介绍B-tree B+ tree树

B-tree结构视图

一棵m阶的B-tree树，则有以下性质

（1）Ki表示关键字值，上图中，k1

（2）Pi表示指向子节点的指针，左指针指向左子节点，右指针指向右子节点。即是：p1[指向值]

（3）所有关键字必须唯一值（这也是创建myisam 和innodb表必须要主键的原因），每个节点包含一个说明该节点多少个关键字，如上图第二行的i和n

（4）节点：

●每个节点最可以有m个子节点。

●根节点若非叶子节点，至少2个子节点，最多m个子节点

●每个非根，非叶子节点至少[m/2]子节点或叫子树（[]表示向上取整），最多m

个子节点

（5）关键字：

●根节点的关键字个数1~m-1

●非根非叶子节点的关键字个数[m/2]-1~m-1,如m=3，则该类节点关键字个数：

2-1~2

（6）关键字数k和指向子节点个数指针p的关系：

●k+1=p ，注意根据储存数据的具体需求，左右指针为空时要有标志位表示没有B+tree结构示意图如下：

B+树是B-树的变体，也是一种多路搜索树：

●非叶子结点的子树指针与关键字个数相同

●为所有叶子结点增加一个链指针（红点标志的箭头）

B+树是B-树的变体，也是一种多路搜索树：

●非叶子结点的子树指针与关键字个数相同

●为所有叶子结点增加一个链指针（红点标志的箭头）

2、MyisAM索引结构

MyisAM索引用的B+tree来储存数据，MyisAM索引的指针指向的是键值的地址，地址存储的是数据，如下图：

（1）结构讲解：上图3阶树，主键是Col2，Col值就是改行数据保存的物理地址，其中红色部分是说明标注。

●1标注部分也许会迷惑，前面不是说关键字15右指针的指向键值要大于15，怎么下面还有15关键字？因为B+tree的所以叶子节点包含所有关键字且是按照升序排列（主键索引唯一，辅助索引可以不唯一），所以等于关键字的数据值在右子树

●2标注是相应关键字存储对应数据的物理地址，注意这也是之后和InnoDB索引不同的地方之一

●2标注也是一个所说MyiAM表的索引和数据是分离的，索引保存在”表名.MYI”文件内，而数据保存在“表名.MYD”文件内，2标注的物理地址就是“表名.MYD”文件内相应数据的物理地址。（InnoDB表的索引文件和数据文件在一起）

●辅助索引和主键索引没什么大的区别，辅助索引的索引值是可以重复的（但InnoDB 辅助索引和主键索引有很明显的区别，这里先提醒注意一下）

3、Annode索引结构

（1）首先有一个表，内容和主键索引结构如下两图：Col1Col2Col3

115phpb en

220mhyc oe

323phpyu

425bearp a

540phpg oo

645phph ao

748phpxu e

……

结构上：由上图可以看出InnoDB的索引结构很MyisAM的有很明显的区别

●MyisAM表的索引和数据是分开的，用指针指向数据的物理地址，而InnoDB表中索引和数据是储存在一起。看红框1可一看出一行数据都保存了。

●还有一个上图多了三行的隐藏数据列（虚线表），这是因为MyisAM不支持事务，InnoDB处理事务在性能上并发控制上比较好，看图中的红框2中的DB_TRX_ID是事务ID，自动增长；db_roll_ptr是回滚指针，用于事务出错时数据回滚恢复；db_row_id 是记录行号，这个值其实在主键索引中就是主键值，这里标出重复是为了容易介绍，还有的是若不是主键索引（辅助索引），db_row_id会找表中unique的列作为值，若没有unique列则系统自动创建一个。关于InnoDB跟多事务MVCC点此：https://www.doczj.com/doc/3a16135171.html,/?post=72

（2）加入上表中Col1是主键（下图标错），而Col2是辅助索引，则相应的辅助索引结构图：

可以看出InnoDB辅助索引并没有保存相应的所有列数据，而是保存了主键的键值（图中1、2、3….）这样做利弊也是很明显：

●在已有主键索引，避免数据冗余，同时在修改数据的时候只需修改辅助索引值。

●但辅助索引查找数据事要检索两次，先找到相应的主键索引值然后在去检索主键索

引找到对应的数据。这也是网上很多mysql性能优化时提到的“主键尽可能简短”

的原因，主键越长辅助索引也就越大，当然主键索引也越大。

4、MyisAM索引与InnoDB索引相比较

● MyisAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持

● AnnoDB支持事务，MyisAM不支持

● MyisAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引

相差无几；AnnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值

● MyisAM键值分离，索引载入内存（key_buffer_size）,数据缓存依赖操作系统；

InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池

● MyisAM主键（唯一）索引按升序来存储存储，InnoDB则不一定

● MyisAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，

InnoDB则是估计值。这里涉及到信息统计的知识，MyisAM统计信息是保存磁盘

中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内

MyisAM处理字符串索引时用增量保存的方式，如第一个索引是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance‘，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引

第三部分：MYSQL优化

mysql优化是一个重大课题之一，这里会重点详细的介绍mysql优化，包括表数据类型选择，sql语句优化，系统配置与维护优化三类。

1、表数据类型选择

（1）能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。

（2）避免用NULL，这个也是网上优化技术博文传的最多的一个。理由是额外增加字节，还有使索引，索引统计和值更复杂。很多还忽略一

个count(列)的问题，count(列)是不会统计列值为null的行数。更多关于NULL 可参考：https://www.doczj.com/doc/3a16135171.html,/?post=71

（3）字符串如何选择char和varchar？一般phper能想到就是char是固定大小，varchar能动态储存数据。这里整理一下这两者的区别：

属性

Char Varchar

值域大小最长字符数是255（不是

字节），不管什么编码，

超过此值则自动截取255

个字符保存并没有报错。

65535个字节，开始两位

存储长度，超过255个字

符，用2位储存长度，否则

1位，具体字符长度根据编码来确定，如utf8

则字符最长是21845个

如何处理字符串末尾空

格去掉末尾空格，取值出来

比较的时候自动加上进

行比较

Version<=4.1，字符串末

尾空格被删掉，

version>5.0则保留

储存空间固定空间，比喻char(10)

不管字符串是否有10个

字符都分配10个字符的

空间

Varchar内节约空间，但

更新可能发生变化，若

varchar(10),开始若储存

5个字符，当update成7

个时有myisam可能把行

拆开，innodb可能分页，

这样开销就增大

适用场合适用于存储很短或固定

或长度相似字符，如

MD5加密的密码

char(33)、昵称char(8)

等

当最大长度远大于平均长

度并且发生更新的时候。

注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1（4）整型、整形优先原则

Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。值域范围：-2^(n-1)~ 2^(n-1)-1

很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题

笔者建议：能用tinyint的绝不用smallint

误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50

（5）精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储

数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL 类型的数据将会转换成DOUBLE类型。

2、sql语句优化

9mysql> create table one (

10id smallint(10) not null auto_increment primary key,

11username char(8) not null,

12password char(4) not null,

13`level` tinyint (1) default 0,

14last_login char(15) not null,

15index(username,password,last_login))engine=innodb;

这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.

17Index_type | Comment |

18+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

19| one | 0 | PRIMARY| 1 | id | A |20242 | NULL | NULL| |

20BTREE | |

21+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

22| one | 1 | username | 1 | username | A |10121 | NULL | NULL| |

23BTREE | |

24+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

25| one | 1 | username | 2 | password| A |10121 | NULL | NULL| YES |

26BTREE | |

27+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

28| one | 1 | username | 3 | last_login | A |20242 | NULL | NULL| |

29BTREE | |

30+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

(1)最左前缀原则

定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的

顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。

举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不从username开始，(username,last_login)---断层，少了password，都无法利用到索引。

因为B+tree多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序

检索

测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。

还有的是以下的测试用的时间0.00sec看不出什么时间区别，因为数据量只有20003

条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前缀原则对与提高性能方面是不可否认的。

Ps：最左前缀原则中where字句有or出现还是会遍历全表

(1.1)能正确的利用索引

Where子句表达式顺序是(username)

31mysql> explain select * from one where username='abgvwfnt';

32+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

34+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

35| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |

36+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

37 1 row in set (0.00 sec)

●Where子句表达式顺序是(username,password)

38mysql> explain select * from one where username='abgvwfnt'and password='123456';

39+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

41+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

42| 1 | SIMPLE | one | ref | username | username | 43 | const,const |

1 | Using where |

43+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

44 1 row in set (0.00 sec)

●Where子句表达式顺序是(username,password, last_login)

45mysql> explain select*from one where username='abgvwfnt'and password='123456'and last_login='1338251170';

46+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

48+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

49| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const |

1 | Using where |

50+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

51 1 row in set (0.00 sec)

上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row 分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到索引。

(1.2)不能正确的利用索引

●Where子句表达式顺序是(password, last_login)

52mysql> explain select* from one where password='123456'and last_login='1338251170';

53+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

54| id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | 55+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

56| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where |

57+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

58 1 row in set (0.00 sec)

●Where 子句表达式顺序是(last_login)

59mysql> explain select * from one where last_login='1338252525';

60+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

61| id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | 62+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

63| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20146 | Using where |

64+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

65 1 row in set (0.00 sec)

以上的两条语句都不是以username开始，这样是用不了索引，通过type=all（全表扫描），key_len=null，rows都很大20146

Ps：one表里只有20003条数据，为什么出现20146，这是优化器对表的一个估算值，不精确的。

●Where 子句表达式虽然顺序是(username,password, last_login)或

(username,password)但第一个是有范围’<’、’>’，’<=’，’>=’等出现

66mysql> explain select* from one where username>'abgvwfnt'and password ='123456'and last_login='1338251170';

67+----+-------------+-------+------+---------------+------+---------+------+-------+-------

------+

68| id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | 69+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

70| 1 | SIMPLE | one | ALL| username | NULL| NULL| NULL| 20146 | Using where |

71+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

72 1 row in set (0.00 sec)

这个查询很明显是遍历所有表，一个索引都没用到，非第一列出现范围（password列或last_login列），则能利用索引到首先出现范围的一列，也就是“where username='abgvwfnt' and password >'123456'and last_login='1338251170';”或则“where username='abgvwfnt' and password >'123456'and last_login<'1338251170';”索引长度ref_len=43,索引检索到password列，所以考虑多列索引的时候把那些查询语句用的比较的列放在最后（或非第一位）。

断层，即是where顺序(username, last_login)

73mysql> explain select*from one where username='abgvwfnt'and last_login='1338252525';

74+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

76+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

77| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |

78+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

79 1 row in set (0.00 sec)

注意这里的key_len=24=8*3(8是username的长度，3是utf8编码)，rows=5，和下面一条sql语句搜索出来一样

80mysql> select * from one where username='abgvwfnt';

81+-------+----------+----------+-------+------------+

83+-------+----------+----------+-------+------------+

84| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |

85| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |

86| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |

87| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |

88| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

89+-------+----------+----------+-------+------------+

90 5 rows in set (0.00 sec)

92mysql> select * from one where username='abgvwfnt'and last_login='1338252525';

93+-------+----------+----------+-------+------------+

95+-------+----------+----------+-------+------------+

96| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

97+-------+----------+----------+-------+------------+

98 1 row in set (0.00 sec)

这个就是要的返回结果，所以可以知道断层(username,last_login)，这样只用到username索引，把用到索引的数据再重新检查last_login条件，这个相对全表查询来说还是有性能上优化，这也是很多sql优化文章中提到的where 范围查询要放在最后

（这不绝对，但可以利用一部分索引）

(1.3)如果一个查询where子句中确实不需要password列，那就用“补洞”。

99mysql> select distinct(password) from one;

100+----------+

101| password |

102+----------+

103| 234567 |

104| 345678 |

105| 456789 |

106| 123456 |

107+----------+

1084 rows in set (0.08 sec)

可以看出password列中只有这几个值，当然在现实中不可能密码有这么多一样的，再

说数据也可能不断更新，这里只是举例说明补洞的方法

109mysql> explain select* from one where username='abgvwfnt'and password in('123456','234567','345678','456789')

110and last_login='1338251170';

111+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

113+----+-------------+-------+-------+---------------+----------+---------+------+------+---

----------+

114| 1 | SIMPLE | one | range | username | username| 83 | NULL|4 | Using where |

115+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1161 row in set (0.00 sec)

可以看出ref=83 所有的索引都用到了，type=range是因为用了in子句。

这个被“补洞”列中的值应该是有限的，可预知的，如性别，其值只有男和女（加多一个不男不女也无妨）。

“补洞”方法也有瓶颈，当很多列，且需要补洞的相应列（可以多列）的值虽有限但很多（如中国城市）的时候，优化器在优化时组合起来的数量是很大，这样的话就要做好基准测试和性能分析，权衡得失，取得一个合理的优化方法。

(1.4)like

117mysql> explain select * from one where username like'abgvwfnt%';

118+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

119| id | select_type | table | type | possible_keys | key| key_len | ref |

120rows | Extra |

121+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

122| 1 | SIMPLE | one | range | username | username | 24 | NULL |

123 5 | Using where |

124+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1251 row in set (0.00 sec)

126mysql> explain select * from one where username like'%abgvwfnt%';

127+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

128| id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | 129+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

130| 1 | SIMPLE | one | ALL| NULL| NULL| NULL| NULL| 20259 | Using where |

131+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1321 row in set (0.01 sec)

对比就知道like操作abgvwfnt%能用到索引，%abgvwfnt%用不到

---------------------------------------------------------------------------------------------

(2)Order by 优化

(2.1)filesort优化算法.

在mysql version()<4.1之前，优化器采用的是filesort第一种优化算法，先提取键值和指针，排序后再去提取数据，前后要搜索数据两次，第一次若能使用索引则使用，第二次是随机读（当然不同引擎也不同）。mysql version()>=4.1,更新了一个新算法，就是在第一次读的时候也把selcet的列也读出来，然后在sort_buffer_size中排序（不够大则建临时表保存排序顺序），这算法只需要一次读取数据。所以有这个广为人传的一个优化方法，那就是增大sort_buffer_size。Filesort第二种算法要用到更的空间，sort_buffer_size不够大反而会影响速度，所以mysql开发团队定了个变量max_length_for_sort_data，当算法中读出来的需要列的数据的大小超过该变量的值才使用，所以一般性能分析的时候会尝试把max_length_for_sort_data改小。

(2.2)单独order by 用不了索引，索引考虑加where 或加limit

先建一个索引(last_login),建的过程就不给出了

133mysql> explain select * from one order by last_login desc;

134+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

136| Extra |

137+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

138| 1 | SIMPLE | one | ALL| NULL| NULL | NULL| NULL | 2046 1393 | Using filesort |

140+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

1411 row in set (0.00 sec)

142

143mysql> explain select * from one order by last_login desc limit 10;

144+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+

145| id | select_type | table | type | possible_keys | key| key_len | ref

146| rows | Extra |

147+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+

mysql数据库索引优化

我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的[url=javascript:;]技术[/url]，但是最有效的莫过于恰当地使用索引了。在MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。）索引对单个表查询的影响索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000 行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。例如对下面这样的一个student表： mysql>SELECT * FROM student +------+---------+---------+---------+---------+ | id | name | english | chinese | history | +------+---------+---------+---------+---------+ | 12 | Tom | 66 | 93 | 67 | | 56 | Paul | 78 | 52 | 75 | | 10 | Marry | 54 | 89 | 74 | | 4 | Tina | 99 | 83 | 48 | | 39 | William | 43 | 96 | 52 | | 74 | Stone | 42 | 40 | 61 | | 86 | Smith | 49 | 85 | 78 | | 37 | Black | 49 | 63 | 47 | | 89 | White | 94 | 31 | 52 | +------+---------+---------+---------+---------+ 这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生： mysql>SELECT name,english FROM student WHERE english<60; +---------+---------+ | name | english | +---------+---------+ | Marry | 54 | | William | 43 | | Stone | 42 | | Smith | 49 |

2013电大数据库原理与应用作业答案3

一、单项选择题（共20 道试题，共40 分。） 1. 在T-SQL语法中，Select语句的完整语法较复杂，但至少包括的部分为（）。 A. Select，Into B. Select，From C. Select，Group D. 仅Select 2. 下列（）统计函数可以计算平均值。 A. Sum B. Avg C. Count D. Min 3. 下列叙述中不是视图的特点的是（）。 A. 为用户集中数据 B. 降低数据库设计的复杂性 C. 存储数据 D. 组织数据以便导出到其他应用程序中 4. （）必须确保索引键不包含重复的值。 A. 聚集索引 B. 非聚集索引 C. 索引视图 D. 唯一索引 5. 对于Update语句的实现说法正确的是（）。 A. Update一次只能修改一列的值 B. Update只能修改不能赋值 C. Update可以指定要修改的列和赋予的新值

D. Update不能加Where条件 6. T-SQL对标准SQL的扩展主要表现为（）。 A. 加入了程序控制结构和变量 B. 加入了建库和建表语句 C. 提供了分组（Group by）查询功能 D. 提供了Min、Max等统计函数 7. SQL Server的字符型系统数据类型主要包括（）。 A. Int、Money、Char B. Char、Varchar、Text C. Datetime、Binary、Int D. Char、Varchar、Int 8. 在T-SQL语法中，用来插入数据的命令和用于更新的命令分别是（）。 A. Insert，Update B. Update，Insert C. Delete，Update D. Create，Insert Into 9. 执行哪一个系统存储过程，可以查看视图的定义信息（）。 A. sp_helptext B. sp_depends C. sp_help D. sp_rename 10. 下列的SQL语句中，（）不是数据定义语句。 A. Create Table B. Drop View C. Create View

如何优化数据库,提高查询效率

龙源期刊网 https://www.doczj.com/doc/3a16135171.html, 如何优化数据库，提高查询效率作者：代鸿彬来源：《学习与科普》2019年第10期摘要：随着信息时代的到来，生活和工作当中已经无法避免的需要和计算机打交道，和计算机打交道的同时就必须要用到数据库。数据库系统是计算机当中的一项重要系统，储存在用户的关键信息，不仅对个人影响很大，同时对企事业单位也有着重要影响。关键词：信息时代；数据库；索引数据库是信息的载体也是数据的最佳表现形式，它的共享性导致了数据会被大量的搜索查询，为了提高查询的效率，就不得不对数据库进行优化。一、利用索引进行优化。索引是数据库的重要组成部分，也是使用者根据需要进行查询最直接的方法，优化索引可以提高查询的效率。当前的数据库当中大部分还是使用国际商业机器公司以前的索引顺序存取方法，对于用户来说肯定会选择方便、快捷的索引方式，怎么方便怎么来。在建立索引的时候针对不同的内容，需要建立不同的连接方式，但是随着用户的增多，查询内容和方向的多元化，这就造成了在实际工作当中经常会有使用频率很少的索引出现，甚至也会出现没有查询所需的索引，这种情况可以通过查询优化器进行自动生成的索引进行查询。对于使用频率较为频繁的列，需要对其进行排序或者分组的列上建立索引时，要优化索引提高效率，对于使用频率很少的列可以不建立索引。二、简化排序进行优化。对于部分企事业单位需要排序的内容很多时，就要使用大型数据表来满足查询需求，但是大型数据表涉及的内容很多，为了避免出现重复排序的现象需要对数据表进行简化。在大型数据表当中有一部分的内容可以自动进行排序的次序输出，这时就可以直接利用查询优化器进行优化，将复杂的排序简单化，从而提高索引查询效率。需要排序的列对索引优化影响较大，就像语言当中的ORDER BY 或者GROUP BY句子当中的列次序和索引当中的列次序基本是不同的，但是排序的列可通过表的不同形式表现出来。通过简化排序避免了重复的排序，并且将数据库进行了合理的合并。如果不进行简化排序，就需要将排序的范围进行缩小简化，从而提高查询使用的效率。三、大型表行数据库存取的合理消除。数据库系统的存储量是有上限的，所有的索引内容都占有数据库空间，尤其是大型数据表占有的空间更大，将会造成索引时间变长。但是大型表行数据有些内容是不必要的，在进行索引查詢时，数据表当中的存取顺序对查询的效率有直接的影响。例如需要采用存取策略时，通

数据库索引的优缺点及使用时的注意事项

本文介绍了数据库索引，及其优、缺点。针对MySQL索引的特点、应用进行了详细的描述。分析了如何避免MySQL无法使用，如何使用EXPLAIN分析查询语句，如何优化MySQL索引的应用。索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。注：[1]索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL 在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。[2]另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。 1. InnoDB数据表的索引与MyISAM数据表相比，索引对InnoDB数据的重要性要大得多。在InnoDB数据表上，索引对InnoDB数据表的重要性要在得多。在InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的苊、基础。"数据行级锁定"的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到(但不限于)SELECT...LOCK IN SHARE MODE、SELECT...FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。 2. 限制如果WEHERE子句的查询条件里有不等号(WHERE coloum != ...)，MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = ...)，MySQL也将无法使用索引。在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数据类型相同时才能使用索引。

数据库原理索引、视图的定义实验报告

数据库原理实验报告题目：索引、视图的定义院系：计算机科学与工程学院

【实验题目】索引、视图的定义【实验目的】掌握使用T -SQL语句创建视图的方法，包括视图的建立、删除、修改；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。掌握创建索引的方法。【实验内容】 1、据库TestDB中，基于表"项目数据表"和"员工数据表"创建视图，要求为: (1)视图名为"员工项目"。 (2)包含字段"编号"、"姓名"、"名称"和"开始日期"。 (3)字段别名分别是"员工编号"、”员工姓名"、"项目名称"、"项目开始日期"。 2、使用企业管理器和Transact-SQL语句在实验二的数据表"员工数据表"中基于"姓名"创建索引，要求索引名为"IDX_Name"，索引类型为非聚集索引。【实现方法】 1、视图 (1)打开查询分析器。在查询窗口书写CREATE VIEW语句创建视图，并指定字段别名： USE TestDB GO CREATE VIEW员工项目(员工编号，员工姓名，项目名称，项目开始日期) AS SELECT a·编号，a·姓名，b·名称，b·开始日期， FROM员工数据表AS a INNER JOIN项目数据表AS b ON a·编号=b·负责人 WHERE a·编号=b·负责人 GO (2)使用INSERT语句通过视图向员工数据表中添加一条记录，要求"姓名"字段值为"马中兴"。 USETestDB GO INSERTINTO 员工项目(员工姓名) VALUES('马中兴') GO (3)使用UPPDATE语句通过视图将第二步中插入记录的员工姓名改为"马中新"。 USETestDB GO UPDATE员工项目 SET 项目负责人= '马中新’， WHERE 项目负责人=’马中兴’

SQLSERVER索引及优化详解

SqlServer索引及优化详解（一）深入浅出理解索引结构实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。（二）何时使用聚集索引或非聚集索引下面的表总结了何时使用聚集索引或非聚集索引（很重要）。

数据库索引概论及详解

记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 使用索引，在一般情况下，将能明显提高查询的性能，但系统为维护索引，也必将增加许多额外的开销。所以，何时应建立索引，查询时是否使用索引，对系统性能的影响将是非常大的。在这里，我想对这个问题谈一下自己的认识。首先，在下列情况下，不适合建立索引： 1、表的规模不大，在这种情况下，直接查找表的开销比搜索索引再定位的开销要小。 2、表被频繁更新，在这种情况下，维护索引的开销要大于使用索引所带来的性能提高。 3、表上已经建立了许多索引。 4、用户的查询方式经常发生变化。上述这些情况都是比较直观的，但是，即使建立了索引，在具体查询时，系统也未必会使用该索引。不管是何种数据库系统，其查询优化过程由两个层次构成：代数优化（或称基于规则的优化）和物理优化（或称基于代价的优化）（部分数据库系统可能不含物理优化过程）。代数优化是使用一组预定义的规则来对查询进行优化，在这种优化方式下，如果表上建有索引，系统将使用该索引。物理优化是在代数优化的基础上，根据物理统计信息，来估计各种执行方案的执行代价，从中选取一种最优（代价最小）的执行方案。在这种优化方式下，如果表上建有索引，是否使用索引，将取决于查询的 “选中度”（selectivity）。什么是选中度？举个例子，假设表中有一名为“年龄”的字段，有一查询需要查出该表中所有“年龄”不超过50岁的记录，如果表中有70% 的记录满足这一条件，则称该查询的选中度为70。当选中度超过某一预先给定的值P（P的大小取决于系统的具体实现）时，遍历整个表的开销比搜索索引再定位的开销要小，此时系统将不使用索引。通过统计字段的值分布，可以估计查询的选中度，如果它大于P，系统将不使用索引，直接遍历表。这是一种非常重要的统计信息，它还可用于估计连接操作结果集的大小。当然，当查询比较固定时，用户也可以根据自己对应用的理解预先估

MySQL优化自学手册

/* * ------------------------------------------------------------------- * |-标题：MySQL优化自学手册 * |-整理: 杨白玉 * |-时间: 2015年9月25日 * ------------------------------------------------------------------- */ mysql优化前提：数据库性能的优劣直接影响到程序的性能，所以数据库的设计与参数配置至关重要。数据库优化的方式： 1、数据库设计 2、sql语句的优化 3、数据库参数的配置（扩展数据库的缓存或者数据库的空间） 4、恰当的硬件资源（钱的问题，有钱就能满足）

第一章数据库的设计一、数据库的设计：数据库的设计指的就是表的设计。设计要符合三范式（规范的模式），有时我们也需要适当的逆范式；二、什么是三范式？第一范式：1NF是对属性（可理解为字段）的原子性约束，要求属性具有原子性，不可再分。第二范式：2NF是对记录的唯一性约束，要求记录有唯一的标识，即实体的唯一性；第三范式：3NF是对字段冗余的约束，即任何字段不能由其他字段派生出来，要求字段没有冗余，这是可以做到的。然而，没有冗余的数据库未必是好的数据库，有时候为了提高运行的效率，我们也会使用适当的逆范式，方法就是：增加字段。一般来说，1NF在关系型数据库中是自动满足的； 2NF通常通过主键自增的唯一性来约束。而且，记录本身也很少会完全一样； 3NF主要是在主从表中，不会出现相同的字段与字段值；

第二章 SQL语句的优化一、SQL语句优化的步骤： 1、通过show status 命令了解各种sql的执行频率； 2、定位执行效率较低的SQL语句，主要集中在查询语句 3、通过explain分析低效率的sql语句的执行情况 4、确定问题并采取相应的优化措施二、sql语句有几类？ ddl(数据定义语句)[create alter drop] dml(数据操作语句)[insert delete update] select dtl(数据事物语句)[commit rollback savepoint] dcl(数据控制语句)[grant revoke] show status命令该命令可以显示mysql数据库当前的状态，我们主要重点关注“Com”开头的指令。 1、显示数据库开启本次会话后到目前的信息： show status like “Com%”; <=> show session status like “Com%”; 2、显示数据库从启动到目前的信息： Show global status like “Com%”;

Oracle索引原理

Oracle数据库中的索引详解一、ROWID的概念存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和/，row在数据块中的存储方式 SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20; 比如：OOOOOOFFFBBBBBBRRR OOOOOO：data object number, 对应dba_objects.data_object_id FFF：file#, 对应v$datafile.file# BBBBBB：block# RRR：row# Dbms_rowid包 SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual; 具体到特定的物理文件二、索引的概念 1、类似书的目录结构 2、Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度 3、索引直接指向包含所查询值的行的位置，减少磁盘I/O 4、与所索引的表是相互独立的物理结构 5、Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引 6、语法：CREA TE INDEX index ON table (column[, column]...); 7、B-tree结构(非bitmap)： [一]了解索引的工作原理：表：emp

目标：查询Frank的工资salary 建立索引：create index emp_name_idx on emp(name);

[试验]测试索引的作用： 1. 运行/rdbms/admin/utlxplan 脚本 2. 建立测试表 create table t as select * from dba_objects; insert into t select * from t; create table indextable as select rownum id,owner,object_name,subobject_name, object_id,data_object_id,object_type,created from t; 3. set autotrace trace explain 4. set timing on 5. 分析表，可以得到cost 6. 查询object_name=’DBA_INDEXES’ 7. 在object_name列上建立索引 8. 再查询 [思考]索引的代价：插入，更新三、唯一索引 1、何时创建：当某列任意两行的值都不相同 2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立 3、语法：CREA TE UNIQUE INDEX index ON table (column); 4、演示

sql优化方案讲解

Sql优化方案一．数据库优化技术 1.索引（强烈建议使用） 1.1优点创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 1.2 缺点第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 1.3 使用准则索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引。第一，在经常需要搜索的列上，可以加快搜索的速度；

第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 1.4 总结 1）索引提高了数据库的检索性能，但一定程度上牺牲了修改性能。因此适用于“多查询少修改”（insert，update，delete）的表。 2）对此类表中的外键，需要分组，排序或作为检索条件的字段建立索引 3）对此类表中查询使用少，字段取值少，字段数据量大的不应创建索引

数据库索引的作用及实例(精)

1. 1.索引作用 2. 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术, 能够大大提高查询效率。特别是当数据量非常大, 查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。 3. 4. 例如,有 3个未索引的表 t1、 t2、 t3,分别只包含列 c1、 c2、 c3,每个表分别含有 1000行数据组成,指为 1~1000的数值,查找对应值相等行的查询如下所示。 5. 6. SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3 7. 8. 此查询结果应该为 1000行, 每行包含 3个相等的值。在无索引的情况下处理此查询, 必须寻找 3个表所有的组合, 以便得出与 WHERE 子句相配的那些行。而可能的组合数目为 1000×1000×1000(十亿,显然查询将会非常慢。 9. 10. 如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。 11. 12. (1从表 t1中选择第一行,查看此行所包含的数据。 13. 14. (2使用表 t2上的索引,直接定位 t2中与 t1的值匹配的行。类似,利用表 t3上的索引,直接定位 t3中与来自 t1的值匹配的行。

15. 16. (3 扫描表 t1的下一行并重复前面的过程, 直到遍历 t1中所有的行。 17. 18. 在此情形下,仍然对表 t1执行了一个完全扫描,但能够在表 t2和 t3上进行索引查找直接取出这些表中的行, 比未用索引时要快一百万倍。 19. 20. 利用索引, MySQL 加速了 WHERE 子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。 21. 22.2. 创建索引 23. 在执行 CREATE TABLE语句时可以创建索引, 也可以单独用 CREATE INDEX或 ALTER TABLE来为表增加索引。 24. 25.1. ALTER TABLE 26.ALTER TABLE用来创建普通索引、 UNIQUE 索引或 PRIMARY KEY索引。 27. 28. 29. 30.ALTER TABLE table_name ADD INDEX index_name (column_list 31. 32.ALTER TABLE table_name ADD UNIQUE (column_list 34.ALTER TABLE table_name ADD PRIMARY KEY (column_list 35.

MYSQL索引和优化详细说明教程

MYSQL索引和优化详细说明教程 2008-05-16 15:59 MYSQL索引和优化一、什么是索引？索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。假设我们创建了一个名为people的表：然后，我们完全随机把1000个不同name值插入到people表。可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name 列的索引，MySQL将在索引中排序name列：对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL 命令为“SELECT peopleid FROM people WHERE name=\’Mike\’;”），MySQL 能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。二、索引的类型 MySQL提供多种索引类型供选择：普通索引这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

文摘索引型数据库和全文数据库区别

通过对文摘索引型数据库和全文数据库的现状进行比较, 总结出两类数据库的相同和不同特征两类数据库检索系统的相同特征 1，网络检索无论是国外引进还是国内购置及自我开发,网络版数据库检索。网络检索方式有很多优势优势，读者可直接在任意具有权限的连接的计算机上利用通用的浏览器便捷地检索。可同时检索同一若干年代的累积数据或相关数据库的相关数据。 2，资源整合和集成检索用户可以在同一平台上跨库检索，读者可在多个数据库的基础上跨库检索。 3，融菜单检索和高级检索于一体文摘索引型和全文数据库都是直接面对大众读者,所以都能提供简单的菜单式检索,读者通过点击和选择菜单命令和利用检索窗口的功能键或功能词实现简单的检索。为了读者解决对复杂一点的检索往往无能为力的情况，两类数据库一般都提供了高级检索形式来实现。4，综合运用布尔检索、截词检索和位置检索等检索技术，这些传统检索技术功能就是在文摘索引型数据库检索基础上发展起来的。 5数据库检索人性化，用户无论是普通读者还是非专业人士,对检索界面、检索过程、检索帮助、个性检索、结果输出等方面一目了然。两类数据库检索系统的不同特征文摘索引型数据库和全文数据库的最大差别就是前者结果只提供题录和文摘等二次文献信息,后者除可提供二次文献信息外，还能提供作者原文的一次文献信息。 1，检索途径存在着差异检索途径有主题，分类及除此之外的作者、号码等其它辅助途径, 通过数据库设置的检索字段反映检索途径的实现。不同的数据库根据检索的实际需要设置检索字段。全文数据库设置的检索字段一般较文摘索引型数据库少 2，收录文献的原则和目的不同,数据库所起的作用不同。文摘索引型数据库一般收录特定时期的综合学科领域或某一学科分支的相同或不同出版类型的文献。文摘索引型数据库能反映某一段时间内某一学科某一领域的理论和方法的进展及技术与手段的应用。全文数据库以为用户提供利用一次文献为主要宗旨,其数据库商必须和着者或出版单位商谈着作使用权问题,只有双方达成协议签署合同,并履约支付着作权报酬才能使用文献原文而收录数据库。因此全文数据库不可能存在收录文献全面性问题。相对而言,全文数据库很难像文摘索引数据库那样从宏观上反映某一学科某一领域的学术进展情况, 更不可能充当学术评价的工具。 3 ，检索技术的运用不尽相同检索原理的不同，文摘索引型数据库是以记录组织文献, 处理每一条记录依据基于文献内容的特征属性和文献外表的特征，体现传统的布尔检索、截词检索和位置检索等功能。全文数据库主要通过运用对整个文本信息的分析,利用将全部文本划分为主题紧凑的不同子段,用不同的关键字特征标注各子段的文本切分技术和计算机自动进行全文自动抽词标引来处理原始文献的。全文检索技术能体现关键词在子段和全文出现的频率和分布,处理的是典型的非结构化的非线性的数据。 4，主题检索特征不同文摘索引型数据库在提供自然语言的同时,一般都有自己的主题词表反映数据库中各检索词之间的关系,依据主题词表对文献进行主题标引,对每篇文献给出若干个主题词。全文数据库一般没有自己的主题词表, 主题检索依靠不加规范的自然语言实现。使用自然语言主要是基于检索最终用户的大众化, 最大好处就是避免了人工标引的随意性、繁琐性,提高了处理数

数据库原理实验报告-实验四-视图与索引

《数据库原理》实验报告题目：实验四视图与索引学号班级日期 2016.10.20 一、实验内容、步骤以及结果 1．在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分) 2．用两种不同的SQL语句创建第五版教材第三章第9题中要求的视图（视图名：V_SPJ）（10分，每种方法5分)。 --第一种方法 CREATE VIEW V_SPJ AS SELECT sno,pno,qty FROM SPJ WHERE jno=( SELECT jno FROM J WHERE jname ='' ); GO --删除建好的视图 DROP VIEW V_SPJ; GO --第二种方法

CREATE VIEW V_SPJ AS SELECT sno,pno,qty FROM SPJ,J WHERE J.jno=SPJ.jno AND J.jname=''; 3．用SQL语句完成第五版教材第三章第11题中的视图查询（10分，每小题5分)。 11.请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码 (PNO)、供应数量(QTY)。针对该视图VSP完成下列查询： (1)找出三建工程项目使用的各种零件代码及其数量。 (2)找出供应商S1的供应情况。

4．用SQL语句完成视图的数据更新。(15分，每题5分) (1)给视图V_SPJ中增加一条数据。提示： -SPJ表中JNO允许为空时，数据可以插入基本表，此时JNO为NULL，由于JNO 为NULL，所以视图中没有该条数据。 -SPJ表中JNO不能为空时，可以使用instead of触发器实现。 (2)修改视图V_SPJ中的任意一条数据的供应数量。

数据库建立索引的原则

数据库建立索引的原则使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如employee 表的姓（lname）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。在数据库关系图中，您可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。建立索引的优点 1.大大加快数据的检索速度; 2.创建唯一性索引，保证数据库表中每一行数据的唯一性; 3.加速表和表之间的连接; 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。索引的缺点 1.索引需要占物理空间。 2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。有关这些约束的更多信息，请参见主键约束和唯一约束。唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee 表中职员的姓(lname) 上创建了唯一索引，则任何两个员工都不能同姓。有关唯一索引的更多信息，请参见创建唯一索引。主键索引数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。有关主键的更多信息，请参见定义主键。聚集索引在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。一、索引 1. 概念：索引是揭示文献内容出处，提供文献查考线索的工具书。 2. 类型：种类很多，从不同的角度可以划分出不同的类型。按文种分，可以分为中文索引的外文索引；按收录范围分，可以分为综合性索引和专题性索引；按收录文献的时间分，可以分为近期索引和回溯性索引；按索引款目的标目分，可以分为题名索引、著者索引、语词索引、主题索引、分类索引等。 3. 功能：揭示文献的内容和指引读者查找信息 4. 作用：索引揭示了一书、一刊的基本情况，如篇目、文句。可以深入、完整、详细、系统地为读者提所需文献的具体线索。铁律一：天下没有免费的午餐，使用索引是需要付出代价的。索引的优点有目共睹，但是，却很少有人关心过采用索引所需要付出的成本。若数据库管理员能够对索引所需要付出的代价有一个充分的认识，也就不会那么随意到处建立索引了。

mysql优化笔记

◆Mysql数据库的优化技术<大型网站优化技术> 对mysql优化时一个综合性的技术，主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/delete/add]分离 e: 存储过程[模块化编程，可以提高速度] 数据库的三层结构： f: 对mysql配置优化[配置最大并发数my.ini, 调整缓存大小] g: mysql服务器硬件升级 h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM) CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) ◆什么样的表才是符合3NF (范式) 表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF 1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF ?数据库的分类关系型数据库: mysql/oracle/db2/informix/sysbase/sql server 非关系型数据库: (特点: 面向对象或者集合) NoSql数据库: MongoDB(特点是面向文档) 2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现id primary key ; 3NF: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF：显示推导处理

数据库原理及应用(SQL Server 2008)第7章索引与视图-ANSWER

7.6.1 选择题 7.6.2 填空题 1. 聚集索引非聚集索引唯一性索引索引视图 2. 修改数据 3. 创建表 4. 删除 5. 表扫描使用索引查找 7.6.3 简答题 1. 分析索引的优点和缺点。答：这是因为创建索引可以大大提高系统的性能：（1）通过创建唯一性索引，可以保证每一行数据的唯一性。（2）可以大大加快数据的检索速度，这也是索引的最主要的原因。（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。（4）在使用ORDER BY和GROUP BY子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。（5）通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。既然增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？虽然索引有许多优点，但是为表中的每一个列都增加索引是非常不明智的做法。这是因为增加索引也有缺点：（1）创建索引和维护索引要耗费时间。（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚集索引，那么需要的空间就会更大。（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 2. 堆结构的特点是什么？答：堆是不含聚集索引的表，表中的数据没有任何的顺序。堆结构中数据按照插入的先后次序存放，堆文件的数据页面不一定在物理上相邻。堆文件执行插入操作很容易，但是效率不高。因为堆文件只能执行顺序扫描，这对范围查询很有效，但对于随机查询（单个记录）的效率很低。查询最少的次数为1，最多的次数为N（N为记录数），平均次数为(N+1)/2。如果N比较大，耗费的CPU和I/O资源都会很大。 3. 什么是聚集索引和非聚集索引？比较这两种索引结构的特点。答：聚集索引是一种数据表的物理顺序与索引顺序相同的索引。建立索引时，系统将对表的物理数据页中的数据按列进行排列，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的。非聚集索引是一种数据表的物理顺序与索引顺序不相同的索引。非聚集索引与聚集索引

SQL索引详解(优化数据库)

SQL索引一步到位 SQL索引在数据库优化中占有一个非常大的比例，一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。 1.1 什么是索引？ SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间下面举两个简单的例子：图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引. 看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。 1.2 索引的存储机制首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c，聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

文档之家