什么是索引?
简单的来说,索引就是相当一个目录,举个栗子,我现在想在一张学生表中查找叫李明的学生,没有索引的话,mysql执行全盘扫描(理解为for循环),并且扫描的是数据库中的所有字段。如果我在name这个字段上建立一个索引,那么只需要去查找name这一列,然后在将这一列的值取出来就好。
索引的优缺点
任何事物都是一种双刃剑,有优点必然有缺点。
索引的优点很明显:查找速度快。
索引的缺点:索引也是表中的信息,索引如果使用不恰当,反而会使查找速度降低。
下面一个例子看一下用索引查找和不用索引查找的时间。
city表结构如下:
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
`test` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
我们查询人口大于200000的城市信息:
右下角时间为0.469秒
下面我们加上索引来查一下:
重新修改表结构:
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
`test` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `testindex` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=304080 DEFAULT CHARSET=latin1
我们在population加上了普通索引,下边来查询:
右下角时间0.016秒,效率提交了约30倍,可见适当的加上所以还是很必要的。
索引分类
普通索引:上述使用的就是普通索引
语法:alter table 表名 add index 索引名('字段名')
create index on 表名(列名);
唯一索引:字段设置唯一
主键也是唯一索引,因为主键字段必须是唯一的。
全文索引:可以在varchar,char,text上边创建
关键字:fulltext(语法自行百度把)
这个全文索引我在用mysql8.0的时候遇到一些问题,详见我的博客置顶,去看看
多列索引:可以创建基于多个字段的索引
使用其中的一个字段去搜索也可以作为普通索引。
合理使用比单列索引效率更高。遵循最左匹配原则(下边详讲)
索引在mysql是怎么存储的?
索引在mysql中是通过B树,B+树,R-树以及散列类型。
最左匹配原则:
通过上面了解了索引的存储方式以后,可以更好的了解最左匹配原则:
将age,sex,grade作为多列索引。
select * from test where age<10 and sex ='man' and grade<5
查询的时候就会先去查找age<10的数据,然后在这点数据中找sex=‘man’的数据最后再进行筛选出grade<5的数据。
但是如果我把查询条件顺序修改一下,那么他的执行是怎么执行的呢?
select * from test where grade<5 and sex ='man' and age<10
但是这条数据并不是并不是向上述那样执行的,那么不是最左匹配原则吗?
这是因为mysql中存在查询优化器,他会去判断这条语句怎么执行效率最高,然后生成执行计划。