【mysql数据库】

SQL

数据库分页

-- 在所有的查询结果中，返回前5行记录。
SELECT prod_name FROM products LIMIT 5;
-- 在所有的查询结果中，从第6行开始，返回5行记录。
SELECT prod_name FROM products LIMIT 5,5;

在偏移量非常大的时候，例如 LIMIT 10000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面的10000条记录都将被抛弃，这样的代价是非常高的。优化此类分页查询可以使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。针对：SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 10000,20;，如果表非常大，可改写为：

SELECT film.film_id,film.description 
FROM sakila.film
INNER JOIN (
    SELECT film_id FROM sakila.film ORDER BY title LIMIT 10000,20
) AS lim USING(film_id);

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：SELECT film_id,description FROM skila.film WHERE position BETWEEN 10001 AND 20 ORDER BY position;
LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租赁记录做翻页，那么可以根据最新一条租赁记录向后追溯，这种做法可行是因为租赁记录的主键是单调增长的。首先使用下面的查询获得第一组结果：SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;，假设上面的查询返回的是主键16049到16030的租赁记录，那么下一页查询就可以从16030这个点开始：SELECT * FROM sakila.rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

聚合函数

COUNT()函数：统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，它有两种用法：

1. COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
2. COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
3. COUNT()函数可以与GROUP BY一起使用来计算每个分组的总和。

AVG()函数()：

AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

AVG()函数可以与GROUP BY一起使用，来计算每个分组的平均值。

SUM()函数：

SUM()是一个求总和的函数，返回指定列值的总和。

SUM()可以与GROUP BY一起使用，来计算每个分组的总和。

MAX()函数：

MAX()返回指定列中的最大值。

MAX()也可以和GROUP BY关键字一起使用，求每个分组中的最大值。

MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

MIN()函数：

MIN()返回查询列中的最小值。

MIN()也可以和GROUP BY关键字一起使用，求出每个分组中的最小值。

MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

表跟表之间的关联关系

表与表之间常用的关联方式有两种：内连接、外连接
内连接：通过INNER JOIN来实现，返回两张表中满足连接条件的数据

等值连接：通过WHERE子句中的条件，将两张表连接在一起，它的实际效果等同于内连接

外连接：通过OUTER JOIN来实现，它会返回两张表中满足连接条件和不满足连接条件的数据。外连接有两种形式：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）。
左外连接：返回左表中的所有记录和右表中满足连接条件的记录。
右外连接：返回右表中的所有记录和左表中满足连接条件的记录。

以上是从语法上来说明表与表之间关联的实现方式，而从表的关系上来说，比较常见的关联关系有：一对多关联、多对多关联、自关联。
一对多关联：这种关联形式最为常见，一般是两张表具有主从关系，并且以主表的主键关联从表的外键来实现这种关联关系。另外，以从表的角度来看，它们是具有多对一关系的
多对多关联：如果两张表具有多对多的关系，那么它们之间需要有一张中间表来作为衔接，以实现这种关联关系。这个中间表要设计两列，分别存储那两张表的主键。因此，这两张表中的任何一方，都与中间表形成了一对多关系，从而在这个中间表上建立起了多对多关系。
自关联：自关联就是一张表自己与自己相关联，为了避免表名的冲突，需要在关联时通过别名将它们当做两张表来看待。一般在表中数据具有层级（树状）时，可以采用自关联一次性查询出多层级的数据。

SQL中怎么将行转成列

可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。有两种实现方式：

1. 使用 CASE…WHEN…THEN 语句实现：

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

SUM()是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject="语文"的记录只有一条，所以SUM()的值就等于对应那一条记录的score的值。假如userid ='001' and subject='语文'的记录有两条，则此时SUM()的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。

2. 使用IF()函数实现

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
FROM tb_score 
GROUP BY userid

IF(subject='语文',score,0)作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

SQL注入

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作。

举个例子：比如我们的登录功能，其登录界面包括用户名和密码输入框以及提交按钮，登录时需要输入用户名和密码，然后提交。此时调用接口/user/login/ 加上参数username、password，首先连接数据库，然后后台对请求参数中携带的用户名、密码进行参数校验，即SQL的查询过程。假设正确的用户名和密码为ls和123456，输入正确的用户名和密码、提交，相当于调用了以下的SQL语句：SELECT * FROM user WHERE username = 'ls' AND password = '123456'，SQL中会将#及–以后的字符串当做注释处理，如果我们使用 ’ or 1=1 # 作为用户名参数，那么服务端构建的SQL语句就如下：select * from user where username='' or 1=1 #' and password='123456'，而#会忽略后面的语句，而1=1属于常等型条件，因此这个SQL将查询出所有的登录用户。其实上面的SQL注入只是在参数层面做了些手脚，如果是引入了一些功能性的SQL那就更危险了，比如上面的登录功能，如果用户名使用这个 ’ or 1=1;delete * from users; #，那么在";"之后相当于是另外一条新的SQL，这个SQL是删除全表，是非常危险的操作，因此SQL注入这种还是需要特别注意的。

如何解决SQL注入

1. 严格的参数校验：在一些不该有特殊字符的参数中提前进行特殊字符校验
2. SQL预编译：在知道了SQL注入的原理之后，我们同样也了解到MySQL有预编译的功能，指的是在服务器启动时，MySQL Client把SQL语句的模板（变量采用占位符进行占位）发送给MySQL服务器，MySQL服务器对SQL语句的模板进行编译，编译之后根据语句的优化分析对相应的索引进行优化，在最终绑定参数时把相应的参数传送给MySQL服务器，直接进行执行，节省了SQL查询时间，以及MySQL服务器的资源，达到一次编译、多次执行的目的，除此之外，还可以防止SQL注入。

具体是怎样防止SQL注入的呢？实际上当将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。我们常用的JDBC就有预编译功能，不仅提升性能，而且防止SQL注入。

将一张表的部分数据更新到另一张表

update b set b.col=a.col from a,b where a.id=b.id;
update b set col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

WHERE和HAVING的区别

WHERE是在查询结果返回之前约束数据的，WHERE中不能使用聚合函数。
HAVING是在查询返回结果之后对查询结果进行过滤操作，在HAVING中可以使用聚合函数，但不能使用除了分组字段和聚合函数之外的其他字段。

where性能更好，这是因为在需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。但如果需要使用聚合函数，就要用having

索引

一、索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
二、索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
三、索引的优点：

1. 类似大学图书馆建书目索引，可以减少磁盘I/O的次数，加快查询速率
2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性，如果这个数据建了索引，那么会自动给他加上唯一约束 。
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度
4. 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 ，降低CPU的消耗。因为索引是排好序的能快速查找的数据结构，既然排好序，那进行分组和排序时会更高效

四、缺点：

1. 创建索引和维护索引要耗费时间，随着数据量的增加，所耗费的时间也会增加。
2. 索引存储在磁盘上，需要占磁盘空间
3. 虽然索引大大提高了查询速度，但是当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。因为索引是排好序的能快速查找的数据结构，假如索引现在是1，1，2，2，3，4，4，假如要增加一个索引3，那么4，4，需要移动，删除和修改同理。如何解决这个问题：在维护表时，先删除表中的索引，然后插入数据，插入完成后再建索引

索引分类

MySQL的索引可以分为以下几类：

1. 普通索引和唯一索引
   普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
   唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

2. 单列索引和组合索引
   单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
   组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
3. 全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
4. 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

如何创建及保存MySQL的索引？

1. 创建表时：

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
# 1. UNIQUE、FULLTEXT、SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
#2. INDEX与KEY的作用相同，用来指定创建索引，INDEX较常用；
#3. index_name指定索引的名称（给索引起别名），为可选参数，如果不指定，那么MySQL默认col_name为索引名；
#4. col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
#5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
#6. ASC或DESC指定升序或者降序的索引值存储。

# 创建联合索引
CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

2. 在已经存在的表上创建索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]
CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

CREATE INDEX idx_cmt ON book6(COMMENT);
CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

怎么判断要不要加索引？索引设计原理

1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引
4. 当唯一性是某种数据本身的特征时，指定唯一索引。
5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

只要创建了索引，就一定会走索引吗？

不一定。比如，在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。

如何判断数据库的索引有没有生效？

使用EXPLAIN语句查看：EXPLAIN SELECT * FROM book WHERE year_publication=1990;
EXPLAIN语句将为我们输出详细的SQL执行信息，如果key的值包含了year_publication字段，则说明在查询时使用了该索引。

索引是越多越好吗？

不是。一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

避免索引失效的办法

1. 使用组合索引时，需要遵循“最左前缀”原则；
2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
3. 尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
4. MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
5. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
6. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
7. 少用or，用它来连接时会索引失效。

所有的字段都适合创建索引吗？

不是。不适合创建索引的情况：

1. 频繁更新的字段不适合建立索引；
2. where条件中用不到的字段不适合建立索引；
3. 数据比较少的表不需要建索引；
4. 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；
5. 参与列计算的列不适合建索引。

索引的实现原理

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

1. MyISAM索引实现：MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
   
   如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
   
2. InnoDB索引实现：虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
   下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
   
   第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
   
   了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

数据库索引的重构过程

什么时候需要重建索引呢？

1. 表上频繁发生update，delete操作；
2. 表上发生了alter table …move操作（move操作导致了rowid变化）。

怎么判断索引是否应该重建？

一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析：analyze index index_name validate structure;
在相同的session中查询index_stats表：select height,DEL_LF_ROWS/LF_ROWS from index_stats;
当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

如何重建索引？

直接重建索引：

alter index indexname rebuild;
alter index indexname rebuild online;

此方法较快，建议使用。

rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

rebuild重建索引的过程：

Rebuild以index fast full scan或table full scan方式（采用那种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online执行表扫描获取数据，重建过程中有排序的操作；
Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；
rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。
重建索引过程中的注意事项：

执行rebuild操作时，需要检查表空间是否足够；
虽然说rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；
Rebuild操作会产生大量Redo Log；

MySQL的索引为什么用B+树？

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

联合索引的存储结构是什么，它的有效方式是什么？

从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。

MySQL的Hash索引和B树索引有什么区别？

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。它们有以下的不同：

1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
2. hash索引不支持使用索引进行排序，原理同上。
3. hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。
4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
5. hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

聚簇索引和非聚簇索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引，每个页的大小都为16KB，且不能更改。
聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根据主键值查找聚簇索引来得到数据，这种查找方式又被称为书签查找。因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。

什么是联合索引？

联合索引是指对表上的多个列进行索引，联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。另外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。

select in语句中如何使用索引？

索引是否起作用，主要取决于字段类型：

1. 如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。
2. 如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。
   IN的字段，在联合索引中，按以上方法，也会起作用。

模糊查询语句中如何使用索引？

在MySQL中模糊查询mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。