数据库-优化-索引-索引的优化

索引的优化

1、什么是索引？

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

     数据库使用索引以找到特定值，然后顺指针找到包含该值的行。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的！

2、如何创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

       1、ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

说明：其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

 

2、CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

       说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

3、索引类型

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。

事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

4、删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

 5、查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

6、什么情况下，使用索引了？

 1、表的主关键字

2、自动建立唯一索引

3、表的字段唯一约束

4、直接条件查询的字段（在SQL中用于条件约束的字段）

5、查询中与其它表关联的字段

6、查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度）

7、查询中统计或分组统计的字段

8、表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）

9、经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）

10、数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）

11、经常和主字段一块查询但主字段索引值比较多的表字段

12、对千万级MySQL数据库建立索引的事项及提高性能的手段

3、如何选择合适的列建立索引

1、在where从句，group by从句，order by从句，on从句中的列添加索引

       2、索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO也会越大）

3、离散度大的列放到联合索引的前面

例子：

select * from payment where staff_id =2 and customer_id =584;

注意:

       是index（staff_id，customer_id）好，还是index（customer_id，staff_id）好

那我们怎么进行验证离散度好了？

A、我们先查看一下表结构

desc payment;

B、分别查看这两个字段中不同的id的数量，数量越多，则表明离散程度越大：因此可以通过下图看出：customer_id 离散程度大。

结论：由于customer_id 离散程度大，使用index（customer_id，staff_id）好

C、mysql联合索引

 

①命名规则 ：表名_字段名

 1、需要加索引的字段，要在where条件中

2、数据量少的字段不需要加索引

3、如果where条件中是OR关系，加索引不起作用

4、符合最左原则

②什么是联合索引

1. 两个或更多个列上的索引被称作联合索引，又被称为是复合索引。
2. 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。  

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

4、索引优化SQL的方法

1、索引的维护及优化（重复及冗余索引）

       增加索引会有利于查询效率，但会降低insert，update，delete的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引

2、如何找到重复和冗余的索引

重复索引：

重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中的 primary key和ID列上的索引就是重复索引

create table test(
id int not null primary key,
name varchar(10) not null,
title varchar(50) not null,
unique(id)
)engine=innodb;

冗余索引：

       冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key（name，id）就是一个冗余索引。

create table test(
id int not null primary key,
name varchar(10) not null,
title varchar(50) not null,
key(name,id)
)engine=innodb;

说明：对于innodb来说，每一个索引后面，实际上都会包含主键，这时候我们建立的联合索引，又人为的把主键包含进去，那么这个时候就是一个冗余索引。

3、如何查找重复索引

工具：使用pt-duplicate-key-checker工具检查重复及冗余索引

pt-duplicate-key-checker -uroot -padmin -h 127.0.0.1

4、索引维护的方法

       由于业务变更，某些索引是后续不需要使用的，就要进行删除。

在mysql中，目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析；

pt-index-usage -uroot -padmin /var/lib/mysql/mysql-host-slow.log

附：https://www.percona.com/downloads/