4-Mysql-索引简述

最新推荐文章于 2023-07-02 00:12:50 发布

小崔侃大山

最新推荐文章于 2023-07-02 00:12:50 发布

阅读量87

点赞数

分类专栏： Mysql 文章标签： mysql sql 索引数据库

本文链接：https://blog.csdn.net/weixin_42985717/article/details/113099807

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文知识点来源于网络，仅限学习笔记使用，记录学习过程与心得，如有不适，请联系。

一、性能下降（执行时间长）原因

sql语句编写问题
各种连接，子查询导致建立的索引没有用上，甚至于根本没有建立索引。
关联查询过多。
服务器相关参数设置不合理，导致性能下降。

二、索引概述

2.1 什么是索引

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。

2.2 为什么要建立索引

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引就是一种排好序的快速查找数据结构。

2.3 索引的优缺点

优点：
提高减速数据的效率，降低数据库的IO消耗，通过索引对数据项进行排序，降低了数据排序成本，降低了CPU消耗。
缺点：

索引本身也很大，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引往往以文件的形式存储到磁盘上，所以索引也要占磁盘空间。
2.更新表数据时，不进要保存数据，还要保存一下索引文件每次更新了索引列的字段，会调整因为更新所带来的键值变化后索引的信息，所以虽然索引提高了查询效率，但也降低了更新表的速度。

2.4 索引分类

单值索引
一个索引只包含一个列，一个表可以有多个单值索引，一般来说，一个表不要超过五个单值索引。

#外部创建
CREATE INDEX [indexname]ON mytable(colname);

#创建表的时候创建
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
KEY|INDEX [indexName] (username(length))  
);  

#alter语句添加
ALTER table tableName ADD INDEX indexName(columnName)

唯一索引
索引列的值必须唯一，但允许有空值。

#创建表时添加,可以使用标记约束和列级约束
CREATE TABLE mytable(  
ID INT NOT NULL,   UNIQUE
username VARCHAR(16) NOT NULL,  
UNIQUE [KEY|INDEX] [indexName] (rowname(length))  
); 

#使用alter 语句
ALTER TABLE T1  ADD  UNIQUE [indexname] (rowname(length))

#外部创建
CREATE UNIQUE INDEX [indexname] ON TABLENAME(rowname(length))

复合索引
一个索引包含多个列

CREATE TABLE `mytable` (
'aaa' varchar(16) NOT NULL default '', 
'bbb' varchar(16) NOT NULL default '', 
'ccc' int(11) UNSIGNED NOT NULL default 0,
 KEY|INDEX [indexName] (`aaa`,`bbb`,`ccc`) 
 );

#使用alter 语句
alert table mytableadd INDEX `sindex` (`aaa`,`bbb`,`ccc`)

全文索引
Mysql全文索引是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

CREATE TABLE `mytableadd ` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `title` varchar(200) DEFAULT NULL,

  `content` text,

  PRIMARY KEY (`id`),

  FULLTEXT KEY|INDEX `title` (`title`,`content`)

);

#使用alter 语句
ALTER TABLE mytableadd ADD FULLTEXT INDEX fulltext_mytableadd (title,content);

三、索引为什么能快速查找数据

在我们存放数据时，如果建立索引，数据库系统会维护一个满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，可以再这些数据结构之上，实现高级查找算法，这种结构就是索引。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘上，为了加快数据的查找，可以维护二叉树，每个节点分别包含索引键值和一个执行对应数据的物理地址指针，这样就可以运用二叉树在一定的复杂度内获取相应的数据，从而快速的减速出符合条件的记录。
除了二叉树还有BTree索引，哈希索引。

未完待续。。。