网站首页添加代码,网站转化低的原因,清丰网站建设费用,新手怎么开网店步骤原标题#xff1a;MySQL数据库快问快答前言今天楼主给大家列一下关于数据库几个常见问题的要点#xff0c;如果大家对其中的问题感兴趣#xff0c;可以自行扩展研究。1. UNION ALL 与 UNION 的区别UNION和UNION ALL关键字都是将两个结果集合并为一个。UNION在进行表链接后会…原标题MySQL数据库快问快答前言今天楼主给大家列一下关于数据库几个常见问题的要点如果大家对其中的问题感兴趣可以自行扩展研究。1. UNION ALL 与 UNION 的区别UNION和UNION ALL关键字都是将两个结果集合并为一个。UNION在进行表链接后会筛选掉重复的记录所以在表链接后会对所产生的结果集进行排序运算删除重复的记录再返回结果。而UNION ALL只是简单的将两个结果合并后就返回。由于UNION需要排序去重所以 UNION ALL 的效率比 UNION 好很多。2. TRUNCATE 与 DELETE 区别TRUNCATE 是DDL语句而 DELETE 是DML语句。TRUNCATE 是先把整张表drop调然后重建该表。而 DELETE 是一行一行的删除所以 TRUNCATE 的速度肯定比 DELETE 速度快。TRUNCATE 不可以回滚DELETE 可以。TRUNCATE 执行结果只是返回0 rows affected可以解释为没有返回结果。TRUNCATE 会重置水平线(自增长列起始位)DELETE 不会。TRUNCATE 只能清理整张表DELETE 可以按照条件删除。一般情景下TRUNCATE性能比DELETE好一点。3. TIMESTAMP 与 DATETIME 的区别相同点TIMESTAMP 列的显示格式与 DATETIME 列相同。显示列宽固定在19字符并且格式为YYYY-MM-DD HH:MM:SS。不同点TIMESTAMP4个字节存储时间范围1970-01-01 08:00:01~2038-01-19 11:14:07。值以UTC格式保存涉及时区转化存储时对当前的时区进行转换检索时再转换回当前的时区。DATETIME8个字节存储时间范围1000-10-01 00:00:00~9999-12-31 23:59:59。实际格式存储与时区无关。4. 什么是联合索引两个或更多个列上的索引被称作联合索引联合索引又叫复合索引。5. 为什么要使用联合索引减少开销 建一个 联合索引( col1,col2,col3)实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。覆盖索引 对联合索引(col1,col2,col3)如果有如下的sql: select col1,col2,col3 from test where col11 and col22。那么MySQL可以直接通过遍历索引取得数据而无需回表这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化手段之一。效率高 索引列越多通过索引筛选出的数据越少。有1000W条数据的表有如下sql select from table where col11 and col22 and col33 假设假设每个条件可以筛选出10%的数据如果只有单值索引那么通过该索引能筛选出 1000W * 10%100w 条数据然后再回表从100w条数据中找到符合 col22 and col3 3 的数据然后再排序再分页如果是联合索引通过索引筛选出 1000w * 10% * 10% * 10%1w 效率得到明显提升。6. MySQL 联合索引最左匹配原则在 MySQL 建立联合索引时会遵循最左前缀匹配的原则即最左优先在检索数据时从联合索引的最左边开始匹配。MySQL 会一直向右匹配直到遇到范围查询(、 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。7. 什么是聚集和非聚集索引聚集索引就是以主键创建的索引。非聚集索引就是以非主键创建的索引。8. 什么是覆盖索引覆盖索引(covering index)指一个查询语句的执行只用从索引页中就能够取得(如果不是聚集索引叶子节点存储的是主键列值最终还是要回表也就是要通过主键再查找一次)避免了查到索引后再做回表操作减少I/O提高效率。可以结合第10个问题更容易理解。9. 什么是前缀索引前缀索引就是对文本的前几个字符(具体是几个字符在创建索引时指定)创建索引这样创建起来的索引更小。但是MySQL不能在ORDER BY或GROUP BY中使用前缀索引也不能把它们用作覆盖索引。创建前缀索引的语法ALTERTABLEtable_name ADDKEY(column_name(prefix_length))10. InnoDB 与 MyISAM 索引存储结构的区别MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址。而在InnoDB中表数据文件本身就是按BTree组织的一个索引结构这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引所以必须有主键如果没有显示定义自动为生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型。InnoDB的辅助索引(Secondary Index也就是非主键索引)存储的只是主键列和索引列如果主键定义的比较大其他索引也将很大。MyISAM引擎使用BTree作为索引结构索引文件叶节点的data域存放的是数据记录的地址指向数据文件中对应的值每个节点只有该索引列的值。MyISAM主索引和辅助索引(Secondary key)在结构上没有任何区别只是主索引要求key是唯一的辅助索引可以重复(由于MyISAM辅助索引在叶子节点上存储的是数据记录的地址和主键索引一样所以不需要再遍历一次主键索引)。简单的说主索引的区别 InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。辅助索引的区别 InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。11. 为什么尽量选择单调递增数值类型的主键InnoDB中数据记录本身被存于主索引(B树)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放因此每当有一条新的记录插入时MySQL会根据其主键将其插入适当的结点和位置如果页面达到装载因子(InnoDB默认为15/16)则开辟一个新的页。如果使用自增主键那么每次插入新的记录记录就会顺序添加到当前索引结点的后续位置当一页写满就会自动开辟一个新的页这样就会形成一个紧凑的索引结构近似顺序填满。由于每次插入时也不需要移动已有数据因此效率很高也不会增加很多开销在维护索引上。如果使用非自增主键由于每次插入主键的值近似于随机因此每次新纪录都要被插入到现有索引页的中间某个位置此时MySQL不得不为了将新记录查到合适位置而移动元素甚至目标页可能已经被回写到磁盘上而从缓存中清掉此时又要从磁盘上读回来这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的索引结构后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。简单的说索引树只能定位到某一页每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。12. 建表时int 后面的长度的意义int占多少个字节已经是固定的了长度代表了显示的最大宽度。如果不够会用0在左边填充但必须搭配zerofill使用。也就是说int的长度并不影响数据的存储精度长度只和显示有关。13. SHOW INDEX 结果字段代表什么意思Table表名。Non_unique0该索引不含重复值。1该索引可含有重复值。Key_name索引名称如果是注解索引名称总是为PRIMARY。Seq_in_index该列在索引中的序号从 1 开始。例如存在联合索引 idx_a_b_c (a,b,c)则a的Seq_in_index1b2c3。Column_name列名。Collation索引的排列顺序A(ascending)D (descending)NULL (not sorted)。Cardinality一个衡量该索引的唯一程度的值可以使用ANALYZE TABLE(INNODB) 或者 myisamchk -a(MyISAM)更新该值。如果表记录太少该字段的意义不大。一般情况下该值越大索引效率越高。Sub_part对于前缀索引用于索引的字符个数。如果整个字段都加上了索引则显示为NULL。NullYES该列允许NULL值。该列不允许NULL值。Index_type索引类型包括(BTREE, FULLTEXT, HASH, RTREE)。如何解决like%字符串%时索引失效LIKE问题like 以通配符开头 (%abc…)mysql索引失效会变成全表扫描的操作。罪魁祸首是%不是LIKELIKE 条件是 type range 级别%xxx%全表扫描%xxx全表扫描xxx%range解决办法使用覆盖索引可以由 ALL 变为INDEX为啥呢覆盖索引之后就能使用使用索引进行全表扫描。这里要注意一下使用符合索引的时候命中一个字段就可以不用全部命中。15. MySQL高效分页当然想要实现上述效果的前提是返回搜狐查看更多id是唯一索引而且单调递增。N 的值是上一次查询的记录的最后一条id(需要前端保存一下不能直接用传统的方法获得)不支持跨页查询只能按照第1234页这样查询逐页查询。责任编辑
