浙江省网站备案注销申请表,免费申请网站com域名,wordpress实用,网站需求分析是在建站的什么阶段做的_为什么要做?本文基于Mysql5.7版本和InnoDB存储引擎。1、InnoDB索引组织表在InnoDB引擎中#xff0c;表都是按照主键顺序组织存放的#xff0c;这种存放方式的表称为索引组织表。InnoDB存储引擎中的表#xff0c;都有主键#xff0c;如果没有显式声明主键#xff0c;则采取以下措施表都是按照主键顺序组织存放的这种存放方式的表称为索引组织表。InnoDB存储引擎中的表都有主键如果没有显式声明主键则采取以下措施该表如果有非空唯一索引则该列为主键如果有多个则取第一个如果没有非空唯一索引则InnoDB自动创建一个大小为6字节的指针作为主键为什么推荐使用自增ID做主键而不是UUID?这是由于B树的性质决定的UUID无序占用空间大每(磁盘)页能存放的索引少这会导致磁盘IO次数增加效率变慢什么是堆表与索引组织表相对应堆表是按照行数据的插入顺序存放无组织在某些情况下堆表比索引组织表更快2、索引原理2.1、什么是索引索引是一个单独的、存储在磁盘上的数据库结构。InnoDB支持B树索引B树索引由于一个结点存储的是一个磁盘页的数据因此只能先找到被查找数据所在的页然后将页读进内存中最后再在内存中查找关于B树数据结构的相关知识请看 什么是B-树、B树、B树、B*树2.2、B树索引分类聚集(主键)索引辅助(非聚集、二级)索引普通索引唯一索引联合索引聚集索引和主键索引的相同点叶子结点存放所有数据(这是由B树的性质所决定的)不同点聚集索引的叶子结点存放一整行数据而辅助索引的叶子结点只存放该列数据和主键每张表只能有一个聚集索引如果是select *操作则优化器更倾向于使用聚集索引因为聚集索引能在B树的叶子结点上找到一整行的数据聚集索引对于主键的排序和范围查找非常快如果采用辅助索引查找比如select * from user where name张三假设在辅助索引name上通过3次查找得到(name张三,id10)再在聚集索引上通过3次查找得到id为10的整行数据因此一共进行了6次查找因此直接查找聚集索引比查找辅助索引效率高聚集索引的叶子结点包含了全部数据2.3、联合索引原理如上图所示联合索引的结点以(a列的值,b列的值)形式存储。下面看看如何使用联合索引进行查询select * from tb1 where a? and b?走联合索引因为a和b在一起是排序的select * from tb1 where a?走联合索引因为单独的a是排序的select * from tb1 where b?不走联合索引因为单独的b不是排序的如上图b的顺序为1,2,1,4,1,2联合索引的好处首先a列和b列都是排好序的比如我要查询用户最近三次的购买记录可以使用user_id作为索引也可以使用(user_id,buy_date)的联合索引如果使用user_id作为索引则需要将查询出来的结果再按buy_date进行一次排序才能查出最近三次的购买记录而联合索引已经将buy_date排好序了只需要取最后三条数据就是用户最新三次的购买记录2.4、什么是覆盖索引定义如果能直接从辅助索引上查找到所有想要的数据而不需要回表(通过主键在聚集索引上做二次查询)就称该辅助索引覆盖了这条select语句又称索引覆盖优化器一般会倾向于使用覆盖索引而不是聚集索引因为聚集索引占用空间大磁盘IO次数多联合索引可以当做覆盖索引使用。对于联合索引(a,b)而言使用b作为查询条件则不走索引但是如果此时没有针对b的索引且要查询的列该联合索引都有则优化器会将该联合索引视作覆盖索引(联合索引上单独的b是无序的所以是从头到尾顺序查找尽管如此也比在聚集索引上顺序查找效率高)explain的Extra中有Using index就表明使用了覆盖索引2.5、操作索引查看某个表的索引show index from 表名下面解释一下各字段的意思Table索引所在表名Non_unique值为0表示唯一Key_name索引名称Seq_in_index索引中该列的位置Column索引中列的名称Collation列以什么方式存储在索引中值为A或NULLB树索引总是A即排序的Cardinality表示该列中唯一值数目的估计值。如果该值非常小则表示该列重复数据较多为什么是估计值呢因为该值不是实时更新的(代价太大)可以用analyze table命令手动更新一次Cardinality值Sub_part是否只对列的一部分索引比如某个列类型为varchar(1000)可以设置只索引该列的前100个字符则Sub_part的值为100如果索引的是列的全部则Sub_part为NULLPacked关键字如何被压缩。如果没有被压缩则为NULLNull该列是否允许有NULL值Index_type索引类型在select语句中使用force index(index_name)强制使用某个索引而user index(index_name)则提示使用某个索引。3、性能优化3.1、执行计划查看sql的执行计划explain select ...下面简要介绍一下各字段的意思详细解释请查阅Mysql5.7官方文档https://dev.mysql.com/doc/refman/5.7/en/explain-output.htmlidselect的查询序列号select_type查询类型simple简单查询不包括连接查询和子查询primary主要的查询最外层的查询subquery子查询table表示查询的表partitions命中的分区如果是非分区表则为NULLtype表的连接类型const只返回一行数据查询速度很快该列具有唯一索引或主键索引如果是普通索引即时真的只返回一行数据也不会是constrange范围查询index根据辅助索引全表扫描ALL无索引(聚集索引)全表扫描possible_keys通过检测where子句获得可用的索引key实际使用的索引rows显示Mysql在表中进行查询时必须检查的行数extra表示Mysql在处理查询时的详细信息3.2、索引设计原则索引优点唯一索引可以保证唯一性加快速度查询速度、表与表连接速度、分组和排序的速度缺点索引需要占用磁盘空间索引的维护(增删改)需要时间尤其是数据量大的时候索引设计原则索引并非越多越好索引太多会影响insert、update、delete的性能经常更新的列不使用索引经常查询(逻辑外键、where子句、group by子句、order by子句)的列使用索引数据量小的表不使用索引重复值很多的列不使用索引删除长期未使用的索引不用的索引会造成不必要的性能消耗避免使用冗余索引如(name,city)和(name)就是冗余索引因此一般应该扩展已有的索引而不是创建新的索引3.3、索引失效的情况避免where子句中对字段施加函数会造成无法命中索引。这是因为b树只能对原值进行索引。索引列尽量不要存在null值将该列设置not null约束可以用0代替null。假设订单表有1000万数据订单号为null有100条分析如下三条sql语句explain select order_num from goods_order where order_num f035a84a-4672-4517-bd8a-2c9f2d92423eexplain select order_num from goods_order where order_num is nullexplain select order_num from goods_order where order_num is not null通过执行结果发现只有is not null不走索引。联合索引遵循最左前缀原则否则不走索引。使用in不走索引用exists代替如果是连续的数值则可以用between代替。应尽量避免在 where 子句中使用 or 来连接条件如果一个字段有索引一个字段没有索引则不走索引。like子句不能前置百分比否则不走索引如果非要前置百分比不可则考虑使用全文索引。where 子句中不要使用 ! 或 否则不走索引。limit offset查询缓慢时可以借助主键索引来提高性能select a.* from bigtable a inner join (select id from bigtable LIMIT 6520000,10) b on a.idb.id最后看一下下面这种情况比如select * from order where order_id10000 and order_id20000注意order_id不是主键此时有一个辅助索引order_id由于该索引上不包含所有的字段(注意是select *)因此还要回表。通过order_id找到的对应主键是无序的所以回表的过程是磁盘离散读而磁盘顺序读的速度是远大于离散读的(固态硬盘的随机读速度非常快)特别是数据量大的情况下差异更明显这时会优化器会放弃辅助索引而走全表扫描。总体来说优化器选择走不走索引也不是千篇一律的有时候也要看实际情况。对于我们而言要熟练使用explain查看sql执行计划在实际项目中分析sql性能的瓶颈进行优化。
