目录什么是 MySQL 索引索引提高查询效率的直观感受基于 B 树的 MySQL 索引聚簇索引 VS 非聚簇索引主键索引 VS 非主键索引索引操作创建索引创建索引的建议创建主键索引创建唯一键索引创建普通索引创建联合索引创建前缀索引创建全文索引查看索引删除索引什么是 MySQL 索引MySQL 的服务器是运行在内存的所有的数据库的 CURD 操作也都是在内存中进行的索引也是如此。提高算法效率的因素1、组织数据的方式 2、算法本身。举例对于一个数组要找一个元素可以采用线性遍历的方式。但如果这个数组是有序的组织数据的方式就可以用二分查找算法本身提高算法效率。还可以将数组换成哈希结构组织数据的方式就可以用哈希算法算法本身提高算法效率。MySQL 的索引也是同样的道理它是运行在内存的特定数据结构以及与它配套的算法它的作用就是提高 MySQL 的查询效率.MySQL 通过操作系统与磁盘交互的基本单位是 16 KB。索引提高查询效率的直观感受接下来创建一个有 80 万行的员工信息表查找这个表的某一行查询员工编号为998877的员工select * from EMP where empno998877; 1 row in set (4.93 sec)可以看到耗时4.93秒这还是在本机一个人来操作在实际项目中如果放在公网中假如同时有 1000个人并发查询那很可能就死机。现在给 empno 这一列加上索引alter table EMP add index(empno);再查询员工编号为998877的员工select * from EMP where empno998877; 1 row in set (0.01 sec)可以看到只耗时0.01秒基于 B 树的 MySQL 索引MySQL 通过操作系统与磁盘交互的基本单位是 16 KB。这个基本数据单元在 MySQL 这里叫做 page注意和系统的page区分MySQL 中的数据文件是以 page 为单位保存在磁盘当中的。MySQL 的 CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据。 而只要涉及计算就需要CPU参与而为了便于CPU参与一定要能够先将数据移动到内存当中。 所以在特定时间内数据一定是磁盘中有内存中也有。后续操作完内存数据之后以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互也就是IO了。而此时IO的基本单位就是Page。为了更好的进行上面的操作 MySQL 服务器在内存中运行的时候在服务器内部就申请了被称 Buffer Pool 的大内存空间来进行各种缓存。其实就是很大的内存空间来和磁盘数据进行IO交互。 为l更高的效率一定要尽可能的减少系统和磁盘IO的次数create table if not exists user ( id int primary key, age int not null, name varchar(16) not null ); mysql show create table user \G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE user ( id int(11) NOT NULL, age int(11) NOT NULL, name varchar(16) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 --默认就是InnoDB存储引擎 1 row in set (0.00 sec) mysql insert into user (id, age, name) values(3, 18, 杨过); Query OK, 1 row affected (0.01 sec) mysql insert into user (id, age, name) values(4, 16, 小龙女); Query OK, 1 row affected (0.00 sec) mysql insert into user (id, age, name) values(2, 26, 黄蓉); Query OK, 1 row affected (0.01 sec) mysql insert into user (id, age, name) values(5, 36, 郭靖); Query OK, 1 row affected (0.00 sec) mysql insert into user (id, age, name) values(1, 56, 欧阳锋); Query OK, 1 row affected (0.00 sec) mysql select * from user; -------------------- | id | age | name | -------------------- | 1 | 56 | 欧阳锋 | | 2 | 26 | 黄蓉 | | 3 | 18 | 杨过 | | 4 | 16 | 小龙女 | | 5 | 36 | 郭靖 | -------------------- 5 rows in set (0.00 sec)通过观察我们发现向一个具有主键约束的表中乱序插入数据数据会自动排序中断一下---为何IO交互要是 Page局部性原理为何 MySQL 和磁盘进行 IO 交互的时候要采用 Page 的方案进行交互呢?用多少加载多少不香吗? 如上面的 5 条记录如果 MySQL 要查找 id2 的记录第一次加载 id1第二次加载 id2一次一条记录那么就需要 2 次 IO。如果要找 id5那么就需要 5 次 IO。 但如果这 5 条(或者更多)都被保存在一个 Page 中(16KB能保存很多记录),那么第一次 IO 查找 id2 的时候整个 Page 会被加载到 MySQL 的 Buffer Pool 中这里完成了一次 IO。但是往后如果在查找 id1,3,4,5 等完全不需要进行 IO 了而是直接在内存中进行了。所以就在单 Page 里面大大减少了 IO 的次数。 你怎么保证用户一定下次找的数据就在这个 Page 里面我们不能严格保证但是有很大概率因为有局部性原理。 往往 IO 效率低下的最主要矛盾不是 IO 单次数据量的大小而是 IO 的次数。理解单个Page既然MySQL 通过操作系统与磁盘交互的基本单位是 page那么一段时间内内存一定存在多个 pageMySQL 要管理好所有的 page就要先描述 page再用特定的数据结构组织 page我们先了解单个 page 的构成单个 page 的构成prev 和 next 构成双向链表实际肯定更加复杂里面的数据记录也构成一个单向链表。理解多个Page对书的目录的理解如果要看书的某一个章节可以从第一页逐页翻到目标章节也可以查看目录然后一下就翻到目标章节显然第二种方式更快。书中的目录是多花了纸张的但是却提高了效率所以目录是一种“空间换时间的做法”。单页情况针对上面的单页Page我们能否也引入目录呢当然可以要查找 id4 的记录之前必须线性遍历4次 才能拿到结果。现在直接通过目录2[3]直接进行定位新的起始位置现在只需要线性遍历两次提高了效率。通过目录快速定位的前提是 id 必须是有序的如同书的页码是有序的这也解释了上面为什么具有主键约束的表中乱序插入数据数据会自动排序其实是为构建目录做准备。多页情况既然单页内部可以构建目录那么页与页之间也可以构建目录。使用一个目录项来指向某一页而这个目录项存放的就是将要指向的页中存放的最小数据的键值。和页内目录不同的地方在于这种目录管理的级别是页而页内目录管理的级别是行。其中每个目录项的构成是键值指针。图中没有画全。如果页目录仍然有很多我们还可以给页目录加目录我称之为“目录的目录”以上构建目录的过程就叫作构建主键索引。上面所示的数据结构与 B 树十分相像它是大多数存储引擎所采用的常见存储结构。这种存储结构提高搜索效率的原因显而易见查找的本质是排除这种存储结构除了“叶子结点”是有效数据其他都是“目录结点”。从算法的角度通过目录查找每次都排除一大批数据提高了效率从 IO 的角度找到目的行所在的 page 途径更少的目录 page减少了 IO 次数提高了效率上图就称为 MySQL innodb 下的索引结构并且对所有表的 CURD都是对上面的结构进行 CURD。问题如果在建表的时候没有指明那一列是主键那么这张表的也是像上面一样存储吗答案是也是像上面一样存储的如果没有指明那一列是主键MySQL 会对该表添加一个隐藏列作为主键InnoDB 在建立索引结构来管理数据的时候其他数据结构为何不行链表肯定不行它是暴力线性遍历二叉搜索树或者AVL 红黑树还是没有 B 树优秀一是二叉搜索树的退化问题可能退化成为线性结构二是它们的树高都比 B 树更高这意味着在查找的时候 IO 次数的增多。Hash官方的索引实现方式中 MySQL 是支持 HASH 的不过 InnoDB 和 MyISAM 并不支持.Hash的算法特征决定了虽然有时候也很快(O(1))不过在面对范围查找就明显不行另外还有其他差别有兴趣可以查一下。B 树B 树的非叶子结点也要存储有效数据那么非叶子结点存储的目录就变少了 树的高度就比 B 树高这意味着在查找的时候 IO 次数的增多。并且 B 树的叶子节点全部相连而 B 没有聚簇索引 VS 非聚簇索引MyISAM 存储引擎同样使用 B 树作为索引结果与 innodb 不同的是叶节点的 data 域存放的是数据记录的地址这种存储方式称为非聚簇索引而像 innodb 那样叶节点的 data 域存放的是有效数据的存储方式称为聚簇索引。下图为MyISAM 存储引擎创建的表的主索引 Col1 为主键。从 Linux 文件系统角度的区别用 MyISAM 存储引擎创建一张表会创建三个文件分别存储表结构数据、该表的有效数据、该表的主键索引数据。而用 innodb 存储引擎创建一张表会创建两个文件分别存储表结构数据、表的有效数据以及主键索引数据。主键索引 VS 非主键索引主键索引被指定为 primary key 的列创建的索引和非主键索引没有指定为 primary key 的列创建的索引如 unique 索引和普通索引在不同的存储引擎下查询数据的方式有所不同。在 innodb 存储引擎下叶子节点存储的是索引列的值 对应的主键值。查询时先通过普通索引找到主键再拿着主键去主键索引中查完整数据这个过程叫回表。为何I nnoDB 针对这种辅助普通索引的场景不给叶子节点也附上数据呢原因就是太浪费空间了。在 MyISAM 存储引擎下叶子节点存储的是索引列的值 数据行的物理地址指针与主键索引的结构没有本质区别索引操作创建索引创建索引的建议适合创建索引的列1. 频繁出现在WHERE条件中的列这是最直接的判断标准。查询条件中用得越多的列越值得建索引。-- 如果这类查询很多user_id 就非常适合建索引 SELECT * FROM orders WHERE user_id 123;2. 频繁用于JOIN连接的列连接条件ON子句中的列如果没有索引MySQL 需要对被连接表做全表扫描性能极差。-- orders.user_id 和 users.id 都应该有索引 SELECT * FROM orders JOIN users ON orders.user_id users.id;3. 频繁用于ORDER BY或GROUP BY的列索引本身是有序的可以直接利用索引顺序来排序避免filesort文件排序性能很差。-- 如果经常按 create_time 排序这个列就适合建索引 SELECT * FROM logs ORDER BY create_time DESC;4. 区分度选择性高的列区分度 COUNT(DISTINCT 列名) / COUNT(*)值越接近 1索引效果越好。列区分度是否适合建索引id唯一1.0非常适合手机号接近 1.0非常适合性别男/女约 0.5不适合状态只有 3-5 种值很低通常不适合为什么区分度低的列不适合比如在性别上建索引查询WHERE gender 男会返回约 50% 的数据MySQL 优化器会认为全表扫描比用索引更划算因为用索引还要回表随机 I/O 太多最终根本不会走这个索引。5. 字符串列的前缀当字符串很长时如果要对长字符串如VARCHAR(200)建索引可以使用前缀索引只取前 N 个字符。CREATE INDEX idx_address ON users (address(20)); -- 只取前20个字符这样既能加速查询又能大幅节省存储空间和内存。不适合创建索引的列1. 很少出现在查询条件中的列索引是“以写换读”的优化手段。如果一个列几乎不被查询建索引只会拖慢写入速度浪费磁盘空间完全没有收益。2. 区分度极低的列如上所述性别、是否删除0/1、类型少数几种枚举这类列建索引基本没用。3. 频繁更新的列每次更新该列的值对应的索引树都要同步更新删除旧键值 插入新键值维护成本很高。如果查询中确实需要用到建议权衡利弊。4. 大文本或二进制列TEXT、BLOB这些列通常存储大量数据直接建索引会导致索引非常庞大且查询时无法有效利用索引除非使用全文索引FULLTEXT或指定前缀长度。5. 表的数据量很小如果一张表只有几百行数据全表扫描和走索引的代价几乎没差别甚至全表扫描更快因为走索引要多一次回表。这种情况下建索引是“杀鸡用牛刀”。创建主键索引第一种方式-- 在创建表的时候直接在字段名后指定primary key create table user1( id int primary key, name varchar(30) );第二种方式-- 在创建表的最后指定某列或某几列为主键索引 create table user2( id int, name varchar(30), primary key(id) );第三种方式create table user3( id int, name varchar(30) ); -- 创建表以后再添加主键 alter table user3 add primary key(id);主键索引的特点指定为 primary key 的列会自动创建主键索引这是一个强制且自动的行为不需要你额外执行任何CREATE INDEX语句一个表中最多有一个主键索引当然可以使复合主键主键索引的效率高主键不可重复创建主键索引的列它的值不能为 null且不能重复主键索引的列基本上是 int创建唯一键索引第一种方式-- 在表定义时在某列后直接指定 unique 唯一属性。 create table user4( id int primary key, name varchar(30) unique );第二种方式-- 创建表时在表的后面指定某列或某几列为 unique create table user5( id int primary key, name varchar(30), unique(name) );第三种方式create table user6( id int primary key, name varchar(30) ); -- 创建表以后再添加唯一键 alter table user6 add unique(name);唯一索引的特点指定为 unique 的列会自动创建主键索引这是一个强制且自动的行为不需要你额外执行任何CREATE INDEX语句一个表中可以有多个唯一索引查询效率高如果在某一列建立唯一索引必须保证这列不能有重复数据如果一个唯一索引上指定 not null等价于主键索引创建普通索引第一种方式create table user8( id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后指定某列为索引 );第二种方式create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); --创建完表以后指定某列为普通索引第三种方式create table user10(id int primary key, name varchar(20), email varchar(30)); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user10(name);普通索引的特点一个表中可以有多个普通索引普通索引在实际开发中用的比较多如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引创建联合索引第一种方式create table user8( id int primary key, name varchar(20), email varchar(30), index(name,email) --在表的定义最后指定某两列或多列为索引 );第二种方式create table user9( id int primary key, name varchar(20), email varchar(30) ); alter table user9 add index(name,email); --创建完表以后指定某两列或多列为联合索引第三种方式create table user10( id int primary key, name varchar(20), email varchar(30) ); -- 创建一个索引名为 idx_name 的联合索引 create index idx_name on user10(name,email);联合索引的特点联合索引在B树中不是把name和email分开存储成两个独立的索引而是将(name, email)组合成一个整体键值。排序规则是严格的“先主后次”首先按照name排序当name相同时再按照email排序。最左前缀原则查询条件必须包含联合索引的“最左前列”索引才会生效。比如WHERE name 张三只用第一列 索引生效WHERE name 张三 AND email zhanga.com用全两列 索引生效WHERE email zhanga.com跳过了最左列name索引不会使用 索引失效索引覆盖的优势因为联合索引的叶子节点存储的是(name, email, id)主键值会被自动追加所以如果你只查询这三个字段不需要回表直接从索引中就能拿到所有数据速度非常快。比如SELECT id, name, email FROM user8 WHERE name 张三;直接返回所有‘张三’的email创建前缀索引主要针对字符串列VARCHAR、CHAR、TEXT、BLOB且满足以下条件时该列长度较长比如存的是地址、文章摘要、日志信息。该列经常出现在WHERE条件中需要加速查询。前缀的区分度足够高取前几个字符就能基本区分出不同的行。前缀长度并不是越长越好也不是越短越好。判断标准是在保证足够区分度的前提下前缀越短越好。方式1 -- 创建索引时指定前缀长度 CREATE INDEX idx_name ON 表名 (列名(前缀长度)); 方式2 -- 建表时指定 CREATE TABLE users ( id INT PRIMARY KEY, address VARCHAR(200), INDEX idx_address (address(20)) -- 只取前20个字符 );案例-- 假设 users 表有 1000 万行address 列平均长度 50 个字符 -- 如果对整个 address 列建索引索引会非常庞大 CREATE INDEX idx_address ON users (address(10)); -- 只取前 10 个字符因为地址的前 10 个字符如“北京市海淀区”、“上海市浦东新”已经能过滤掉大部分数据索引大小却缩小了 5 倍。创建全文索引当对文章字段或有大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但是有 要求要求表的存储引擎必须是MyISAM而且默认的全文索引支持英文不支持中文。如果对中文进 行全文检索可以使用sphinx的中文版(coreseek)。CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engineMyISAM;INSERT INTO articles (title,body) VALUES (MySQL Tutorial,DBMS stands for DataBase ...), (How To Use MySQL Well,After you went through a ...), (Optimizing MySQL,In this tutorial we will show ...), (1001 MySQL Tricks,1. Never run mysqld as root. 2. ...), (MySQL vs. YourSQL,In the following database comparison ...), (MySQL Security,When configured properly, MySQL ...);如果使用如下查询方式虽然查询出数据但是没有使用到全文索引mysql select * from articles where body like %database%; ----------------------------------------------------------------- | id | title | body | ----------------------------------------------------------------- | 1 | MySQL Tutorial | DBMS stands for DataBase ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | ----------------------------------------------------------------- // 可以用explain工具看一下是否使用到索引 mysql explain select * from articles where body like %database%\G *************************** 1. row *************************** id: 1 select_type: SIMPLE 简单查询即没有使用子查询/多表查询等等 table: articles 在那张表查询 type: ALL 使用全盘查询 possible_keys: NULL key: NULL key为null表示没有用到索引 key_len: NULL ref: NULL rows: 6 Extra: Using where 1 row in set (0.00 sec)如何使用全文索引呢mysql SELECT * FROM articles- WHERE MATCH (title,body) AGAINST (database); ----------------------------------------------------------------- | id | title | body | ----------------------------------------------------------------- | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | ----------------------------------------------------------------- // 通过explain来分析这个sql语句 mysql explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST (database)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE 简单查询即没有使用子查询/多表查询等等 table: articles 在那张表查询 type: fulltext 使用全文索引 possible_keys: title key: title key用到了 title全文索引的索引名 key_len: 0 ref: rows: 1 Extra: Using where 1 row in set (0.00 sec)查看索引语法show index from 表名; // index 可以换成 keys //或者 desc 表名; // 信息比较简略示例mysql show index from goods\G *********** 1. row *********** Table: goods 表名 Non_unique: 0 0表示唯一索引 Key_name: PRIMARY 主键索引 Seq_in_index: 1 Column_name: goods_id 索引在哪列 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE 基于B树的索引 Comment: 1 row in set (0.00 sec)删除索引删除主键索引alter table 表名 drop primary key;其他索引(唯一键索引或者普通索引)的删除alter table 表名 drop index 索引名; // 索引名就是 show index from 表名 中的 Key_name 字段 // 或者 drop index 索引名 on 表名;
