如何优化sql,效率最高，SQL索引优化

以你之姓@ 2023-06-19 01:55 106阅读 0赞

**索引概念和作用**  
索引是一种使记录有序化的技术，它可以指定按某 列/某几列预先排序，从而大大提高查询速度(类似 于汉语词典中按照拼音或者笔画查找)。  
索引的主要作用是加快数据查找速度,提高数据库 的性能。

**MySQL索引类型**  
从物理存储角度上,索引可以分为聚集索引和非聚 集索引。  
1.聚集索引(Clustered Index)  
聚集索引决定数据在磁盘上的物理排序，-个表只 能有一个聚集索引。  
2.非聚集索引(Non-clustered Index)  
引上只包含被建立索引的数据，以及一个行定位符 row-locator, 这个行定位符，可以理解为一个聚集 索引物理排序的指针，通过这个指针，可以找到行 数据

**从逻辑角度，索引可以分为以下几种。**  
1.普通索引:最基本的索引，它没有任何限制。2.唯一索引:与普通索引类似，不同的就是索引 列的值必须唯一-, 但允许有空值。如果是组合 索引，则列值的组合必须唯-  
3.主键索引:它是一种特殊的唯一 索引，用于唯ll 一标识数据表中的某一条记录， 不允许有空 值，- -般用primary key来约束。主键和聚 集索引的关系详见"问题详解”中的第4题。4.联合索引(又叫复合索引) :多个字段上建立 的索引，能够加速复合查询条件的检索。5.全文索引:老版本 MySQL自带的全文索引 只能用于数据库引擎为MyISAM的数据表, 新版本MySQL 5.6的InnoDB支持全文索引 默认MySQL不支持中文全文检索，可以通过 扩展MySQL，添加中文全文检索或为中文内 容表提供对应的英文索引表的方式来支持

MySQL索引优化规则  
可以通过以下规则对MySQL索引进行优化。  
1.前导模糊查询不能使用索引。  
例如下面SQL语句不能使用索引。  
select \* fromdoc where title like '%XX’ 而非前导模糊查询则可以使用索引，如下面的SQL 语句。  
select \* fromdoc where title like ‘XX%’  
页面搜索严禁左模糊或者全模糊，如果需要可以用 搜索引擎来解决。  
2.union、 in、 or 都能够命中索引,建议使用in。  
示例代码如下:  
select \* fromdoc where status=1  
unionall  
select \* fromdoc where status=2  
直接告诉MySQL怎么做，MySQL 耗费的CPU最 少，但是-般不这么写sQL。  
●in:能够命中索引。.  
示例代码如下:  
select \* fromdoc where status in (1, 2) 查询优化耗费的CPU比union all多，但可以忽略 不计，一般情况下建议使用in  
●or:新版的MySQL能够命中索引。  
示例代码如下:  
select \* fromdoc where status= 1 or status =2 查询优化耗费的CPU比in多,不建议频繁用or。3.负向条件查询不能使用索引， 可以优化为in查 询  
负向条件有: !=、<>、not in、not exists、not like 等  
例如下面代码:  
select \* fromdoc where status != 1 and status != 2 可以优化为in查询:  
select \* fromdoc where status in (0,3,4)  
4.联合索引最左前缀原则(又叫最左侧查询)  
●如果在(a,b,c)三个字段上建立联合索引，那么 它能够加快a I(a,b) |(a,b,c)三组查询速度。  
例如登录业务需求，代码如下。  
selectuid,  
login\_ time from user where login. *name=? andpas 可以建立(login name, passwd)的联合索引。  
因为业务上几乎没有passwd的单条件查询需求， 而有很多login name的单条件查询需求，所以可  
(passwd, login. name)。  
●建联合索引的时候，区分度最高的字段在最左  
●如果建立了(a,b)联合索引，就不必再单独建立 a索引。同理，如果建立了(a,b,c)联合索引,  
就不必再单独建立a、(a,b) 索引。  
●存在非等号和等号混合判断条件时，在建索引 时，请把等号条件的列前置。如where a>?  
and b=?，那么即使a的区分度更高，也必须  
把b放在索引的最前列。  
●最左侧查询需求，并不是指SQL语句的 where顺序要和联合索引- -致。  
下面的SQL语句也可以命中(login. name, passwd) 这个联合索引。  
selectuid,  
login time from user where passwd=? andlogin. na me=2  
但还是建议where后的顺序和联合索引一致，养成 好习惯。  
5.范围列可以用到索引(联合索引必须是最左前  
●范围列可以用到索引(联合索引必须是最左前’ 缀) ,但是范围列后面的列无法用到索引，索 引最多用于- -个范围列，如果查询条件中有两| 个范围列则无法全用到索引。  
假如有联合索引(empno、title、 fromdate), 那么 下面的SQL中emp. no可以用到索引，而title和 from .date则使用不到索引。  
select \* fromemployees. titles where emp\_no< 10010’ and title='Senior Engineer and from date between ‘1986-01-01’ and ‘1986-12-31’  
6.把计算放到业务层而不是数据库层。  
附:如果使用  
select \* fromdoc where create* time<=YEAR ( '2016-03-12 12:12:12);  
依然会用到索引;  
●在字段上进行计算不能命中索引。  
例如下面的SQL语句。  
select \* fromdoc where YEAR(create\_ *time)<= ‘2016’  
即使date.上建立了索引，也会全表扫描，可优化为 值计算，如下:  
select \* fromdoc where create* \_time <= ‘201 6-01-  
●把计算放到业务层。  
这样做不仅可以节省数据库的CPU,还可以起到查 询缓存优化效果。  
比如下面的SQL语句:  
select \* fromorder where date < = CURDATE( 可以优化为:  
select \* fromorder where date < = 2018-01-2412:00:00’  
优化后的SQL释放了数据库的CPU多次调用，传 入的SQL相同，才可以利用查询缓存。  
7.强制类型转换会全表扫描  
如果phone字段是varchar类型，则下面的SQL不 能命中索引。  
select \* fromuser where phone=13800001234  
可以优化为:  
select \* fromuser where phone='13800001234’8.更新十分频繁、数据区分度不高的字段上不宜建 立索引。  
●更新会变更B+树，更新频繁的字段建立索引 会大大降低数据库性能  
“性别”这种区分度不大的属性，建立索引是没  
有什么意义的，不能有效过滤数据，性能与全 表扫描类似。

*  \-般区分度在80%以上的时候就可以建立索 引，区分度可以使用count(distinct(列 名))/count(*) 来计算。  
    9.利用覆盖索引来进行查询操作,避免回表。  
    数据能从索引中取得，而不用通过行  
    定位符row-locator再到row.上获取，即"被查询列 要被所建的索引覆盖"，这能够加速查询速度。  
    例如登录业务需求，代码如下。  
    selectuid,  
    login\_ time from user where login name=? andpas swd=?  
    可以建立(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 利用，如WHERE a210 ORDERBYb:索引(a,b)无法排序。  
    11.使用短索引(又叫前缀索引) 来优化索引。  
    前缀索引，就是用列的前缀代替整个列作为索| key,当前缀长度合适时，可以做到既使得前缀索引 的区分度接近全列索引，同时因为索引key变短而 减少了索引文件的大小和维护开销，可以使用 count(distinct left(列名,索引长度))/count(*)来计 算前缀索引的区分度。  
    前缀索引兼顾索引大小和查询速度，但是其缺点是 不能用于ORDER BY和GROUP BY操作，也不能用 于覆盖索引(Covering Index,即当索引本身包含 查询所需全部数据时，不再访问数据文件本身) 很多时候没必要对全字段建立索引，根据实际文本 区分度决定索引长度即可。  
    11.使用短索引(又叫前缀索引)来优化索引。  
    前缀索引,就是用列的前缀代替整个列作为索引  
    key,当前缀长度合适时，可以做到既使得前缀索引  
    的区分度接近全列索引，同时因为索引key变短而  
    减少了索引文件的大小和维护开销，可以使用  
    count(distinct left(列名,索引长度))/count(*)来计  
    算前缀索引的区分度。  
    前缀索引兼顾索引大小和查询速度，但是其缺点是 不能用于ORDER BY和GROUP BY操作，也不能用 于覆盖索引(Coverinq Index,即当索引本身包含 查询所需全部数据时，不再访问数据文件本身) 很多时候没必要对全字段建立索引，根据实际文本 很多时候没必要对全字段建立案引，根据实际文本 区分度决定索引长度即可。  
    例如对于下面的SQL语句:  
    SEL EC FROM employees.employees WHERE first\_ name='Eric’AND last\_ name=‘Anido’;  
    我们可以建立索引: (firstname, lastname())。.  
    12.建立索引的列，不允许为null。  
    单列索引不存null值，复合索引不存全为null的 值，如果列允许为null,可能会得到"不符合预期”的! 结果集，所以，请使用not null约束以及默认值。  
    13.利用延迟关联或者子查询优化超多分页场景。  
    MySQL并不是跳过offset 行，而是取offset+N 行，然后返回放弃前offset行,返回N行，那当 offset特别大的时候，效率就非常的低下，要么控 制返回的总页数，要么对超过特定阈值的页数进行 SOL改写。  
    selecta. fr  
    件limit100000,20 ) b where a.id=b.id  
    14.业务.上具有唯一特性的字段，即使是多个字段的! 组合，也必须建成唯-索引。  
    不要以为唯一索引影响了 insert 速度,这个速度损 耗可以忽略，但提高查找速度是明显的。另外，即 使在应用层做了非常完善的校验控制，只要没有唯 一索引， 根据墨菲定律，必然有脏数据产生。  
    15.超过三个表最好不要join。  
    需要join的字段，数据类型必须-致，多表关联查 询时，保证被关联的字段需要有索引o  
    16.如果明确知道只有一条结果返回, limit 1能够提\} 高效率。  
    比如如下SQL语句:  
    select \* fromuser where login\_name=? 可以优化为:  
    select \* fromuser 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(,time)。  
    第二问: MySQL的查询优化器会自动调整where 子句的条件顺序以使用适合的索引，对于上面的第-条SQL,如果建立索引为idxba(b,a)也是可以用 到索引的，不过建议where后的字段顺序和联合索 引保持一致，养成好习惯。  
    2.假如有联合索引(empno、title、 fromdate), 下面| 的SQL是否可以用到索引，如果可以的话，会使用 几个列?  
    select \* fromemployees titles where emp \_no betw een 10001’ and’1 0010’ andtitle=‘Senior Enaineer’ and from date between 1986-01- Engieer andtrom-  
    01 and ‘1986-12-31’  
    回答:可以使用索引，可以用到索引全部三个列, 这个SQL看起来是用了两个范围查询，但作用于  
    empno实际是多值精确匹配，在MySQL中要谨慎 地区分多值匹配和范围匹配，否则会对MySQL的 行为产生困惑。  
    3.既然索引可以加快查询速度,那么是不是只要是 查询语句需要，就建上索引?  
    回答:不是，因为索引虽然加快了查询速度,但索 引也是有代价的。索引文件本身要消耗存储空间， 同时索引会加重插入、删除和修改记录时的负担。 另外，MySQL在运行时也要消耗资源维护索引，因 此索引并不是越多越好。一般两种情况下不建议建 索引。第-种情况是表记录比较少，例如- -两千条 甚至只有几百条记录的表，没必要建索引，另-种 是数据的区分度比较低，可以使用  
    count(distinct(列名))/count(*) 来计算区分度。  
    4.主键和聚集索引的关系?  
    回答:在MySQL中，InnoDB引擎表是(聚集)索  
    先按照主键进行聚集，如果没有定义主键，InnoDB 会试着使用唯一-的非空索引来代替， 如果没有这种 索引，InnoDB 就会定义隐藏的主键然后在上面进行 聚集。由此可见，在InnoDB表中,主键必然是聚 集索引，而聚集索引则未必是主键。MyISAM引擎 表是堆组织表(Heap Organize Table)，它没有聚集’ 索引的概念。  
    5.- 个6亿的表a, - -个3亿的表b,通过外键tid关 联，如何最快的查询出满足条件的第10000到第50200中的这200条数据记录?  
    回答:方法一:如果a表tid是自增长，并且是连 续的，b表的id为索引。SQL语句如下。  
    select \* froma,b where a.tid =  
    b.id and a.tid> 500000 limit200;  
    方法二:如果a表的tid不是连续的，那么就需要 使用覆盖索引，tid 要么是主键，要么是辅助索引， b表id也需要有索引。SQL语句如下。  
    select \* fromb, (select tid from a limit 50000,200) awhere b.id= a.tid;  
    6.假如建立联合索引(a,b,c),下列语句是否可以使 用索引，如果可以，使用了那几列? (考察联合索 店  
    引最左前缀原则)  
    where a= 3  
    答:是，使用了a列。  
    where a= 3and b=5  
    答:是，使用了a, b列。  
    wherea= 3 andc=4 and b=5  
    答:是，使用了a, b, c列。  
    where b= 3  
    答:否。  
    wherea=3andc=4  
    答:是，使用了a列。  
    wherea= 3 and b> 10 andc= 7  
    答:是，使用了a, b列。  
    where a= 3 and b like ‘Xx%’ andc = 7 答:是,使用了a, b列。  
    7.文章表的表结构如下:  
    CREATE TABL EIF NOT EXISTS article\* (id"int(10) unsigned NOTNULL AUTO *INCREMENT,  
    、author. jid’ int(10) unsignedNOT NULL,  
    、category jid int(10) unsigned NOT NULL,  
    ‘views’ int(10) unsignedNOT NULL,  
    、comments int(10) unsignedNOT NULL,  
    "tite’ varbinary(255) NOT NULL,  
    、content text NOTNULL,  
    PRIMARY KEY (id)  
    下面语句应该如何建立索引?  
    selectauthor. jid, title, content from article’ wherecategory\_id = 1 and comments> 1 order byviews desc limit 1;  
    回答:  
    没有联合索引时，explain显示， 如下图所示:  
    idxcategoryidcommentsviews(category* id,comme nts, views)联合索引时，explain显示， 如下图所  
    示  
    创建idxcategoryidviews(categoryid,iews)联合索 引，explain 显示，如下图所示:  
    由此可见，可以创建  
    idxcategoryidviews(categoryid,views)联合索引。  
    结语  
    对于大数据量的业务，应该时刻考虑到性能，本文 只是从使用层面提供了- -些优化的思路，由于业务 和数据的复杂性，需要具体问题具体分析，对于数 据量比较大的业务，最好自己使用explain具体分 析一下、同时、知其然知其所以然，有时间大家可  
    以看看索引的底层实现原理。 欢迎各路大神拍砖， 以看看系5  
    共同学习

Explain  
6 mysql  
1)、id列数字越大越先执行,如果说数字-样大,那么就 从上往下依次执行，id列为null的就表是这是一一个结果集， 不需要使用它来进行查询。  
2)、select type列常见的有:  
A: simple:表示不需要nion操作或者不包含子查询的简 单select查询。 有连接查询时，外层的查询为simplnple, 且 有一个  
Selet, 1最个需要union操作或者含有子查询的 B: primary: 一个需要u select, 位于最外层的单位查询的select. type即为  
C: union: union连接的两个select查询,第- -个查询是 dervied派生表， 除了第- -个表外,第二个以后的表 select *type都是union  
D: dependent union;与union- 样，出现在union 或union al语句中，但是这个查询要受到外部查询的影响 E: union result:包含union的结果集，  
语句中，因为它不需要参与的，果集，在nion和ounion al  
i subquery:除了from字句中包含的子查询外，其他地方  
的子查询都可能是subquery  
G: dependent subquery:与dependent union类似，表示 这个subquery的查询要受到外部表查询的影响 H: derived: from字句中出现的子查询，也叫做派生表， 其他数据库中可能叫做内联视图或嵌套select  
3)、table  
是别名，如秦不，如果查询使用了别名，那么这里显示的. 显示的查询表名，如果查询使用 死梨名，如果不涉及对数据表的操作，那么这显示为nul,  
示为尖括号括起来的就表示这个是临时 表，后边的N就是执行计划中的id,表示结果来自于这个查  
询产生。如果是尖括号括起来的*