一、索引的介绍
1、什么是索引?
一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。
说起加速查询,就不得不提到索引了,索引就好比一本书的目录,它能让你更快的找到自己想要的内容,让获取的数据更有目的性,从而提高数据库检索数据的性能。
2、为什么要有索引呢?
索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典,查火车车次,飞机航班等。
本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构,索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
3、索引类型的介绍
- BTREE:B+树索引
- HASH:HASH索引
- FULLTEXT:全文索引
- RTREE:R树索引
1)二分法
假如一本书有1000页,如何利用二分法快速找到第824页?
2) BTREE算法
1、select * from XXX where id=38; #3次IO
P2(28)--->P2(35)--->38
2、select *from XXX where id>38<73; #范围查询 9次IO(一个节点是一次IO)
P2(28)--->P2(35)--->38< ID <P3(65)--->P1(65)--->73
3) B+TREE算法
1、select * from XXX where id=38; #3次IO
P2(28)--->P2(35)--->38
2、select *from XXX where id>38<73; #范围查询 5次IO
P2(28)--->P2(35)--->38< ID <P3(65)--->P1(65)--->73
3、优化了范围查询,在叶子节点添加了相邻节点的指针
4) B*TREE算法
#暂时5.6和5.7版本应用的都是b+tree
二、索引的管理
索引建立在表的列上(字段)的;
在where后面的列建立索引才会加快查询速度;
pages<---索引(属性)<----查数据。
索引分类:主键索引、普通索引、唯一索引
1、普通索引
mysql> desc xiangqin;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| QQ | int(11) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | enum('f','m') | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| face | varchar(10) | YES | | NULL | |
| body | varchar(10) | YES | | NULL | |
| hight | int(11) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
1)添加普通索引
mysql> alter table xiangqin add index idex_age(age);
2)删除普通索引
mysql> alter table xiangqin drop index idex_age;
mysql> show index from xiangqin;
1.1 前缀索引
1)是普通索引的一个分支,根据字段的前N个字符建立索引;
2)避免对大列建索引,如果有就使用前缀索引;(例如blog)
3)创建前缀索引
mysql> alter table xiangqin add index inx_name (name(4));
1.2 联合索引
1)是普通索引的一个分支,多个字段建立一个索引;
2)创建联合索引
mysql> alter table xiangqin add index idx_all(sex,body,face,money);
3)mysql> select * from xiangqin where sex='f' and money>1000; #查询联合索引一定要按排序
例:
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点:前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
原则:创建联合索引一定要了解用户需求,把最常用来做为条件查询的列放在最前面。
2、主键索引
1)添加主键索引(非空且唯一)
mysql> alter table xiangqin add primary key pri_id(id); #主键名字是固定的primary
2)删除主键索引
mysql> alter table xiangqin drop primary key;
3、唯一索引
1)添加唯一索引(唯一但可空)
mysql> alter table xiangqin add unique key uni_name(name);
2)删除唯一索引
mysql> alter table xiangqin drop primary key;
3)唯一索引可以创建多个,但是列里不能有重复值,如何判断列里是否有重复值?
mysql> select count(name) from xiangqin; #统计列有多少行数据
+-------------+
| count(name) |
+-------------+
| 5 |
+-------------+
1 row in set (0.01 sec)
mysql> select distinct(name) from xiangqin; #去重后列的数据
+------+
| name |
+------+
| bgx |
| drz |
| fbb |
| fj |
| ly |
+------+
5 rows in set (0.00 sec)
mysql> select count(distinct(name)) from xiangqin; #统计去重后列的数据
+-----------------------+
| count(distinct(name)) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.00 sec)