Mysql目前主要的索引类型:
1、普通索引:
它没有任何限制,值可以为空;
//创建索引,修改表结构(添加索引),删除索引
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
DROP INDEX index_name ON table
2、唯一索引
索引列的值必须唯一,但允许有空值。
//创建索引,修改表结构(添加索引)
CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
3、主键索引
特殊的唯一索引,一个表只能有一个主键,不允许有空值。
//一般是在建表的时候同时创建主键索引:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
4、组合索引
指在多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合。
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
5、全文索引
全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
//创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
//修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
//直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
索引有哪些数据结构?
哈希、B+
索引的作用:
- 索引能极大地减少扫描行数
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机 IO 变成顺序 IO
哈希索引介绍:
哈希索引是基于哈希表(散列表)实现的,他让索引(比如是name)通过哈希函数的转换映射到散列表对应的位置上,散列表的每行有对应数据行的指针。
优点:查询速度很快。
innoDB 引擎本身是不支持显式创建哈希索引的,我们可以在 B+ 树的基础上创建一个伪哈希索引。
加了索引却不生效的几种情况:
索引设计原则:三星索引
1、第一星:WHERE 后面参与查询的列可以组成了单列索引或联合索引
2、第二星:避免排序,order by ***
3、第三星:SELECT的列尽量是索引列,避免回表
覆盖索引:
如果现在有个主键索引ID,普通索引NAME。SELECT * FROM USER WHERE NAME=‘张三’ 执行该SQL语句时先查到NAME索引上的张三,然后找到他的ID,再去主键索引找到该ID对应的信息(这个过程叫回表)
而覆盖索引是直接查询ID,其实在Name字段的索引上就已经有了,那就不需要回表了。
最左匹配:
MySQL会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE)就停止匹配。
聚集索引:
为什么使用B+树?
首先我们要先了解一下,常用的几种树的概念,优缺点等。
二叉树、平衡二叉树、B树、B+树、B*树
上图:
B+树关键字集合分布在叶子结点中,非叶节点只是叶子结点中关键字的索引;
B树中任何一个关键字只出现在一个结点中,而B+树中的关键字必须出现在叶节点中,也可能在非叶结点中重复出现;
二叉树树高很高不适合做索引。
B树一个节点可以存储多个元素,相对于完全平衡二叉树整体的树高降低了,磁盘IO效率提高了。
而B+树是B树的升级版,只是把非叶子节点冗余一下,这么做的好处是为了提高范围查找的效率,会有指针指向下一个节点的叶子节点。
explain用法
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
各个字段的含义:
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明