欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql 开发进阶篇系列 3 SQL 优化

程序员文章站 2022-11-30 23:27:50
一. 本章介绍mysql中的索引的分类,存储,使用方法的介绍 1. 索引的存储分类 MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件, innodb 存储引擎的表的数据和索引是存储在同一个表空间里面,可以有多个文件组成。 MyISAM和Innodb存储引擎都支持btree索 ......

一. 本章介绍mysql中的索引的分类,存储,使用方法的介绍

  1.  索引的存储分类
  MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独立的一个文件, innodb 存储引擎的表的数据和索引是存储在同一个表空间里面,可以有多个文件组成。 MyISAM和Innodb存储引擎都支持btree索引,memory/heap存储引擎支持hash和btree索引。

  2. mysql如何使用索引
查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有索引条件使用了多列关键字最左边的前缀时,才可以使用索引,否则不能.

  (1) 创建多列索引

 -- 多列索引又叫复合索引  创建city表的多列
CREATE INDEX ix1 ON city(cityname(10),citycode);    
-- 使用cityname进行查询,发现即使不使用citycode也能使用到索引,这就是索引前缀的特性
EXPLAIN SELECT * FROM city WHERE cityname='12'

mysql 开发进阶篇系列 3 SQL 优化

-- 使用citycode进行查询,就没有使用索引
EXPLAIN SELECT * FROM city WHERE citycode='12'

mysql 开发进阶篇系列 3 SQL 优化

  (2) 使用like
    需要使用模糊查询时, 建了索引的字段 %必须放在关键词后面

 -- 如下所示
EXPLAIN SELECT * FROM city WHERE cityname LIKE '12%'

mysql 开发进阶篇系列 3 SQL 优化

  (3) 使用条件is null
    如果列名是索引,那么使用is null 会使用到索引

  -- 如下所示
EXPLAIN SELECT * FROM city WHERE cityname IS NULL

mysql 开发进阶篇系列 3 SQL 优化

  3. 存在索引但不会使用到索引

(1) 使用索引比全表扫描更慢,则不使用索引。 比如key分布在1到100之间,使用索引 key>1 and key<90 。
(2) 使用memory/heap表 使用where条件不使用 = 进行查询时,那么不会用到索引。
(3) 用or分割开的条件, 如果or 前面条件中列有索引,or后面列没有索引,那么索引不会被用到如下

 --  country_id 列是索引  citycode不是索引
EXPLAIN SELECT * FROM city WHERE country_id=2 OR citycode='000'

mysql 开发进阶篇系列 3 SQL 优化

--  country_id 列是外键索引  city_id主键索引
EXPLAIN SELECT * FROM city WHERE country_id=2 OR city_id=2 (发现key也是空后面在分析)

mysql 开发进阶篇系列 3 SQL 优化

   (4) 列类型是字符串,记得值加引号

--  没有加引号
EXPLAIN SELECT * FROM city WHERE cityname=22

mysql 开发进阶篇系列 3 SQL 优化