网站后台功能,做投融资平台的网站都有哪些,东莞做网站需要多少钱,uml电子商务网站建设文档一、索引的作用索引通俗来讲就相当于书的目录#xff0c;当我们根据条件查询的时候#xff0c;没有索引#xff0c;便需要全表扫描#xff0c;数据量少还可以#xff0c;一旦数据量超过百万甚至千万#xff0c;一条查询sql执行往往需要几十秒甚至更多#xff0c;5秒以上…一、索引的作用索引通俗来讲就相当于书的目录当我们根据条件查询的时候没有索引便需要全表扫描数据量少还可以一旦数据量超过百万甚至千万一条查询sql执行往往需要几十秒甚至更多5秒以上就已经让人难以忍受了。提升查询速度的方向一是提升硬件(内存、cpu、硬盘)二是在软件上优化(加索引、优化sql优化sql不在本文阐述范围之内)。能在软件上解决的就不在硬件上解决毕竟硬件提升代码昂贵性价比太低。代价小且行之有效的解决方法就是合理的加索引。索引使用得当能使查询速度提升上万倍效果惊人。二、MySQL索引类型mysql的索引有5种主键索引、普通索引、唯一索引、全文索引、聚合索引(多列索引)。唯一索引和全文索引用的很少我们主要关注主键索引、普通索引和聚合索引。1)主键索引主键索引是加在主键上的索引设置主键(primary key)的时候mysql会自动创建主键索引2)普通索引创建在非主键列上的索引3)聚合索引创建在多列上的索引。三、索引的语法查看某张表的索引SHOW INDEX FROM 表名创建普通索引ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列)创建聚合索引ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2)删除某张表的索引DROP INDEX 索引名 ON 表名;四、EXPLAIN 分析SQL执行的状态EXPLAIN列的解释table 显示这一行的数据是关于哪张表的type 这是重要的列显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALLpossible_keys 显示可能应用在这张表中的索引。如果为空没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句key 实际使用的索引。如果为NULL则没有使用索引。key_len 使用的索引的长度。在不损失精确性的情况下长度越短越好ref 显示索引的哪一列被使用了如果可能的话是一个常数rows MYSQL认为必须检查的用来返回请求数据的行数Extra 关于MYSQL如何解析查询的额外信息。----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Extra字段值含义Distinct 一旦MYSQL找到了与行相联合匹配的行就不再搜索了Not exists MYSQL优化了LEFT JOIN一旦它找到了匹配LEFT JOIN标准的行就不再搜索了Range checked for each Record(index map:#) 没有找到理想的索引因此对于从前面表中来的每一个行组合MYSQL检查使用哪个索引并用它来从表中返回行。这是使用索引的最慢的连接之一Using filesort 看到这个的时候查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的这发生在对表的全部的请求列都是同一个索引的部分的时候Using temporary 看到这个的时候查询需要优化了。这里MYSQL需要创建一个临时表来存储结果这通常发生在对不同的列集进行ORDER BY上而不是GROUP BY上Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行并且连接类型ALL或index这就会发生或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------type字段值含义const 表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行这个值实际就是常数因为MYSQL先读这个值然后把它当做常数来对待eq_ref 连接中MYSQL在查询时从前面的表中对每一个记录的联合都从表中读取一个记录它在查询使用了索引为主键或惟一键的全部时使用ref 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如利用最左边前缀)时发生。对于之前的表的每一个行联合全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好range 这个连接类型使用索引返回一个范围中的行比如使用或index 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好因为索引一般小于表数据)ALL 这个连接类型对于前面的每一个记录联合进行完全扫描这一般比较糟糕应该尽量避免五、性能测试(一)、测试环境测试环境博主家用台式机处理器为AMD FX(tm)-8300 Eight-Core Processor 3.2GHz;内存8G;64位 windows 7。MySQL 5.6.17(二)、MyISAM引擎测试1). 创建一张测试表DROP TABLE IF EXISTS test_user;CREATE TABLE test_user (id bigint(20) PRIMARY key not null AUTO_INCREMENT,username varchar(50) DEFAULT NULL,email varchar(30) DEFAULT NULL,password varchar(32) DEFAULT NULL,status tinyint(1) NULL DEFAULT 0) ENGINEMyISAM DEFAULT CHARSETutf8;存储引擎使用MyISAM是因为此引擎没有事务插入速度极快方便我们快速插入千万条测试数据等我们插完数据再把存储类型修改为InnoDB。2). 使用存储过程插入1千万条数据create procedure myproc()begindeclare num int;set num1;while num 10000000 doinsert into test_user(username,email,password) values(CONCAT(username_,num), CONCAT(num ,qq.com), MD5(num));set numnum1;end while;end3). 执行 call myproc();由于使用的MyISAM引擎插入1千万条数据仅耗时246秒若是InnoDB引擎插入100万条数据就要花费数小时了。MyISAM引擎之所以如此之快一个原因是使用了三个文件来存储数据frm后缀存储表结构、MYD存储真实数据、MYI存储索引数据。每次进行插入时MYD的内容是递增插入MYI是一个B树结构每次的索引变更需要重新组织数据。但相对于InnoDB来说MyISAM更快。4). sql测试1. SELECT id,username,email,password FROM test_user WHERE id999999耗时0.114s。因为我们建表的时候将id设成了主键所以执行此sql的时候走了主键索引查询速度才会如此之快。2. 我们再执行 SELECT id,username,email,password FROM test_user WHERE usernameusername_9000000耗时4.613s。用EXPLAIN分析一下信息显示进行了全表扫描。3. 那我们给username列加上普通索引。ALTER TABLE test_user ADD INDEX index_name(username) ;此时Mysql开始对test_user表建立索引查看mysql 数据目录查看目录文件列表可以看到新建了三个临时文件新的临时数据表MYD文件大小并未变更临时索引文件MYI文件大小增加了很多。查看执行结果此过程大约耗时 221.792s,建索引的过程会全表扫描逐条建索引当然慢了。等执行完毕后mysql把旧的数据库文件删除再用新建立的临时文件替换掉之。(删除索引过程也是同样的步骤)。4. 再来执行select id,username,email,password from test_user where usernameusername_9000000耗时0.001s。可见查询耗时提高的很可观。用EXPLAIN分析一下Extra 字段告诉我们使用到了索引 index_name和之前的EXPLAIN结果对比未建立索引前进行了全部扫描建立索引后使用到了索引查询耗时对比明显。5. 再用username和password来联合查询SELECT id, username, email, PASSWORD FROM test_user WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 AND username username_9000000;耗时0.001s执行 EXPLAIN 显示使用到了 index_name 索引条件语句不分password、useranme先后顺序结果都是一样。说明sql优化器优先用索引命中。6. 我们再执行SELECT id, username, email, PASSWORD FROM test_user WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 OR username username_900000此时虽然我们已经对 username 加了索引但是password列未加索引索引执行password筛选的时候还是会全表扫描因此此时查询速度立马降了下来。耗时5.118s。EXPLAIN一下使用OR条件的时候虽然WHERE 语句中有用到索引字段但还是进行了全表扫描。7. 当我们的sql有多个列的筛选条件的时候就需要对查询的多个列都加索引组成聚合索引加上聚合索引ALTER TABLE test_user ADD INDEX index_union_name_password(username,password)通过临时文件的大小来看索引文件的大小已经超过了数据文件很多了。索引侧面来说索引要合理利用索引就是用空间换时间。[SQL]ALTER TABLE test_user ADD INDEX index_union_name_password(username,password)受影响的行: 10024725。时间: 1399.785s。8. 再来执行[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username username_900000 OR password 7ece221bf3f5dbddbe3c2770ac19b419耗时4.416s。EXPLAIN竟然是全表扫描不可思议 使用 OR 语句竟然没有启用聚合索引也没使用到单索引username9. 再来执行[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username username_900000 AND password 7ece221bf3f5dbddbe3c2770ac19b419耗时0.001s。EXPLAINAND 语句才使用到了聚合索引聚合索引必须使用AND条件同时要符合最左原则请戳我。10. 主键区间查询[SQL]EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id 8999990 AND id 8999999受影响的行: 0时间: 0.001s。命中7行查询时间很短。[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id 8999900 AND id 8999999受影响的行: 0时间: 0.010s[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id 8999000 AND id 8999999受影响的行: 0时间: 0.029s[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE id 8990000 AND id 8999999受影响的行: 0时间: 0.139s通过不断加大区间来看查询时间跟查询的数据量成相对的正比增长同时使用到了主键索引。11. 字符串区间查询[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username username_800000 AND password 7ece221bf3f5dbddbe3c2770ac19b419受影响的行: 0时间: 6.059sEXPLAIN:未使用索引和聚合索引进行了全表扫描。[SQL]SELECT id, username, email, PASSWORD FROM test_user WHERE username username_900000 AND password 7ece221bf3f5dbddbe3c2770ac19b419受影响的行: 0时间: 11.488sEXPLAIN:也使用到了索引和聚合索引。对比得出字符串进行区间查询是否能使用到索引的条件得看mysql是如何优化查询语句的。12.最左原则1]. 新建 A、B、C 聚合索引[SQL]ALTER TABLE test_user ADD INDEX index_union_name_email_password(username,email,password)受影响的行: 10024725时间: 3171.056s2]. SQL 测试慎用 OR 条件可能将会导致全表扫描。覆盖了 A、B、C 索引该语句使用了覆盖索引WHERE 语句的先后顺序并不影响。MySQL会对SQL进行查询优化最终命中ABC索引。命中了 A、B、C 索引中的 AB组合查询耗时很短没有命中到 A、B、C 索引所以进行了全表扫描查询耗时长。小结要使用覆盖索引必须都是 AND 条件慎用 OR 条件。要使用覆盖索引如ABC需满足条件语句中有 A、AB、ABC才会使用覆盖索引采用最左原则。(三)、InnoDB引擎测试1). 新建 InnoDB 表根据上文的步骤新建一个 test_user_innodb 表引擎使用MyISAM然后将存储引擎修改回InnDB。使用如下命令 ALTER TABLE test_user_innodb ENGINEInnoDB; 此命令执行时间大约耗时5分钟耐心等待。[SQL]ALTER TABLE test_user_innodb ENGINEInnoDB;受影响的行: 10024725时间: 692.475s执行完毕后 test_user_innodb 表由之前的 三个文件 变为 两个文件test_user_innodb.frm 和 test_user_innodb.idb。其中frm文件记录表结构idb文件记录表中的数据其实就是一个B树索引文件不过该树的叶子节点中的数据域记录的是整行数据记录。所以 Innodb 的查找次数比 MyISAM 表减少一次磁盘IO查找逻辑但相对来说插入数据也就没有MyISAM 快了有所求就有所得吧同时 InnoDB 支持行锁、表锁InnoDB 的锁机制是建立在索引上的所以如果没命中索引那么将是加表锁。2). SQL 测试1. [SQL]SELECT id,username,email,password FROM test_user_innodb WHERE usernameusername_9000000受影响的行: 0时间: 14.540s显示进行了全表扫描但跟MyISAM表对比来说扫描的行数小了很多可能这就是底层B树布局不一样导致的吧。2. 那我们给username列加上普通索引。ALTER TABLE test_user_innodb ADD INDEX index_name(username) ;此时Mysql开始对 test_user_innodb 表建立索引查看mysql 数据目录仔细观察发现只生成了一个表结构临时文件。ibd文件容量在不断增大。这个跟MyISAM表加索引逻辑不一样。[SQL]ALTER TABLE test_user_innodb ADD INDEX index_name(username) ;受影响的行: 0时间: 157.679s此过程大约耗时 157.679s, 貌似建索引的过程未进行全表扫描对比MyISAM表减少60s左右。为何如何估计需要看底层实现了3. 再执行 SELECT id,username,email,password FROM test_user_innodb WHERE usernameusername_9000000[SQL]SELECT id,username,email,password FROM test_user_innodb WHERE usernameusername_9000000受影响的行: 0时间: 0.001s可见查询耗时减少的很可观对比与未加索引。用EXPLAIN分析一下和MyISAM表没有多少差别。4. 再用username和password来联合查询SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 AND username username_9000000;耗时0.001s执行 EXPLAIN 显示使用到了 index_name 索引条件语句不分password、useranme先后顺序结果都是一样。说明sql优化器优先用索引命中。5. 我们再执行SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 OR username username_900000此时虽然我们已经对 username 加了索引但是password列未加索引索引执行password筛选的时候还是会全表扫描因此此时查询速度立马降了下来。[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 OR username username_900000受影响的行: 0时间: 10.719sEXPLAIN一下使用OR条件的时候虽然WHERE 语句中有用到索引字段但还是进行了全表扫描。对比MyISAM 表来说没有多大却别唯一的就是rows行数不一样。6. 加上聚合索引ALTER TABLE test_user_innodb ADD INDEX index_union_name_password(username,password)此时Mysql开始对 test_user_innodb 表建立索引查看mysql 数据目录和之前的一样新增了一个临时表结构文件ibd文件不断增大。[SQL]ALTER TABLE test_user_innodb ADD INDEX index_union_name_password(username,password)受影响的行: 0时间: 348.613s建立索引的时间比MyISAM 快。7. 再来执行[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE password 7ece221bf3f5dbddbe3c2770ac19b419 OR username username_900000受影响的行: 0时间: 10.357s对比MyISAM 竟然是慢了6s左右 和MyISAM 的全表扫描无差别。InnoDB的OR查询性能没有MyISAM 快应该是为了实现事务导致的性能损失8. 再来执行[SQL] SELECT id, username, email, PASSWORD FROM test_user WHERE username username_900000 AND password 7ece221bf3f5dbddbe3c2770ac19b419耗时0.001s。EXPLAINAND 语句才使用到了聚合索引聚合索引必须使用AND条件同时要符合最左原则请戳我。9. 主键区间查询[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id 8999990 AND id 8999999受影响的行: 0时间: 0.000s[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id 8999900 AND id 8999999受影响的行: 0时间: 0.001s[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id 8999000 AND id 8999999受影响的行: 0时间: 0.003s[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE id 8990000 AND id 8999999受影响的行: 0时间: 0.022s通过不断加大区间来看查询时间跟查询的数据量成相对的正比增长同时使用到了主键索引。相对于MyISAM 表来说主键区间查询的耗时小很多很多看来只能用底层的B树的实现不一样来解释了MyISAM 的B树子节点的叶子节点数据域存储的是数据在MYD文件中的数据地址。InnoDB 的B树子节点的叶子节点数据域存储的是整行数据记录这个节省了一次硬盘IO操作应该是这个特点导致了主键区间查询比MyISAM 快的原因。10. 字符串区间查询[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username username_800000 AND password 7ece221bf3f5dbddbe3c2770ac19b419受影响的行: 0时间: 12.506s未使用索引和聚合索引进行了全表扫描。缩小区间在查询[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username username_900000 AND password 7ece221bf3f5dbddbe3c2770ac19b419受影响的行: 0时间: 12.213s[SQL]SELECT id, username, email, PASSWORD FROM test_user_innodb WHERE username username_1000000 AND password 7ece221bf3f5dbddbe3c2770ac19b419受影响的行: 0时间: 19.793s11.最左原则1]. 新建 A、B、C 聚合索引[SQL]ALTER TABLE test_user_innodb ADD INDEX index_union_name_email_password(username,email,password)受影响的行: 0时间: 588.579s对比MyISAM 表来说建立该索引的时间是其的1/6之一。建立索引的时间相对可观。磁盘占用来说InnoDB总量更小。2]. SQL 测试和MyISAM 表对比竟然没使用到全表扫描而且使用到了聚合索引。覆盖了 A、B、C 索引该语句使用了覆盖索引WHERE 语句的先后顺序并不影响。MySQL会对SQL进行查询优化最终命中ABC索引。命中了 A、B、C 索引中的 AB组合查询耗时很短没有命中到 A、B、C 索引最左原则竟然不是全表扫描而是使用了索引。和MyISAM 表对比MyISAM 表是全表扫描而InnoDB却是使用到了索引。六、总结两大引擎MyISAM、InnoDB分析背景数据记录10024725行表索引 主键、A、AB、ABC相同点1.都是B树的底层实现。2.WHERE条件都符合索引最左匹配原则。不同点1.MyISAM的存储文件有三个frm、MYD、MYI 文件InnoDB的存储文件就两个frm、ibd文件。总文件大小InnoDB引擎占用空间更小。2.InnoDB的存储文件本身就是索引构成建立新索引的时间比MyISAM快。3.MyISAM比InnoDB查询速度快插入速度也快。4.主键区间查询InnoDB查询更快。字符串区间查询MyISAM相对更快。5.有A、AB、ABC索引的情况下A OR B 查询InnoDB查询性能比MyISAM慢。不建议使用OR 条件进行查询。6.InnoDB表没有命中到 A、B、C 索引最左原则时BC组合查询命中了索引但还是完全扫描比全表扫描快些。MyISAM是全表扫描。开篇也说过软件层面的优化一是合理加索引二是优化执行慢的sql。此二者相辅相成缺一不可如果加了索引还是查询很慢这时候就要考虑是sql的问题了优化sql。实际生产中的sql往往比较复杂如果数据量过了百万加了索引后效果还是不理想使用集群、垂直或水平拆分。
