sql索引优化

㈠ sql优化及原理详解，五分钟读懂sql优化

在我而言这算是一个复习，然后总结出来给大家当个教材吧。
我也是看视频总结出来的笔记，所以说的都很简单和浅薄。有不全面或者偏颇的地方欢迎指出，共同交流进步哈。（因为我当时是看视频总结的笔记，所以可能说的比较杂乱，我尽量写的分明一点，在最后会附上笔记，忽略我字丑）

索引是什么呢？它相当于字典的目录。
索引：index是帮助mysql高效获取数据的数据结构，索引是数据结构（树，默认是B树），hash等。
索引的弊端: 事物都是两面的，有利必然有弊。

索引的优势： 索引有这么多弊端我们还使用的原因是因为优大于劣。

索引的分类：

举个小例子让大家更理解复合索引：如果我把一个表中name，age这两个列做成复合索引（注意顺序很重要）。那么我们形成的目录一级目录是name，二级目录是age。在name相同时才会age再形成目录。因为它本身的排序不是像目录一样一行一行列出来的，所以我们尽量用目录来想象它比较好理解。下面是图解：

有几点注意的事项：

这里说一下，上面说的方法都是原生的sql，比如我现在习惯使用navicat，所以可以直接操作。。爽的不行。

然后删除查询也都是直接可视的，方便的不得了。就不多说了。

mysql做例子，还有个引擎是可以优化的。mysql中引擎分两种：

sql优化等级：

上面说的这些等级在explain中可以看到。

单表优化常用方法：

多表优化常用方法：

因为上面也提到了b树，所以还是单独聊聊吧。其实我也不是很理解。只能说一个浅显的认识而已。这里也就是简单的说一下。
首先，B树不仅可以二叉，还可以三叉，多叉。而只要大于二叉的都叫做BTree。
据说三层BTree可以存放上百万数据。
BTree一般都指B+树，数据全部存放在叶节点中。（这里简单的一个三叉树图）

好了，就写到这里吧，希望日后算法的知识会的更多以后能把B树这个坑填完~~~然后有不同意见或者自己理解的可以留言或者私聊。
全文手打，如果你觉得对你有帮助麻烦点个赞点个关注啥的~~

㈡ 如何在 SQL 数据库优化 索引，SQL索引优化

1、主键就是聚集索引
2、只要建立索引就能显着提高查询速度
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度

(四)其他书上没有的索引使用经验总结
1、用聚合索引比用不是聚合索引的主键速度快
2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下
3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
4 、日期列不会因为有分秒的输入而减慢查询速度

(五)其他注意事项
1. 不要索引常用的小型表
2. 不要把社会保障号码(SSN)或身份证号码(ID)选作键
3. 不要用用户的键
4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)
5. 使用系统生成的主键

二、改善SQL语句
1、Like语句是否属于SARG取决于所使用的通配符的类型
2、or 会引起全表扫描
3、非操作符、函数引起的不满足SARG形式的语句
4、IN 的作用相当与OR
5、尽量少用NOT
6、exists 和 in 的执行效率是一样的
7、用函数charindex()和前面加通配符%的LIKE执行效率一样
8、union并不绝对比or的执行效率高
9、字段提取要按照“需多少、提多少”的原则，避免“select *”
10、count(*)不比count(字段)慢
11、order by按聚集索引列排序效率最高
12、高效的TOP

㈢ sql调优的几种方式

你好，
SQL优化的一些方法
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
5.in 和 not in 也要慎用，否则会导致全表扫描，
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

㈣ MySQL性能优化之索引设计

上一篇给小伙伴们讲了关于SQL查询性能优化的相关技巧，一个好的查询SQL离不开合理的索引设计。这篇小二就来唠一唠怎么合理的设计一个索引来优化我们的查询速度，要是有不合理的地方...嗯..

当然啦，开个玩笑，欢迎小伙伴们指正！

通常情况下，字段类型的选择是需要根据业务来判断的，通常需要遵循以下几点。

下列各种类型表格内容来自菜鸟教程，权当备忘。

优化建议：

注意： INT(2)设置的为显示宽度，而不是整数的长度，需要配合 ZEROFILL 使用 。

例如 id 设置为 TINYINT(2) UNSIGNED ，表示无符号，可以存储的最大数值为255，其中 TINYINT(2) 没有配合 ZEROFILL 实际没有任何意义，例如插入数字200，长度虽然超过了两位，但是这个时候是可以插入成功的，查询结果同样为200；插入数字5时，同样查询结果为5。

而 TINYINT(2) 配合 ZEROFILL 后，当插入数字5时，实际存储的还是5，不过在查询是MySQL会在前面补上一个0，即查询出来的实际为 05 。

优化建议：

优化建议：

通常来说，考虑好表中每个字段应该使用什么类型和长度，建完表需要做的事情不是马上建立索引，而是先把相关主体业务开发完毕，然后把涉及该表的SQL都拿出来分析之后再建立索引。

尽量少建立单值索引( 唯一索引除外 )，应当设计一个或者两三个联合索引，让每一个联合索引都尽量去包含SQL语句中的 where、order by、group by 的字段，同时确保联合索引的字段顺序尽量满足SQL查询的最左前缀原则。

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，性别一共有三个值：男、女、保密，那么该字段的基数就是3。

如果对这种小基数字段建立索引的话，因为索引树中只有男、女、保密三个值，根本没法进行快速的二分查找，同时还需要回表查询，还不如全表扫描嘞。

一般建立索引，尽量使用那些基数比较大的字段，那么才能发挥出B+树快速二分查找的优势来。

在 where 和 order by 出现索引设计冲突时，是优先针对where去设计索引？还是优先针对order by设计索引？

通常情况下都是优先针对 where 来设计索引，因为通常情况下都是先 where 条件使用索引快速筛选出来符合条件的数据，然后对进行筛选出来的数据进行排序和分组，而 where 条件快速筛选出来的的数据往往不会很多。

对生产实际运行过程中，或者测试环境大数据量测试过程中发现的慢查询SQL进行特定的索引优化、代码优化等策略。

终于轮到实战了，小二最喜欢实战了。

写到这里不得不吐槽一下，这个金三银四的跳槽季节，年前提离职了，结果离职还没办完就封村整整两个礼拜了，呜呜呜...

上节小二就提到会有个很有意思的小案例，那么在疫情当下，门都出不去的日子，感觉这个例子更有意思了，咱们来讨论一下各种社交平台怎么做的用户信息搜索呢。

社交平台有一个小伙伴们都喜欢的功能，搜索好友信息，比如小二熟练的点开省份...城市..性别..年龄..身高...

咳咳咳...小二怎么可能干这种事情，小二的心里只有代码，嗯...没错，就是这样。

这个就可以说是对于用户信息的查询筛选了，通常这种表都是非常大数据量的，在不考虑分库分表的情况下，怎么通过索引配合SQL来优化呢？

通常我们在编写SQL是会写出类似如下的SQL来执行，有 where、order by、limit 等条件来查询。

那么接下来小二一个一个慢慢增加字段来分析分析，怎么根据业务场景来设计索引。

针对这种情况，很简单，设计一个联合索引 (provice, city, sex) 就完事了。

那么这时候有小伙伴就会说了，很简单啊，范围字段放最后咱还是知道的，联合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，这么干没毛病，但是小伙伴们有没有想过有些人万一既喜欢帅哥又喜欢美女，别想歪了哈...，挺多小姐姐就既喜欢帅哥又喜欢美女的。

那么这个时候小姐姐就不搜索性别了，那么这个时候联合索引只能用到前两个字段了，那么不符合咱们的专业标准啊，咋办呢？这时候还是有办法的，咱们只需要动动小脑袋改改SQL就行了，在没有选择性别时判断一下，改成下面这样就可以了。

咋办嘞，同样往联合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。

针对这种多个范围查询的话，为了比较好的利用索引，在业务允许的情况下可以使用固定范围，然后数据库字段存储范围标识就可以了，这样就转化为了等值匹配，就可以很好地利用索引了。

例如最后登录时间字段不记录最后登录时间，而是记录设置字段 is_login_within_seven_days 在7天内有登录则为1，否则为0，最后索引设计成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那么根据场景最后设计出来的这个索引可能已经可以覆盖大部分的查询流量了，那么如果还有其他一部分热度比较高的查询怎么办呢，办法也很简单啊，再加一两个索引即可。

例如通常会查询这个城市比较受欢迎(评分：score)的小姐姐，这时候添加一个联合索引 (provice, city, sex, score) 那么就可以了。

可以看出，索引时必须结合场景来设计的，思路就是尽量用不超过3个复杂的联合索引来抗住大部分的80%以上的常用查询流量，然后再用一两个二级索引来抗下一些非常用查询流量。

以上就是小二要给大家分享的索引设计，如果能动动你发财的小手给小二点个免费的赞就更好啦~

下篇小二就来讲讲MySQL事务和锁机制。

㈤ SQL优化（二）

SQL优化一： sql优化（一）

上片文章已经详细介绍了explain各个字段的含义，以及什么情况应该建立索引，什么情况不需要建立索引以及sql语句性能的判断依据，接下来我介绍下如何合理的建立索引。

sql语句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

分析：首先我们根据where后面的条件建立符合索引，然后根据order by后面的字段建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）数据列建立复合索引，但由于comments是一个范围，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，又因为comments字段在复合索引里处于中间位置，而comments>1是一个条件（是一个范围值），在复合索引的一个范围值的数据列后面的索引全部失效，mysql无法利用索引再对后面的views部分进行检索，也就是说views无法按照索引排序，所以explain下此sql语句，type为range，extra使用的是Using filesort，这是比较糟糕的。所以我们放弃comments这个范围字段，建立索引idx_article_cv，即以（category_id，views）数据列建立复合索引，explain 此sql，type变成了ref，extra的using filesort也变成了using index，这就变得好多了。

索引：idx_article_cv，即以（category_id，views）数据列建立复合索引

前段时间做了一个销售精细化项目，是公司crm项目的一个大模块，大致就是为销售人员制定指标，实现销售目标从区域到团到业务员到客户，实时跟踪业务员所负责客户的下单量的情况。这就存在许多关联关系，区域-团，团-业务员，业务员-客户，这使得sql常常需要关联多张表。

sql语句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左连接，左边的表一定是全表查询，所以要建立右边表对应关联字段的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)字段建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）字段建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)字段建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)字段建立索引。建立索引后，sql查询速度明显快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、尽可能减少join语句中的NestedLoop的循环次数，永远用小结果集驱动大结果集

2、优先优化NestedLoop的内层循环

3、保证join语句总被驱动表上的join字段已经被索引

4、当无法保证被驱动表join条件字段被索引，且内存资源充足的前提下，不要太吝啬joinBuffer的设置

1、全值匹配我最爱

2、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则，指的是查询从索引的最左前列开始并且不跳过索引中的列

3、并在索引列上做任何操作（计算、函数、自动or手动类型转换），这些会导致索引失效而转向全表扫描

4、存储引擎不能使用索引中范围条件右边的列，范围之后的索引全失效

5、尽量使用覆盖索引（之访问索引的查询（索引列和查询的列一致）），减少select *

6、mysql在使用不等于（！=、>、<）的时候无法使用索引会导致全表扫描。

7、is null、is not null也无法使用索引。

8、like以通配符开头（"%abc.."），mysql索引失效也会变成全表扫描的操作。

9、字符串不加单引号也会引起索引失效

10、少用or，用它来连接时会索引失效。

1、对于单值索引，尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

3、在选择组合索引的时候，尽量选择尽可能包含当前query中的where字句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

全值匹配我最爱，最左前缀要遵守

带头大哥不能死，中间兄弟不能断

索引列上少计算，范围之后全失效

like百分写最右，覆盖索引不写里

不等空值还有or，索引失效要少用

var引号不可丢，sql高级也不难

㈥ sql优化的N种方法

1.SQL语句中IN包含的值不应过多:
例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了； 实测速度差距不是很大.

2.SELECT语句务必指明字段名称:
禁止用 * 来查询 ,禁止用 * 来查询 ,禁止用 * 来查询 , 查找哪个字段,就写具体的字段.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查询一条数据的时候，使用limit 1
【这个很有用】

4.避免在where子句中对字段进行null值判断:
【实测：null值的判断依然走了索引】
explain select uid from user_test WHERE phone is null;

5.避免在where子句中对字段进行表达式操作:

6.对于联合索引来说，要遵守最左前缀法则:
例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败

注意: id, name,sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.

前三条sql都能命中索引,中间两条由于不符合最左匹配原则,索引失效.

最后一条sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

7.尽量使用inner join，避免left join：
如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
【实测：不是很准确，具体用explain测试】

8.注意范围查询语句:
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

解决办法: 业务允许的情况下,使用 >= 或者<= 这样不影响索引的使用.

explain select * from user_test where uid=10 and name='张三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='张三' and phone='13527748096';

9.不建议使用%前缀模糊查询:
例如 : LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 来连接条件，如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描
解决办法: 将or连接的双方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='张三';

11.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。(此处存在疑点,我本人测试的时候,发现索引还是能使用到)

12.字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效

㈦ mysql有几种索引类型使用索引时都有那些地方要注意sql优化原则

MySQL索引类型包括：
一、普通索引
这是最基本的索引，它没有任何限制。有以下几种创建方式：
1.创建索引
代码如下:

CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

2.修改表结构

代码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定。
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- 删除索引的语法：
DROP INDEX [indexName] ON mytable;

二、唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。
四、组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：

代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取MySQL的效率，就要考虑建立组合索引。
二：使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串行进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算

select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

select * from users where adddate<‘2007-01-01';

6.不使用NOT IN和<>操作。

三：sql优化原则
常见的简化规则如下：
1.不要有超过5个以上的表连接（JOIN）
2.考虑使用临时表或表变量存放中间结果。
3.少用子查询
4.视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
5.连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。
6.最好是把连接拆开成较小的几个部分逐个顺序执行。
7.优先执行那些能够大量减少结果的连接。
8.拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。

如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。

㈧ mysql有几种索引类型使用索引时都有那些地方要注意sql优化原则是什么

mysql的索引类型及使用索引时的注意事项有：

一、普通索引。这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1、创建索引

代码如下:

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

2、修改表结构

代码如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- 删除索引的语法：

DROP INDEX [indexName] ON mytable;

二、唯一索引。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

代码如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主键索引。它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

代码如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

四、组合索引。为了形象地对比单列索引和组合索引，为表添加多个字段：

代码如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

代码如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

[code]
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin"

㈨ SQL优化万能公式：5 大步骤 + 10 个案例

在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的SQL就是整个系统性能的瓶颈。

1、通过慢查日志等定位那些执行效率较低的SQL语句

2、explain 分析SQL的执行计划

type由上至下，效率越来越高

Extra

3、show profile 分析

了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”

4、trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

5、确定问题并采用相应的措施

案例1、最左匹配

索引

SQL语句

查询匹配从左往右匹配，要使用order_no走索引，必须查询条件携带shop_id或者索引( shop_id , order_no )调换前后顺序

案例2、隐式转换

索引

SQL语句

隐式转换相当于在索引上做运算，会让索引失效。mobile是字符类型，使用了数字，应该使用字符串匹配，否则MySQL会用到隐式替换，导致索引失效。

案例3、大分页

索引

SQL语句

对于大分页的场景，可以优先让产品优化需求，如果没有优化的，有如下两种优化方式， 一种是把上一次的最后一条数据，也即上面的c传过来，然后做“c < xxx”处理，但是这种一般需要改接口协议，并不一定可行。另一种是采用延迟关联的方式进行处理，减少SQL回表，但是要记得索引需要完全覆盖才有效果，SQL改动如下

案例4、in + order by

索引

SQL语句

in查询在MySQL底层是通过n*m的方式去搜索，类似union，但是效率比union高。in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_pe_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

处理方式，可以( order_status , created_at )互换前后顺序，并且调整SQL为延迟关联。

案例5、范围查询阻断，后续字段不能走索引

索引

SQL语句

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

案例8、复杂查询

如果是统计某些数据，可能改用数仓进行解决；如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

案例9、asc和desc混用

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

㈩ 数据库基础详解：存储过程、视图、游标、SQL语句优化以及索引

写在文章前：本系列文章用于博主自己归纳复习一些基础知识，同时也分享给可能需要的人，因为水平有限，肯定存在诸多不足以及技术性错误，请大佬们及时指正。

存储过程 是事先经过编译并存储在数据库中的一段SQL语句的集合。想要实现相应的功能时，只需要调用这个存储过程就行了（类似于函数，输入具有输出参数）。

优点 ：

缺点 ：

Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器（包含一个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1。

Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除。不能回滚，不会触发触发器。

触发器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）来触发运行的操作（不能被直接调用，不能接收参数）。在数据库里以独立的对象存储，用于保证数据完整性（比如可以检验或转换数据）。

约束（Constraint）类型：

从数据库的基本表中通过查询选取出来的数据组成的虚拟表（数据库中只存放视图的定义，而不存放视图的数据）。可以对其进行增/删/改/查等操作。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）。

可以跟基本表一样，进行增删改查操作( 增删改操作有条件限制，一般视图只允许查询操作 )，对视图的增删改也会影响原表的数据。 它就像一个窗口，透过它可以看到数据库中自己感兴趣的数据并且操作它们。 好处：

用于定位在查询返回的结果集的特定行，以对特定行进行操作。使用游标可以方便地对结果集进行移动遍历，根据需要滚动或对浏览/修改任意行中的数据。主要用于交互式应用。它是一段私有的SQL工作区，也就是一段内存区域，用于暂时存放受SQL语句影响的数据，简单来说，就是将受影响的数据暂时放到了一个内存区域的虚表当中，这个虚表就是游标。

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。即游标用来逐行读取结果集。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。通俗来说，游标就是能在sql的查询结果中，显示某一行（或某多行）数据，其查询的结果不是数据表，而是已经查询出来的结果集。

简单来说：游标就是在查询出的结果集中进行选择性操作的工具。

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询。减少锁竞争。

索引是对数据库表中一列或多列的值进行排序的一种结构（说明是在列上建立的），使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止。有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

从应用上分， 主键索引（聚集） ， 唯一索引（聚集/非聚集） ， 普通索引 ， 组合索引 ， 单列索引和全文索引