mysql之高性能索引

mysql之高性能索引
mysql之高性能索引

当db的量达到一定数量级之后，每次进行全表扫描效率就会很低，因此一个常见的方案是建立一些必要的索引作为优化手段，那么问题就来了：
- 那么什么是索引呢？
- 索引的实现原理是怎样的？
- 我们通常说的聚集索引，非聚集索引的区别是什么？
- 如何创建和使用索引呢？
I. 索引介绍

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。简而言之,索引是数据结构

1. 几种树的结构

a. B+树

单来说就是一种为磁盘或者其他存储设备而设计的一种平衡二叉树,在B+tree中所有记录都按照key的大小存放在叶子结点上，各叶子结点直接用指针连接

b. 二叉树

二叉树的规则是父节点大于左孩子节点，小于右孩子节点

c. 平衡二叉树

首先是一个二叉树，但是要求任意一个节点的左右孩子节点的高度差不大于1

d. B树

首先是一个平衡二叉树，但是又要求每个叶子节点到根节点的距离相等

那么B树和B+树的区别是什么呢？
- B+树的叶子节点可以包含一个指针，指向另一个叶子节点
- B+树键值的拷贝存在非叶子节点；键值+记录存储在叶子节点
2. InnoDB引擎之B+树

mysql的InnnoDB引擎采用的B+树，只有叶子节点存储对应的数据列，有以下好处
- 叶子结点通常包含较多的记录，具有较高的扇出性(可理解为每个节点对应的下层节点较多)，因此树的高度较低(3~4)，而树的高度也决定了磁盘IO的次数，从而影响了数据库的性能。一般情况下，IO次数与树的高度是一致的
- 对于组合索引，B+tree索引是按照索引列名(从左到右的顺序)进行顺序排序的，因此可以将随机IO转换为顺序IO提升IO效率;并且可以支持order by group等排序需求;适合范围查询
3. hash索引

hash索引，相比较于B树而言，不需要从根节点到叶子节点的遍历，可以一次定位到位置，查询效率更高，但缺点也很明显
- 仅能满足"=","IN"和"<=>"查询，不能使用范围查询
- 无法进行排序
- 不支持部分索引
- hash碰撞
4. 聚集索引与非聚集索引

a. 聚集索引

InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，非叶子节点存放<key,address>，address就是下一层的地址

聚簇索引的结构图:

b. 非聚集索引

非聚簇索引，叶子节点上的data是主键(即聚簇索引的主键，所以聚簇索引的key，不能过长)。为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证

非聚簇索引结构图：

从非聚集索引的结构上，可以看出这种场景下的定位流程：
- 先通过非聚集索引，定位到对应的叶子节点，找到对应的主键
- 根据上面找到的主键，在聚集索引中，定位到对应的叶子节点（获取数据）
5. 索引的优点
- 避免全表扫描（当走不到索引时，就只能一个一个的去匹配；如果走索引，则可以根据B树来定位）
- 使用索引可以帮助服务器避免排序或者临时表（叶子节点上的指针，可以有效的支持范围查询；此外叶子节点本身就是根据key进行排序的）
- 索引将随机IO变成顺序IO
6. 适用范围

索引并不是适用于任何情况。对于中型、大型表适用。对于小型表全表扫描更高效。而对于特大型表，考虑”分区”技术

II. 索引的使用原则

一般我们在创建表的时候，需要指定primary key, 这样就可以确定聚集索引了，那么如何添加非聚集索引呢？

1. 索引的几个语法

创建索引

-- 创建索引create index `idx_img` on newuser(`img`);

-- 查看show create table newuserG;

输出

show create table newuserG

*************************** 1. row ***************************

Table: newuser

Create Table: CREATE TABLE `newuser` (

`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',

`username` varchar(30) DEFAULT '' COMMENT '用户登录名',

`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',

`password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',

`address` text COMMENT '用户地址',

`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',

`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',

`img` varchar(100) DEFAULT '' COMMENT '用户头像',

`extra` text,

`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',

`created` int(11) NOT NULL,

`updated` int(11) NOT NULL,

PRIMARY KEY (`userId`),

KEY `idx_username` (`username`),

KEY `idx_nickname` (`nickname`),

KEY `idx_email` (`email`),

KEY `idx_phone` (`phone`),

KEY `idx_img` (`img`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

另一种常见的添加索引方式

alter table newuser add index `idx_extra_img`(`isDeleted`, `img`);

-- 查看索引show index from newuser;

输出结果

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| newuser | 0 | PRIMARY | 1 | userId | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_username | 1 | username | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_nickname | 1 | nickname | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_email | 1 | email | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_phone | 1 | phone | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_img | 1 | img | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_extra_img | 1 | isDeleted | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_extra_img | 2 | img | A | 3 | NULL | NULL | YES | BTREE | | |

+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

删除索引

drop index `idx_extra_img` on newuser;

drop index `idx_img` on newuser;

-- 查看索引show index from newuser;

输出

show index from newuser;

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| newuser | 0 | PRIMARY | 1 | userId | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_username | 1 | username | A | 3 | NULL | NULL | YES | BTREE | | || newuser | 1 | idx_nickname | 1 | nickname | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_email | 1 | email | A | 3 | NULL | NULL | | BTREE | | || newuser | 1 | idx_phone | 1 | phone | A | 3 | NULL | NULL | | BTREE | | |

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

强制走索引的一种方式

语法： select * from table force index(索引) where xxx

explain select * from newuser force index(PRIMARY) where userId not in (3, 2, 5);

-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | range | PRIMARY | PRIMARY | 8 | NULL | 4 | Using where |-- +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

explain select * from newuser where userId not in (3, 2, 5);

-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

2. 索引使用规则

当一个表内有多个索引时，如何判断自己的sql是否走到了索引，走的是哪个索引呢？

可以通过 explain 关键字来进行辅助判断，当然在实际写sql时，我们也有必要了解下索引匹配的规则，避免设置了一些冗余的索引，或者写出一些走不到索引的sql

测试的表结构如下

*************************** 1. row ***************************

Table: newuser

Create Table: CREATE TABLE `newuser` (

`userId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',

`username` varchar(30) DEFAULT '' COMMENT '用户登录名',

`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',

`password` varchar(50) DEFAULT '' COMMENT '用户登录密码 & 密文根式',

`address` text COMMENT '用户地址',

`email` varchar(50) NOT NULL DEFAULT '' COMMENT '用户邮箱',

`phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户手机号',

`img` varchar(100) DEFAULT '' COMMENT '用户头像',

`extra` text,

`isDeleted` tinyint(1) unsigned NOT NULL DEFAULT '0',

`created` int(11) NOT NULL,

`updated` int(11) NOT NULL,

PRIMARY KEY (`userId`),

KEY `idx_username` (`username`),

KEY `idx_nickname_email_phone` (`nickname`,`email`,`phone`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

a. 最左前缀匹配原则

这个主要是针对多列非聚簇索引而言，比如有下面这个索引 idx_nickname_email_phone(nickname, email, phone) , nickname 定义在email的前面，那么下面这几个语句对应的情况是

-- 走索引explain select * from newuser where nickname='小灰灰' and email='greywolf@xxx.com';

-- 1. 匹配nickname，可以走索引explain select * from newuser where nickname='小灰灰';

-- 输出:-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+-- | 1 | SIMPLE | newuser | ref | idx_nickname_email | idx_nickname_email | 92 | const | 1 | Using index condition |-- +----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-----------------------+

-- 2. 虽然匹配了email, 但是不满足最左匹配，不走索引explain select * from newuser where email='greywolf@xxx.com';

-- 输出-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | NULL | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

b. 无法跳过某个列使用后续索引列

即对索引 idx_nickname_email_phone(nickname, email, phone) , 如果你的sql中，只有 nickname 和 phone, 那么phone走不到索引，因为不能跳过中间的email走索引

c. 范围查询后的列无法使用索引

如 >, <, between, like这种就是范围查询，下面的sql中，email 和phone都无法走到索引，因为nickname使用了范围查询

select * from newuser where nickname like '小灰%' and email='greywolf@xxx.com' and phone=15971112301 limit 10;

d. 列作为函数参数或表达式的一部分

-- 走不到索引explain select * from newuser where userId+1=2 limit 1;

-- 输出-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+-- | 1 | SIMPLE | newuser | ALL | NULL | NULL | NULL | NULL | 3 | Using where |-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

3. 索引缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
4. 注意事项
- 索引不会包含有NULL值的列
- 使用短索引
- 索引列排序
- like语句操作
- 不要在列上进行运算
- 尽量不使用NOT IN和<>操作
5. sql使用策略

a. 使用一个sql代替多个sql

通常建议是使用一个sql来替代多个sql的查询

当然若sql执行效率很低，或者出现delete等导致锁表的操作时，也可以采用多个sql，避免阻塞其他sql

b. 分解关联查询

将关联join尽量放在应用中来做，尽量执行小而简单的的sql
- 分解后的sql简单，利于使用mysql缓存
- 执行分解后的sql，减少锁竞争
- 更好的扩展性和维护性（sql简单）
- 关联sql使用的是内嵌循环算法nestloop，而应用中可以使用hashmap等结构处理数据，效率更高
c. count
- count(*) 统计的是行数
- count(列名) 统计的是列不为null的数量
d. limit
- limit offset, size; 分页查询，会查询出 offset + size 条数据，获取最后的size条数据
如 limit 1000, 20 则会查询出满足条件的1020条数据，然后将最后的20个返回，所以尽量避免大翻页查询

e. union

需要将where、order by、limit 这些限制放入到每个子查询，才能重分提升效率。另外如非必须，尽量使用Union all，因为union会给每个子查询的临时表加入distinct，对每个临时表做唯一性检查，效率较差。

6. mysql使用查询

a. 查看索引

-- 单位为GBSELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size'FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

b. 查看表空间

SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'

FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';

c. 查看数据库中所有表的信息

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',

table_rows AS 'Number of Rows',

CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',

CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,

CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'

FROM information_schema.TABLES

WHERE table_schema LIKE 'databaseName';
相关阅读:
java小白学习练手成绩管理系统(一)
java初级学习练手成绩管理系统(四)
Echart可视化学习集合
 java小白学习练手成绩管理系统(六)
java小白学习练手成绩管理系统
 网页内容加密
 Ubuntu 10.04 安装配置指南
 Linux目录解释
 完整清除XP垃圾文件系统自带的秘密武器
 人人都应该掌握的一些电脑操作技巧
原文地址：https://www.cnblogs.com/manmanrenshenglu/p/9013073.html

mysql之高性能索引

mysql之高性能索引

I. 索引介绍

1. 几种树的结构

a. B+树

b. 二叉树

c. 平衡二叉树

d. B树

2. InnoDB引擎之B+树

3. hash索引

4. 聚集索引与非聚集索引

a. 聚集索引

b. 非聚集索引

5. 索引的优点

6. 适用范围

II. 索引的使用原则

1. 索引的几个语法

创建索引

另一种常见的添加索引方式

删除索引

强制走索引的一种方式

2. 索引使用规则

a. 最左前缀匹配原则

b. 无法跳过某个列使用后续索引列

c. 范围查询后的列无法使用索引

d. 列作为函数参数或表达式的一部分

3. 索引缺点

4. 注意事项

5. sql使用策略

a. 使用一个sql代替多个sql

b. 分解关联查询

c. count

d. limit

e. union

6. mysql使用查询

a. 查看索引

b. 查看表空间

c. 查看数据库中所有表的信息