新版博客SEO优化基本完成,新老博客内容正在整合中,保证每篇文章高质量。 SiteMap RSS Github
Mysql查询优化---索引
嘉美伯爵   2018年7月12日 23:14   数据库   MySQL   233  

首先我们先了解一下Mysql的查询调用关系, Mysql的逻辑架构总的来说分为三层。

  • 客户端层:并非MySQL所独有,诸如:连接处理、授权认证、安全等功能均在这一层处理
  • 核心服务层:查询解析、分析、优化、缓存、内置函数;所有的跨存储引擎的功能也在这一层实现:存储过程、触发器、视图等
  • 存储引擎层:负责MySQL中的数据存储和提取,其中中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异(诸如myisam、innodb引擎)。

索引类型

普通索引

  • 索引值不一定唯一,允许有空值 CREATE INDEX index_name ON table(column(length))

唯一索引

  • 索引值必须唯一,允许有空值 CREATE UNIQUE INDEX indexName ON table(column(length))

主键索引

  • 即建表时的主键字段,为主键索引,唯一不为空
### 建立主键即创建主键索引
CREATE TABLE `table` (
    `id` int(11) PRIMARY KEY  NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
);

组合索引

  • 只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用 create index username_password on user(username,password)

全文索引

  • 只有char、varchar,text 列上可以创建全文索引 CREATE FULLTEXT INDEX index_content ON article(content)

创建索引

  • position_village表共666935条数据
mysql> select count(id) from position_village;
+-----------+
| count(id) |
+-----------+
|    666935 |
+-----------+
1 row in set (1.18 sec)
  • 语法: CREATE INDEX index_name ON table_name (column_list);
mysql> create index villageid on position_village(village_name);
Query OK, 0 rows affected (11.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 对比
### 对创建索引的village_name字段进行查询,为了体现,我们查询空集。
mysql> select  * from position_village where village_name="dd";
Empty set (0.02 sec)
### 未创建索引的town_name字段
mysql> select  * from position_village where town_name="dd";
Empty set (0.79 sec)

修改索引

ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
ALTER TABLE article ADD FULLTEXT index_content(content);
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age)

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

索引失效

  • 定义常量避免
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
  • 相同字符集

    如果把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引,必须保证相同字符集。

优缺点

优点

1.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 2.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 3.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 5.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

缺点

1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚集索引那么需要的空间就会更大。 3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。