甜点网站开发需求分析,一款app的开发成本,个人养老金制度将落地,合肥集团网站建设哪个好转自#xff1a;CSDNMySQL是目前绝大多数互联网公司使用的关系型数据库#xff0c;它性能出色、资源丰富、成本低廉#xff0c;是快速搭建互联网应用的首选关系型数据库。但是#xff0c;俗话说#xff0c;“好马配好鞍”#xff0c;仅仅会使用MySQL是不够的#xff0c;… 转自CSDNMySQL是目前绝大多数互联网公司使用的关系型数据库它性能出色、资源丰富、成本低廉是快速搭建互联网应用的首选关系型数据库。但是俗话说“好马配好鞍”仅仅会使用MySQL是不够的对MySQL在不同场景下使用性能的最小化使用代价是一个重要的课题。一般在互联网公司的大部分业务中读写的比例大约是10:1也就是说查询的场景往往比更新或写入的场景多得多那么问题来了如何优化查询呢前几天公司同事发了一条SQL语句问这个SQL有没有走索引对于一般的开发人员而言优化SQL的方式是在SQL中查询条件里的字段上添加索引。但是如何添加索引索引的顺序如何索引是如何匹配命中的一般的开发人员可能只知道大概并没有很深入的了解。MySQL索引原理索引的目的与原理在日常生活中经常有这样的场景有一个没见到过的英文单词我们查字典找到这个单词的意思我们要出去旅行查询到具体地点的航班号诸如此类。在这样的场景中我们都是通过不断的缩小范围来筛选出最终预期的结果同时把随机的事件变成顺序事件查询字典比如查单词mysql我们是按照一个字母一个字母的顺序来查询的查询航班号我们也是通过地点机场航空公司一个一个来筛选缩小范围的。我们总是通过同一种查找方式来锁定数据。数据库也是一样但显然要比现实生活中的场景要复杂得多因为不仅会有等值查询()还有范围查询(磁盘I/O与预读刚刚提到了磁盘访问那么这里先简单介绍一下磁盘的I/O与预读。磁盘读取数据靠的是机械运动每次读取数据花费的时间可以分成寻道时间、旋转延迟、传输时间三个部分。寻道时间指的是磁臂移动到指定磁盘所需要的时间主流的磁盘一般在5ms以下旋转延迟指的是我们经常说的磁盘转速比如一个磁盘7200转表示的就是每分钟磁盘能转7200次转换成秒也就是120次每秒旋转延迟就是1/120/24.17ms传输时间指的是从磁盘读取出数据或将数据写入磁盘的时间一般都在零点几毫秒相对于前两个可以忽略不计。那么访问一次磁盘的时间即一次磁盘I/O的时间约等于54.179.17ms9ms左右听起来还是不错的哈但要知道一台500-MIPS的机器每秒可以执行5亿条指令因为指令依靠的是电的性质换句话说执行一次I/O的时间可以执行40万条指令数据库动辄百万级甚至千万级的数据每次9ms的时间显然是一个灾难。上图是计算机硬件延迟时间的对比图。考虑到磁盘I/O是非常高昂代价的操作计算机系统做了一些优化当一次I/O时不光会把当前磁盘地址的数据读取到内存中而且会把相邻的数据也读取到内存缓冲区中因为局部预读性原理告诉我们当计算机访问一个地址的数据的时候与其相邻的数据也会很快访问到。每一次I/O读取的数据我们称之为一页(Page)。具体一页的数据有多大这个跟操作系统有关一般为4K或8K也就是我们读取一页数据的时候实际上才发生了一次I/O这个理论对于索引的数据结构设计很有帮助。索引的数据结构上面讲了索引的基本原理数据库的复杂性以及操作系统的一些内容目的就是让大家了解到任何一种数据结构都不是凭空产生的一定有它的背景和使用场景。那么我们需要这些数据结构能够做什么呢其实很简单就是每次查找数据的时候把磁盘I/O次数限制在一个很小的数量级最好是一个常量数量级。那么我们就想到如果一个高度可控的多路搜索树是否能够满足需求呢在这样的背景下B树应运而生。详解B树如上图是一棵B树。B树的定义童鞋可以自行百度我们只说一些重点。图中浅蓝色的块我们称之为一个磁盘可以看到每个磁盘块包含几个数据项(深蓝色)和指针(黄色)。如磁盘块1包含数据17和数据35包含指针P1,P2,P3P1指向数据小于17的磁盘块P2指向数据在17到35之间的数据所在磁盘块P3指向数据大于35的数据所在的磁盘块。真实数据存在于叶子节点即3591013152829366075799099 。非叶子节点不存储真实数据只存储指引搜索方向的数据项如17、35并不真实存在于数据表中。B树的查找过程还是使用上面的B树。假设我们要查找数据项29那么我们首先会把磁盘块1由磁盘加载到内存中此时进行了一次I/O在内存中用二分查找确定29在17和35之间锁定磁盘块1的P2指针内存计算时间由于非常短(对比于I/O)可以忽略不计通过磁盘块1的P2指针的磁盘地址指向磁盘块3由磁盘加载到内存此时进行了第二次I/O29在26和30之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存此时进行了第三次I/O同时内存中计算二分查找找到29查询结束。这一过程一共进行了3次I/O。在真实使用场景中三层的B树可以表示上百万的数据如果上百万的数据查询只需要三次I/O性能提高将会是巨大的。B树就是一种索引数据结构如果没有这样的索引每个数据项发生一次I/O那么成本将会大大提升。B树的性质在上面的查找例子中我们可以分析出一些B树的性质I/O的次数取决于B树的高度H假设当前数据表的数据为N每个磁盘块的数据项的数量是M则有Hlog(M1)N当数据量N一定的情况下M越大H越小而M磁盘块大小/数据项大小磁盘块大小也就是一个数据页的大小是固定的如果数据项占的空间越小数据项的数量越多树的高度也就越低。这也就是为什么每个数据项即索引字段要尽量的小比如int占4个字节要比bigint的8个字节小一半。这也是为什么B树要求把真实数据放在叶子节点内而不是内层节点内一旦放到内层节点内磁盘块的数据项会大幅度的下降导致树层级的增高。当数据项为1时B树会退化成线性表。B树的数据项是复合性数据结构比如(nameagegender)的时候B树是按照从左到右的顺序来建立搜索树的比如当(小张22女)这样的数据来检索的时候B树会优先比较name来确定下一步的搜索方向如果name相同再依次比较age和gender最后得到检索的数据。但是当(22女)这样没有name的数据来的时候B树就不知道下一步该查哪个节点因为建立搜索树的时候name就是第一个比较因子必须根据name来搜索才知道下一步去哪里查询。比如当(小张男)这样的数据来检索时B树就可以根据name来指定搜索方向但下一字段age缺失所以只能把名字是“小张”的所有数据都找到然后再匹配性别是“男”的数据了。这个是非常重要的一条性质即索引的最左匹配特性。索引的类型在MySQL中索引分为两大类聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的而非聚簇索引则不同聚簇索引能够提高多行检索的速度而非聚簇索引则对单行的检索速度很快。在这两大类的索引类型下还可以将索引分成四个小类普通索引最基本的索引没有任何限制是我们大多数情况下使用到的索引。唯一索引与普通索引类型不同的是唯一索引的列值必须唯一但允许为空值。全文索引全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表作用于CHAR、VARCHAR、TEXT数据类型的列。组合索引将几个列作为一条索引进行检索使用最左匹配原则。建立索引的原则当我们了解完索引原理之后对慢查询的优化应该有一些想法这里我们先总结一下建立索引的一些原则最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则MySQL会一直向右匹配直到遇到范围查询(, 3 AND d 4如果建立 (a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。等于()和in 可以乱序。比如a 1 AND b 2 AND c 3 建立(a,b,c)索引可以任意顺序MySQL的查询优化器会帮你优化成索引可以识别的模式。尽量选择区分度高的列作为索引区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率比率越大我们扫描的记录数就越少唯一键的区分度是1而一些状态、性别字段可能在大数据面前区分度是0。可能有人会问这个比率有什么经验么使用场景不同这个值也很难确定一般需要JOIN的字段我们要求在0.1以上即平均1条扫描10条记录。索引列不能参与计算尽量保持列“干净”。比如FROM_UNIXTIME(create_time) 2016-06-06 就不能使用索引原因很简单B树中存储的都是数据表中的字段值但是进行检索时需要把所有元素都应用函数才能比较显然这样的代价太大。所以语句要写成 create_time UNIX_TIMESTAMP(2016-06-06)。尽可能的扩展索引不要新建立索引。比如表中已经有了a的索引现在要加(a,b)的索引那么只需要修改原来的索引即可。单个多列组合索引和多个单列索引的检索查询效果不同因为在执行SQL时MySQL只能使用一个索引会从多个单列索引中选择一个限制最为严格的索引。慢查询的优化步骤查看运行效果是否真的很慢主要设置SQL_NO_CACHE。WHERE条件单表查询锁定最小返回记录表。这句话的意思是把查询语句的WHERE都应用到表中返回的记录数最小的表开始查起单表每个字段分别查询看哪个字段的区分度最高EXPLAIN查看执行计划是否与1预期一致(从锁定记录较少的表开始查询)ORDER BY LIMIT 形式的SQL语句让排序的表优先查多去了解业务的使用场景加索引时要参照建立索引的几大原则观察结果不符合预期则重新从1开始分析索引的优化方法索引不会包含有NULL值的列只要列中包含有NULL值都将不会被包含在索引中组合索引中只要有一列有NULL值那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时不要让索引字段的默认值为NULL。使用短索引假设如果有一个数据类型为CHAR(255)的列在前10个或20个字符内绝大部分数据的值是唯一的那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作。索引列排序MySQL查询只使用一个索引因此如果WHERE子句中已经使用了索引的话那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作尽量不要包含多个列的排序如果需要最好给这些列也创建组合索引。LIKE语句操作一般情况下不建议使用LIKE操作如果非使用不可如何使用也是一个研究的课题。LIKE %aaaaa%不会使用索引但是LIKE aaa%却可以使用索引。不要在索引列上进行运算在建立索引的原则中提到了索引列不能进行运算这里就不再赘述了。END文字 / echo文中贴纸 / 网络(侵删)配图 / 网络(侵删)排版 / PHP学习手记PHP学习手记 科技 | 前沿 | 技术 | 学习微信号phpStudy
