网站访问量统计代码怎么做,西安seo公司,建筑公司财务做账流程,wordpress 静态链接文章目录 1、定位慢查询2、慢查询的原因分析3、索引3.1 数据结构选用#xff1a;二叉树 红黑树3.2 数据结构选用#xff1a;B树 4、聚簇索引、非聚簇索引、回表查询4.1 聚簇索引、非聚簇索引4.2 回表查询 5、覆盖索引、超大分页优化5.1 覆盖索引5.2 超大分页处理 6、索… 文章目录 1、定位慢查询2、慢查询的原因分析3、索引3.1 数据结构选用二叉树 红黑树3.2 数据结构选用B树 4、聚簇索引、非聚簇索引、回表查询4.1 聚簇索引、非聚簇索引4.2 回表查询 5、覆盖索引、超大分页优化5.1 覆盖索引5.2 超大分页处理 6、索引的创建7、索引的失效8、SQL优化的经验9、面试 1、定位慢查询
Arthas在线查看方法耗时运维工具Prometheus链路追踪工具Skywalking、Zipkin、OpenTemplate MySQL自带的慢日志记录执行超过n秒的SQL
//修改配置文件,文件位置
/etc/my.cnf//开启慢查询开关生产环境不建议开启会损失部分性能
slow_query_log1//设置超过2秒的SQL
long_query_time2慢SQL被记录到/var/lib/mysql/localhost-slow.log 2、慢查询的原因分析
慢SQL通常是因为
聚合查询多表查询表数据量过大查询深度分页查询
前三种可尝试使用SQL执行计划分析原因
# SELECT语句前添加EXPLAIN或DESC查看SQL语句执行情况的信息
EXPLAIN select * from t_table;
DESC select * from t_table;此时SELECT返回的不是表数据是一些执行信息
possible key 当前sql可能会使用到的索引key 当前sql实际命中的索引key_len 索引占用的大小key和key_len搭配检查是否存在索引失效Extra额外的优化建议 type这条sql的连接的类型性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
system查询MySQL系统内置库的表const根据主键查询eq_re主键索引查询或唯一索引查询ref索引查询range范围查询index索引树扫描遍历整个索引all不走索引全盘扫描3、索引
一种用于高效查数据的数据结构以某种方式指向表里的数据。如下表不加索引查age45的数据就是逐行对比 遍历整个表直至最后一行效率低下 如果去维护一个类似二叉树的结构再查age45的数据则直接从根节点开始⇒ 45 36去右侧 ⇒ 45 48 ⇒去左侧 ⇒ 查找完毕如此查找效率提升这即索引的思想
3.1 数据结构选用二叉树 红黑树
MySQL索引底层的数据结构是B树。不选二叉树是因为 如果数据递增或递减此时二叉树变链表即最坏情况的二叉树效率很低。既然二叉树有平衡性问题那再考虑自平衡的二叉树 ⇒ 红黑树 红黑树时间复杂度为O(log n)但其也是一个二叉树每个节点最多只能两个分支因此大数据量下红黑树会很高。 ⇒ B树每个节点可以多个分支是一种多叉路衡查找树。以一颗5阶B树为例最大度数mas-degree为5每个节点最多存储4个key 图中的灰色部分存储指针指向子节点。如20左侧的指针指向的就是20以内的数据20和30之间的指针则指向20~30之间的数据以此类推。且绿色部分存储的是对应的那条数据。
3.2 数据结构选用B树
相比二叉树B树是一种矮胖树B树则是B树的一种优化非叶子节点只存储指针不存储数据。只有在叶子节点才去存储对应的数据前面的非叶子节点起一个导航的作用非叶子节点上就匹配到的数据在叶子节点上也能找到这个数。 MySQL默认的存储引擎InnoDB默认使用B树实现索引。相比B树B树
磁盘读写代价更低只有叶子节点存数据查询效率更稳定最后都要落到叶子节点适合于区间查询叶子节点之间的双向指针比如查6~34这个区间的数先从根节点对比走左边到16再走左边到6再跟双向指针拿到6到34的数据不需要再从根节点开始重新找一次
4、聚簇索引、非聚簇索引、回表查询
4.1 聚簇索引、非聚簇索引
聚簇索引又叫聚集索引即B树的叶子节点保存的是整行数据。非聚簇索引又叫二级索引即B树单独叶子节点存储的是那行数据对应的主键 聚簇索引选取规则节点里存哪个
如果存在主键主键索引就是聚集索引如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引如果表没有主键或没有合适的唯一索引则InnoDB 会自动生成一个rowid 作为隐藏的聚集索引
如下建立聚簇索引时这张表有主键ID因此节点中存储的是ID值最后叶子节点中存的那个row是整条数据值。 再比如给表的name字段建立非聚簇索引节点存储name的值最后的叶子节点存储的是这条数据的主键值
4.2 回表查询
select * form user where name Arm;给name字段加了非聚簇索引因此执行如上SQL先根据name的非聚簇索引的B树 ⇒ A小于L走左边到G和J再走左边找到Arm ⇒ 因为是select *而非聚簇索引的叶子节点存的是主键 ⇒ 拿着主键回到聚簇索引从其根节点开始查 ⇒ 聚簇索引的叶子节点存了整行数据返回select * 的结果 总之回表查询就是先根据非聚簇索引找到主键值再根据主键值到聚簇索引拿到整行数据
5、覆盖索引、超大分页优化
5.1 覆盖索引
即查询使用了索引并且你需要返回的字段在索引中能够全部找到。 select * form tb_user where id 1;是覆盖索引虽然select * 但其where是根据id过滤的即用的是主键索引、聚簇索引索引的叶子节点存了整行数据需要返回的字段在索引中能够全部找到 select id, name from tb_user where name Arm;是覆盖索引where根据name过滤走name的非聚簇索引最后叶子节点存了id而最后需要返回的就是id和name select id, name, gender from tb_user where name Arm;不是覆盖索引索引中拿不到gender值需要回表查询 很明显能一次查询出来的符合覆盖索引的效率最高走回表查询的SQL效率低
5.2 超大分页处理
使用limit分页查需要对数据进行排序数据量很大时效率很低 比如limit 900 000010此时需要排序前9000010行数据再返回9000000到9000010行这10行 解决方案是覆盖索引 子查询 即先根据主键去分页order by id 不select *而是select id再和原来的表关联查
6、索引的创建
需要创建索引的场景
数据量大单表超过10万行且查询频繁给常作为where、order by、group by操作的字段创建索引如果字段是字符串类型且长度很长给其建立索引压力大可截取前几个字建立前缀索引 多用联合索引而不是单列索引。因为如果给A B两个字段建立了联合索引刚好又select A, B from table where A 1就是覆盖索引避免了回表查询效率更高。下图即给name、status、address三个字段建了联合索引 索引并不是越多越好因为增删改也要同步去维护索引索引多了会影响增删改的效率
7、索引的失效
给表tb_seller的namestatusaddress字段创建联合索引 索引失效的场景
1违反最左前缀法则最左前缀法则即select后面的字段必须从索引的最左前列开始并且不跳过索引中的列。以下为索引不失效的写法 以下写法索引失效 以下写法中途跳过了联合索引的某一列只有最左侧字段索引生效从key_len的大小可以看出其只命中了一个字段 2对status范围查询则status右边的列address没有用到索引但namestatus还是走了索引了3在索引所在的列上进行运算索引会失效4字符串不加单引号索引失效因为不对字符串类型加单引号MySQL优化器会自动进行类型转换造成索引失效
5以%开头的Like模糊查询索引失效注意如果仅仅是末尾进行模糊查询索引不会失效 8、SQL优化的经验
1表设计优化
设置合适的数值类型tinyint、int、bigint字符串类型char和varcharchar定长、效率高varchar长度灵活可变根据字符串实际长度来但效率稍低 2SQL语句优化
避免select *避免索引失效的写法使用union all代替unionunion会把两个查询的结果再做个去重 避免where中对字段进行计算操作join表时能用inner join不left join或者right join业务必须要用时可将小表行数少的表放外面。原因参考for循环嵌套如下写法MySQL进行三次连接每次连接进行1000次操作反之就是进行1000次连接每次连接进行3次操作inner join 就会自动优化把小表放外面。left join或right join就不会把小表放外面 3读写分离主从复制
用于避免写操作影响查询效率主库写从库读 4索引的创建和失效 5分库分表见下篇
9、面试
