一、数据库结构优化(非常重要
)
1. 减少数据冗余:(数据冗余是指在数据库中存在相同的数据,或者某些数据可以由其他数据计算得到),注意,尽量减少不代表完全避免数据冗余;
2. 尽量避免数据维护中出现更新,插入和删除异常:
总结:要避免异常,需要对数据库结构进行范式化设计
。
3. 节约数据存储空间。
4. 提高查询效率。
重要
):设计数据的逻辑存储结构。数据实体之间的逻辑关系,解决数据冗余和数据维护异常。数据范式可以帮助我们设计;非常重要
)PRIMARY KEY(主键索引) ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
1. 索引大大减少了存储引擎需要扫描的数据量;
2. 索引可以帮助我们进行排序以避免使用临时表;
3. 索引可以把随机I/O变为顺序I/O。
**1、索引会增加写操作的成本;**
**2、太多的索引会增加查询优化器的选择时间。**
索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,而有500页是目录,它当然效率低,目录是要占纸张的,而索引是要占磁盘空间的。
Innodb
索引列最大宽度为667
个字节(utf-8
差不多255
个字符),MyIsam
索引类宽度最大为1000
个字节,于是出现前缀索引,索引的选择性。
对于列的值较长,比如BLOB、TEXT、VARCHAR
,就必须建立前缀索引,即将值的前一部分作为索引。这样既可以节约空间,又可以提高查询效率。但无法使用前缀索引做 ORDER BY
和 GROUP BY
,也无法使用前缀索引做覆盖扫描。
语法: ALTER TABLE table_name ADD KEY(column_name(prefix_length))
选择性
差的列不适合,如性别,查询优化器可能会认为全表扫描性能更好);选择性高
的列优先;I/O
,查找越快);如果索引了多列,要遵守最左前缀
法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
跟组合索引有点类似,如果索引包含所有满足查询需要的数据的索引则成为覆盖索引
(Covering Index),也就是平时所说的不需要回表操作。即索引的叶子节点上面包含了他们索引的数据(hash索引不可以)。
判断标准:使用explain
,可以通过输出的extra
列来判断,对于一个索引覆盖查询,显示为using index
,MySQL
查询优化器在执行查询前会决定是否有索引覆盖查询。
查询中使用了太多的列
(如SELECT *
);%
号的like
查询(底层API所限制);套路重点
)
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上不计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写 *;
不等空值还有or,索引失效要少用;
字符单引不可丢,SQL高级也不难 ;
group by
实质是先排序后分组
,遵照索引的最佳左前缀。;max_length_for_sort_data
参数的设置+增大sort_buffer_size
参数的设置;where
高于having
,能写在where
限定的条件就不要去having
去限定了注:主键约束相当于(唯一约束 + 非空约束)
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示:Multiple primary key defined!!!
检查工具:pt-duplicate-key-checker
explain 查询计划 Using where:表示优化器需要通过索引回表查询数据; Using index:表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表,如覆盖索引;