1.数据库优化最直接方式就是添加索引;
2.对于大数据可以分库,分表
3.对于索引的操作主要有:添加,删除,修改
3.1.添加索引
1.添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
2.添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`)
3.添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
4.添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` )
举例:为user表age添加单列索引
ALTER TABLE user ADD INDEX (age)
5.添加组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)
举例:为user表age添加组合索引
ALTER TABLE user ADD INDEX (age,name,sex)
组合索引说明:
(1) 组合索引的索引文件以B-Tree格式保存,在创建组合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
(2)组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。
(3)组合索引顺序不同,mysql会自动优化,建议顺序与where查询条件保持一致
注意:1. 索引分单列索引和组合索引。
(1)单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引
(2)组合索引,即一个索引包含多个列
2.创建索引时,需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)
3.2.删除索引(有三种形式)
1. 使用 DROP INDEX 语句删除索引
2. 语法格式1:DROP INDEX index_name ON table_name;
举例:删除user表age索引列
DROP INDEX age ON user
语法格式2:ALTER TABLE table_name DROP INDEX index_name
举例:删除user表age索引列
ALTER TABLE user DROP INDEX age
语法格式3:ALTER TABLE table_name DROP PRIMARY KEY
注:语法格式3,相当于删除主键索引
删除索引说明:
(1)前两条语句是等价的,删除掉table_name中的索引index_name
(2)第3条语句只在删除PRIMARY KEY(主键索引)索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名,
如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引
(3)若从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。
如果删除组成索引的所有列,则整个索引将被删除
3.3.修改索引
(1)在MySQL中并没有提供修改索引的直接指令,一般情况下,我们需要先删除掉原索引,再根据需要创建一个同名的索引,
从而变相地实现修改索引操作
(2)--先删除 ALTER TABLE user DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引 CREATE INDEX idx_user_username ON user (username(8));
等价于:ALTER TABLE `table_name` ADD INDEX index_name (`column` )
3.4.查看索引
查询语句:SHOW INDEX FROM table_name
扩展:1.MySQL强制走索引
mysql between 日期索引 索引问题-日期索引使用
语法格式: FORCE INDEX ( 列名 )
举例:有一个user表,存在日期字段date
SELECT * FROM `table_xxx` FORCE INDEX ( date) WHERE date BETWEEN '2017-12-01' AND '2017-12-30'
4.重建索引:在数据库运行了较长时间后,索引都有损坏的可能,这时就需要重建,也是维护索引的重要工作之一
语法格式:REPAIR TABLE table_name QUICK;
注意:1.REPAIR TABLE 用于修复被破坏的表(只对MyISAM引擎有效)
2.OPTIMIZE TABLE table_name 优化表
3.QUICK 用在数据表还没被修改的情况下,速度最快
4.多数情况下,简单得用”repair table table_name”不加选项就可以搞定问题
5.索引生效问题
5.1.MySql索引失效的几种情况
(1)索引无法存储null值
a.单列索引无法储null值,复合索引无法储全为null的值。
b.查询时,采用is null条件时,不能利用到索引,只能全表扫描。
(2)不适合键值较少的列(重复数据较多的列)
(3)前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
(4)如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
(5)对于多列索引,不是使用的第一部分,则不会使用索引
(6)如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.2.MySQL主要提供2种方式的索引:B-Tree索引,Hash索引
(1)B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN).相当于二分查找。
(2)哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1)
(3)如果值的差异性大,并且以等值查找(=、 <、>、in)为主,Hash索引是更高效的选择,它有O(1)的查找复杂度。
如果值的差异性相对较差,并且以范围查找为主,B树是更好的选择,它支持范围查找
6.多表关联的索引使用总结
6.1.假如有用户表(user)与部门表(dept),通过主键id关联
6.2.关联表分别针对于user_id与dept_id建立单列索引idx_user,idx_dept最优。
7.索引使用规则小结:
7.1.表的主键、外键必须有索引
7.2.数据量超过300的表应该有索引
7.3.经常与其他表进行连接的表,在连接字段上应该建立索引
7.4.经常出现在Where子句中的字段,特别是大表的字段,应该建立索引
7.5.索引应该建在选择性高的字段上
7.6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引
7.7.频繁进行数据操作的表,不要建立太多的索引
7.8.删除无用的索引,避免对执行计划造成负面影响