MYSQL数据库与SQL优化

一、MYSQL数据库
2、MYSQL数据库存储引擎
2.1、ISAM
快速读写，不支持事务
2.2、MYISAM 主要使用
是ISAM的扩展，（MYSQL5.5之前的缺省数据库引擎）。
还提供了表格锁定的机制，来支持并发的读写操作，但是需要经常运行OPTIMIZE TABLE命令来恢复更新机制浪费的空间。因为MYSQL强调快速读写，所以在WEB开发中受到青睐。
使用该数据库搜索引擎，会生成三个文件
frm:表结构信息
MYD：数据文件信息
MYI：表的索引信息
缺点：数据越多写越慢，因为不仅要维护数据信息，还要维护索引信息。索引列越多，效率相对越低。
2.3、InnoDB 主要使用
支持了外键、支持了事务。效率相对之前的慢点。是MYSQL5.5包括及以上的默认搜索引擎。参照了ORACL的非锁定读（for update ）。
在MYSQL5.7版本中，InnoDB存储引擎管理的数据文件为两个，frm（表、数据）、ibd（索引）。
对于InnoDB每一条SQL语句都默认封装成事务，自动提交，这样影响速度，所以最好把多条SQL放在begin transaction和commit之间，组成一个事务。
必须有主键，通过主键查询效率很高，辅助索引是通过先查主键在通过主键查索引，主键别太大，否则会造成其他索引都会很大。主键索引和辅助索引是独立的。

          不保存表的具体行数，执行select count(*) from table 需要全表扫描。而MYISAM用一个变量保存了表的行数，执行count(*),速度很快，不能加group by   order by。
        不支持全文索引，而MYISAM支持全文索引。

     **2.4、Memory**

           是内存中的存储引擎。数据不存在磁盘上，仅将表结果存放在frm中。支持索引，支持Hash和B-Tree两种索引。定长存储，不支持TEXT和BLOB。应用很少。

    **2.5、DBCCluster**

           主要用于MYSQLCuster分布式集群环境。从5.0以后才提供的新功能。

**3、存储引擎管理**

   3.1、查看支持的存储引擎。
    show engines;

	3.2、查看默认引擎
	show variables like '%engine%'
    3.3、 查看表所使用的存储引擎
    show create table tablename;
    3.4、创建表的时候使用存储引擎
    create table tablename(column_name column_type) engine = engine_name;
    3.5、修改表的存储引擎
    alter table tablename engine = engine_name;
    3.6、修改默认存储引擎
    在配置文件中修改  my.ini  路径：c:\programdata/MYSQL Server 5.7/my.ini
    default-storage-engine-INNODB
4、	MYSQL中索引简介
	4.1、索引的优点
			第一：通过创建唯一索引，可以保证每一行数据的唯一性。
			第二：大大加快数据检索速度，这也是创建索引的主要原因。
			第三：可以加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
			第四：在使用分组和排序进行检索时，同样可以减少分组赫尔排序的时间。
			第五：通过使用索引，可以在查询过程中，使用查询优化器，提供系统的性能。
	4.2、索引的缺点
			第一：创建索引和维护索引需要耗时，并且随着数据量增加而增加
			第二：索引需要占空间，
			第三：对表数据做新增、修改删除，索引也需要维护，降低了数据的维护速度。
	4.3、什么样的字段适合索引
			第一：经常需要搜索的列上
			第二：在作为主键的列上，强制该列的唯一性和组织表中的排列结构
			第三：	在经常用在连接的列上，主要就是外键列，
    		第四：在经常需要根据范围搜索的列上，因为索引已经排序，其指定的范围是连续的。
    		第五：在经常需要排序的列上
    		第六：在经常使用的where条件列上，例如select * from table where f1 and f2.需要在f1\f2上建索引，只在f1或者f2上建没用。
    4.4、什么样的字段不适合索引
	    	第一：对于很少使用的列。
	    	第二：只有很少数据值的列。比如性别列。
	    	第三：对于定义为text、image和big类型的列，因为这些数据值要不就是很多，要不就是数据量很大。
	    	第四：当修改性能远远大于检索性能时。因为这俩相互矛盾，增加索引降低修改性能，减少索引提高修改性能，降低检索性能。
5、MYSQL中的索引
	5.1B-Tree索引
		B-Tree索引是扁平化的结构，深度减少，广度增加，按照balance tree的数据结构存储。比如跟节点下有多个子节点，每个子节点再有多个子节点。
	5.2Full-text索引	    	
		Full-text也是B-Tree,用来解决like低效问题，但是只能解决‘ABC%’like方式，如：ABCDE,索引建立为A,AB,ABC,ABCD,ABCDE。
6、索引管理
	6.1、普通索引
		没有任何限制，再MYISAM中默认是BTREE索引。
	6.1.1、创建索引
		CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN(LENGTH))	;   length可以不写。
		ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME (COLUMN(LENGTH));
		CREATE TABLE TABLE_NAME (ID INT,TITLE VARCHAR(30),INDEX INDEX_NAME(TITLE(LENGTH)));
		理论上来说，长字符串200，截取10、20 就可以高效的查找数据了。
	6.1.2、查看索引
		show index from table_name;	
		show keys from  table_name;	  #MYSQL可以使用。
	6.1.3、删除索引
		DROP INDEX INDEX_NAME;
		ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;
		ALTER TABLE TABLE_NAME DROP PRIMARY KEY;
	6.2、唯一索引
		索引列的值必须唯一，但允许空值。如果是组合索引，在列值的组合必须唯一。
	6.2.1.、创建索引
		CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN(LENGTH))		;
		ALTER TABLE TABLE_NAME ADD UNIQUE INDEX_NAME (COLUMN(LENGTH));
		CREATE TABLE TABLE_NAME (ID INT,TITLE VARCHAR(30),UNIQUE INDEX_NAME(TITLE(LENGTH)));
	6.3、全文索引。
		全文索引只能仅可以用于MYISAM表，在CHAR,VARCHAR,TEXT类型字段上，对于较大数据集，将数据输入到一个没有FULLTEXT的表中，然后创建索引，比输入到有FULLTEXT索引的表中速度更快，对于大数据量的数据表，生成索引是非常耗时耗费硬盘空间的做法。
	6.3.1创建索引
		CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(COLUMN(LENGTH))		;
		ALTER TABLE TABLE_NAME ADD FULLTEXT INDEX_NAME (COLUMN(LENGTH));
		CREATE TABLE TABLE_NAME (ID INT,TITLE VARCHAR(30),FULLTEXT INDEX_NAME(TITLE(LENGTH)));	
	6.4、组合索引
		create table article(id int,title varchar(255),time date).
		例如在表上建组合索引，alter table add index (title(50),time(10)).相当于建立两个索引。
		--title,time
		--title。从左开始进行的组合排列。ABCDE,索引建立为A,AB,ABC,ABCD,ABCDE。 和这里类似。
		select * from article where title ='测试' and time = 1234567890;
		select * from article where title ='测试' ;会用到索引
		select * from article where  time = 1234567890;不会用到索引。
	6.4.1、创建索引
	CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(COLUMN_list)		;
7、索引优化
	查询提高了效率，但是更新表速度降低，当insrt\updat\delete次数大于查询，放弃使用索引。一般来说不会出问题，但一个大表建立多种索引，就会使得索引文件膨胀。
	7.1、NULL值不包含在索引中，
		NULL值不包含在索引中，所以数据库设计的时候默认值不设置为null。
	7.2、使用短索引
		对串列进行索引，如果可能应该指定一个前缀长度，如varchar(255),如果在10或20个字符内，多数值唯一，创建索引的时候CREATE FULLTEXT INDEX INDEX_NAME ON TABLE_NAME(COLUMN(LENGTH))		;
		ALTER TABLE TABLE_NAME ADD FULLTEXT INDEX_NAME (COLUMN(LENGTH));
		CREATE TABLE TABLE_NAME (ID INT,TITLE VARCHAR(30),FULLTEXT INDEX_NAME(TITLE(LENGTH)));	
	7.3、索引列排序	
		MYSQL查询只使用一个索引，如果where已经使用索引，order by中的列不会使用索引，数据库默认排序满足要求尽量不使用order by 。尽量不要包含多个列的排序，如果需要最好做组合索引。
	7.4、like语句
		一般不鼓励like，like '%aaa%'不使用索引，like‘aaa%’会使用索引。
	7.5、不要在列上运算
		where year(date) <2017, 可以改为date<'2017-01-01'
	7.6、索引总结
explain  select * from tur_user where userid = 111;	  type那看到的是全部 ALL  还是索引 等等。
8、总结
	只对一下操作符使用索引，<,<=,=,>,>=,between in ,		 like ‘aaa%’。一个表理论可以创建16个索引，建议不超过6

个。
9、优化策略
9.1、避免全表扫描
9.2、避免判断null，可以设置默认值。
9.3、避免不等，不使用<>，!=
9.4、避免使用or
改为union all
select * from t where num =10 union select * from t where num =20;
9.5、慎用in\not in
select id from t1 where id in (select id from t2 where id >10);外层会使用全表扫描，可以改为
select id from t1 join (select id from t2 where id >10) t on t1.id = t.id。
9.6、注意模糊查询
只能like ‘aaa%’ 才使用索引。
9.7、避免查询条件中字段计算。
where year(date) <2017, 可以改为date<‘2017-01-01’
9.8、避免查询条件中对字段进行函数操作
where substring(name,1,3) = ‘abc’
应该改为 where name like ‘abc%’
9.9、where语句=左边注意
尽量不要进行函数、算数运算。
9.10、组合索引使用
查询条件顺序需要和索引顺序一致才会使用上索引。
9.11、不要定义无异议的查询
如需要生成一个空表结构
select col1,col2 into #t from t where 1=0;
改为create table #t
9.12、使用exists代替in
9.13、索引也可能失效，当索引列有大量数据重复，可能不适用索引，例如性别列
9.14、字段类型选择，
用数字类型代替字符型。
用varchar代替char。节省空间，在较小的空间中搜索更快。
9.15、不使用*
用字段代替*，不返回无用字段。