MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。
确保on/where语句中的列上有索引. 确保order by / group by 只根据一个表上的字段进行,这样才有使用索引进行排序分组的可能性.
锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。 例如,外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。 注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候,salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。
CREATE TABLE customerinfo( CustomerIDINT NOT NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATE TABLE salesinfo( SalesIDNT NOT NULL,CustomerIDINT NOT NULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE)TYPE=INNODB;
注意:索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
索引的缺点
所以单表数据太少,索引反而会影响速度;更新非常频繁的数据不适宜建索引
如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c > 3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。(很简单:索引命中只能是相等的情况,不能是范围匹配,在第三个c中,是大于,执行万第三个,无法,命中)
经常需要ORDER BY、GROUP BY、DISTINCT等操作的字段,排序操作会浪费很多时间。如果为分组字段或者排序字段建立索引,可以有效地避免排序操作。
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
即索引列不能带函数,否则会导致索引失效
区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,个人认为尽量不要选用状态\状态字段为索引
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间要比对CHAR(10)类型的字段需要的时间要多。
MySQL索引最多包含16个索引列
like 模糊查询中,右模糊查询(abc%)会使用索引,而(%abc)和(%abc%)会放弃索引而使用全表扫描
联合查询,子查询等多表操作时关连字段要加索引,Rmman大佬说,不建议使用子查询