MySQL 索引和索引优化分析
索引简介
介绍
- 索引(index)是帮助MySQL高效获取数据的数据结构。
- 可以理解为：索引是数据结构；或者排好序的快速查找数据结构。
- 索引本身很大，不可能全部存在内存中，是以索引文件的形式存储在磁盘上。
优缺点
-
优点
- 提高数据检索效率，降低数据库的io成本。
- 通过索引对数据排序，降低排序成本，降低cpu消耗。
-
缺点
- 降低了更新表的速度，如insert、update和delete。
- 索引也是一张表，该表保存了主键和索引字段，并指向实体表的纪录，所以占用了更多空间。
索引结构
-
BTree
BTree的特点：
（1）所有键值分布在整个树中。
（2）任何关键字出现且只出现在一个节点中。
（3）搜索有可能在非叶子节点结束。
（4）在关键字全集内做一次查找，性能逼近二分查找算法。
B-Tree存在的问题：
（1）每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时就会导致每个节点（即一个页）能存储的key的数量很小
（2）当存储的数据量很大时，同样1会导致B-Tree的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率
image.png
image.png
-
BTree
B+Tree与BTree的不同在于：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data。
（2）为所有叶子节点增加了一个链指针。
image.png
image.png
-
总结：
因为计算机内存问题和查询效率问题，mysql选择B+Tree
索引分类
-
基本语法
# 创建索引
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
# 删除索引
DROP INDEX [indexName] ON mytable;
# 查看索引
SHOW INDEX FROM table_name\G
-
单一索引
# 随表一起建索引：
create table `t_emp`(
`id` int(11) not null auto_increment,
`name` varchar(20) default null,
`age` int(3) default null,
`deptId` int(11) default null,
`empno` int not null,
primary key (`id`),
key 'idx_dept_id' ('deptId') --创建索引
)engine=innodb auto_increment=1 default charset = utf8;
# 单独建单值索引：
create index idx_name on t_emp(name);
# 删除索引：
drop index idx_name on t_emp;
-
唯一索引
create unique index idx_empno on t_emp(empno);
-
主键索引
注意：新建的表可以创建主键索引，已有数据的表不能新建索引。
# 随表一起建索引：
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)索引
-
复合索引
create index idx_age_deptid_name on t_emp(age,deptId,name);
image.png
索引创建条件
-
需要创建索引的条件
- 主键自动建立唯一索引 。
- 频繁作为查询条件的字段应该创建索引(where 后面的语句)。
- 查询中与其它表关联的字段，外键关系建立索引。
- 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
-
不需要创建索引的条件
- 表记录太少
- 经常增删改的表或者字段
- Where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引
Explain（索引创建的标尺）
介绍
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
使用方式
#Explain + SQL语句
explain select c.name, ab.name ceoname from t_emp c left join (
select a.name,b.id from t_emp a inner join t_dept b on a.id = b.CEO
) ab on c.deptId = ab.id;
可以得到以下字段
image.png
字段解释
id字段
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。
每个id号，表示一趟独立的查询。一个sql 的查询趟数越少越好，即id号越少越好。
image.png
select_type字段
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
image.png
table字段
显示这一行的数据是关于哪张表的。
type字段
字段类型
image.png
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
（1）system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
（2）system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
image.png
possible_keys字段
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key字段
实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
key_len字段
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引。
ref字段
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
rows字段
rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。
Extra字段
image.png
