• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

MySQL索引

MySQL zkinogg 1年前 (2020-08-09) 184次浏览 0个评论

一.索引的分类

1 辅助索引

(1) 提取索引列的所有值,进行排序
(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
(3) 在叶子节点中的值,都会对应存储主键ID

2 聚集索引

(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
(2)MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

3.聚集索引和辅助索引的区别

(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
(2) 在一张表中,聚集索引只能有一个,一般是主键.
(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
(4) 聚集索引,叶子节点存储的是有序的整行数据.
(5) MySQL 的表数据存储是聚集索引组织表

4.辅助索引细分

(1) 单列辅助索引
(2) 联合索引(覆盖索引) 
(3) 唯一索引

二.查询

1.查询类型

全表扫描        : ALL 
索引扫描        : index,range,ref,eq_ref,const(system),NULL
index: 全索引扫描
mysql> desc select countrycode  from city;

range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select  * from city where countrycode like 'CH%';

# 对于辅助索引来讲,!= 和not in等语句是不走索引的
# 对于主键索引列来讲,!= 和not in等语句是走range

2.查询级别

type:查询级别

type=ALL 全表扫描,

type=index 索引全扫描,遍历整个索引来查询匹配的行
type=range 索引范围扫描,常见于 <,<=,>,>=,between,in等操作符。
  例 
    explain select * from adminlog where id>0 , 
    explain select * from adminlog where id>0 and id<=100
    explain select * from adminlog where id in (1,2) 
 
type=ref        #使用非唯一索引或唯一索引的前缀扫描,返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
type=eq_ref     #类似于ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中有一条记录匹配;简单来说,说是多表连接中使用 主建或唯一健作为关联条件

type=const/system #单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的,所以性能最优。条件使用=。 

type=NULL #不用访问表或者索引,直接就能够得到结果

2.查询索引

desc city;
PRI   ==> 主键索引 
MUL   ==> 辅助索引
UNI   ==> 唯一索引 

mysql> show index from city\G

3.创建索引

# 单列的辅助索引:
mysql> alter table city add index idx_name(name);

# 多列的联合索引:
mysql> alter table city add index idx_c_p(countrycode,population);

# 唯一索引: 
mysql> alter table city add unique index uidx_dis(district);

mysql> select count(district) from city;
mysql> select count(distinct district) from city;

# 前缀索引:
mysql> alter table city add index idx_dis(district(5));


# 删除索引 :
mysql> alter table city drop index idx_name;
mysql> alter table city drop index idx_c_p;
mysql> alter table city drop index idx_dis;

三.压力测试

mysql> use test
mysql> source /tmp/t100w.sql

# 未做优化之前测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='MN89'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='MN89'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 755.861 seconds
    Minimum number of seconds to run all queries: 755.861 seconds
    Maximum number of seconds to run all queries: 755.861 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

# 索引优化后
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1.678 seconds
    Minimum number of seconds to run all queries: 1.678 seconds
    Maximum number of seconds to run all queries: 1.678 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

四、explain的使用

1.explain语法

explain + DQL语句

mysql> explain select * from city where countrycode ='CHN' or countrycode ='USA';

#查询中国和美国的数据
mysql> select * from city where countrycode ='CHN' or countrycode ='USA';
mysql> select * from city where countrycode in ('CHN','USA');
mysql> select * from city where countrycode = 'CHN' union all select * from city where countrycode = 'USA';

Extra(扩展)
    Using temporary 使用group by大概率出现
    Using filesort 使用了order by大概率出现
    Using join buffer 使用join on大概率出现

2.4. explain(desc)使用场景(面试题)

你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?

题目意思:  我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
(1)应急性的慢:突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句

2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
   (2)一段时间慢(持续性的):
   (1)记录慢日志slowlog,分析slowlog
   (2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
   (3)建索引,改语句

五.索引应用规范

# 建立索引的原则(DBA运维规范)
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引

六.不走索引的几种情况及面试题


(1) 没有查询条件,或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据,应该是25%以上。
(3) 索引本身失效,统计数据不真实

面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
#建表
mysql> create table test (id int ,name varchar(20),telnum varchar(10));
Query OK, 0 rows affected (0.04 sec)
#插入数据
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
#建立索引
mysql> desc phonenum;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| phone | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#查询语句级别全文扫描
mysql> explain select * from phonenum where phone=6666666;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | phonenum | ALL  | uni_key       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#当给字符加上引号,查询为索引扫描
mysql> explain select * from phonenum where phone='6666666';
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | phonenum | const | uni_key       | uni_key | 13      | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
(6) <> ,not in 不走索引(辅助索引)
(7) like "%aa" 百分号在最前面不走
(8) 联合索引查询不按照顺序有可能不走索引




极客公园 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL索引
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!