Mysql 索引

什么是数据库索引

　
数据库索引是对数据库表中一列或多列的值进行排序的一种结构。数据库的索引就像一本书的目录，能够加快数据库的查询速度。

MySQL 索引类型

MySQL 索引有四种类型：

  • PRIMARY

  • INDEX

  • UNIQUE

  • FULLTEXT

这四种索引类型都是单列索引，也就是他们都是作用于单个一列，所以也称单列索引（一个索引也可以作用于多个列上，称为组合索引或复合索引）

单列索引

  • 新建一张测试表
CREATE TABLE T_USER( ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL);
PRIMARY：主键索引（索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定））
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,PRIMARY KEY(ID))
NORMAL：普通索引（索引列没有任何限制）
  • 建表时指定
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,INDEX USERNAME_INDEX(USERNAME(16))) //给列 USERNAME 建普通索引 USERNAME_INDEX
  • ALTER语句指定
ALTER TABLE T_USER ADD INDEX U_INDEX (USERNAME) //给列 USERNAME 建普通索引 U_INDEX
  • 删除索引
DROP INDEX U_INDEX ON t_user  //删除表 t_user 中的索引 U_INDEX
UNIQUE：唯一索引。索引列的值必须是唯一的，但允许有空；
  • 建表时指定
CREATE TABLE t_user(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,UNIQUE U_INDEX(USERNAME)) //给列USERNAME添加唯一索引T_USER
  • ALTER语句指定
ALTER TABLE t_user ADD UNIQUE u_index(USERNAME) //给列T_USER添加唯一索引u_index
  • 删除索引
DROP INDEX U_INDEX ON t_user
FULLTEXT：全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。索引的新建和删除和上面一致，这里不再列举...

组合索引（复合索引）

  • 新建一张表
CREATE TABLE T_USER(ID INT NOT NULL,USERNAME VARCHAR(16) NOT NULL,CITY VARCHAR(10),PHONE VARCHAR(10),PRIMARY KEY(ID) )

组合索引就是把多个列加入到统一个索引中，如新建的表T_USER，我们给USERNAME+CITY+PHONE创建一个组合索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE)  //组合普通索引

ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //组合唯一索引

这样的组合索引，其实相当于分别建立了（USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE）三个索引。

为什么没有（CITY,PHONE）索引呢？这是因为MYSQL组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合。

并不是查询语句包含这三列就会用到该组合索引：
　　
这样的查询语句才会用到创建的组合索引

SELECT * FROM t_user where USERNAME="parry" and CITY="广州" and PHONE="180"
SELECT * FROM t_user where USERNAME="parry" and CITY="广州"
SELECT * FROM t_user where USERNAME="parry" and PHONE="180"

这样的查询语句是不会用到创建的组合索引　

SELECT * FROM t_user where CITY="广州" and PHONE="180"
SELECT * FROM t_user where CITY="广州"
SELECT * FROM t_user where PHONE="180"

索引不足之处

  • 索引提高了查询的速度，但是降低了 INSERT、UPDATE、DELETE 的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；

  • 建立索引会创建索引文件，索引文件会占用一定的磁盘存储空间

索引使用注意事项

  • 只要列中包含 NULL 值将不会被包含在索引中。组合索引只要有一列含有 NULL 值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL;

  • 使用短索引。如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。
    如下：

ALTER TABLE t_user add INDEX U_INDEX(USERNAME(16)) 优于 ALTER TABLE t_user add INDEX U_INDEX(USERNAME)
　　
使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。

  • 索引列排序。Mysql 在查询的时候只会使用一个索引，因此如果 where 子句已经使用了索引的话，那么 order by 中的列是不会使用索引的，所以 order by 尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

  • Like 语句。一般情况下不是鼓励使用 like,如果非使用，那么需要注意 like"%aaa%" 不会使用索引；但 like“aaa%” 会使用索引。

  • 不使用 NOT IN 和 <> 操作

索引方式 HASH 和 BTREE 比较

HASH

用于对等比较，如 "=" 和 "<=>"

BTREE

BTREE 索引看名字就知道索引以树形结构存储，通常用在像 "=，>，>=，<，<=、BETWEEN、Like" 等操作符查询效率较高；

通过比较发现，我们常用的是 BTREE 索引方式，当然 Mysql 默认就是 BTREE 方式

posted @ 2020-11-18 10:22  Binge-和时间做朋友  阅读(25)  评论(0)  编辑  收藏  举报