(一)MySQL调优之-索引指南
什么是索引
1、索引是帮助MySQL高效获取数据的数据结构,简单说索引就是数据结构
2、索引的目的就在于提高查询效率, 可以类比字典
3、你可以简单理解为排好序的快速查找的数据结构
**索引约等于查得快 + 排序 **
索引的优势和劣势?
优势
1、创建了索引,提高了检索效率,降低的数据库的IO成本
2、通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
劣势
1、实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录所以索引也是需要占用磁盘空间的, 虽然索引大大的提高了查询的效率, 同时却会降低更新表的数据, 如对表进行INSERT, UPDATE和DELETE因为更新表时,MySQL不仅仅保存我们的数据, 还要保存我们这条数据索引文件每次添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息。
2、索引只是提高效率的一个因素, 如果你的MySQL有大数据了的表, 就需要花费时间研究最优秀的索引,或者优化查询的条件
MySQL索引的结构
在Java的范畴,重点讲解BTree索引。
B+树(Balance Tree多路平衡查找树)
【初始化介绍】
一颗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。
真实的情况是,3层的B+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
索引的分类
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;
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 ;
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),
PRIMARY KEY(id)
);
单独建主键索引:
ALTER TABLE customer
add PRIMARY KEY customer(customer_no);
删除建主键索引:
ALTER TABLE customer
drop PRIMARY KEY ;
修改建主键索引:
必须先删除掉(drop)原索引,再新建(add)索引
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_no_name ON customer(customer_no,customer_name);
删除索引:
DROP INDEX idx_no_name on customer ;
索引的创建方式
创建
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除
DROP INDEX [indexName] ON mytable
查看
SHOW INDEX FROM table_name\G
使用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 ,用于全文索引。
哪些情况需要创建索引
1.主键自动创建索引
2.频繁查询的字段创建索引
3.查询中,与其他表关联的字段,外键关系创建索引
4.频繁更新的字段不适合创建索引, 因为每次更新不单单更新记录,还更新索引的BTree
5.WHERE条件中用不到的字段不创建索引
6.单值/组合选问题,who? (在高并发下倾向创建组合索引)
7.查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段
哪些情况不要创建索引
- 表记录太少
- 经常增删改的表
why: 提高了查询效率,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和,因为更新表时, 不因更新的表数据,同时还会保存索引文件 - 数据重复且分布平均的表字段, 因此应该只是为最经常查询和最经常排序的数据创建索引注意,如果某个数据列包含许多重复内容,为她建立索引就没有太大实际的效果