当前位置： 首页 > news >正文

WordPress如何建立手机网站,阿里云wordpress建站,网站建设域名所有权,企业年度申报网上申报索引 索引#xff1a;一种有序的存储结构#xff0c;按照单个或者多个列的值进行排序。索引的目的#xff1a;提升搜索效率。索引分类#xff1a; 数据结构 B 树索引#xff08;映射的是磁盘数据#xff09;hash 索引#xff08;快速锁定内存数据#xff09;全文索引 …索引 索引一种有序的存储结构按照单个或者多个列的值进行排序。索引的目的提升搜索效率。索引分类 数据结构 B 树索引映射的是磁盘数据hash 索引快速锁定内存数据全文索引 将存储在数据库中的整本书和整篇文章中的任意内容信息查找出来的技术。在短字符串中用 LIKE %在全文索引中用 match 和 against。一般使用 elasticsearch。 物理存储 聚集索引聚簇索引主键所对应的 B 树。包含主键 ID 和表数据辅助索引二级索引除了主键之外的其它索引。只包含 key 和主键 ID 回表查询辅助索引 B 树通过 key 查找到主键 ID然后通过主键 ID 查找聚簇索引 B 树从而得到表记录。 struct zcoder_tb {int id; // primary keystring name; // keystring phone; // keyshort age; }; mapint, zcoder_tb // 聚簇索引 mapstring, int // 辅助索引 mapstring, int // 辅助索引列属性 主键索引非空唯一索引一个表只有一个主键索引在 innodb 中主键索引的 B 树包含表数据信息。PRIMARY KEY(key1, key2)唯一索引不可以出现相同的值可以有 NULL 值。UNIQUE(key1, key2)普通索引允许出现相同的索引内容 。INDEX(key1, key2) – OR KEY(key1, key2)前缀索引只比较长字符串的前几个字符。 列的个数 单列索引组合索引对表上的多个列进行索引。INDEX(key1, key2) UNIQUE(key1, key2) PRIMARY KEY(key1, key2)索引代价 占用空间有多个索引就有多个 B 树。维护的代价DML 操作变慢如果修改的字段有索引非聚簇索引除了要修改聚簇索引 B 树还要修改对应的辅助索引 B 树。 索引的使用场景 wheregroup byorder by 不使用索引的场景 不使用 where / / /group by / / /order by列中的数据区分度不高经常修改的列表数据量少 innodb 中 B 树多路平衡搜索树 特征 非叶子节点只存储索引信息只存储 key。叶子节点还存储数据信息存储 key 和 value。叶子节点之间依次相连。节点的大小为 16 KB映射的是连续的磁盘页通过 mmap 映射磁盘数据。一个叶子节点至少存储两行数据如果某一行数据大于 16 KB则会截取一部分数据进行存储并保留一个地址位记录另一个 B 树所对应的地址然后把剩余的数据存储在另一个 B 树中。 为什么采用 “多路” 的树结构 一个节点多条链路相较于平衡二叉搜索树是一个更加矮胖的结构树的高度较低较少的磁盘 IO 次数来索引数据。 为什么非叶子节点只存储索引信息 B 树节点映射固定大小的磁盘数据可以包含更多的索引信息能快速锁定数据所在叶子节点的位置。 为什么叶子节点依次相连 便于范围查询避免中序遍历回溯去查找下一个节点。 索引信息和数据信息的分层管理便于高效地组织磁盘数据快速实现单点和范围查询。 聚簇索引查找流程 select * from user where id 18 and id 40;辅助索引查找流程 辅助索引的叶子节点不包含行记录的全部数据只存储了用来排序的 key 和一个 bookmark该书签存储了聚集索引的 key。
– 某个表 包含 id、name、lockyNum; id是主键lockyNum 是辅助索引; select * from user where lockyNum 33;innodb 体系结构 Buffer Pool 缓存表和索引数据 聚簇索引 B 树的数据。采用 LRU 算法只缓存比较热的数据。缓存大小为 128 MB。有三个链表组织数据 free list 组织 Buffer Pool 中未使用的缓存页。flush list 组织 Buffer Pool 中的脏页也就是待刷磁盘的页。lru list 组织 Buffer Pool 中的冷热数据当 Buffer Pool 没有空闲页时将把 lru list 中最久未使用的数据淘汰。 Buffer Pool 中的数据修改没有刷到磁盘怎么确保内存中数据安全mysql 关闭时内存数据丢失 Change Buffer Change Buffer 缓存辅助索引的数据变更DML 操作Change Buffer 中的数据将会异步 merge 到 Buffer Pool 中。 索引存储 innodb 由段、区、页组成段分为数据段、索引段、回滚段等。区大小为 1 MB一个区由 64 个连续页构成页的默认值为 16 KB页为逻辑页磁盘物理页大小一般为 4KB 或者 8KB。为了保证区中的页连续存储引擎一般一次从磁盘中申请 4~5 个区。顺序内存 IO数组 随机内存 IO红黑树 ≈ \approx ≈ 顺序磁盘 IO 随机磁盘 IO 索引覆盖 一种数据查询方式。针对的是辅助索引。直接通过辅助索引 B 树就能获取要查询的值而无需通过回表查询。在 select 中尽量写我们所需要的字段。 最左匹配规则 针对组合索引。从左到右依次匹配遇到 、 、 b e t w e e n 、 l i k e 、、between、like 、、between、like 就停止匹配。尽量扩展索引而不是单独创建索引。 索引下推 目的减少回表次数减少 server 层和存储引擎层的交互次数从而提升查询效率。对象辅助索引普通索引和联合索引场景居多。5.6 版本后支持。没有索引下推机制server 层向存储引擎层请求数据在 server 层根据索引条件进行数据过滤。有索引下推将索引条件判断下推到存储引擎中过滤数据最终由存储引擎进行数据汇总返回给 server 层。 索引失效 where or 或 and包含非索引字段。in 子查询。 LIKE 模糊查询通配符 % 开头。explain select * from zcoder_tb where name like %张;索引字段参与运算。from_unixtime(idx) 2024-02-21; # 索引失效 idx unix_timestamp(2024-02-21) # 索引有效索引字段发生隐式转换。 将列隐式转换为某个类型实际等价于在索引列上作用了隐式转换函数。 在索引字段上使用 NOT、 、! 。id 0; # 索引失效 idx 0 or idx 0; # 索引有效组合索引中没有使用第一列索引。 索引原则 查询频次较高且数据量大的表建立索引索引选择使用频次较高过滤效果好的列或者组合。使用短索引节点包含的信息多较少磁盘 IO 操作。比如 smallinttinyint。对于组合索引考虑最左侧匹配原则和索引覆盖。尽量选择区分度高的列作为索引该列的值相同的越少越好。尽量扩展索引在现有索引的基础上添加复合索引最多 6 个索引。不要 select 尽量只列出需要的列字段方便使用索引覆盖。索引列列尽量设置为非空。对于很长的动态字符串考虑使用前缀索引。 注意前缀索引不能做 order by 和 group by。有时候需要索引很长的字符串这会让索引变的大且慢。 通常情况下可以使用某个列开始的部分字符串作为索引这样大大的节约索引空间从而提高索引效率。 但这会降低索引的区分度索引的区分度是指不重复的索引值和数据表记录总数的比值。 索引的区分度越高则查询效率越高因为区分度更高的索引可以让 MySQL 在查找的时候过滤掉更多的行。 对于 BLOB , TEXT , VARCHAR 类型的列必要时使用前缀索引。 因为 MySQL 不允许索引这些列的完整长度使用该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。select count(distinct left(name,3))/count() as sel3, count(distinct left(name,4))/count() as sel4, count(distinct left(name,5))/count() as sel5, count(distinct left(name,6))/count(*) as sel6, from user; alter table user add key(name(4));可选开启自适应 hash 索引或者调整 Change Buffer。select innodb_adaptive_hash_index; set global innodb_adaptive_hash_index1; – 默认是开启的select innodb_change_buffer_max_size; – 默认值为 25表示最多使用 ¹⁄₄ 的缓冲池内存空间最大值为 50 set global innodb_change_buffer_max_size30出现了 SQL 比较慢如何解决 找到 SQL 语句 show processlistshow processlist查看连接线程可以查看此时线上运行的 SQL 语句。 如果要查看完整的 SQL 语句SHOW FULL PROCESSLIST 然后优化该语句。开启慢日志查询– 查看 SHOW GLOBAL VARIABLES LIKE slow_query%; SHOW GLOBAL VARIABLES LIKE long_query%; – 设置 SET GLOBAL slow_query_log ON; – on 开启off 关闭 SET GLOBAL long_query_time 4; – 单位秒默认 10s此时设置为 4s

或者修改配置

slow_query_log ON long_query_time 4 slow_query_log_file D:/mysql/mysql57-slow.log# 查找最近 10 条慢查询日志 mysqldumpslow -s t -t 10 -g select D:/mysql/mysql57-slow.log分析 SQL 语句 索引 wheregroup byorder by SQL 语句 in 和 not in 优化成联合查询减少联合查询 工作中不要用 age 字段而是存储他的生日年月日 主键选择 innodb 中表是索引组织表每张表有且仅有一个主键。 如果显式设置 PRIMARY KEY则该 key 作为该表的主键。如果没有显式设置则从非空唯一索引中选择 只有一个非空唯一索引则选择该索引为主键。有多个非空唯一索引则选择声明的第一个作为主键。 没有非空唯一索引则自动生成一个 6 字节的 _rowid 作为主键。