mysql的索引

2022/5/29 2:19:41

编程Tag： mysql table select 索引 id name

本文主要是介绍mysql的索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。正确合理的使用索引可以大大提高MySQL的检索速度，但过多的使用索引将会造成滥用。索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。
- FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
  
  全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
- 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
  
  HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
- BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
- RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
索引种类：

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个组合索引【坑】：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（索引合并：使用多个单列索引组合搜索）

操作索引：

创建索引

#创建普通索引
CREATE INDEX index_name ON table_name(col_name);
#创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
#创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
#创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

通过修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

创建表时直接指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

删除索引

#直接删除索引
DROP INDEX index_name ON table_name;
#修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

创建索引的时机：

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
```
SELECT t.Name  FROM mytable_t t LEFT JOIN mytable_m m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;
```
此时就需要对city和age建立索引，由于mytable_m表的userame也出现在了JOIN子句中，也有对它建立索引的必要。刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。

命中索引:

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。错误举例：

#like '%xx'    
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)
- 连表时注意条件类型需一致
- 索引散列值（重复多）不适合建索引，例：性别不适合

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;

这篇关于mysql的索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

mysql的索引

相关编程文章