1.什么是索引
2.索引的优劣势
3.索引的分类
4.索引结构
5.索引的创建条件
6.mysql常见瓶颈
7.Explain

1.什么是索引

　　索引是一种满足特定查找算法，提高查找效率的数据结构。

　　  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

 

2.索引的优劣势

2.1优势

提高数据检索效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低CPU(运算)消耗

 

2.2劣势

1.索引也是占用空间的

2.对数据进行变更操作的时候，同时还需要对索引进行操作

 

3.索引的分类

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

单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
删除主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
 
修改主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

 

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

单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name ;

 

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

单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

 

复合索引：即一个索引包含多个列

单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

 

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 ，用于全文索引。
 
复制代码

 

4.索引结构

4.1没有索引时查询数据

 在没有索引的情况下，如果执行select * from t where age = 22，那么要找到age = 22的数据，则是从上往下一个一个比较，直到第6行才能找到，并且数据库的文件是存在磁盘上的文件中，所以每次比较都算做一次IO操作，也就是6次IO操作，如果数据量大，可以想象查询成本将会非常大，这种查询方式被称为 全表扫描，索引就解决了这个问题

 

4.2索引数据结构分类

1）BTree索引

2）B+Tree索引

3）聚簇索引与非聚簇索引

4）full-text全文索引

5）Hash索引

6）R-Tree索引

 

4.3BTreee(多路平衡二叉树)

1)特性
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列

 

 

 

2)介绍
【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点(没有子节点的节点)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
 
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

 

5.索引的创建条件

1）数据量大

2）常用语作为查询条件、少编辑的字段

3）用于排序的字段

4）外键关联的字段

5）统计分组字段

6）数据重复且分布平均的表字段

 

6.mysql常见瓶颈

1)CPU

SQL中对大量数据进行比较、关联、排序、分组

 

2)IO

实例内存满足不了缓存数据或排序等需要，导致产生大量物理 IO。查询执行效率低，扫描过多数据行。

 

3)锁

不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡住。