索引种类

加速查找

hash索引

btree索引

命中索引

索引

索引操作

普通索引

唯一索引

联合索引（最左前缀匹配）

局部索引

覆盖索引

索引合并

避免使用如下语句

like ‘%xx’

使用函数

or

类型不一致

!=

>

order by

组合索引最左前缀

其他注意事项

分页

解决办法

博客园示例

索引种类

普通索引：加速查找；

主键索引：加速查找，不能为空和重复的约束；

唯一索引：加速查找，不能为空但是可以重复；

联合索引：多列组合成一个索引；

联合主键索引：多个列联合起来创建一个主键；

联合唯一索引：多个列联合起来创建一个唯一索引；

联合普通索引：多个列联合起来创建一个索引；

加速查找

无索引：从前到后依次查找；

有索引：创建额外的文件，按照特定的格式存储的，以后再查询的时候，会先去索引文件里面查，查指定的数据在原始表的哪个位置，拿到具体位置的时候，再去原始表里面查直接定位数据位置；

索引的存储方式有很多种常见的有hash、btree

hash索引

hash索引就是将内容索引的字段数据进行hash，hash完成之后我们会存储两个数据，一个是数据的hash值，一个是数据的存储地址，这种情况下，如果有千万级别的数据首先查询进来了如果条件指定的字段是该索引的值，那么首先会将这个值进行hash，然后去我们的索引文件里面找到这个hash值取出数据存储地址，直接定位原始数据，从而实现的快速查找。但是这种情况只是针对单值的时候效果很好，如果我们的条件是like或者<、>那么查询速度就没那么快了； 说白了就是单值快，范围慢；

缺点：对于单值的定位很有效果但是对于范围为取值就不那么适用了；

btree索引

btree索引首先有一个root节点，然后依次往下拓展，读取root节点，判断82大于在0-120之间，走左边分支。读取左边branch节点，判断82大于80且小于等于120，走右边分支。读取右边leaf节点，在该节点中找到数据82及对应的rowid，使用rowid去物理表中读取记录数据块(如果是count或者只select rowid，则最后一次读取不需要)，如果我们有10个分支那么可以容纳2的10次方条数据索引也就是1024条，所以说如果我们有1024条数据最多只需要经过10次的判断就能找到该数据索引，即10次I/O就可以拿到数据，然后直接拿到数据，而由于Btree索引对结构的利用率很高，定位高效。当1千万条数据时，Btree索引也是三层结构(依稀记得亿级数据才是3层与4层的分水岭)。定位记录仍只需要三次I/O，这便是开头所说的，100条数据和1千万条数据的定位，在btree索引中的花销是一样的。 详解：

建立索引需要有额外的数据结构

创建索引在查的时候是特别快，但是当我们是增加或者删除更新的时候，那么插入的数据的性能就会降低，因为每一次插入都会引起多次I/O，第一次表数据的插入，第二次索引的更新...

命中索引

如果我们用法不得当也无法命中索引，比如使用的是like等语句，它不会去索引里面找，而是直接去数据表里面找；

索引

索引自身是一种独特的数据结构，这种数据结构是存储在表中数据之外的，一个单独数据集合用来帮我们去加速查询操作，但是它会降低写性能，因为索引中的数据其实就是某个或者某些字段被抽出来单独存放的一个数据集，每次写入数据都会写入表，写了表之后还得增加索引，一次写操作引发了两次写操作；

索引操作

创建：create [unique|fulltext|spatial] index index_name [index_type] on tab_name(col_name,...)

fulltext、spatial：只对myisam表适用；

查看：show {index|indexs|keys} {from | in} tab_name [{from | in} db_name][where expr]

删除：drop index index_name on tab_name

# 创建索引

[cce]>desc cce;

+--------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| name | char(3) | NO | | NULL | |

| ages | tinyint(4) | NO | | NULL | |

| gender | enum('m','f') | NO | | m | |

+--------+----------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

[cce]>create index in_name on cce(name);

Query OK, 0 rows affected (0.73 sec)

Records: 0 Duplicates: 0 Warnings: 0

[cce]>desc cce;

+--------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------------------+------+-----+---------+----------------+

| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |

| name | char(3) | NO | MUL | NULL | |

| ages | tinyint(4) | NO | | NULL | |

| gender | enum('m','f') | NO | | m | |

+--------+----------------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

# 删除索引

[cce]>drop index in_name on cce;

Query OK, 0 rows affected (0.28 sec)

Records: 0 Duplicates: 0 Warnings: 0

普通索引

创建

create index index_name on tab_name(col_name)

删除

drop index index_name on tab_name

唯一索引

创建

create unique index index_name on tab_name(col_name)

删除

drop unique index index_name on tab_name

联合索引（最左前缀匹配）

创建

create index index_name on tab_name(col_name,..)

删除

drop index index_name on tab_name

create index index_name_email on cce(name,email)

# 最左前缀匹配

select * from cce where name='cce'; # 走索引

select * from cce where name='cce' and email='mail0426@163.com'; # 走索引

select * from cce where email='mail0426@163.com'; # 不走索引

局部索引

caichangen666

caichangen888

# 创建局部索引

如果文本前几个字符相同，那么我们就可以使用局部索引来创建索引

create index index_name on tab_name(title(title_lenght))

覆盖索引

在索引文件中能够直接过去数据这种行为叫做覆盖索引，比如name是索引字段：select name from cce where name ='cce';

索引合并

将多个单列索引合并使用，如果我们的id和name是索引：select * from cce where id=1 and name='cce';

避免使用如下语句

like ‘%xx’

select * from tb1 where name like '%cn';

使用函数

select * from tb1 where reverse(name) = 'wupeiqi';

or

select * from tb1 where nid = 1 or email = 'seven@live.com';

特别的：当or条件中有未建立索引的列才失效，以下会走索引

select * from tb1 where nid = 1 or name = 'seven';

select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'

类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然...

select * from tb1 where name = 999;

!=

select * from tb1 where name != 'alex'

特别的：如果是主键，则还是会走索引

select * from tb1 where nid != 123

>

select * from tb1 where name > 'alex'

特别的：如果是主键或索引是整数类型，则还是会走索引

select * from tb1 where nid > 123

select * from tb1 where num > 123

order by

select email from tb1 order by name desc;

当根据索引排序时候，选择的映射如果不是索引，则不走索引

特别的：如果对主键排序，则还是走索引：

select * from tb1 order by nid desc;

组合索引最左前缀

如果组合索引为：(name,email)

name and email -- 使用索引

name -- 使用索引

email -- 不使用索引

其他注意事项

- 避免使用select *

- count(1)或count(列) 代替 count(*)

- 创建表时尽量时 char 代替 varchar

- 表的字段顺序固定长度的字段优先

- 组合索引代替多个单列索引（经常使用多个条件查询时）

- 尽量使用短索引

- 使用连接（JOIN）来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值（重复少）不适合建索引，例：性别不适合

分页

select * from cce limit 1000000,10; # 这种方法在大量级别的数据下使用是不理想的，因为它需要进行一个扫描，以此为例，他会从0到1000010扫描，然后取最后10条

解决办法

1、只能看指定的页面；

2、扫描索引表

select * from cce where id in (select * from (select id from cce limit 20000,10) as s);

3、记录最大或者最小的ID：

select * from cce where id > min_id limit 10; # 从1000000开始扫

select * from cce where id < max_id order by id desc limit 10;

1、页面只有上一页或者下一页；

上一页：

select * from cce where id > min_id limit 10; # 此处的min_id是当前页面最小的id

下一页：

select * from cce where id < max_id order by id desc limit 10; # 此处的min_id是当前页面最大的id

博客园示例

# 第一页

select * from cce limit 10;

# 第二页

select * from cce where id > 10 limit 10;

# 第三页

select * from cce where id > 20 limit 10;

...

# 第两百页

select * from cce where id < 2000 order by id desc limit 10;

# 通过索引去寻找id，不经过扫描直接取到ID值，然后在源表中取出数据