sql优化

索引

数据库 索引和字典目录差不多原理。如果数据不是很多，不用索引查询 会更快，否 则用索引查询会更快。索引和表存储位置是分开的，索引的主要目的是提高数据 检索性能。索引的创建和删除不会影响表中数据。然而删除索引有可能导致数据检索的性能下降。索引表会占据磁盘空间。索引中的数据和 字典目录一样按照字母顺序排序，每个数据指向数据表中的对应一个或多个位置。在where语句中会用到索引，如果没有指定where条件的索引会全表扫描。因为索引表中数据是有顺序的，所以数据库查找索引的时候会进行类似于二分法查找索引数据，然后用该索引数据指向的表中数据位置数据。索引是用来提升查询性能的。
创建单列索引CREATE INDEX NAME_IDX ON EMPLOYEES (LASTNAME);where条件中经常用此列时适合
唯一索引CREATE UNIQUE INDEX NAME_IDX ON EMPLOYEES (LASTNAME);一个列 是唯一的并且不允许null值适用，比上述索引查询性能更优
复合索引：复合索引指在表的两到多个列创建索引。列的顺序需要注意，通常索引确定性最强的索引应该放在 最前面，不是唯一索引的话一个索引的值可能映射到表中多个数据；但是最经常被查询的列应该放在最前面CREATE INDEX FLIGHT_IDX ON FLIGHTS (ROUTEID, AIRCRAFTFLEETID); 在这个例子中你假设这两个列在where条件中经常一起使用所以这么 用到。如果where条件中经常只指定一个列，那么单列索引更合适
隐藏索引：当你创建主键或者唯一约束，那么索引会自动创建
什么时候应该考虑索引？1.主键会自动创建唯一索引。外键是个极好的选择因为你经常用它join父表，通常join条件的列应该被索引。2.经常在 ORDER BY 和GROUP BY 中参考的列应该被考虑索引。例如如果你的查询语句中以name排序，在name列上索引是有益的，因为索引已经排好序了但是数据表中数据无序，这样查询不需要在排序了。3.如果一个列唯一值很多，或者在where语句中作为过滤条件此where条件只返回表中很少比例数据的话，应该被索引。4.具体环境中是否索引，唯一索引还是复合索引，得多测试，建立不当也会降低性能和浪费磁盘空间
什么时候应该避免索引？1.小表不应该建立索引，此时先查索引表再查数据表不如直接全表扫描数据表快2.如果一个索引返回很大比例的数据表中数据，不应该建立索引3.索引会降低插入更新性能。你可以在大量更新之前删除索引，更新完成后在创建索引，这样更新性能就不会影响。一个列频繁更新不应该作为索引。
修改一个索引：ALTER INDEX INDEX_NAME。生产环境修改索引要小心，因为索引被立即重新构建，需要资源开销，此外原先索引不能用了，重建过程中查询性能也会因为无法用索引导致下降
删除一个索引：DROP INDEX INDEX_NAME。删除索引会导致性能降低或者提高。对于缩影最好先测试在上生产环境.
问题：1.创建索引的主要缺点是什么2.为什么在一个复合索引中顺序很重要？3.索引的主要目的是阻止表数据重复吗？
练习：1.对于下面情况，决定是否添加索引a.几个列，但是表很小b.中等大小的表，但是重复是被允许的c.几个列，非常大的表，几个列在where条件中用作过滤d.达标，很多的列，很多增删改查操作
		2.在EMPLOYEES 表的POSITION列上创建EP_POSITION 索引
		3.修改上述索引变成唯一索引，为什么不能成功？

如何保证可读性？

1.总是每个语句另起一行，例如FROM 语句另起一行区分SELECT 语句，WHERE 语句另起一行区分FROM 语句
2.当语句中子句的参数超过一行时，使用制表符或空格进行缩进。要么都使用空制表符要么都使用空格
3.语句中有多张表，使用表别名
4.注释和sql语句分开，注释太多也会很乱
5.如果太多列被选择，那么一列名一行
6.如果很多表被使用在from中，一个表名一行
7.where条件中每个条件一行
SELECT EMPLOYEES.FIRSTNAME, EMPLOYEES.LASTNAME, AIRPORTS.CITY, AIRPORTS.

AIRPORTNAME, COUNTRIES.COUNTRY
FROM EMPLOYEES INNER JOIN AIRPORTS ON EMPLOYEES.AIRPORTID = AIRPORTS.AIRPORTID
INNER JOIN
COUNTRIES ON AIRPORTS.COUNTRYCODE = COUNTRIES.COUNTRYCODE WHERE EMPLOYEES.SALARY>70000 AND AIRPORTNAME LIKE ‘M%’ AND AIRPORTS.City LIKE ‘G%’;
可以转换成
SELECT E.FirstName,
E.LastName,
A.City,
A.AirportName,
C.Country
FROM Employees AS E INNER JOIN
Airports AS A ON E.AirportID = A.AirportID INNER JOIN
Countries AS C ON A.CountryCode = C.CountryCode
WHERE
(E.Salary > 70000)
AND (A.AirportName LIKE ‘M%’)
AND (A.City LIKE ‘G%’);

like

Query 1：SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, STATE
FROM EMPLOYEES
WHERE LASTNAME LIKE ‘STEVENS’;
Query 2:SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, STATE
FROM EMPLOYEES
WHERE LASTNAME LIKE ‘%EVENS%’;
Query 3:SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, STATE
FROM EMPLOYEES
WHERE LASTNAME LIKE ‘ST%’;
1会利用索引，2和3不怎么确定的和1比较来说，因此比1要慢。3很可能比2要快因为第一个字母是确定的所以3会使用索引

避免使用or操作符

1.SELECT EMPLOYEEID, LASTNAME, FIRSTNAME
FROM EMPLOYEES
WHERE CITY = ‘INDIANAPOLIS IN’
OR CITY = ‘KOKOMO’
OR CITY = ‘TERRE HAUTE’;
2.SELECT EMPLOYEEID, LASTNAME, FIRSTNAME
FROM EMPLOYEES
WHERE CITY IN (‘INDIANAPOLIS IN’, ‘KOKOMO’,
‘TERRE HAUTE’);
2比1更快

尽量避免使用HAVING

避免大的sort操作

大的sort操作意味着使用ORDER BY, GROUP BY, 和HAVING语句。一定要使用的话，可以再数据库使用不频繁的时候进行这些排序查询操作

使用存储过程

批量上传记录

此时不应该高峰期上传，而且上传时索引删除上传成功之后索引再重新创建

尽量优化查询次数最多的语句