分享几个简单MySQL优化小妙招(2)-木庄网络博客

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制：

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id，比如 uuid 这种

Mysql 索引分类

-- 创建
CREATE [UNIQUE] INDEX [indexName] ON table_name(column))
-- 删除?
DROP INDEX [indexName] ON tableName;
-- 查看?
SHOW INDEX FROM tableName;

-- 使用Alter命令:

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)?

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

-- 添加普通索引，索引值可出现多次:
ALTER TABLE tbl_name ADD INDEX index_name (column_list)?

--该语句指定了索引为 FULLTEXT ，用于全文索引:
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。 ?

-- 在表创建时直接创建索引
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name VARCHAR(200),?
? PRIMARY KEY(id), ?
? KEY (customer_name)
);

-- 单独创建索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引

索引列的值必须唯一，但允许有空值。 ?

随表一起创建：

CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id),?
? KEY (customer_name),?
? UNIQUE (customer_no)
);

单独建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引。 ?

-- 随表创建
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id)
);

-- 单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no)

-- 删除建主键索引：
ALTER TABLE customer drop PRIMARY

复合索引

即一个索引包含多个列。 ?

-- 随表一起建索引：
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id),?
? KEY (customer_name),?
? UNIQUE (customer_name),?
? KEY (customer_no,customer_name)
);

-- 单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

索引优化

最佳左前缀法则

使用复合索引时，需遵循最左前缀法则（查询从索引的最左前列开始并且不跳过索引中的列）。即过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

不要在索引列上做任何计算

索引列上做【计算、函数、(自动\手动)类型转换】等操作时，会导致索引失效而转向全表扫描。

索引列上不能有范围查询

执行mysql命令时应将可能做范围查询的字段的索引顺序放在最后。

尽量使用覆盖索引

覆盖索引：SQL 只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。即查询列和索引列时不要使用 select *…而是使用select a,b,c….。

1、使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。
2、字段的 is null 可以用到索引而 is not null 不会使用索引。
3、不能使用前缀进行模糊匹配：

... like '%a%' ?√... like '%a' ?? ?√... like 'a%' ?? ?×

使用 union all 或者 union 来替代or示例：

假设abc为索引

-- 索引被使用：
where a = 3;
where a = 3 and b = 5;
where a = 3 and b = 5 and c = 4;

-- 索引未被使用：
where a <> 3;
where abs(a) =3;
where b = 3;
where b = 3 and c = 4;
where c = 4;

-- 使用到a索引，但是未使用b、c索引
where a = 3 and c = 5；
where a = 3 and b > 4 and c = 5;
where a is null and b is not null;

子查询优化

在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx i。

排序分组优化

无过滤，不索引

where，limt 都相当于一种过滤条件，所以才能使用上索引。

顺序错，必排序

where 两侧列的顺序可以变换，效果相同，但是 order by 列的顺序不能随便变换。

方向反，必排序

如果可以用上索引的字段都使用正序或者逆序，实际上是没有任何影响的，无非将结果集调换顺序。

-- 两个排序方式都是desc：
select * from mytest where name='ahzoo' order by deptid desc, name desc

如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此还是需要手动排序的。

-- 两个排序方式相反，一个是降序一个是升序
select * from mytest where name='ahzoo' order by deptid desc, name asc

到此这篇关于分享几个简单MySQL优化小妙招的文章就介绍到这了,更多相关MySQL优化小妙招内容请搜索

更多Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » 分享几个简单MySQL优化小妙招

分享几个简单MySQL优化小妙招