数据库索引详解（mysql）-CSDN博客

本文链接：https://blog.csdn.net/sang_li/article/details/89280508

一、了解索引

1.1 什么是索引？为什么要建立索引？

索引是对数据库表中一列或多列的值进行排序的一种数据结构(索引的数据结构有hash、btree等类型，数据结构的内容，我们不在此讨论)，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

索引对查询的效率有着至关重要的影响，一个好的索引，可以几
十倍的提高查询效率，同时索引也是数据库优化的起点。

1.2 缺点

过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
所以，一张表中的索引条数最好不要超过5条。

二、为什么使用索引能提高sql查询效率？

2.1 数据库索引里究竟存的是什么？

数据库索引是创建在表的某列上的，并且存储了这一列的所有值。但是，需要理解的重点是数据库索引并不存储这个表中其他列（字段）的值。
数据库索引同时存储了指向表中的相应行的指针。指针是指一块内存区域，该内存区域记录的是对硬盘上记录的相应行的数据的引用。因此，索引中除了存储列的值，还存储着一个指向在行数据的索引。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

如果没有索引，在执行一条查询时会逐条向下遍历，就是所谓的全表扫描.

// An highlighted block
SELECT area FROM tb_areas WHERE areaid = '320602'

以上面这条sql为例子不使用索引和使用索引的差异。

不使用索引:(EXPLAIN sql执行计划)

EXPLAIN SELECT area FROM tb_areas WHERE areaid = '320602'

未使用索引的查询情况

未建立索引时，进行全表扫描，比对的记录条数是3321条

下面我们为areaid建立索引

CREATE INDEX areaid ON tb_areas(areaid(20))

再次查看sql执行计划
建立索引时

可以看到比对的条数变为1

使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

三、索引的分类

索引是在存储引擎中实现的，不同的存储引擎会使用不同的索引

MyISAM和InnoDB存储引擎：只支持BTREE索引，不能更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

3.1从类型来分类单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引

3.1.1 单列索引：(一个索引只包含单个列，但一个表中可以有多个单列索引)

普通索引：
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：
索引列中的值必须是唯一的，但是允许为空值，并且null可能有多个

主键索引：
也即主键约束，是一种特殊的唯一索引，不允许有空值。

3.1.2 组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说

3.1.3 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

3.2 按照存储方式分为：聚集与非聚集索引

mysql 中不同引擎对聚集与非聚集索引的实现和支持也不同

1.MyISAM的B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
2.MyISAM还采用压缩机制存储索引，比如，第一个索引为“her”，第二个索引为“here”，那么第二个索引会被存储为“3,e”，这样的缺点是同一个节点中的索引只能采用顺序查找。
3.InnoDB 的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键 (所以聚簇索引的key，不能过长)。为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证。

3.3 聚簇索引

表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大。逻辑顺序决定了表中相应行的物理顺序。
特点：

(1) 一个表中只能创建一个聚簇索引
(2) 数据会根据索引键的顺序重新排列数据
(3) 无索引，数据无序;有索引，数据与索引同序
(4) 数据与索引在不同位置
(5) 索引在叶节点上存储,在叶节点上有一个＂指针＂直接指向要查询的数据区域
(6) 如果在该字段上进行范围查询，或者该表很少做增删改，建立聚簇索引
(7) 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
(8) 含有聚合索引的表,再创建非聚合索引的,索引标志指向聚合索引.

3.4 非聚簇索引

不影响表中的数据存储顺序,检索效率比聚簇索引引低,对数据新增/修改/删除的影响很少
。是通过二叉树的数据结构来描述的，逻辑顺序，特点：

(1) 非聚集索引数据与索引不同序
(2) 先建聚集索引才能创建非聚集索引
(3) 数据不会根据索引键的顺序重新排列数据
(4) 一个表可以可以创建多个非聚簇索引
(5) 叶节点的指针指向的数据也在同一位置存储
(6) 非聚集索引其实可以看作是一个被聚集索引含有的表，他仅包含原表中非聚集索引的列和指向实际物理表的指针

为什么主键通常建议使用自增id呢？

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
聚簇索引不但在检索上可以大大的提高效率，在数据读取上也一样。比如：需要查询f~t的所有单词。
一个使用MyISAM的主索引，一个使用InnoDB的聚簇索引。两种索引的B+Tree检索时间一样，但读取时却有了差异。
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。
鉴于聚簇索引的范围查询效率，很多人认为使用主键作为聚簇索引太多浪费，毕竟几乎不会使用主键进行范围查询。但若再考虑到聚簇索引的存储，就不好定论了。

四、如何使用

数据库怎么知道什么时候使用索引？

当这个sql运行时，数据库会检查在查询的列上是否有索引。假设创建了索引，数据库会接着检查使用这个索引做查询是否合理 - 因为有些场景下，使用索引比起全表扫描会更加低效。数据库会自动选择它认为最高效的策略来执行sql。

4.1 普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

直接创建索引

CREATE INDEX indexName ON mytable(username(length)); 
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

4.2 唯一索引

直接创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构(添加索引)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

4.3 主键索引

其实我们以前声明的主键约束，就是一个主键索引

创建表的时候直接指定

CREATE TABLE t2

(

id INT NOT NULL,

name CHAR(10),

PRIMARY KEY(id)

);

修改表结构(添加索引)

ALTER TABLE test ADD CONSTRAINT test_PrimaryKey PRIMARY KEY (PrimaryKey);

	以上都是单列索引

4.4 复合索引

直接创建索引

CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);

修改表结构(添加索引)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

4.5 聚集索引

创建聚集索引的语法：

create NONCLUSTERED INDEX indexname ON tablename(columnName())

4.6 非聚集索引

创建非聚集索引的语法：

create CLUSTERED INDEX indexname on tablename(columnName())

4.7 删除索引

--直接删除索引
DROP INDEX index_name ON table_name;

--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

4.8 显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
SHOW INDEX FROM table_name; \G

五、何时创建索引

定义主键的数据列一定要建立索引。

定义有外键的数据列一定要建立索引。

对于经常查询的数据列最好建立索引。

对于需要在指定范围内的快速或频繁查询的数据列;

经常用在WHERE子句中的数据列。

经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

对于定义为text、image和bit的数据类型的列不要建立索引。

对于经常存取的列避免建立索引

限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

六、索引优化

6.1 注意

数据库表中添加索引后确实会大大加快查询效率，但如果以错误的方式使用，则即使建立索引也会不生效。

- like '%xx'    
 	select * from tb1 where name like '%cn';
 	特别的：当'cn%'会走索引
 	select * from tb1 where name like 'cn%';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or    
	select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
            
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=    
	select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
    select * from tb1 where nid != 123
- >    
 	select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
    select * from tb1 where nid > 123
    select * from tb1 where num > 123
- order by    
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
- 组合索引最左前缀
    如果组合索引为：(name,email) 这里和顺序有关必须使用左侧字段后左侧才能生效
    name and email       -- 使用索引    
    name                 -- 使用索引    
    email                -- 不使用索引

避免使用select *
count(1)或count(列) 代替 count(*)
创建表时尽量时 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（经常使用多个条件查询时）
尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)
连表时注意条件类型需一致
索引散列值（重复多）不适合建索引

6.2 LIMIT分页

若需求是每页显示10条数据，如何建立分页？

我们可以先使用LIMIT尝试：

--第一页
SELECT * FROM table_name LIMIT 0,10;
--第二页
SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;

但是这样做有如下弊端：
每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录
若记录的id不连续，则会出错

改善：
若已知每页的max_id和min_id，则可以通过主键索引来快速定位:

--下一页
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);
--上一页
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);
--当前页之后的某一页
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);
--当前页之前的某一页
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;

6.3 慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

查看慢日志参数：

--查询配置命令show variables like '%query%';
--当前配置参数
	binlog_rows_query_log_events    OFFft_query_expansion_limit    20have_query_cache    YES
--时间限制，超过此时间，则记录
	long_query_time    10.000000
	query_alloc_block_size    8192
	query_cache_limit    1048576
	query_cache_min_res_unit    4096
	query_cache_size    1048576
	query_cache_type    OFF
	query_cache_wlock_invalidate    OFF
	query_prealloc_size    8192
--是否开启慢日志记录
	slow_query_log    OFF
--日志文件
	slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log

修改当前配置

set global 变量名 = 值;
--例如，修改时间限制为20
slong_query_time = 20;
ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效

查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

--verbose    版本
--debug      调试
--help       帮助
--v           版本-d           
调试模式-s ORDER     
排序方式
     what to sort by (al, at, ar, c, l, r, t), 'at' is default              
      al: average lock time
      ar: average rows sent
      at: average query time
       c: count               
       l: lock time
       r: rows sent
       t: query time-r           
反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM       
显示前N条
just show the top n queries-a           
不要将SQL中数字转换成N，字符串转换成S。
don't abstract all numbers to N and strings to 'S'-n NUM       
abstract numbers with at least n digits within names -g PATTERN   正则匹配；
grep: only consider stmts that include this string -h HOSTNAME  mysql机器名或者IP；
hostname of db server for *-slow.log filename (can be wildcard),default is '*', i.e. match all -i NAME      name of server instance (if using mysql.server startup script) -l  总时间中不减去锁定时间；
don't subtract lock time from total time

6.4 其他操作

- 查看表结构
    desc table_name;
 - 查看生成表的SQL
    show create table table_name;
 - 查看索引
    show index from  table_name;
 - 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;