1.索引概述
索引是帮助mysql高效获取数据的数据结构(有序)。
优势 | 劣势 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引列额外占用空间 |
通过索引列对数据进行排序,降低数据排序成本,降低cpu消耗 | 降低更新表的速度,insert、update、delete的效率降低 |
2.索引结构
索引结构 | 描述 |
---|---|
B+tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree空间索引 | 空间索引是MYISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text全文索引 | 是一种通过建立倒排索引,快速匹配文档的方式。 |
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引(默认) | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 支持 | 支持 | 不支持 |
3.B+tree索引
特点:层级少、查找效率高,支持范围查询和排序
4.Hash索引
特点:只支持对等比较(= 、in)不支持范围查询(between、>=、<=、....)、不支持排序、查询效率高
5.索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只有一个 | primary |
唯一索引 | 避免同一表中数据列的值重复 | 可以有多个 | unique |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 查找文本中的关键字 | 可以有多个 | fulltext |
分类 | 含义 | 特点 |
---|---|---|
聚集索引 | 将数据存储与索引放到了一块,索引结构的叶子节点保存行数据 | 有且仅有一个 |
二级索引 | 将数据与索引分开存储,索引结构的叶子节点关联对应主键 | 可以有多个 |
聚集索引选取规则:
存在主键时,主键作为聚集索引
不存在主键时,第一个唯一索引作为聚集索引
没有主键也没有唯一索引时,nnoDB自动生成rowid作为隐藏的聚集索引
6.索引操作
create [unique | fulltext] index 索引名 on 表名(index_col_name,...);#创建索引
show index from 表名;#查看索引
drop index 索引名 on 表名;#删除索引
7.SQL性能分析(linux CentOS7系统)
show [session | global] status [like 'com______'];#查看数据库执行频次
show variables like 'slow_query_log';#查看慢查询日志(默认关闭)
vi /etc/my.cnf #打开慢查询日志配置文件(/etc/my.cnf)
slow_query_log=1 #开启慢查询
long_query_time=2 #设置慢查询时间为2s
Esc键+':wq' #linux指令,保存并退出(需要在root用户下修改)
systemctl restart mysqld #修改配置后重启mysql服务
cd /var/lib/mysql/ #linux指令,打开慢查询根目录
cat localhost-slow.log #linux指令,打开慢查询日志文件
tail -f localhost-slow.log #linux指令,获取慢查询日志实时更新
select @@have_profiling;#查看是否支持profile操作
select @@profiling;#查看是否开启profile操作
set profiling=1;#打开profiling操作
show profiles;#查看sql语句耗时基本情况
show profile for query query_id;#查看query_id的sql语句耗时情况
show profile cpu for query query_id;#查看query_id的sql语句cpu使用情况
explain select 字段列表 from 表名 where 条件;#查看select语句执行计划
8.索引使用
最左前缀法则:联合索引中,查询从索引的最左列开始,不跳过索引中的列,如果跳过了某一列,则索引将部分失效
索引失效情况一:对索引进行运算操作,索引将会失效
索引失效情况二:查询字符串时不加引号,索引将失效
索引失效情况三:头部模糊匹配时,索引失效(尾部模糊匹配时,索引不失效)
索引失效情况四:or两边都要有索引,否则索引失效
索引失效情况五:如果mysql评判使用索引比全表更慢,则不使用索引
explain select *from 表名 use index (索引名) where 条件;#建议使用索引
explain select *from 表名 ignore index (索引名) where 条件;#不使用索引
explain select *from 表名 force index (索引名) where 条件;#强制使用索引
9.索引设计原则
1)针对数据量较大,且查询比较频繁的表建立索引
2)针对常作为查询条件、排序、分组操作的字段建立索引
3)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
4)如果是字符串类型的字段,字段的长度较长,可以针对字段的特点建立前缀索引
5)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表查询,提高查询效率
6)要控制索引的数量,索引越多,维护索引结构的代价也就越大,会影响增删改的效率
7)索引列不能存储null值时应该添加not null约束,有助于优化器确定选择最有效的索引