MySQL索引

目录

1、什么是索引？

2、索引的特点

3、索引的缺点

4、索引的分类

（1）主键索引

（2）唯一索引

（3）常规索引

（4）全文索引

5、主键索引

6、唯一索引

7、常规索引

8、全文索引 

9、优化原则—创建索引

（1）强制

（2）建议 

10、优化原则—SQL编写

（1）强制

（2）建议 

11、优化原则—索引失效

---

1、什么是索引？

        就好比你想查一个字，然后你通过新华字典的目录信息快速的找到了这个字，呢新华字典就相当于是索引。

        索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2、索引的特点

（1）高效性

        利用索引可以提高数据库的查询效率。

（2）唯一性

        索引可以确保所查的数据的唯一性。

（3）完整性

        用户可以加速表和表之间的连接， 实现表与表之间的参照完整性。

（4）特殊能力

        通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。

3、索引的缺点

（1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。

（2）因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

（3）如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

4、索引的分类

（1）主键索引

        在数据库关系图中为表定义一个主键将自动创建主键索引。

（2）唯一索引

        不允许具有索引值相同的行，从而禁止重复的索引或键值。

（3）常规索引

        最基本的索引类型，没有唯一性之类的限制。

（4）全文索引

        搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。

5、主键索引

（1）语法格式

CREATE TABLE `表名` (
`字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(`字段1`)
)

 （2）说明

• 某一个属性组能唯一标识一条记录；
• 最常见的索引类型；
• 确保数据记录的唯一性；
• 确定特定数据记录在数据库中的位置。

（3）代码示例

create table test(
    # auto_increment设置主键自增
	line1 int(11) auto_increment primary key
)

（4）实战实例

         现有一条数据量900万数据的一张表，对齐查询eid为200000的数据记录。表结构为：

CREATE TABLE `emp` (
  `eid` bigint(20) DEFAULT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `esex` varchar(5) DEFAULT NULL,
  `ebirthday` datetime DEFAULT NULL,
  `ehisday` datetime DEFAULT NULL,
  `job` varchar(15) DEFAULT NULL,
  `emoney` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

         直接对其查询：

select * from emp where eid = 200000;
# 时间: 11.306ms

        添加查询字段细节对齐查询：

select eid,ename,esex from emp where eid = 200000;
# 时间: 10.072ms

        添加主键索引对其查询：

alter table emp add primary key(eid);
select * from emp where eid = 200000;
# 时间: 0.003ms

        由此可见索引对查询效率直接性的提高了。 

6、唯一索引

（1）语法格式

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(`字段1`)
)

（2）说明

• 快速定位特定数据；
• index和key关键字都可设置常规索引；
• 应加在查找条件的字段；
• 不宜添加太多常规索引，影响数据的插入、删除和修改操作。

（3）示例代码 

create table test(
	line1 int(11) auto_increment primary key,
	line2 int(11) not null unique
)

唯一索引数据不能重复，所以当重复时必然报错

7、常规索引

（1）语法格式

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL,
INDEX/KEY(`字段1`)
)

（2）说明

• 避免同一个表中某数据列中的值重复；
• 对比主键索引只能有一个,唯一索引可有多个。

（3）示例代码 

create table test(
	line1 int(11) auto_increment primary key,
	line2 int(11) not null unique,
	line3 int(11) not null,
	line4 int(11) not null,
	index(line3,line4)
)

8、全文索引 

（1）语法格式

CREATE TABLE `表名` (
`字段1` VARCHAR(32) NOT NULL,
fulltext key (字段名，字段名，字段名) with parser ngram
)ENGINE=innodb

（2）用法

SELECT <字段表> FROM <表名> 
WHERE MATCH(字段) 
AGAINST (‘要搜索的关键词’);

（3）注意事项

• Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。
• 只有char、varchar、text类型字段能创建全文索引。
• 当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。
• Mysql内置ngram解析器，可以解析中日韩三国文字。有汉字的一定要启用它。
• 英文分词用空格，逗号；中文分词用 ngram_token_size设定。
• 注意需要在配置文件中加入：[mysqld] ngram_token_size=2

（4）示例代码

        创建表：

create table wenzhang(
	wid int PRIMARY KEY auto_increment,
	title varchar(20),  
	content text, 
	zuozhe varchar(20), 
	fulltext key(title,content,zuozhe) with parser ngram -- 中日韩
);

        插入数据：

insert into wenzhang(title,content,zuozhe) 
values
('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');

         查看含有标题或者作者含有西安字段的记录：

select * from wenzhang where match(title,zuozhe) AGAINST('西安');

9、优化原则—创建索引

（1）强制

• InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新 。
• InnoDB和MyISAM存储引擎表，索引类型必须为BTREE 。

（2）建议 

• 主键的名称以 pk 开头，唯一键以 uni 或 uk 开头，普通索引以 idx 开头，一律使用小写格式，以字段的名称或缩写作为后缀。
• 多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。
• 单个表上的索引个数 不能超过6个 。
• 在建立索引时，多考虑建立联合索引 ，并把区分度最高的字段放在最前面。
• 在多表JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
• 建表或加索引时，保证表里互相不存在冗余索引 。 比如：如果表里已经存在key(a,b)， 则key(a)为冗余索引，需要删除。

10、优化原则—SQL编写

（1）强制

• 程序端SELECT语句必须指定具体字段名称，禁止写成 *。

（2）建议 

• 程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
• 除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
• INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很 快，但会引起主从同步延迟。
• SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以 内。
• 线上环境，多表 JOIN 不要超过5个表。
• 减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果 集请保持在1000行以内，否则SQL会很慢。
• 对单表的多次alter操作必须合并为一次 对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整 合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极 大影响。
• 批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
• 事务里包含SQL不超过5个。 因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
• 事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

11、优化原则—索引失效

• 最佳左前缀法则
• 主键插入顺序
• 计算、函数导致索引失效
• 类型转换导致索引失效
• 范围条件右边的列索引失效
• 不等于(!= 或者<>)索引失效
• is null可以使用索引，is not null无法使用索引
• like以通配符%开头索引失效
• OR 前后存在非索引的列，索引失效
• 数据库和表的字符集统一使用utf8mb4