MySQL 索引

数据库优化 学习笔记

一、索引的简介

1.1、什么是索引

        索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
        更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.2、索引的出现

        一般的应用系统对比数据库的读写比例在 10:1 左右(即有10次查询操作时有1次写的操作)，而且插入操作和更新操作很少出现性能问题，遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
        当数据库中数据量很大时，查找数据会变得很慢。

        解决办法： 使用索引

1.3、索引的目的

        索引的目的在于提高查询效率，可以类比字典，如果要查 "mysql" 这个单词，我们肯定需要定位到 m 字母，然后从上往下找到 y 字母，再找到剩下的 sql。

1.4、索引的原理

        除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

        数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。

---

二、索引操作

2.1、查看索引

	show index from 表名

2.2、创建索引

• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致；
• 字段类型如果不是字符串，可以不填写长度部分。
• 索引主要有单值索引（用一列创建索引）、复合索引（用多列创建索引）、唯一索引（这一列的数据不能重复）

	create index 索引名称 on 表名(字段名称(长度))

2.3、删除索引

	drop index 索引名称 on 表名;

注意：

• MySQL 会默认为表单主键添加索引
• 索引创建之后，使用 select 语句查询时回自带使用，所以查询语句不变
  
  

---

三、索引案例

3.1、创建一个表 Demo 用以测试

	create table Demo (id);

3.2、使用 Python 程序向表中加入 100000 条数据

from pymysql import *

conn = connect(host='localhost',port=3306,user='root',passwd='',db='php',charset='utf8')
cur = conn.cursor()
for i in range(100000):
    sql = f"insert into Demo values({i})"
    # print(sql)
    cur.execute(sql)

conn.commit()

3.3、开启运行时间检测

	set profiling=1;
	-- set profiling=0;								-- 这是关闭

3.4、查找 id=99999 的数据

	-- 没有设置主键, 所以默认没有索引
	select * from Demo where id=99999;

3.5、为表 Demo 的 id 列创建索引 idx_D

	create index idx_D on Demo(id);

3.6、再次查找 id=99999 的数据

	select * from Demo where id=99999;

3.7、查看执行的时间

	show profiles;

结果： 增加了索引后，速度提高了很多。但是并不是所有情况建立索引都可以提高效率。

---

四、索引的建立情况

4.1、适合建立索引的情况

1. 主键自动建立索引；
2. 频繁作为查询条件的字段应该建立索引；
3. 查询中与其他表关联的字段，外键关系建立索引；
4. 在高并发的情况下创建复合索引；
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 (建立索引的顺序跟排序的顺序保持一致)。
   
   

4.2、不适合建立索引的情况

1. 频繁更新的字段不适合建立索引；
2. where 条件里面用不到的字段不创建索引；
3. 表记录太少，当表中数据量超过三百万条数据，可以考虑建立索引；
4. 数据重复且平均的表字段（比如，性别、国籍）。