03Mysql 05 mysql 索引

一、索引

1.什么是索引

1)索引就好比一本书的目录,它能让你更快的找到自己想要的内容。
2)让获取的数据更有目的性,从而提高数据库检索数据的性能。

2.索引的种类

1)BTREE:B+树索引(Btree,B+tress,B*tree)
2)HASH:HASH索引 (memery搜索引擎支持)
3)FULLTEXT:全文索引 (只可以用在myisam搜索引擎,一般纯文本数据格式才会使用)
	类似于like + %,但是like + %只适合少量的数据,对于大量数据会很慢,全文索引相对于大量的数据比like速度快,准确度低
4)RTREE:R树索引 (仅支持geometry数据类型,经纬度)

二分法:
5000
1-2500  2501-5000
		2501-3751 3752-5000
1)Btree索引介绍

在这里插入图片描述

2)B+tree索引介绍

在这里插入图片描述

3)B*tree索引介绍

在这里插入图片描述

3.索引根据算法分类

索引是建立在表的字段上面的
当where后面接的条件里面有索引会加快查询速度
1)主键索引(聚集索引)
#创建主键索引
1.建表时直接添加主键索引
mysql> create table student(id int not null primary key comment '学号');
mysql> create table student(id int not null comment '学号', primary key(id));

2.已经建好的表,添加主键索引
mysql> alter table student2 add primary key pri_id(id);
mysql> alter table student2 add primary key suibianxieshenme(id);

#查看索引
mysql> show index from student2;
2)唯一键索引
#创建唯一键索引
1.建表时直接添加唯一键索引
mysql> create table student(id int not null unique key comment '学号');

2.添加唯一键索引
mysql> alter table student add unique key uni_id(id);
mysql> create unique key uni_id on student(id);
扩展
#数据库添加唯一键索引时,该列不能有相同数据

#数据条数查看  类似于 wc -l
mysql> select count(name) from city;

#数据去重	类似于 uniq -c
mysql> select distinct(name) from city;

#当以上两个值相同时才可以创建主键索引或者唯一键索引

#国家表的name列可以创建唯一键索引
mysql> alter table country add unique key uni_name(name);
3)普通索引(辅助索引)
#添加普通索引
mysql> alter table city add index idx_name(name);
mysql> create index idx_dis on city(District);
4)全文索引
#创建全文索引
mysql> create table pull(id int, bookname varchar(10), content text, fulltext(content));

#查询数据时
mysql> select * from pull where match(content) against('查找的字符串');
5)查看索引
1.方式一:
mysql> show index from city;

2.方式二:
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   | MUL |         |                |
| CountryCode | char(3)  | NO   | UNI |         |                |
| District    | char(20) | NO   | MUL |         |                |
| Population  | int(11)  | NO   | MUL | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

PRI:主键索引
UNI:唯一键索引
MUL:普通索引
6)删除索引
mysql> alter table city drop index idx_dis;

4.索引根据创建方式分类

#注意事项
1.在创建索引的时候,会把创建索引那一列的数据按照Btree的方式进行排序;
2.创建索引,会占用磁盘空间,所以不要每一列都创建索引;
3.在同一列上,避免创建多个索引;
4.避免在数据很长的列上创建索引,如果想创建索引,就创建前缀索引;
1)前缀索引
#创建前缀索引
mysql> alter table student add index idx_name(sname(4));
2)联合索引
#1.创建一个库
mysql> create database xiangqin;

#2.创建一个表
mysql> use xiangqin;
mysql> create table people(id int, name varchar(20), age tinyint, money int, gender enum('m','f'), hight int, weight int, figure varchar(10), looks varchar(10));

#3.创建联合索引
mysql> alter table people add index idx_all(gender,looks,age,money);

#4.查看索引
mysql> show index from people;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people |          1 | idx_all  |            1 | gender      | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| people |          1 | idx_all  |            2 | looks       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| people |          1 | idx_all  |            3 | age         | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| people |          1 | idx_all  |            4 | money       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

#5.联合索引当没有按照建立索引顺序查询时,不走索引,或者部分走索引

二、explain 使用

1.explain用法

#1.查询中国和美国的城市
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');

mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

#2.explain 用法
mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql> explain select * from city where countrycode in ('CHN','USA');

2.explain 注解

id			执行顺序
table 		查询的表
type  		查询使用的类型
possible_keys	可能使用到的索引
key			真的使用到的索引
key_len		索引长度,可以使用前缀索引控制
ref			查询速度的注释
rows		查询数据的数量,有的时候会比真实数据偏高
Extra
	Using temporary	使用grouop by的时候出现
	Using filesort 使用order by 的时候出现
	Using join buffer 使用join on的时候缓存

3.查询数据的种类

1)全表扫描
#1.什么是全表扫描
在explain语句结果中,type为ALL

#2.什么时候出现全表扫描
1)查询所有数据的时候
	mysql> explain select * from city;

2)不走索引的时候
	2.1 当查询条件没有索引的时候
		mysql> explain select * from city where District='shanghai';
	2.2 索引创建有问题
	2.3 语句有问题
2)索引扫描
#常见的索引扫描类型,前提条件,查询的条件必须有索引
1)index		全索引扫描
	mysql> explain select population from city;
	
2)range		范围查询 (sql与只要达到range级别就ok了)
	mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
	mysql> explain select * from city where countrycode in ('CHN','USA');
	mysql> explain select * from city where population > 30000000;
	
3)ref		精确查找
	mysql> explain select * from city where population = 30000000;
	
4)eq_ref	类似于ref,使用join on的时候
	mysql> explain select * from city join country on city.countrycode=country.code where city.population < 100;

5)const		查询的条件的列是唯一索引或主键索引
	mysql> explain select * from country where name='Aruba';		唯一键
	mysql> explain select * from country where code='CHN';			主键

6)system	跟const同级别

7)null		执行过程中不访问表或者索引
	mysql> explain select min(population) from city;

三、索引的建立

1.建立索引的原则

1.如果可以建立唯一索引,就建立唯一索引
mysql> select count(id) from xuesheng;
+-----------+
| count(id) |
+-----------+
|        14 |
+-----------+
1 row in set (0.00 sec)

mysql> select distinct(id) from xuesheng;			#统计内容和数量
mysql> select count(distinct(id)) from xuesheng;	#只统计数量
+---------------------+
| count(distinct(id)) |
+---------------------+
|                  14 |
+---------------------+
1 row in set (0.00 sec)
	#以上两个值相同才可以创建唯一索引
	
2.为经常需要排序、分组和联合操作的字段建立索引
	经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。
	如果为其建立索引,可以有效地避免排序操作

3.为常作为查询条件的字段建立索引
	如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。
	因此,为这样的字段建立索引,可以提高整个表的查询速度。
	
4.删除不再使用或者很少使用的索引
	表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。
	数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

5.尽量使用前缀索引
	创建索引的时候需要排序
	
6.限制索引的数目
	索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
	修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
函数总结:
password()		#密码加密
database()		#当前数据库
now()			#当前时间
count()			#统计数量
distinct()		#数据去重后数量

max()			#最大值
min()			#最小值
sum()			#求和
avg()			#求平均值

2.什么时候不走索引?

1)没有查询条件,或者查询条件没有建索引
#没有加查询条件
mysql> explain select * from city;

#条件是全表
mysql> explain select * from city where 1=1;

#条件没有索引
mysql> explain select * from city where District='shanghai';
2)查询的结果集是原表中的大部分数据,25%以上
#查询的数据占所有数据的98%,不走索引
mysql> explain select * from city where population < '3000000';

#查询的数据占所有数据的12.5%,走索引
mysql> select * from city where population < '100000';

#如果一定要查询大部分数据,为了让他走索引,使用limit
mysql> explain select * from city where population < '1000000000' limit 60;
mysql> explain select * from city where population < '1000000000' limit 60,60;
3)索引本身失效
反复的修改,添加索引,索引被玩坏了,每一次添加索引,都会对数据进行排序
4)查询条件使用函数在索引列上或者对索引列进行运算,运算包括(+,-,*等)
#在符号(=)前面进行运算时,不走索引
mysql> explain select * from city where id-1=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

#在符号(=)后面进行运算时,走索引
mysql> explain select * from city where id=1+1;
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | const | PRIMARY,index_id | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
5)隐式转换,会导致索引失效
#1.创建一个表
mysql> create table test(id int,name varchar(10),phonenum varchar(20));

#2.创建索引
mysql> alter table test unique key idx_num(phonenum);

#3.插入数据
mysql> insert into test values(1,'jc','110'),(2,'hj','119'),(3,'jm','120');
mysql> insert into test values(4,'cdh',114),(5,'USAjc',999),(6,'hc',12306);

#4.测试查询数据走不走索引
#不走索引:
mysql> explain select * from test where phonenum=110;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_num       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

#走索引
mysql> explain select * from test where phonenum='110';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | idx_num       | idx_num | 23      | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

#原因:因为数据库中看到的数字,是varchar数据类型,如果是字符类型,查询时必须加引号;
6)<> 和 not in 和 or 也不走索引
mysql> explain select * from test where phonenum <> '110';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_num       | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from test where phonenum not in ('110','120','114','999','12306');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_num       | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from test where phonenum='110' or phonenum='120';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_num       | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

#使用union all代替or,可以走索引
mysql> explain select * from test where phonenum='110' union all select * from test where phonenum='120';
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | PRIMARY      | test       | const | idx_num       | idx_num | 23      | const |    1 | NULL            |
|  2 | UNION        | test       | const | idx_num       | idx_num | 23      | const |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set (0.00 sec)
7)like模糊查询 % 位置决定走不走索引
#%在最前面一定不走索引
mysql> explain select * from city where countrycode like '%HN';

#%在中间或者在最后面,可能走索引,也可能不走索引,取决于查询的数量多少
8)联合索引查询的条件不是按照顺序排列的,或者条件没有联合索引的第一个列
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m'; 
#不走索引
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值