精华内容
下载资源
问答
  • MySQL索引优化

    千次阅读 2021-03-09 16:25:41
    在MySQL中,我们经常会为表中的某些字段建立索引,那么怎么通过索引优化我们的查询呢? 索引的分类与基本概念 索引可以分为以下五种: 普通索引:仅加速查询。 唯一索引:普通索引+列值唯一(可以有null)。 ...

    在MySQL中,我们经常会为表中的某些字段建立索引,那么怎么通过索引优化我们的查询呢?

    索引的分类与基本概念

    索引可以分为以下五种:

    • 普通索引:仅加速查询。

    • 唯一索引:普通索引+列值唯一(可以有null)。

    • 主键索引:唯一索引+列值不能为空。

    • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。

    • 全文索引:对文本的内容进行分词,进行搜索,通常用于varchar或text字段(只支持MyISAM引擎)。

    根据索引的存储方式还可以分为以下两种:

    • 聚簇索引(Clustered Index):数据与索引存储在一起,主键索引底层就是用聚簇索引实现的。

    • 非聚簇索引(Secondary Index):数据与索引分开存放,其他非主键索引底层都是非聚簇索引实现的,也叫二级索引。

    聚簇索引的优点与缺点:

    • 优点:因为索引和数据存在为同一棵B+树中,所以访问数据速度快。

    • 缺点:插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置,基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题,聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。

    几个基本概念:

    • 索引合并:使用多个单列索引组合搜索。

    • 回表查询:在非聚簇索引上并没有存放数据行,存放的只是主键ID,如果需要查询除索引列、主键列的其他列就需要回到聚簇索引上根据主键ID查询。

    • 覆盖索引:查询的数据列从索引中就能够获取到,不必回表查询,换句话说就是所查询的列能被所建的索引覆盖到。

    索引的使用

    创建索引

    可以在创建表时创建索引,格式如下:

    CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 
    

    创建表时创建索引:

    create table t1 (id int, name varchar(50), index idx_id(id)); -- 创建普通索引
    create table t2 (id int, name varchar(50), unique index idx_id(id)); -- 创建唯一索引
    create table t3 (id int, name varchar(50), primary key(id)); -- 创建主键索引
    create table t4 (id int primary key, name varchar(50)); -- 创建主键索引
    create table t5 (id int, name varchar(50), age int, index idx_id_name_age(id, name, age)); -- 创建组合索引
    

    表已经创建好后增加索引,语法:

    ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名(长度))
    

    使用:

    alter table t5 add index idx_name(name);
    

    使用CREATE INDEX创建索引,语法:

    CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])
    

    使用:

    create index idx_age on t5(age);
    

    查询索引

    mysql> show index from t5;
    +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | t5    |          1 | idx_id_name_age |            1 | id          | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
    | t5    |          1 | idx_id_name_age |            2 | name        | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
    | t5    |          1 | idx_id_name_age |            3 | age         | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
    | t5    |          1 | idx_name        |            1 | name        | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
    | t5    |          1 | idx_age         |            1 | age         | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |
    +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    5 rows in set (0.05 sec)
    

    删除索引

    使用ALTER删除索引:

    alter table t5 drop index idx_name;
    

    使用DROP删除索引:

    drop index idx_age on t5;
    

    索引的最佳实践

    1. 避免使用select *,实际业务中需要哪些属性就获取哪些(按需获取),假设需要获取的列都在索引上面,这样就能直接返回,无需回表查询。

    2. 当使用索引列进行查询的时候不要使用表达式,这样会导致索引失效,可以把计算放到业务层而不是数据库层。

    3. 当使用索引列进行查询的时候不要使用not in,<>,这样会导致索引失效。

    4. 当使用索引列进行查询的时候,like '%xxx%'不会走索引,like 'xxx%'会走索引的一部分。

    5. 当使用索引列进行查询的时候,条件的类型与字段的类型需保持一致,字符串类型除外,字符串类型可以转换为任意类型。

    6. 尽量使用主键查询,而不是其他索引,因为主键查询不会触发回表查询。

    7. 更新十分频繁,数据区分度不高的字段上不宜建立索引,更新会变更B+树,更新频繁的字段建议索引会大大降低数据库性能,类似于性别这类区分不大的属性,建立索引是没有意义的,不能有效的过滤数据,一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。

    8. 当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致。

    9. 能使用limit的时候尽量使用limit。

    10. 单表索引建议控制在5个以内,单索引字段数不允许超过5个(组合索引)。

    11. exists代替in。

    12. 最左前缀原则。

    关于exists代替in

    exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,反之如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false。

    假设现在有如下两条SQL语句:

    select * from A where exists (select * from B where B.id = A.id);
    select * from A where A.id in (select id from B);
    

    查询1可以转化以下伪代码,便于理解:

    j = 0;
    for (i = 0; i < count(A); i++) {
      a = get_record(A, i); // 从A表逐条获取记录
      if (B.id == a.id) // 如果子条件成立
        result[j++] = a;
    }
    return result;
    

    大概就是这么个意思,其实可以看到,查询1主要是用到了B表的索引,A表如何对查询的效率影响不大。

    假设B表的所有id为1,2,3,查询2可以转换为:

    
    select * from A where A.id = 1 or A.id = 2 or A.id = 3;
    

    这个好理解了,这里主要是用到了A的索引,B表如何对查询影响不大。

    如果查询的两个表大小相当,那么用in和exists差别不大。

    如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。

    展开全文
  • 一、索引优化分析

    万次阅读 2021-06-10 13:53:32
    一、索引优化分析 1.性能下降sql慢 执行时间长 等待时间长出现的原因 查询语句写的太差了 索引失效–建了索引没有用上 ​ ps: 索引可以在底层优化排序, 关联查询太久join() 太多关联查询,和子查询(需求分析...

    一、索引优化分析

    1.性能下降sql慢 执行时间长 等待时间长出现的原因

    • 查询语句写的太差了

    • 索引失效–建了索引没有用上

      ​ ps: 索引可以在底层优化排序,

    • 关联查询太久join() 太多关联查询,和子查询(需求分析不详细或者是设计上有缺陷)

    • 服务器调优以及各个参数设置(缓冲,线程数等)

    2.常见通用的join 查询

    a.sql执行顺序

    ​ from>where>group by>having>select>order by>limit

    ​ group by(分组)>having(分组过滤)>count(分组函数)

    b.7种join

    ​ 基于: sql 语句SELECT * from tbl_emp a {参数}JOIN tbl_dept b on a.deptId=b.id;

    ​ 1.inner join 是公有部分

    ​ left join 左表全部,右表空补null

    ​ right join 右表全部,左表空补null

    ​ A表独有 B表取null

    ​ SELECT * from tbl_emp a LEFT JOIN tbl_dept b on a.deptId=b.id where b.id is null;

    ​ B表独有 A表取null

    ​ SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptId=b.id where a.id is null;

    ​ A,b都有 涉及union

    ​ SELECT * from tbl_emp a LEFT JOIN tbl_dept b on a.deptId=b.id
    ​ UNION
    ​ SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptId=b.id;

    ​ A,B各个的独有

    ​ SELECT * from tbl_emp a LEFT JOIN tbl_dept b on a.deptId=b.id where b.id is null
    ​ union
    ​ SELECT * from tbl_emp a right JOIN tbl_dept b on a.deptId=b.id where a.id is null;

    展开全文
  • MySQL数据库:SQL优化与索引优化

    千次阅读 多人点赞 2018-11-25 02:36:55
    一、索引优化规则: 1、union、in、or 都能够命中索引,建议使用 in: (1)union:能够命中索引,并且MySQL耗费的CPU最少。 select * from doc where status=1 union all select * from doc where status=2; ...

    一、索引优化:

    1、like语句的前导模糊查询不使用索引:

    select * from doc where title like '%XX';   --不能使用索引
    select * from doc where title like 'XX%';   --非前导模糊查询,可以使用索引

    2、负向条件查询不能使用索引:

    负向条件有:!=、<>、not in、not exists、not like 等

    例如下面SQL语句:(假设status的取值为0、1、2、3、4)

    select * from doc where status != 1 and status != 2;     --不能使用索引

    select * from doc where status in (0,3,4);                      --优化为 in 查询,可以使用索引

    3、范围条件右边的列不能使用索引(范围列可以用到索引):

    范围条件有:<、<=、>、>=、between等。

    索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。

    假如有联合索引 (emp_no 、title、from_date ),那么下面的 SQL 中 emp_no 可以用到索引,而title 和 from_date 则使用不到索引。

    select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

    4、在索引列做任何操作(计算、函数、表达式)会导致索引失效而转向全表扫描:

    • select * from doc where YEAR(create_time) <= '2016';         -- 不能使用索引
    • select * from doc where create_time<= '2016-01-01';            -- 可以使用索引
    • select * from order where date < = CURDATE();                 -- 不能使用索引
    • select * from order where date < = '2018-01-2412:00:00';     -- 可以使用索引
    • select id from t where substring(name,1,3)=’abc’                   -- 不能使用索引
    • select id from t where name like ‘abc%’                                 -- 可以使用索引
    • select id from t where num/2=100                                          -- 不能使用索引
    • select id from t where num=100*2                                         -- 可以使用索引

    5、where 子句中索引列使用参数,也会导致索引失效:

    因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num=@num                                   -- 不能使用索引

    select id from t with(index(索引名)) where num=@num   --可以改为强制查询使用索引:

    6、强制类型转换会导致全表扫描:

    字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作。 

    如果 phone 字段是 varchar 类型,则下面的 SQL 不能命中索引,因为内部发生的类型转换。

    select * from user where phone=13800001234;      -- 不能使用索引

    select * from user where phone='13800001234';     -- 可以使用索引

    7、is null, is not null 在无法使用索引,不过在mysql的高版本已经做了优化,允许使用索引

    select id from t where num is null;     -- mysql低版本不能使用索引

    select id from t where num=0;           -- 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询

    8、使用组合索引时,要符合最左前缀原则:

    组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c),那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。 

    (1)建立联合索引的时候,区分度最高的字段在最左边:

    (2)存在等号和非等号混合判断条件时,在建立索引时,把等号条件的列前置,如 where a > ? and b= ?,那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。

    (3)最左前缀查询时,并不是指SQL语句的where顺序要和联合索引一致,但还是建议 where 条件的顺序和联合索引一致。

    (4)假如index(a,b,c), where a=3 and b like 'abc%' and c=4,a能用,b能用,c不能用。

    9、利用覆盖索引来进行查询操作,避免回表,减少select * 的使用 :

    覆盖索引:被查询列要被所建的索引覆盖,被查询列的数据能从索引中直接取得,不用通过行定位符 再到 row 上获取,加速查询速度。

    例如登录业务需求,SQL语句如下。

    Select uid, login_time from user where login_name=? and passwd=?

    可以建立(login_name, passwd, login_time)的联合索引,由于 login_time 已经建立在索引中了,被查询的 uid 和 login_time 就不用去 row 上获取数据了,从而加速查询。

    10、利用索引下推减少回表的次数:

    索引下推是Mysql5.6版本推出的功能,用于优化查询。

    在不使用索引下推的情况下,在使用非主键索引进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

    在使用索引下推的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

    11、使用前缀索引:

    短索引不仅可以提高查询性能而且可以节省磁盘空间和I/O操作,减少索引文件的维护开销,但缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引。比如有一个varchar(255)的列,如果该列在前10个或20个字符内,可以做到既使前缀索引的区分度接近全列索引,那么就不要对整个列进行索引。为了减少key_len,可以考虑创建前缀索引,即指定一个前缀长度,可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度(计算前缀索引的区分度在文章第三部分会介绍)。

    12、order by、group by后面的列如果有索引,可以利用索引的有序性可以消除排序带来的CPU开销。

    (1)order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。例如对于语句 where a= ? and b= ? order by c,可以建立联合索引(a,b,c)。

    (2)如果索引中有范围查找,那么索引有序性无法利用,如 WHERE  a > 10 ORDER BY b; 索引(a,b)无法排序。

    (3)如果是前缀索引,是不能消除排序的

    (4)order by排序字段顺序,即asc/desc升降要跟索引保持一致,充分利用索引的有序性来消除排序带来的CPU开销

    12、进行join联表查询的字段需要建立索引,join最好不要超过三个表,需要 join 的字段,数据类型必须一致:

    多表关联查询时,保证被关联的字段需要有索引。left join是由左边决定的,左边的数据一定都有,所以右边是我们的关键点,建立索引要建右边的。当然如果索引在左边,可以用right join。

    13、单表索引建议控制在5个以内。

    索引不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,同时也会暂用空间。一个表的索引数较好不要超过5个。

    14、SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

    consts:单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。

    ref:使用普通的索引

    range:对索引进行范围检索。

    当 type=index 时,索引物理文件全扫,速度非常慢。

    15、业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引,防止脏数据产生:

    不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然。

    16、更新十分频繁、数据区分度不高的列不宜建立索引:

    数据更新会变更 B+ 树,在更新频繁的字段建立索引会大大降低数据库性能。类似于“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。

     

    二、SQL语句优化:

    1、减少请求的数据量:

    (1)只返回必要的列,用具体的字段列表代替 select * 语句

    MySQL数据库是按照行的方式存储,而数据存取操作都是以一个页大小进行IO操作的,每个IO单元中存储了多行,每行都是存储了该行的所有字段。所以无论取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。但是如果查询的字段都在索引中,也就是覆盖索引,那么可以直接从索引中获取对应的内容直接返回,不需要进行回表,减少IO操作。除此之外,当存在 order by 操作的时候,select 子句中的字段多少会在很大程度上影响到我们的排序效率。

    (2)只返回必要的行,使用 Limit 语句来限制返回的数据。如果不使用 Limit  的话,MySQL将会一行一行的将全部结果按照顺序查找,最后返回结果,借助 Limit 可以实现当找到指定行数时,直接返回查询结果,提高效率 

    2、优化深度分页的场景:利用延迟关联或者子查询

    对于 limit m, n 的分页查询,越往后面翻页(即m越大的情况下)SQL的耗时会越来越长,对于这种应该先取出主键id,然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。

    延迟关联示例如下,先快速定位需要获取的 id 段,然后再关联:

    # 延迟关联:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

    # 覆盖索引:select的数据列只用从索引中就能够得到,不用回表查询

    select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id;

    但对于深度分页的情况,最好还是将上次遍历到的最末尾的数据ID传给数据库,然后直接定位到该ID处 再 往后面遍历数据

    3、分解大连接查询:

    将一个大连接查询分解成对每一个表进行一次单表查询,然后在应用程序中进行关联,这样做的好处有:

    • (1)减少锁竞争;
    • (2)让缓存更高效。对于连接查询,如果其中一个表发生变化,那么整个查询缓存就无法使用。而分解后的多个查询,即使其中一个表发生变化,对其它表的查询缓存依然可以使用。
    • (3)分解成多个单表查询,这些单表查询的缓存结果更可能被其它查询使用到,从而减少冗余记录的查询。
    • (4)在应用层进行连接,可以更容易对数据库进行拆分,从而更容易做到高性能和可伸缩。
    • (5)查询本身效率也可能会有所提升。比如使用 IN() 代替连接查询,可以让 MySQL 按照 ID 顺序进行查询,这可能比随机的连接要更高效。

    4、避免使用select的内联子查询:

    在select后面有子查询的情况称为内联子查询,SQL返回多少行,子查询就需要执行过多少次,严重影响SQL性能。

    5、尽量使用Join代替子查询:

    由于MySQL的优化器对于子查询的处理能力比较弱,所以不建议使用子查询,可以改写成Inner Join,之所以 join 连接效率更高,是因为 MySQL不需要在内存中创建临时表

    select 
        b.member_id,b.member_type, a.create_time,a.device_model 
    from 
        member_operation_log a 
    inner join 
        (select member_id,member_type from member_base_info where `status` = 1) as b 
    on 
        a.member_id = b.member_id;
    

    6、多张大数据量的表进行JOIN连接查询,最好先过滤在JOIN:

    在多个表进行 join 连接查询的时候,最好先在一个表上先过滤好数据,然后再用过滤好的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间

    7、避免在使用or来连接查询条件:

    如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。

    8、union、in、or 都能够命中索引,但推荐使用 in:

    (1)union:能够命中索引,并且MySQL 耗费的 CPU 最少

    select * from doc where status=1
    union all 
    select * from doc where status=2;

    (2)in:能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计

    select * from doc where status in (1, 2);

    (3)or:新版的 MySQL 能够命中索引,但是如果一个字段有建立索引、一个字段没有建立索引,那么将导致索引失效而进行全表扫描,or 查询优化耗费的 CPU 比 in 多

    select * from doc where status = 1 or status = 2

    对于上面三种关键词:union all 分两步执行,而 in 和 or 只用了一步,效率高一点。用 or 的执行时间比 in 时间长。因为使用 or 条件查询,会先判断一个条件进行筛选,再判断 or 中另外的条件再筛选,而 in 查询直接一次在 in 的集合里筛选,并且or 查询优化耗费的 CPU 比 in 多,所以推荐使用in

    9、对于连续的数值,能用 between 就不要用 in:

    10、小表驱动大表,即小的数据集驱动大的数据集:

    in 和 exists 都可以用于子查询,那么 MySQL 中 in 和 exists 有什么区别呢?

    • (1)使用exists时会先进行外表查询,将查询到的每行数据带入到内表查询中看是否满足条件;使用in一般会先进行内表查询获取结果集,然后对外表查询匹配结果集,返回数据。

    • (2)in在内表查询或者外表查询过程中都会用到索引;exists仅在内表查询时会用到索引

    • (3)一般来说,当子查询的结果集比较大,外表较小使用exist效率更高;当子查询的结果集较小,外表较大时,使用in效率更高。

    • (4)对于 not in 和 not exists,not exists 效率比 not in 的效率高,与子查询的结果集无关,因为 not in 对于内外表都进行了全表扫描,没有使用到索引。not exists的子查询中可以用到表上的索引。

    11、使用union all 替换 union:

    当SQL语句需要union两个查询结果集合时,这两个结果集合会以union all的方式被合并,然后再输出最终结果前进行排序。如果用union all替代union,这样排序就不是不要了,效率就会因此得到提高.。需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录。

    12、优化Group by,使用where子句替换Having子句:

    避免使用having子句,having只会在检索出所有记录之后才会对结果集进行过滤,这个处理需要排序分组,如果能通过where子句提前过滤查询的数目,就可以减少这方面的开销。

    on、where、having这三个都可以加条件的子句,on是最先执行,where次之,having最后。

    提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。

    低效: SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 

    高效: SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

    13、尽量使用数字型字段:

    若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能。引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    14、写出统一的SQL语句:

    对于以下两句SQL语句,很多人都认为是相同的。不过数据库查询优化器则认为是不同的,虽然只是大小写不同,但必须进行两次解析,生成2个执行计划。所以应该保证相同的查询语句在任何地方都一致,多一个空格都不行。

    • select * from dual
    • select * From dual

    15、使用复合索引须遵守最左前缀原则:

    复合索引必须使用到最左边字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    16、当需要全表删除且无需回滚时,使用Truncate替代delete:

    drop、truncate、delete的区别:https://blog.csdn.net/a745233700/article/details/85238118

    17、使用表的别名:

    当在SQL语句中连接多个表时, 使用表的别名并把别名前缀用于每个Column上,这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

    18、避免使用耗费资源的操作:

    带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句,会启动SQL引擎执行耗费资源的排序功能,DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。通常。带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写,如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

    19、Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    20、应尽可能的避免更新聚簇索引数据列,因为聚簇索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。

    21、尽量使用表变量来代替临时表。

    22、考虑使用“临时表”暂存中间结果。临时表并不是不可使用,适当地使用它们可以使某些查询更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表,后面的查询就在临时表中查询了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。但是,对于一次性事件,较好使用导出表。

    23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

    24、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

    25、避免频繁创建和删除临时表,以减少系统表资源的消耗。

    26、尽量避免使用游标,因为游标的效率较差。与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

    27、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。

    28、尽量避免大事务操作,提高系统并发能力。

    29、在运行代码中,尽量使用PreparedStatement来查询,不要用Statement。

     

    三、索引的选择性与前缀索引:

    既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

    第一种情况是表记录比较少,没必要建索引,让查询做全表扫描就好了。

    第二种情况是索引的选择性较低。所谓索引的选择性,是指 不重复的索引值 与 表记录数量 的比值:

    显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。

    例如,employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

    SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.0000 |
    +-------------+

    title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

    有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

    假设employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

    EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
    |  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
    +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

    如果频繁按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引。有两种选择,建<first_name>或<first_name, last_name>,看下两个索引的选择性:

    SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.0042 |
    +-------------+
    SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.9313 |
    +-------------+

    <first_name>显然选择性太低,<first_name, last_name>选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

    SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.7879 |
    +-------------+

    选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

    SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
    +-------------+
    | Selectivity |
    +-------------+
    |      0.9007 |
    +-------------+

    这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,我们把这个前缀索引建上:

    ALTER TABLE employees.employees
    ADD INDEX `first_name_last_name4` (first_name, last_name(4));

    此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

    SHOW PROFILES;
    +----------+------------+---------------------------------------------------------------------------------+
    | Query_ID | Duration   | Query                                                                           |
    +----------+------------+---------------------------------------------------------------------------------+
    |       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    |       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
    +----------+------------+---------------------------------------------------------------------------------+

    性能的提升是显著的,查询速度提高了120多倍。

    前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于覆盖索引

    展开全文
  • SQLServer索引优化 ——无用索引和索引缺失(三) SQL Server 索引优化——无用索引和索引缺失中,我们根据动态视图sys.dm_db_index_usage_stats探测无用索引;SQL Server 索引优化——无用索引和索引缺失(二)...

    SQL Server 索引优化

                                  ——无用索引和索引缺失(三)

    SQL Server 索引优化——无用索引和索引缺失中,我们根据动态视图sys.dm_db_index_usage_stats 探测无用索引;SQL Server 索引优化——无用索引和索引缺失(二)中使用动态视图sys.dm_db_missing_index_details、sys.dm_db_missing_index_groups、sys.dm_db_missing_index_group_stats 发现缺失索引。本文将根据“数据库引擎优化顾问”(DTA)来发现无用或缺失的索引。

    要使用“数据库引擎优化顾问”,首先需要对数据库负载进行监控,为数据库负载分析准备数据。从SSMS的工具中,打开SQL Server Profile,输入安全连接方式。在常规的标签下,模板选择“Standard(默认值)”,事件选择标签下,选择事件Stored Procedures→RPC:Completed;TSQL→SQL:BatchCompleted,SQL:BatchStarting,点击运行。如下图所示:

    监控一段时间后,停止运行,将跟踪文件保存为trace.trc

    在SSMS的工具下,打开“数据库引擎优化顾问”,建立安全连接,如下图。在“工作负荷”下选择跟踪文件所在的路径,选择“工作负荷分析的数据库”,这里我选择的tempdb;最后选择需要优化的数据库和表。

    在优化选项中,“限定优化时间”可以不选,或者自己设定结束时间;“在数据库中使用的物理设计结构(PDS)”,选择“索引和索引视图(D)”;“使用的分区策略”选择“对齐分区(A)”;在数据库中保留的物理设计结构(PDS),我选择了“保留对齐分区(R)”,设定如下:

    点击开始运行后,获得如下建议

    通过测试可以了解到,最终的索引或分区建议,与Profile追踪的时间长短、时间段有关。如果工作负载不代表该数据库的典型工作负载,并且缺少重要的查询,那么索引建议也将是不完整的、不准确的或完全错误的。使用该方法的要求是:

    1. 在不影响生产环境的性能的情况下,尽可能的延长跟踪时间,搜集较多的事件;

    2. 将需要优化的数据库备份,还原到非生产环境服务器,进行分析(因为保证1的情况下,数据量会非常大,在原生产环境进行分析,会消耗大量的生产环境的资源CPU、IO,降低生产环境的性能,影响业务)

    比较动态视图和数据库引擎优化顾问,两者的共同缺点都是可能因搜集的信息不全,导致建议不准确, 所以无论使用动态视图,还是使用数据库引擎优化顾问,其建议都需要审慎使用。

    最后再重申一遍,虽然这些特性在确定可能对数据库有益的索引时非常有用,但它们也可能是一把双刃剑,在错误使用时弊大于利。盲目地实现这些特性的建议几乎总是会导致数据库中的索引重复或重叠,以及索引太多而不是太少。

    如果喜欢,可以扫码关注SQL Server 公众号,将有更多精彩内容分享:

                                                                     

    展开全文
  • SQL Server索引优化——重复索引

    千次阅读 2019-02-20 18:06:00
    SQL Server索引优化——重复索引 在写完《SQLServer索引优化——无用索引和索引缺失》系列后,就想着写点关于发现重复索引的内容,刚好在Kimberly的博文中发现了这篇,就偷懒了,直接将其翻译过来。 一直以来,对...
  • mysql千万级数据量根据索引优化查询速度

    万次阅读 多人点赞 2016-08-15 18:05:16
    (一)索引的作用 ...提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(加索引优化sql;优化sql不在本文阐述范围之内)。 能在软件上解决的,就不在硬件上解决,毕竟硬件提升...
  • 【进阶】索引优化原则

    千次阅读 多人点赞 2019-03-18 19:23:19
    索引优化有很作最佳实践原则,下面对常用原则进行分析。 MySql索引底层数据结构和算法:https://blog.csdn.net/yhl_jxy/article/details/88392411 MySql explan执行计划详解:...
  • Mysql索引优化及面试题

    万次阅读 2019-07-19 08:45:28
    Mysql索引优化 一:索引介绍 索引是关系型数据库中给数据库表中一列或者多列的值排序后的储存结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引. MySql索引类型有:唯一索引,主键(聚集)...
  • MySQL数据库优化之索引优化

    千次阅读 2018-07-17 11:52:32
    学习索引优化之前安装演示数据库以供练习 使用mysql提供的sakila数据库 文件下载: http://downloads.mysql.com/docs/sakila-db.tar.gz 解压后导入数据库 shell&gt; mysql -uroot -p &lt; salila-schema....
  • SQLServer索引优化 ——无用索引和索引缺失 我们知道,合理的索引能大幅提升性能,但冗余的索引也会降低数据库性能。随着我们业务的发展,数据库的中的表、表结构、查询的内容都有可能发生变化。这样,有的索引就...
  • MySQL5.7数据库-索引优化

    千次阅读 2021-02-05 15:42:06
    文章目录索引优化索引分类回表覆盖索引键表SQL来使用口诀来练习 索引优化 索引优化不只是单单对复合索引 也是对普通索引的一种优化 索引分类 分类角度 索引名称 数据结构 B+数,Hash索引, B数等 存储...
  • mysql 索引 优化 面试

    千次阅读 2017-05-18 17:37:57
    mysql 索引 优化 面试题目: 问如何优化下面的Mysql SQL语句? select * from employee where employee.deptName in ( "departA", "departB", "departC") and tbl.locationID = 3 and tbl.level > 5; 思路: 1: ...
  • 【数据库】索引优化策略

    千次阅读 2018-10-31 20:53:01
    索引优化策略 关于什么是索引,如何建立索引,索引的优缺点等,请移步我的另外一篇文章mysql索引简谈 一、为什么要建立索引? 一句话,为了加快查询效率。注意这里的“查询”,而不是增删改。建立索引的列,一旦...
  • Sql server索引优化

    千次阅读 2017-04-01 15:15:27
    Sql server索引优化
  • mysql调优二-索引优化

    千次阅读 2021-01-11 17:13:32
    mysql调优-索引优化 文章目录mysql调优-索引优化前言一、前置:索引相关知识1、索引用途/优点/分类2、索引过程现象3、索引数据结构4、索引匹配方式二、hash/(非)聚簇/覆盖 索引说明1.哈希索引2.(非)聚簇索引3.覆盖...
  • SQL优化:索引优化

    万次阅读 2017-08-22 08:18:08
     SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引,...
  • 【MySQL】索引优化原则

    千次阅读 2020-04-30 10:24:32
    前面几篇博文谈到索引使用场景和explain命令帮助我们分析索引的执行情况,今天进入正题,来谈谈索引优化的原则。 1、全值匹配 查询语句尽量使用全值匹配。 2、左前缀原则 如果一个索引是组合索引,索引了多列,要...
  • MySql ORDER BY 索引优化

    千次阅读 2017-11-25 17:03:13
    上一篇:MySql 索引优化 MySql 支持两种方式的排序,FileSort 和 Index ,Index 效率高,它指的是MySql 扫描索引本身完成排序。FileSort 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。因此...
  • MySQL索引与索引优化

    千次阅读 2020-09-04 16:48:00
    MySQL索引就是用于优化器上。 索引: MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。 索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速...
  • SQLServer索引优化——重复索引(部分重复) 前文给出了查询完全重复索引方法,但更多的情况下,是多个管理员,根据单个或部分查询,创建其各自需要的索引,而没有考虑整体情形,就会出现大量的部分重复索引,这对...
  • MySQL利用索引优化ORDER BY排序语句

    千次阅读 2017-11-02 21:36:26
    MySQL如何利用索引优化ORDER BY排序语句 MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组...
  • 索引优化原则

    千次阅读 2014-04-01 14:05:57
    索引优化原则   1 数据类型的选择   数据类型越小越好,越简单越好,避免null值     2 索引的列顺序   索引的列顺序很重要,例如index(a,b),当where中有a或a、b时,索引会被使用,但只有b时则不会被使用。...
  • 如何在 SQL 数据库优化 索引,SQL索引优化-技巧 出处:http://www.cr173.com/html/8688_all.html 在数据库存优化设计中往往会提到索引,这编文章就来详细的说明一下在 SQL SERVER 下面的建立索引的技巧和需要注意...
  • MySQL索引优化分析4—关联查询与子查询优化1.关联查询优化1.1 案例1.2 建议2.子查询优化 之前介绍了单表查询中的索引优化,本节将介绍关联查询中的索引优化。 1.关联查询优化 案例将使用的数据表的创建如下: # 1....
  • EventBus源码---索引优化的使用

    千次阅读 2021-01-02 17:56:49
    搞了4个小时左右,终于把EventBus的索引优化弄明白了。这里的明白是知道怎么使用了,源码的话,我们下篇文章来说。所以这篇文章我们仅从使用上面来说索引优化。有的同学可能会说,使用嘛,即便是没用过,百度一下也...
  • sql中索引优化查询效率的总结

    千次阅读 2017-07-24 16:43:17
    mysql使用索引优化查询效率。
  • mysql索引优化

    千次阅读 2017-05-28 16:16:28
    十分地简单认识下与索引有关的数据结构 ...优化器不使用索引优化 索引的类型普通索引 唯一索引 主键索引 联合索引 覆盖索引 全文索引 创建索引的几大原则 简单例子体验下联合索引 1.十分简单地认识下与索
  • 常用的数据库索引优化语句总结

    千次阅读 2016-07-18 21:51:42
    近期,我们对之前编写的数据库脚本进行了全面的自查,从数据库的性能方面考虑,将脚本里面的很多SQL语句进行了优化。对于一条SQL语句来说,索引...本文对常用的数据库索引优化语句进行了总结,可供相关的开发人员参考。
  • 面试官:索引优化策略有哪些?

    千次阅读 2019-12-23 15:17:37
    索引优化策略 不要在索引列上进行运算或使用函数 在列上进行运算或使用函数会使索引失效,从而进行全表扫描。如下面例子在publish_time,id列上分别加上索引,publish_time为datetime类型,id为in...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 491,665
精华内容 196,666
关键字:

索引优化