🍅 Java基础教程系列： Java基础教程系列
 
 
🍅 Java学习路线总结：搬砖工逆袭Java架构师
 
 
🍅 Java经典面试题大全：10万字208道Java经典面试题总结(附答案)
 
 
🍅 简介：Java领域优质创作者🏆、CSDN哪吒公众号作者✌ 、Java架构师奋斗者💪
 
 
🍅 扫描主页左侧二维码，加入群聊，一起学习、一起进步 
 
 
🍅 欢迎点赞 👍 收藏 ⭐留言 📝   
 
 
一、MySQL三层逻辑架构
 
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图：
 
 
1、第一层负责连接管理、授权认证、安全等等。
 
每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证，也可以通过SSL证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。
 
2、第二层负责解析查询
 
编译SQL，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这一层实现。
 
3、第三层是存储引擎
 
存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。
 
二、对比InnoDB与MyISAM
 
1、 存储结构
 
MyISAM：每个MyISAM在磁盘上存储成三个文件。分别为：表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
 
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
 
2、 存储空间
 
MyISAM： MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。
 
InnoDB： 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
 
3、 可移植性、备份及恢复
 
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
 
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
 
4、 事务支持
 
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
 
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
 
5、 AUTO_INCREMENT
 
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
 
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
 
6、 表锁差异
 
MyISAM： 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
 
InnoDB： 支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
 
7、 全文索引
 
MyISAM：支持 FULLTEXT类型的全文索引
 
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
 
8、表主键
 
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
 
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
 
9、表的具体行数
 
MyISAM： 保存有表的总行数，如果select count() from table;会直接取出出该值。
 
InnoDB： 没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
 
10、CRUD操作
 
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
 
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
 
11、 外键
 
MyISAM：不支持
 
InnoDB：支持
 
三、sql优化简介
 
1、什么情况下进行sql优化
 
性能低、执行时间太长、等待时间太长、连接查询、索引失效。
 
2、sql语句执行过程
 
（1）编写过程
 
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
 
（2）解析过程
 
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
 
3、sql优化就是优化索引
 
索引相当于书的目录。
 
索引的数据结构是B+树。
 
四、索引
 
1、索引的优势
 
（1）提高查询效率（降低IO使用率）
 
（2）降低CPU使用率
 
比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。
 
2、索引的弊端
 
（1）索引本身很大，可以存放在内存或硬盘上，通常存储在硬盘上。
 
（2）索引不是所有情况都使用，比如①少量数据②频繁变化的字段③很少使用的字段
 
（3）索引会降低增删改的效率
 
3、索引的分类
 
（1）单值索引
 
（2）唯一索引
 
（3）联合索引
 
（4）主键索引
 
备注：唯一索引和主键索引唯一的区别：主键索引不能为null
 
4、创建索引
 
alter table user add INDEX `user_index_username_password` (`username`,`password`)
 
 
5、MySQL索引原理 -> B+树
 
MySQL索引的底层数据结构是B+树
 
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
 
B-Tree结构图中每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
 
B+Tree相对于B-Tree有几点不同：
 
非叶子节点只存储键值信息。
 所有叶子节点之间都有一个链指针。
 数据记录都存放在叶子节点中。
 将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
 
 
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
 
可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：
 
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。
 
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
 
数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
 
五、如何触发联合索引
 
1、对user表建立联合索引username、password
 
 
2、触发联合索引
 
（1）使用联合索引的全部索引键可触发联合索引
 
 
（2）使用联合索引的全部索引键，但是用or连接的，不可触发联合索引
 
 
（3）单独使用联合索引的左边第一个字段时，可触发联合索引
 
 
（4）单独使用联合索引的其它字段时，不可触发联合索引
 
 
六、分析sql的执行计划---explain
 
explain可以模拟sql优化执行sql语句。
 
1、explan使用简介
 
（1）用户表
 
 
（2）部门表
 
 
（3）未触发索引
 
 
（4）触发索引
 
 
（5）结果分析
 
explain中第一行出现的表是驱动表。
 
指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
未指定联接条件时，行数少的表为[驱动表]
对驱动表直接进行排序就会触发索引，对非驱动表进行排序不会触发索引。
 
2、explain查询结果简介
 
（1）id：SELECT识别符。这是SELECT的查询序列号。
 
（2）select_type：SELECT类型：
 
SIMPLE： 简单SELECT(不使用UNION或子查询)
PRIMARY： 最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)
（3）table：表名
 
（4）type：联接类型
 
system：表仅有一行(=系统表)。这是const联接类型的一个特例。
const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个行的话)，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery：该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN (SELECT key_column FROM single_table WHERE some_expr)
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range
index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
all：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
（5）possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
 
（6）key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
 
（7）key_len：key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
 
（8）ref：ref列显示使用哪个列或常数与key一起从表中选择行。
 
（9）rows：rows列显示MySQL认为它执行查询时必须检查的行数。
 
（10）Extra：该列包含MySQL解决查询的详细信息。
 
Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。
 
 
 
 
🍅 Java基础教程系列： Java基础教程系列
 
 
🍅 Java学习路线总结：搬砖工逆袭Java架构师
 
 
🍅 Java经典面试题大全：10万字208道Java经典面试题总结(附答案)
 
 
🍅 简介：Java领域优质创作者🏆、CSDN哪吒公众号作者✌ 、Java架构师奋斗者💪
 
 
🍅 扫描主页左侧二维码，加入群聊，一起学习、一起进步 
 
 
🍅 欢迎点赞 👍 收藏 ⭐留言 📝   
 
 
 
 
 
 添加微信，备注1024，赠送Java学习路线思维导图  

参考：https://www.cnblogs.com/barrywxx/p/4351901.html
 
什么是索引
 
 
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息
 
 
常见的查询算法：顺序查找、二分查找、二叉排序树查找、哈希散列法、分块查找、平衡多路搜索树B树（B-tree）
 
 
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
 
 
建立索引的优缺点
 
优点：
 
1.大大加快数据的检索速度;
 2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
 3.加速表和表之间的连接;
 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
 
缺点：
 
1.索引需要占用数据表以外的物理存储空间
 2.创建索引和维护索引要花费一定的时间
 3.当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。
 
索引类型：
 
根据数据库的功能，可以在数据库设计器中创建索引：唯一索引、主键索引和聚集索引。 尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。
 
唯一索引： UNIQUE 例如：create unique index stusno on student（sno）；
 表明此索引的每一个索引值只对应唯一的数据记录，对于单列唯一性索引，这保证单列不包含重复的值。对于多列唯一性索引，保证多个值的组合不重复。
 
主键索引： primary key
 数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
 
https://blog.csdn.net/zhang150114/article/details/90573226
主键和唯一索引都要求值唯一，但是它们还是有区别的：

①.主键是一种约束，唯一索引是一种索引；
②.一张表只能有一个主键，但可以创建多个唯一索引；
③.主键创建后一定包含一个唯一索引，唯一索引并一定是主键；
④.主键不能为null，唯一索引可以为null；
⑤.主键可以做为外键，唯一索引不行；
 
聚集索引（也叫聚簇索引）：cluster
 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
 
索引的实现方式
 
1.B+Tree 索引
 
MyISAM和InnoDB存储引擎的默认创建的是B+tree索引，Memory存储引擎也可以为B+tree索引（或者Hash索引）
InnoDB 的 B+Tree 索引分为主索引和辅助索引。
 
主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引
 
 
辅助索引 的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找
 
 
 
2. 哈希索引
 
哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
 
无法用于排序与分组；
 
只支持精确查找，无法用于部分查找和范围查找。
 
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找
 
3. 全文索引
 
MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
 
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
 
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
 
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引
 
4. 空间数据索引
 
MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
 
必须使用 GIS 相关的函数来维护数据。

背景：
 为了提高数据库效率，建索引是家常便饭；那么当查询条件为2个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。
 
 
一、联合索引测试
 
注：Mysql版本为 5.7.20
 
创建测试表(表记录数为63188)：
 
CREATE TABLE `t_mobilesms_11` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id，创建任务时的userid',
  `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
  `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
  `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
  `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
  `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
  `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
  `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
  `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
  `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `联合索引` (`userId`,`mobile`,`billMonth`)
) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'
 
我们为userId, mobile, billMonth三个字段添加上联合索引！
 
我们选择 explain 查看执行计划来观察索引利用情况：
 
 
1.查询条件为 userid
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'
 
 
可以通过key看到，联合索引有效
 
 
2.查询条件为 mobile
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
 

 可以看到联合索引无效
 
 
3.查询条件为 billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
 

 联合索引无效
 
 
4.查询条件为 userid and mobile
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
 

 联合索引有效
 
 
5.查询条件为 mobile and userid
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222' 
 

 在4的基础上调换了查询条件的顺序，发现联合索引依旧有效
 
 
6.查询条件为 userid or mobile
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
 

 把 and 换成 or，发现联合所索引无效！
 
 
7.查询条件为 userid and billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'
 

 这两个条件分别位于联合索引位置的第一和第三，测试联合索引依旧有效！
 
 
8.查询条件为 mobile and billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
 

 这两个条件分别位于联合索引位置的第二和第三，发现联合索引无效！
 
 
9.查询条件为 userid and mobile and billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
 

 所有条件一起查询，联合索引有效！（当然，这才是最正统的用法啊！）
 
 
二、单列索引测试
 
创建三个单列索引：
 
 
1.查询条件为 userid and mobile and billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
 

 我们发现三个单列索引只有 userid 有效（位置为查询条件第一个），其他两个都没有用上。
 
那么为什么没有用上呢？按照我们的理解，三个字段都加索引了，无论怎么排列组合查询，应该都能利用到这三个索引才对！
 
其实这里其实涉及到了mysql优化器的优化策略！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引去使用，也就是说，此处userid 、mobile 、billMonth这三个索引列都能用，只不过优化器判断使用userid这一个索引能最高效完成本次查询，故最终explain展示的key为userid。
 
 
2.查询条件为 mobile and billMonth
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
 

 我们发现此处两个查询条件只有 mobile 生效（位置也为查询条件第一个）
 
 
3.查询条件为 userid or mobile
 
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972' 
 

 这次把 and 换成 or，发现两个查询条件都用上索引了！
 
我们在网上可能常常看到有人说or会导致索引失效，其实这并不准确。而且我们首先需要判断用的是哪个数据库哪个版本，什么引擎？
 
比如我用的是mysql5.7版本，innodb引擎，在这个环境下我们再去讨论索引的具体问题。
 
关于or查询的真相是：
 所谓的索引失效指的是：假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法，没啥问题。
 
此刻需要注意type类型为index_merge。
 我查资料说mysql 5.0 版本之前 使用or只会用到一个索引（即使如上我给userid和mobile都建立的单列索引），但自从5.0版本开始引入了index_merge索引合并优化！也就是说，我们现在可以利用上多个索引去优化or查询了。
 
index_merge作用：
 1、索引合并是把几个索引的范围扫描合并成一个索引。
 2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
 3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
 
index_merge应用场景：
 
1.对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果
 
2.对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果
 
3.对AND和OR组合语句求结果
 
 
三、结论
 
通俗理解：
 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
 
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
 
 
重点：
 
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！
 
 
最左前缀原则：
 
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，
 注：如果第一个字段是范围查询需要单独建一个索引
 注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的第一位置，即最左边
 
 
同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？
 
这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；
 
 
有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！
 
 
联合索引本质：
 
当创建**(a,b,c)联合索引时，相当于创建了(a)单列索引**，(a,b)联合索引以及**(a,b,c)联合索引**
 想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！
 注：这个可以结合上边的 通俗理解 来思考！
 
 
其他知识点：
 
1、需要加索引的字段，要在where条件中
 2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）
 3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
 4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。
 
 
最后的说明：
 
网上关于索引优化等文章太多了，针对各个数据库各个版本各种引擎都可能存在不一样的说法！
 
我们的SQL引擎自带的优化也越来越强大，说不定你的某个SQL优化认知，其SQL引擎在某次升级中早就自优化了。
 
所以要么跟进官方文档，要么关注数据库大牛的最新文章，要么在现有数据库环境下自己去亲手测试！
 
数据库领域的水很深。。大家加油。。共勉 ~

       目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:image.png这里设表一共有三列,假设我  
 
        在 MySQL 中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论 MyISAM 和 InnoDB 两个存储引擎的索引实现方式。
 
MyISAM 索引实现 
 
MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图:
 
这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。
 
辅助索引 
 
在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示
 

 
同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。
 
MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。
 

 InnoDB 索引实现 
 
虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。
 
1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
 
而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。
 

 
上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集,
 
 1 .InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。
 
 同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:
 

 
这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
 
 2.第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
 例如,图 11 为定义在 Col3 上的一个辅助索引:
 


  
 
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引（回表）:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
 
 引申:为什么不建议使用过长的字段作为主键?
 
 因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
 

聚簇索引与非聚簇索引 
 
InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:
 第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。
 第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。
 
MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点
 的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。
 
为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异
 

 
 
 
联合索引及最左原则
 
联合索引存储数据结构图：
 
 
最左原则：
 
例如联合索引有三个索引字段（A,B,C）
 
查询条件：
 
（A，，）---会使用索引
 
（A，B，）---会使用索引
 
（A，B，C）---会使用索引
 
（，B，C）---不会使用索引
 
（，，C）---不会使用索引
 
 
 
*最后来一个问题：mysql假设一行数据大小为1k，则一颗层高为3的b+树可以存放多少条数据？
 
mysql页默认大小16k，如果数据行大小1k，叶子节点存放的完整数据，则叶子节点一页可以放16条数据；非叶子节点页面存放的是主键和指针，所以主要看主键是啥类型，假设是integer，则长度8字节，指针大小在innodb是6字节，一共14字节，所以非叶子节点每页可以存16384/14=1170个主键数据（1170个分叉），则三层b+树数据可以存1170*1170*16=21902400条数据。（千万级别）