网站模板怎么用呢,wordpress设计导航,企业服务云平台,怎样建设个人手机网站根据Mysql索引原理及优化这个博主的视频学习#xff0c;对现在的岁月云项目中mysql进行优化#xff0c;我要向这个博主致敬#xff0c;之前应用居多#xff0c;理论所知甚少#xff0c;于是将学习到东西#xff0c;应用下来#xff0c;看看是否有好的改观。
1 索引原理…根据Mysql索引原理及优化这个博主的视频学习对现在的岁月云项目中mysql进行优化我要向这个博主致敬之前应用居多理论所知甚少于是将学习到东西应用下来看看是否有好的改观。
1 索引原理
1.1 BTree 索引的目的是用来快速查询的怎样查找快这就用到大学所学的数据结构的知识。理论不需要我来写视频博主讲的比我好。我只记录我所理解的。如果所有的左边都比当前节点小所有的右边都比当前节点大这样就很容易缩小范围于是想到二叉查找树。 但是二叉查找树当数据有序的时候就可能比较惨形成单向链表时间复杂度升级为O(n)于是就有了平衡二叉树AVL 树因为平衡所以查询稳定但是因为一个节点最多有两个节点树高问题依旧存在。 于是就有了B树因为B树允许一个节点存放多个数据树高再次降低但B树的存储结构是key和data都在节点中这就是一则使得查询性能不稳定二则因为节点也保存了数据内存增加且data大了key就小了节点自然也就增多了B树的层高也相应就升高了I/0次数增加性能就会变差。另外B树的数据在树的节点当中因此适合随机查询而不适合范围查询在Btree在线这个地方可以演示插入的过程。 接着思想演化为选择B树B树的特点是叶子节点包括全部关键字和相应记录的指针则查询自然是稳定的Btree中叶子节点采用双向链表的结构则范围查询的问题也就解决了。非叶子节点只有索引没有数据因此一节点就可以存储更多的内容树高就降低了。 InnoDB中最小存储单元是页每页16KBmysql设计者将BTree的一个节点大小设置为一个页16KB这样每个节点只需要一次IO就能够完整载入整页数据。知道了这个概念就知道后面查询成本怎么计算的。 一个高度为2的BTree存在一个根节点和若干叶子节点总记录数根节点指针数*单个叶子节点的记录数。如果是主键索引聚簇索引根节点指针数根据主键长度来mysql中int类型占4个字节指针类型占6个字节那么一页16*1024/461638即一个节点最多存储1638个索引指针。每个叶子节点记录数跟一行记录大小有关如果一行记录为1KB则一页记录16行如此得出高度为2的BTree总记录数1638*1626208。同理告诉为3的BTree可以存放记录数1638*1638*1642,928,704所以并不是别人说的mysql单表不能超过2000万这个固定数字。
1.2 聚簇索引
1.2.1 回表 InnoDB 使用聚簇索引来存储表的数据而辅助索引的叶子节点不仅包含索引列的值还包含对应的主键值。这种设计使得通过辅助索引查找数据时能够快速定位到聚簇索引中的具体行位置。 因此就有了回表操作先查辅助索引但获取数据实际是根据聚簇索引主键索引得到对应的数据这个过程就是回表扫描了辅助索引树和聚簇索引树。 使用覆盖索引解决回表问题。一个索引包含了所需查询的所有字段就可以不需要回表。这个方法虽然没有问题但是中小型系统估计就不会去考虑这个优化研发成本跟这点性能成本比更本就不算什么。 1.2.2 索引下推 mysql5.6引入用于查询优化在索引遍历过程中对于索引包含的字段先做判断把不符合条件的记录过滤掉减少回表次数。
1.2.3 主键类型选择 多语言、表情符号和减少编码转换使得utf8mb4 成为首选。在utf8mb4中1个char占4个字节。执行下面的语句可以知道自己的表中各字段占用的空间。
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_OCTET_LENGTH,CASEWHEN DATA_TYPE IN (char, varchar) THEN CHARACTER_OCTET_LENGTHWHEN DATA_TYPE tinyint THEN 1WHEN DATA_TYPE smallint THEN 2WHEN DATA_TYPE mediumint THEN 3WHEN DATA_TYPE int THEN 4WHEN DATA_TYPE bigint THEN 8WHEN DATA_TYPE IN (float, double) THEN NUMERIC_PRECISION / 8 1WHEN DATA_TYPE decimal THEN CEIL(NUMERIC_PRECISION / 9) * 4 IF(NUMERIC_SCALE 0, 2, 0)ELSE NULLEND AS estimated_bytes
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA accAND TABLE_NAME acc_analysis_equation; 知道这个基础就知道数据表中状态字段最好使用tinyint不要使用char了。而如下面bigint也才8个字节如果使用32位uuid则需要128个字节索引存储空间相比下将16倍。 2 Explain性能分析
2.1 单表 从慢日志中找到一条插入时的问题explain结果如下 创建索引后再次查看 这个语句就过关了。 不要小看这个索引创建可以再慢日志可以看到有很多相同的语句这样性能就会提升很多了。下图可以看到Rows_examined预计扫码记录数203万这个数据不小。 2.2 联表 以下面的慢日志样例作explain。
id表示查询中执行子句或操作表的顺序id相同执行的顺序是由上到下。如果由子查询id是不同的。id越大执行优先级越高也就是说子查询会优先执行。type找到数据所需要的扫码方式重要指标 完整的连接类型,一般需要保证查询至少range级别最好能到ref。const代表主键索引或者唯一索引req_ref代表对于前表中每一行后表只有一行被扫描这个只连接使用索引的部分都是主键或者唯一非空索引才会出现ref代表普通索引对于前表的每一行后表有可能有多于一行的数据被扫描。range代表范围查询between、in、、都是范围查询。index代表SQL使用了索引但没有通过索引进行过滤需要扫描索引上的全部数据例如Innodb索引count查询就会扫描索引上的全部数据进行统计。
systemconsteq_refreffulltextref_or_null
index_mergeunique_subqueryindex_subquery
rangeindexALL
possible_keys可能应用到这张表的索引一个或多个列出但实际不一定会用key实际使用的索引若为null两种情况1、没有建立索引。2、建立索引但索引失效。查询中使用了覆盖索引该索引只会出现在key列表中。
key_len索引中使用的字节数。 refconst代表常数等值查询、连接查询中ref字段显示驱动表关联的字段。 rows表示mysql在检索时估算的行记录数量。extra①using filesort查询结果进行文件排序性能非常差需要优化②Using temporary;使用临时表存储数据结果。③Using where查询的列未被索引覆盖被全表扫描。④Using index使用了索引获取数据使用到了聚簇索引或者覆盖索引非常好。⑤Using join buffer使用了连接缓存会显示join连接查询时所需要的查询算法。⑥Using index condition表示了使用了索引下推仅适用于二级索引因为聚簇索引完整记录是被InnoDb缓存中。 3 高性能索引创建及使用策略
3.1 索引列类型尽量小 知道了索引原理就知道了索引列类型越小那么索引空间就比较少查询性能也就越快一个页纳入更多的索引数据也就减少了磁盘IO损耗。内存中可以纳入更多的数据页缓存在实战中更关注在选择自增、雪花id还是uuid。 int类型自增对于绝大多数项目是够用的但是多租户模式下一般用联合所以这个自增就不太起作用。就可以用到雪花id。而uuid是字符串排序自然是按照字母的循序而因为字母是无序的自然查询性能就慢一些。
3.2 选择性尽量高 索引是为了快速定位如果选择性低的比如凭证类型、性别之类的基本就是固定选项这些字段设置索引就没有多大意义不能帮你过滤大批的数据。 例如下例中现有的group_id是凭证类型收、记、付、转只会有四个选项更甚的是大多数用户用的都是记账凭证。 按照选择性公式计算选择性不同值得数量/总行数按照下面得计算选择性0.0025非常低代表group_id列重复性很高不适合作为索引项。选择性越高越适合作为索引项。因此需要将group_id列从联合索引中去除以缩短索引字段长度存储更多有效数据。 对比一下去掉前后从下面可以看到没有变化因此这个索引字段去掉是合理得。 因为数据库做了分表于是写一个存储过程批量更新表索引如下
BEGIN-- 定义一个游标来遍历表名DECLARE done INT DEFAULT FALSE;DECLARE tbl_name VARCHAR(255);DECLARE cur CURSOR FORSELECT table_nameFROM information_schema.tablesWHERE table_schema DATABASE() AND table_name REGEXP ^acc_voucher_[0-9]$;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE;OPEN cur;read_loop: LOOPFETCH cur INTO tbl_name;IF done THENLEAVE read_loop;END IF;-- 删除旧索引SET sql CONCAT(ALTER TABLE , tbl_name, DROP INDEX idx_adId_period_groupId_num);PREPARE stmt FROM sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;-- 添加新索引SET sql CONCAT(ALTER TABLE , tbl_name, ADD INDEX idx_adId_period_num (as_id, period, num) USING BTREE);PREPARE stmt FROM sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END LOOP;CLOSE cur;
END
3.3 最佳左前缀法则 针对联合查询因为索引排序也是有比较过程多个字段肯定有一个顺序你多个索引字段自然回选择左的字段作为先排序的。这里有回到了BTree的原理了BTree是局部有序。 查看下面的索引最左指的是索引Fieds左边第一并不是where条件的最左因为查询引擎会进行优化。 从下图可以看到索引失效了进行了全表扫描。 接着我们调整索引顺序将relate_company调整到最左边,再次查看索引生效。 3.4 like使用
3.5 null值
3.6 不要对索引列做任何操作 索引减少了磁盘IO因此可以先获取数据然后通过程序来做你想做的业务而不是在sql语句上去做业务直接相关的函数操作因为索引列作函数操作导致索引失效而内存速度与磁盘IO速度无法媲美。因此才有这个原则。 字符串类型隐式转换问题如下面accounting_standard字段定义的char(1)可以查看到key_len是4已经发生了隐式转换. 下面是按照字符串的方式查找扫描的rows相比隐式转换的要少。 4 慢日志查询分析
4.1 慢日志参数 my.cnf配置3个配置跟他有关系
# 开启慢日志
slow_query_log 1
# 慢日志的位置
slow_query_log_file /data/mysql_3306/logs/sql_query_slow.log
# 单位为s
long_query_time 1log_queries_not_using_indexes表述为使用索引的查询也会被记录到慢查询日志中因此再调优的时候需要开启执行命令set global log_queries_not_using_indexes1;
mysql show variables like %log_output%;
----------------------
| Variable_name | Value |
----------------------
| log_output | FILE |
----------------------
1 row in set (0.02 sec)mysql show variables like %log_queries%;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| log_queries_not_using_indexes | OFF |
--------------------------------------
1 row in set (0.01 sec)4.2 慢日志样例
4.2.1 超过long_query_time示例 从慢查询中选取一条查看Query_time表明查询执行的时间Lock_time为等待锁的时间Rows_sent表示查询结果的数量Rows_examine表示查询扫描的行数。
# Time: 2024-12-06T13:25:50.57019508:00
# UserHost: root[root] [10.101.12.88] Id: 367258
# Query_time: 1.286461 Lock_time: 0.000064 Rows_sent: 420 Rows_examined: 2044
SET timestamp1733462750;
selectaccVoucher.as_id as_id_accVoucher, accVoucher.id id_accVoucher, accVoucher.group_id group_id_accVoucher,accVoucherEntry.debit_amount debit_amount_accVoucherEntry, accVoucherEntry.credit_amountcredit_amount_accVoucherEntry,accVoucherEntry.price price_accVoucherEntry, accVoucherEntry.count count_accVoucherEntry,accVoucherEntry.fc_id fc_id_accVoucherEntry, accVoucherEntry.rate rate_accVoucherEntry,accVoucherEntry.debit_amount_original debit_amount_original_accVoucherEntry,accVoucherEntry.credit_amount_original credit_amount_original_accVoucherEntry,accVoucherEntry.line_num line_num_accVoucherEntryfrom acc_voucher_15 accVoucherinner join acc_voucher_entry_15 accVoucherEntry on accVoucherEntry.v_id accVoucher.id andaccVoucherEntry.as_idaccVoucher.as_idWHERE (accVoucher.as_id 112665 AND accVoucher.period 202107 AND accVoucher.period 202107) ORDER BY accVoucher.period ASC,accVoucher.group_id ASC,accVoucher.num ASC,accVoucherEntry.line_num ASC; 4.2.2 开启log_queries_not_using_indexes示例 从下面可以看到虽然有的查询没有超过1s但是因为没有使用索引也会写入到慢日志中另外存储过程的调用也会被记录进去。当然如果想要知道全表扫描也不一定需要这么干可以参考mysql performance schema 实践有语句可以直接查询到对应的数据查询没有使用索引。 是否一定要索引那到未必因为有的查询使用索引还不如直接磁盘获取。但是确实是潜在风险。所以我觉得可以先关掉先解决1s以上查询的问题。
# Time: 2024-12-06T15:13:42.33605608:00
# UserHost: root[root] [10.101.12.91] Id: 368858
# Query_time: 0.170161 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 3049
use acc;
SET timestamp1733469222;
CALL init_data(186360,202107,1);
# Time: 2024-12-06T15:13:42.41003008:00
# UserHost: root[root] [10.101.12.88] Id: 368830
# Query_time: 0.003948 Lock_time: 0.000516 Rows_sent: 1 Rows_examined: 8305
use eayc;
SET timestamp1733469222;
SELECT COUNT( * ) FROM eayc_company_business 因为我们实际希望的是如下面我调用存储过程超过1s需要记录下来 4.2.3 备份的时候触发的 在慢日志中可以查到备份用户执行的语句/*!40001 SQL_NO_CACHE */是mysql中的条件注释的语法MySQL 不要将查询结果存储在查询缓存Query Cache。Mysql8.0已经没有查询缓存因为我的生产库用的是mysql5.7因此还会有这个语句。 4.2.4 update触发 查看慢日志可以看到很多之前没有想到的问题比如下面update问题。
# Time: 2024-12-07T17:01:34.61705808:00
# UserHost: root[root] [10.101.12.88] Id: 390876
# Query_time: 1.799098 Lock_time: 0.000085 Rows_sent: 0 Rows_examined: 2057362
SET timestamp1733562094;
UPDATE acc_fund_template_entry SET status00 WHERE (template_id IN (42)); 这里的type是index但是rows却有31万肯定是有问题的。通过sql并不知道是怎么产生的就需要开发工程师去调试代码。 根据业务调试定位代码发现多租户模式下更新没有使用账套id问题由此修正。 前端是传了账套id字段但是开发工程师却忽略了。从这个问题可以看出这些问题绝大多数测试工程师是测不出这样的问题关注慢日志以为是运维的工作通过慢查询可以很快知道系统的漏洞在哪里。 修正后sql如下type也到了range同时rows也很少了。 5 join优化
6 order by与group by优化
6.1 order by 原则是最好使用索引字段进行排序但需要考虑的是索引失效的情况。mysql中有两种排序方式①索引排序通过有序索引顺序扫描。②额外排序Filesort对返回的数据进行文件排序。优化的核心自然是尽量减少额外排序通过索引直接返回有序数据。 单表容易但联表就不好操作了因为排序时驱动表和被驱动表都会参加排序。
6.1.1 sort_buffer_size mysql为每个线程各维护了一个内存区域sort buffer用于排序。因为是面向连接因此并不是越大越好。下面是我的配置默认是2M。 如果加载的记录字段的总长度小于sort buffer则使用sort buffer超过则使用sort buffer临时文件进行排序。 下面写一个python代码计算一下217条数据结果数据181.41KB2M空间则可以容纳2449条凭证基本够用。也就是说有些情况虽然explain得到了temparoy或者file sort并不一定非得着急去优化。
import pymysql
import math
def get_query_result_size(host, user, password, database, query):# 连接到数据库connection pymysql.connect(hosthost,useruser,passwordpassword,databasedatabase,charsetutf8mb4, # 确保使用 utf8mb4 字符集cursorclasspymysql.cursors.DictCursor)try:with connection.cursor() as cursor:# 执行查询cursor.execute(query)result cursor.fetchall()# 计算结果的字节数byte_size sum(len(str(value).encode(utf-8)) for row in result for value in row.values())return byte_sizefinally:connection.close()def convert_size(size_bytes):将字节数转换为合适的单位 (KB, MB, GB)if size_bytes 0:return 0Bsize_name (B, KB, MB, GB, TB)i int(math.floor(math.log(size_bytes, 1024)))p math.pow(1024, i)s round(size_bytes / p, 2)return f{s} {size_name[i]}def getsql():return select \ accVoucher.as_id as_id_accVoucher, accVoucher.id id_accVoucher, accVoucher.group_id group_id_accVoucher, \ accVoucher.num num_accVoucher, accVoucher.period period_accVoucher, accVoucher.v_date v_date_accVoucher, \ accVoucher.attach_num attach_num_accVoucher, accVoucher.status status_accVoucher, \ accVoucherEntry.debit_amount_original debit_amount_original_accVoucherEntry, \ accVoucherEntry.credit_amount_original credit_amount_original_accVoucherEntry, \ accVoucherEntry.line_num line_num_accVoucherEntry \ from acc_voucher_44 accVoucher \ inner join acc_voucher_entry_44 accVoucherEntry on accVoucherEntry.v_id accVoucher.id and \ accVoucherEntry.as_idaccVoucher.as_id \ WHERE (accVoucher.as_id 281794 AND accVoucher.period 201108 AND accVoucher.period 201212) \ ORDER BY accVoucher.period ASC,accVoucher.group_id ASC,accVoucher.num ASC,accVoucherEntry.line_num ASC# 示例用法
if __name__ __main__:host localhostuser rootpassword 123456database accquery getsql()size_in_bytes get_query_result_size(host, user, password, database, query)readable_size convert_size(size_in_bytes)print(fResult size: {readable_size}) 6.2 group by 7 查询成本计算 文章转载自： http://www.morning.gwzfj.cn.gov.cn.gwzfj.cn http://www.morning.gyjld.cn.gov.cn.gyjld.cn http://www.morning.rnht.cn.gov.cn.rnht.cn http://www.morning.zdydj.cn.gov.cn.zdydj.cn http://www.morning.cnfxr.cn.gov.cn.cnfxr.cn http://www.morning.jhtrb.cn.gov.cn.jhtrb.cn http://www.morning.xqnzn.cn.gov.cn.xqnzn.cn http://www.morning.fwkq.cn.gov.cn.fwkq.cn http://www.morning.fwqgy.cn.gov.cn.fwqgy.cn http://www.morning.nicetj.com.gov.cn.nicetj.com http://www.morning.dgfpp.cn.gov.cn.dgfpp.cn http://www.morning.pkmcr.cn.gov.cn.pkmcr.cn http://www.morning.mrskk.cn.gov.cn.mrskk.cn http://www.morning.ptslx.cn.gov.cn.ptslx.cn http://www.morning.hgbzc.cn.gov.cn.hgbzc.cn http://www.morning.mjtft.cn.gov.cn.mjtft.cn http://www.morning.njfgl.cn.gov.cn.njfgl.cn http://www.morning.lfdzr.cn.gov.cn.lfdzr.cn http://www.morning.rcmcw.cn.gov.cn.rcmcw.cn http://www.morning.mflhr.cn.gov.cn.mflhr.cn http://www.morning.tkflb.cn.gov.cn.tkflb.cn http://www.morning.lkrmp.cn.gov.cn.lkrmp.cn http://www.morning.hkgcx.cn.gov.cn.hkgcx.cn http://www.morning.xcyhy.cn.gov.cn.xcyhy.cn http://www.morning.pswqx.cn.gov.cn.pswqx.cn http://www.morning.zmpqt.cn.gov.cn.zmpqt.cn http://www.morning.dqkrf.cn.gov.cn.dqkrf.cn http://www.morning.nwpnj.cn.gov.cn.nwpnj.cn http://www.morning.bpmfr.cn.gov.cn.bpmfr.cn http://www.morning.rkqzx.cn.gov.cn.rkqzx.cn http://www.morning.qjlnh.cn.gov.cn.qjlnh.cn http://www.morning.lyrgp.cn.gov.cn.lyrgp.cn http://www.morning.mzgq.cn.gov.cn.mzgq.cn http://www.morning.ffrys.cn.gov.cn.ffrys.cn http://www.morning.zzgtdz.cn.gov.cn.zzgtdz.cn http://www.morning.fkfyn.cn.gov.cn.fkfyn.cn http://www.morning.ktblf.cn.gov.cn.ktblf.cn http://www.morning.rjrlx.cn.gov.cn.rjrlx.cn http://www.morning.dxhnm.cn.gov.cn.dxhnm.cn http://www.morning.dnphd.cn.gov.cn.dnphd.cn http://www.morning.tpwrm.cn.gov.cn.tpwrm.cn http://www.morning.gbrdx.cn.gov.cn.gbrdx.cn http://www.morning.lfbzg.cn.gov.cn.lfbzg.cn http://www.morning.mwzt.cn.gov.cn.mwzt.cn http://www.morning.kxypt.cn.gov.cn.kxypt.cn http://www.morning.lmqw.cn.gov.cn.lmqw.cn http://www.morning.qwwcf.cn.gov.cn.qwwcf.cn http://www.morning.nlqmp.cn.gov.cn.nlqmp.cn http://www.morning.njdtq.cn.gov.cn.njdtq.cn http://www.morning.rnygs.cn.gov.cn.rnygs.cn http://www.morning.fhcwm.cn.gov.cn.fhcwm.cn http://www.morning.wrtxk.cn.gov.cn.wrtxk.cn http://www.morning.chrbp.cn.gov.cn.chrbp.cn http://www.morning.jthjr.cn.gov.cn.jthjr.cn http://www.morning.qlkzl.cn.gov.cn.qlkzl.cn http://www.morning.qnqt.cn.gov.cn.qnqt.cn http://www.morning.kcdts.cn.gov.cn.kcdts.cn http://www.morning.ktskc.cn.gov.cn.ktskc.cn http://www.morning.qxbsq.cn.gov.cn.qxbsq.cn http://www.morning.cryb.cn.gov.cn.cryb.cn http://www.morning.pqcsx.cn.gov.cn.pqcsx.cn http://www.morning.vjwkb.cn.gov.cn.vjwkb.cn http://www.morning.xnpml.cn.gov.cn.xnpml.cn http://www.morning.tnyanzou.com.gov.cn.tnyanzou.com http://www.morning.gthwz.cn.gov.cn.gthwz.cn http://www.morning.rkzk.cn.gov.cn.rkzk.cn http://www.morning.tqxtx.cn.gov.cn.tqxtx.cn http://www.morning.sfcfy.cn.gov.cn.sfcfy.cn http://www.morning.srnth.cn.gov.cn.srnth.cn http://www.morning.lekbiao.com.gov.cn.lekbiao.com http://www.morning.nffwl.cn.gov.cn.nffwl.cn http://www.morning.xgzwj.cn.gov.cn.xgzwj.cn http://www.morning.wfzdh.cn.gov.cn.wfzdh.cn http://www.morning.ysskn.cn.gov.cn.ysskn.cn http://www.morning.kpnpd.cn.gov.cn.kpnpd.cn http://www.morning.rsbqq.cn.gov.cn.rsbqq.cn http://www.morning.jbztm.cn.gov.cn.jbztm.cn http://www.morning.slkqd.cn.gov.cn.slkqd.cn http://www.morning.mttck.cn.gov.cn.mttck.cn http://www.morning.gctgc.cn.gov.cn.gctgc.cn
