Myql索引篇+c#性能优化篇

1.如何优化已经创建的索引看他是否需要删除

show index from 表名字;

Table：表示索引所属的表名，对于你的表来说，应该是 t_order_charge。

Non_unique：表示索引是否是唯一索引。如果值为 0，则表示该索引是唯一索引；如果值为 1，则表示该索引是非唯一索引。

Key_name：表示索引的名称。这是索引的标识符，可以用来在表中唯一标识一个索引。

Seq_in_index：表示索引中的列序列号，即索引中的第几个列。如果一个索引包含多个列，该列会显示索引中的列的序号。

Column_name：表示索引中的列名，即该索引涉及的列的名称。

Collation：表示列的排序规则（collation）。它指定了用于比较和排序索引列数据的规则。

Cardinality：表示索引中不同值的数量，即基数。基数越高，表示该索引的选择性越好。（运行久以后可能为null）表中1/16发生变化会自动更新统计信息,innodb内部有计数器stat_modified_counter>2000000000 也会更新并且置0继续统计

这个统计是采样统计,根据索引的节点来随机取多少个子节点进行采样

Sub_part：如果索引只是使用列的一部分，则此列显示使用的列的字符数或字节数。如果整个列都被使用，则此列为 NULL。

Packed：表示索引的存储格式。如果该列的值为 NULL，则表示索引没有压缩。

优点：

减小存储空间：压缩索引可以减小磁盘空间的使用，对于大型数据库来说，这可能是一个重要的考虑因素。

IO 效率：减小索引大小可能会提高 IO 效率，尤其是在索引无法完全缓存在内存中的情况下。

缺点：

CPU 消耗：压缩和解压缩索引需要额外的 CPU 计算资源。对于高负载的数据库，这可能成为一个瓶颈。

更新代价：对于压缩的索引，更新可能会更昂贵，因为需要更新压缩后的数据块。

Null：表示索引列是否允许包含 NULL 值。如果值为 YES，则表示该列允许包含 NULL 值；如果值为 NO，则表示该列不允许包含 NULL 值。

优点：

灵活性：允许 NULL 值的列提供了更大的灵活性，因为它允许在数据中表示缺失或未知的信息。

节省空间：对于大多数数据库实现，NULL 值通常不占用额外的存储空间。因此，允许 NULL 的列可能节省存储空间。

缺点：

复杂性：处理 NULL 值可能增加了查询和应用程序的复杂性，因为必须考虑到可能存在 NULL 的情况。

引发错误：如果不注意处理 NULL 值，可能会导致一些不期望的行为，例如在进行数学运算时。

NOT NULL 列：

优点：

数据完整性： NOT NULL 列确保表中的数据是完整的，不会存在缺失值。这可以提高数据的一致性和可靠性。

简化查询：查询不需要额外的 NULL 检查，因为已知该列不包含 NULL 值。

缺点：

缺乏灵活性：不允许 NULL 的列可能导致一些情况下缺乏灵活性，例如在某些业务场景中允许某些信息为空可能更为合适。

可能增加存储空间：在某些情况下，NOT NULL 列可能需要更多的存储空间，因为数据库引擎不再需要为 NULL 值保留额外的位。

Index_type：表示索引的类型，例如 BTREE、FULLTEXT 等。

Comment：一般包含有关索引的其他信息，例如是否是主键索引、是否是外键等。重要

Visible：索引是否可见 ANALYZE TABLE 可能会更改可见性或者索引的增加和修改删除

ANALYZE TABLE your_table_name;

什么时候使用：当表的数据分布发生较大变化时，或者大量的数据被添加或删除时，执行 ANALYZE TABLE 是有意义的。这种操作对于数据库查询优化很

使用的意义：，因为查询优化器根据表的统计信息来生成最优的查询执行计划。如果表的数据分布发生变化，或者新的索引被添加或删除，那么执行计划可能会失效，导致性能下降。

6.mysql索引原理 B+数索引

mysql中使用InnoDB存储引擎也就是页处理的索引方案

新建一张表的各个属性介绍

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为节点。

从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中 B+ 树最上边的那个节点也称为根节点

B+数基本不会超过4层假如每一层的记录是1000条那么4层的b+数将会有 1000×1000×1000×100=100000000000记录

没有索引如何进行查询 6.1.1

条件是主键的话：在页目录中通过二分法快速定位对应的槽然后再遍历槽找到对应的记录

条件是其他列：因为没有目录所以也没有槽 ,只能从 最小记录开始依次遍历单链表的每条记录　

数据crud

1.分配的页编号不会连续如果上一页的数据量>16KB 就会进行页分裂

2.如果下一个数据页中的记录主键ID<上个也中的最大主键ID 就会进行一次记录移动

3.索引的创建就是把key(索引的条件,最小的主键ID) 和目录页号放一起

4.record_tyoe:0：普通用户记录

　　　　　　 1：目录项记录

　　　　　　 2：最小记录

　　　　　　 3：最大记录

索引的基础

@每个索引都是一棵B+树,最下一边一层是叶子节点,存用户记录(索引列的值+主键ID)。目录项存的是内节点

@innoDB自动为表创建聚集索引,聚集索引的叶子节点存用户的完整记录完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

@B+树每层节点按照索引从小到大顺序排序组成双向链表。

@页内的记录是按照主键从小到大排序形成单向链表

@如果是联合索引则页面和记录先按照 联合索引 前边的列排序,如果列的值相同,则按照后边的列排序

@索引查找方式：先从B+树根节点一层层向下搜索,因为每个页都是按照索引列建立的（页目录）,索引会非常快

索引的代价

@索引的空间代价：每个索引都是一颗B+树,每个树的每个节点都是一个数据页,页=16KB 所以索引越多树越多占用空间越大

@索引的时间代价：每次对表的增删改,都需要去修改各个B+树的索引,因为B+树每层节点都是按照索引列的值从小到大排序组成的双向链表,不论是叶子节点的记录,还是内节点的记录

增删改都会对排序造成破坏。所以存储引擎需要额外的时间进行一些记录的移位,页面分裂,页面回收.来维护节点和记录的排序

索引的适用的条件

@联合索引的命中条件, 如果创建的联合索引是Name，Date，number

@全值匹配：

select *from where Name=1,Date=2,number =3 会走索引且顺序不受影响 select *from where ,Date=2,number =3Name=1 是一样的三个索引都会走

@匹配左边的列：

select *from where Name=1,number =3 这个就只会走Name这一列因为联合索引是按照 Name Date number 顺序来找的所以只会命中Name这一列

select *from where Date=1,number =3 这个不会走索引,因为Name是最开始的索引列,没有她=没有头

@匹配列前缀：

select *from where Name='%1' 会走索引,因为是按照前缀排好序的如果是一串url地址按照 url建立索引为了匹配这个原则我们根据.com .cn .dot 来找的话可以把这几个单词放在最前面这样就可以命中索引了

select *from where Name='%中间的值%' 不会走索引,因为是按照前缀排好序的

@匹配范围值

SELEnumber T * FROM person_info

WHERE

Name> 'Asa' AND Name< 'Datearlow'; 会命中索引

索引查询顺序：

找到name 值为Asa 的记录。
找到name 值为Datearlow 的记录。

由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表）

所以找到这些记录的主键值,再去聚集索引进行一次回表得到完整的记录值

SELEnumber T * FROM person_info

WHERE

Name> 'Asa' AND Name< 'Datearlow' #会命中索引

AND Date> '1980-01-01'; #不会命中索引因为name查找的是一个范围的值所以不会根据date进行排序

对这个联合索引只会用到 Name部分的索引,不会用到Date 因为只有name值相同才会根据Date排序,而这个查询中是根据Name范围找的,记录中可能并不是按照Date列进行排序,所以再搜索条件中以date列进行查找是用不到B+树索引

@精确匹配某一列并且范围匹配另外一行

SELECT *

FROM person_info WHERE

name = 'Ashburn' #精确查找使用了索引列

AND Date> '1980-01-01' AND Date< '2000-12-31' #因为 Name是精确查找的,所以得到的name都是相同的,所以会再按照date排序,所以这个条件也会命中索引

AND number > '15100000000'; #因为date是范围查找所以不会命中索引,所以只能根据上一步得到的记录进行遍历

@Order By 排序 GROUP BY 等同于 orderby

SELECT * FROM person_info ORDER BY name, date, number LIMIT 10; 会命中索引因为是按照联合索引排序进行查询的
SELECT * FROM person_info ORDER BY date, number name,; 不会命中索引顺序不同

SELECT * FROM person_info WHERE name = 'A' ORDER BY date,number LIMIT 10; 会命中索引因为name相同会按照 date,number排序

@不可以使用索引进行排序的几种情况

ASC，DESC混用只能单独存在

WHERE子句中出现非排序使用到的索引列 WHERE country = 'China' ORDER BY name LIMIT 10; 这个使用不到索引

排序列包含非同一个索引的列 ORDER BY name, country LIMIT 10; name 和country 并不属于一个联合索引中的列，所以无法使用索引进行排序

排序列使用了复杂的表达式 ORDER BY UPPER(name) LIMIT 10;

所以得到结论：如果要使用联合索引查询他是先匹配Name,再去匹配Date,再去匹配number 。所以： where中的条件必须是从左往右并且要连续最开始的头列不能缺少才会命中

　　　　　　@联合索引的排序：先按照Name排序,再按照Date排序，再按照number 排序

回表的代价

WHERE name > 'Asa' AND name < 'Barlow';

@访问一次二级索引：从联合索引中取出出name 值在Asa ～ Barlow 之间的用户记录饮记录是顺序的所以这里=顺序IO

@访问一次聚集索引：再再根据当前记录里面包含的主键ID去聚集索引查找(回表) 因为得到的主键ID是不连续的所以=随机IO 一般顺序IO比随机IO性能高很多

@如果二级索引占的数据量>90% 需要回表的记录也就越多,不如直接全表扫描(扫描聚集索引)

@查询优化器会根据二级索引得到统计的数据量选择进行二级索引+回表还是全表扫描

SELECT * FROM person_info ORDER BY name, birthday, phone_number;
由于查询列表是* ，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这
样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（ filesort ）低，所以优化器会倾向于使用全表扫
描的方式执行查询。如果我们加了LIMIT 子句，比如这样：
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
这样需要回表的记录特别少，优化器就会倾向于使用二级索引 + 回表的方式执行查询。

@为了避免回表的性能损耗最好查询列表只包含索引列

SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'因为查询的列全是联合索引里面的值所以不需要进行回表操作

上面的这种就叫覆盖索引

如何挑选索引

@只为用于搜索、排序或分组的列创建索引

@考虑列值的重复数量, 列的值如果全是1 或者大部分重复, 就没办法快速查找,且因为重复过多还需要进行回表性能损耗大

@索引列的类型尽量小能用int 不用bigint

数据类型越小，在查询时进行的比较操作越快,占用的存储空间就越少

在一个数据页内就可以放下更多的记录，从而减少磁盘I/O 带
来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

@如果索引是字符串值

只对字符串前几个值进行索引比对前部存,节省空间,减少了字符串的比较时间所以建立索引的时候可以 name(10) 标识字符串前缀索引策略

问题：索引列前缀对排序的影响

SELECT * FROM person_info ORDER BY name LIMIT 10;

因为二级索引不包含完整的name列信息,所以无法记录进行排序,就只能用文件排序了

@如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出
现的话，是用不到索引的

WHERE my_col * 2 < 4 不会走索引

WHERE my_col < 4/2 会走索引

@让主键具有AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入

总结

1. B+ 树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
2. B+ 树索引适用于下边这些情况：
全值匹配
匹配左边的列
匹配范围值
精确匹配某一列并范围匹配另外一列
用于排序
用于分组
3. 在使用索引时需要注意下边这些事项：
只为用于搜索、排序或分组的列创建索引
为列的基数大的列创建索引
索引列的类型尽量小
可以只对字符串值的前缀建立索引
只有索引列在比较表达式中单独出现才可以适用索引
为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT 属性。
定位并删除表中的重复和冗余索引
尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

11.表连接原理

select*from A（驱动表）

inner join B （被驱动表）on a.id=b.id

1.驱动表每获取到一条数据就会去被驱动表里面匹配记录所以, 驱动表只会被访问一遍,而被驱动表的访问次数和驱动表的结果记录数有关

2.内连接选取的驱动表都没关系,左连接的驱动表就是左边的同理右连接也是

3.如果是3个表连接那么就是相当于嵌套循环查询一样先循环A,在循环A里面来一个循环表B 再在B循环里面循环表C 直到匹配结果集 foreachA{ foreachB{foreachC}}

4.所以对于被驱动表B来说虽然访问次数过多,如果列是主键ID，那么就会走const索引方法 b.id这一列已经是确定的值了,或者不是主键只要给这个条件列加索引访问的等级就是ref

5.连接查询,如果数据量大,IO高走的全是全表扫描,内存不足怎么办? mysql其实建立了 joinBUffer 连接缓冲区,就是在执行连接查询的时候提前申请固定大小的内存,把驱动表的结果集中记录在里面,然后去扫描被驱动表,每一条被驱动表的记录一次性的和join buffer进行匹配,由于在内存就会很快,最好的情况就是 join buffer足够大容纳所有的驱动表结果, 这样就只需要访问一次被驱动表完成连接了

表连接的本质：

1.连接就是把表都取出来进行匹配然后把结果发送给客户端,如果不加条件,产生的结果集就是笛卡尔积

2.内连接:不符合on的条件不会加入到最好结果集中,而外连接会

3.嵌套循环连接算法是指驱动表之访问一次,被驱动表多次访问,次数按照驱动表的结果记录,由于被驱动表被访问多次,因此可以为被驱动表建立合适的索引,

4.由于被驱动表非常大,多次访问可能有很多磁盘IO,此时可是使用基于快的嵌套循环链接算法来缓解造成的性能损耗

性能优化

1.案列大数据查询使用mysql慢怎么办也走了主键索引

   #region MyRegion

            int groupSize = 200;

            // 使用扩展方法进行分组
            var groupedIds = settleChargeDetailEntityList
                            .Select((x,index) => new { Value = x,Group = index / groupSize })
                            .GroupBy(item => item.Group)
                            .Select(group => group.Select(item => item.Value.Id)
                                                  .ToList()
                                   )
                            .ToList();

            // 初始化 SQL 查询字符串
            var sqlBuilder = new StringBuilder();

            foreach (var item in groupedIds)
            {
                // 构建 SQL 查询语句
                sqlBuilder.AppendLine($@" 
                                        select Id, IsDeleted from t_settle_charge_detail where id in ({string.Join(",",item)})
                                    "
                                     );

                sqlBuilder.AppendLine("union all");
            }

            // 移除末尾的 "union all" 和多余的换行符
            sqlBuilder.Remove(sqlBuilder.Length - "union all".Length - Environment.NewLine.Length,"union all".Length + Environment.NewLine.Length);

            var sql = sqlBuilder.ToString();

            #endregion

2.案例：大数据批量更新 10w条推荐写法不适用mysql临时表模式

//此处要走sqlsuagur的事务模式 而不是c#的事务模式   
   for (int i = 0; i < updateList.Count; i += 300)
                  {
                    
                      var takelist = updateList.Skip(i).Take(300).ToList();
                      var isok     = await Context.Updateable(takelist).ExecuteCommandAsync();

                      if (isok<=0)
                      {
                          throw new BusinessException(message: $"执行销账更新保存语句报错");
                      }
                  }

3 案例：List数据量太大 List.FirstOrDefalut会性能低使用哈希查询实体

var HashList= aa.ToDictionary(entity => entity.条件ID);

HashList.TryGetValue(item.条件ID,out var 返回的实体);

3 案例：List数据量太大 List.FirstOrDefalut会性能低使用哈希查询值

    var orderChargelistDic=orderChargelist.ToDictionary(group => group.Id);
       var orderChargeInfo = orderChargelistDic[item.ChargeDetailId];

posted @ 2024-01-26 10:38 12不懂3 阅读(20) 评论(0) 编辑收藏举报

刷新页面返回顶部

12不懂三

Myql索引篇+c#性能优化篇

公告