java面试之Mysql索引（一）

Mysql面试之索引（一）

1.索引的基础操作

索引是帮助数据库高效获取数据的排好序的数据结构。

优势

• 提高数据检索的效率，降低检索过程中必须要读取得数据量，降低数据库IO成本
• 降低数据库的排序成本

劣势

• 索引会增加 增、删、改操作所带来的IO量与调整索引的计算量。
• 索引要占用空间，随着数据量的不断增大，索引还会带来存储空间的消耗。

建表时创建：

CREATE TABLE 表名(

字段名 数据类型 [完整性约束条件],

       ……，

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
    PRIMARY KEY (id),
    -- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
	FOREIGN KEY (projectid) REFERENCES project (id),
    -- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode),
    -- 创建组合索引
	INDEX (fileuploadercode,projectid)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

建表后创建：
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];
或
CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法];


ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);
CREATE INDEX fp ON projectfile(fileuploadercode, projectid);
ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid) ;

-- 将id列设置为主键
ALTER TABLE projectfile ADD PRIMARY KEY(id) ;
-- 将id列设置为自增
ALTER TABLE projectfile MODIFY id INT AUTO_INCREMENT;

查看已创建的索引：
show index from 表名;

SHOW INDEX FROM projectfile;

索引的删除：
DROP INDEX 索引名 ON 表名
或
ALTER TABLE 表名 DROP INDEX 索引名

DROP INDEX fileuploadercode_3 ON projectfile;
ALTER TABLE projectfile DROP INDEX fileuploadercode;

#列的属性还带有AUTO_INCREMENT，那么要先将这个列的自动增长属性去掉，才可以删除主键。
ALTER TABLE projectfile MODIFY id INT;
ALTER TABLE projectfile DROP PRIMARY KEY;
#添加外键，MYSQL在建外键后,会自动建一个同名的索引
ALTER TABLE projectfile ADD CONSTRAINT projectfile_ref_dept FOREIGN KEY(projectid) REFERENCES sys_dept(id);
#删除外键后需要在删除同名索引
ALTER TABLE projectfile DROP FOREIGN KEY projectfile_ref_dept;
ALTER TABLE projectfile DROP INDEX projectfile_ref_dept

查看SQL语句对索引的使用情况
EXPLAIN列的解释

字段	字段含义
table	显示这一行的数据是关于哪张表的
type	显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys	显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key	如果为NULL，则没有使用索引
key_len	使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数
rows	MYSQL认为必须检查的用来返回请求数据的行数
Extra	关于MYSQL如何解析查询的额外信息。

type字段	字段含义
const	表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref	连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref	这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range	这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index	这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL	这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

#在select语句前加上EXPLAIN即可
EXPLAIN SELECT * FROM projectfile WHERE fileuploadercode='1'

#涉及到多个索引字段时,如果这些索引字段中，存在主键索引，那么只会使用该索引
#possible_key中，只是SQL语句里涉及到的索引；key中才是实际上执行查询操作时使用到了的索引。
EXPLAIN SELECT * FROM projectfile WHERE fileuploadercode='1' AND id='1'

#模糊查询时，%如果在前面，那么不会使用索引。
EXPLAIN SELECT * FROM projectfile WHERE fileuploadercode LIKE '1%' 

当对索引字段进行 >， <，>=， <=，not in，between …… and ……，函数(索引字段)，like模糊查询%在字段前时，不会使用该索引

#完全满足最左原则：
EXPLAIN SELECT * FROM projectfile WHERE   fileuploadercode ='1' AND projectid='2' AND filename='2'
EXPLAIN SELECT * FROM projectfile WHERE   projectid='2' AND filename='2' AND fileuploadercode ='1' 

#select语句只能查询出一条记录，fileuploadercode 字段时会走组合，filename不走
EXPLAIN SELECT * FROM projectfile WHERE   fileuploadercode ='1' AND filename='2'
#select语句查询出两条记录，不走索引
EXPLAIN SELECT * FROM projectfile WHERE   fileuploadercode ='1' AND filename='2'

#满足最左原则 不管select查询出几条记录
EXPLAIN SELECT * FROM projectfile WHERE   fileuploadercode ='1' AND projectid='1' AND filename='2'

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

2.索引的数据结构

在此网站可以演绎不同数据结构的构造过程。

（一）hash

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

（二）B树

二叉查找树：左子树的键值小于根的键值，右子树的键值大于根的键值；二叉查找树可以任意地构造；对二叉查找树进行中序遍历，得到有序集合。

平衡二叉树：在符合二叉排序树的条件下，还满足任何节点的两个子树的高度最大差为1，是带有平衡条件的二叉查找树；AVL树适合用于插入与删除次数比较少，但查找多的情况；

　　如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，四种姿态的失去平衡二叉树可以概括为：LL（左左）、RR（右右）、LR（左右）、RL（右左）。它们的示意图如下：

LL：LeftLeft，也称“左左”。插入或删除一个节点后，根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RR：RightRight，也称“右右”。插入或删除一个节点后，根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

LR：LeftRight，也称“左右”。插入或删除一个节点后，根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。

RL：RightLeft，也称“右左”。插入或删除一个节点后，根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

平衡的方法
（1）LL失去平衡的情况下，可以通过一次旋转让AVL树恢复平衡，步骤如下：
将根节点的左孩子作为新根节点。
将新根节点的右孩子作为原根节点的左孩子。
将原根节点作为新根节点的右孩子。
LL旋转示意图如下：

（2）RR的旋转：RR失去平衡的情况下，旋转方法与LL旋转对称，步骤如下：
将根节点的右孩子作为新根节点。
将新根节点的左孩子作为原根节点的右孩子。
将原根节点作为新根节点的左孩子。
RR旋转示意图如下：

（3）LR的旋转：LR失去平衡的情况下，需要进行两次旋转，步骤如下：
围绕根节点的左孩子进行RR旋转。
围绕根节点进行LL旋转。
LR旋转示意图如下：

（4）RL的旋转：RL失去平衡的情况下也需要进行两次旋转，旋转方法与LR旋转对称，步骤如下：
围绕根节点的右孩子进行LL旋转。
围绕根节点进行RR旋转。
RL旋转示意图如下：

红黑树：一中弱平衡二叉树，每个节点增加一个存储位表示结点的颜色，可以是红或黑(非红即黑)；时间复杂度是O(logN)；Java集合中的TreeSet和TreeMap，C++ STL中的set、map，以及Linux虚拟内存的管理，都是通过红黑树去实现的。

红黑树的性质：

1.节点是红色或黑色。

2.根节点是黑色。

3.每个叶子节点都是黑色的空节点（NIL节点）。

4 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)

5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

6.通过对任何一条从根到叶子的路径上各个节点着色的方式的限制，红黑树确保没有一条路径会比其他路径长出两倍

B树：一种平衡的多叉树

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree:

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
　　分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

（三）B+树

数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址；B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用；数据库中B+Tree的高度一般都在2~4层。

3.mysql索引为什么用B+树

hash：虽然可以快速定位，但范围查找效率低，Hash索引不能被排序，Hash索引在发生大量Hash值相同的情况时性能不一定比B-Tree索引高，Hash索引不能使用部分索引键查询，Hash索引不能避免表数据的扫描。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

B树一个节点可以存储多个元素，相对于平衡二叉树、红黑树，整棵树的高度就降低了，磁盘IO效率提高了。
B+树是B树的升级版，只是把非叶子节点冗余一下，B+树的叶子节点之间会有指针相连，这么做的好处是为了提高范围查找的效率。

B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低。

B+树的节点存储

• B+树中一个节点大小为一页或页的倍数最为合适。如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费

• Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页）
  MySQL中可通过如下命令查看页的大小：
  show variables like ‘innodb_page_size’;
  SHOW GLOBAL STATUS LIKE “Innodb_page_size”

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：117016=18720条，一颗高度为3的B+树能存储的数据为：11701170*16=21902400（千万级条）。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

假设有一张学生表，id为主键

MyISAM存储引擎索引文件和数据文件是分离的。使用myisam存储引擎创建的表相关文件有三个，.frm是存放表结构数据，MYD是表数据，MYI是存放索引，索引树上会存储数据在MYD文件里面的位置。

InnoDB存储引擎索引和数据都在同一个文件。使用Innodb存储引擎创建的表相关文件只有两个，.frm文件是存放表结构数据，.ibd存放的数据和索引。

在 InnoDB中，因为设计之初就是认为主键是非常重要的。是以主键为索引来组织数据的存储，当我们没有显示的建立主键索引的时候，搜索引擎会隐式的为我们建立一个主键索引以组织数据存储。数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同，InnoDB就是以聚集索引来组织数据的存储的，在叶子节点上，保存了数据的所有信息。如果这个时候建立了name字段的索引，会产生一个辅助索引，即name字段的索引，而此刻叶子节点上所保存的数据为 聚集索引（ID索引）的关键字的值，基于辅助索引找到ID索引的值，再通过ID索引区获取最终的数据。这个做法的好处是在于产生数据迁移的时候只要ID没发生变法，那么辅助索引不需要重新生成，不这么做的话，如果存储的是磁盘地址的话，在数据迁移后所有辅助索引都需要重新生成。

为什么官方建议使用自增长整型主键作为索引。

方便比较，每次插入都是插入到最后，不会大规模调整树结构，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动。如果是其他类型，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

4.索引的分类

聚簇索引(聚集索引)：数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。
如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

非聚簇索引(辅助索引)：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引

两者区别
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息.
非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询，查询较慢。
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
聚簇索引修改表的速度较慢，对于经常更新的列不宜建立聚簇索引，查询速度快

覆盖索引：如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引

索引覆盖不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，也会走索引。比如联合索引userid_2（userid,buy_date）
select count(*) from buy_log where buy_date >= ‘2011-01-01’ and buy_date < ‘2011-02-01’;

回表查询
建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1,col2),执行下面的语句
select col1,col2 from 表名 where col1=‘xxx’;

普通索引(INDEX)：最基本的索引，它没有任何限制，列值可以取空值或重复值

唯一索引(UNIQUE)： 避免同一个表中某数据列中的值重复，允许有空值

主键索引(PRIMARY KEY)：一种特殊的唯一索引，不允许为null值

全文索引(FULLTEXT)：目前MySQL中只有MyISAM存储引擎支持，并且只有CHAR、VARCHAR、TEXT类型支持。它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

复合索引：基于多个字段而创建的索引。最左匹配原则、最少空间原则。

组合索引 Index (A, B, C),当三 列 在 where 中 出 现 的 顺 序 如 (A, B, C) 、(A, B)、(A)才能用到索引。

下面条件可以用组合索引查询：

• A>5
• A=5 AND B>6
• A=5 AND B=6 AND C=7
• A=5 AND B=6 AND C IN (2, 3)

下面条件将不能用组合索引查询：

• B>5 ——查询条件不包含组合索引首列字段
• B=6 AND C=7 ——理由同上

下面条件将能用部分组合索引查询（重要！ ！ ！ ！ ） ：

• A>5 AND B=2 ——当范围查询使用第一列， 查询条件仅仅能使 用第一列
• A=5 AND B>6 AND C=2——范围查询使用第二列， 查询条件仅仅能使用 前二列
• A=5 AND B IN (2, 3) AND C=2 ——理由同上

下面条件可以用上组合索引排序：

• ORDER BY A——首列排序
• A=5 ORDER BY B——第一列过滤后第二列排序
• ORDER BY A DESC, BDESC——注意， 此时两列以相同顺序排序
• A>5 ORDER BY A——数据检索和排序都在第一列

下面条件不能用上组合索引排序：

• ORDER BY B ——排序在索引的第二列
• A>5 ORDER BY B ——范围查询在第一列， 排序在第二列
• A IN(1,2) ORDER BY B ——理由同上
• ORDER BY A ASC, B DESC ——注意， 此时两列以不同顺序排序

组合索引(userid,buy_date)在第一个键相同的情况下，已经对第二个键进行了排序处理。

#在userid=1的情况下，buy_date都已经排序好了
select * from buy_log where userid=1 order by buy_date desc limit 3;
#二次排序
select * from buy_log order by buy_date desc limit 3;

5.索引建立的原则

建议有索引的

• 定义主键的数据列一定要建立索引。
• 定义有外键的数据列一定要建立索引。
• 对于经常查询的数据列最好建立索引。
• 对于需要在指定范围内的快速或频繁查询的数据列。
• 经常用在WHERE子句中的数据列。
• 经常被join on的字段适合使用索引。
• 复杂的多条件and查询都需要建立对应的多列索引。
• 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

建议无索引的

• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
• 对于定义为text、image和bit的数据类型的列不要建立索引。
• 对于经常存取的列避免建立索引

限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
每张表里面理论上最多可创建16个索引。

优化原则

• 将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
• 索引列的数据长度能少则少
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1
• 尽量使用短索引
• 建表的时候能使用数字类型的字段就使用数字类型
• 索引中的数据顺序和查找的排列顺序一致，即index(a,b,c)使用order by a,b,c，index(c desc,b asc,a desc)使用order by c desc,b asc,a desc
• 返回自己想到的数据列，少用select *
• 尽量减少子查询，使用关联查询（left join, right join, inner join）代替
• 减少使用IN或者NOT IN，使用exists,not exists或者关联查询语句代替
• 尽量用union或者union all代替or
• 在where字句中避免使用 != 或<>、or、in、字段是null、前置%(like ‘%abc%’)、对字段进行表达式操作(id/2=100000 id=2*100000)、对字段进行函数操作(substring(name,1,3))、=左边进行函数、算术、表达式等操作
• count(1)或count(列) 代替 count(*)
• 当数据量大时，避免使用where 1=1的条件
• 将不需要的记录在GROUP BY 之前过滤掉
• 避免使用HAVING子句
• 索引中的列包含了查询中所需的全部列，也就是尽量用覆盖索引,要使用覆盖索引必须都是 AND 条件，慎用 OR 条件。
• 增加中间表进行优化（在统计报表的场景，后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）
• 合理的增加冗余的字段（减少表的关联查询）

6.索引的优化测试

DROP TABLE IF EXISTS `test_user`; 
CREATE TABLE `test_user` (  
    `id` BIGINT(20)  PRIMARY KEY NOT NULL AUTO_INCREMENT,  
    `username` VARCHAR(50) DEFAULT NULL,  
    `email` VARCHAR(30) DEFAULT NULL,  
    `password` VARCHAR(32) DEFAULT NULL,
    `status`  TINYINT(1) NULL DEFAULT 0
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
#存储引擎使用MyISAM是因为此引擎没有事务，插入速度极快，方便我们快速插入千万条测试数据

DELIMITER $$ #声明存储过程的结束符号为$$
CREATE PROCEDURE myproc()
BEGIN   
    DECLARE num INT;   
    SET num=1;   
    WHILE num <= 10000000 DO   
        INSERT INTO test_user(username,email,PASSWORD) VALUES(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));   
        SET num=num+1;  
    END WHILE;  
END$$
DELIMITER ;

CALL myproc();

#耗时: 0.002s  主键索引
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE id=9999999;
#耗时：4.613s 无索引；有username索引 耗时：0.001s
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE username='username_9999999';
#条件语句不分password、useranme先后顺序，说明sql优化器优先用索引命中。
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE `password` = '283f42764da6dba2522412916b031080' AND username='username_9999999';
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE username='username_9999999' AND `password` = '283f42764da6dba2522412916b031080';
#使用OR条件的时候，虽然WHERE 语句中有用到索引字段，但还是进行了全表扫描。
#建立(username,password)索引时，还是进行了全表扫描。使用 OR 语句竟然没有启用聚合索引，也没使用到单索引username
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE `password` = '283f42764da6dba2522412916b031080'  OR username='username_9999999';
#主键区间查询，不断加大区间来看，查询时间跟查询的数据量成相对的正比增长，同时使用到了主键索引
EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8999990 AND id < 8999999;
EXPLAIN SELECT id, username, email, PASSWORD FROM test_user WHERE id > 8990000 AND id < 8999999;
#建立(username,email,password)索引，虽然走索引，查询时间长
EXPLAIN SELECT id,username,email,PASSWORD FROM test_user WHERE username='username_9999999' AND `password` = '283f42764da6dba2522412916b031080'  OR email='9999999@qq.com'

7.分页优化

#适合千级数据
select * from 表名 limit 起始位置,每页记录数  
SELECT * FROM tb_user LIMIT 3000,10;
主键自增
#适合万级数据
select * from 表名 where 主键>(页数*每页记录数),每页记录数  
select * from tb_user where id >999980 limit 110;
select * from 表名 where 主键>(页数*每页记录数) order by 主键 ASC 每页记录数
select * from tb_user where id >999980 order by id asc limit 110;
select * from (select id from tb_user order by id limit 999980,110) q join tb_user t on t.id=q.id;
#适合百万级数据
SELECT * from tb_user where id>=(select id from tb_user limit 999980,1) limit 110;
select * from tb_user join (select id from tb_user limit 999980,110) as lim using(id)