企业官网建设流程全解析

专业做中文网站,平台seo,微信公众平台开发微网站,美图秀秀在线制作图片先搞懂#xff1a;索引为啥不能多#xff1f;1. 写入/更新/删除速度直接变慢2. 占用大量磁盘空间3. 让MySQL优化器“选花眼”4. 浪费内存缓存哪些是“无用索引”#xff1f;常见的冗余情况1. 重复索引2. 功能重复的索引怎么判断索引是否过多#xff1f;实用检查方法1. 看索…先搞懂索引为啥不能多1. 写入/更新/删除速度直接变慢2. 占用大量磁盘空间3. 让MySQL优化器“选花眼”4. 浪费内存缓存哪些是“无用索引”常见的冗余情况1. 重复索引2. 功能重复的索引怎么判断索引是否过多实用检查方法1. 看索引数量和数据量的匹配2. 查索引使用率3. 找未使用的索引建索引的“黄金法则”少而精才是王道1. 按业务类型决定索引数量2. 索引优先级排序按重要性来3. 精简索引的实战例子4. 索引管理的小技巧最后总结下刚接触MySQL的时候我总觉得索引建得越多越好不管什么查询先给字段加个索引再说。后来做项目时发现表的写入速度越来越慢甚至有些查询明明加了索引却比没加还慢——这才明白索引不是“万能钥匙”多了反而会帮倒忙。今天就聊聊索引过多的问题再分享些实用的建索引技巧。先搞懂索引为啥不能多索引本质是帮查询“快速定位”的数据结构但它不是白给的每加一个索引都要付出对应的代价。1. 写入/更新/删除速度直接变慢我认为这是索引过多最明显的影响。数据写入的时候不光要写主数据还要给每个索引都更新一遍——就像给文件建了多个目录新增文件时每个目录都得改肯定费时间。看个直观的例子假设用户表有5个索引执行一条插入语句INSERTINTOusers(name,age,city,phone,email)VALUES(张三,25,北京,13800138000,zhangsanexample.com);实际执行时数据库要做6次写入操作1次写主数据5次更新对应的索引。更新和删除也是同理如果更新的字段有索引得先删除旧的索引记录再插入新的。我们的经验是索引数量和写入耗时基本成正比比如无索引插入1000条0.1秒1个索引插入1000条0.2秒5个索引插入1000条0.6秒多一个索引写入压力就大一分。2. 占用大量磁盘空间索引本身也要存数据而且数量多了之后占用的空间可能比数据本身还大。我之前查过公司一个表数据才100MB5个索引加起来居然占了100多MB相当于数据体积翻倍。教大家一个查询索引占用空间的SQL自己就能排查SELECTtable_name,index_name,ROUND(index_length/1024/1024,2)AS索引大小(MB),ROUND(data_length/1024/1024,2)AS数据大小(MB),ROUND(index_length/data_length,2)AS索引/数据比FROMinformation_schema.TABLESWHEREtable_schema你的数据库名;健康的情况应该是索引大小小于数据大小如果索引比数据还大就得考虑精简了。3. 让MySQL优化器“选花眼”MySQL执行查询前优化器会先选一个最优索引。如果表有10个甚至更多索引优化器得一个个评估哪个最合适这个过程本身就耗CPU。更麻烦的是优化器可能选错索引。比如表有idx_ageage、idx_citycity、idx_age_cityage,city三个索引执行SELECT * FROM users WHERE age 20 AND city 北京时优化器可能误选idx_age或idx_city而不是更合适的联合索引idx_age_city导致查询变慢。4. 浪费内存缓存InnoDB的缓冲池是有限的会缓存常用的数据和索引。如果索引太多大量不常用的索引会占满缓冲池导致真正需要的热数据没法被缓存查询时得从磁盘读速度自然慢。比如缓冲池总共4GB本来3GB缓存数据、1GB缓存常用索引查询很快但索引多了之后2GB都被不常用的索引占了数据缓存只剩2GB频繁出现磁盘IO性能直接下滑。哪些是“无用索引”常见的冗余情况很多时候索引过多是因为建了重复或冗余的索引自己却没发现。1. 重复索引比如先建了单列索引idx_a (a)又建了联合索引idx_a_b (a,b)——idx_a就是重复索引因为联合索引idx_a_b的前缀列是a完全能覆盖idx_a的查询场景留着idx_a只会增加维护成本。2. 功能重复的索引比如idx_a_b (a,b)和idx_b_a (b,a)如果查询条件主要是a开头或b开头可能都需要但如果很少用b开头的查询idx_b_a就是多余的。还有比如idx_name (name)和idx_name_part (name(20))都是给name字段建索引留一个合适的就行没必要建两个。怎么判断索引是否过多实用检查方法光说不行得有具体的判断标准分享几个我平时常用的方法。1. 看索引数量和数据量的匹配不同规模的表适合的索引数量不一样小型表10万行3-5个索引足够数据少全表扫描都快多建索引没必要中型表10万-1000万行5-8个索引平衡读写性能大型表1000万行8-12个索引再多就会严重影响写入2. 查索引使用率用这条SQL能看到每个索引的使用情况 rows_read很低说明索引很少被用SELECTobject_schemaAS数据库名,object_nameAS表名,index_nameAS索引名,rows_readAS读取次数,rows_insertedAS插入次数,rows_updatedAS更新次数,rows_deletedAS删除次数FROMperformance_schema.table_io_waits_summary_by_index_usageWHEREindex_nameISNOTNULLORDERBYrows_readDESC;如果某个索引的rows_read是0或者远小于rows_updated基本就是无用索引。3. 找未使用的索引MySQL 8.0有个很方便的视图能直接查出未使用的索引SELECT*FROMsys.schema_unused_indexes;低于8.0版本的话用这条SQL手动排查SELECTs.index_name,s.table_name,s.rows_selected,CASEWHENs.rows_selected1000THEN考虑删除WHENs.rows_selected10000THEN观察ELSE保留ENDAS建议FROM(SELECTOBJECT_NAMEAStable_name,INDEX_NAMEASindex_name,COUNT_READASrows_selected,COUNT_UPDATEASrows_updatedFROMperformance_schema.table_io_waits_summary_by_index_usageWHEREOBJECT_SCHEMADATABASE())sWHERErows_selected0OR(rows_selected100ANDrows_updated1000);建索引的“黄金法则”少而精才是王道我认为建索引的核心是“按需创建”不是越多越好而是要精准。分享几个实用的策略1. 按业务类型决定索引数量OLTP系统比如电商交易、用户中心频繁写入索引要少而精3-8个足够只给高频查询建索引OLAP系统比如报表分析、数据仓库频繁读取索引可以稍多8-15个覆盖多种查询模式但也要控制维护成本2. 索引优先级排序按重要性来主键索引必须有默认会建唯一约束索引比如用户手机号、身份证号保证数据唯一高频查询的WHERE条件列比如订单表的user_id、商品表的category_id高频查询的JOIN条件列比如关联查询时的外键高频的ORDER BY/GROUP BY列避免排序耗时覆盖索引查询的列都在索引里不用回表比如SELECT age,city FROM users WHERE age25建idx_age_city就能覆盖低选择性列比如性别选值只有男/女建索引效果差通常不需要3. 精简索引的实战例子之前见过一个电商商品表居然建了15个索引包括各种单列索引和重复的联合索引导致写入很慢。优化后精简到6个核心索引效果立竿见影优化前反面例子CREATETABLEproducts(idINTPRIMARYKEY,nameVARCHAR(200),category_idINT,priceDECIMAL(10,2),stockINT,statusTINYINT,created_timeDATETIME,-- 冗余索引太多INDEXidx_name(name),INDEXidx_category(category_id),INDEXidx_price(price),INDEXidx_cat_price(category_id,price),INDEXidx_cat_status(category_id,status),INDEXidx_name_part(name(20))-- 和idx_name重复);优化后正面例子CREATETABLEproducts(idINTPRIMARYKEY,nameVARCHAR(200),category_idINT,priceDECIMAL(10,2),stockINT,statusTINYINT,created_timeDATETIME,-- 6个核心索引覆盖所有高频查询INDEXidx_cat_price_status(category_id,price,status),-- 分类价格状态查询INDEXidx_status_created(status,created_time),-- 状态创建时间查询INDEXidx_name(name(50)),-- 商品名称搜索INDEXidx_stock_status(stock,status),-- 库存预警查询INDEXidx_created(created_time),-- 时间范围查询INDEXidx_cat_created(category_id,created_time)-- 分类时间查询);优化后写入速度提升40%内存占用减少60%查询性能却没下降。4. 索引管理的小技巧定期审计每月查一次未使用的索引及时清理用不可见索引测试删除索引前先设为不可见ALTER TABLE users ALTER INDEX idx_test INVISIBLE观察一段时间业务无影响再删除建索引前问自己几个问题这个查询每天执行超过100次吗有类似索引能复用吗表写入频繁吗索引列选择性高吗比如性别选择性低不适合建最后总结下索引就像调料放对了能提味放多了反而毁菜。它不是越多越好而是要在查询性能和写入成本之间找平衡。我认为核心要点就几个每个索引都是“负债”要承担维护成本不是必需的就别建优先建联合索引一个设计好的联合索引能顶好几个单列索引定期清理无用、冗余的索引就像定期大扫除写入频繁的表索引一定要精简查询频繁的表可适当多建但也要控制数量其实建索引没有绝对的标准关键是结合自己的业务场景按需创建、定期优化。如果盲目建一堆索引最后只会导致整个数据库性能下滑得不偿失。