MySQL 索引
索引
-
什么是索引?
索引好比是书的目录,可以快速定位想要看的内容
让获取的数据更有目的性,从而提高数据库检索的性能
-
索引类型
- BTREE:B+树索引 innodb
- HASH: HASH索引
- FULLTEXT: 全文索引
- RTREE: R树索引
-
索引分类(尽量避免在大列内建索引如果要建索引使用前缀索引)
-
主键索引 非空,唯一
-
唯一索引 唯一 (唯一加not null 相当于主键索引)
-
普通索引 (前缀索引,联合索引都属于普通索引)
前缀索引: 在创建索引时,数据库会对索引进行排序,如果数据很长的话,排序需要占用时间长,并且占用较多的磁盘空间,前缀索引只会对指定的前几个字符串进行排序,占用时间短,磁盘空间少
联合索引:把多个字段建立成一个索引
-
#添加主键索引
primary key (字段)
alter table student2 add primary key pri_id(id);
#删除主键索引
alter table student modify id int not null; #先取消自增长
alter table student drop primary key ;
ALTER TABLE users MODIFY id int UNSIGNED NOT NULL;#先取消自增长
DROP INDEX `PRIMARY` ON users;
#添加唯一索引
name varchar(15) not null unique #创建是添加 方法1
unique key uni_name(name) #创建是添加 方法2
alter table student add unique key uni_name(name); #后续添加唯一索引
CREATE UNIQUE INDEX uk_users_name ON t_users(name);
CREATE UNIQUE INDEX ph_index ON users(phone);
#删除唯一索引
alter table student drop index uni_name;
drop index ph_index from users;
#判断能否添加唯一索引
1. select count(name) from student #统计name字段共有多少行数据
2. select count(distinct(name)) from student; #去重统计那么列有多少行数据
3. 对比 1和2 结果是否相同
#创建普通索引
KEY `idx_name` (`name`) #建表时添加
alter table 表名 add index 索引名称(要加索引列);
alter table student add index idx_name(name);
CREATE INDEX indexName ON table_name (column_name)
CREATE INDEX ph_index ON users(phone);
#添加前缀索引
alter table student add index idx_name(name(10));
#添加联合索引 a b c d
alter table student add index inx_all(age,sex,date,address); #注意索引顺序(把最经常查的列放在最前面)
select * from student where age=18 and date='2021',address='xxx'; #部分走索引 (age走索引,date,address不走索引) 原因:联合索引查找是必须按照索引顺序查找,且联合索引开头必须是age
例如:a,ab,abc abcd 走索引或者部分走索引
bc,bcd,bca,bda,bad,... 不走索引
#删除索引
alter table student drop index idx_name;
DROP INDEX ph_index ON student;
#查看索引
show index from student;
外键约束
如果一张表中有一个非主键的字段指向了另一张表中的主键，就将该字段叫做外键。一张表中外键可以有多个，也就是不同字段指向了不同表中的主键。需要注意数据表的存储引擎必须为InnoDB，因为InnoDB提供事务支持以及外部键等高级数据库功能，相反的MyISAM不支持。
- 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
- 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
外键的作用是保持数据一致性、完整性，主要体现在下面两个方面：
阻止执行
从表插入新行，其外键值不是主表的主键值便阻止插入；
从表修改外键值，新值不是主表的主键值便阻止修改；
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。
级联执行
主表删除行，连带从表的相关行一起删除；
主表修改主键值，连带从表相关行的外键值一起修改。
外键约束
cascade 外键表中外键字段值会被更新,或所在的列会被删除.(on delete cascade ,on update cascade )
restrict 相当于no action,即不进行任何操作.即,拒绝父表update外键关联列,delete记录.
set null: 被父面的外键关联字段被update ,delete时,子表的外键列被设置为null.而对于insert,子表的外键列输入的值,只能是父表外键关联列已有的值.否则出错.
ON UPDATE CASCADE：主表修改记录时，从表关联记录的外键字段也会修改。（将CASCADE改为RESTRICT，意思相反）
ON DELETE CASCADE：主表删除记录时，从表关联记录的外键字段也会删除。（将CASCADE改为RESTRICT，意思相反）
格式
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
#参数说明：
#CONSTRAINT ：用于设置外键约束名称，可以省略
#FOREIGN KEY：外键设置，用于指定外键字段
#REFERENCES：主表及主键设置，用于指定主表和主键
#创建数据库
create database test1;
use test1;
#创建主表
create table t1(id int not null auto_increment, name varchar(20),primary key(id) )engine=innodb default charset=utf8;
#创建从表并添加外键约束
create table t2( id int not null auto_increment,score tinyint,stu_id int,primary key(id),foreign key(stu_id) references t1(id))engine=innodb default charset=utf8;
#后期创建约束
alter table users add foreign key [depid2id] (depid) references dep(id);
alter table t2 add [constraint stu_fk] foreign key(stu_id) references t1(id);
alter table t2 add [constraint stu_fk] foreign key(stu_id) references t1(id)on delete cascade on update cascade;
#主表插入数据
insert into t1(name)values ('zhang3'),('test')('wang5'),('li4');
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 2 | test |
| 3 | wang5 |
| 4 | li4 |
+----+-------+
#从表插入数据
insert into t2(score,stu_id)values(50,3),(60,4);
mysql> select * from t2;
+----+-------+--------+
| id | score | stu_id |
+----+-------+--------+
| 2 | 70 | 2 |
| 3 | 50 | 3 |
| 4 | 60 | 4 |
+----+-------+--------+
#例 当设置为references无约束时 删除主表记录报错
mysql> delete from t1 where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test1`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `t1` (`id`))
#需先删除从表数据后在删主表
mysql> delete from t2 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.01 sec)
#例 当设置为references on delete cascade on update cascade时 同步删除从表数据
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 3 | wang5 |
| 4 | li4 |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+----+-------+--------+
| id | score | stu_id |
+----+-------+--------+
| 3 | 50 | 3 |
| 4 | 60 | 4 |
+----+-------+--------+
2 rows in set (0.00 sec)
#删除外键
alter table 表名 drop foreign key 外键约束名称
性能优化分析
查询sql读写频次用于分析数据库是读多还是写多,用于后续优化参考
session级查询
#单独查询
mysql> show session status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 2 |
+---------------+-------+
mysql> show [session] status like 'com_insert';
mysql> show [session] status like 'com_update';
mysql> show [session] status like 'com_delete';
#session级正则查询
mysql> show status where variable_name rlike '^com_[siud][enp].{4}$';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete | 0 |
| Com_insert | 0 |
| Com_select | 2 |
| Com_update | 0 |
+---------------+-------+
4 rows in set (0.00 sec)
global全局查询
#单独查询某一项
mysql> show global status like 'com_select';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_select | 6630571 |
+---------------+---------+
mysql> show global status like 'com_insert';
mysql> show global status like 'com_update';
mysql> show global status like 'com_delete';
#global全局正则查询
mysql> show global status where variable_name rlike 'com_[siud][enp].{4}$';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_delete | 1564 |
| Com_insert | 3258 |
| Com_select | 6631056 |
| Com_update | 85196 |
+---------------+---------+
4 rows in set (0.01 sec)
Profiling 详解
Profiling是从 mysql5.0.3版本以后才开放的，启动profile之后，所有查询包括错误的语句都会记录在内，可以时用他查询更为详细的sql执行状态
默认情况下profiling为关闭状态，需要手动开启
mysql> select @@profiling; 查看是否开启
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
mysql> set profiling=1; #开启profiling
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.02 sec)
mysql> select * from test_db.j_student;
+-----+--------+---------------------+------+
| sno | sname | sage | ssex |
+-----+--------+---------------------+------+
| 1 | 张三 | 1980-01-23 00:00:00 | 男 |
| 2 | 李四 | 1982-12-12 00:00:00 | 男 |
| 3 | 张飒 | 1981-09-09 00:00:00 | 男 |
| 4 | 莉莉 | 1983-03-23 00:00:00 | 女 |
| 5 | 王弼 | 1982-06-21 00:00:00 | 男 |
| 6 | 王丽 | 1984-10-10 00:00:00 | 女 |
| 7 | 刘香 | 1980-12-22 00:00:00 | 女 |
+-----+--------+---------------------+------+
7 rows in set (0.02 sec)
mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00569025 | select @@profiling |
| 2 | 0.02005925 | select * from test_db.j_student |
+----------+------------+----------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 5; #使用 <show profile for query 编号> 可以更为详细的查看SQL语句的状态
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.001160 |
| Executing hook on transaction | 0.000045 |
| starting | 0.000131 |
| checking permissions | 0.000072 |
| Opening tables | 0.002035 |
| init | 0.000066 |
| System lock | 0.000109 |
| optimizing | 0.000042 |
| statistics | 0.000172 |
| preparing | 0.000218 |
| executing | 0.015537 |
| end | 0.000025 |
| query end | 0.000026 |
| waiting for handler commit | 0.000103 |
| closing tables | 0.000034 |
| freeing items | 0.000093 |
| cleaning up | 0.000193 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.03 sec)
mysql> show profile cpu for query 5; #show profile cpu 可以查看CPU使用情况
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000446 | 0.000341 | 0.000000 |
| Executing hook on transaction | 0.000020 | 0.000017 | 0.000000 |
| starting | 0.000019 | 0.000019 | 0.000000 |
| checking permissions | 0.000016 | 0.000016 | 0.000000 |
| Opening tables | 0.000135 | 0.000170 | 0.000000 |
| init | 0.000019 | 0.000017 | 0.000000 |
| System lock | 0.000043 | 0.000044 | 0.000000 |
| optimizing | 0.000028 | 0.000028 | 0.000000 |
| statistics | 0.000071 | 0.000071 | 0.000000 |
| preparing | 0.000048 | 0.000048 | 0.000000 |
| executing | 0.071558 | 0.071060 | 0.000000 |
| end | 0.000033 | 0.000030 | 0.000000 |
| query end | 0.000012 | 0.000011 | 0.000000 |
| waiting for handler commit | 0.000028 | 0.000028 | 0.000000 |
| closing tables | 0.000025 | 0.000026 | 0.000000 |
| freeing items | 0.000105 | 0.000105 | 0.000000 |
| cleaning up | 0.000068 | 0.000068 | 0.000000 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)
explain 详解
desc expain 基本一样,都可以查看并分析SQL语句的执行情况 mysql8中新增explain analyze 可更为详细查看
Table scan on users (cost=1.05 rows=8) (actual time=0.246..0.256 rows=8 loops=1)
1.全表扫描 在explain 语句结果中出现all 为全表扫描
1).什么时间出现全表扫描 (生产环境避免出现全表扫描 性能极差)
-
需要获取全部数据
-
不走索引导致
-
没索引
-
索引创建有问题
-
语句有问题
ps:当查询结果集是原表中大部分数据,(25%以上?)不走索引**
2.索引扫描
1)常见索引类型
- all 最差 全表扫描
- index 查询时走索引,但是会遍历整个索引树
- range 索引上进行范围查询
- ref 查询时连接字段不是主键或者唯一索引
- eq_ref 连表查询时连接字段是主键或者唯一索引
- const 查询主键或者唯一键时
- system myiam引擎, 一般表中只有1条数据时才会出现
- null 最好 一般不查表时会出现如:select @@slow_query_log
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | MUL | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
index 级别
index 与all的区别为index类型只遍历索引树
例如: district 加普通索引
mysql> explain select district from city;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | city | index | NULL | inx_dist | 20 | NULL | 4188 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
range 级别
索引范围扫描,例如带有between或者where子句中含有<>查询 从某一个点开始匹配,返回条件值的记录行
例如: population加普通索引
explain select * from city where population<10000;
explain select * from city where countrycode in ('chn','usa');
explain select * from city where population between 10000 and 40000;
ref级别
使用的非唯一索引扫描或者是唯一索引前缀扫描,返回某个"单个条件值"的记录行
explain select * from city where countrycode ='chn' union all select * from city where countrycode='usa'; #合并查询
explain select * from city where district='shanghai'; #where条件为索引的精确查找
eq_ref级别
与ref差不多,区别:使用的是唯一索引,多表查询中'条件为主键或者是唯一索引作为关联条件'
explain select t1.name,t2.score from t1 join t2 on t1.id=t2.id where t2.score=70;
explain select t1.name,t2.score from t1, t2 where t1.id=t2.id and t2.score=70;
const,system级别
#where条件为主键索引
explain select * from t1 where id=1;
null级别
执行命令时,不用访问或者不走索引,例如直接从索引列选取最小值或者最大值
explain select * from t1 where id=0;
explain select * from t1 where id=100000000000000000;
索引建立规范
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?
- 1、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如:
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
注意：如果重复值较多，可以考虑采用联合索引
- 2．为经常需要排序、分组和联合操作的字段建立索引
例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作
-
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
因此，为这样的字段建立索引，可以提高整个表的查询速度。
- 3.1 经常查询
- 3.2 列值的重复值少
注：如果经常作为条件的列，重复值特别多，可以建立联合索引
- 4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
----------------------------------------------------------------------------- 我是华丽的分割线 ---------------------------------------------------------------------------
- 5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 - 6．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
---------------------------------------------------------------------------- 是的，没错，又是我 -------------------------------------------------------------------------
重点关注：
- 1.没有查询条件，或者查询条件没有建立索引
#全表扫描
select * from table;
select * from table where 1=1;
在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）SQL改写成以下语句：
#情况1
#全表扫描
select * from table;
#需要在price列上建立索引
selec * from tab order by price limit 10;
#情况2
#name列没有索引
select * from table where name='zhangsan';
1、换成有索引的列作为查询条件
2、将name列建立索引
- 2.查询结果集是原表中的大部分数据，应该是25％以上
mysql> explain select * from city where population>3000 order by population;
1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
-
3.索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决 -
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
#例子
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
- 5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';
- 6． <> ，not in 不走索引
mysql> select * from tab where telnum <> '1555555';
mysql> explain select * from tab where telnum <> '1555555';
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
#改写成
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
- 7．like "%_" 百分号在最前面不走
#走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
#不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';
%linux%类的搜索需求，可以使用Elasticsearch -------> ELK
- 8.单独引用联合索引里非第一位置的索引列
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m';
#不走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
索引失效
运算操作
前面模糊匹配
字符串不加引号
or两侧用的列有一列没有索引
数据分布影响(25%左右) 如果优化器判定全表扫描快于索引,那将不走索引
覆盖索引
- 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
- 是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即，索引包含了查询正在查找的数据）
SQL提示
建议优化器走哪条索引
- use index 建议走某条索引
- ignore index 忽略某条索引
- force index 强制走某条索引
mysql>select id,name from users ignore index(idx_users_phone) where id='12345678901';
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果