索引优化分析_索引介绍

1.是什么

1.MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构 ”
1)详解(重要)
下图就是一种可能的索引方式示例：

1.左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
2.为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

2)结论
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
2. *一般来说索引本身也很大，不可能全部存储在内存中，因此 索引往往以索引文件形式存储磁盘上

磁盘块和磁盘页:

1. 数据库索引是存储在磁盘上的，如果数据很大，必然导致索引的大小也会很大，超过几个G（好比新华字典字数多必然导致目录厚）
2. 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，为了降低内存开销，InnoDB在把磁盘数据读入到内存时会以页（Page）为基本单位。（一次I/O操作）
3. 每一个磁盘块在MySQL中是一个页，页大小是固定的，MySQL InnoDB的默认的页大小是16k，每个索引会分配在页上的数量是由索引字段的大小决定。当索引字段值的长度越长，每一页上的数量就会越少，因此在一定数据量的情况下，索引的深度会越深，影响索引的查找效率。
4. SHOW GLOBAL STATUS LIKE ‘Innodb_page_size’; – 16KB

2.优势

1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3.劣势

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

4.MySQL索引

4.1.聚簇索引与非聚簇索引

1.聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
2.术语‘聚簇’表示数据跟索引放在一起就是聚簇索引。
3.如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。
聚簇索引的好处：

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

1. 对于MySQL数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
2. 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
3. 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

5.MySQL索引分类

5.1.单值索引

即一个索引只包含单个列，一个表可以有多个单列索引
语法：

随表一起建索引：
CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);
单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
删除索引：
DROP INDEX idx_customer_name  on customer;

5.2.唯一索引

索引列的值必须唯一，但允许有空值
语法：

随表一起建索引：
CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
删除索引：
DROP INDEX idx_customer_no on customer ;

5.3.主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引
语法：

随表一起建索引：
CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
CREATE TABLE customer2 (
  id INT(10) UNSIGNED   ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200) 
);
单独建主键索引：
ALTER TABLE customer2 add PRIMARY KEY customer2(id);  
删除主键索引：
ALTER TABLE customer2 drop PRIMARY KEY ;  
修改主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

5.4.复合索引

即一个索引包含多个列
语法：

 随表一起建索引：
CREATE TABLE customer (
  id INT(10) UNSIGNED  AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
单独建索引：
CREATE INDEX idx_customer_no_name ON customer(customer_no,customer_name); 
删除索引：
DROP INDEX idx_customer_no_name  on customer ;

5.5.基本语法

1.创建

CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 

如果是CHAR，VARCHAR类型，length可以小于字段实际长度； 如果是BLOB和TEXT类型，必须指定length。

ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

2.删除

DROP INDEX [indexName] ON mytable; 

3.查看

SHOW INDEX FROM table_name

4.使用ALTER命令
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

6.哪些情况需要创建索引

1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段，外键关系建立索引
4)单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
5)查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6)查询中统计或者分组字段

思考：排序和分组哪个更伤性能？

7.哪些情况不要创建索引

1)表记录太少
300万数据时MySQL性能就开始下降了，这时就可以开始开始优化了

2)经常增删改的表
提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3)where条件里用不到的字段不创建索引