精华内容
下载资源
问答
  • Mysql数据库索引优化

    2020-05-23 19:28:13
    第二是提供SQI优化器(Mysql会自动等价优化我们写的sql) 引擎层:提供了各种存储数据的方式(InnoDB MyISAM等) InnoDB(默认):事务优先 (适合高并发操作;行锁)每行数据都加个锁。支持外键 MyISAM:性能优先(表锁) ...

        在讲Mysql数据库优化前先讲讲Mysql是怎样执行我们写的sql。

    一、Mysql分层

    连接层:提供与客户端连接的服务(只是和客户端建立连接)

    服务层:第一是提供各种用户使用的接口(select,insert...)。第二是提供SQI优化器(Mysql会自动等价优化我们写的sql)

    引擎层:提供了各种存储数据的方式(InnoDB MyISAM等)

    InnoDB(默认):事务优先 (适合高并发操作;行锁)每行数据都加个锁。支持外键

    MyISAM:性能优先(表锁)

    存储层:存储数据

    二、SQL解析过程

    SQL编写过程:select ....from ...join ...on ...where ...group by ....having ....order by ...limit....

    SQL解析过程:from ....on ...join ...where ... group by ...having ...select ...order by ...limit....

    三、SQL性能问题(优化索引)

    分析SQL执行计划:explain+SQL。可以模拟SQL优化器执行sql,从而知道自己写的sql性能高低。

    Mysql查询优化会干扰我们的优化

    explain+SQL 参数讲解:

    1,id:编号

    这是一个三表查询例子:

    结论:当id值相同时,由上到下顺序执行,先查询t、tc、c(笛卡尔积,表数据小的先执行,前提编号id相同)

    这是一个子查询例子:

    结论:id值不同,id值越大越优先执行(本质:在嵌套子查询时,先查内层,再查外层)

    这是多表+子查询的例子:

    结论:id有相同又有不同时,大的先执行,相同的由上往下顺序执行

    2,select_type:查询类型

    PRIMARY:包含子查询SQL中的主查询(最外层)

    SUBQUERY:包含子查询SQL中的子查询(非最外层)

    SIMPLE:简单查询(不包含子查询、union)

    DERIVED:衍生查询(使用到了临时表)

    UNION:在from子查询中有table1 union table2  table1就是DERIVED,table2  就是UNION

    UNION RESULT:告知开发人员哪些表时UNION

    3,table:所查询的表名

    4,type:类型(该条SQL的查询性能),常见的有system>const>eq_ref>ref>range>index>all 

    4.1,system:只有一条数据的衍生表(基本达不到,忽略)

    4.2,const:仅仅能查到一条数据的sql,用于Primary key 或 unique索引(基本达不到),与索引类型有关

    4.3,eq_ref:唯一性索引(基本达不到):对于每个索引健的查询,返回匹配唯一行数据(有且只有一个,不能多,不能为0)

    例如:select ... from student  where name='JAY';查询出来的数据可以有很多条,但是名字叫JAY的只能有一条

    4.4,ref:非唯一性索引:对于每个索引健的查询,返回所有行(0,多)

    4.5,range:检索指定范围的行,where后面是一个范围chaxun(between  in  <>)

    4.6,index:查询全部索引中数据

    4.7,all:查询全部表数据

    5,possible_keys:可能用到的索引

    6,key:真正用到的索引

    7,key_len:实际所用索引长度(作用:用于判断复合索引是否被完全使用)

    例如:创建了一个3个字段的复合索引(a,b,c),三个字段都是char(20)类型(一个字符等于3个字节),就可以根据key_len来判断用了几个字段的索引了。(字段允许为空的话 会额外用一个字节标识)

    a char(20) :是固定长度类型。一个字符等于3个字节,如果a可以为null,额外用一个字节标识

    a varchar(20):可变长度类型。一个字符等于3个字节,如果a可以为null,额外用一个字节标识,额外用两个字节标识可变类型

    8,ref:表之间的引用。作用:指明当前表 所参照的字段

    例如:select ..... from ....where a.c = b.x (b.x可以是常量,如果是常量则值为const)

    9,rows:通过索引查到的行数

    10,Extra:额外字段

    10.1, using filesort:性能消耗大,需要一次额外的排序(查询),需要优化    常见于order by中 

    排序前提:先查询

    先讲一个单值索引例子:我们创建一个表,有3个单值索引:

    create table test02
    (
    a1 char(3),
    a2 char(3),
    a3 char(3),
    index  idx_a1(a1),
    index  idx_a2(a2),
    index  idx_a3(a3),
    )

    explain select  * from test02 where a1 ='' order by a1; 

    explain select  * from test02 where a1 ='' order by a2;  --------using filesort

    小结:对于单个索引,查询和排序不是同一个字段(要额外再拍一次序),则会出现using filesort

                 避免:where哪些字段就order by哪些字段

    复合索引例子:
    drop index idx_a1(a1) on test02;
    drop index idx_a1(a2) on test02;
    drop index idx_a1(a3) on test02;
    alter table test02 add index idx_a1_a2_a3(a1,a2,a3);

    explain select  * from test02 where a1 =' ' order by a2; 

    explain select  * from test02 where a1 =' ' order by a3;  --------using filesort

    explain select  * from test02 where a2 =' ' order by a3;  --------using filesort

    (1)explain select a1 from test02 where a2 =' ' order by a3; --------using filesort 

    (2)explain select  a2 from test02 where a1 =' ' order by a3;

    (1)为什么会出现using filesort 呢?前面我们讲到SQL解析过程为:

       from ....on ...join ...where ... group by ...having ...select ...order by ...limit....,所以上述SQL应改为(2)

    小结:复合索引:不能跨列(最佳左前缀原则)

                避免:where 和 order by 按照复合索引顺序使用,不要跨列或无序使用。也要注意SQL解析顺序

               将含in的范围查询,放到where条件的最后(因为范围查询常常导致索引失效,以免影响后面的索引)

    10.2,using temporary:性能消耗大,用到了临时表。常见于 group by

    explain select  * from test02 where a1 in ('1' , '2', '3')  order by a1;

    explain select  * from test02 where a1 in ('1' , '2', '3')  order by a2; --------using temporary

    避免:查询哪些列,就用哪些列group by

    10.3,using index:性能提升,覆盖索引。原因:只需要查询索引不需要回表查询

    例:age、name是复合索引: (age,name)

    select  age,name from test where age='' and name='';

    覆盖索引:一个索引覆盖了(包含了)满足查询(select)语句中字段与查询条件(where)中所涉及的字段,可以只查索引,不回表查询

    10.4,using where:需要回原表查询

    10.5,impossible where:where字句永远为false

    10.6,using join buffer:Mysql引擎使用了连接缓存(编写的sql太差,启动缓存)

    四、索引优化

    4.1 单表优化

    主要就是上面讲到的:

    复合索引:不能跨列(最佳左前缀原则)

    将含in的范围查询,放到where条件的最后(因为范围查询常常导致索引失效,以免影响后面的索引),假如复合索引(a,b,c),最佳左前缀原则,如果a索引失效了,则b、c都失效

    4.2 多表优化

    select  *  from teacher2 t left join course2 c on t.cid=c.cid where c.name = 'java';

    小表驱动大表: t.cid=c.cid   t.表有10条数据,放左边、c表有300条数据,放右边

    索引:如果是左连接,索引一般加到右边。右连接索引一般加到左边

    索引建立在经常查询的字段

    这里顺便附上join常用写法图:

    很多索引优化的理解都可以参考上篇文章讲的Mysql索引底层的数据结构来理解。

    五、索引使用场景

    5.1,适合建立索引

    主键自动建立唯一索引
    频繁作为查询条件的字段应该创建索引
    查询中与其它表关联的字段,外键关系建立索引
    频繁更新的字段不适合建立索引(因为每次更新不单单是更新了记录还会更新索引)
    where条件用不到的字段不创建索引
    单键/组合索引 倾向选择组合索引
    查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度(order by name,age,height时,建立索引(name,age,height))
    查询中统计或者分组字段(group by,分组前提必排序)

    5.2,不适合建立索引

    表记录太少(300万条数据后,效率下降明显)
    经常增删改的表
    如果某个数据列包含许多重复且平均分配的内容,为它建立索引就没有太大实际效果(选择性:如果一个表有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性为1980/2000=0.99,越接近1,索引效率越高)

    六、常见索引失效场景

    6.1,复合索引
    不要跨列或无序使用(最佳左前缀)
    尽量使用权所有匹配
    不能使用(!= <>)或is null is not null,否则自身和右侧索引全部失效
    6.2,不要在索引上进行任何操作
    SQL优化是概率层面的优化(服务层有SQL优化器,会影响我们的优化),需要用explain去验证Mysql底层是否使用了我们的优化
    尽量使用覆盖索引(100%生效)
    6.3,like尽量以"常量"开头,不要以%开头,否则索引失效
    select * from teacher where name like %aa%  name索引失效
    select name from teacher where name like %aa%  如果非要用%开头,使用覆盖索引挽救
    6.4,尽量不要使用类型转换,否则索引失效
    tname 为字符串类型
    select * from teacher where tname = 123//底层将123 ->'123'
    6.5,尽量不要使用or,否则索引失效

    七,常见优化方法

    (1)exist和in
    如果主查询的数据集大,则使用in。如果子查询数据大,则使用exist。
    exist语法:将主查询的结果,放到子查询结果进行校验,如果符合校验,则保留数据
    select tname from teacher where exist (select * from teacher)
    (2)order by 优化
    using filesort有两种算法:双路排序和单路排序(根据IO的次数)
    Mysql4.1之前默认使用双路排序(扫描两次磁盘)
    Mysql4.1之前默认使用单路排序(只读取一次全部字段,在buffer中排序,如果buffer容量太小会自动切换到双路排序)
    优化:
    选择使用单路、双路;调整buffer的容量大小
    避免使用select *....
    复合索引 不要跨列使用,避免using filesort
    保证全部的排序字段,排序的一次性(都是升序或降序)

    八,SQL排查

    慢查询日志:Mysql提供的一种日志记录,用于记录Mysql中响应时间超过阈值(默认10S)的SQL语句(long_query_time)。慢查询日志默认是关闭的。建议:开发调优时开启,上现网环境关闭

    8.1,检查是否开启了慢查询日志:show variables like '%slow_query_log'; 默认关闭

    8.2,开启慢查询日志

    临时开启慢查询日志:set global slow_query_log =1;

    永久开启慢查询日志:etc/my.cnf 中追加配置:

    8.3,慢查询阈值

    查看慢查询阈值:show variables like '%long_query_time';

    设置慢查询阈值:set global long_query_time=3;

    执行语句:select sleep(4);该SQL超过我们设置的慢查询阈值3S。

    查询超过阈值的SQL:show global status like '%slow_queries%';

    具体可以到日志文件中查看。或者可以通过mysqldumpslow(mysql自带)工具查看,具体参数命令查看mysqldumpslow --help

     

    展开全文
  • 一、SQL查询优化(重要)1.1 获取有性能问题SQL的三种方式通过用户反馈获取存在性能问题的SQL;通过慢查日志获取存在性能问题的SQL;实时获取存在性能问题的SQL;1.1.2 慢查日志分析工具相关配置参数:slow_query_...

    4408760d19d471f617d5256b81648c5e.png

    一、SQL查询优化(重要)

    1.1 获取有性能问题SQL的三种方式

    1. 通过用户反馈获取存在性能问题的SQL;
    2. 通过慢查日志获取存在性能问题的SQL;
    3. 实时获取存在性能问题的SQL;

    1.1.2 慢查日志分析工具

    相关配置参数:

    slow_query_log # 启动停止记录慢查日志,慢查询日志默认是没有开启的可以在配置文件中开启(on)
    slow_query_log_file # 指定慢查日志的存储路径及文件,日志存储和数据从存储应该分开存储
    
    long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001(1毫秒)比较合适
    log_queries_not_using_indexes #是否记录未使用索引的SQL
    

    常用工具:mysqldumpslow和pt-query-digest

    pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord  slow-mysql.log

    1.1.3 实时获取有性能问题的SQL(推荐)

    f5a519ccfe256659b13220d74edc37cd.png
    SELECT id,user,host,DB,command,time,state,info
    FROM information_schema.processlist
    WHERE TIME>=60

    查询当前服务器执行超过60s的SQL,可以通过脚本周期性的来执行这条SQL,就能查出有问题的SQL。

    1.2 SQL的解析预处理及生成执行计划(重要)

    1.2.1 查询过程描述(重点!!!)

    9d317ec0b05471590dd2a62f40412e21.png

    通过上图可以清晰的了解到MySql查询执行的大致过程:

    1. 发送SQL语句。
    2. 查询缓存,如果命中缓存直接返回结果。
    3. SQL解析,预处理,再由优化器生成对应的查询执行计划。
    4. 执行查询,调用存储引擎API获取数据。
    5. 返回结果。

    1.2.2 查询缓存对性能的影响(建议关闭缓存)

    第一阶段:
    相关配置参数:

    query_cache_type # 设置查询缓存是否可用
    query_cache_size # 设置查询缓存的内存大小
    query_cache_limit # 设置查询缓存可用的存储最大值(加上sql_no_cache可以提高效率
    query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据
    query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单
    
    
    
    缓存查找是利用对大小写敏感的哈希查找来实现的,Hash查找只能进行全值查找(sql完全一致),如果缓存命中,检查用户权限,如果权限允许,直接返回,查询不被解析,也不会生成查询计划。

    在一个读写比较频繁的系统中,建议关闭缓存,因为缓存更新会加锁。将query_cache_type设置为off,query_cache_size设置为0。

    1.2.3 第二阶段:MySQL依照执行计划和存储引擎进行交互

    这个阶段包括了多个子过程:

    d3e3b603d1593868913dee548b8c5ab0.png

    f8894ca4c5fe8969b7942e59004a0744.png

    4cdb11be05fdd34920685ccf851348ba.png

    一条查询可以有多种查询方式,查询优化器会对每一种查询方式的(存储引擎)统计信息进行比较,找到成本最低的查询方式,这也就是索引不能太多的原因。

    1.3 会造成MySQL生成错误的执行计划的原因

    1、统计信息不准确
    2、成本估算与实际的执行计划成本不同

    54e669352c358d07981c8d363c823c53.png

    3、给出的最优执行计划与估计的不同

    b6689aa74ada0743b8f77d3e37a978ac.png

    4、MySQL不考虑并发查询
    5、会基于固定规则生成执行计划
    6、MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数

    1.4 MySQL优化器可优化的SQL类型

    查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。为了生成最优的执行计划,查询优化器会对一些查询进行改写

    可以优化的sql类型

    1、重新定义表的关联顺序;

    badf9ad524deee4d534208787a430115.png

    2、将外连接转换为内连接;

    3、使用等价变换规则;

    86d8321381278e6c01ea007d167d91bd.png

    4、优化count(),min(),max();

    26aa51b36720e66b7dd7fe47fd63a76b.png

    5、将一个表达式转换为常数;
    6、子查询优化;

    63e5c7c89856bfe6d6ad83e5445a271a.png

    7、提前终止查询,如发现一个不成立条件(如where id = -1),立即返回一个空结果;

    8、对in()条件进行优化;

    1.5 查询处理各个阶段所需要的时间

    1.5.1 使用profile(目前已经不推荐使用了)

    set profiling = 1; #启动profile,这是一个session级的配制执行查询
    
    show profiles; # 查询每一个查询所消耗的总时间的信息
    
    show profiles for query N; # 查询的每个阶段所消耗的时间
    

    1.5.2 performance_schema是5.5引入的一个性能分析引擎(5.5版本时期开销比较大)

    启动监控和历史记录表:use performance_schema

    update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%';
    
    update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';
    

    6920276467205b61ea96a99c60234ebb.png

    48877f881eea1921821e7598451f4287.png

    1.6 特定SQL的查询优化

    1.6.1 大表的数据修改

    d6dff210677f5d1eb76e1dd6c537d48c.png

    bc642e1a0486f15cb5560918b537349d.png

    1.6.2 大表的结构修改

    对表中的列的字段类型进行修改

    改变字段的宽度时还是会锁表

    无法解决主从数据库延迟的问题

    1. 利用主从复制,先对从服务器进入修改,然后主从切换
    2. (推荐)
    添加一个新表(修改后的结构),老表数据导入新表,老表建立触发器,修改数据同步到新表, 老表加一个排它锁(重命名), 新表重命名, 删除老表。

    745436264fab26a42f69d71415535c12.png

    修改语句这个样子:

    alter table sbtest4 modify c varchar(150) not null default ''

    利用工具修改:

    d04aaf5a26b4844bf22da8bc6c484e06.png

    1.6.3 优化not in 和 <> 查询

    子查询改写为关联查询:

    SELECT customer_id,first_name,last_name,email 
    FROM customer
    WHERE customer_id
    NOT IN (SELECT customer_id FROM payment) 
    

    改写后:

    SELECT a.customer_id,a.first_name,a.last_name,a.email 
    FROM customer a
    LEFT JOIN payment b ON a.customer_id = b.customer_id
    WHERE b.customer_id IS NULL
    

    二、分库分表

    2.1 分库分表的几种方式

    分担读负载 可通过 一主多从,升级硬件来解决。

    2.1.1 把一个实例中的多个数据库拆分到不同实例(集群)

    1b7bd00a12168e7a3a8103c53cbfcfd7.png

    拆分简单,不允许跨库。但并不能减少写负载。

    2.1.2 把一个库中的表分离到不同的数据库中

    d33ee594ded2f33efb2fd8693fa84710.png

    该方式只能在一定时间内减少写压力。

    以上两种方式只能暂时解决读写性能问题。

    2.1.3 数据库分片

    对一个库中的相关表进行水平拆分到不同实例的数据库中

    c828756d592ee9b370fc13e56b449cbb.png


    2.1.3.1 如何选择分区键

    1. 分区键要能尽可能避免跨分区查询的发生
    2. 分区键要尽可能使各个分区中的数据平均


    2.1.3.2 分片中如何生成全局唯一ID

    使用auto_increment_increment和auto_increment_offset参数
    使用全局节点来生成ID
    在Redis等缓存服务器中创建全局ID(推荐)

    完!


    原作者:唐成勇
    原文链接:MySQL性能管理及架构设计(三):SQL查询优化、分库分表 - 完结篇
    原出处:思否segmentfault

    d69852a32e3caeae99df6cf5aa1ad999.gif
    展开全文
  • 数据库索引就好比一本字典前面的目录,它是方便快速查询操作的,能提高查询效率。一方面,我们在编写sql语句时,要争取写出高质量的sql语句,以便减小数据库底层查询优化器组件的工作,提升性能(参见前面一篇介绍...

    数据库索引就好比一本字典前面的目录,它是方便快速查询操作的,能提高查询效率。一方面,我们在编写sql语句时,要争取写出高质量的sql语句,以便减小数据库底层查询优化器组件的工作,提升性能(参见前面一篇介绍sql查询过程的文章https://blog.csdn.net/talkingmute/article/details/104127163);另一方面,要防止索引失效的情况。

    我们创建索引的场景,要和查询语句中条件字段的使用频率相关,比如某几个字段经常被查询,就创建这几个字段的复合索引。如某个字段经常被查询,就创建这个字段的单独索引。

    mysql数据库创建索引:mysql> alter table 表名 add index 索引名(某个字段,某个字段,某个字段...);

    sqlite数据库创建索引:sqlite> create index 索引名 on 表名(某个字段,某个字段,某个字段...);

    mysql数据库里可以用explain select ...查询语句;来查看这条语句的查询性能,是否高质量。比如:explain select id from department where name='技术部';这里我们在部门表里根据name字段,查询id,因为name字段没有索引,所以现在性能是最低,参数type为all,就表示是全表查找。

    当我们在name字段创建了索引后,参数type就变成ref,表示性能已经很高了。后面的key就是使用的索引,可以看到是我们创建的name_index。

    上边是单独索引,就是只一个字段的。如我们创建的是复合索引,比如

    这里我们是在员工表里有name,age两个字段的复合索引,这时sql查询后的type也是ref,性能很高。在key里使用的是我们创建的复合索引na_index。

    上边是mysql数据库里使用索引的一些简单步骤,下面转到sqlite数据库。

    比如我们还是有一个员工表employee,在sqlite命令里输入.schema 表名就可以看到该表的结构信息。

    sqlite> .schema employee
    CREATE TABLE employee(id integer primary key autoincrement,name text not null,dept_id int, age int,foreign key(dept_id) references department(id));
    CREATE INDEX name_age_index on employee(name,age);
    CREATE INDEX age_index on employee(age);

    可以看到里面有2个索引,一个是name和age复合索引,一个是age的单独索引。

    在sqlite中有一个命令叫做explain query plan,可以查看sqlite是如何执行查找操作的。不用索引时,使用的是“SCAN”这个词,即全表扫描。使用索引时,使用的是“SEARCH”这个词。

    比如下面两条命令:

    sqlite> explain query plan select id,name,age from employee where name='aa' and age=23;

    sqlite> explain query plan select id,name,age from employee where dept_id=3;

    可以看到dept_id字段没有索引,是scan table employee,表示是全表扫描。

    而对于复合索引来说,我们要按照创建时的字段顺序来进行条件查询,不能中间有跳过。比如复合索引是a,b,c这样字段顺序,在写sql语句时,可以用复合索引的就是a,ab,abc这样字段条件。如把前面的字段跳过,只判断后面字段,将不会使用复合索引。还有,中间的查询条件字段不能有算术运算(加减乘除等),不能有!=和<>判断大小运算符,这些都是会让索引部分失效的情况。

    这里,我们先删除age_index这个单独索引,然后测试name_age_index这个复合索引:

    这里的条件语句where name>'aa' and age>23;因为name字段后出现>判断大小运算符,所以会导致复合索引部分失效,下面是(name>?)丢失了后面的age索引字段。

    这里的条件语句where age>23;因为前面的name字段没有,所以复合索引完全失效,一个也没用上,是全表扫描。

    这里的条件语句where name!='aa' and age>23;因为name字段出现!=判断运算符,会导致第一个字段的索引就无法使用,继而后面字段的索引也没法用,是全表扫描。

    还有,关于in(某几个值)或者between and(在。。。范围)这样的运算指令,in是会让索引继续有效的,建议可以多使用它。而between and的效果类似于><大于小于运算符。

    总结来说,对于字段的判断查询:

    1、=等于和in(某几个值)是会保持索引字段有效。

    2、><这样大于小于和between and(区间判断)虽然不会让该索引字段失效,但是会导致后续的其他索引字段失效。

    3、算术运算(加减乘除)和!=不等于一样,都是严重让索引字段完全失效的,包括后续的其他索引字段也失效。

    比如:

    关于模糊查询like %这样sql语句,看下面这个例子,找出年龄在30-39之间的员工。

    当使用sqlite> select id,name,age from employee where age like '3%';是全表扫描,

    改成sqlite> explain query plan select id,name,age from employee where age >=30 and age <=39;就是使用索引了。

     

    展开全文
  • 数据库 索引

    2020-12-14 18:21:27
    数据库 索引 文章目录数据库 索引1、概述2、索引的种类3、索引的底层实现原理3.1 索引的基础知识3.1 索引提高检索速度3.3 哈希索引4、...(4)通过使用索引,可以在查询的过程中,使用优化隐藏,提高系统的性能。
  • 转:数据库索引优化

    2014-01-15 16:10:18
    给定查询的不同执行策略可能会有不同的代价,构造具有最小查询执行代价的查询执行...为了在诸多查询策略中作出选择,数据库系统的优化器必须估计每个查询策略的代价,磁盘访问次数常常是衡量代价的主要标准。在没有按照
    给定查询的不同执行策略可能会有不同的代价,构造具有最小查询执行代价的查询执行计划是数据库系统的职责。查询优化是为了查询选择最有效的查询策略的过程。查询优化是尽量找出与给定表达式等价的、但是执行效率更高的一个表达式,而且决定执行运算时所采用的具体算法以及将使用的特定索引等。
    为了在诸多查询策略中作出选择,数据库系统的优化器必须估计每个查询策略的代价,磁盘访问次数常常是衡量代价的主要标准。在没有按照某策略执行查询前,准确计算出该策略的代价是不可能的,所以,优化器要利用数据库系统中的统计信息,来估计查询策略的代价。Informix数据库系统这些统计信息保存在SYSMASTER数据库中,
    如果要维护准确的统计值,那么每当表数据修改时,相应的统计值也必须更新,这种更新会带来很大的代价,因此Informix系统不是在每次修改时对统计值更新。因此,用于选择查询策略的统计数据不一定完全正确,有时会遇到查询用不到应该使用的索引,就是统计信息没有更新的原因。 对Informix数据库系统,这些统计信息保存在SYSMASTER数据库中,可以使用UPDATE STATISTICS命令更新。
    以下是用于估计代价的信息:
    记录数
    表空间的页数
    记录长度
    字段不同值个数
    字段值的分布
    索引的层数
    索引叶结点数目
    索引B+树的深度
    索引是升序还是降序或聚类索引
    索引占用的页面数目
    Informix 数据库服务器中的优化器为SQL语句的查询提供最有效的策略,这就使得你在进行表的连接查询时不必全面考虑究竟那个表首先搜索,以及究竟需要使用那个索引。
    通过执行update statistics命令可以更新系统的统计信息,使得优化器得到当前最新的统计信息。当修改或删除一个表的相关数据时,系统的统计信息并不自动更新。比如:如果使用delete命令删除一个数据库表内的一条记录,删除完成后查找systables内关于该表的记录信息时,将会发现nrows(数据库表的记录行数目)并没有改变。而通过执行update statistics命令,就可以使系统表systables、sysdistrib、syscolumns、sysindexes等表内的信息得到更新。在运行完update statistics后,这时就会发现systables内的nrows字段已得到更新。如果执行update statistics  medium(high),在sysdistrib表内还可以得到更新的数据分布信息。所以,当大量地修改数据库表后最好执行一下update statistics操作。另外,update statistics将强迫存储过程的优化(对sysprocpplan更新)。以下是与update statistics 相关的系统表:

    1、syscolumns:
    描述了数据库内的每个字段,其中的colmin、colmax存储了数据库各表字段的次小及次大值,这些值只有在该字段是索引且运行了Update statistics之后才生效。如对于字段值1、2、3、4、5,则4为次大值,2为次小值。

    2、sysdistrib:
    存储了数据分布信息。该表内提供了详细的表字段的信息用于提供给优化器优化SQL  Select语句的执行。当执行update statistics  medium(high)之后将往此表存入信息。
    执行“dbschema -hd”可以得到指定表或字段的分布信息

    2、sysindexes:
    描述了数据库内的索引信息。对于数据库内的每个索引对应一条记录。修改索引之后只有执行Update statistics才能使其改变在该表内得到反映。同时也更新clust的数值,在该表的数据页数目及数据库记录条数之间

    3、systables:
    通过执行Update statistics可以更新nrows数据

    update statistics有以下三种级别:

    1、LOW:
    缺省为LOW,此时搜集了关于column的最少量信息。只有systables、syscolumns、sysindexes内的内容改变,不影响sysdistrib。为了提高效率,一般对非索引字段执行LOW操作

    2、HIGH:
    此时构建的分布信息是准确的,而不是统计意义上的。
    因为耗费时间和占用CPU 资源,可以只对表或字段执行HIGH操作。对于非常大的表,数据库服务器将扫描一次每个字段的所有数据。可以配置DBUPSPACE环境变量来决定可以利用的最大的系统磁盘空间
    3、MEDIUM:
    抽样选取数据分布信息,故所需时间比HIGH要少
    什么时候应该执行update ststistics ?
    建议在以下情况,执行update statistics 操作:
    对数据做了大量修改,大量是针对数据的分布而言,若数据分布没有明显的改变则可以不做
    改变的数据库表有与之相关的存储过程,避免在运行时存储过程重新优化
    数据库升级之后完成对索引的转变
    update ststistics 的方法
    考虑到速度性能因素,执行update statistics的推荐方法:
    对表执行:update  statistics medium for table ####  distributions only
    对每个索引的首字段执行:update statistics high
    对复合索引执行:update statistics low
    必要时对非索引字段但在条件中使用到的字段执行Update statistics high操作
    展开全文
  • ALTER SYSTEM SET OPTIMIZER_MODE=RULE scope=both; 其他可以选择的模式还有ALL_ROWS/CHOOSE/FIRST_ROWS/ALL_...首先,应在PL/SQL工具的执行计划中查看,各种优化器模式下的索引执行情况。然后设置合适的优化器...
  • 数据库索引设计及优化

    千次阅读 2018-11-01 12:12:11
    1.表的索引数量不应有上限,只要所有的SQL都能够流畅的运行。 一个页从磁盘读取到数据缓冲池中,一个页包含多条记录,我们可能需要该页上的所有行或一部分行或一行,但花费的成本都相同,约10ms. 在计算领域中, 10...
  • 第1章 概述 1、性能低下的常见原因--不合适的索引 ...3、磁盘驱动使用率 只有在更新频率多于10次/秒的情况下,不稳定列才可能成为问题 4、系统化的索引设计 1)矩阵模型的方法,用于预测每个字段读取和更新...
  • 数据库索引

    2019-11-07 16:48:50
    一、数据库索引 1.1 Cardinality关键字 数据库中有一个关键字Cardinality,优化器会根据这个关键字的值来判断是否使用索引,一般使用索引的字段是这个字段的值是高选择性的,即这个字段有大量的值,该字段的值越...
  • MySQL查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。  你的最终目标是提交SELECT语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因...
  • 第14章 优化器不是完美的 练习 14.1 重写SQL 14.8中的游标,使得新游标的访问路径满足: MC=1仅需访问索引无排序 -- SQL 14.8 DECLARE CURSOR141 CURSOR FOR SELECT LNAME, FNAME, CNO FROM CUST WHERE ...
  • 最近在做SQL Server数据库性能优化,因此复习下一索引、视图、存储过程等知识点。本篇为索引篇,知识整理来源于互联网。 索引加快检索表中数据的方法,它对数据表中一个或者多个列的值进行结构排序,是数据库中一个...
  • 条件1: 如果驱动表所对应的驱动结果集的记录数较少 ,同时在被驱动的表的连接列又存在唯一性索引 (或者在被驱动表的连接列有选择性非常好的非唯一索引) 那么使用嵌套循环连接。如果驱动表对应的驱动结果集对应的...
  • 作者想要达成的目标: 试着把我们自己放在优化器的位置. 展示如何使用这些知识来量化系统运行所涉及的工作.
  • 优化器决定了一条sql语句访问路径,使用哪个索引,是否要使用辅助式随机读 索引路径定义一次索引片段的的顺序扫描,以及对每一个所需表页的一次同步读 表的同步读是耗时很多的一个步骤,要减少同步读(减少读...
  • MySQL数据库索引

    2021-03-13 10:39:30
    文章目录索引是什么索引的优缺点索引有哪些结构基本的索引类型 索引是什么 索引是对数据库表中一个或多个...通过使用索引可以在查询过程中使用优化隐藏,提高系统的性能 建立索引需要时间且需要维护,进行插入、删除
  • 数据库索引介绍

    2020-09-16 17:14:19
    索引是建立在数据库表的某些列上面的 1.1 索引的优点 为什么要创建索引? 答:创建索引可以大大的提高系统的查询性能。...第五、通过使用索引,在查询的过程中,使用查询优化器,提高系统的性能。 1.2
  • 将执行计划"反编译"成 SELECT 语句,运行 SHOW WARNINGS ,可得到被 MySQL优化器优化后的查询语句。 EXPLAIN PARTITIONS SELECT 用于分区表的 EXPLAIN 生成 QEP 的信息,用来查看索引是否正在被使用,并且输出 其...
  • 概述很多朋友接触数据库会简单的认为优化就是建立索引,其实,数据库的优化还是...接下来分享的这本书--数据库查询优化器的艺术:原理解析与SQL性能对sql优化这块讲的还是很细的。全书共四篇,第一篇是理论、第二篇是...
  • 高效索引使用索引的理由由于mysql在默认情况下,表中的数据记录是没有顺序可言的,也就是说在数据检索过程中,符合条件的数据存储在哪里,我们是完全不知情的,如果使用select语句进行查询,数据库会从第一条记录...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,287
精华内容 914
关键字:

数据库索引优化器