MySql索引

索引定义

一般情况下，对于查询语句，在没有建立索引的时候，mysql会进行全表扫描，而且不扫描完不会停止，如果我在nickname上建立索引，那么mysql相当于只扫描nickname这一列即可，而且因为这一列已排好序，找到对应结果或结果集可以直接返回。

聚簇索引与非聚簇索引

聚簇索引（innoDB ）

将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

聚簇索引效率要高一些

非聚簇索引（MyISAM）

B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方

索引分类

• 单列索引(全文索引，主键索引，唯一索引，普通索引)：一个索引只包含一个列，一个表可以有多个单列索引。
• 组合索引:一个组合索引包含两个或两个以上的列

索引存储类型-B-Tree索引

InnoDB使用的是B+Tree。

• B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
• B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。

B-Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。

索引的原理

如上图，是一颗b+树。浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中

查找过程

在上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+树的性质

(1) 索引字段要尽量的小。

通过上面b+树的查找过程，或者通过真实的数据存在于叶子节点这个事实可知，IO次数取决于b+数的高度h。

假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则树高h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的；如果数据项占的空间越小，数据项的数量m越多，树的高度h越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

(2) 索引的最左匹配特性。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

创建索引的集达原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整<font =“color”>MySQL会找出执行效率最高的一种查询方式。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

• 例子：

创建（a,b,c,d）的索引
1、a = 1 and b = 2 and c > 3 and d = 4：d用不到索引
2、a = 1 and b = 2 and d = 4 可以用到索引
3、c = 1 and b = 2 and a = 4 可以用到
4、b = 2 and d = 4 and c = 3 不可以用到

可以看到只要查询条件的列中包含组合索引最左边的那一列，不管该列在查询条件中的位置，都会使用索引进行查询。

创建索引

单列索引-普通索引

 - 建表时：INDEX IndexName(`字段名`(length))
 - 建表后：CREATE INDEX IndexName ON `TableName`(`字段名`(length)) ；
   或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)

注意：如果字段数据是CHAR，VARCHAR类型，可以指定length，其值小于字段的实际长度，如果是BLOB和TEXT类型就必须指定length。

• 这个length的用处是什么?
  有时候需要在长文本字段上建立索引，但这种索引会增加索引的存储空间以及降低索引的效率，这时就可以用到length，创建索引时用到length的索引，我们叫做前缀索引，前缀索引是选择字段数据的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。

此处展示的语句用于创建一个索引，索引使用字段数据的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));

使用字段数据的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖索引。

• 这里又引出了一个新概念，覆盖索引！
  如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

  SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
  

因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

单列索引-唯一索引

要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值

 - 建表时：UNIQUE INDEX IndexName(`字段名`(length))
 - 建表后：CREATE UNIQUE  INDEX IndexName ON `TableName`(`字段名`(length)) 
   或ALTER TABLE TableName ADD UNIQUE  INDEX IndexName(`字段名`(length)）

单列索引-主键索引（PRIMARY KEY）

不允许有空值
一般在建表的时候自动创建，主键一般会设为 int 而且是 AUTO_INCREMENT自增类型的

create table table_name(
	id varchar(20) not null primary key auto_increment,
	name varchar(50)
)

对于大型项目中，可能需要多个数据库，所以一般的表的主键会有专门的生成策略而不是采用自增的，比如雪华式算法

单列索引-全文索引

假设字段的数据类型是长文本，文本字段上(text等)建立了普通索引，我们需要查找关键字的话，那么其条件只能是where column like ‘%xxxx%’ ，但是，这样做就会让索引失效，这时就需要全文索引了。

 - 建表时：FULLTEXT INDEX IndexName(`字段名`(length))
 - 建表后：CREATE FULLTEXT  INDEX IndexName ON `TableName`(`字段名`(length)) 
   或ALTER TABLE TableName ADD FULLTEXT  INDEX IndexName(`字段名`(length))

使用：

SELECT * FROM TableName
WHERE MATCH(column1， column2) AGAINST(‘xxx′， ‘sss′， ‘ddd′)

这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

如何提取关键字？

关键字即分词，MYSQL的FULLTEXT对分词不够智能，对中文也不是很支持，所以我们一般不用全文索引。取而代之的是：coreseek=sphinx+mmesg（sphinx就是索引程序；mmseg就是分词程序） 这个程序就可以解决这个问题的啦。

国内有人修改了sphinx源码，内建和mmseg配合，整合到一起就是coreseek啦（中文版sphinx）

组合索引

组合索引，即一个索引包含多个列

 - 建表时：INDEX IndexName(`字段名`(length)，`字段名`(length)，........)
 - 建表后：CREATE INDEX IndexName ON
   `TableName`(`字段名`(length)，`字段名`(length)，........) 
   或ALTER TABLE TableName ADD INDEX
   IndexName(`字段名`(length)，`字段名`(length)，........)

索引的不足

虽然索引可以提高查询效率，但索引也有自己的不足之处。
索引的额外开销：

(1) 空间：索引需要占用空间；

(2) 时间：查询索引需要时间；

(3) 维护：索引须要维护（数据变更时）；

不建议使用索引的情况：

(1) 数据量很小的表

(2) 空间紧张

查询优化神器 - explain命令

explain命令详解
关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

(root@yayun-mysql-server) [test]>explain select d1.age, t2.id from (select age,name from t1 where id in (1,2))d1, t2 where d1.age=t2.age group by d1.age, t2.id order by t2.id;
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref    | rows | Extra                           |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL   |    2 | Using temporary; Using filesort |
|  1 | PRIMARY     | t2         | ref   | age           | age     | 5       | d1.age |    1 | Using where; Using index        |
|  2 | DERIVED     | t1         | range | PRIMARY       | PRIMARY | 4       | NULL   |    2 | Using where                     |
+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+
3 rows in set (0.00 sec)

(root@yayun-mysql-server) [test]>

• id包含一组数字，表示查询中执行select子句或操作表的顺序
  优先执行id值大的，相同的话执行由上到下

• select_type 表示查询中每个select子句的类型（简单OR复杂）

  a. SIMPLE：查询中不包含子查询或者UNION
  b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
  c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
  d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
  e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  f. 从UNION表获取结果的SELECT被标记为：UNION RESULT
  
  SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
  DEPENDENT意味着select依赖于外层查询中发现的数据。 UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

• type 表示MySQL在表中找到所需行的方式，又称“访问类型”，
  常见类型如下:

  ALL：全盘扫描
  index：index与ALL区别为index类型只遍历索引树
  range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
  ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
  eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
  NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

  从上到下，性能从最差到最好

• possible_keys
  指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

• key
  显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

• key_len
  表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

• ref
  表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows
  表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

• Extra
  包含不适合在其他列中显示但十分重要的额外信息 具体见命令详解

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析
常用优化总结

优化语句很多，需要注意的也很多，针对平时的情况总结一下几点：

• 1、有索引但未被用到的情况（不建议）

(1) Like的参数以通配符开头时

尽量避免Like的参数以通配符开头，否则数据库引擎会放弃使用索引而进行全表扫描。

以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '%0'//这是全表扫描，没有使用到索引，不建议使用。

不以通配符开头的sql语句，例如：select * from t_credit_detail where Flistid like '2%'//很明显，这使用到了索引，是有范围的查找了，比以通配符开头的sql语句效率提高不少。

(2) where条件不符合最左前缀原则时

例子已在最左前缀匹配原则的内容中有举例。

(3) 使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

select * from t_credit_detail where Flistid != '2000000608201108010831508721'

(4) 索引列参与计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'

(5) 对字段进行null值判断

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

低效：select * from t_credit_detail where Flistid is null ;
可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询：
高效：select * from t_credit_detail where Flistid =0;

(6) 使用or来连接条件

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';
可以用下面这样的查询代替上面的 or 查询：
高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001'

• 2、避免select *

在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。所以，应该养成一个需要什么就取什么的好习惯。

• 3、order by 语句优化

任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

方法：
1.重写order by语句以使用索引；

2.为所使用的列建立另外一个索引

3.绝对避免在order by子句中使用表达式。

• 4、GROUP BY语句优化

提高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

• 5、用 exists 代替 in

很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)
用下面的语句替换：
select num from a where num exists(select 1 from b where num=a.num)

• 6、使用 变长字段：varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

• 7、能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为：
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

• 8、能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

• 9、在Join表的时候使用相当类型的例，并将其索引

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）