哪里有网站推广软件,做职业资格考试的网站有哪些,佛山市网站建设 骏域动力,江苏省建设培训网站SQL性能分析
SQL执行频率 MySQL 客户端连接成功后#xff0c;通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令#xff0c;可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次#xff0c;通过sql语句的访问频次#xff0c;我们可… SQL性能分析
SQL执行频率 MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次通过sql语句的访问频次我们可以判断到当前数据库到底是以查询为主还是以增删改为主从而为数据库优化提供参考依据。 如果是以增删改为主我们可以考虑不对其进行索引的优化。 如果是以查询为主那么就要考虑对数据库的索引进行优化了。 慢查询日志 如果当前数据库是以查询为主我们可以通过慢查询日志来查看耗时较长的查询慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有 SQL语句的日志。MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。 慢查询日志默认是关闭的 开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息同时重启mysql服务使配置文件生效 # 开启MySQL慢日志查询开关 slow_query_log1 # 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志 long_query_time2 查询600万数据的表 毫无疑问出现在慢查询日志上
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数能够看到当前MySQL是否支持profile操作
#查询是否有profile参数
select have_profiling
#查询profile功能是否开启 0表示关闭 1表示开启
select profiling可以看到当前MySQL是支持 profile操作的但是开关是关闭的。可以通过set语句在 session/global级别开启profiling
SET profiling 1; 执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id; explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行 过程中表如何连接和连接的顺序。在select语句开头加上explain关键词即可通过explain各字段的值我们可以分析出此时的sql语句走不走索引有没有回表查询等情况方便我们进行sql优化 Explain 执行计划中各个字段的含义:
字段含义 id select查询的序列号表示查询中执行select子句或者是操作表的顺序 (id相同执行顺序从上到下id不同值越大越先执行)。 select_type 表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接 或者子查询、PRIMARY主查询即外层的查询、 UNIONUNION 中的第二个或者后面的查询语句、 SUBQUERYSELECT/WHERE之后包含了子查询等 type 表示连接类型性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 possible_key 显示可能应用在这次查询的索引一个或多个。 key 实际使用的索引如果为NULL则没有使用索引。 key_len 表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好。 rows MySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值 可能并不总是准确的。 filtered 表示返回结果的行数占需读取行数的百分比filtered 的值越大越好。 索引使用
效率验证
准备一张百万数据的表根据id查询数据我们可以发现此时耗时仅为0.01秒这是因为id默认就是主键索引这是已经有索引的查询情况 根据name字段查询时此时没有建立name字段的索引可以看到耗时达到6秒之久 建立索引之后根据name查询耗时仅有0.01秒可见索引能大大提升查询效率 最左前缀法则
如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始 并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。
以tb_user表为例此时有idx_user_pro_age_sta这个联合索引这个联合索引涉及到三个字段顺序分别为profession agestatus。 对于最左前缀法则指的是查询时最左变的列也就是profession必须存在否则索引全部失效。 而且中间不能跳过某一列否则该列后面的字段索引将失效。
explain select * from tb_user where profession 软件工程 and age 31 and status0 联合索引的三个字段都在联合索引必然生效只要where条件后面的三个字段都在索引就会生效与字段的先后顺序无关。 我们尝试profession后面的两个字段逐步减少会发现联合索引仍然生效正说明只要索引的最左边的字段在where子句中索引就会生效。从图中我们也可以推测出profession字段索引长度为47、age 字段索引长度为2、status字段索引长度为5。 但此时我们将查询条件中的profession去掉此时联合索引失效。 如果最左的列存在但是跳过了中间的列那么只会中间列之后的索引都不会生效虽然走了联合索引但长度只有47为profession字段索引长度。 范围查询 联合索引中出现范围查询(,)范围查询右侧的列索引失效。从索引长度可以看到status字段的索引并没有生效 但如果我们将范围查询加上等号即≥和≤这种形式联合索引就生效在业务允许的情况下尽可能的使用类似于 或而避免使用 或 来避免索引失效 索引失效情况 索引列函数运算
当根据phone字段进行等值匹配查询时, 索引生效。 当进行函数运算时索引就失效走的是全表扫描 如果进行的是数值运算索引仍然生效 字符串不加引号
如果不给phone字段添加引号造成索引类型不匹配索引也会失效 模糊查询
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。
头部模糊查询走的是全表扫描 如果是尾部模糊则走联合索引索引生效如果前后模糊查询由于前面模糊查询已使索引失效所以也是索引失效 or连接条件
用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会 被用到因为or前面的用了索引or后面的列没有索引还是要走全表扫描mysql优化器就会判断直接全表扫描避免浪费一次查找索引树的时间
数据分布影响 相同的SQL语句只是传入的字段值不同最终的执行计划也完全不一样这是因为MySQL在查询时会评估使用索引的效率与走全表扫描的效率如果走全表扫描更快则放弃索引走全表扫描。 因为索引是用来索引少量数据的如果通过索引查询返回大批量的数据则还不 如走全表扫描来的快此时索引就会失效。即数据分布情况也会影响索引是否生效 SQL提示 sql提示就是在执行查询时我们自己指定要使用的索引 此时我们有 profession这个字段的单列索引 我们可以看到possible_keys中 idx_user_pro_age_sta,idx_user_pro 这两个 索引都可能用到最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。我们想看到的是走单列索引毕竟我的mysql我做主这时候就要用到sql提示啦
我们可以在select语句时加上use index(索引名)来建议mysql使用我们指定的索引仅仅是建议mysql内部还会再次进行评估 上面的use index 仅仅是建议要是mysql不听怎么办这时候就需要force index来强制mysql执行我们指定的索引即使效率可能下降但爷乐意千金难买爷乐意下图我们可以看到正常情况下肯定不会走sta索引的毕竟我们where子句是根据profession来查的但通过我们的强制也能让mysql执行这一索引 覆盖索引 尽量使用覆盖索引减少select * 覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到。使用覆盖索引能减少能大大提高查询其原因就是需要返回的列在索引列已经全部找到不需要回表查询了这也是mysql优先使用联合索引的原因 前缀索引
介绍 当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让 索引变得很大查询时浪费大量的磁盘IO 影响查询效率。此时可以只将字符串的一部分前缀建 立索引这样可以大大节约索引空间从而提高索引效率。 语法
create index idx_xxxx on table_name(column(n)) ;
为tb_user表的email字段建立长度为5的前缀索引。 前缀长度 可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值 索引选择性越高则查询效率越高 唯一索引的选择性是1这是最好的索引选择性性能也是最好的。即要保证取得的前缀尽量唯一不重复。 查询流程 前缀索引相当于二级索引但他匹配到时必须回表查询确认根据前缀索引匹配到行数据的email值跟sql语句的email值是否一样同时要遍历到链表的下一个元素看是否与前缀索引匹配如果是就要重复刚刚的流程然后返回数据如果不是直接返回数据即可 单列索引与联合索引 前文提到的覆盖索引mysql会优先使用联合索引以此来减少回表查询提高查询效率 单列索引即一个索引只包含单个列。 联合索引即一个索引包含了多个列。 索引设计原则 1).针对于数据量较大且查询比较频繁的表建立索引。 2). 针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。 3). 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。 4). 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。 5). 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间 避免回表提高查询效率。 6). 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率同时索引也会占用硬盘空间。 7). 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时它可以更好地确定哪个索引最有效地用于查询。
