mysql-性能分析+索引

一.性能分析1.查看执行频次MySQL客户端连接成功后通过show [session|global] status命令可以提供服务器状态信息通过以下指令可以查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次SHOW GLOBAL|SESSION STATUS LIKE Com_______;(7个下划线)2.慢查询日志慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。查看慢查询日志开关状态ON为开启OFF为关闭show variables like slow_query_log;启用慢查询日志重启后失效SET GLOBAL slow_query_log ON;MySQL的慢查询日志默认没有开启需要在MySQL的配置文件/etc/my.cnf中配置如下信息重启后不会失效1.开启慢查询日志开关slow_query_log12.设置慢查询日志的时间例如2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志long_query_time2在执行这两条命令前需要以管理员身份打开慢查询日志文件具体操作可以上网搜索更改后记得重启MySQL服务日志文件位置/var/lib/mysql/localhost-slow.log3.profileshow profile 能在做SQL优化时帮我们了解时间都耗费在哪里。通过 have_profiling 参数能看到当前 MySQL 是否支持 profile 操作查看当前 MySQL 是否支持 profile 操作SELECT have_profiling;查看当前profiling是否开启select profiling;profiling 默认关闭可以通过set语句在session/global级别开启 profilingSET profiling 1;查看所有语句的耗时show profiles;查看指定query_idSQL语句的编号的SQL语句各个阶段的耗时show profile for query query_id;查看指定query_idSQL语句的编号的SQL语句CPU的使用情况show profile cpu for query query_id;4.explainEXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序语法直接在select语句之前加上关键字 explain / descEXPLAIN|DESC SELECT 字段列表 FROM 表名 HWERE 条件;EXPLAIN 各字段含义:idselect 查询的序列号表示查询中执行 select 子句或者操作表的顺序id相同执行顺序从上到下多表查询id不同值越大越先执行子查询select_type表示 SELECT 的类型常见取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、allNULL查询不访问任何表时出现一般不会出现system当访问系统表或表中仅有一行记录时出现const使用PRIMARY KEY或者UNIQUE索引进行精确匹配且只匹配到一行记录时会出现eq_ref在连接查询里使用PRIMARY KEY或者UNIQUE索引进行连接且对于每个来自前面表的记录在当前表中都能通过索引找到唯一匹配的记录时出现ref使用非唯一性索引查询时会出现range使用索引进行范围查询时出现index查询需要扫描整个索引树来获取数据时出现不一定扫描全表all扫描全表数据时出现从优到劣NULL system const eq_ref ref range index ALLpossible_key可能应用在这张表上的索引一个或多个Key实际使用的索引如果为 NULL则没有使用索引Key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好rowsMySQL认为必须要执行的行数在InnoDB引擎的表中是一个估计值可能并不总是准确的filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好二.索引1.概述索引是帮助 MySQL高效获取数据的数据结构有序。优点提高数据检索效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗缺点索引列也是要占用空间的索引大大提高了查询效率但降低了更新的速度比如 INSERT、UPDATE、DELETE2.索引结构索引结构描述BTree最常见的索引类型大部分引擎都支持B树索引Hash底层数据结构是用哈希表实现只有精确匹配索引列的查询才有效不支持范围查询R-Tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-Text(全文索引)是一种通过建立倒排索引快速匹配文档的方式类似于 Lucene, Solr, ES索引InnoDBMyISAMMemoryBTree索引支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text5.6版本后支持支持不支持2.1 BTree二叉树的缺点可以用红黑树来解决:红黑树也存在大数据量情况下层级较深检索速度慢的问题。2.2 B-Tree多路平衡查找树以一棵最大度数max-degree指一个节点的子节点个数为55阶的 b-tree 为例每个节点最多存储4个key5个指针每一个节点都存储数据2.3 BTree所有的数据都会出现在叶子节点叶子节点形成一个单向链表MySQL 索引数据结构对经典的 BTree 进行了优化。在原 BTree 的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的 BTree提高区间访问的性能2.4 Hash哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。如果两个或多个键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决特点Hash索引只能用于对等比较、in不支持范围查询betwwn、、、…无法利用索引完成排序操作查询效率高通常只需要一次检索就可以了效率通常要高于 BTree 索引存储引擎支持在MySQL中支持hash索引的是Memory引擎而InnoDB中具有自适应hash功能hash索引是存储引擎根据 BTree 索引在指定条件下自动构建的。2.5 思考为什么 InnoDB 存储引擎选择使用 BTree 索引结构相对于二叉树层级更少搜索效率高对于 B-Tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针也跟着减少要同样保存大量数据只能增加树的高度导致性能降低相对于 Hash 索引BTree 支持范围匹配及排序操作3.索引分类3.1 分类分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个FULLTEXT在 InnoDB 存储引擎中根据索引的存储形式又可以分为以下两种分类含义特点聚集索引(Clustered Index)将数据存储与索引放一块索引结构的叶子节点保存了行数据必须有而且只有一个二级索引(Secondary Index)将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个3.2 演示图3.3 聚集索引选取规则如果存在主键主键索引就是聚集索引如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引如果表没有主键或没有合适的唯一索引则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引3.4 思考1以下 SQL 语句哪个执行效率高为什么select * from user where id 10; select * from user where name Arm; -- 备注id为主键name字段创建的有索引答第一条语句因为第二条需要回表查询相当于两个步骤。2InnoDB 主键索引的 BTree 高度为多少答假设一行数据大小为1k一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间主键假设为bigint占用字节数为8. 可得公式n * 8 (n 1) * 6 16 * 1024其中 8 表示 bigint 占用的字节数n 表示当前节点存储的key的数量(n 1) 表示指针数量比key多一个。算出n约为1170。如果树的高度为2那么他能存储的数据量大概为1171 * 16 18736 如果树的高度为3那么他能存储的数据量大概为1171 * 1171 * 16 21939856。另外如果有成千上万的数据那么就要考虑分表。from tb_user where substring(phone, 10, 2) 15;字符串类型字段使用时不加引号索引将失效。如explain select * from tb_user where phone 17799990015;此处phone的值没有加引号模糊查询中如果仅仅是尾部模糊匹配索引不会失效如果是头部模糊匹配索引失效。如explain select * from tb_user where profession like %工程;前后都有 % 也会失效用 or 分割开的条件如果 or 其中一个条件的列没有索引那么涉及的索引都不会被用到如果 MySQL 评估使用索引比全表更慢则不使用索引4.语法创建索引CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);如果不加 CREATE 后面索引类型参数则创建的是常规索引查看索引SHOW INDEX FROM table_name;删除索引DROP INDEX index_name ON table_name;5.索引使用规则5.1 最左前缀法则如果索引关联了多列联合索引要遵守最左前缀法则最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效后面的字段索引失效。比较特殊的一种情况是如果直接跳跃第一列那么第一列后面的索引都会失效即此时联合索引完全失效联合索引中出现范围查询, 范围查询右侧的列索引失效。可以用或者来规避索引失效问题。字段的位置可以任意只要缺少复合索引某个字段后面的字段索引全部失效5.2 索引失效情况在索引列上进行运算操作索引将失效。如explain select * from tb_user where substring(phone, 10, 2) 15;字符串类型字段使用时不加引号索引将失效。如explain select * from tb_user where phone 17799990015;此处phone的值没有加引号模糊查询中如果仅仅是尾部模糊匹配索引不会失效如果是头部模糊匹配索引失效。如explain select * from tb_user where profession like %工程;前后都有 % 也会失效用 or 分割开的条件如果 or 其中一个条件的列没有索引那么涉及的索引都不会被用到如果 MySQL 评估使用索引比全表更慢则不使用索引5.3 SQL 提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。使用索引select * from 表名 use index(索引名) where 查询条件;不使用哪个索引select * from 表名 ignore index(索引名) where 查询条件;必须使用哪个索引select * from 表名 force index(索引名) where 查询条件;use 是建议不一定使用实际使用哪个索引 MySQL 还会自己权衡运行速度去更改force就是无论如何都强制使用该索引。5.4 覆盖索引即查询使用了索引并且需要返回的列在该索引中已经全部能找到尽量使用覆盖索引减少select *的书写5.4.1 explain 中 extra 字段含义using index condition查找使用了索引但是需要回表查询数据using where; using index;查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询如果在聚集索引中直接能找到对应的行则直接返回行数据只需要一次查询哪怕是select *如果在辅助索引中找聚集索引如select id, name from xxx where namexxx;也只需要通过辅助索引(name)查找到对应的id返回name和name索引对应的id即可只需要一次查询如果是通过辅助索引查找其他字段则需要回表查询如select id, name, gender from xxx where namexxx;所以尽量不要用select *容易出现回表查询降低效率除非有联合索引包含了所有字段5.4.2 面试题一张表有四个字段id, username, password, status由于数据量大需要对以下SQL语句进行优化该如何进行才是最优方案select id, username, password from tb_user where usernameitcast;解给username和password字段建立联合索引则不需要回表查询直接覆盖索引5.5 前缀索引当字段类型为字符串varchar, text等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率此时可以只降字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率create index 索引名 on 表名(列名(n));前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。求选择性公式截取长度可以任取不断运行直到合适为止select count(distinct 列名) / count(*) from 表名; select count(distinct substring(列名, 1, 截取长度)) / count(*) from 表名;show index 里面的sub_part可以看到接取的长度5.6 不满足最左前缀法则仍可能触发复合索引的情况只是可能触发优化器可能仍选择全表扫描。5.6.1 覆盖索引当查询的字段完全包含在联合索引中即使WHERE条件不满足最左前缀MySQL 可能选择 全索引扫描而非全表扫描来直接返回数据例如索引a,b,cSELECT b, c FROM table WHERE b 10;数据可直接从索引中提取无需回表优化器可能选择扫描整个索引。5.6.2 索引下推ICPMySQL 5.6 支持 ICP当查询条件包含部分联合索引列时即使不满足最左前缀存储引擎层仍会利用索引过滤数据减少回表次数例如索引a,b,cSELECT * FROM table WHERE a 1 AND c 3;a 作为最左前缀生效c 的条件通过 ICP 在存储引擎层过滤。索引下推ICP需在 MySQL 5.6 且开启optimizer_switchindex_condition_pushdownon5.6.3 排序/索引优化若 ORDER BY 或 GROUP BY 的字段顺序与联合索引一致即使 WHERE 条件不满足最左前缀仍可能利用索引优化排序或分组例如索引a,bSELECT * FROM table WHERE a 1 ORDER BY b;索引 (a, b) 天然按 a, b 排序优化器可能选择索引避免 filesort。5.6.4 范围查询后的等值查询若查询条件中 最左前缀为范围查询后续列的等值条件可能仍会使用索引例如索引a,b,cSELECT * FROM table WHERE a 1 AND b 2;索引会先按 a 的范围查找再匹配 b 的等值条件需结合索引下推。5.7 单列索引和联合索引单列索引即一个索引只包含单个列联合索引即一个索引包含了多个列在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引多条件联合查询时MySQL优化器会评估哪个字段的索引效率更高会选择该索引完成本次查询5.8 设计原则针对于数据量较大且查询比较频繁的表建立索引针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高如果是字符串类型的字段字段长度较长可以针对于字段的特点建立前缀索引尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价就越大会影响增删改的效率如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询