数据库索引优化

根据数据库一键优化总结如下：

一：设计优先，先入为主

默认使用innodb，可使用99%的业务场景

1，并发

2，数据一致性

3crashrecovery 故障自动修复，修复相对myisam速度快

4，更高的存取效率

二：数据表设计优先，schema设计

首先介绍下schema是什么？

数据库Schema有两种含义，一种是概念上的Schema，指的是一组DDL语句集，该语句集完整地描述了数据库的结构。

还有一种是物理上的 Schema，指的是数据库中的一个名字空间，它包含一组表、视图和存储过程等命名对象。

物理Schema可以通过标准SQL语句来创建、更新和修改。

例如以下SQL语句创建了两个物理Schema：

create schema SCHEMA_A;

create table SCHEMA_A.CUSTOMERS(ID int not null,……);

create schema SCHEMA_B;

create table SCHEMA_B.CUSTOMERS(ID int not null,……);

简单的说：就是一个数据库用户所拥有的数据库的对象。

 

3，性别，是否枚举类型，使用enum/tinyint 而非char vachar

4，杜绝text/blob 可以做垂直分表拆分，或者转成myisam表

5，所有字段显示定义not null

6,技术很低的字段不创建索引（mysql还不支持bitmap索引）

7,常用排序orderby groupby distinct 字段上创建索引

8，索引数量不要太多

9，多使用联合索引，少使用单独索引

单一列的索引,联合索引. 顾名思义,单一列索引就是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引.

优缺点比较:

1):索引所占用空间:单一列索引相对要小.

2):索引创建时间:单一列索引相对短.

3):索引对insert,update,delete的影响程序:单一列索引要相对低.

4):在多条件查询时,联合索引效率要高.

索引的使用范围:单一列索引可以出现在where 条件中的任何位置,而联合索引需要按一定的顺序来写.

索引.

查看数据表创建索引情况命令

show INDEX from persion

查看查询语句利用索引情况，在sql语句前加上explain ，如下举例：

explain select last_name,first_name from persion where last_name = 'wei' and first_name='zhang';

 

Type，连接错作的类型，

create table person (id int, last_name varchar(30), first_name varchar(30))

create unique index person_id on person (id)

create index person_name on person (last_name, first_name)

联合索引使用结论:

联合索引与索引顺序有关系

第一种情况:–条件和输出列和索引列顺序相同

select last_name,first_name from person where last_name=’1′ and first_name=’1′

结果:利用person_name联合索引查找

第二种情况:–条件列与索引列顺序不同,但输出列相同

select last_name,first_name from person where first_name=’1′ and last_name=’1′

结果:利用person_name联合索引查找

第三种情况:–条件列与输出列与索引列的顺序都不相同

select first_name,last_name from person where first_name=’1′ and last_name=’1′

结果:利用person_name联合索引查找

第四种情况:–条件列在first_name和last_name中间加入另外一个条件

SELECT id, first_name,last_name from person where first_name=’1′ AND id=1 and last_name

结果:不能利用person_name联合索引查找

第五种情况:--在输出列中分开first_name和last_name

SELECT first_name,id,last_name from person where first_name='1' and last_name='1'

结果:利用person_name联合索引查找

第六种情况:条件列没有出现联合索引的第一列

SELECT first_name,id,last_name from person where first_name=’1′

SELECT first_name,last_name from person where first_name=’1′

SELECT last_name ,first_name from person where first_name=’1′

结果:不能利用person_name联合索引.

第七种情况:–条件列出现联合索引的第一列

SELECT first_name,id,last_name from person where last_name=’1′

SELECT first_name,last_name from person where last_name=’1′

SELECT last_name ,first_name from person where last_name=’1′

结果:利用person_name联合索引查找

1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

2):条件列中只要条件相连在一起,以本文例子来说就是:

last_name=’1′ and first_name=’1′

与

first_name=’1′ and last_name=’1′

,无论前后,都会利用上联合索引.

3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

单一列索引的应用结论:

只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化

 

10.字符型列创建索引时，创建前缀索引

 

无法使用索引的场景：

1,通过索引扫描的记录数超过30%,变成全表扫描

2,联合索引中，对一个索引列使用范围查询，只能使用部分索引

3，联合索引中，第一个查询条件不是最左索引列

4，模糊查询条件列最左以通配符%开始。

5，内存表（heap表）使用hash索引时，使用范围检索，或者orderby

6，两个独立索引，其中一个用于检索，一个用于排序，只能用到其中一个索引。

7,表关联字段类型或者长度不一样。

8，索引字段条件上使用函数 select * from table order by rand()

三：架构设计：