1、什么是索引？
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。<br /> 一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。<br /> 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
MySQL在查询方面主要就是两种方式：全表扫描、根据索引检索。
| 举例 | |
|---|---|
| 全表扫描 | 一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。效率比较低。 |
| 索引检索 | 先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。 |
select * from t_user where name = ‘jack’;
以上的这条SQL语句会去name字段上扫描，为什么？因为查询条件是：name=’jack’
如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。
索引检索注意：
- 在实际中，汉语字典前面的目录是排序的，按照a b c d e f….排序，
为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围其实就是扫描某个区间罢了！）
在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
- 遵循左小又大原则存放。采用中序遍历方式遍历取数据。
2、索引的实现原理？
| 假设有一张用户表：t_user | ||
|---|---|---|
| id(PK) | name | 每一行记录在硬盘上都有物理存储编号 |
| 100 | zhangsan | 0x1111 |
| 120 | lisi | 0x2222 |
提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）<br />下面这个图不是自平衡二叉树（仅供理解）<br />
3、MySQL自动添加索引
在mysql当中，主键上，以及unique字段上都会自动添加索引的！！
什么条件下，我们会考虑给字段添加索引呢？
条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。<br /> 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。
4、索引的创建与删除
1）创建索引：
- create index emp_ename_index on emp(ename);
- 给emp表的ename字段添加索引，起名：emp_ename_index
格式：create index 索引名 on 表名(列名);
2）删除索引：
- drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
格式：drop index 索引名 on 表名;
5、索引使用查询
在mysql当中，怎么查看一个SQL语句是否使用了索引进行检索？
- explain select * from emp where ename = ‘KING’;
扫描14条记录：说明没有使用索引。type=ALL
- create index emp_ename_index on emp(ename);
- explain select * from emp where ename = ‘KING’;
6、索引失效
| 索引失效 | 失效原因 | 解决方案 |
|---|---|---|
| 模糊匹配 | 使用”%”作为开头会失效，放在中间或者其他的位置不会失效 （Mysql的string类型使用前缀索引） |
尽量避免模糊查询以”%”开头 不使用select* ，而是使用索引列 |
| or | A or B，只有A和B均为索引时才走索引. 否则索引失效 |
使用union代替or |
| 复合索引失效 | 违反最左前缀法则 | 全值匹配：索引列都有具体值 最左前缀法则： create index ABC_index on table (A,B,C) 若B失效，则A有效，C失效 |
| 索引列参加运算 | ||
| 索引列使用函数 | ||
| 字符串不加单引号 | MySQL的查询优化器会自动进行类型转换，造成索引失效 | 字符串加上单引号 |
| is NULL, is NOT NULL |
MySQL 底层自动判断走索引是否快于全表扫描。当索引列非空值多，is NULL则走全表扫描 | |
| not in | in走索引，not in 失效 |
1)失效的第1种情况：模糊匹配
- select * from emp where ename like ‘%T’;
ename上即使添加了索引，也不会走索引，为什么？
原因是因为模糊匹配当中以“%”开头了！尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略。
explain select * from emp where ename like ‘%T’;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br />
2)失效的第2种情况：or
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。
explain select * from emp where ename = ‘KING’ or job = ‘MANAGER’;
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+<br /> | 1 | SIMPLE | emp | ALL | emp_ename_index | NULL | NULL | NULL | 14 | Using where |<br /> +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+<br />建议使用union，不会让索引失效
3)失效的第3种情况：复合索引
使用复合索引的时候，没有使用左侧的列查找，索引失效
- 什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
- create index emp_job_sal_index on emp(job,sal);
explain select * from emp where job = ‘MANAGER’;//索引正常
+——+——————-+———-+———+—————————-+—————————-+————-+———-+———+——
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———-+———+—————————-+—————————-+————-+———-+———+——
| 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where |
+——+——————-+———-+———+—————————-+—————————-+————-+———-+———+——
explain select * from emp where sal = 800;//索引失效
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
4）失效的第4种情况：索引列参加了运算
在where当中索引列参加了运算，索引失效。<br /> mysql> create index emp_sal_index on emp(sal);<br /> explain select * from emp where sal = 800;<br /> +----+-------------+-------+------+---------------+---------------+---------+-------+------+------------<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+---------------+---------------+---------+-------+------+------------<br /> | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where |<br /> +----+-------------+-------+------+---------------+---------------+---------+-------+------+------------mysql> explain select * from emp where sal+1 = 800;<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
5）失效的第5种情况：索引列使用了函数
在where当中索引列使用了函数<br /> explain select * from emp where lower(ename) = 'smith';<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+<br /> | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |<br /> +----+-------------+-------+------+---------------+------+---------+------+------+-------------+失效的第6...<br /> 失效的第7...
7、避免索引失效(对6补充/黑马课程)
create table `tb_seller` (`sellerid` varchar (100),`name` varchar (100),`nickname` varchar (50),`password` varchar (60),`status` varchar (1),`address` varchar (100),`createtime` datetime,primary key(`sellerid`))engine=innodb default charset=utf8mb4;-- 创建联合索引create index idx_seller_name_sta_addr on tb_seller(name,status,address);
1). 全值匹配 ，对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;
2). 最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。(想象爬楼梯)
匹配最左前缀法则，走索引：
违法最左前缀法则 ， 索引失效：
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
3). 范围查询右边的列，不能使用索引 。范围查询条件之后的字段，索引失效
根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
4). 不要在索引列上进行运算操作， 索引将失效。
5). 字符串不加单引号，造成索引失效。
由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。
6). 尽量使用覆盖索引，避免select *
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
如果查询列，超出索引列，也会降低性能。(索引列没有password，所以需要回表查询)
TIP :using index ：使用覆盖索引的时候就会出现using where：在查找使用索引的情况下，需要回表去查询所需的数据using index condition：查找使用了索引，但是需要回表查询数据using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;
8). 以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
解决方案 ：通过覆盖索引来解决
不适用select * ，而是使用索引列（主键是默认有索引的）
9). 如果MySQL评估使用索引比全表更慢，则不使用索引。
明明单独创建了address索引，但是explain查看没有使用索引，这种情况跟表中数据有关，查看表数据知道，12条数据，11条是’北京市’，使用索引查找效率不如直接全表扫描来的快，所以在执行SQL语句的时候，MySQL放弃使用索引，而使用全表扫描。
10). is NULL ， is NOT NULL 有时索引失效。
MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则使用is not null的时候走索引，使用is null的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。
11). in 走索引， not in 索引失效。
12). 单列索引和复合索引。尽量使用复合索引，而少使用单列索引 。
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);就相当于创建了三个索引 ：namename + statusname + status + address
创建单列索引
create index idx_seller_name on tb_seller(name);create index idx_seller_status on tb_seller(status);create index idx_seller_address on tb_seller(address);
使用单列索引的时候，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。
8、查看索引使用情况
show status like 'Handler_read%'; -- 查看当前会话索引使用情况show global status like 'Handler_read%'; -- 查看全局索引使用情况
Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
9、数据库优化
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类？
- 单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。
…..
注意：唯一性比较弱的字段上添加索引用处不大。
若有收获，就点个赞吧
0 人点赞
