CentOS MySQL索引

CentOS MySQL索引

索引及执行计划

什么是索引

类似书的目录

索引就好比一本书的目录，它能让你更快的找到自己想要的内容。 让获取的数据更有目的性，从而提高数据库检索数据的性能。

注意：给指定的字段，排序，添加'目录'功能，索引并不是越多越好，也不是每个字段都必须加索引

MySQL中索引的类型

)BTREE:B+树索引 HASH：HASH索引 FULLTEXT：全文索引 RTREE：R树索引

Btree算法

Btree

三路Btree

根节点 枝节点 叶子结点

select * from tb1 where id=15; 精确查询：3次IO select * from tb1 where id>10 and id<30; 范围查询：6次

B+tree

select * from tb1 where id=15; 精确查询：3次 select * from tb1 where id>10 and id<30; 范围查询：4次 # B+tree和Btree比 1.优化了范围查询 2.在叶子节点上添加了相邻的指针

B*tree(了解即可)

索引管理

索引建立在表的列上(字段)的。 在where后面的列建立索引才会加快查询速度。

索引分类

主键索引 联合索引 唯一索引 前缀索引 联合索引 普通索引 前缀索引 联合索引

如何创建索引

## 创建索引 alter table score add index idx_sno(sno); ## 查看索引 show index from 表名; show index from stu; desc:查看索引 PRI:主键索引 UNI:唯一键索引 MUL:普通索引 ## 删除索引 alter table score drop index idx_sno; alter table score drop key idx_sno; ## 添加主键索引 mysql> create table test(id int,name varchar(10) not null); Query OK, 0 rows affected (0.13 sec) mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> alter table test add primary key(id); ## 删除主键索引 mysql> alter table test drop primary key; ## 添加唯一键索引 mysql> alter table test add unique key uni_name(name); # 添加唯一键要求：该字段的数据不能有重复的 mysql> alter table linux50.teacher add unique key uni_tanme(tname); ERROR 1062 (23000): Duplicate entry '徐亮伟' for key 'uni_tanme' ### 判断是否可以在name字段上创建唯一键 1.先统计该字段总共有多少行 mysql> select count(tname)from linux50.teacher; +--------------+ | count(tname) | +--------------+ | 6 | +--------------+ 1 row in set (0.00 sec) 2.再统计，去重后，该字段有多少行 mysql> select count(distinct(tname)) from linux50.teacher; +------------------------+ | count(distinct(tname)) | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec) # 如果两个数值一样，则可以创建唯一键索引，如果两个数值不一样，则无法创建唯一键索引（有重复值） ## 删除唯一键索引 alter table test drop index 索引名; alter table test drop index uni_name;

前缀索引

给某一字段数据内容特别长的列，创建前缀索引

## 普通前缀索引的创建 mysql> alter table city add index idx_district(district(4)); # 优先按前四个字符排序 ## 唯一索引前缀索引创建 alter table student2 add unique key uni_name(name(3)); # 原则 1.避免对大列（数据长的列）建索引 2.如果有，就使用前缀索引

联合索引

将多个字段，做成一个索引

联合索引的查询顺序要和创建时的顺序一致，才可以提高效率 A B C D E ## 创建联合索引为B,A,C all_(B,A,C) ## select 查询时也要按这个顺序查 select B,A,C # 走索引的,其余的搜索都是不走索引的 A,C B,C ## 普通联合索引创建 mysql> alter table student add index idx_all(sname,sage,ssex); ## 主键联合索引 mysql> alter table score add primary key (cno,mark); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc score; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | sno | bigint(20) | NO | | NULL | | | cno | bigint(20) | NO | PRI | NULL | | | mark | float(4,1) | NO | PRI | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) ## 唯一键联合索引 mysql> alter table score drop primary key; mysql> alter table score add unique key uni_all(sno,cno); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc score; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | sno | bigint(20) | NO | PRI | NULL | | | cno | bigint(20) | NO | PRI | NULL | | | mark | float(4,1) | NO | | NULL | | +-------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) ### 索引删除 普通索引 唯一索引 alter table 表名 drop index 索引名; 主键索引 alter table 表名 drop primary key; alter table 表名 drop primary; 联合索引 idx_all(id,name,age) alter table 表名 drop index idx_all; 索引，无法直接修改，删除索引后，重新创建。

索引的执行计划

## explain(执行计划) 检测一个SQL语句的执行效率 将explain加入在要执行的SQL语句之前即可 mysql> explain select * from score; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | score | ALL | NULL | NULL | NULL | NULL | 9 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> desc select * from score; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | score | ALL | NULL | NULL | NULL | NULL | 9 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ ## 查看SQL语句的执行效率，主要看type列 分类: - 全表扫描 - ALL:效率最低,且最好不要出现的类型 - 索引扫描 - index:全索引扫描 - range:范围查询 - ref:非唯一键或者唯一键的前缀索引的精确查询 - eq_ref:连表查询的时候 - system、const:主键索引精确查询 - NULL:无法查出数据时,效率最高 ## 全表扫描 ALL:企业中绝对不会出现 1.业务确实要获取所有数据 2.不走索引导致的全表扫描 - 没索引 - 索引创建有问题 - 语句有问题 - 索引损坏 ## 索引扫描 # index:全索引扫描,创建索引的列,全部数据都查询出来了 mysql> explain select sno from score; # range:范围查询,一般来说,一条SQL语句,只要达到range级别,就OK mysql> explain select sno from score where sno>2; # ref:唯一索引的前缀扫描或者非唯一索引扫描(精确查询) mysql> explain select sno from score where sno=2; # eq_ref:连表查询,传统连接,join on explain select city.countrycode,country.name as 国家,city.name as 城市,city.population as 城市人口数 from city join country on city.country.countrycode=country.code and city.population<100; # const、system:主键精确查询 mysql[world]> explain select * from city where id=10; # null:不进行表的扫描,没有这样的数据 explain select * from city where id>100000000000000000000000000000000000000000000000; ### 范围查询优化 mysql[world]> explain select * from city where countrycode='CHN' or countrycode='USA'; mysql[world]> explain select * from city where countrycode in ('CHN','USA'); ### 联合查询优化，or精确范围 explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

索引建立的原则

索引的创建，是为了提示查询的效率，需要知道在哪些字段上创建索引

索引不是每一个字段都要创建，也不是越多越好

根据用户的喜好，被查询的越多的字段，要创建索引

1.索引首要选择，唯一键索引 判断是否可以创建唯一键索引 select count(需要创建唯一键索引的字段) from 表; select count(distinct(需要创建唯一键索引的字段)) from 表; 2.其次，如果无法创建唯一索引，重复值比较多，创建联合索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。 3.为经常作为查询条件的字段，创建普通索引 经常查询 增加查询的效率 4.尽量能使用前缀索引，就用前缀索引 减少创建索引的排序时间 增加查询的效率 5.索引的数量不是越多越好 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 6.删除不再使用或者很少使用的索引 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

不走索引的原因

数据库查询效率低，导致网站加载速度慢，如何解决？

在企业中，开启MySQL的慢日志（慢查询日志）

查看，哪一条SQL语句执行的速度慢，取出SQL语句，使用explain查看该SQL语句是否走索引

没有走索引

mysql> explain select * from linux50.course; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | course | ALL | NULL | NULL | NULL | NULL | 3 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) ## 没有走索引

1.全表扫描，使用select *查询没有加条件

## 使用limit来优化 mysql> explain select * from world.city where limit 60;

2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from world.city where population>50 limit 60;

3.条件本身做运算

## 不走索引 mysql> explain select * from city where id-1=9; # 不能使用字段做计算

4.隐式转换导致索引失效

mysql> create table test1(id int,name varchar(10),phone char(11)); Query OK, 0 rows affected (0.14 sec) mysql> alter table test1 add index idx_phone(phone); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | phone | char(11) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) ## 不走索引 mysql> mysql> explain select * from test1 where phone=133; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test1 | ALL | idx_phone | NULL | NULL | NULL | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) ## 走索引 mysql> explain select * from test1 where phone='133'; +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | test1 | ref | idx_phone | idx_phone | 34 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) ## 优化方案: 1.查看字段的创建数据类型 2.告诉开发,在查询的时候,一定要按照字段的数据类型来查询 3.如果是字符串,就加引号 4.如果是整形,就不加引号

5.不等于 或者 not in

使用不等于或者not in会影响到结果集 ## 优化方案： - 使用limit来优化 - 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit 、or或in尽量改成union

6.使用like模糊查询%在前面

mysql> explain select * from city where countrycode like '%HN'; ## 优化方案： - 尽量不要使用%在前面的SQL语句，尽量将%放在后面 - 那就不要使用MySQL，使用搜索引擎式数据库：elasticsearch

7.使用联合索引查数据时

不走索引，要根据联合索引创建的顺序来查询数据 创建顺序 ：ABC 查询顺序：ABC AB AC BC

8.索引损坏或失效

删了重建

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。