MySQL--索引

一、了解索引
1、定义
索引是存储引擎用于快速找到记录的一种数据结构。索引建立在表上，对其中一个或多个字段进行排序。
2、目的
提高查询效率。
3、原理
通过不断缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。
4、本质
一张保存了主键和索引字段，并指向实体表记录的表，往往以文件形式存储在硬盘上。
5、优点
①提高数据检索效率，降低数据库的IO成本；
②通过索引列对数据进行排序，降低数据排序成本和CPU的消耗。
6、缺点
①频繁插入和删除数据非常耗时，降低更新表的速度，且如果有大数据量的表需要不断调整优化索引；
②索引存放在磁盘中，影响存储的数据量。
二、常见的索引及相关语法
1、种类
（1）普通索引：不附加任何限制。
（2）唯一性索引unique：该索引限制所设置索引的值必须为唯一。
（3）全文索引fulltext：该索引只可创建在varchar、char、text类型字段上。
（4）单列索引：在表中单个字段上创建，且只根据该字段进行索引。
（5）多列索引：在表中多个字段上创建，可以通过这几个查询，且只有查询条件中使用了这些字段中第一个字段时索引才被使用。
2、语法
（1）建表时添加索引
建表同时建立单列索引

CREATE TABLE user1(id INT,
userName VARCHAR(20),
sex VARCHAR(4),
INDEX (userName)
);

建表同时建立唯一性索引

CREATE TABLE user2(id INT,
userName VARCHAR(20),
sex VARCHAR(4),
UNIQUE INDEX index_userName(userName)
);

建表同时建立多列索引

CREATE TABLE t_user3(id INT ,
userName VARCHAR(20),
sex VARCHAR(4),
INDEX index_ns(userName,sex)
);

（2）给已存在的表添加索引
添加单列索引

CREATE INDEX index_userName ON user1(userName);

添加唯一性索引

CREATE UNIQUE INDEX index_userName ON user1(userName);

添加多列索引

CREATE INDEX index_ns ON user1(userName,sex);

（3）删除索引

DROP INDEX index_userName ON user1;

三、使用索引
1、适合建立索引的场景
（1）表的主键、外键必须有索引（外键是唯一的，而且经常会用来查询）。
（2）数据量超过300的表。
（3）经常与其他表进行连接的表，可以在连接字段上应该建立索引。
（4）出现在where、order by、group by等后面的字段。为此类字段添加索引可以加快判断速度。
（6）经常用到排序的列上，因为索引已经排序。
2、不适合建立索引的场景
（1）在查询中很少使用的列。很少使用到的列，有无索引不会提高查询速度，并且还会因为增加了索引，降低系统维护速度、增大空间需求。
（2）只有很少数据值的列。由于这些列的取值很少，在查询时增加索引，并不能明显加快检索速度。
（3）对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
（4）修改性能远大于检索性能时。修改性能和检索性能是互相矛盾的。如当增加索引时，会提高检索性能，但是会降低修改性能。
（5）不会出现在where条件中的字段。
3、不同引擎下的索引
答：索引和数据放在一起使管理变得复杂，建议将两者分开。如果数据改动频繁选择MyIsam，反之选择InnoDB。
此外，功能需求多时选择InnoDB引擎，反之选择MyIsam。因为MyISAM不像InnoDB那样提供事务支持、外键等功能，即如果要使用MyISAM，需要确定业务不需要且以后也不需要高并发读写及事务支持。
4、MyIsam、InnoDB、Memory支持的索引类型

注：①hash索引不适合做范围查询，此外其查询效率近似为O(1)；②InnoDB引擎的B树索引为聚簇型索引，而MyIsam引擎的B树索引为非聚簇型索引。
四、设计原则
1、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2、为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作字段会浪费很多时间。如果为其建立索引，可以提高效率。
3、为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。为这样的字段建立索引可以提高整个表的查询速度。
4、限制索引数目
每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，因此索引的数目并不是越多越好。并且，修改表时，也需要对索引进行重构和更新，浪费时间。
5、尽量使用数据量少的索引
索引的值过长会影响查询速度。例如，对CHAR(100)类型的字段进行全文检索比对CHAR(10)类型字段全文检索要耗时。
6、尽量使用前缀来索引
如果索引字段的值很长，可以使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，只检索字段的前若干个字符可以提高检索速度。
7、删除不再使用或者使用少的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要，将它们删除可以减少索引对更新操作的影响。
8、小表不应建立索引
9、包含大量的列并且不需要搜索非空值的时候可以考虑不建索引
五、底层结构——B+树
1、查询效率：logM(N)
2、只在叶子节点存放数据
3、索引存储在文件中，B+树一个节点可以存放多个数据，一次IO可以读取多个值，效率提高
4、规定不超过3层（否则索引存储的数据量过于庞大，影响效率）
六、索引失效
1、隐式转换导致索引失效
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
例：select * from test where tu_mdn=13333333333;
改正：select * from test where tu_mdn=‘13333333333’;
2、对索引列进行运算导致索引失效,我所指的对索引列进行运算包括+、-、*、/、! 等
例：select * from test where id-1=9;
改正：select * from test where id=10;
3、使用MySQL内部函数导致索引失效（这种情况应创建基于函数的索引）
例：select * from test where 函数(id)=10;
4、此外，<>、not in、!=，以及like”%_“都会导致索引失效
注：①not in用不到索引，但in可以用到（因为not in没有明确查询条件）；
②like"%“可采用在建立索引时用reverse(columnName)处理（因为最左比较法，%不够明确，如果是like”%"可以用到索引）。
5、使用OR关键字查询语句
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时查询中才会使用索引，否则查询将不使用索引。
七、索引的优化
1、遵循索引设计原则
2、避免索引失效
3、修改MySQL配置
4、B+树是B树的优化
区别：
①存储方面，B树的叶子节点和非叶子节点存的都是数据，非叶子节点既是数据也是路由；B+树的非叶子节点只是路由。
②维护方面，B+树更容易维护、查询效率更平均、更适合做范围查询。
八、使用索引时的注意事项
1、添加索引
例：

create table student (id int primary key,name varchar(20),age int);
	insert student values(1,'zs',12);
	insert student values(2,'ls',23);
	insert student values(3,'wl',32);

（1）explain select * from student where id=3\G
答：id是主键，此处无需索引
（2）explain select * from student where name=‘ls’\G
答：alter table student add index(name)
（3）explain select * from student where name=‘ls’ and age = 23\G
答：alter table student add index(name,score);
注：若此处查询顺序调换，索引顺序不用换
（4）explain select id from student where name=‘ls’\G
答：alter table student add index(name);
（5）explain select age from student where name=‘ls’\G
答：alter table student add index(name,age);
注：此处(name,age)顺序不能换
2、索引的最左前缀原则：只要包含第一个索引字段则会用到该索引