当db的量达到一定数量级之后,每次进行全表扫描效率就会很低,因此一个常见的方案是建立一些必要的索引作为优化手段,那么问题就来了:
<!-- more -->
MySQL官方对索引的定义为:索引是帮助MySQL高效获取数据的数据结构。简而言之,索引是数据结构
单来说就是一种为磁盘或者其他存储设备而设计的一种平衡二叉树,在B+tree中所有记录都按照key的大小存放在叶子结点上,各叶子结点直接用指针连接
二叉树的规则是父节点大于左孩子节点,小于右孩子节点
首先是一个二叉树,但是要求任意一个节点的左右孩子节点的高度差不大于1
首先是一个平衡二叉树,但是又要求每个叶子节点到根节点的距离相等
那么B树和B+树的区别是什么呢?
mysql的InnnoDB引擎采用的B+树,只有叶子节点存储对应的数据列,有以下好处
hash索引,相比较于B树而言,不需要从根节点到叶子节点的遍历,可以一次定位到位置,查询效率更高,但缺点也很明显
InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,非叶子节点存放<key,address>,address就是下一层的地址
聚簇索引的结构图:
非聚簇索引,叶子节点上的data是主键(即聚簇索引的主键,所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证
非聚簇索引结构图:
从非聚集索引的结构上,可以看出这种场景下的定位流程:
索引并不是适用于任何情况。对于中型、大型表适用。对于小型表全表扫描更高效。而对于特大型表,考虑”分区”技术
一般我们在创建表的时候,需要指定primary key, 这样就可以确定聚集索引了,那么如何添加非聚集索引呢?
创建索引
-- 创建索引
create index `idx_img` on newuser(`img`);
-- 查看
show create table newuser\G;
输出
show create table newuser\G
*************************** 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 |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
当一个表内有多个索引时,如何判断自己的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
这个主要是针对多列非聚簇索引而言,比如有下面这个索引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 |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
即对索引idx_nickname_email_phone(nickname, email, phone)
, 如果你的sql中,只有 nickname 和 phone, 那么phone走不到索引,因为不能跳过中间的email走索引
如 >, <, between, like这种就是范围查询,下面的sql中,email 和phone都无法走到索引,因为nickname使用了范围查询
select * from newuser where nickname like '小灰%' and email='greywolf@xxx.com' and phone=15971112301 limit 10;
-- 走不到索引
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 |
-- +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
通常建议是使用一个sql来替代多个sql的查询
当然若sql执行效率很低,或者出现delete等导致锁表的操作时,也可以采用多个sql,避免阻塞其他sql
将关联join尽量放在应用中来做,尽量执行小而简单的的sql
如 limit 1000, 20
则会查询出满足条件的1020条数据,然后将最后的20个返回,所以尽量避免大翻页查询
需要将where、order by、limit 这些限制放入到每个子查询,才能重分提升效率。另外如非必须,尽量使用Union all,因为union会给每个子查询的临时表加入distinct,对每个临时表做唯一性检查,效率较差。
-- 单位为GB
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Total Data Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'databaseName';
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';
基于hexo + github pages搭建的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛
尽信书则不如,已上内容,纯属一家之言,因本人能力一般,见识有限,如发现bug或者有更好的建议,随时欢迎批评指正
本文系转载,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文系转载,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。