文章目录
什么是索引
- 索引(index)是帮助MySQL高效获取数据的数据结构。
- 索引的目的在于提高查询效率,类似于字典。
- 索引是排好序的快速查找数据结构。
- 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以 某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
- 平常所说的索引,如果没有特别指明,都是B树(多路索引树,并不一定是二叉树)结构组织的索引。
优势
- 提高数据检索的效率,降低数据库的IO成本。
- 通过索引列队数据进行排序,降低数据排序的成本,降低了CPU的消耗。
劣势
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是要占空间的。
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新条件了索引列的字段,都会调整因为更新所带来的键值变化会的索引信息。
索引分类
- 单值索引:一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引:索引列的值必须唯一,但允许有空值。
- 复合索引:一个索引包含多个列。
需要索引情况
- 主键自动建立唯一索引。
- 频繁作为查询条件的字段应该创建索引。
- 查询中与其他表关联的字段,外键关系建立索引。
- 频繁更新的字段不适合创建索引。
- where条件里用不到的字段不创建索引。
- 在高并发下倾向创建组合索引。
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
- 查询中统计或者分组字段。
- 一个索引的选择性越接近于1,这个索引的效率就越高。
不需要索引情况
- 表记录太少(300万左右)。
- 经常增删改的表。
- 数据重复且分布平均的表字段,应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容,为他建立索引就没有太大的实际效果。
索引单表优化
建立数据表
create table `article`(
`id` int primary key auto_increment,
`author_id` int,
`category_id` int,
`views` int,
`comments` int,
`title` varchar(100),
`content` text);
插入数据
insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
values (1, 1, 1, 1, "1", "1")
insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
values (2, 2, 2, 2, "2", "2")
insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
values (3, 1, 1, 3, "3", "3")
没索引查询
explain
select `id`, `author_id` from `article`
where `category_id` = 1 and `comments` > 1
order by `views` desc
limit 1;
性能分析
优化
尝试一
建立新索引
create index `idx_article_ccv` on `article` (`category_id`, `comments`, `views`);
性能分析
尝试二
删除原先索引
drop index `idx_article_ccv` on `article`
建立新索引
create index `idx_article_cv` on `article` (`category_id`, `views`)
性能分析
索引两表优化
建立数据表
create table `class`(
`id` int primary key auto_increment,
`card` int)
create table `book`(
`book_id` int primary key auto_increment,
`card` int)
插入数据
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
没索引查询
explain
select * from `class` left join `book` on `class`.`card` = `book`.`card`
性能分析
优化
尝试一
左连接,索引加在右表(优)
create index `idx_book_card` on `book` (`card`)
性能分析
尝试二
左连接,索引加在左表
删除原先索引
drop index `idx_book_card` on `book`
建立新索引
create index `idx_class_card` on `class` (`card`)
性能分析
索引三表优化
建立数据表
create table `phone`(
`phone_id` int primary key auto_increment,
`card` int)
插入数据
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
没索引查询
删除原先索引
drop index `idx_class_card` on `class`
explain
select * from `class` left join `book` on `class`.`card` = `book`.`card`
left join `phone` on `book`.`card` = `phone`.`card`
性能分析
优化
尝试一
建立新索引
create index `idx_book_card` on `book` (`card`)
create index `idx_phone_card` on `phone` (`card`)
性能分析
索引优化
建立数据表
create table `staffs`(
`id` int primary key auto_increment,
`name` varchar(30),
`age` int,
`pos` varchar(20),
`add_time` timestamp)
插入数据
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("不知火舞", 21, "法师", now())
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("猪八戒", 21, "坦克", now())
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("白起", 21, "坦克", now())
建立索引
create index `idx_staffs_name_pos_age` on `staffs` (`name`, `age`, `pos`)
全值匹配
最左前缀法则:查询从索引的最左前列开始,并且不跳过索引中的列。
explain select * from `staffs` where `name` = "不知火舞"
explain select * from `staffs` where `name` = "不知火舞" and age = 21
explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"
explain select * from `staffs` where age = 21 and pos = "法师"
explain select * from `staffs` where pos = "法师"
不在索引列做任何操作
不使用计算、函数、(自动或手动)类型转换
explain select * from `staffs` where `name` = "不知火舞"
explain select * from `staffs` where left(`name`,4) = "不知火舞"
存储引擎不能使用索引中范围条件右边的列
explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"
explain select * from `staffs` where `name` = "不知火舞" and age > 11 and pos = "法师"
pos索引没有用到
使用覆盖索引
索引列和查询列一致
explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"
explain select `name`, `age`, `pos` from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"
不使用不等于
explain select * from `staffs` where `name` = "不知火舞"
explain select * from `staffs` where `name` != "不知火舞"
不使用 is null 或 is not null
explain select * from `staffs` where `name` is null
explain select * from `staffs` where `name` is not null
谨慎使用like
explain select * from `staffs` where `name` = "不知火舞"
explain select * from `staffs` where `name` like "%不知火舞%"
explain select * from `staffs` where `name` like "%不知火舞"
explain select * from `staffs` where `name` like "不知火舞%"
使用覆盖索引解决
explain select `name`, `pos`, `age` from `staffs` where `name` like "%不知火舞%"
字符串加引号
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("100", 25, "不知道", now())
explain select * from `staffs` where `name` = "100"
explain select * from `staffs` where `name` = 100
少用or
explain select * from `staffs` where `name` = "白起" or "猪八戒"
查询优化
小表驱动大表
select * from `A` where `id` in (select `id` from `B`)
当B表的数据集必须小于A表的数据集时,用in优于exists。
select * from `A` where exists (select 1 from `B` where `B`.`id` = `A`.`id`)
当A表的数据集必须小于B表的数据集时,用exists优于in。
- exists
select * from table where exists (subquery)
将主查询的数据,放到子查询中做条件验证,根据验证结果(true或false)来决定主查询的数据结果是否得以保留
order by 关键字优化
建立数据表
create table `person`(
`id` int primary key auto_increment,
`age` int,
`birth` timestamp)
插入数据
insert into `person` (`age`, `birth`) values (22, now())
insert into `person` (`age`, `birth`) values (23, now())
insert into `person` (`age`, `birth`) values (24, now())
建立索引
create index `idx_person_age_birth` on `person` (`age`, `birth`)
尽量使用index方式排序
explain select * from `person` where `age` > 20 order by `age`
explain select * from `person` where `age` > 20 order by `age`, `birth`
explain select * from `person` where `age` > 20 order by `birth`
explain select * from `person` where `age` > 20 order by `birth`, `age`
explain select * from `person` order by `age` asc, `birth` desc
- order by语句使用索引最左前列。
- 使用where子句与order by子句条件列组合满足索引最左前列。