索引那点事

本质

对数据库表中的一列或者多列的值进行排序的一种结构。

分类

• 聚簇索引
  一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序(类似字典拼音排序，其实就是数据的物理存储顺序)。

每一张表只能建一个聚簇索引(一个目录只能按照一种排序方式哈)，并且所占空间为该表120%的附加空间(包含该表的副本和索引中间页，其中InnoDB是B+树索引和数据行)，设置什么为簇族索引对性能很关键。
好比：字典查找你认识的字，“安”(ān)，自然的翻开前几页，如果前几页没有，那么这本字典就是没有的，因为字典是按照A-Z顺序+声调排序的，这种按照规则排序的目录称为簇族索引。

其中SQLSERVER的簇族索引默认是主键(非常不好，很少用id去查询)
InnoDB通过主键簇族数据，如果没有定义主键，会选择一个唯一的空索引代替，如果没有这样的索引，会隐含式定义个主键作为簇族索引。

MySql InnoDB的簇族索引

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

非簇族索引

好比：字典里查找你不认识的字，“鸖”（hè），自然先去根据偏旁部首查找你要找的字，然后根据这个字后的页码（假如为：300页），翻到你要找的字，此时偏旁部首表就是以部首为键值得非簇族索引，此时鸖前后有字A(假如为：80页),字B(假如为：500页)，很显然，此时“连续”的字A，鸖，字B，并不是真正的连续的，这个连续只是他们在非簇族索引里的排序。整个过程中找到鸖字，要比簇族索引要慢，分为了两个步骤，先在非簇族索引目录中找到真正的数据位置，然后再去获得真正的数据(簇族索引直接找到数据，因为我的数据和顺序是一致的哈)

左图(InnoDB簇族索引)：主键索引的叶节点存储行数据，二级索引的叶节点存储行的主键值。
右图(MyISAM非簇族索引)：主键索引和二级索引都存储着该行数据的指针(地址)，表数据和主键分开存储。

优缺点比较

1.簇族索引可以明显提高I/O密集型应用的性能，数据插入时，按照主键顺序插入最快，InnoDB一般ID自增。
2.更新主键代价很高，要导致行移动，InnoDB表主键一般不可更新。
3.二级索引访问需要先找到主键值(Key+PK cols)，然后根据主键再找到行数据(Row),而非簇族索引的二级索引和主键索引都是存储的是地址。
4.簇族适合排序。

例子

select * from table where dateTm > '时间'  用时：54s  dateTm上建立非簇族索引
select * from table where dateTm > '时间'  用时：2s    dateTm上建立簇族索引

簇族索引永远比非簇族索引快

select * from table order by id desc  用时：10s  id主键 数据量(10w)
select * from table order by id desc  用时：7s    id为主键且簇族索引 数据量(10w)

簇族索引比一般主键 order by快

select * from table where dateTm > '时间'  用时:6s //dateTm 簇族索引 数据量(100w)
select * from table where dateTm > '时间'  用时:3s //dateTm 簇族索引 数据量(50w)
select * from table where dateTm = '时间'  用时:3s // dateTm 簇族索引 数据量(50w)

簇族索引下的时间段，耗时会按照数据表的百分比增长。

簇族索引建立原则

1.数据项里有一些重复的值，但不是很多都重复
2.复合簇族索引的起始列一定是最常用的

select  * from table where a>'1'   用时：1s //用上复合簇族索引。
select * from table where a>'1' and b>'1' 用时：0.9s  //用上复合簇族索引，索引覆盖，性能最优。
select * from table where b>'1' 用时：2s //未用上复合簇族索引，性能最差。

查询优化器

数据库自带查询优化器，会自动根据where子句优化，利用一个扫描参数(SARG),限制需要扫描的数据量(有时优化器并不会按照你的本意快速查询)

例如：select * from table where a > '1'

用到SARG	未用到SARG
a like '你好%'	a like '%你'
a = '1' and b =2	a = '1' or b =2
NOT,!=,<>,!>,NOT EXISTS,NOT IN,NOT LIKE,ABS(a),函数，2*a>,表达式等

Sql优化

• in和or作用一样，都会引起全表扫描，索引会失效。

• exists和in效率一样

MySQL 查询语句执行时间

SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37
1.SELECT @@profiling; 查询profile是否开启
2.未开启则 SET profiling = 1;
3.执行SQL select * from table
4.show profiles 查看所有的sql,找到刚刚那条sql的query_id为x,执行下面语句
5.show profile cpu,block io,memory,swaps,context switches,source for query x 查看详细信息