MySQL基础之 索引
MySQL索引讲解
索引的好处:
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
索引的缺点:
过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
索引的设计原则
1、选择唯一性索引,也就是说多选择那些设置为主键,唯一性UNIQUE的字段来建立索引
2、为经常排序、分组的字段建立索引
3、常用作查询条件的字段建立索引,也就是常在WHERE等等子句中指定查询的列。
4、建立合适数量的索引,并不是索引越多越好
5、尽量使用数据量少的字段建立索引
6、尽量使用前缀来建立索引,比如TEXT和BLOG类型的字段,对前面的几个字符建立索引即可
7、不用的索引及时删除。
创建索引的两种方式
1、创建新表的同时创建索引。
2、如果对已存在的表创建索引,使用CREATE INDEX命令或者ALTER TABLE命令
现在来分析这两种用法
一、创建新表的同时创建索引。
CREATE TABLE TABLE_NAME(字段名 数据类型 ,
................,
[UNIQUE | FULLTEXT ] INDEX | PRIMARY KEY,
[INDEX_NAME](fileld1,field2........ [(length)] [ ASC|DESC ]);
);
UNIQUE | FULLTEXT INDEX:这两个选取其中任意一个,比如选择UNIQUE INDEX表示创建唯一索引,FULLTEXT INDEX表示创建全文索引。也可以直接就写INDEX表示创建普通索引。
PRIMARY KEY:表示创建主键索引。
INDEX_NAME:索引的名称。
filed:最后跟上field,表示对哪个字段创建的索引,还可以跟多个列表示使用多列索引。
length:索引的长度,必须是字符串类型的字段才可以使用。
二、已存在的表使用CREATE INDEX命令或者ALTER TABLE命令
创建普通索引
1、CREATE INDX
CREATE INDEX INDEX_NAME ON TABLE_NAME(’COLUMN_NAME’);
注释:COLUMN_NAME是要创建索引的列。
如果创建不止一个索引,多个COLUMN_NAME之间用逗号隔开即可。
2、ALTER TABLE 修改表结构(添加索引)
ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME(‘COLUMN_NAME’);
3、删除索引
DROP INDEX INDEXNAME ON TABLE_NAME;
ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
创建唯一索引
1、CREATE INDEX
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(’COLUMN_NAME’);
如果创建不止一个索引,多个COLUMN_NAME之间用逗号隔开。
2、ALTER TABLE修改表结构(添加索引)
ALTER TABLE TABLE_NAME ADD UNIQUE INDEX_NAME(‘COLUMN_NAME’)
3、删除索引
ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
创建主键索引
主键只能作用在一个列上,添加主键索引时,主键默认不为空(NOT NULL)。
1、ALTER TABLE修改表结构
ALTER TABLE TABLE_NAME MODIFY TABLE_NAME DEFINATION NOT NULL;
ALTER TABLE TABLE_NAME ADD PRIMARY KEY('COLUMN_NAME');
2、删除索引
ALTER TABLE TABLE_NAME DROP PRIMARY KEY; #因为一个表中只能有一个主键,所以不需要指定索引名
总结:不能用CREATE INDEX语句创建PRIMARY KEY索引。
显示索引信息
SHOW INDEX显示信息,\G格式化输出
SHOW INDEX FROM TABLE_NAME;\G
联合索引
联合索引就是对多个字段同时建立的索引,多列索引使用的时候是有要求的,就是只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。
举个例子:
mysql> desc test1_1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | grade | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table test1_1 add index id_g (id,grade); # id为联合索引的第一个字段 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from test1_1 where id =2 \G # 使用联合索引的第一个字段进行查询 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ref possible_keys: id_g #使用了索引 key: id_g key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test1_1 where grade = 90 \G #使用联合索引的第二个字段查询, *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ALL possible_keys: NULL # 发现没有使用索引 key: NULL key_len: NULL ref: NULL rows: 6 filtered: 16.67 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test1_1 where id = 3 AND grade=95 \G; # 使用联合索引的两个字段都作为查询条件 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1_1 partitions: NULL type: ref possible_keys: id_g # 使用了联合索引 key: id_g key_len: 6 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) ERROR: No query specified
删除索引
删除索引一般我们要使用建立索引时的索引名,可以使用show create table table_name;\G命令来查看之前建立索引的名称。
删除索引的语法:DROP INDEX INDEX_NAME ON TABLE_NAME;
删除主键索引的语法:ALTER TABLE TABLE_NAME DROP PRIMARY KEY;
相关文章
- MySQL索引面试题分析(索引分析,典型题目案例)
- Mysql授权允许远程访问解决Navicat for MySQL连接mysql提示客户端不支持服务器请求的身份验证协议;考虑升级MySQL客户端
- mysql的cardinality异常,导致索引不可用
- 用Navicat连接mysql报错:2003-Can't connect to MySql server on '10.100.0.109'(10039)
- Mysql第五天 索引
- MySQL的Explain关键字查看是否使用索引
- Mysql正则匹配某列是否含有手机号
- mysql之show engine innodb status解读(转)
- loadrunner11:Data Wizard使用,连接数据库进行参数化(mysql举例)
- MySQL运维---索引
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- 谈Mysql索引
- MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)--续
- MYSQL避免全表扫描__如何查看sql查询是否用到索引(mysql)
- Mysql索引数据结构有多个选择,为什么一定要是B+树呢?_面试 (MySQL 索引为啥要选择 B+ 树)
- MySQL普通索引与唯一索引__mysql中唯一索引和普通索引的用途及区别
- Ansible roles角色实战案例:httpd nginx memcached mysql
- 【安装包】MySQL客户端
- 让人敬佩的白发程序员——MySQL/MariaDB之父Monty阿里交流会
- MySQL Study之--Mysql无法启动“mysql.host”
- mysql索引失效的几种情况和解决方案—— 筑梦之路
- Mysql实战篇之怎么给字符串加索引--03
- Mysql基础篇之索引下--05
- 【MySql】MySQL索引15连问(相信大家看完肯定会有帮助)
- 【性能优化】MySql查询性能优化必知必会
- MySQL索引基础
- MySQL_03_MySQL索引加快查找速度的内部原理