MySQL的索引

外网数据结构测试

索引优缺点

优点

  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变成顺序IO
  • 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
  • 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
  • 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  • 对于非常小的表，大部分情况下简单的全表扫描更高效；

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

因此应该只为最经常查询和最经常排序的数据列建立索引。

MySQL里同一个数据表里的索引总数限制为16个。

联合索引、最左前缀匹配

最左原则：
假设组合索引为：a,b,c的话；那么当SQL中对应有：a或a，b或a，b，c的时候，可称为完全满足最左原则；当SQL中查询条件对应只有a，c的时候，可称为部分满足最左原则；当SQL中没有a的时候，可称为不满足最左原则。

注：MySQL5.7开始，会自动优化，如：会把c，b，a优化为a，b，c使之完全遵循最左原则；会把c，a优化为a，c使之部
分遵循最左原则。即：SQL语句中的对应条件的先后顺序无关。

原理：

结合b+数据结构来看：排序的时候是有优先级的从前往后排序，所以为了效率上讲，基本第一个的索引是十分重要的，数据量起来之后，缺少前面的遍历难度基本都是按照指数级增长

mysql 字符串数组 使用索引查询_mysql 字符串数组 使用索引查询

数据库的索引的实现

数据的索引实现为B+Tree、hash索引结构存储数据.

B+Tree是一种多路平衡查询树，所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点)，所以对于范围查询的时候不需要做全表扫描。

Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的。

联合索引的作用是什么？

1>用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；

2>覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一；

3>索引列越多，通过索引筛选出的数据越少。

索引是越多越好吗？为什么？

1>合理的建立索引能够加速数据读取效率，不合理的建立索引会拖慢数据库的响应速度。

2>索引越多，更新数据的速度越慢。

不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。

例子：

SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了

唯一索引和普通索引哪个性能更好？

· 对于查询操作来说：普通索引和唯一索引的性能相近，都是从索引树中进行查询；

· 对于更新操作来说：唯一索引要比普通索引执行的慢，因为唯一索引需要先将数据读取到内存中，再在内存中进行数据的唯一效验，所以执行起来要比普通索引更慢。

以下 or 查询有什么问题吗？该如何优化？

· select * from t where num=10 or num=20;

· 答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：

select * from t where num=10 union select * from t where num=20;

Mysql不走索引归类

  1. 查询条件在索引列上使用函数操作,或者运算的情况

例如以下case是不走索引的：

explain select * from student where abs(age) =18;
explain select * from student where age + 1=18;
  1. 查询条件字符串和数字之间的隐式转换

例如：name与age分别做字符串/数字（88）的隐式转换；

以下case走索引情况：

explain select * from student where name =’88’; 
explain select * from student where age='88'; 
explain select * from student where age =88;

以下case不走索引情况：

explain select * from student where name=88;
  1. 特殊修饰符 %%, Or 将不走索引
explain select * from student where name like'%name%' ;
explain select * from student where name ='name' or age = 18;

如何让 like %abc 走索引查询？

我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 REVERSE() 函数来创建一个函数索引，查询脚本

如下：

select * from t where reverse(f) like reverse(’%abc’);

主键索引和非主键索引有什么区别？

如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。（见后文）

主键和唯一索引的区别？

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

1>主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

2>主键不允许为空值，唯一索引列允许空值；

3>一个表只能有一个主键，但是可以有多个唯一索引；

4>主键可以被其他表引用为外键，唯一索引列不可以；

5>主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本

为什么建议InnoDB表必须建立主键？

InnoDB中采用的是聚簇索引，表数据文件本身就是按照B+Tree组织的一个索引结构文件，主键索引默认就是B+Tree，由此主键索引可以维护整张表。如果在实际建表过程中不建立主键，MySQL会自动在表中找一列数据（该列数据没有重复值）来建立唯一索引，在B+tree中维护整张表的数据。

并且使用主键自增的索引？

整型比大小更快，整型对于UUID来说占用存储空间小。

用自增方便每次插入到叶子节点链的后面，对于B+树的分裂来说更加方便。如果不用自增的话，有可能插入到叶子节点的中间位置，对于B+树的分裂来说不太方便。主要影响数据写入表的性能。