浅谈MYSQL之索引INDEX
索引概念
索引:
-
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
-
是一种特殊数据结构,定义在查找时作为查找条件的字段,在MySQL又称为键key,索引通过存储引擎实现。
-
本质是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
-
优点:
- 索引可以降低服务需要扫描的数据量,减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序I/O
-
缺点:占用额外空间,影响插入速度
索引类型
- B+ TREE、HASH、R TREE
- 聚簇(集)索引、非聚簇索引:数据和索引是否存储在一起
- 聚簇索引:索引 和 数据文件为同一个文件。
- 非聚簇索引: 索引 和 数据文件分开的索引
- 主键索引、二级(辅助)索引
- 稠密索引、稀疏索引:是否索引了每一个数据项
- 简单索引、组合索引
- 左前缀索引:取前面的字符做索引
- 覆盖索引:从索引中即可取出要查询的数据,性能高
索引分类
- 普通索引index :加速查找
通过ALTER TABLE table_name ADD INDEX index_name (column);
创建普通索引 - 唯一索引
- 主键索引:primary key :加速查找+约束(不为空且唯一)
- 唯一索引:unique:加速查找+约束 (唯一)
- 联合索引
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);
创建组合索引:
- 全文索引fulltext :在文本中查找关键词,而不是直接比较索引中的值,类似搜索引擎,用于搜索很长一篇文章的时候,效果最好。
- 空间索引spatial :了解就好,几乎不用
索引结构
二叉树
- 根节点,树枝节点不存储数据,只存储索引
- 树叶节点存储数据
B-Tree
- B-TREE 每个节点都是一个二元数组: [key, data],所有节点都可以存储数据。key为索引key,data为除key之外的数据
- 检索原理:首先从根节点进行二分查找,如果找到则返回对应节点的data,否则对相应区间的指针指向的节点递归进行查找,直到找到节点或未找到节点返回null指针。
- 缺点:
- 1.插入删除新的数据记录会破坏B-Tree的性质,因此在插入删除时,需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。造成IO操作频繁。
- 2.区间查找可能需要返回上层节点重复遍历,IO操作繁琐。
B+Tree
- B+Tree是B-Tree优化后的进一步设计,结构如上图所示.与B-Tree相比,B+Tree有以下不同点:
- 非叶子节点不存储data,只存储索引key;
- 只有叶子节点才存储data。
- 增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能
- 按顺序存储,每一个叶子节点到根结点的距离是相同的;
- 左前缀索引,适合查询范围类的数据
- B+TREE 只在叶子节点存储数据和所有叶子结点包含一个链指针以及其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围,先定位索引再通过索引高效快速定位数据
可以使用B+Tree索引的查询类型:
- 全值匹配:精确所有索引列,如:姓wang,名xiaochun,年龄30
- 匹配最左前缀:即只使用索引的第一列,如:姓wang
- 匹配列前缀:只匹配一列值开头部分,如:姓以w开头的
- 匹配范围值:如:姓ma和姓wang之间
- 精确匹配某一列并范围匹配另一列:如:姓wang,名以x开头的只访问索引的查询
B+Tree索引的限制:
- 如不从最左列开始,则无法使用索引,如:查找名为xiaochun,或姓为g结尾
- 不能跳过索引中的列:如:查找姓wang,年龄30的,只能使用索引第一列
- 特别提示:
索引列的顺序和查询语句的写法应相匹配,才能更好的利用索引
为优化性能,可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
Mysql 索引实现
MyISAM & InnoDB 都使用B+Tree索引结构。但是底层索引存储不同,MyISAM 采用非聚簇索引,而InnoDB采用聚簇索引。
MyISAM索引
原理:
- 采用非聚簇索引-MyISAM myi索引文件和myd数据文件分离,
- 索引文件仅保存数据记录的指针地址。
- 叶子节点data域存储指向数据记录的指针地址。
- 底层存储结构: frm -表定义、 myi -myisam索引、 myd-myisam数据
执行过程
- MyISAM索引按照B+Tree搜索,如果指定的Key存在,则取出其data域的值,
- 然后以data域值-数据指针地址去读取相应数据记录。
- 辅助索引和主索引在结构上没有任何区别,只是主索引要求key是唯一的,
- 辅助索引的key可以重复。MyISAM索引树如下:
InnoDB索引
InnoDB优势: 高扩展性,充分发挥硬件性能、 Crash Safe、 支持事务、 可以在线热备份
InnoDB特性:
- 1.事务支持(ACID)
- 2.扩展性优良
- 3.读写不冲突
- 4.缓存加速
InnoDB功能组件:
- redo/undo
- 异步IO
- MVCC
- 行级别锁
- Page Cache(LRU)
InnoDB物理存储结构
- InnoDB物理存储文件结构说明:
- InnoDB以表空间Tablespace(ibd文件)结构进行组织,
- 每个Tablespace 包含多个Segment段,
- 每个段分为2种段:叶子节点Segment&非叶子节点Segment
- 一个Segment段包含多个Extent,一个Extent占用1M空间包含64个Page(每个Page 16k)
- InnoDB B-Tree 一个逻辑节点就分配一个物理Page,一个节点一次IO操作。,一个Page里包含很多有序数据Row行数据,Row行数据中包含Filed属性数据等信息。
- 表插入数据扩展原理: 一次扩张一个Extent空间(1M),包含64个Page,按照顺序结构向每个page中插入顺序。
InnoDB索引原理:
- 采用聚簇索引- InnoDB数据和索引文件为一个idb文件,表数据文件本身就是主索引,相邻的索引临近存储。
- 叶节点data域保存了完整的数据记录(数据[除主键id外其他列data]+主索引[索引key:表主键id])。
- 叶子节点直接存储数据记录,以主键id为key,叶子节点中直接存储数据记录。
- 底层存储结构: frm -表定义、 ibd: innoDB数据&索引文件
注意
- 由于InnoDB采用聚簇索引结构存储,索引InnoDB的数据文件需要按照主键聚集,
- 因此InnoDB要求表必须有主键(MyISAM可以没有)。
- 如果没有指定mysql会自动选择一个可以唯一表示数据记录的列作为主键,
- 如果不存在这样的列,mysql自动为InnoDB表生成一个隐含字段(6个字节长整型)作为主键。
- InnoDB的所有 辅助索引 都引用 数据记录的主键 作为data域。
聚簇索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:
- 首先检索辅助索引获得数据记录主键,
- 然后用主键到主索引中检索获得数据记录。
InnoDB聚簇索引结构
索引查找流程:
- 1.索引精确查找: 确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中。(select * from user_info where id = 23)
- 索引范围查找:读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=22 (select * from user_info where id >= 18 and id < 22)
- 全表扫描:直接读取叶节点头结点, 顺序扫描, 返回符合条件记录, 到最终节点结束
(select * from user_info where name = ‘ab’)
- 二级索引查找
通过二级索引查出对应主键,拿主键回表查主键索引得到数据,二级索引可筛选掉大量无效记录,提高效率
索引优化
索引优化使用
- 独立地使用列:尽量避免其参与运算,独立的列指索引列不能是表达式的一部分,也不能是函数的参数,在where条件中,始终将索引列单独放在比较符号的一侧
- 左前缀索引:构建指定索引字段的左侧的字符数,要通过索引选择性来评估
- 索引选择性:不重复的索引值和数据表的记录总数的比值
- 多列索引:AND操作时更适合使用多列索引,而非为每个列创建单独的索引
- 选择合适的索引列顺序:无排序和分组时,将选择性最高放左侧
索引优化建议
- 只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
- 尽量使用短索引,如果可以,应该制定一个前缀长度
- 对于经常在where子句使用的列,最好设置索引
- 对于有多个列where或者order by子句,应该建立复合索引
- 对于like语句,以 % 或者 ‘-’ 开头的不会使用索引,以% 结尾会使用索引
- 尽量不要在列上进行运算(函数操作和表达式操作)
- 尽量不要使用not in和<>操作
SQL语句性能优化
- 查询时,能不要就不用,尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多表连接时,尽量小表驱动大表,即小表 join 大表
- 在有大量记录的表分页时使用 limit
- 对于经常使用的查询,可以开启缓存
- 多使用 explain 和 profile 分析查询语句
- 查看慢查询日志,找出执行时间长的sql语句优化
EXPLAIN分析索引工具
- 语法:
EXPLAIN [explain_type] SELECT select_options
- EXPLAIN输出信息说明:
管理索引
创建索引
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
使用ALTER 命令添加索引
- 有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
删除索引
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
查看索引
SHOW INDEXES FROM [db_name.]tbl_name;
优化表空间
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;
实验:索引的使用
- 准备工作:建立一个很大的表
# 创建一个表
CREATE TABLE employees (ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(10) NOT NULL,AGE TINYINT UNSIGNED,GENDER ENUM('M','F'),MAJOR VARCHAR(50));
# 使用脚本添加500条数据
[root@Centos7 mysql]# GENDER=("M" "F")
[root@Centos7 mysql]# for i in {1..500};do mysql -uroot -pcaiji -e "INSERT INTO hellodb.employees (NAME,AGE,GENDER) VALUES ('emp$i','$[$RANDOM%43+18]','${GENDER[$RANDOM%2]}')"; done
# 注意上述for循环使用库的准确位置hellodb.employees
MariaDB [hellodb]> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 500 |
+----------+
1 row in set (0.00 sec)
- 分析查询语句
EXPLAIN SELECT * FROM exployees WHERE AGE>50;
MariaDB [hellodb]> EXPLAIN SELECT NAME FROM employees WHERE AGE>50;
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 500 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
# elect_type='SIMPLE'表示当前为简单查询
# type=ALL 表示当前为全局搜索,没有使用索引
# rows 表示使用的行数500
- 创建索引
#创建age单字段索引
MariaDB [hellodb]> CREATE INDEX inx_age ON employees(AGE);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
#创建AGE NAME 多字段索引
MariaDB [hellodb]> CREATE INDEX inx_age_and_name ON employees(AGE,NAME);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#创建NAME AGE 多字段索引
MariaDB [hellodb]> CREATE INDEX inx_name_and_age ON employees(NAME,AGE);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看全部索引信息
MariaDB [hellodb]> SHOW INDEX FROM employees;
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | ID | A | 500 | NULL | NULL | | BTREE | | |
| employees | 1 | inx_age | 1 | AGE | A | 100 | NULL | NULL | YES | BTREE | | |
| employees | 1 | inx_age_and_name | 1 | AGE | A | 100 | NULL | NULL | YES | BTREE | | |
| employees | 1 | inx_age_and_name | 2 | NAME | A | 500 | NULL | NULL | | BTREE | | |
| employees | 1 | inx_name_and_age | 1 | NAME | A | 500 | NULL | NULL | | BTREE | | |
| employees | 1 | inx_name_and_age | 2 | AGE | A | 500 | NULL | NULL | YES | BTREE | | |
+-----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- 分析查询语句
EXPLAIN SELECT NAME FROM expolyees WHERE AGE>50;
MariaDB [hellodb]> EXPLAIN SELECT NAME FROM employees WHERE AGE>50;
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | employees | range | inx_age,inx_age_and_name | inx_age_and_name | 2 | NULL | 110 | Using where; Using index |
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
/ type='range' 表示根据索引进行了范围搜索
/ possible_keys='inx_age,inx_age_and_name' 表示可能用到的索引为二者
/ key='inx_age_and_name' 表示真正用到的索引
/ 由于搜索字段为AGE,索引age, age_and_name都是以字段AGE开始,故系统认为他们可能被用到;
/ 而由于age_and_name索引包含了查询结果字段,故被选做实际用到的索引
- 分析查询语句
EXPLAIN SELECT NAME FROM exployees WHERE NAME LIKE 'emp1%';
MariaDB [hellodb]> EXPLAIN SELECT NAME FROM employees WHERE NAME LIKE 'emp1%';
+------+-------------+-----------+-------+------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+------------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | employees | range | inx_name_and_age | inx_name_and_age | 32 | NULL | 111 | Using where; Using index |
+------+-------------+-----------+-------+------------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
# 此时用搜索条件是NAME,故而只用到了一个索引'index_name_and_age'
- 分析符合查询语句
EXPLAIN SELECT NAME,AGE FROM exployees WHERE AGE<(SELECT avg(AGE) FROM employees) ;
MariaDB [hellodb]> EXPLAIN SELECT NAME,AGE FROM employees WHERE AGE<(SELECT avg(AGE) FROM employees);
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
| 1 | PRIMARY | employees | index | inx_age,inx_age_and_name | inx_age_and_name | 34 | NULL | 500 | Using where; Using index |
| 2 | SUBQUERY | employees | index | NULL | inx_age | 2 | NULL | 500 | Using index |
+------+-------------+-----------+-------+--------------------------+------------------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
# select_type 这个字段描述标明了该查询语句的主从关系
# type ='index'并Extra='Using index' 表名该语句使用了覆盖索引,效率极高
- 分析使用主键查询语句
EXPLAIN SELECT NAME,AGE FROM employees WHERE ID=50;
MariaDB [hellodb]> EXPLAIN SELECT NAME,AGE FROM employees WHERE ID=50;
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
/ type='const' 表示查询条件为精确查询某个数,并且返回结果只有一行。通常是主键查询
- 分析精准查询语句
EXPLAIN SELECT NAME,AGE FROM employess WHERE AGE=30;
MariaDB [hellodb]> EXPLAIN SELECT NAME,AGE FROM employees WHERE AGE=30;
+------+-------------+-----------+------+--------------------------+------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+--------------------------+------------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | inx_age,inx_age_and_name | inx_age_and_name | 2 | const | 8 | Using index |
+------+-------------+-----------+------+--------------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec
/ type='ref'表示查询属于精确查询,查询条件匹配单个值,而匹配结果可能不止一个
- 删除索引
MariaDB [hellodb]> DROP INDEX inx_age_and_name ON employees;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> ALTER TABLE employees DROP INDEX inx_name_and_age;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> SHOW INDEX FROM employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | ID | A | 500 | NULL | NULL | | BTREE | | |
| employees | 1 | inx_age | 1 | AGE | A | 100 | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
/ 两种办法删除索引可以达到同样的效果
- 验证索引左前缀特性
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE NAME LIKE 'Y%';
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | students | range | inx_NAME | inx_NAME | 152 | NULL | 3 | Using index condition |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE NAME LIKE '%Y%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE NAME LIKE '%Y';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
/ 以Y%开头的条件用到了NAME索引
/ 其他的条件索引没有生效
- 验证复合索引就左原则
MariaDB [hellodb]> EXPLAIN SELECT * FROM employees WHERE NAME='emp100';
+------+-------------+-----------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employees | ref | inx_name | inx_name | 32 | const | 15 | Using index condition |
+------+-------------+-----------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> EXPLAIN SELECT * FROM employees WHERE ID=100 AND NAME='emp100';
+------+-------------+-----------+-------+------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------+------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | PRIMARY,inx_name | PRIMARY | 4 | const | 1 | |
+------+-------------+-----------+-------+------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
/ ID条件在前,优先使用了ID索引
完毕