MySQL数据库索引及优化

索引

什么是索引

索引是用来提高数据库性能的常用工具，索引就像数据的目录一样，通过索引可以快速找到需要的内容。

索引的原理，加快查询

索引是建立了针对数据内容的排序结果的指针，根据指针快速定位所要的数据。

索引的设计原则

1  索引列一般为where子句中的列或连接列子句中的列。

2  尽量不对基数小的列做索引。

3  尽可能使用短索引，如果对字符列索引尽量指定最小长度。

4  复合索引前缀特性，索引的顺序很重要。

　　创建复合索引时将最常用限制条件列放在最左边，一次递减。

5  避免出现无用的索引。

6  InnoDB尽量指定主键。

避免过度使用索引

1  索引的建立对提高检索能力有用，但是对数据库维护很费资源。

2  对选择性不高的列（例如性别）过度索引，影响插入更新速度。

3  索引会占用磁盘空间，降低写操作，执行计划要考虑各个索引。

4  索引不是越多越好。

索引语法

1  创建索引语法

Syntax:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

2  删除索引

Syntax:
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

3  查看表索引

mysql> show index from t1;

更多语法用帮助查看

mysql> help create index
mysql> help alter table

索引类型

1  主键索引

2  唯一索引

3  复合索引

4  覆盖索引

5  函数索引

6  分区索引

优化

1  硬件软件优化。

　　硬件：cpu、内存、硬盘（性能ssd > sas > sata）、磁盘raid

　　软件：操作系统（x86_64）、MySQL（编译安装）

2  参数优化（my.cnf）。

　　监控（show global status \G）、工具（mysqlreport）

3  SQL语句的优化。 

　　1.索引的优化。

　　　　a 抓出慢sql，配置my.cnf，或白名单机制

　　　　　long_query_time=2 #大于2秒就记录

　　　　   log-slow-queries=/data/3306/slow-log.log # 按天轮询 按天分析

　　　　b 慢查询日志分析工具

　　　　  mysqlsla

　　　　c 处理：每天晚上0点定时分析慢查询。

　　2.SQL语句的拆分（大的复杂的SQL语句拆分成小的）。

　　　　子查询、join连表查询

　　3.数据库是存储数据的地方，计算功能尽量放在前端应用程序。

　　4.搜索功能，一般不要用数据库。

4  架构的优化。

　　1.业务上进行拆分（像搜索 like）

　　2.数据库前端必须加缓存cache，例：memcached。

　　3.某些业务使用nosql持久化存储，例：memcahcedb，redis。

　　4.动态的数据静态化，整个文件静态化，页面片段静态化。

　　5.数据库集群与读写分离，一主多从等，通过程序或dbproxy进行集群读写分离。

　　6.单表超过千万，拆库拆表。

5  流程制度安全优化。 

 　　1.任何一次人为数据库记录的更新都要走流程。

　　 2.客户端管理。