MySQL——索引

索引

1、索引：一种用于快速查询和检索数据的数据结构 B树、B+树、Hash

2、优缺点：

=（1）优点：可以大大加快数据的检索速度、创建唯一性索引可以保证数据库表中每行数据的唯一性；一般情况下，索引查询比全表查询要快的

=（2）缺点：创建和维护索引需要消耗时间，对有索引的数据进行增删改时，索引需要动态修改，会降低sql执行效率；索引需要使用物理文件存储，会消耗一定的空间

3、索引底层数据结构

=（1）Hash表：K-V键值对、哈希算法、哈希冲突（链地址法）

           MySQL没有使用的原因：Hash冲突问题；Hash索引不支持顺序和范围查询（只能用于精准定位）

=（2）B树：多路平衡查找树

           B树的所有节点集存放键key也存放数据data

           叶子节点是独立的

检索过程相当于对每个节点关键字做二分查找，不稳定

=（3）B+树：B树的变体

           B+树只有叶子节点存放key和data,其它节点存放key

           B+树叶子节点有一条链指向相邻的叶子节点

           B+树检索稳定，从根节点号叶子节点的过程，叶子节点的顺序检 索明显

=（4） InnoDB和MyISAM——索引的区别

==相同点：都是使用B+Tree 索引结构

==不同点：

               MyISAM：B+Tree叶子节点的data域 存放的是 数据记录的地址   索引文件和数据文件是分离的

                   检索时，先按照B+Tree搜索索引，指定的key存在，取出data的值，根据data值即地址，读取相应的数据记录 （“非聚簇索引”）

              InnoDB:数据文件本身就是索引文件

                   B+树的叶子节点的data域保存了完整的数据记录

                   检索时，搜索索引，指定的key存在，key时数据表的主键，innodb表数据文件本身就是索引文件。（聚簇索引），其余的索引作为辅助索引，辅助索引的data域存储相应记录主键的值。

                   在根据主索引搜索时，找到key所在的叶子节点可以取出数据；在根据辅助索引搜索时，则需要先取出主键的值，再走一遍主索引。

4、索引类型

=（1）主键索引：数据表的主键列使用的就是主键索引

            InnoDB表中，没有显示指定表的主键时，会自动先检查表中是否有唯一索引且不允许存在null值的字段

            有则选择该字段为默认的主键，没有就字段创建一个6byte的自增主键。

=（2）二级索引（辅助索引）：叶子节点存储的数据是主键

              通过二级索引，可以定位主键的位置。

              唯一索引、普通索引、前缀索引等属于二级索引

     唯一索引（unique key）：属性列不能出现重复的数据，可以为null,一张表允许创建多个唯一索引

     普通索引（index）：为了快速查询数据，一张表允许创建多个普通索引，允许数据重复和null

     前缀索引（prefix）:只适用于字符串类型的数据，对文本的前几个字符创建索引

     全文索引（full text）：为了检索大文本数据中的关键字的信息

5、聚簇索引和非聚簇索引

=（1）聚簇索引：索引结构和数据一起存放的索引 主键索引

           B+树非叶子节点存储索引，叶子节点存储索引和数据

              优点：查询速度快，叶子节点有序

              缺点：依赖于有序的数据，更新代价大

=（2）非聚簇索引：索引结构和数据分开存放的索引 二级索引

           非聚簇索引的叶子节点不一定存放数据的指针，可能存放主键，再根据主键回表查询数据

              优点：更新代价比聚簇索引小，因为叶子节点不存放数据

              缺点：依赖于有序的数据；可能会二次查询（回表）

=（3）覆盖索引：索引包含所有需要查询的字段的值

            非聚簇索引不一定会回表查询

            需要查询的字段正好是索引的字段，可以直接根据该索引查到数据，无需回表查询

6、适合创建索引的条件

=（1）不为null的字段：null字段难优化，建议使用0，1，true，false

=（2）被频繁查询的字段

=（3）被作为条件查询的字段：被作为where条件查询的字段

=（4）频繁需要排序的字段：索引已经排序，可以利用索引的排序，加快排序查询的时间

=（5）被经常频繁用于连接的字段：可能是一些外键列，外键列不一定要建立外键，该列涉及到表和表的关系，频繁被连接查询的字段，可以建立索引

=（6）多考虑建立联合索引：建立索引要占用磁盘空间，每个索引对应一颗B+树，联合索引中多个字段在一个索引上，可以节约磁盘空间，修改数据操作效率也会提升

7、为表字段添加索引的语句

=（1）添加主键索引primary key

alter table table_name ADD PRIMARY KEY(column)

=（2）添加唯一索引unique

alter table table_name ADD UNIQUE(column)

=（3）添加普通索引index

alter table table_name ADD INDEX Index_name(column)

=（4）添加全文索引fulltext

alter table table_name ADD FULLTEXT(column)

=（5）添加多列索引

alter table table_name ADD INDEX index_name(column1、column2、column3)

8、索引下推

（1）默认开启

（2）作用：减少回表次数、只针对二级索引有效

（3）例，返回索引再判断条件再定位数据

select * from people where a='666' and name like '%heyu%' and saying like '%changan%'

==没有采用索引下推：会通过 a 查询对应数据，返回MySQL服务端，再进行判断数据是否符合name like ... saying like...。

==采用索引下推：会返回符合a 的索引，根据name like... saying like...判断索引是否符合条件，符合再定位对应数据。

9、判断是否使用索引

语句 explain

10、建议使用自增长主键做索引

（1）自增主键是连续的，插入过程中能减少页分裂

（2）能减少页分裂造成的数据的移动

11、索引失效的情况

（1）使用!= <> 会导致索引失效

（2）类型转换会造成索引失效

（3）where后进行函数表达式会造成索引失效

（4）where有运算符运算

（5）or 会引起索引失效 ，同一字段间的or就不会

（6）‘%’前置的模糊查询 %heyu

（7）not in、not exits 会导致索引失效