mysql之索引
mysql索引:雖然索引大大提高了查詢速度,同時會降低更新表的速度,如對表進行insert,update和delete.因爲更新表時,mmysql不僅要保存數據,還要保存一下索引文件(該文件保存了主鍵和索引字段),索引建立過多會佔用磁盤空間的索引文件
分類1:單列索引和組合索引
分類2: 普通索引---唯一索引----主鍵索引----組合索引----全文索引(該索引只能用於myisam)
創建索引:
alter table tablename add index indexname (tablecolumns(length)) #普通索引
alter table tablename add unique index indexname (tablecolumns(length)) #唯一索引
或
CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC])
);
eg:
- create table test1(
- name char(50),
- unique index uq(name(10) )
- );
- UNIQUE:可选。表示索引为唯一性索引。
- SPATIAL:可选。表示索引为空间索引。
- INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是一样的。
- 索引名:可选。给创建的索引取一个新名称。
- 字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
- 长度:可选。指索引的长度,必须是字符串类型才可以使用。
- ASC:可选。表示升序排列。
- DESC:可选。表示降序排列。
索引的值必須唯一,單允許有空值,如果是組合索引,則列值的組合必須唯一。
ALTER TABLE myIndex ADD INDEX name_city_age(vc_Name(10),vc_City,i_Age);
建表时,vc_Name长度为 50,这里为什么用 10 呢?
回答: 因爲多數名稱的前10各字符通常不同,而且使用列的一部分創建索引可以使索引文件大大減少,從而節省了大量的磁盤空間,有利於提高insert操作的速度。
如果分别在 vc_Name,vc_City,i_Age 上建立单列索引,让该表有 3 个单列索引,查询时和上述的组合索引效率一样吗?大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。(也就是mysql在查詢時,只會用到一個索引)
刪除索引:
alter table tablename drop index indexname;
顯示索引信息:
show index from tbale
索引原則:
1.越少越好,因爲多了會佔用磁盤空間,增加插入和刪除的操作時間。一個表的索引越多,插入和刪除的速度越慢,如果一個系統需要快速錄入,那麼就決不能使用太多索引]
--------------------------
復合索引:創建時,應該考慮列的順序,而且創建復合索引應當包含少數幾個列,並且這些列在select查詢裏使用。在復合索引裏包含太多的列,會造成需要使用相當多的內存去存儲復合索引的列和值,其後果是內存溢出和性能降低。
[使用asc和desc對復合索引進行進一步優化]
- CREATE INDEX idx_example
- ON table1 (col1 ASC, col2 DESC, col3 ASC)
- SELECT * FROM tb WHERE col1 = val1
- SELECT * FROM tb WHERE col1 = val1 and col2 = val2
- SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3
- [使復合索引有效:爲a a|b a|b|c]
其中 有三列分别是:col1 升序,col2 降序, col3 升序。现在如果我们执行两个查询
1:Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
和索引顺序相同
2:Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
和索引顺序相反
查询1,2 都可以使复合索引优化。
如果查询为:
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
排序结果和索引完全不同时,此时的查询不会被复合索引优化。
-------------------------------
性能優化[參考http://www.jb51.net/article/82254.htm ]
分爲: 數據庫設計------sql語句優化-----數據庫參數配置------硬件資源和操作系統
sql數據庫設計部分
https://blog.csdn.net/wangqyoho/article/details/52900585
sql語句優化部分
1.使用like關鍵字的查詢時: 如果匹配字符串的第一個字符爲"%",索引不會起作用,只有%不在第一個位置時,索引才有效。
2.使用復合索引
3.查詢語句的查詢條件中只有or關鍵字,且or前後的兩個條件中的列都是索引時,查詢中才會使用索引。否則該查詢不會使用索引。
4.[在mysql中,使用join查詢來替代子查詢]
mysql中的子查詢雖然使查詢語句很靈活,但是執行效率不高。mysql需要爲子查詢的內層查詢結果建立一個臨時表,然後外層語句從臨時表中查詢記錄。查詢結束後再撤銷臨時表。(鏈接查詢不需要建立臨時表,其速度比子查詢更快)
5.盡量避免null字段,定義時盡量使用not null,原因是允許null時不方便查詢優化,復合索引也會失效,而且如果列有索引的時候會額外佔用空間。
6.不要使用select *
7.update時,where語句盡量走索引,不然會全表掃描.
8.or,如果:select a from A where b=1 or c=1这种where里面不同字段进行or,这种尽量改为union
9.避免使用"%前綴",因爲會導致索引失效
10.盡量不用insert select,數據量大有延遲,同步完了可能有錯誤。
數據庫參數配置
[通過優化mysql的參數可以提高資源利用率,從而達到提高mysql服務器性能的目的]