mysql索引的使用场景 mysql索引的实现原理

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

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

mysql索引的使用场景 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"

MySql是怎么使用的索引，在哪些情况下会使用

MySql为以下这些操作使用索引：

1、为了快速查找匹配WHERE条件的行。

2、为了从考虑的条件中消除行。如果在多个索引之间选择一个，正常情况下，MySql使用找到行的小数量的那个索引。

3、如果表有一个multiple-column索引，任何一个索引的左前缀可以通过使用优化器来查找行。例如，如果你有一个 three-column索引在(col1, col2, col3)，你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。

MySQL的索引是什么？怎么优化？

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方：

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

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事务和锁机制。

MySql索引之哪些情况适合创建索引

说明：不要以为索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在

数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者

使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多

个，那么可以在这些列上建立 组合索引 。

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就

能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或

删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更

新不需要对索引进行维护。

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行

SQL 语句：

运行结果（600637 条记录，运行时间 0.683s ）：

如果我们对 student_id 创建索引，再执行 SQL 语句：

运行结果（600637 条记录，运行时间 0.010s ）：

你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因

为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增

长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，

没有 WHERE 条件过滤是非常可怕的。

后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在

student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：

运行结果（1 条数据，运行时间 0.189s ）：

这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s 。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字

段的散列度(选择性)会降低。 怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

通过不同长度去计算，与全表的选择性对比：

公式：

例如：

引申另一个问题：索引列前缀对排序的影响

拓展：Alibaba《Ja开发手册》

【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本

区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达

90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count()的区分度来确定。

这样也可以较少的建立一些索引。同时，由于"左前缀原则"，可以增加联合索引的使用率。

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先

访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

举例2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。

学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代

表男性。

如果我们要筛选出这个学生表中的男性，可以使用：

运行结果（10 条数据，运行时间 0.696s ）：

结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字

符串等。

① 冗余索引

举例：建表语句如下

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一

个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有

什么好处。

② 重复索引

另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样：

我们看到，col1 既是主键、又给它定义为一个索引，还给它定义了一个普通索引，可是主键本身就

会生成聚簇索引，所以定义的索引和普通索引是重复的，这种情况要避免。

欢迎共同进步：

QQ群：1007576722

MySQL索引机制(详细+原理+解析)

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

集一个索引包含多个列（左前缀匹配原则）

索引列的值必须，但允许有空值

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

设定主键后数据会自动建立索引，InnoDB为聚簇索引

即一个索引只包含单个列，一个表可以有多个单列索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表操作，减少了I/O效率

查看索引

列名解析：

删除索引

查看：

删除前:

删除后：

普通的索引，没有什么介绍

查看:（注意和前缀索引Sub_part的区别）

当索引的列是unique的时候，会生成索引，索引关于null有下列两种情况

SQLSERVER 下的索引的列，允许null值，但多允许有一个空值

MYSQL下的索引的列，允许null值，并且允许多个空值

查看:

会建立两个索引，一个非聚簇索引，一个是索引

结果:

可以插入两个空值（明人不说暗话，我喜欢MySQL）

一方面，它不会索引所有字段所有字符，会减小索引树的大小.

另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

查看：

查看：

复合索引的左前缀匹配原则 ：

对于复合索引，查询在一定条件才会使用该索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% 10%=1w。

在模糊搜索中很有效，搜索全文中的某一个字段，可以参考这篇博文

:

我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。

查看:

我们插入进去的时候，数据的id都是乱序的，为什么这里后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构 ，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的操作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

InnoDB中主键索引一定是聚簇索引，聚簇索引一定是主键索引。

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

Q:主键索引作为聚簇索引需要注意什么

在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用

多列索引是在表的多个字段上创建的索引，满足左前缀匹配原则，索引才会被使用

查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

MySQL联表查询的索引使用

一共3张表 knowledge , knowledge_question , knowledge_answer ，数据在 6000~10000 之间。

执行的语句：

执行时间约 10分钟 ，查看执行如下：

全部都是全表扫描，根据MySQL联表查询的算法 Nested-Loop Join ，MySQL查询的结果集是3张表的笛卡尔积，所以效率特别低。

耗时变成 20毫秒

给Where条件建立索引，并不一定会使用。

比如：在表 knowledge 的字段 update 上建立索引 idx_time ：

结果执行上来看，并没有使用索引 idx_time 。

如果where条件从 k.update_time>'2019-01-03 12:00:00' 修改为 k.update_time='2019-01-03 12:00:00' （从 > 变成 = ）

则会使用索引 idx_time

在建立索引的时候，会遇到 Table Metadata Lock 的问题，可以先 show processlist ，找到占用表锁的连接，然后 kill 。

MySQL简单介绍——换个角度认识MySQL

1、InnoDB存储引擎

Mysql版本>=5.5 默认的存储引擎，MySQL使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。

存储格式 : 数据，索引集中存储，存储于同一个表空间文件中。

InnoDB的行锁模式及其加锁方法： InnoDB中有以下两种类型的行锁：共享锁（读锁： 允许事务对一条行数据进行读取）和 互斥锁（写锁： 允许事务对一条行数据进行删除或更新）， 对于update，insert，delete语句，InnoDB会自动给设计的数据集加互斥锁，对于普通的select语句，InnoDB不会加任何锁。

InnoDB行锁的实现方式： InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

（1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录。

（2）Mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果使用相同的索引键，是会出现冲突的。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，但都是通过行锁来对数据加锁。

优点：

1、支持事务处理、ACID事务特性；

2、实现了SQL标准的四种隔离级别（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（Isolation ）和持续性（Durability ））；

3、支持行级锁和外键约束；

4、可以利用事务日志进行数据恢复。

5、锁级别为行锁，行锁优点是适用于高并发的频繁表修改，高并发是性能优于 MyISAM。缺点是系统消耗较大。

6、索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存。

缺点：

因为它没有保存表的行数，当使用COUNT统计时会扫描全表。

使用场景：

(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

2、 MyISAM存储引擎

MySQL<= 5.5 MySQL默认的存储引擎。ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。擅长与处理，高速读与写。

功能：

（1）支持数据压缩存储,但压缩后的表变成了只读表，不可写；如果需要更新数据，则需要先解压后更新。

（2）支持表级锁定，不支持高并发；

（3）支持并发插入。写操作中的插入操作，不会阻塞读操作（其他操作）；

优点：

1.高性能读取；

2.因为它保存了表的行数，当使用COUNT统计时不会扫描全表；

缺点：

1、锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。

2、此引擎不支持事务，也不支持外键。

3、INSERT和UPDATE操作需要锁定整个表；

使用场景：

(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB和MyISAM一些细节上的别：

1、InnoDB不支持FULLTEXT类型的索引，MySQL5.6之后已经支持（实验性）。

2、InnoDB中不保存表的 具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含 where条件时，两种表的操作是一样的。

3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

6、另外，InnoDB表的行锁也不是的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

1．索引概述

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

2．索引种类

从索引的定义方式和用途中来看：主键索引，索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的异：对索引关键字的要求不同。

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。

索引,unique index：要求关键字不能重复。同时增加约束。

主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。

全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

PS：这里主键索引和索引的区别在于：主键索引不能为空值，索引允许空值；主键索引在一张表内只能创建一个，索引可以创建多个。主键索引肯定是索引，但索引不一定是主键索引。

3.索引原则

如果索引不遵循使用原则，则可能导致索引无效。

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。否则索引不会用到索引, 例如这条sql就不会用到索引:select from A where id+1=10;

（2）左原则

Like：匹配模式必须要左边确定不能以通配符开头。例如:select from A where name like '%小明%' ，不会用到索引，而select from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果业务上需要用到'%小明%'这种方式,有两种方法:1.可以考虑全文索引,但mysql的全文索引不支持中文；2.只查询索引列或主键列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 这三种情况都会用到name的索引;

复合索引：一个索引关联多个字段，仅仅针对左边字段有效果，添加复合索引时，第一个字段很重要，只有包含第一个字段作为查询条件的情况才会使用复合索引(必须用到建索引时选择的第一个字段作为查询条件,其他字段的顺序无关),而且查询条件只能出现and拼接,不能用or,否则则无法使用索引.

（3）OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

（4）MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引，例如：select from A where id > 1;这里弃用索引的主要原因：查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到后一条的顺序IO开销，还要大。

4.索引的使用场景

（1）索引检索：检索数据时使用索引。

（2）索引排序: 如果order by 排序需要的字段上存在索引，则可能使用到索引。

（3）索引覆盖: 索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。例如: select name from A where name like '小明%';

建立索引索引时，不能仅仅考虑where检索，同时考虑其他的使用场景。（在所有的where字段上增加索引，就是不合理的）

5.前缀索引

前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个关键字为'王'的前缀索引。语法：Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。

6.索引失效

（1） 应尽量避免在 where 子句中使用 != 或 > 操作符，否则将引擎放弃使用索引而进行全表扫描；

（2） 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；

（3） 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；

（4）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；如select id from t where num/2 = 100；

（5） 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；如：select id from t where substring(name,1,3) = ’abc’ ；

（6）应尽量避免在where子句中对字段进行类型转换，这将导致引擎放弃使用索引而进行全表扫描； 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,如select id from t where id = 1；如果id字段在表设计中是varchar类型，那么即使id列上存的是数字，在查询时也一定要用varchar去匹配，sql应改为select id from t where id = '1'；

（7）应尽量避免在where子句中单独引用复合索引里非第一位置的索引；

join 的两种算法：BNL 和 NLJ

NLJ（Nested Loop Join）嵌套循环算法；以如下 SQL 为例：

select from t1 join t2 on t1.a=t2.a

SQL 执行时内部流程是这样的：

1. 先从 t1（假设这里 t1 被选为驱动表）中取出一行数据 X；

2. 从 X 中取出关联字段 a 值，去 t2 中进行查找，满足条件的行取出；

3. 重复1、2步骤，直到表 t1 后一行循环结束。

这就是一个嵌套循环的过程，如果在被驱动表上查找数据时可以使用索引，总的对比计算次数等于驱动表满足 where 条件的行数。假设这里 t1、t2都是1万行，则只需要 1万次计算，这里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循环联接）。

如果 t1、t2 的 a 字段都没有索引，还按照上述的嵌套循环流程查找数据呢？每次在被驱动表上查找数据时都是一次全表扫描，要做1万次全表扫描，扫描行数等于 1万+1万1万，这个效率很低，如果表行数更多，扫描行数动辄几百亿，所以优化器肯定不会使用这样的算法，而是选择 BNL 算法；

BNLJ（Block Nested Loop Join）块嵌套循环算法；

1. 把 t1 表（假设这里 t1 被选为驱动表）满足条件的数据全部取出放到线程的 join buffer 中；

2. 每次取 t2 表一行数据，去 joinbuffer 中进行查找，满足条件的行取出，直到表 t2 后一行循环结束。

这个算法下，执行的 Extra 中会出现 Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表扫描，总的扫描行数等于 1万+1万。但是由于 joinbuffer 维护的是一个无序数组，每次在 joinbuffer 中查找都要遍历所有行，总的内存计算次数等于1万1万。另外如果 joinbuffer 不够大放不下驱动表的数据，则要分多次执行上面的流程，会导致被驱动表也做多次全表扫描。

BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率。

例如：

如果内层循环有100条记录，外层循环也有100条记录，这样的话，每次外层循环先将10条记录放到buffer中，内层循环的100条记录每条与这个buffer中的10条记录进行匹配，只需要匹配内层循环总记录数次即可结束一次循环（在这里，即只需要匹配100次即可结束），然后将匹配成功的记录连接后放入结果集中，接着，外层循环继续向buffer中放入10条记录，同理进行匹配，并将成功的记录连接后放入结果集。后续循环以此类推，直到循环结束，将结果集发给client为止。

可以发现，若用NLJ，则需要100 100次才可结束，BNLJ则需要100 / block_size 100 = 10 100次就可结束,大大减少了循环次数。

JOIN 按照功能大致分为如下三类：

JOIN、STRAIGHT_JOIN、INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。

LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join。

mysql 多表连接查询方式，因为mysql只支持NLJ算法,所以如果是小表驱动大表则效率更高；反之则效率下降；因此mysql对内连接或等值连接的方式做了一个优化,会去判断join表的数据行大小,然后取数据行小的表为驱动表。

INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接，那平时如何选择呢？一般情况下用INNER JOIN、JOIN或者WHERE等值连接，因为MySQL 会按照"小表驱动大表的策略"进行优化。当出现需要排序时，才考虑用STRAIGHT_JOIN指定某张表为驱动表。

两表JOIN优化

a.当无order by条件时，根据实际情况，使用left/right/inner join即可，根据explain优化 ；

b.当有order by条件时，如select from a inner join b where 1=1 and other condition order by a.col；使用explain解释语句；

1）如果第一行的驱动表为a，则效率会非常高，无需优化；

2）否则，因为只能对驱动表字段直接排序的缘故，会出现using temporary，所以此时需要使用STRAIGHT_JOIN明确a为驱动表，来达到使用a.col上index的优化目的；或者使用left join且Where条件中不含b的过滤条件，此时的结果集为a的全集，而STRAIGHT_JOIN为inner join且使用a作为驱动表。注：使用STRAIGHT_JOIN虽然不会using temporary，但也不是一定就能提高效率，如果a表数据远远超过b表，那么有可能使用STRAIGHT_JOIN时比原来的sql效率更低，所以怎么使用STRAIGHT_JOIN，还是要视情况而定。

在使用left join（或right join）时，应该清楚的知道以下几点：

(1). on与 where的执行顺序

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。

(2).注意ON 子句和 WHERE 子句的不同

即使右表的数据不满足ON后面的条件,也会在结果集拼接一条为NULL的数据行,但WHERE后面的条件不一样,右表不满足WHERE的条件,左表关联的数据也会被过滤掉。

(3).尽量避免子查询，而用join

往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询。

（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in：select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)

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

（3）join语句,MySQL里面的join是用小表去驱动大表,而由于MySQL join实现的原理就是做循环，比如left join就是对左边的数据进行循环去驱动右边的表,左边有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是mn行数据。左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集.值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后.否则MySQL会当作普通的连表查询；

（4）select count() from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的；

（5）select from t 这种语句要尽量避免，使用具体的字段代替，更有实际意义，需要什么字段就返回什么字段；

（6）数据量大的情况下，limit要慎用，因为使用limit m，n方式分页时，mysql每次都是查询前m+n条，然后舍弃前m条，所以m越大，偏移量越大，性能就越。比如：select from A limit 1000000,20这钟，查询效率就会非常低，当分页的页数大于一定的数量之后，就可以换种方式来分页：select from A a join （select id from A limit 1000000，20） b on a.id=b.id;