MySQL 索引

索引的建立原则

1.能创建唯一索引就创建唯一索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
  如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
  因此，为这样的字段建立索引，可以提高整个表的查询速度。
4.尽量使用前缀来索引
  如果索引字段的值很长，最好使用值的前缀来索引。
例如，TEXT和BLOG类型的字段，进行全文检索，会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
5.限制索引的数目 
  索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
6.删除不再使用或者很少使用的索引
  表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

mysql> show index from test10;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
# PRI ：主键索引
# UNI： 唯一键索引
# MUL ：普通索引

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间寻找的位置，而无需查看所有数据。这比按顺序读取每一行要快得多。
# 1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
# 2.让获取的数据更有目的性，从而提高数据库检索数据的性能。
# 分类
1.BTREE: B+树索引（Btree，B+tree，B*tree）
2.HASH：HASH索引（memery存储引擎支持）
3.FULLTEXT：全文索引（myisam存储引擎支持）
4.RTREE：R树索引
# Btree索引
# B+tree索引
# B*tree索引
# 索引是建立在数据库字段上面的
# 当where条件后面接的内容有索引的时候，会提高速度

# 注意事项
1. 创建索引是会将数据重新进行排序
2. 创建索引会占用磁盘空间
3. 在同一列上避免创建多个索引
4. 避免在数据很长的字段上创建索引

一. 根据算法分类

主键索引 PRIMARY KEY 运用理解

# 主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。
mysql> create table test(id int not null auto_increment primary key comment '学号');
Query OK, 0 rows affected (0.04 sec)

唯一键索引 UNIQUE 运用理解

# 唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。
mysql> create table test2(id int not null auto_increment unique key comment '学号');
Query OK, 0 rows affected (0.04 sec)

普通索引 INDEX 运用理解

# 普通索引：这是最基本的索引，它没有任何限制。
mysql> alter table city add index inx_name(name);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

二. 根据配置方法分类

前缀索引

# 前缀索引
  1:前缀索引一般只能用于普通索引当中,不能使用在unique当中,如果强行unique中索引有可能无法被使用上
  2:前缀索引只支持英文和数字,一般使用场景在网站中的用户注册模块,因为用户名注册多用邮箱和手机号码为主

# 设置前缀索引
mysql> alter table test add index index_key(name(4));

联合索引

#联合索引使用三种情况
1.部分走索引		id,name,age
2.全部走索引		id,name,age,looks
3.不走索引		 name,age

#创建联合索引
mysql> alter table xiangqin add index lh_key(id,name,age,looks);

三. 查询数据

全表扫描

#1.什么是全表扫描
查询数据时type类型为ALL

#2.什么情况全表扫描
1）查询数据库所有数据
	mysql> explain select * from country
2）没有走索引
	没设置索引
	索引损坏

索引扫描

1.index			#全索引扫描
	mysql> explain select Name from city;

2.range			#范围查询
	mysql> explain select * from city where countrycode ='CHN' or countrycode ='USA';
	#有限制查询到的数据在总数据的20%以内，超过则走全文扫描，所以在查询是可以使用limit限制
	mysql> explain select * from city where countrycode != 'CHN' limit 500;

3.ref			#精确查询
	mysql> explain select * from city where countrycode ='CHN';

4.eq_ref		#使用join on时偶尔会出现

5.const			#查询条件是唯一索引或主键索引
	mysql> explain select * from city where id=1;

6.system		#查询级别与const一样，当数据很少时为该级别

7.null			#不需要读取数据，只需要获取最大值或者最小值
	mysql> explain select max(population) from city;

查询条件带了特使符号(+，-)

#在=号左侧有特殊符号，不走索引
mysql> explain select * from city where id-1=1;

#在=号右侧有特殊符号，走索引
mysql> explain select * from city where id=3-1;

# 隐式转换
查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能导致查询无法使用索引。

索引实例

[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port=mysql
server_id = 2
skip_name_resolve
log_err=/usr/local/mysql/data/mysql.err
log_bin=/usr/local/mysql/data/mysql-bin
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

创建库

create database four;
create database four charset utf8 COLLATE utf8_general_ci;

创建表

use lw;
create table four(
id int primary key auto_increment comment "id号",
name varchar(12) not null comment "姓名",
age tinyint unsigned not null comment "年龄",
gender enum('M','F') default 'M' comment "性别",
cometime datetime comment "入学时间");

插入内容

# 张三
mysql> insert into  four values(1,'zs',18,'M','2020-3-14');
  # 修改表字符集
alter table four charset utf8;
alter database lw charset utf8;

  # 修改字段字符集
  mysql> alter table four CHANGE name  name VARCHAR(12) CHARACTER SET utf8 COLLATE utf8_general_ci;
  # 修改字段
  show create table four;
  # 建表时修改字符集
  create database four charset utf8 COLLATE utf8_general_ci;
  # 修改名字为中文
  update four set name='张三' where id=1;
  # 刷新
  flush privileges;
# 李四
mysql> insert into  four values(2,'王二',18,'M','2019-3-14');
# 王二
mysql> insert into  four values(3,'赵四',18,'F','2019-5-14');
# 刘六
mysql> insert into  four values(4,'刘六',18,'M''2020-3-14');

添加索引

# 主键索引
alter table four add primary key pri_id(id);
# 唯一键索引
alter table ip add unique key uni_key(id);
# 普通索引
alter table test add index ljp_key(cometime);

查看数据

# 查看表数据
select * from four;
# 查看表架构
show tables;
# 查看库字符集
show create database lw;
# 查看表字符集
show create table four;
# 查看索引
show index from four;

总结

主键索引唯一键索引 PRT/UNT： 不可以有重复，相当于书的目录大纲，可以多个但不能重复。id 证件号电话微信号等条件唯一的内容。

普通索引 MUL： 可以重复，相当于书的目录小结，可以多个。

前缀索引： 可定义前缀字符数量创建前缀索引。

联合索引： 可定义多个字段组合创建联合索引。

注意事项

创建索引是会将数据重新进行排序
创建索引会占用磁盘空间
在同一列上避免创建多个索引
避免在数据很长的字段上创建索引

posted @ 2020-08-16 19:39 JoJoblog 阅读(112) 评论(0) 收藏举报

刷新页面返回顶部