青浦手机网站制作,网站开发 绩效考核,wordpress中文版 docker,outlook企业邮箱收费标准来源#xff1a;cnblogs.com/songwenjie/p/9410009.html本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容#xff0c;下面一一展开(本文图片可点开放大)。一、MySQL索引基础首先#xff0c;我们将从索引基础开…来源cnblogs.com/songwenjie/p/9410009.html本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容下面一一展开(本文图片可点开放大)。一、MySQL索引基础首先我们将从索引基础开始介绍一下什么是索引分析索引的几种类型并探讨一下如何创建索引以及索引设计的基本原则。此部分用于测试索引创建的user表的结构如下1. 什么是索引“索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。”——《高性能MySQL》我们需要知道索引其实是一种数据结构其功能是帮助我们快速匹配查找到需要的数据行是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。2. 索引类型可以使用SHOW INDEX FROM table_name;查看索引详情主键索引 PRIMARY KEY它是一种特殊的唯一索引不允许有空值。一般是在建表的时候同时创建主键索引。注意一个表只能有一个主键。唯一索引 UNIQUE唯一索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引普通索引 INDEX这是最基本的索引它没有任何限制。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引组合索引 INDEX即一个索引包含多个列多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引全文索引 FULLTEXT也称全文检索是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引索引一经创建不能修改如果要修改索引只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。3、索引设计的原则1)适合索引的列是出现在where子句中的列或者连接子句中指定的列2)基数较小的类索引效果较差没有必要在此列建立索引3)使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间4)不要过度索引。索引需要额外的磁盘空间并降低写操作的性能。在修改表内容的时候索引会进行更新甚至重构索引列越多这个时间就会越长。所以只保持需要的索引有利于查询即可。二、MySQL索引优化实战上面我们介绍了索引的基本内容这部分我们介绍索引优化实战。在介绍索引优化实战之前首先要介绍两个与索引相关的重要概念这两个概念对于索引优化至关重要。此部分用于测试的user表结构1、索引相关的重要概念基数单个列唯一键(distict_keys)的数量叫做基数。SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;user表的总行数是5gender列的基数是2说明gender列里面有大量重复值name列的基数等于总行数说明name列没有重复值相当于主键。返回数据的比例user表中共有5条数据SELECT * FROM user;查询满足性别为0(男)的记录数那么返回记录的比例数是同理查询name为swj的记录数返回记录的比例数是现在问题来了假设name、gender列都有索引那么SELECT * FROM user WHERE gender 0; SELECT * FROM user WHERE name swj;都能命中索引吗user表的索引详情SELECT * FROM user WHERE gender 0;没有命中索引注意filtered的值就是上面我们计算的返回记录的比例数。SELECT * FROM user WHERE name swj;命中了索引index_name因为走索引直接就能找到要查询的记录所以filtered的值为100。因此返回表中30%内的数据会走索引返回超过30%数据就使用全表扫描。当然这个结论太绝对了也并不是绝对的30%只是一个大概的范围。回表当对一个列创建索引之后索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能如果回表次数太多就不应该走索引扫描应该直接走全表扫描。EXPLAIN命令结果中的Using Index意味着不会回表通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。2. 索引优化实战有些时候虽然数据库有索引但是并不被优化器选择使用。我们可以通过SHOW STATUS LIKE Handler_read%;查看索引的使用情况Handler_read_key如果索引正在工作Handler_read_key的值将很高。Handler_read_rnd_next数据文件中读取下一行的请求数如果正在进行大量的表扫描值将较高则说明索引利用不理想。索引优化规则1)如果MySQL估计使用索引比全表扫描还慢则不会使用索引。返回数据的比例是重要的指标比例越低越容易命中索引。记住这个范围值——30%后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。2)前导模糊查询不能命中索引。name列创建普通索引前导模糊查询不能命中索引EXPLAIN SELECT * FROM user WHERE name LIKE %s%;非前导模糊查询则可以使用索引可优化为使用非前导模糊查询EXPLAIN SELECT * FROM user WHERE name LIKE s%;3)数据类型出现隐式转换的时候不会命中索引特别是当列类型是字符串一定要将字符常量值用引号引起来。EXPLAIN SELECT * FROM user WHERE name1;EXPLAIN SELECT * FROM user WHERE name1;4)复合索引的情况下查询条件不包含索引列最左边部分(不满足最左原则)不会命中符合索引。name,age,status列创建复合索引ALTER TABLE user ADD INDEX index_name (name,age,status);user表索引详情SHOW INDEX FROM user;根据最左原则可以命中复合索引index_nameEXPLAIN SELECT * FROM user WHERE nameswj AND status1;注意最左原则并不是说是查询条件的顺序EXPLAIN SELECT * FROM user WHERE status1 AND nameswj;而是查询条件中是否包含索引最左列字段EXPLAIN SELECT * FROM user WHERE status2 ;5)union、in、or都能够命中索引建议使用in。union:EXPLAIN SELECT*FROM user WHERE status1UNION ALLSELECT*FROM user WHERE status 2;in:EXPLAIN SELECT * FROM user WHERE status IN (1,2);or:EXPLAIN SELECT*FROM user WHERE status1OR status2;查询的CPU消耗orinunion。6)用or分割开的条件如果or前的条件中列有索引而后面的列中没有索引那么涉及到的索引都不会被用到。EXPLAIN SELECT * FROM payment WHERE customer_id 203 OR amount 3.96;因为or后面的条件列中没有索引那么后面的查询肯定要走全表扫描在存在全表扫描的情况下就没有必要多一次索引扫描增加IO访问。7)负向条件查询不能使用索引可以优化为in查询。负向条件有!、、not in、not exists、not like等。status列创建索引ALTER TABLE user ADD INDEX index_status (status);user表索引详情SHOW INDEX FROM user;负向条件不能命中缓存EXPLAIN SELECT * FROM user WHERE status !1 AND status ! 2;可以优化为in查询但是前提是区分度要高返回数据的比例在30%以内EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);8)范围条件查询可以命中索引。范围条件有、、between等。status,age列分别创建索引ALTER TABLE user ADD INDEX index_status (status);ALTER TABLE user ADD INDEX index_age (age);user表索引详情SHOW INDEX FROM user;范围条件查询可以命中索引EXPLAIN SELECT * FROM user WHERE status5;范围列可以用到索引(联合索引必须是最左前缀)但是范围列后面的列无法用到索引索引最多用于一个范围列如果查询条件中有两个范围列则无法全用到索引EXPLAIN SELECT * FROM user WHERE status5 AND age24;如果是范围查询和等值查询同时存在优先匹配等值查询列的索引EXPLAIN SELECT * FROM user WHERE status5 AND age24;8)数据库执行计算不会命中索引。EXPLAIN SELECT * FROM user WHERE age24;EXPLAIN SELECT * FROM user WHERE age124;计算逻辑应该尽量放到业务层处理节省数据库的CPU的同时最大限度的命中索引。9)利用覆盖索引进行查询避免回表。被查询的列数据能从索引中取得而不用通过行定位符row-locator再到row上获取即“被查询列要被所建的索引覆盖”这能够加速查询速度。user表的索引详情因为status字段是索引列所以直接从索引中就可以获取值不必回表查询Using Index代表从索引中查询EXPLAIN SELECT status FROM user where status1;当查询其他列时就需要回表查询这也是为什么要避免SELECT*的原因之一EXPLAIN SELECT * FROM user where status1;10)建立索引的列不允许为null。单列索引不存null值复合索引不存全为null的值如果列允许为null可能会得到“不符合预期”的结果集所以请使用not null约束以及默认值。remark列建立索引ALTER TABLE user ADD INDEX index_remark (remark);IS NULL可以命中索引EXPLAIN SELECT * FROM user WHERE remark IS NULL;IS NOT NULL不能命中索引EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;虽然IS NULL可以命中索引但是NULL本身就不是一种好的数据库设计应该使用NOT NULL约束以及默认值。a. 更新十分频繁的字段上不宜建立索引因为更新操作会变更B树重建索引。这个过程是十分消耗数据库性能的。b. 区分度不大的字段上不宜建立索引类似于性别这种区分度不大的字段建立索引的意义不大。因为不能有效过滤数据性能和全表扫描相当。另外返回数据的比例在30%以外的情况下优化器不会选择使用索引。c. 业务上具有唯一特性的字段即使是多个字段的组合也必须建成唯一索引。虽然唯一索引会影响insert速度但是对于查询的速度提升是非常明显的。另外即使在应用层做了非常完善的校验控制只要没有唯一索引在并发的情况下依然有脏数据产生。d. 多表关联时要保证关联字段上一定有索引。e. 创建索引时避免以下错误观念索引越多越好认为一个查询就需要建一个索引宁缺勿滥认为索引会消耗空间、严重拖慢更新和新增速度抵制唯一索引认为业务的唯一性一律需要在应用层通过“先查后插”方式解决过早优化在不了解系统的情况下就开始优化。3. 小结对于自己编写的SQL查询语句要尽量使用EXPLAIN命令分析一下做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱SQL能力是一个重要的指标。作为后端程序员深以为然。(完)ps最近学习资料有更新wx公众号Java7749#java讨论区# #编程语言JAVA# #MySQL#
