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

    千次阅读 2021-02-19 09:42:44
    将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的...

    目录

    一、MySQL索引基础

    二、MySQL索引优化实战

    三、 索引优化实战


    MySql学习专栏

    1. MySQL基础架构详解

    2. MySQL索引底层数据结构与算法

    3. MySQL5.7开启binlog日志,及数据恢复简单示例

    4. MySQL日志模块

    5. MySQL的MVCC及实现原理

    6. MySQL索引优化

    7. MySQL——通过EXPLAIN分析SQL的执行计划

    8. MySQL执行语句性能优化

    9. MySQL中的关联查询(内连接,外连接,自连接)

    10. MySQL中复杂的增删改语句

    11. 面试必问的 MySQL,你懂了吗?

     

     

    本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础索引优化实战数据库索引背后的数据结构三部分相关内容。

    以下讲解是居于数据库  5.7.32 版本

     

    一、MySQL索引基础

    首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的基本原则。

    此部分用于测试索引创建的user表的结构如下:

    desc user;

     

    1. 什么是索引?

    "索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。"

    ——《高性能MySQL》

     我们需要知道索引其实是一种数据结构,其功能是帮助我们快速匹配查找到需要的数据行,是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

    2. 索引类型

    可以使用SHOW INDEX FROM table_name;查看索引详情:

    主键索引 PRIMARY KEY:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。 

     

    唯一索引 UNIQUE:唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引:

    ALTER TABLE user ADD UNIQUE u_phone (phone);

     删除索引命令

    DROP INDEX <索引名> ON <表名>
    
    drop  user index on idx_name_age_position;

    语法说明如下:

    • <索引名>:要删除的索引名。
    • <表名>:    指定该索引所在的表名。

    唯一组合索引命令

    ALTER TABLE table_name ADD UNIQUE <索引名> (<列>,<列>)
    ALTER TABLE user ADD UNIQUE u_name_age (user_name,age);

     

    普通索引 INDEX:这是最基本的索引,命令如下

    ALTER TABLE <table_name> ADD INDEX <idx_name> (<column>);

    语法说明如下:

    • <idx_name>:要添加的索引名称
    • <table_name>:    指定该索引所在的表名
    • <column>:    指定要添加索引的列
    alter table user add index idx_satus (status);

     
    组合索引 INDEX:即一个索引包含多个列,多用于避免回表查询,命令如下

    ALTER TABLE <table_name> ADD INDEX <idx_name> (<column1>,<column2>);

    语法说明如下:

    • <idx_name>:要添加的索引名称
    • <table_name>:    指定该索引所在的表名
    • <column1>:    指定要添加索引的列1
    • <column2>:    指定要添加索引的列2
    alter table user add index idx_user_age (user_name,age);

    全文索引 FULLTEXT:也称全文检索,是目前搜索引擎使用的一种关键技术,命令如下

    ALTER TABLE <table_name> ADD FULLTEXT <f_name> (<column>);

    语法说明如下:

    • <table_name>:    指定该索引所在的表名
    • <f_name>:   要添加的索引名称
    • <column>:    指定要添加索引的列

    索引一经创建不能修改,如果要修改索引,只能删除重建。

    3、索引设计的原则

    1. 适合索引的列是出现在where子句中的列,或者连接子句中指定的列;
    2. 基数较小的类,索引效果较差,没有必要在此列建立索引;
    3. 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;
    4. 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

    二、MySQL索引优化实战

    上面我们介绍了索引的基本内容,这部分我们介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

    此部分用于测试的user表结构:

     

    1、索引相关的重要概念

    基数:单个列唯一键(distict_keys)的数量叫做基数。

    select count(distinct user_name),count(distinct gender) from user;
    

    user表的总行数是6,gender列的基数是3,说明gender列里面有大量重复值,name列的基数等于总行数,说明name列没有重复值,相当于主键。

    返回数据的比例:user表中共有6条数据:

    查询满足性别为0(男)的记录数:

    那么返回记录的比例数是:

     

    同理,查询name为'smile'的记录数:

    现在问题来了,假设name、age 列都有索引,那么SELECT * FROM user WHERE age = 28 ;

    SELECT * FROM user WHERE name = 'smile';都能命中索引吗?

    user表的索引详情:

     SELECT * FROM user WHERE age = 28;没有命中索引,注意filtered的值就是上面我们计算的返回记录的比例数。

    EXPLAIN select * from user where age = 28;
    

     

    SELECT * FROM user WHERE name = 'smile';命中了索引index_name,因为走索引直接就能找到要查询的记录,所以filtered的值为100。

     

    组合索引底层还是使用B+树索引,并且还是只有一棵树,只是此时的排序会:首先按照第一个索引排序,在第一个索引相同的情况下,再按第二个索引排序,依次类推。

    这也是为什么有“最佳左前缀原则”的原因,因为右边(后面)的索引都是在左边(前面)的索引排序的基础上进行排序的,如果没有左边的索引,单独看右边的索引,其实是无序的。

    还是以字典为例,我们如果要查第2个字母为 k 的,通过目录是无法快速找的,因为首字母 A - Z 里面都可能包含第2个字母为 k 的。

    回表:当对一个列创建索引之后,索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能,如果回表次数太多,就不应该走索引扫描,应该直接走全表扫描。

    EXPLAIN命令结果中的Using Index意味着不会回表,通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

     

    三、 索引优化实战

    有些时候虽然数据库有索引,但是并不被优化器选择使用。我们可以通过' SHOW STATUS LIKE 'Handler_read% ';查看索引的使用情况:

     

    Handler_read_key:如果索引正在工作,Handler_read_key的值将很高。

    Handler_read_rnd_next:数据文件中读取下一行的请求数,如果正在进行大量的表扫描,值将较高,则说明索引利用不理想。

    索引优化规则:

    1)如果MySQL估计使用索引比全表扫描还慢,则不会使用索引。

    返回数据的比例是重要的指标,比例越低越容易命中索引。记住这个范围值——30%,后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

    2)前导模糊查询不能命中索引。

    EXPLAIN SELECT * FROM user WHERE user_name LIKE '%s%'; 

     

    非前导模糊查询则可以使用索引,可优化为使用非前导模糊查询:

    EXPLAIN SELECT * FROM user WHERE user_name name LIKE 's%';

     

    3)数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来。

    EXPLAIN SELECT * FROM user WHERE user_name = 1;

    EXPLAIN SELECT * FROM user WHERE user_name = '1';

    4)union 能够命中索引,建议使用unionin、or不能命中索引

    union:

    EXPLAIN SELECT*FROM user WHERE status =1
    UNION ALL
    SELECT*FROM user WHERE status = 2;

    in:

    EXPLAIN SELECT * FROM user WHERE status IN (1,2);

     

    or:

    EXPLAIN SELECT * FROM user WHERE status=1 OR status=2;

     

    如上图,实际上并没有走索引,为什么?

    为什么where条件中使用or索引不起作用?where条件中使用or,索引就会失效,会造成全表扫描    是误区!!!

    一,要求使用的所有字段,都必须建立索引。

    二,数据量太少,制定执行计划时发现全表扫描比索引查找更快。

     

    5)负向条件查询不能使用索引,可以优化为in查询。

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

    负向条件不能命中缓存:

    EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;

    可以优化为in查询,但是前提是区分度要高,返回数据的比例在30%以内:

     

    8)范围条件查询可以命中索引。范围条件有:<、<=、>、>=、between等。

    status,age列分别创建索引

    user表索引详情:

    范围条件查询可以命中索引:

    EXPLAIN SELECT * FROM user WHERE status > 5;

    范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引:

    EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;

    如果是范围查询和等值查询同时存在,优先匹配等值查询列的索引:

    EXPLAIN SELECT * FROM user WHERE status>5 AND age = 28;

    8)数据库执行计算不会命中索引。

    EXPLAIN SELECT * FROM user WHERE age>24;

    EXPLAIN SELECT * FROM user WHERE age+1>24;

    计算逻辑应该尽量放到业务层处理,节省数据库的CPU的同时最大限度的命中索引。

    9)利用覆盖索引进行查询,避免回表。

    被查询的列,数据能从索引中取得,而不用通过行定位符row-locator再到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

    user表的索引详情:

    因为status字段是索引列,所以直接从索引中就可以获取值,不必回表查询:

    Using Index代表从索引中查询:

    EXPLAIN SELECT status FROM user where status=1;

    当查询其他列时,就需要回表查询,这也是为什么要避免SELECT*的原因之一:

    EXPLAIN SELECT * FROM user where status=1;

     

    10)建立索引的列,不允许为null。

    单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集,所以,请使用not null约束以及默认值。

    remark列建立索引:

    ALTER TABLE user ADD INDEX index_remark (remark);

    IS NULL可以命中索引:

    EXPLAIN SELECT * FROM user WHERE remark IS NULL;

    IS NOT NULL不能命中索引:

    EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

    虽然IS NULL可以命中索引,但是NULL本身就不是一种好的数据库设计,应该使用NOT NULL约束以及默认值。

    a. 更新十分频繁的字段上不宜建立索引:因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。

    b. 区分度不大的字段上不宜建立索引:类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤数据,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。

    c. 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。

    d. 多表关联时,要保证关联字段上一定有索引。

    e. 创建索引时避免以下错误观念:索引越多越好,认为一个查询就需要建一个索引;宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度;抵制唯一索引,认为业务的唯一性一律需要在应用层通过“先查后插”方式解决;过早优化,在不了解系统的情况下就开始优化。

    3. 小结

    对于自己编写的SQL查询语句,要尽量使用EXPLAIN命令分析一下,做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱,SQL能力是一个重要的指标。作为后端程序员,深以为然。

     

    参考文档

    1. MySQL索引优化看这篇文章就够了!

    2. 为什么where条件中使用or索引不起作用?

    展开全文
  • mysql索引优化

    2021-02-04 02:14:33
    索引类型从物理存储角度上,索引可以分为聚集索引和非聚集索引。1.聚集索引(Clustered Index)聚集索引决定数据在磁盘上的物理排序,一个表只能有一个聚集索引。2.非聚集索引(Non-clustered Index)非聚集索引并不决定...

    索引类型

    从物理存储角度上,索引可以分为聚集索引和非聚集索引。

    1.聚集索引(Clustered Index)

    聚集索引决定数据在磁盘上的物理排序,一个表只能有一个聚集索引。

    2.非聚集索引(Non-clustered Index)

    非聚集索引并不决定数据在磁盘上的物理排序,索引上只包含被建立索引的数据,以及一个行定位符 row-locator,这个行定位符,可以理解为一个聚集索引物理排序的指针,通过这个指针,可以找到行数据。

    从逻辑角度,索引可以分为以下几种。

    1.普通索引:最基本的索引,它没有任何限制。

    2.唯一索引:与普通索引类似,不同的就是索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

    3.主键索引:它是一种特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用primary key来约束

    4.联合索引(又叫复合索引):多个字段上建立的索引,能够加速复合查询条件的检索。

    5.全文索引:老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表,新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索,可以通过扩展 MySQL,添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

    MySQL索引优化规则

    1.前导模糊查询不能使用索引。

    例如下面 SQL 语句不能使用索引。

    select * from doc where title like '%XX'

    而非前导模糊查询则可以使用索引,如下面的 SQL 语句。

    select * from doc where title like 'XX%'

    2.union、in、or 都能够命中索引,建议使用 in。

    union:能够命中索引。示例代码如下:

    select * fromdoc where status=1

    union all

    select * fromdoc where status=2

    直接告诉 MySQL 怎么做,MySQL 耗费的 CPU 最少,但是一般不这么写 SQL。

    in:能够命中索引。示例代码如下:

    select * fromdoc where status in (1, 2)

    查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in

    or:新版的 MySQL 能够命中索引。示例代码如下:

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

    查询优化耗费的 CPU 比 in 多,不建议频繁用 or。

    3.负向条件查询不能使用索引,可以优化为 in 查询。

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

    例如下面代码:

    select * from doc where status != 1 and status != 2

    可以优化为 in 查询:

    select * from doc where status in (0,3,4)

    4.联合索引最左前缀原则(又叫最左侧查询)

    如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

    例如登录业务需求,代码如下。

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

    可以建立(login_name, passwd)的联合索引。

    因为业务上几乎没有 passwd 的单条件查询需求,而有很多 login_name 的单条件查询需求,所以可以建立(login_name, passwd)的联合索引,而不是(passwd, login_name)。

    建联合索引的时候,区分度最高的字段在最左边。

    如果建立了(a,b)联合索引,就不必再单独建立 a 索引。同理,如果建立了(a,b,c)联合索引,就不必再单独建立 a、(a,b) 索引。

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

    最左侧查询需求,并不是指 SQL 语句的 where 顺序要和联合索引一致。

    下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

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

    但还是建议 where 后的顺序和联合索引一致,养成好习惯。

    5.范围列可以用到索引(联合索引必须是最左前缀)。

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

    范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。

    假如有联合索引 (empno、title、fromdate),那么下面的 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'

    6.把计算放到业务层而不是数据库层。

    在字段上进行计算不能命中索引。

    例如下面的 SQL 语句。

    select * from doc where YEAR(create_time) <= '2016'

    即使 date 上建立了索引,也会全表扫描,可优化为值计算,如下:

    select * from doc where create_time <= '2016-01-01'

    把计算放到业务层。这样做不仅可以节省数据库的 CPU,还可以起到查询缓存优化效果。

    比如下面的 SQL 语句:

    select * from order where date < = CURDATE()

    可以优化为:

    select * from order where date < = '2018-01-2412:00:00'

    优化后的 SQL 释放了数据库的 CPU 多次调用,传入的 SQL 相同,才可以利用查询缓存。

    7.强制类型转换会全表扫描

    如果 phone 字段是 varchar 类型,则下面的 SQL 不能命中索引。

    select * from user where phone=13800001234

    可以优化为:

    select * from user where phone='13800001234'

    8.更新十分频繁、数据区分度不高的字段上不宜建立索引。

    更新会变更 B+ 树,更新频繁的字段建立索引会大大降低数据库性能。

    “性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。

    一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count() 来计算。*

    9.利用覆盖索引来进行查询操作,避免回表。

    被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

    例如登录业务需求,代码如下。

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

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

    10.如果有 order by、group by 的场景,请注意利用索引的有序性。

    order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。

    例如对于语句 where a=? and b=? order by c,可以建立联合索引(a,b,c)。

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

    11.使用短索引(又叫前缀索引)来优化索引。

    前缀索引,就是用列的前缀代替整个列作为索引 key,当前缀长度合适时,可以做到既使得前缀索引的区分度接近全列索引,同时因为索引 key 变短而减少了索引文件的大小和维护开销,可以使用 count(distinct left(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

    前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引(Covering Index,即当索引本身包含查询所需全部数据时,不再访问数据文件本身),很多时候没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。

    例如对于下面的 SQL 语句:

    SELEC * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

    我们可以建立索引:(firstname, lastname(4))。

    12.建立索引的列,不允许为 null。

    单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

    13.利用延迟关联或者子查询优化超多分页场景。

    MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。

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

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

    14.业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。

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

    15.超过三个表最好不要 join。

    需要 join 的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。

    16.如果明确知道只有一条结果返回,limit 1 能够提高效率。

    比如如下 SQL 语句:

    select * from user where login_name=?

    可以优化为:

    select * from user where login_name=? limit 1

    自己明确知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动。

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

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

    ref:使用普通的索引(Normal Index)。

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

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

    18.单表索引建议控制在5个以内。

    19.单索引字段数不允许超过5个。

    字段超过5个时,实际已经起不到有效过滤数据的作用了。

    20.创建索引时避免以下错误观念

    索引越多越好,认为一个查询就需要建一个索引。

    宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。

    抵制惟一索引,认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

    过早优化,在不了解系统的情况下就开始优化。

    问题详解

    1. 请问如下三条 SQL 该如何建立索引?

    where a=1and b=1

    where b=1

    where b=1 order by time desc

    MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引吗?

    回答:

    第一问:建议建立两个索引,即 idxab(a,b) 和 idxbtime(b,time)。

    第二问:MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,对于上面的第一条 SQL,如果建立索引为 idxba(b,a) 也是可以用到索引的,不过建议 where 后的字段顺序和联合索引保持一致,养成好习惯。

    2.假如有联合索引(empno、title、fromdate),下面的 SQL 是否可以用到索引,如果可以的话,会使用几个列?

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

    回答:可以使用索引,可以用到索引全部三个列,这个 SQL 看起来是用了两个范围查询,但作用于 empno 上的“between”实际上相当于“in”,也就是说 empno 实际是多值精确匹配,在 MySQL 中要谨慎地区分多值匹配和范围匹配,否则会对 MySQL 的行为产生困惑。

    3.既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?

    回答:不是,因为索引虽然加快了查询速度,但索引也是有代价的。索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担。另外,MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,另一种是数据的区分度比较低,可以使用 count(distinct(列名))/count(*) 来计算区分度。

    4.主键和聚集索引的关系?

    回答:在 MySQL 中,InnoDB 引擎表是(聚集)索引组织表(Clustered IndexOrganize Table),它会先按照主键进行聚集,如果没有定义主键,InnoDB 会试着使用唯一的非空索引来代替,如果没有这种索引,InnoDB 就会定义隐藏的主键然后在上面进行聚集。由此可见,在 InnoDB 表中,主键必然是聚集索引,而聚集索引则未必是主键。MyISAM 引擎表是堆组织表(Heap Organize Table),它没有聚集索引的概念。

    5.一个6亿的表 a,一个3亿的表 b,通过外键 tid 关联,如何最快的查询出满足条件的第50000到第50200中的这200条数据记录?

    回答:方法一:如果 a 表 tid 是自增长,并且是连续的,b表的id为索引。SQL语句如下。

    select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

    方法二:如果 a 表的 tid 不是连续的,那么就需要使用覆盖索引,tid 要么是主键,要么是辅助索引,b 表 id 也需要有索引。SQL语句如下。

    select * from b, (select tid from a limit 50000,200) a where b.id = a.tid;

    6.假如建立联合索引(a,b,c),下列语句是否可以使用索引,如果可以,使用了那几列?(考察联合索引最左前缀原则)

    where a= 3

    答:是,使用了 a 列。

    where a= 3 and b = 5

    答:是,使用了 a,b 列。

    where a = 3 and c = 4 and b = 5

    答:是,使用了 a,b,c 列。

    where b= 3

    答:否。

    where a= 3 and c = 4

    答:是,使用了 a 列

    where a = 3 and b > 10 andc = 7

    答:是,使用了 a,b 列。

    where a = 3 and b like 'xx%' andc = 7

    答:是,使用了 a,b 列。

    展开全文
  • Mysql索引优化方法

    2021-02-11 05:07:14
    本文主要和大家分享Mysql索引优化方法,希望能帮助到大家。索引是存储引擎用于快速找到记录的一种数据结构。尤其是当表的数据量越来越大的时候,正确的索引对查询性能的提升尤为明显。但在日常工作中,索引却常常被...

    本文主要和大家分享Mysql索引优化方法,希望能帮助到大家。索引是存储引擎用于快速找到记录的一种数据结构。尤其是当表的数据量越来越大的时候,正确的索引对查询性能的提升尤为明显。但在日常工作中,索引却常常被忽略,甚至被误解。本文将为大家简单介绍下Mysql索引优化的原理与注意事项。

    一、索引的类型

    1)B-Tree索引

    B-Tree索引是用的最多的索引类型了,而且大多数存储引擎都支持B-Tree索引。

    B-Tree本身是一种数据结构,其是为磁盘或其他直接存取的辅助设备而设计的一种平衡搜索树。Mysql中的B-Tree索引通常是B-Tree的变种B+Tree实现的。其结构如下:

    979e8a9ad07326b4e48339ae33d57eff.png

    B+Tree的特点是,数据都存储在叶子节点,并且每个叶子节点的数据都是按相同顺序(升序或降序)排列存储的,再者相邻的叶子节点都用指针连接在一点,这种结构非常适合于范围查找。

    B-Tree索引能够显著加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,而是从索引的根节点逐层往下进行搜索,这大大缩小了存储引擎扫描数据的范围,因此对查询速度的提升非常明显。

    2)Hash索引

    Hash索引,顾名思义,就是通过哈希表实现的索引。其特点是只有精确匹配索引的所有列才有效。对于每一行数据,存储引擎都会对所有索引列计算一个哈希码,Hash索引把哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

    在Mysql中,目前只有Memory引擎显式支持Hash索引,而且由于Hash索引不支持范围查找,也不支持排序,更不支持部分索引列匹配查找,所以Hash索引用的比较少。

    下文将着重介绍B-Tree索引的用法。

    为了下文叙述方便,我们将假设有一个user表,其字段如下:

    id:bigint类型,主键

    name:varchar类型

    age:int类型

    interest:varchar类型

    并且在name、age、interest上建立了一个联合索引index_1,索引顺序为(name,age,interest),这个索引顺序非常重要,后文将会提及。

    二、B-Tree索引的用法

    1)全值匹配

    全值匹配指的是和索引中所有列进行匹配,如对上述user表查询 where name='aaa' and age=20 and interest='篮球' 是可以使用到索引的所有列的。

    2)匹配最左前缀

    匹配最左前缀是指只使用到多列索引的左边若干列。如对上述user表查询 where name = 'aaa' 是可以使用到索引的,并且只使用到索引的第一列。

    3)匹配列前缀

    匹配列前缀是指只匹配某一列的开头部分,如对上述user表查询 where name like 'aaa%' 是可以使用到索引的,注意是匹配列的开头部分,如果查询的是 where name like '%aaa'就不能使用到索引了。

    4)匹配范围值

    如对上述user表查询 where name > 'aaa' and name < 'bbb' 也是可以使用到索引的。

    5)精确匹配某一列并范围匹配另外一列

    如对上述user表查询 where name='aaa' and age >10,可以使用到索引,并且使用到索引的前2列。

    三、B-Tree索引的限制

    1)如果不是按照索引的最左列开始查找,则无法使用索引。

    如对上述user表查询 where age=20则无法使用到索引,因为age不是索引列中的最左数据列。

    2)不能跳过索引中的列。

    如对上述user表查询 where name='aaa' and interest='足球',则只能使用到索引的第一列,因为where条件中没有包含age这一列。

    相关推荐:

    展开全文
  • mysql索引优化面试题

    2021-01-28 06:16:44
    曾经偷偷的面试了两个单位,都提到了Mysql优化问题,所以以后要多多学习数据库的优化知识了。建设数据库的优化大概主要就是索引优化了吧,因为我们不可能修改数据结构的情况下,提高数据库的查询效率似乎也只能...

    曾经偷偷的面试了两个单位,都提到了Mysql的优化问题,所以以后要多多学习数据库的优化知识了。建设数据库的优化大概主要就是索引的优化了吧,因为我们不可能修改数据结构的情况下,提高数据库的查询效率似乎也只能用索引了。当然这也是建立在你sql语句写的比较科学的情况下,如果你的sql语句本身就写的比较垃圾,神仙也救不了你!

    下边是在网上找到的一些资料,保留下来备用吧

    1,创建索引

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    2,复合索引

    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,

    age,

    salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    3,索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    4,使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    5,排序的索引问题

    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    6,like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    7,不要在列上进行运算

    select * from users where YEAR(adddate)<2007;

    将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

    select * from users where adddate

    8,不使用NOT IN和操作

    NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id<3来代替。

    展开全文
  • 将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容,下面一一展开(本文图片可点开放大)。一、MySQL索引基础首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨...
  • mysql索引优化助记口诀

    千次阅读 2020-12-25 18:03:00
    口诀 不等not null还有or,索引全中也无效;...= is null只会有两种情况:只有在命中了全部索引,也就是组合索引中的索引全用到了才会都有效并且是全部有效,否则就全无效。 组合索引中除了上面的特例外,
  • 谈谈Mysql索引优化不得不提防的坑逍遥子 码农沉思录前言在之前的文章《聊聊Mysql优化之索引优化》中,笔者简单介绍了Mysql索引优化的原理和一些使用场景,然而Mysql索引优化的内容还远远不止这些。在实际工作中,...
  • 使用此表结构来说一下索引优化和索引失效。大概总结分为一下几点全值匹配我最爱(怎么建怎么用)从三条语句中看出三条语句都用到了索引,而且type类型为ref,where后面的条件越来越多精度越来越高,精度越来越高带来的...
  • 补充:看到好多朋友后台留言说对SQL优化感兴趣,我又重新整理了下文章,将更多关于sql优化的知识分享出来,SQL优化也是面试中必问的知识点,一定要牢记优化套路。场景我用的数据库是MySQL5.6,下面简单的介绍下场景...
  • MYSQL索引优化

    2021-01-21 10:28:33
    1.查看sql的执行频率MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级...
  • 原标题:一次关于 Mysql 索引优化的思考转自:简书 作者:Fooisartwww.jianshu.com/p/efb7881f18b2查看系统性能监控,发现有十多条慢查询,决定将其优化。挑选其中一条典型Sql记录其优化历程。1.概述在下文的查询...
  • 1. explain是什么使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。2. explain能干吗可用于分析:表的读取顺序数据读取操作的操作...
  • InnoDB首先会使用主键创建一个主键B+树索引和数据文件,此外还会通过联合索引(b,c,d)生成一个索引树,同样是B+树的结构,只不过它的data部分存储的是联合索引所在行的主键值(上图叶子节点紫色背景部分),这里...
  • MySQL索引优化练习

    2021-04-24 21:20:22
    自己做了一个关于社区的项目,需要优化一下SQL查询,特此记录一下 文章目录练习一、练习二、练习三 练习一、 explain分析用户sql 访问类型type为ALL类型,查询效率最低 查看索引 此时 t_user 表中只有一个主键索引...
  • Mysql索引优化

    2021-01-19 22:55:07
    2. MySQL索引优化2.1. Explain执行计划分析2.2. 索引命中策略略分析2.3. 索引分析总结2.4. 数据库出现问题后如何死⽽而不不僵// 数据库卡顿情景解决思路SQL及索引:⾼质量的SQL,避免索引失效数据库表结构:范式,⾄...
  • MySQL索引优化面试题

    2021-01-12 12:26:04
    深入理解mysql索引优化+面试题 为什么like a% 可以用到索引 而%a是用不到索引的? 我有一个联合索引(a,b) 查询的时候条件用a可以使用索引b为什么用不到索引?
  • MySQL索引优化

    千次阅读 2021-02-20 20:48:18
    MySQL索引优化一 索引结构 Explain分析 索引最佳实践
  • 1、MySQL逻辑架构日常在CURD的过程中,都避免不了跟数据库打交道,大多数业务都离不开数据库表的设计和SQL的编写,那如何让你编写的SQL语句性能更优呢?先来整体看下MySQL逻辑架构图:MySQL整体逻辑架构图可以分为...
  • 范围查询可以通过一个索引的单个或者多个索引值的区间来检索数据记录,也适用于符合索引。下面就区间是怎么样从where子句中被提取出来的作出详细解释。使用单值索引的范围查询对于单值索引来说索引值的区间能够很好...
  • MySql索引优化注意

    2021-03-03 21:36:15
    设计MySql索引的时候有一下几点注意:1,创建索引对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,...
  • MySQL索引优化实战

    2021-01-28 20:04:23
    索引从物理上可以分为:聚集索引,非聚集索引从逻辑上可以分为:普通索引,唯一索引,主键索引,联合索引,全文索引索引优化策略不要在索引列上进行运算或使用函数在列上进行运算或使用函数会使索引失效,从而进行全...
  • 1 left joinEXPLAIN ...结论:在优化关联查询时,只有在被驱动表上建立索引才有效! left join 时,左侧的为驱动表,右侧为被驱动表2right join条件用于确定如何从左表搜索行, 右边一定都有EXPLAIN SELECT*FROM ...
  • Mysql索引 1.索引的原理、类型、结构 2.创建索引的注意事项、使用原则 3.如何排查和消除慢查询 高能预警:本章考点偏理论 二、什么是索引? 为什么需要索引? 1.索引是数据表中一个或者多个列进行排序的数据结构 2....
  • MySQL索引优化实践

    2021-03-12 14:37:26
    全职匹配 最左前缀原则 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描 ...= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引 .

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 223,285
精华内容 89,314
关键字:

mysql索引优化

mysql 订阅
友情链接: TripleDES_cipher-master.zip